Simple Benchmark of your MySQL Server

Installing Sysbench

The easiest way to perform benchmark on a MySQL server is by using sysbench. To install:

Debian/Ubuntu based:

$ apt-get install sysbench

RedHat/CentOS/Fedora based:

$ yum install sysbench

Preparing Sysbench

If we have a MySQL server ready, let’s create a database for sysbench called sbtest.

mysql> CREATE SCHEMA sbtest;

It is better to increase the default MySQL max_connections (which is too low for this test):

mysql> SET GLOBAL max_connections = 1000;

Let’s prepare the sample data for benchmarking with 2 million rows:

$ sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=2000000 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd prepare

Perform the Benchmark

Now let’s the benchmarking begin. We will start an OLTP (online transactions processing) benchmark test with 8 threads and 10000 max-requests then send the output to a file called sysbench.log:

$ sysbench --db-driver=mysql --test=oltp --num-threads=8 --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log

Repeat the steps for 16, 32, 64, 128, 256 and 512 threads and append the output to sysbench.log:

$ for i in 16 32 64 128 256 512; do sysbench --db-driver=mysql --test=oltp --num-threads=$i --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log ; done

We will then need to convert the sysbench output to CSV format so we could plot it to a graph using gnuplot.

$ cat sysbench.log | egrep " cat|threads:|transactions:" | tr -d "\n" | sed 's/Number of threads: /\n/g' | sed 's/\[/\n/g' | sed 's/[A-Za-z\/]\{1,\}://g'| sed 's/ \.//g' | awk {'print $1 $3'} | sed 's/(/\t/g' > sysbench.csv

The content of sysbench.csv should be similar as below:

8    274.53
16   397.47
32   442.79
64   516.90
128  503.36
256  414.35
512  445.67

Plotting Graph

I will use GNUplot for this task. To install:

yum install gnuplot #redhat/centos based
apt-get install gnuplot #debian/ubuntu based

Create a file called mygraph, and then paste following lines:

# output as png image
set terminal png
 
# save file to "benchmark.png"
set output "benchmark.png"
 
# graph title
set title "Benchmark for Sysbench"
 
# aspect ratio for image size
set size 1,1
 
# enable grid on y and x axis
set grid y
set grid x
 
# x-axis label
set xlabel "Threads"
 
# y-axis label
set ylabel "Transactions (tps)"
 
# plot data from sysbench.csv
plot "sysbench.csv" using (log($1)):2:xtic(1) with linesp notitle

Run following command to produce the graph based on our template:

$ gnuplot mygraph

And you should see it generates a png image which you can view it similar as below:

benchmark

That’s it. The benchmark results are well-presented in the graph. We can conclude that our MySQL server was performing nicely up until 64 threads then it appears to have some dropping after that.