How do I benchmark MySQL?

Question

I'm currently using MySQL workbench. I want to see the difference in performance as the number of rows in a table increases. I want to specifically test and compare 1000 rows, 10,000 rows, 100,000 rows, 1,000,000 rows and 10,000,000 rows.

So, are there any tools that will allow me to do this and provide statistics on disk I/O, memory usage, CPU usage and time to complete query?

Answer 1

yes. Benchmark is your best option I guess for some of them

you can make simple queries likes:

jcho360> select benchmark (10000000,1+1);
+--------------------------+
| benchmark (10000000,1+1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.18 sec)

jcho360> select benchmark (10000000,1/1);
+--------------------------+
| benchmark (10000000,1/1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (1.30 sec)

a sum is faster than a division (you can do this with all the things that you can imagine.

I'll recommend you to take a look to this program that will help you with this part of performance.

• Mysqlslap (it's like benchmark but you can customize more the result).
• SysBench (test CPUperformance, I/O performance, mutex contention, memory speed, database performance).
• Mysqltuner (with this you can analize general statistics, Storage engine Statistics, performance metrics).
• mk-query-profiler (perform analysis of a SQL Statement).
• mysqldumpslow (good to know witch queries are causing problems).

some of them are third party, but I'm pretty sure that you can find tons of info googling the name of the APP