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.

One thought on “Simple Benchmark of your MySQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *