Using Lua-enabled sysbench

A quite common benchmark for MySQL is sysbench. It was written nearly 10 years ago by Alexey Kopytov.

Sysbench has modes to benchmark raw CPU performance, mutex speed, scheduler overhead and file IO performance. The probably most often used sysbench mode is OLTP. This benchmark mimics a OLTP scenario with small transactions hitting an optimized database. There are many variables to play with, most important is the number of simulated application threads (option --num-threads). The OLTP benchmark can be run read-only, then it does 14 SELECT queries per transaction. Or it can be run read-write which adds 2 UPDATEs and one INSERT and DELETE.

The latest release of this official sysbench tree is 0.4.12. Many Linux distributions ship a package for this.

However there is also a newer version of sysbench, that comes as version number 0.5.
It is only available from the source tree at Launchpad. Building it is straightforward:

bzr branch lp:sysbench sysbench-trunk
cd sysbench-trunk
./autogen.sh
./configure
make

The most important change in sysbench-0.5 is that the OLTP benchmark is now written in Lua. The Lua scripts can be found in the sysbench source directory under sysbench/tests/db. In order to run the OLTP benchmark you now have to give the path to the Lua script to run:

sysbench --test=.../tests/db/oltp.lua ... prepare
sysbench --test=.../tests/db/oltp.lua ... run

By writing your own Lua scripts you can now run arbitrary workloads with the same tool! But there is more.

A new option of sysbench-0.5 is --report-interval. If you set this to a number different from 0 (the default) then sysbench will report intermediate status messages every –report-interval seconds. This is very helpful to detect irregular behavior like write stalls.

Here is an example session:

sysbench-trunk/sysbench$ ./sysbench --test=tests/db/oltp.lua 
 --mysql-socket=/tmp/mysql.sock --mysql-user=root --num-threads=4 
 --max-time=30 --report-interval=5 run

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Random number generator seed is 0 and will be ignored

Threads started!

[   5s] threads: 4, tps: 345.89, reads/s: 4847.81, writes/s: 1384.35, response time: 17.59ms (95%)
[  10s] threads: 4, tps: 338.20, reads/s: 4738.40, writes/s: 1354.40, response time: 17.89ms (95%)
[  15s] threads: 4, tps: 342.20, reads/s: 4792.01, writes/s: 1368.80, response time: 17.97ms (95%)
[  20s] threads: 4, tps: 338.40, reads/s: 4738.59, writes/s: 1354.40, response time: 17.96ms (95%)
[  25s] threads: 4, tps: 333.40, reads/s: 4664.98, writes/s: 1331.99, response time: 17.91ms (95%)
OLTP test statistics:
    queries performed:
        read:                            141862
        write:                           40532
        other:                           20266
        total:                           202660
    transactions:                        10133  (338.75 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 182394 (6097.54 per sec.)
    other operations:                    20266  (677.50 per sec.)

General statistics:
    total time:                          29.9127s
    total number of events:              10133
    total time taken by event execution: 119.5746s
    response time:
         min:                                  4.14ms
         avg:                                 11.80ms
         max:                                 43.79ms
         approx.  95 percentile:              17.90ms

Threads fairness:
    events (avg/stddev):           2533.2500/5.76
    execution time (avg/stddev):   29.8936/0.00

Now when there is light, there is also shadow. In case of sysbench it is that sysbench now consumes more CPU cycles on its own. Means if you run equivalent OLTP benchmarks then sysbench-0.5 will report smaller numbers than sysbench-0.4.12. One reason is the increased latency in sysbench itself, the other is competition for CPU cycles (assuming you run sysbench on the same machine as the database server you benchmark).

In the end it just means that results from sysbench-0.4 and sysbench-0.5 are not comparable. IMHO a small price for the new flexibility.

To be continued …