EmergencyEMERGENCY? Get 24/7 Help Now!

Using sysbench 0.5 for performing MySQL benchmarks


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

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

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!

Share Button
PREVIOUS POST
NEXT POST


Michael Coburn

Michael joined Percona as a Consultant in 2012 after having worked with high volume stock photography websites and email service provider platforms. With a foundation in Systems Administration, Michael enjoys working with SAN technologies and high availability solutions. A Canadian, Michael currently lives in Costa Rica with his wife, two children, and two dogs.



Tags:

, , , , ,

Categories:
Benchmarks, Insight for DBAs, MySQL, OpenStack, Percona MySQL Consulting, Percona MySQL Support


Comments
  • 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.

    Reply

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

    Reply

  • Michael Coburn Post author

    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

    Reply

  • Michael Coburn Post author

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

    Reply

  • Credit for catch goes to AlexeyK :)

    Reply

  • 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

    Reply

  • 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

    Reply

  • Michael Coburn Post author

    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?

    Reply

Leave a Reply

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.