Using sysbench 0.5 for performing MySQL benchmarks

PREVIOUS POST
NEXT POST

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!

PREVIOUS POST
NEXT POST

Comments

  1. Lucian Daia says

    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.

  2. Michael Coburn says

    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

  3. Michael Coburn says

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

Leave a Reply

Your email address will not be published. Required fields are marked *