MySQL Server Benchmarking 101


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.

Why Benchmark?

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

Benchmarking CPU

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 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

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.

Conclusion

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.

Related Articles

Benchmark Factory for Databases

»


See All Articles by Columnist

Sean Hull

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at [email protected] or visit http://www.iheavy.com for more info about consulting services.

Latest Articles