Just a quick how-to, to download, compile and use Sysbench on your RHEL/OL Linux box to test MySQL Server (note: compiling and using Sysbench is no mistery, the README file has all the instructions clearly explained, but I write this little guide to setup and use this tool in minutes).

Download and compile

The repository is hosted at: https://github.com/akopytov/sysbench. You can choose to download the latest master branch as usual or the latest stable. I have picked the latest stable. In order to compile and use certain MySQL library version, make sure mysql_config (used by sysbench auto configure to determine what libraries must be used) and a whole MySQL distribution is installed and/ or in the PATH.

If you have MySQL headers and libraries in non-standard locations (and no mysql_config can be found in the PATH), you can specify them explicitly with --with-mysql-includes and --with-mysql-libs options to ./configure.

Let’s go ahead now with the installation.

tar xf sysbench-1.0.20.tar.gz
cd sysbench-1.0.20
./autogen.sh
./configure
make -j

Now you can install it:

make install

or just use the binary:

cd src/
./sysbench --version
sysbench 1.0.20

Install from repository

If you can’t wait to use sysbench, you can obviously install it from repository for Red Hat/ Oracle Linux:

sudo yum install sysbench

Troubleshooting

If you hit a failure like:

./sysbench 
./sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

That is because sysbench can’t resolve the library, as you can also see from:

ldd sysbench
	linux-vdso.so.1 =>  (0x00007ffc59b91000)
	libmysqlclient.so.20 => not found
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f73eabb3000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f73ea9ab000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f73ea7a7000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007f73ea5a5000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f73ea2a3000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f73ea08d000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f73e9cbf000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f73eadcf000)

You can choose to download and install MySQL binary release (tar-compressed), and set it like:

export LD_LIBRARY_PATH=<MYSQL_BINARY_PACKAGE>/lib/

And verify it’s correctly resolved:

ldd sysbench
	linux-vdso.so.1 =>  (0x00007ffe9c379000)
	libmysqlclient.so.20 => /export/home/mortensi/dbdeployer-binaries/5.7.33/lib/libmysqlclient.so.20 (0x00007fd510bdb000)

Or you can install the relevant RPM mysql-community-libs-<VERSION>.rpm including the library from the download page.

If you are linking an older MySQL version, you may hit error:

error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: 
/usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: 
No such file or directory

In such a case, please refer to this post to understand what you should do.

Quickstart

Sysbench comes with pre-compiled tests, in a single command you can test different things, set TYPE to the desired, among the following:

• fileio
• cpu
• memory
• threads
• mutex

sysbench <TYPE> --threads=2 --report-interval=3 --histogram --time=50 --db-driver=mysql --mysql-host=<HOST> --mysql-db=<SCHEMA> --mysql-user=<USER> --mysql-password=<PASSWORD> run

Benchmarks

Sysbench comes also with a set of predefined benchmarks written in LUA. Here’s basic commands to send some load to your MySQL instance. You will have to prepare, run and clean up your test.

Prepare

sysbench oltp_read_write --table-size=1000000 --db-driver=mysql --mysql-host=<HOST> --mysql-db=<SCHEMA> --mysql-user=<USER> --mysql-password=<PASSWORD> prepare

This will create data under the chosen schema:

sysbench 1.1.0-bbee5d5 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

Run

sysbench oltp_read_write --threads=2 --report-interval=3 --histogram --time=50 --table-size=1000000 --db-driver=mysql --mysql-host=<HOST> --mysql-db=<SCHEMA> --mysql-user=<USER> --mysql-password=<PASSWORD> run

And see results returned after a while (besides a report every 3 seconds and histogram, requested above):

SQL statistics:
    queries performed:
        read:                            97552
        write:                           27872
        other:                           13936
        total:                           139360
    transactions:                        6968   (139.33 per sec.)
    queries:                             139360 (2786.57 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Clean up

sysbench oltp_read_write --db-driver=mysql --mysql-host=<HOST> --mysql-db=<SCHEMA> --mysql-user=<USER> --mysql-password=<PASSWORD> cleanup

Which will drop the data inserted in the preparation phase.

sysbench 1.1.0-bbee5d5 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'…

Usage

Using sysbench, you can:

• run for a fixed time with –time=<SECONDS>
• set no limits on executed queries with –events=0
• disable prepared statements with –db-ps-mode=disable
• get points for plotting using –report-interval=<SECONDS>
• get an histogram with –histogram
• use a prepared OLTP benchmark and get help: sysbench <BENCHMARK> help

About the options for a concrete benchmark, e.g. for oltp_read_write:

sysbench oltp_read_write help
sysbench 1.1.0-bbee5d5 (using bundled LuaJIT 2.1.0-beta3)

oltp_read_write options:
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --create_table_options=STRING Extra CREATE TABLE options []
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
[…]

Finally, make sure you check the help to understand all the options and tests available for this powerful benchmarking tool!

sysbench --help