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:

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:

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:

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 …

2 thoughts on “Using Lua-enabled sysbench

  1. Hi,

    How can we benchmark or valuate the out put provided from sysbench like if i got something like below,

    Test execution summary:

    total time: 60.0036s
    total number of events: 32620
    total time taken by event execution: 359.8823
    per-request statistics:
    min: 1.66ms
    avg: 11.03ms
    max: 981.94ms
    approx. 95 percentile: 15.13ms
    Threads fairness:

    events (avg/stddev): 5436.6667/31.44
    execution time (avg/stddev): 59.9804/0.00

    How can we determine that the performance is good or bad?

  2. Pingback: Lua sysbench – crash course « The MariaDB Blog

Comments are closed.