Sysbench is a open source product that enables you to perform various system benchmarks including databases. Drizzles performs regression testing of every trunk revision with a branched version of sysbench within Drizzle Automation.
A pending branch https://code.launchpad.net/~elambert/sysbench/trunk_drizzle_merge by Eric Lambert now enables side by side testing with MySQL and Drizzle. On a system running MySQL and Drizzle I was able install this sysbench branch with the following commands.
cd bzr bzr branch lp:~elambert/sysbench/trunk_drizzle_merge cd trunk_drizzle_merge/ ./autogen.sh ./configure make sudo make install
Running the default lua tests supplied required me to ensure drizzle was in my path and that I created the ‘sbtest’ schema. I’ll be sure it add that checking to my future developed benchmark scripts.
$ cd sysbench/tests/db $ sysbench --test=insert.lua --db_driver=drizzle prepare sysbench v0.4.10: multi-threaded system evaluation benchmark FATAL: unable to connect to Drizzle server: 23 FATAL: error 0: Unknown database 'sbtest' FATAL: failed to execute function `prepare': insert.lua:7: Failed to connect to the database
$ drizzle -e "create schema sbtest" $ sysbench --test=insert.lua --db_driver=drizzle prepare sysbench v0.4.10: multi-threaded system evaluation benchmark Creating table 'sbtest'...
And running produces the following results.
$ sysbench --num-threads=1 --test=insert.lua --db_driver=drizzle run
sysbench v0.4.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Threads started!
OLTP test statistics:
queries performed:
read: 0
write: 10000
other: 0
total: 10000
transactions: 0 (0.00 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 10000 (879.68 per sec.)
other operations: 0 (0.00 per sec.)
Test execution summary:
total time: 11.3678s
total number of events: 10000
total time taken by event execution: 11.3354s
per-request statistics:
min: 0.32ms
avg: 1.13ms
max: 68.74ms
approx. 95 percentile: 2.41ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 11.3354/0.0
Rerunning the prepare also lacked some auto cleanup to allow for automated re-running.
$ sysbench --test=insert.lua --db_driver=drizzle prepare Creating table 'sbtest'... ALERT: Drizzle Query Failed: 1050:Table 'sbtest' already exists FATAL: failed to execute function `prepare': insert.lua:57: Database query failed
For MySQL
$ sysbench --test=insert.lua --db_driver=mysql --mysql_table_engine=innodb prepare sysbench v0.4.10: multi-threaded system evaluation benchmark Creating table 'sbtest'...
Unfortunately this doesn’t actually create the table in the right storage engine, I had to hack the code to ensure I was comparing InnoDB in each test.
$ sysbench --num-threads=1 --test=insert.l
ua --db_driver=mysql run
sysbench v0.4.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Threads started!
OLTP test statistics:
queries performed:
read: 0
write: 10000
other: 0
total: 10000
transactions: 0 (0.00 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 10000 (897.67 per sec.)
other operations: 0 (0.00 per sec.)
Test execution summary:
total time: 11.1399s
total number of events: 10000
total time taken by event execution: 11.1084s
per-request statistics:
min: 0.27ms
avg: 1.11ms
max: 252.63ms
approx. 95 percentile: 2.48ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 11.1084/0.00
Armed with a working environment I can now write some more realistic production like tests in Lua.
Hi Ronald,
The steps that you have mentioned is the situation where I have installed mysql in the default location. What if I have mysql, (compiled and build) in a different location, say /home/amitb, in that case how do I inform sysbench as to where my executables are. I know about a couple of options like –with-mysql-includes. But it would be really helpful if you can help me with a couple of more examples.
Thanks,
Amit