MySQL benchmark with SysBench (I)

By taking advantage of the previous article about MySQL optimization, I am going to introduce a handy tool called SysBench and aimed at measuring the performance of a MySQL database among other things. In addition, it is also able to evaluate the I/O, scheduler and threads implementation performance, and memory allocation and transfer speed.

So I am going to use this tool in order to verify the improvements commented in the preceding article and related to some parameters of MySQL. The test will be run on Ubuntu Server 12.10 virtualized through VMware. The virtual machine will made up by a 6 GB hard drive, 2 GB of RAM and a couple of virtual cores.

First of all, let's install MySQL and SysBench and increase the default number of maximum connections to 512.

root@ubuntu-server:~# aptitude install mysql-server sysbench

root@ubuntu-server:~# vim /etc/mysql/my.cnf
...
max_connections = 512

root@ubuntu-server:~# service mysql restart

Now let's create a table of 1.000.000 of rows in a database called test by using SysBench.

root@ubuntu-server:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=xxxxxx prepare

Then a straightforward script taken care of running the tests will be developed. This bash script executes the OLTP (OnLine Transaction Processing) test on a table of 1.000.000 of rows. The time limit for the whole execution will be 300 seconds and read, update, delete and insert queries will be performed. The total number of maximum requests will be unlimited.

root@ubuntu-server:~# cat script.sh 
#!/bin/bash

for i in 8 16 32 64 128 256 512
do
    service mysql restart ; sleep 5
    sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=xxxxxx --max-time=300 --oltp-read-only=off --max-requests=0 --num-threads=$i run
done

As you can see in the above script, the number of worked threads to be created will be different in each loop iteration, from 8 to 512. So the idea is to run the script with various MySQL combinations and calculate the number of transactions per second.