Check load on mysql database

mysql performance load stress-testing
Industrial picture Industrial · Jan 24, 2010 · Viewed 12.4k times · Source

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

Answer

Surender picture Surender · Oct 27, 2012

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

Related questions

MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
MySQL vs MongoDB 1000 reads

I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'. I wanted to compare speed with MongoDB and …

Read More