Given the recent excitement & interest around OpenStack I wanted to make sure I was ready to conduct appropriate evaluations of system performance. I generally turn to sysbench since it comes with a variety of different tests (accessed via –test= option interface), including:
- fileio – File I/O test
- cpu – CPU performance test
- memory – Memory functions speed test
- threads – Threads subsystem performance test
- mutex – Mutex performance test
As you can see, sysbench lets you stress many of the fundamental components of your hardware and infrastructure, such as your disk subsystem, along with your CPUs and memory. An additional option exists that is designed to perform synthetic stress testing of MySQL, and I was surprised when I didn’t see it in the above list on version 0.5, as it used to show up as “oltp – OLTP test”. What happened to –test=oltp ??
This list is from the latest release of sysbench which is 0.5 — you’re only going to be on this version if you build it yourself or if you use the package provided courtesy of Frederic Descamps (thanks lefred!). If you’re using the version from EPEL, Ubuntu 14.04, or Debian 7 you’re still using version 0.4.12 (check with sysbench –version). One thing you’ll notice is that the test type of OLTP doesn’t show up anymore. What gives? I was scratching my head until I asked on Percona IRC and found out that in 0.5 the standard OLTP test type was replaced with a different syntax, that instead of passing parameters to sysbench you instead reference scripts written in lua. The advantage here is that now you have an interface in order to write your own specific load tests (provided you know lua, but it isn’t hard). For those of you looking to run the pre-canned load tests they still exist but you have to have them as part of the RPM install or otherwise copied to your system.
Fortunately if you use the package provided by lefred you’ll find these lua scripts here (this is using Amazon ami as of August 4th, 2014):
[root@pxc-control ~]# ls -l /usr/share/doc/sysbench/tests/db/
total 44
-rw-r--r-- 1 root root 3585 Sep 7 2012 common.lua
-rw-r--r-- 1 root root 340 Sep 7 2012 delete.lua
-rw-r--r-- 1 root root 830 Sep 7 2012 insert.lua
-rw-r--r-- 1 root root 2925 Sep 7 2012 oltp.lua
-rw-r--r-- 1 root root 342 Sep 7 2012 oltp_simple.lua
-rw-r--r-- 1 root root 425 Sep 7 2012 parallel_prepare.lua
-rw-r--r-- 1 root root 343 Sep 7 2012 select.lua
-rw-r--r-- 1 root root 3964 Sep 7 2012 select_random_points.lua
-rw-r--r-- 1 root root 4066 Sep 7 2012 select_random_ranges.lua
-rw-r--r-- 1 root root 343 Sep 7 2012 update_index.lua
-rw-r--r-- 1 root root 552 Sep 7 2012 update_non_index.lua
So the trick (if you want to call it that) is that instead of passing a single word to the –test directive, instead you pass the full path to the lua script.
This is the old way (sysbench 0.4.12 from EPEL repo):
--test=oltp --oltp-test-mode=complex
This is the new way (sysbench 0.5):
--test=/usr/share/doc/sysbench/tests/db/insert.lua
Here is an example of a test I’m running through haproxy against a 3-node PXC cluster doing the INSERT-only test type so you can see the full syntax I pass to sysbench:
[root@pxc-control ~]# cat sys_haproxy.sh
#!/bin/bash
sysbench
--test=/usr/share/doc/sysbench/tests/db/insert.lua
--mysql-host=pxc-control
--mysql-port=9999
--mysql-user=sysbench-haproxy
--mysql-password=sysbench-haproxy
--mysql-db=sbtest
--mysql-table-type=innodb
--oltp-test-mode=complex
--oltp-read-only=off
--oltp-reconnect=on
--oltp-table-size=1000000
--max-requests=100000000
--num-threads=3
--report-interval=1
--report-checkpoints=10
--tx-rate=24
$1
And here’s what the insert.lua script looks like:
[root@pxc-control ~]# cat /usr/share/doc/sysbench/tests/db/insert.lua
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
end
function event(thread_id)
local table_name
local i
local c_val
local k_val
local pad_val
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
if (oltp_auto_inc) then
i = 0
else
i = sb_rand_uniq(1, oltp_table_size)
end
k_val = sb_rand(1, oltp_table_size)
c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])
rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, k_val, c_val, pad_val))
end
The thing that I like most about sysbench 0.5 (beyond the lua interface, of course!) is that it now comes with a –report-interval option (which I generally set as = 1) so that you get output while the script is running. No more waiting until the end of the test to get feedback! Here’s a sample of sysbench 0.5 in action running the INSERT test through a local haproxy instance and writing to three nodes in a PXC cluster such as OpenStack Trove might do:
[root@pxc-control ~]# ./sys_haproxy.sh run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 3
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored
Threads started!
[ 1s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 1099.28, response time: 9.86ms (95%)
[ 2s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 973.02, response time: 10.77ms (95%)
[ 3s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 1181.01, response time: 6.23ms (95%)
[ 4s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 1103.00, response time: 6.77ms (95%)
I would also like to call your attention to a blog post by Nilnandan Joshi from Percona’s Support team where he describes a method to build sysbench 0.5 on Debian 7. Thanks Nil for pointing this out!
I hope that helps others out there who upgrade to sysbench 0.5 and then have questions about where –test=oltp went to. I’d love to hear your own sysbench use cases, and whether anyone else is publishing lua scripts for their own load testing!
The post Using sysbench 0.5 for performing MySQL benchmarks appeared first on MySQL Performance Blog.
) : “DATA DIRECTORY” for InnoDB tables.
