Sysbench

Sysbench is a benchmarking tool that has several modes to bench:

• - fileio: This performs the file I/O test
• - cpu: This performs the CPU performance test
• - memory: This performs the memory functions speed test
• - threads: This performs the thread subsystem performance test
• - mutex: This performs the mutex performance test
• - oltp: This performs the OLTP test

To install it, run this command:

> aptitude install sysbench

The common test is to use the Online Transaction Processing (OLTP) scenario with small transactions to hit an optimized database. We will pass arguments to the command to simulate application threads (the --num-threads argument).

You can run this OLTP test with two kinds of scenarios:

• Read only (14 SELECT queries per transaction)
• Read/Write (14 SELECT, 1 INSERT, 1 UPDATE, and 1 DELETE queries per transaction)

The available version in Debian Wheezy is 0.4. A newer version exists with more interesting results such as a reporting interval every x sec. In addition, you can also find a complete set of tests from the sysbench repository. That's why we're not going to use the sysbench version from the MariaDB repository. To install it, you need to proceed as follows:

> aptitude install automake libtool libmariadbclient-dev bzr
> bzr branch lp:sysbench
> cd sysbench
> ./autogen.sh
> ./configure
> make

The sysbench binary is now available in the sysbench folder. We can now test it! First of all, you need to prepare your instance. This will create a dedicated username and database for the tests (sbtest):

> cd sysbench
> ./sysbench --test=tests/db/oltp.lua --num-threads=4 --max-time=30 --mysql-user=root prepare
sysbench 0.5: multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'

You can now run the test:

> ./sysbench --test=tests/db/oltp.lua --num-threads=4 --max-time=30 --mysql-user=root --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: 267.47, reads/s: 3751.99, writes/s: 1069.88, response time: 23.65ms (95%)
[ 10s] threads: 4, tps: 271.20, reads/s: 3796.78, writes/s: 1085.59, response time: 23.06ms (95%)
[ 15s] threads: 4, tps: 270.20, reads/s: 3785.20, writes/s: 1080.80, response time: 21.80ms (95%)
[ 20s] threads: 4, tps: 243.80, reads/s: 3412.38, writes/s: 975.19, response time: 23.80ms (95%)
[ 25s] threads: 4, tps: 265.00, reads/s: 3709.83, writes/s: 1060.81, response time: 22.29ms (95%)
[ 30s] threads: 4, tps: 257.60, reads/s: 3607.19, writes/s: 1029.60, response time: 24.05ms (95%)
OLTP test statistics:
 queries performed:
 read: 110320
 write: 31520
 other: 15760
 total: 157600
 transactions: 7880 (262.55 per sec.)
 deadlocks: 0 (0.00 per sec.)
 read/write requests: 141840 (4725.92 per sec.)
 other operations: 15760 (525.10 per sec.)

 General statistics:
 total time: 30.0132s
 total number of events: 7880
 total time taken by event execution: 119.9270s
 response time:
 min: 5.68ms
 avg: 15.22ms
 max: 110.65ms
 approx. 95 percentile: 23.15ms

 Threads fairness:
 events (avg/stddev): 1970.0000/10.32
 execution time (avg/stddev): 29.9817/0.01

We can see here how many operations this instance is able to handle with its configuration. In this case, we've seen the basic test with OLTP processing.

You can find other tests in the tests/db/ location. Sysbench is an old and common tool to perform tests against MariaDB.

You are now able to perform changes and see the benefits of them with sysbench.