A tutorial on using Sysbench to test MySQL's performance _lua

Given the recent interest and passion for OpenStack, I want to make sure I can do the proper system performance assessment. I started by turning to Sysbench because it brought a wide range of tests for different levels (known through-test=option), including:

• FileIO-File I/O Test
• CPU-CPU System can test
• Memory-memory function Speed test
• Threads-Threading Subsystem system can test
• Mutex-Mutex performance test

As you can see, Sysbench will focus your mind on many of the underlying components of your hardware and infrastructure, such as your disk subsystem, and your CPUs and memory. There is an additional option that is used to perform simulated stress tests on MySQL, and I was surprised when I did not see the above supported test items in version 0.5, although it could be used to display "online transaction processing – online transaction processing testing". -TEST=OLTP, what the hell is going on?

This list comes from the latest release version of Sysbench 0.5--if you are only using it on this version, use Frederic descamps (thanks lefred!) for the package provided. If you use the (System) is Epel,ubuntu 14.04 or Debian 7, you can use the 0.4.12 version (check sysbench version-version). Then, test type OLTP will never appear again. What does this version do? I scratched my head until I asked Percona IRC that the standard OLTP test type in version 0.5 was replaced with a different syntax, using passing arguments to Sysbench, replacing the reference script with Lua. The advantage is that you now have an interface that can be written to your specific load test (provide you with the LUA (interface) you know, and it's not hard to use). If you still want to run the predefined load tests, they still exist, but you have to install them as part of the RPM or copy them directly to your system.

Fortunately, if you use the packages provided by lefred, you will find the following LUA scripts (which use the Amazon Ami as of August 4, 2014):

Copy Code code as follows:

[Root@pxc-control ~]# ls-l/usr/share/doc/sysbench/tests/db/
Total 44
-rw-r--r--1 root 3585 Sep 7 Common.lua
-rw-r--r--1 root Delete.lua Sep 7
-rw-r--r--1 root 830 Sep 7 Insert.lua
-rw-r--r--1 root 2925 Sep 7 Oltp.lua
-rw-r--r--1 root 342 Sep 7 Oltp_simple.lua
-rw-r--r--1 root 425 Sep 7 Parallel_prepare.lua
-rw-r--r--1 root 343 Sep 7 Select.lua
-rw-r--r--1 root 3964 Sep 7 Select_random_points.lua
-rw-r--r--1 root 4066 Sep 7 Select_random_ranges.lua
-rw-r--r--1 root 343 Sep 7 Update_index.lua
-rw-r--r--1 root 552 Sep 7 Update_non_index.lua

So the trick (if you want to call it that) is not to pass--test instructions through a single word, but to pass the full path of a LUA script.

The first of these is the old way (Sysbench 0.4.12 from the Epel Library):

Copy Code code as follows:

--TEST=OLTP--oltp-test-mode=complex

Here is the new Way (Sysbench 0.5):

Copy Code code as follows:

--test=/usr/share/doc/sysbench/tests/db/insert.lua

Here's where I run Haproxy on a 3-node PXC cluster, do a test type that's only inserted, and here's all the commands I pass to Sysbench:

Copy Code code as follows:

[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
$

The following is the contents of the Insert.lua script:

Copy Code code as follows:

[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

I prefer a place in sysbench 0.5 (over the LUA interface, yes!). ) is that it now brings a configuration item –report-interval option (I am usually set to =1) so that you can see the output information periodically while the script is running. Don't wait until the run is over and get feedback! Here's a test sample with Sysbench 0.5 that runs the insert data action through a local Hapoxy instance and writes data to three nodes on the same pxc (Percona Xtrdb Cluster) cluster, for example OpenStack trove does:

Copy Code code as follows:

[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
Intermediate results every 1 second (s)
Random number generator seed is 0 and would 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 also want you to notice the article from Nilnandan Joshi blog, Nilnandan Joshi is from the Percona support team, in which he describes a way to build sysbench 0.5 on Debian 7. Thank you very much for nil's contribution here!

I hope this article will help the group of comrades who want to upgrade to Sysbench 0.5, and can question (inquire) where the-TEST=OLTP has gone. I'd love to see your own list of sysbench users, and if anyone else has published their own LUA scripts for load tests!