Installing and using sysbench on Joyent SmartOS

If you read Percona's MySQL Performance blog (and if you run MySQL in production, you should!), then you know that one of their favorite load testing tools is sysbench. As it turns out, it's not trivial to install this tool, especially when you have to install from source, for example on Solaris-based systems such as the Joyent SmartOS machines. Here's what I did to get it to work.

Download source distribution for sysbench

I downloaded the latest version of sysbench (0.4.12) from the Sourceforge download page for the project.

Compile and install sysbench

If you launch a SmartOS machine in the Joyent cloud, you'll find out very quickly that it's lacking tools that you come to take for granted when dealing with Ubuntu or Fedora. In this case, you need to install compilers and linkers such as gcc and gmake. Fortunately, SmartOS has its own package installer called pkgin, so this is not too bad.

To see what packages are available if you know the tool you want to install, you can run the 'pkgin available' command and grep for the tool name:

# pkgin available | grep gcc
gcc-compiler-4.5.2 GNU Compiler Collection 4.5
gcc-runtime-4.5.2 GNU Compiler Collection 4.5 Runtime libs
gcc-tools-0 Subset of binutils needed for GCC

To install gcc, I ran:

# pkgin install gcc-compiler-4.5.2 gcc-runtime-4.5.2 gcc-tools-0

Similarly, I installed gmake and automake:

# pkgin install gmake automake

When I ran ./configure for sysbench, I got hit with errors of the form

../libtool: line 838: X--tag=CC: command not found 

../libtool: line 871: libtool: ignoring unknown tag : command not found 

../libtool: line 838: X--mode=link: command not found

etc

A quick Google search revealed this life-saving blog post which made things work. So first of all I ran ./autogen.sh, got hit with more errors, and edited configure.ac per the blog post -- basically I commented out this line in configure.ac:

#AC_PROG_LIBTOOL

And added this line:

AC_PROG_RANLIB

Now running ./autogen.sh produces no errors.

At this point I was ready to run ./configure again. However, if you want to run sysbench against a MySQL server, you need to specify MySQL header and library files when you run ./configure. This also means that you need to install some MySQL client package in order to satisfy those dependencies. If you install sysbench on a Joyent Percona SmartMachines, those packages are already there. On a plain SmartOS machine, you need to run:

# pkgin install mysql-client-5.0.92

At this point, on a Percona SmartMachine you have MySQL header files in /opt/local/include/mysql and MySQL libraries in /local/lib. On a vanilla SmartOS machine, the MySQL header files are in /opt/local/include/mysql and the MySQL libraries are in /opt/local/lib/mysql. So the configure command line will be as follows.

On a Percona SmartMachine:

# ./configure --with-mysql-includes=/opt/local/include/mysql --with-mysql-libs=/local/lib/

On a vanilla SmartOS machine where you installed mysql-client:

# ./configure --with-mysql-includes=/opt/local/include/mysql --with-mysql-libs=/opt/local/lib/mysql

Now you're ready to run the usual commands:

# make; make install

If everything goes well, the sysbench binary will be in /usr/local/bin. That directory is not in the default PATH on SmartOS, so you need to add it to your PATH environment variable in .bashrc or .bash_profile.

On a vanilla SmartOS machine, I also had issues when trying to run the sysbench tool -- I got an error message of the type 'ld.so.1: sysbench: fatal: libmysqlclient.so.18: open failed: No such file or directory'

To get past this, I had to do two things: 

1) Add "export LD_LIBRARY_PATH=/opt/local/lib/mysql" to .bashrc

2) symlink from the existing shared library file libmysqlclient.so.15 to libmysqlclient.so.18:

# ln -s /opt/local/lib/mysql/libmysqlclient.so.15 /opt/local/lib/mysql/libmysqlclient.so.18

If you've followed along this far, you reward is that you'll finally be able to run syblog post on 'DROP TABLE and stalls'sbench with no errors.

Running sysbench

It is recommended that you run sysbench from a remote host against your MySQL server, so that no resources on the server get taken by sysbench itself. I used two phases in my sysbench tests: a prepare phase, where the table I tested against was created by sysbench, and the proper load testing phase. For the prepare phase, I ran:

# sysbench --test=oltp --mysql-host=remotehost --mysql-user=mydbadmin --mysql-db=mydb --mysql-password=mypass --mysql-table-engine=innodb --oltp-table-size=1000000 --oltp-table-name=millionRowsA prepare

where

• remotehost is the host running MySQL server
• mydb is a database I created on the MySQL server
• mydbadmin/mypass are the user name and password for a user which I granted all permissions for on the mydb database (with a MySQL statement like "GRANT ALL ON mydb.* TO 'mydbadmin'@'remoteip' IDENTIFIED BY 'mypass'" where remoteip is the IP address of the host I was running sysbench from)

This command will create a table called millionRowsA with 1 million rows, using InnoDB as the storage engine.

To perform a load test against this table, I ran:

# sysbench --test=oltp --mysql-host=remotehost --mysql-user=mydbadmin --mysql-db=mydb --mysql-password=mypass --mysql-table-engine=innodb --oltp-table-size=1000000 --oltp-table-name=millionRowsA --num-threads=16 run

This will run an OLTP-type test using 16 threads. Per the sysbench documentation, an OLTP-type test will perform advanced transactional operations against the test database, thus mimicking real-life scenarios to the best of its ability.

I would like to stress one thing at this point: I am not a big believer in benchmarks. Most of the time I find that they do not even remotely manage to model real-life scenarios that you see in production. In fact, there is nothing like production traffic to stress-test a component of your infrastructure, which is why techniques such as dark launching are so important. But benchmarks do give you at least a starting point for a conversation with your peers or your vendors about specific issues you might find. However, it's important to consider them starting points and not end points. Ovais Tariq from Percona agrees with me in a recent blog post on 'DROP TABLE and stalls':

I would also like to point out one thing about benchmarks – we have been always advising people to look beyond average performance numbers because they almost never really matter in production, it is not a question if average performance is bad but what stalls and pileups you have.

So far, my initial runs of sysbench against a Percona SmartMachine with 16 GB of RAM and against an EC2 m1.xlarge instance running Percona (with RAID0 across ephemeral disks, no EBS) show pretty similar results. No huge advantage either way. (I tried 16 and 32 threads against 1 million row tables and 10 million row tables). One advantage of EC2 is that it's a known ecosystem and I can run Ubuntu. I am working with Joyent on maybe further tuning the Percona SmartMachine to squeeze more performance out of it.