Sunday, March 01, 2009

bencher - a benchmarking utility for MySQL Cluster

bencher is a test program that allows you to benchmark requests on MySQL Cluster. I have used this utility a lot of customers, because it lets me:
  • specify a simple query that I want to benchmark on the command line
  • implement more complex use cases.
  • implement NDBAPI requests
and I don't have to reinvent the wheel every time. It is all there: connectivity, multi-threading support, timers, and some basic statistics, and it compiles on most platforms. I just have to focus on the queries I want to optimize or benchmark.

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 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.

6 comments:

seo expert said...

cool post

ch said...

hi,johan ,i run make in my 64bit os to install bencher,but get the following error:
g++ -Wall -fno-strict-aliasing -g -O2 -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -DUNIV_LINUX -I/usr/include/mysql/storage/ndb -I/usr/include/mysql/storage/ndb/ndbapi -I/usr/include/mysql/storage/ndb/mgmapi -I/usr/include/mysql/ndb -I/usr/include/mysql/ndb/ndbapi -I/usr/include/mysql/ndb/mgmapi -I/usr/include/mysql -rdynamic -L/usr/lib64/mysql -lmysqlclient -lz -lrt -lpthread -lcrypt -lnsl -lm -lpthread -lmygcc -lmystrings -lmysys -lrt -lndbclient -o bencher bencher.o
bencher.o: In function `thread_runner_mysql(void*)':
/root/dbtool/bencher-0.16/src/bencher.cpp:431: undefined reference to `mysql_init'
/root/dbtool/bencher-0.16/src/bencher.cpp:434: undefined reference to `mysql_real_connect'
/root/dbtool/bencher-0.16/src/bencher.cpp:489: undefined reference to `mysql_real_query'
/root/dbtool/bencher-0.16/src/bencher.cpp:502: undefined reference to `mysql_free_result'
/root/dbtool/bencher-0.16/src/bencher.cpp:518: undefined reference to `mysql_next_result'
/root/dbtool/bencher-0.16/src/bencher.cpp:496: undefined reference to `mysql_store_result'
/root/dbtool/bencher-0.16/src/bencher.cpp:506: undefined reference to `mysql_field_count'
/root/dbtool/bencher-0.16/src/bencher.cpp:524: undefined reference to `mysql_errno'
/root/dbtool/bencher-0.16/src/bencher.cpp:529: undefined reference to `mysql_error'
/root/dbtool/bencher-0.16/src/bencher.cpp:594: undefined reference to `mysql_close'
/root/dbtool/bencher-0.16/src/bencher.cpp:444: undefined reference to `mysql_error'
collect2: ld returned 1 exit status

i have modified my Makefile
lmysqlclient_r => lmysqlclient
but no use
i have installed the NDB devel and NDB sharelib rpm ,can u figure out the problem?

ch said...

and here is config info

checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for gawk... (cached) gawk
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ANSI C... none needed
checking for style of include used by make... GNU
checking dependency style of gcc... gcc3
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking dependency style of g++... gcc3
checking how to run the C preprocessor... gcc -E
checking whether make sets $(MAKE)... (cached) yes
checking for egrep... grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for stdlib.h... (cached) yes
checking for string.h... (cached) yes
checking for unistd.h... (cached) yes
checking for sys/stat.h... (cached) yes
checking for atoi... yes
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for mysql_config executable... checking for mysql_config... mysql_config
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ANSI C... (cached) none needed
checking dependency style of gcc... (cached) gcc3
checking whether we are using the GNU C++ compiler... (cached) yes
checking whether g++ accepts -g... (cached) yes
checking dependency style of g++... (cached) gcc3
ok
checking for ndbapi headers... found them
configure: creating ./config.status
config.status: creating Makefile
config.status: creating src/Makefile
config.status: creating config.h
config.status: executing depfiles commands

ch said...

now i modified sharelib path
and get another error
g++ -Wall -fno-strict-aliasing -g -O2 -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -DUNIV_LINUX -I/usr/include/mysql/storage/ndb -I/usr/include/mysql/storage/ndb/ndbapi -I/usr/include/mysql/storage/ndb/mgmapi -I/usr/include/mysql/ndb -I/usr/include/mysql/ndb/ndbapi -I/usr/include/mysql/ndb/mgmapi -I/usr/include/mysql -rdynamic -L/usr/lib64 -lmysqlclient_r -lz -lrt -lpthread -lcrypt -lnsl -lm -lpthread -lmygcc -lmystrings -lmysys -lrt -lndbclient -o bencher bencher.o
/usr/bin/ld: cannot find -lz
collect2: ld returned 1 exit status
make[2]: *** [bencher] Error 1

ch said...

OK i solve the problem by cp the share lib from /usr/lib64 to /usr/lib64/mysql,thank

ch said...

hi, again me :)
i download the newer version of bencher,but i find a littler different with what the document says

in document test result is
Total throughput = 1346.08 qps
Average exec time per thread = 14.86 secs
but my result is
Total throughput = 44019.07 tps
Average exec time per thread = 2.30 secs

it benchmark result is tps or qps?
and also my bencher no --ndb-connectstring option ,how can i test my NDB??

best regards