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

6 comments:

  1. AnonymousFriday, May 08, 2009 1:48:00 PM

    cool post

    ReplyDelete
  2. chThursday, October 15, 2009 6:19:00 AM

    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?

    ReplyDelete
  3. chThursday, October 15, 2009 6:23:00 AM

    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

    ReplyDelete
  4. chThursday, October 15, 2009 7:05:00 AM

    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

    ReplyDelete
  5. chThursday, October 15, 2009 7:17:00 AM

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

    ReplyDelete
  6. chThursday, October 15, 2009 8:16:00 AM

    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

    ReplyDelete