Benchmark MySQL with Sysbench

Tuning your MySQL configuration day in and day out without having an idea of what the hardware of the server can actually do in a perfect world can be a bit frustrating. This is where a tool like sysbench comes into play. Sysbench can allow you to get an idea of how MySQL will perform on your chosen server under load, using a basic set of tests.

It is important to note that this guide will not show you how to benchmark your existing MySQL dataset, but instead, it shows how your overall server will react to a generic MySQL dataset under heavy load.

Situations where this becomes useful is when you want to swap those SAS drives with SSD’s, or perhaps performing a comparison between running MySQL on a server vs using something like Amazon RDS or Rackspace Cloud Databases. It allows you to get a feel for where the bottlenecks may potentially come into play. Perhaps from IO, network saturation, CPU, etc.

Getting started with sysbench is pretty straight forward. I’ll outline how to create the test dataset, then perform a few benchmarks off that dataset. For the purposes of this article, I am most concerned about how many transactions per second MySQL can handle on my server in a perfect world.

First, log into your database server, and create a new test database. Do not attempt to use an existing database with content as sysbench will be populating it with its own tables. I posted 2 grant user statements on purpose. Set the access, username, and password as needed for your environment:

[root@db01 ~]# mysql
mysql> create database sbtest;
mysql> grant all on sbtest.* to 'sysbench'@'%' identified by 'your_uber_secure_password';
mysql> grant all on sbtest.* to 'sysbench'@'localhost' identified by 'your_uber_secure_password';
mysql> flush privileges;

Next, log into your server running sysbench, and install it:

# CentOS 6
[root@sysbench01 ~]#  rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@sysbench01 ~]#  yum install sysbench

# CentOS 7
[root@sysbench01 ~]#  rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
[root@sysbench01 ~]#  yum install sysbench

# Ubuntu 12.04 / Ubuntu 14.04
[root@sysbench01 ~]#  apt-get update
[root@sysbench01 ~]#  apt-get install sysbench

On the sysbench server, run sysbench with the prepare statement so it can generate a table with data to be used during the benchmark. This command will populate a table in the sbtest database with 1,000,000 rows of data, and force innodb:

[root@sysbench01 ~]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=192.168.1.1 --mysql-db=sbtest --mysql-user=sysbench --mysql-password=your_uber_secure_password --db-driver=mysql --mysql-table-engine=innodb prepare

You can verify the table was written properly on your database server by:

[root@db01 ~]# mysql
mysql> use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest           |
+------------------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.13 sec)

Back on the server you are running sysbench on, we are going to run a benchmark using a read/write test (–oltp-read-only=off), for a max time of 60 seconds using 64 threads, with the test mode set to complex (range queries, range SUM, range ORDER by, inserts and updates on index, as well as non-index columns, delete rows).

[root@sysbench01 ~]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=192.168.1.1 --mysql-db=sbtest --mysql-user=sysbench --mysql-password=your_uber_secure_password --max-time=60 --oltp-test-mode=complex --oltp-read-only=off --max-requests=0 --num-threads=64 --db-driver=mysql run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            1932084
        write:                           690030
        other:                           276012
        total:                           2898126
    transactions:                        138006 (2299.32 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2622114 (43687.09 per sec.)
    other operations:                    276012 (4598.64 per sec.)

Test execution summary:
    total time:                          60.0203s
    total number of events:              138006
    total time taken by event execution: 3839.0815
    per-request statistics:
         min:                                  8.76ms
         avg:                                 27.82ms
         max:                                313.65ms
         approx.  95 percentile:              50.64ms

Threads fairness:
    events (avg/stddev):           2156.3438/34.49
    execution time (avg/stddev):   59.9856/0.01

Lets say you want to run the same test, but perform the test using read only queries:

[root@sysbench01 ~]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=192.168.1.1 --mysql-db=sbtest --mysql-user=sysbench --mysql-password=your_uber_secure_password --max-time=60 --oltp-test-mode=complex --oltp-read-only=on --max-requests=0 --num-threads=64 --db-driver=mysql run

Here is an example of running the test in read/write mode, and disconnecting and reconnecting after each query:

[root@sysbench01 ~]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=192.168.1.1 --mysql-db=sbtest --mysql-user=sysbench --mysql-password=your_uber_secure_password --max-time=60 --oltp-test-mode=complex --oltp-read-only=off --max-requests=0 --num-threads=64 --db-driver=mysql --oltp-reconnect-mode=query run

Once you are done with your testing, you can clean up the the database by:

[root@db01 ~]# mysql
mysql> drop database sbtest;
mysql> DROP USER 'sysbench'@'localhost';
mysql> DROP USER 'sysbench'@'%';
mysql> flush privileges;
mysql> quit

Load testing with Siege

Taken directly from the authors site at https://www.joedog.org/siege-home: Siege is an http load testing and benchmarking utility. It was designed to let web developers measure their code under duress, to see how it will stand up to load on the internet. Siege supports basic authentication, cookies, HTTP, HTTPS and FTP protocols. It lets its user hit a server with a configurable number of simulated clients. Those clients place the server “under siege.”

This tool becomes extremely useful when you need to get a feel for how a solution will handle under normal or high traffic events. During these simulated traffic events, it may help expose inefficient database queries, CPU intensive code, opportunities for setting up caching, or simply demonstrate the need for having to add additional web servers to increase overall site capacity.

Unlike many other load testers out there, Siege allows you to populate a file with a listing of your URL’s to help generate a more realistic simulation. While Siege can support a number of different tests, I generally keep it simple and basic. I’ll outline how I utilize it below.

First, install siege:

# CentOS 6 / RedHat 6
[root@loadtest01 ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@loadtest01 ~]# yum install siege

# CentOS 7 / RedHat 7
[root@loadtest01 ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
[root@loadtest01 ~]# yum install siege

# Ubuntu 12.04 / Ubuntu 14.04
[root@loadtest01 ~]# apt-get update
[root@loadtest01 ~]# apt-get install siege

Now click around your site recording about 10 or more URL’s. Make sure it includes various sections of your site that people would likely be visiting. For example, if running an e-commerce site, be sure to include the base domain several times since that will be accessed most often usually. But also include several landing pages, a couple of products, and maybe the shopping cart. For example:

[root@loadtest01 ~]# vim /root/site.txt
http://example-store.com
http://example-store.com
http://example-store.com/cameras
http://example-store.com/electronics
http://example-store.com/home-decor/decorative-accents
http://example-store.com/home-decor/decorative-accents/herald-glass-vase
http://example-store.com/apparel/shirts
http://example-store.com/home-decor/books-music
http://example-store.com/home-decor/books-music/a-tale-of-two-cities.html
http://example-store.com/sale.html
http://example-store.com
http://example-store.com

You should now be able to run your load test:

[root@loadtest01 ~]# siege -c50 -d3 -t30M -i -f /root/site.txt

This load test will be sending 50 concurrent connections, with a random delay between 1 and 3 seconds, lasting for 30 minutes against the url’s posted in /root/site.txt.

A couple quick notes about the flags:

-c, --concurrent=NUM      CONCURRENT users, default is 10
-d, --delay=NUM           Time DELAY, random delay before each request
                            between 1 and NUM. (NOT COUNTED IN STATS)
-t, --time=NUMm           TIMED testing where "m" is modifier S, M, or H
                            ex: --time=1H, one hour test.
-i, --internet            INTERNET user simulation, hits URLs randomly.
-f, --file=FILE           FILE, select a specific URLS FILE.

While the simulated traffic test is running, things you will want to watch your solution for include:
– Observe the CPU, Memory, IO, and Memory usage of the servers.
– Check the database to see if there are any intensive queries consistently running, perhaps indicating the need for redis or memcached.
– Check the MySQL slow query log to see if there are queries that may need a table index, or otherwise need to be optimized.
– Check that any caching software you have installed is returning a good hit rate.
– Ensuring the site remains online during the tests.

Sometimes you want to load test a website that has a username and password prompt provided by an htaccess file. To allow siege to authenticate, do the following:

[root@loadtest01 ~]# auth=`echo -n 'username:password' | openssl base64`
[root@loadtest01 ~]# siege -c50 -d3 -t30M --header="Authorization:Basic $auth" -i -f /root/site.txt