MySQL Server Benchmarking 101
October 12, 2010
Benchmarking can be a very revealing process. It can be used to isolate performance problems, and drill down to specific bottlenecks. More importantly, it can be used to compare different servers in your environment, so you have an expectation of performance from those servers, before you put them to work servicing your application.
We deploy MySQL on a spectrum of different servers. Some may be servers we physically setup in a datacenter, while others are managed hosting servers, and still others are cloud hosted. Benchmarking can help give us a picture of what we're dealing with, be it an economy smart car, speedy BMW, or high performance NASCAR racer.
Simply put, we want to know what our server can handle. We want to get an idea of the IO performance, CPU, and overall database throughput. Simple queries run on the server can give us a sense of queries per second, or transactions per second if we want to get more complicated.
Benchmarking Disk IO
On Linux systems, there is a very good tool for benchmarking disk IO. It's called sysbench. Let's run through a simple example of installing sysbench and running our server through some paces.
Install it from the software repository as follows:
$ yum install -y sysbench
First, you'll want to setup the test files. Sysbench uses these to read and write to.
$ mkdir sysb $ cd sysb $ sysbench --test=fileio prepare
Next, you'll want to run some tests. There are a lot of options to choose from. I would recommend using rndrw (random reads and writes) and/or seqrewr to get a view of how fast IO is overall.
For example, you can run the test as follows:
$ sysbench --test=fileio --file-test-mode=rndrw run
There are a lot of data points to look at here, but focus in on the Mb/sec. This gives you a big picture view of how fast you can do IO to the disk subsystem.
Operations performed: 6000 Read, 4000 Write, 12800 Other = 22800 Total Read 93.75Mb Written 62.5Mb Total transferred 156.25Mb (3.4871Mb/sec) 223.17 Requests/sec executed Test execution summary: total time: 44.8083s total number of events: 10000 total time taken by event execution: 38.8548 per-request statistics: min: 0.02ms avg: 3.89ms max: 269.86ms approx. 95 percentile: 12.54ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 38.8548/0.00
There are other more granular tests you can do, controlling fsync, or doing async I/O. Looking at the Mb/sec though will help you compare across different servers, or RAID systems that might be level 5 versus level 10 for instance.
When you're done, be sure to clean up the test files:
$ sysbench --test=fileio cleanup
Sysbench can also be used to test the CPU performance. It is simpler, as it doesn't need to set up files and so forth. Run that as follows:
$ sysbench --test=cpu run Maximum prime number checked in CPU test: 10000 Test execution summary: total time: 35.4654s total number of events: 10000 total time taken by event execution: 35.4475 per-request statistics: min: 3.49ms avg: 3.54ms max: 9.04ms approx. 95 percentile: 3.50ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 35.4475/0.00
So if you want to compare across different servers, compare the average time 3.54ms above. Also, when running this test, be sure you are not hitting this volume with other work. Preferably, it should be a volume not used by the Operating System either if possible.
Benchmarking Database Throughput
With MySQL 5.1 distributions there is a tool included that can do very exhaustive database benchmarking. It's called mysqlslap.
First, check that you have it available on your server:
$ which mysqlslap
If you don't have it, you'll have to get it from the 5.1 source distribution. Don't worry, it's not as bad as it sounds. You can compile just the client tools (not the entire MySQL server) which is faster and less error prone to boot.
Go to http://www.mysql.com/downloads/mysql/, get a hold of a source tar.gz, and dig in. You may need to in yum install dev tools if you don't have them already, and other packages:
$ yum install glibc gcc libtool
Run configure and tell it not to build the mysql server:
$ ./configure --without-server --disable-shared $ make
If all goes well you'll have mysqlslap at your disposal in the bin subdirectory.
First thing you'll want to do is create a mysqlslap schema, as that is where it looks for objects. For my test below, I created a table "rtest" which has one column, a 64 character string. The following query is particularly badly performing because ORDER BY RAND() is a no-no.
$ mysqlslap --user=root -i 5 -c 10 -q "select * from rtest order by rand() limit 1" Benchmark Average number of seconds to run all queries: 7.694 seconds Minimum number of seconds to run all queries: 7.266 seconds Maximum number of seconds to run all queries: 8.337 seconds Number of clients running queries: 10 Average number of queries per client: 1
Notice that we can specify -i for iterations, to perform the test over and over to get an average. And then -c for concurrent queries. This is important because it is concurrency, and fighting for resources, which typically slows down a running database.
Comparing the above on two different MySQL servers will give you a good overall picture of how fast they are. Note of course that you don't want to have anything else fighting for resources, so no other MySQL sessions, and preferably no other processes or applications running on this server either.
Benchmarking can be a very revealing process. It can be used to isolate performance problems, and drill down to specific bottlenecks. More importantly though, it can be used as a way to compare different servers in your environment, so you have an expectation of performance from those servers, before you put them to work servicing your application.