Check load on mysql database

Question

What would be the best ways to monitor mysql performance and load, queries per second, total queries over a hour etc?

Solution 1

Firstly, be sure to watch for slow queries: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

mysqladmin extended is very useful. See http://www.mysql.com/news-and-events/newsletter/2004-01/a0000000301.html for some tips.

Solution 2

You can use the mysqlslap utility which is provided by MySQL after 5.1 version.

fire below query at your windows command prompt to get the report in csv format or text format as required by you.

mysqlslap.exe --user=root -p --auto-generate-sql --concurrency=40 --number-of-queries=10000 --number-char-cols=4 --number-int-cols=7 >> /Desktop/output.log -vv

mysqlslap.exe --csv=/Desktop/output.csv --user=root -p --auto-generate-sql --concurrency=50 --number-of-queries=500 --number-char-cols=4 --number-int-cols=7 -vv

This command runs on Unix server also.

Output of this command will be somewhat like below.

Building Create Statements for Auto
Building Query Statements for Auto
Parsing engines to use.
Starting Concurrency Test
Loading Pre-data
Generating primary key list
Generating stats
Benchmark

Average number of seconds to run all queries: 124.478 seconds 

Minimum number of seconds to run all queries: 124.478 seconds

Maximum number of seconds to run all queries: 124.478 seconds

Number of clients running queries: 40

Average number of queries per client: 250