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 …