BENCHER - the benchmarking tool for MySQL Cluster

bencher is a test program that allows you to benchmark requests on MySQL Cluster. The simple use case is to specify the SQL query you want to benchmark, the number of threads, and how many times.
You can also customize this very easily (see the Documentation) to benchmark more elaborate SQL requsts, and NDBAPI requests.
bencher outputs per thread statistics and total throughput:
./src/bencher -s /tmp/mysql.sock.3306 -t 2 -l 10000 -q "select * from t1 limit 1"

------- Starting Benchmark ----------
Thread 1 - 638 qps (average qps measured after 5 secs)
Thread 0 - 631 qps (average qps measured after 5 secs)
Thread 1 - 680 qps (average qps measured after 10 secs)
Thread 0 - 679 qps (average qps measured after 10 secs)

------- Benchmark Finished ----------
Thread 0 - max: 83091 us, min 668 us, less than 5 ms: 9761 of 10000, avg: 1485 us, total time: 14949 ms, qps: 668.91
Thread 1 - max: 43743 us, min 578 us, less than 5 ms: 9770 of 10000, avg: 1475 us, total time: 14767 ms, qps: 677.16

Total throughput = 1346.08 qps
Average exec time per thread = 14.86 secs

You can also specify a "querytime-threshold", to see how many transaction have executed under a certain time (default is 5 ms). From the above you can see that for this particular query 9760/10000 requests finished within 5 ms.

Future improvements are amongst others to implement support for warmup periods, reading queries from a file, write statistics to a file etc.

Download - source dist only (latest first) - Community version (GPL)

Version 0.18- released 29th of Apr 2010 - download source dist - fixed finding NDBAPI headers issue.

Drop an email to support@severalnines.com if you have problems or suggestions how to improve this utility.

Installation

Pre-requisites

MySQL Cluster 6.2 or MySQL Cluster 6.3 installed and started

In order to make it simple it is also recommended to have:

ndb_mgmd running on localhost (where bencher is going to be installed)

data nodes can be somewhere else or also on localhost

mysql_config on the PATH

autoconf, automake, libtool (e.g apt-get install autoconf apt-get install automake apt-get install libtool

bencher-0.1 requires STL as well..this will probably be removed soon as it can cause problems on some platforms. Atleast on modern Linux distros it should not be a problem.

Compile bencher

Make sure you have the correct mysql_config on the path. If you have used the config tool (and the default install path):

Make sure you have: g++ and gcc installed (run those two commands from the command line). If they don't exists you have to install them!!

export PATH=/usr/local/mysql/mysql/bin:$PATH

or ..another popular PATH is

export PATH=/usr/local/mysql/bin:$PATH

Proceed with the following when you can execute the command mysql_config (then your PATH is good):

tar xvfz bencher-0.1.tgz

cd bencher-0.1

sh autogen.sh

./configure

make

sudo make install

export LD_LIBRARY_PATH=/usr/local/mysql/mysql/lib:/usr/local/mysql/mysql/lib/mysql

If you don't set the LD_LIBRARY_PATH to where libmysqlclient_r.so.16 is, then you get the following error message when starting bencher:

bencher: error while loading shared libraries: libmysqlclient_r.so.16: cannot open shared object file: No such file or directory

on Solaris (you need Sun studio) run the following configure:

CC=cc-5.0 CXX=CC ./configure

Running bencher

Running bencher is easy:

$ bencher -? will print out the arguments:
-c   --ndb-connectstring=connectstring (no default value)
-h   --mysqlhost=mysql hostname (default is 'localhost')
-u   --mysqluser=mysql username (default is 'root')
-p   --mysqlpassword=mysql password (default is "")
-s   --mysqlsocket=mysql socket (default is /tmp/mysql.sock)
-q   --mysqlquery=query (no default value)
-P   --mysqlport=mysql port (default is 3306)
-d   --database=databaseName (default is 'test')
-t   --threads=N (default is 1)
-l   --loops=N (default is 10000)
-b   --batch=N (default is 1)
-b   --runtime=N (specifies how many seconds the test should run. Default is 0 - disabled (uses --loops instead)
-b   --output=N (N=testname, give the name a test and GnuPlot files will be generated, see below for instructions)
-m   --ndbmulticonnect=N (default is 0)
-T   --querytime-threshold=N (default is 5 (ms))
-?   --help
Important!
If you specify --ndb-connecstring then will benchmark NDBAPI requests and not connect to the MySQL server.
If you leave out --ndb-connecstring bencher will benchmark SQL requests.

The example below runs the query "select * from t1 limit 1" in two threads, 10000 times in each thread, and connects to the mysql server on /tmp/mysql.sock:
./src/bencher -s /tmp/mysql.sock -t 2 -l 10000 -q "select * from t1 limit 1"

Documentation

Benching SQL requests

The following command starts bencher with 10 threads and each thread loops 10 times:
./bencher --mysqlhost=localhost -t 10

The following command starts bencher with 10 threads and each thread will have one Ndb_cluster_connection (this will require --threads=N free [mysqld] slots in config.ini :
./bencher --mysqlhost=localhost -t 10 --ndbmulticonnect

To start bencher to execute a particular query N times:
./bencher --mysqlhost=localhost -t 10 --mysqlquery="select * from t1 limit 1" --loops=10000

Benching custom SQL requests

You can also implement (using MySQL C API) more advanced type of requests. E.g, HLR or IMS type of requests requiring multiple statements. The you must not supply --mysqlquery. SQL requests should be implemented in the thread_runner_mysql function. Locate the section:
/**
* YOU SQL QUERY SHOULD BE COPIED INTO mysqlquery . E.g using sprintf:
* sprintf(mysqlquery, "INSERT INTO t1(a) VALUES (%d)", i);
*/
and put the code there!

Then you can start bencher to execute the request N times with N threads:
./bencher --mysqlhost=localhost -t 10 --loops=10000

Benching NDBAPI requests

In order to benchmark NDBAPI requests you need to know the NDBAPI. The NDBAPI request should be implemented in the thread_runner_ndb function. Locate the section:
/**
* Your NDBAPI code goes here!
*/
and put the code there!

The following command starts bencher with 10 threads and each thread loops 10 times:

./bencher --ndb-connectstring=localhost --threads=10 --loops=10

The following command starts bencher with 10 threads and each thread will have one Ndb_cluster_connection (this will require --threads=N free [mysqld] slots in config.ini :
./bencher --ndb-connectstring=localhost -t 10 --ndbmulticonnect

GnuPlot

If you run bencher with -o (--output) and specify a test name, you will get data files and a GNU Plot script written to /tmp:
./bencher --mysqlhost=localhost -t 10 --mysqlquery="select * from t1 limit 1" --loops=10000 -o mytest
Then you do:
gnuplot gnuplot_t1_mytest.script > mytest.png
And you will get a .png containing two graphs for your test run, one for response times and one for throughput.