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.
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.
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
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.
it from the software repository as follows:
$ yum install -y sysbench
you’ll want to setup the test files. Sysbench uses these to read and
$ mkdir sysb $ cd sysb $ sysbench --test=fileio prepare
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.
example, you can run the test as follows:
$ sysbench --test=fileio --file-test-mode=rndrw run
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
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
you’re done, be sure to clean up the test files:
$ sysbench --test=fileio cleanup
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
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
MySQL 5.1 distributions there is a tool included that can do very exhaustive
database benchmarking. It’s called mysqlslap.
check that you have it available on your server:
$ which mysqlslap
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.
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
configure and tell it not to build the mysql server:
$ ./configure --without-server --disable-shared $ make
all goes well you’ll have mysqlslap at your disposal in the bin subdirectory.
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 1 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
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.
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.
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.