Decide which tool you would like to use:
- MySQL benchmark suite
- Sysbench
- db_STRESS
- http://www.mysqlperformanceblog.com/2012/02/25/introducing-new-type-of-benchmark/
- Build your own
First download sysbench
Install :
- # tar -vxzf sysbench-0.4.12.tar.gz
- cd sysbench-0.4.12
- sysbench-0.4.12]# ./configure
- If you run into errors with libtool check this post out
- sysbench-0.4.12]# make
- sysbench-0.4.12]# make install
- http://sysbench.sourceforge.net/docs/
- The manual should be referenced for all guidelines.
# 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 &||
WWW::Mechanize::Firefox
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.
elinks http://www.google.com/search?ie=ISO-8859-1\&hl=en\&source=hp\&q=mysql
# ab -n 10 -c 2 http://www.google.com/search?ie=ISO-8859-1\&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.
