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
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
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
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 …