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