Friday, May 3, 2013

Benchmarking:: MySQL, CPU, File I/O, Memory :: Sysbench & Apache Benchmark

Decide which tool you would like to use:
I will use Sysbench for this example:
First download sysbench
Install :
SysBench manual
Review your options :
# sysbench  --test=oltp help
Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test
  oltp - OLTP test

Commands: prepare run cleanup help version

Some examples below:

fileio - File I/O test
# sysbench --test=fileio help
fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,fastmmap,slowmmap} [sync]
  --file-extra-flags=STRING     additional flags to use on opening files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all=[on|off]     do fsync() after each write operation [off]
  --file-fsync-end=[on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

  • # sysbench --test=fileio prepare
  • # sysbench --test=fileio --file-test-mode=rndwr run

cpu - CPU performance test
# sysbench --test=cpu help
cpu options:
  --cpu-max-prime=N      upper limit for primes generator [10000]

  • # sysbench --test=cpu --num-threads=25  run

memory - Memory functions speed test
# sysbench --test=memory help 
memory options:
  --memory-block-size=SIZE    size of memory block for test [1K]
  --memory-total-size=SIZE    total size of data to transfer [100G]
  --memory-scope=STRING       memory access scope {global,local} [global]
  --memory-hugetlb=[on|off]   allocate memory from HugeTLB pool [off]
  --memory-oper=STRING        type of memory operations {read, write, none} [write]
  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]

  • # sysbench --test=memory <memory options> run

threads - Threads subsystem performance test
# sysbench --test=threads help  
threads options:
  --thread-yields=N      number of yields to do per request [1000]
  --thread-locks=N       number of locks per thread [8]

  • # # sysbench --test=threads --num-threads=64 --test=threads --thread-yields=100 --thread-locks=6 run

mutex - Mutex performance test
# sysbench --test=mutex help   
mutex options:
  --mutex-num=N        total size of mutex array [4096]
  --mutex-locks=N      number of mutex locks to do per thread [50000]
  --mutex-loops=N      number of empty loops to do inside mutex lock [10000]

  • # sysbench --test=mutex  --num-threads=64  run

oltp - OLTP test
# sysbench --test=oltp help
oltp options:
  --oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp} [complex]
  --oltp-reconnect-mode=STRING    reconnect mode {session,transaction,query,random} [session]
  --oltp-sp-name=STRING           name of store procedure to call in SP test mode []
  --oltp-read-only=[on|off]       generate only 'read' queries (do not modify database) [off]
  --oltp-skip-trx=[on|off]        skip BEGIN/COMMIT statements [off]
  --oltp-range-size=N             range size for range queries [100]
  --oltp-point-selects=N          number of point selects [10]
  --oltp-simple-ranges=N          number of simple ranges [1]
  --oltp-sum-ranges=N             number of sum ranges [1]
  --oltp-order-ranges=N           number of ordered ranges [1]
  --oltp-distinct-ranges=N        number of distinct ranges [1]
  --oltp-index-updates=N          number of index update [1]
  --oltp-non-index-updates=N      number of non-index updates [1]
  --oltp-nontrx-mode=STRING       mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
  --oltp-auto-inc=[on|off]        whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  --oltp-connect-delay=N          time in microseconds to sleep after connection to database [10000]
  --oltp-user-delay-min=N         minimum time in microseconds to sleep after each request [0]
  --oltp-user-delay-max=N         maximum time in microseconds to sleep after each request [0]
  --oltp-table-name=STRING        name of test table [sbtest]
  --oltp-table-size=N             number of records in test table [10000]
  --oltp-dist-type=STRING         random numbers distribution {uniform,gaussian,special} [special]
  --oltp-dist-iter=N              number of iterations used for numbers generation [12]
  --oltp-dist-pct=N               percentage of values to be treated as 'special' (for special distribution) [1]
  --oltp-dist-res=N               percentage of 'special' values to use (for special distribution) [75]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]

Compiled-in database drivers:
  mysql - MySQL driver

mysql options:
  --mysql-host=[LIST,...]       MySQL server host [localhost]
  --mysql-port=N                MySQL server port [3306]
  --mysql-socket=STRING         MySQL socket
  --mysql-user=STRING           MySQL user [sbtest]
  --mysql-password=STRING       MySQL password []
  --mysql-db=STRING             MySQL database name [sbtest]
  --mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
  --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]
  --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]
  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]
  --mysql-create-options=STRING additional options passed to CREATE TABLE []

sysbench expects to find the sbtest database so make sure and create that first or define what database you prefer.
  • sysbench --test=oltp help | grep sbtest
      --oltp-table-name=STRING        name of test table [sbtest]
      --mysql-user=STRING           MySQL user [sbtest]
      --mysql-db=STRING             MySQL database name [sbtest]

# sysbench  --test=oltp  --mysql-host=localhost --mysql-user=root --mysql-password=<Password here> --mysql-table-engine=innodb prepare

# sysbench  --test=oltp  --mysql-host=localhost --mysql-user=root --mysql-password=<Password here> --mysql-table-engine=innodb --num-threads=25  run

Then keep an eye on your results as well as database as the tests run.

Apache Benchmark  &||

 The key take away here is to use these tools to benchmark your application and process not just a single factor of the architecture.

For example, if you expect to have 20,000 users an hour filling out your web form then you should benchmark your application to handle that.  You can use the tools above to mimic the posts, for just one example by passing tokens, and then test the code that is in place on your application.  A test like this then will test the code base and with the proper logging in place (which you could also turn on by a token) you can test how well the developers, dba and system is built.

Very simple example of the concept :

If you can open the url with elinks you can benchmark it.


# ab -n 10 -c 2\&hl=en\&source=hp\&q=mysql

Of course you would execute this against your system at a much higher level to test how your application responds.