Sysbench 0.5 + Ubuntu 14.04 (Trusty) + Percona Server or XtraDB Cluster

I need to perform benchmarks on Percona XtraDB Cluster and Percona Server directly from my Ubuntu 14.04 box. Sysbench 0.5 is the latest stable to date and it supports benchmarking Galera cluster. If you are running on Sysbench 0.4, you would most likely encounter one of following errors if you are running it against a Galera cluster with high value of threads:

ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry '2199902' for key 'PRIMARY'

This post presumes that you have already installed standard Percona related packages including the apt-get repository to run a MySQL server (as instructed on Percona’s  documentation page).

 

Installing Sysbench 0.5

1. Install compiler tools and required packages:

$ sudo apt-get install -y gcc autoconf automake make libtool libssl-dev libcrypto++9

2. Install mysql_config (available from Percona repository):

$ sudo apt-get install -y libperconaserverclient18-dev

3. Get Sysbench from Launchpad:

$ bzr branch lp:sysbench

4. Compile and install sysbench:

$ cd sysbench
$ ./autogen.sh
$ ./configure --prefix=/usr --mandir=/usr/share/man
$ make
$ sudo make install

5. Create LUA template directory:

$ sudo mkdir /usr/share/sysbench/tests/db -p
$ sudo cp sysbench/tests/db/* /usr/share/sysbench/tests/db

6. Verify if sysbench is running in the correct version:

$ sysbench --version
sysbench 0.5

Running Sysbench

1. Before starting the benchmark, we need to prepare the test data. Following command will generate a table consists of 20 million rows on a Galera Cluster through HAproxy which running on 127.0.0.1 port 33306:

$ sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=20000000 \
--mysql-host=127.0.0.1 \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/db/oltp.lua \
prepare

Or we can generate 8 tables consists of 10M rows in parallel with 8 threads (total of 80M rows of data set):

$ sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=10000000 \
--oltp-tables-count=8 \
--num-threads=8 \
--mysql-host=127.0.0.1 \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/db/parallel_prepare.lua \
run

2. Start the benchmark against Galera cluster:

$ sysbench \
--db-driver=mysql \
--num-threads=8 \
--max-requests=5000000 \
--oltp-table-size=20000000 \
--oltp-test-mode=complex \
--test=/usr/share/sysbench/tests/db/oltp.lua \
--mysql-host=127.0.0.1 \
--mysql-port=33306 \
--mysql-user=sbtest \
--mysql-password=password \
run

You can perform other tests based on the LUA templates exist under /usr/share/sysbench/tests/db. Just change the –test parameter to the fullpath of the template file and you are good to go.

Details on this can be found at http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua

 

2 thoughts on “Sysbench 0.5 + Ubuntu 14.04 (Trusty) + Percona Server or XtraDB Cluster

  1. hi!

    just silly question? Why 20M of rows?! Im making test in virtual environment with vagrant and virtual box and I think is too agressive for me.

    What happen if I run the test with less rows? Let’s say …1 million? XD

    thanks!

    Reply

    1. Hi Ruben,

      It doesn’t really matter. I was using 20 M on benchmarking to basically stress test my server to the limit (it was a c4.2xlarge EC2 instance). With Vagrant, I usually run against 500K rows in total.

      Reply

Leave a Reply

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