5

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

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Industrial
  • 41,400
  • 69
  • 194
  • 289

2 Answers2

5

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
Mohit Hapani
  • 49
  • 7
Surender
  • 51
  • 1
  • 1
2

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.

Eli
  • 1,269
  • 8
  • 17
  • Hi eli, thanks. Cannot get access to the slow queries log ftm, but I will look into it. Can't however see how I output the mysqladmin extended. Can i do it with a normal PHP query? – Industrial Jan 25 '10 at 15:19
  • `mysqladmin` is an application on your server. You could do it with a normal query, probably, but I don't know enough about it. In PHP, you could run `exec('mysqladmin extended')` to have your system execute it as a shell command. – Eli Jan 25 '10 at 15:53