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

Update: sysbench 0.5 is available from the Percona repositories:

http://repo.percona.com/release/6/RPMS/x86_64/

http://repo.percona.com/apt/pool/main/s/sysbench/

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):

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):

This is the new way (sysbench 0.5):

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:

And here’s what the insert.lua script looks like:

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:

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!

12 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Lucian Daia

Thanks, this was of great help! I’ve just installed sysbench on a new build of Ubuntu 14.04 and was wondering where the OLTP test went.

Do you know if the prepare part for the test can be run in parallel? There’s a test there called “parallel_prepare.lua”, but when I run ‘prepare’ on it it just uses one thread to insert data in the test tables.

0
Roel Van de Paar

Typo: –mysql-table-type=innodb should be –mysql-table-engine=innodb

0
Michael Coburn

Good catch @Roel, I’ve updated the post.

I poked a bit and it seems that the good news is the default if not specified / option used incorrectly that the table will default to InnoDB. I did a test with default_storage_engine=MyISAM and:
1. no option specified -> table becomes InnoDB
2. option specified as –mysql-table-type=myisam -> table becomes InnoDB
3. option specified as –mysql-table-engine=myisam -> table becomes MyISAM

[michael@centos7 rsandbox_5_6_21]$ ./m sbtest -e “drop database sbtest;create database sbtest;show global variables like ‘default_storage_engine'”
+————————+——–+
| Variable_name | Value |
+————————+——–+
| default_storage_engine | MyISAM |
+————————+——–+
[michael@centos7 rsandbox_5_6_21]$ sysbench –mysql-socket=/tmp/mysql_sandbox20886.sock –mysql-user=msandbox –mysql-password=msandbox –mysql-db=sbtest –test=/home/michael/sysbench/sysbench/tests/db/select.lua prepare
sysbench 0.5: multi-threaded system evaluation benchmark

Creating table ‘sbtest1’…
Inserting 10000 records into ‘sbtest1’
[michael@centos7 rsandbox_5_6_21]$ ./m sbtest -e “show create table sbtest1\G”
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE sbtest1 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT ‘0’,
c char(120) NOT NULL DEFAULT ”,
pad char(60) NOT NULL DEFAULT ”,
PRIMARY KEY (id),
KEY k_1 (k)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
[michael@centos7 rsandbox_5_6_21]$ ./m sbtest -e “drop database sbtest;create database sbtest;show global variables like ‘default_storage_engine'”
+————————+——–+
| Variable_name | Value |
+————————+——–+
| default_storage_engine | MyISAM |
+————————+——–+
[michael@centos7 rsandbox_5_6_21]$ sysbench –mysql-socket=/tmp/mysql_sandbox20886.sock –mysql-user=msandbox –mysql-password=msandbox –mysql-db=sbtest –mysql-table-type=myisam –test=/home/michael/sysbench/sysbench/tests/db/select.lua prepare
sysbench 0.5: multi-threaded system evaluation benchmark

Creating table ‘sbtest1’…
Inserting 10000 records into ‘sbtest1’
[michael@centos7 rsandbox_5_6_21]$ ./m sbtest -e “show create table sbtest1\G” *************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE sbtest1 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT ‘0’,
c char(120) NOT NULL DEFAULT ”,
pad char(60) NOT NULL DEFAULT ”,
PRIMARY KEY (id),
KEY k_1 (k)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
[michael@centos7 rsandbox_5_6_21]$ ./m sbtest -e “drop database sbtest;create database sbtest;show global variables like ‘default_storage_engine'”
+————————+——–+
| Variable_name | Value |
+————————+——–+
| default_storage_engine | MyISAM |
+————————+——–+
[michael@centos7 rsandbox_5_6_21]$ sysbench –mysql-socket=/tmp/mysql_sandbox20886.sock –mysql-user=msandbox –mysql-password=msandbox –mysql-db=sbtest –mysql-table-engine=myisam –test=/home/michael/sysbench/sysbench/tests/db/select.lua prepare
sysbench 0.5: multi-threaded system evaluation benchmark

Creating table ‘sbtest1’…
Inserting 10000 records into ‘sbtest1’
[michael@centos7 rsandbox_5_6_21]$ ./m sbtest -e “show create table sbtest1\G” *************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE sbtest1 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT ‘0’,
c char(120) NOT NULL DEFAULT ”,
pad char(60) NOT NULL DEFAULT ”,
PRIMARY KEY (id),
KEY k_1 (k)
) ENGINE=MyISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000

0
Michael Coburn

Hi @Lucian, I think the purpose of parallel_prepare.lua is about preparing statements, and not about faster loading of the database with test data. Not much that I’m aware of exists to make the loading go faster from within sysbench.. now if you had that test data in a CSV you could load quickest with LOAD DATA INFILE or with pt-fifo.. but that’s a separate blog post idea 🙂

0
Roel Van de Paar

Credit for catch goes to AlexeyK 🙂

0
Matthew Boehm

The sys_haproxy.sh script above is missing newline escapes. If you copy/paste in to your own file, it won’t work. You need to add escapes to each end of line as such:

#!/bin/bash
sysbench \
–test=/usr/share/doc/sysbench/tests/db/insert.lua \
–mysql-host=10.0.1.60 \
–mysql-port=3305 \
…etc

0
Narendra

Hi All,
While i am doing benchmarking on mysql .for the threads above than 1200
i am getting the following error. will you guys help with this

time sysbench –test=/usr/bin/sysbench/sysbench/tests/db/oltp.lua –oltp-table-size=100000000 –mysql-host=host adrees –mysql-db=test –mysql-user=sysbench –max-time=60 –oltp-test-mode=simple –max-requests=0 –num-threads=1500 run
sysbench 0.5: multi-threaded system evaluation benchmark

after running this command ,following error i am facing.

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1500
Random number generator seed is 0 and will be ignored

Threads started!

FATAL: unable to connect to MySQL server, aborting…
(last message repeated 1 times)
FATAL: error 2005: Unknown MySQL server host ‘baseline-mysql.cm8ulxn1bw7j.us-east-1.rds.amazonaws.com’ (0)
PANIC: unprotected error in call to Lua API (Failed to connect to the database)
PANIC: unprotected error in call to Lua API (Failed to connect to the database)

Thanks
Narendra

0
Michael Coburn

Hi @Narenda,

Check the MySQL error log for evidence of a MySQL crash. Also have you been able to run a single threaded test successfully, if not have you verified there are no firewall rules blocking access between sysbench host and mysqld?

0
Sveta Smirnova (@svetsmirnova)

Hi Michael,

looks like URL of blog post with Fedora package changed. Now it is located at http://lefred.be/content/154/

0
Michael Coburn

Thanks Sveta, good catch! I also updated the post with links to the Percona Repositories for RPM and deb builds which ought to make installation a breeze 🙂

0
Anony

Hi Guys,
I hope someone can help me on this forum. I used sysbench for MySQL performance analysis with increasing number of threads. from thread 1 to 12 i got different increasing values but after that like from 12 to 100 threads am getting almost the same values. Please someone can tell me why am getting almost same values after some specific number of threads.
Thank you in advance

0
Ala' Mohsen

hi all , i am trying to use sysbench to evaluate the perofrmance of mysql innodb cluster , but i faced issue when i run same test with same variables i have different values noting that the test is run 100 times and mean is taken to have more accurate value , but each time the mean of transaction per sec has different value and with big difference . using below command .
sysbench –mysql-host=IP –mysql-port=6446 –db-driver=mysql –mysql-user=root –mysql-password=DbaPas4Mysql#1 –mysql-db=test_cluster –threads=10 –events=100000 –time=60 /usr/share/sysbench/bulk_insert.lua run

0