Sysbench is a tool commonly used in database testing. Since version 1.0, it has supported more powerful custom functions, allowing users to conveniently write some Lua scripts to simulate load. The purpose of writing this article is, firstly, because I wanted to explore Sysbench’s custom load usage. Secondly, because I tried the mysqlslap tool provided by MySQL’s official source, and found that it freezes easily during database performance testing, which could mislead users into thinking there is an issue with the database, causing trouble for many. Therefore, I want to help people avoid these pitfalls.

A Simple Example

#!/usr/bin/env sysbench

require("oltp_common")

function prepare_statements()
end

function event()
    con:query("set autocommit = 1")
end

The first line require includes Sysbench’s built-in basic library; the empty prepare_statement is a callback function from oltp_common that must be present; the specific execution of a single load is implemented in the event function.

Save this script as a Lua file, for example, named set.lua, and then execute it using sysbench.

sysbench --config-file=config --threads=100 set.lua --tables=1 --table_size=1000000 run

You can use the above command. Of course, here --tables=1 and --table_size=1000000 are not useful for this load, so they are optional. --threads controls concurrency.

$ cat config
time=120
db-driver=mysql
mysql-host=172.16.5.33
mysql-port=34000
mysql-user=root
mysql-db=sbtest
report-interval=10

In the config file, parameters you don’t frequently adjust are written once to avoid having a long string of parameters in the command line. These are required fields: time represents the test duration, report-interval is used to observe real-time performance results, and the others pertain to how to connect to the database.

The running output generally looks like:

[ 10s ] thds: 100 tps: 94574.34 qps: 94574.34 (r/w/o: 0.00/0.00/94574.34) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 77720.30 qps: 77720.30 (r/w/o: 0.00/0.00/77720.30) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 56080.10 qps: 56080.10 (r/w/o: 0.00/0.00/56080.10) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 93315.90 qps: 93315.90 (r/w/o: 0.00/0.00/93315.90) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 97491.02 qps: 97491.02 (r/w/o: 0.00/0.00/97491.02) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 94034.27 qps: 94034.27 (r/w/o: 0.00/0.00/94034.27) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 74707.37 qps: 74707.37 (r/w/o: 0.00/0.00/74707.37) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 89485.10 qps: 89485.10 (r/w/o: 0.00/0.00/89485.10) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 109296.44 qps: 109296.44 (r/w/o: 0.00/0.00/109296.44) lat (ms,95%): 2.91 err/s: 0.00 reconn/s: 0.00

Finally, there will be a summary report.

SQL statistics:
    queries performed:
        read:                            0
        write:                           0
        other:                           10424012
        total:                           10424012
    transactions:                        10424012 (86855.65 per sec.)
    queries:                             10424012 (86855.65 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      86855.6517
    time elapsed:                        120.0154s
    total number of events:              10424012

Latency (ms):
         min:                                    0.09
         avg:                                    1.15
         max:                                 1527.74
         95th percentile:                        4.91
         sum:                             11994122.49

Threads fairness:
    events (avg/stddev):           104240.1200/600.21
    execution time (avg/stddev):   119.9412/0.01