Lua sysbench – crash course

This is a follow-up on my previous blog post about using Lua enabled sysbench. Today I will dive into how to write Lua scripts for sysbench. Look at this simple example:

function prepare ()
  local i
  print("creating table sbtest.t1 ...")
  db_query("create table t1 (c1 int unsigned primary key, c2 int)")
  db_query("begin")
  for i= 1, 1000 do
    db_query("insert into t1 values (" .. i .. "," .. i .. ")")
  end
  db_query("commit")
end

function cleanup()
  db_query("drop table t1")
end

function help()
  print("sysbench Lua demo; no special command line options available")
end

function thread_init(thread_id)
end

function thread_done(thread_id)
  db_disconnect()
end

function event(thread_id)
  db_query("select c2 from t1 where c1=" .. sb_rand(1, 1000))
end

There are 3 functions prepare(), cleanup() and help(). Those are executed when sysbench is asked to perform the respective action (i.e. sysbench … help).

If you do sysbench ... run then things are different. First sysbench starts the requested number of threads and executes the thread_init() function for each. Prior to that each thread connects to the database server (using the sbtest database). Hence you need not (actually: must not) call db_connect() from thread_init(). The parameter thread_id runs from 1 to the requested number of threads.

After that sysbench pauses for a moment to give all threads the chance to finish initialization. Then each thread executes the event() function in a loop until either the limit on the number of requests (–max-requests) or the execution time limit (–max-time) has been reached.

Finally each thread executes the thread_done() function. Note: db_disconnect() is not automatically called when a sysbench thread exits. If sysbench connects directly to a MySQL server, this is no issue because when sysbench exits, all sockets are closed and MySQL terminates the related sessions. If you connect through some proxy, you might need to explicitly close the connection, just as I have done above.

If any of the mentioned Lua functions is empty, you can omit it from the Lua script. Above I added the thread_init() function only for sake of completeness.

Here is an example session using the Lua code from above:

sysbench-trunk/sysbench$ ./sysbench --test=tests/db/demo.lua help
sysbench 0.5:  multi-threaded system evaluation benchmark

sysbench Lua demo; no special command line options available

sysbench-trunk/sysbench$ ./sysbench --test=tests/db/demo.lua 
  --mysql-socket=/tmp/mysql.sock --mysql-user=root prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

creating table sbtest.t1 ...

sysbench-trunk/sysbench$ ./sysbench --test=tests/db/demo.lua 
  --mysql-socket=/tmp/mysql.sock --mysql-user=root --num-threads=4 
  --max-requests=0 --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: 0.00, reads/s: 37499.12, writes/s: 0.00, response time: 0.19ms (95%)
[  10s] threads: 4, tps: 0.00, reads/s: 37041.45, writes/s: 0.00, response time: 0.19ms (95%)
[  15s] threads: 4, tps: 0.00, reads/s: 37358.99, writes/s: 0.00, response time: 0.19ms (95%)
[  20s] threads: 4, tps: 0.00, reads/s: 36109.20, writes/s: 0.00, response time: 0.20ms (95%)
[  25s] threads: 4, tps: 0.00, reads/s: 35841.39, writes/s: 0.00, response time: 0.20ms (95%)
OLTP test statistics:
    queries performed:
        read:                            1096527
        write:                           0
        other:                           0
        total:                           1096527
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1096527 (36550.85 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          30.0000s
    total number of events:              1096527
    total time taken by event execution: 118.0536s
    response time:
         min:                                  0.07ms
         avg:                                  0.11ms
         max:                                 16.98ms
         approx.  95 percentile:               0.19ms

Threads fairness:
    events (avg/stddev):           274131.7500/5125.89
    execution time (avg/stddev):   29.5134/0.01

If you know Lua, you might have noticed that demo.lua uses some nonstandard Lua functions. Sysbench adds several new functions to the Lua interpreter. The most important are probably db_query() and sb_rand(). There are many more. See the C function sb_lua_new_state() in script_lua.c from the sysbench source for a complete list.

You should also have a look at the delivered Lua code on how to use the functions.

Coming up next: extending oltp.lua …