MySQL基准测试工具sysbench使用

加油奋斗吧 2016-02-01

对于innodb引擎的数据库应用来说,用户可能更关心磁盘和oltp的性能,因此主要测试fileio和oltp这两个项目

测试fileio
[root@iZ257l556beZ ~]# sysbench --test=fileio help
sysbench 0.4.12:  multi-threaded system evaluation benchmark


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-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --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]


其中测试模式里分为 连续/顺序写(seqwr)、连续改写(seqrewr)、连续读(seqrd)、随机读(rndrd)、随机写(rndwr)、随机读写(rndrw) 六种 。
以man手册里的测试示例为例,一次测试需要三步,如:

  1. $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw prepare --准备
  2. $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run --测试
  3. $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw cleanup --清除test文件


执行第一步生成test文件
sysbench --num-threads=16--test=fileio --file-total-size=3G--file-test-mode=rndrw prepare


执行第二步操作测试的结果为:

  1. $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run
  2. sysbench 0.4.12: multi-threaded system evaluation benchmark
  3. Running the test with following options:
  4. Number of threads: 16
  5. Extra file open flags: 0
  6. 128 files, 24Mb each
  7. 3Gb total file size
  8. Block size 16Kb
  9. Number of random requests for random IO: 10000    --可通过max-requests参数设置,最大请求数
  10. Read/Write ratio for combined random IO test: 1.50
  11. Periodic FSYNC enabled, calling fsync() each 100 requests.
  12. Calling fsync() at the end of test, Enabled.
  13. Using synchronous I/O mode
  14. Doing random r/w test
  15. Threads
  16. Done.
  17. Operations performed: 6000 Read, 4002 Write, 12800 Other = 22802 Total
  18. Read 93.75Mb Written 62.531Mb Total transferred 156.28Mb (2.8256Mb/sec)  ---随机写性能
  19.   180.84 Requests/sec executed   --随机写的iops
  20. Test execution summary:
  21.     total time: 55.3085s
  22.     total number of events: 10002
  23.     total time taken by event execution: 306.1095
  24.     per-request statistics:
  25.          min: 0.00ms
  26.          avg: 30.60ms
  27.          max: 508.92ms
  28.          approx. 95 percentile: 162.07ms
  29. Threads fairness:
  30.     events (avg/stddev): 625.1250/66.54
  31.     execution time (avg/stddev): 19.1318/1.07

注:实际测试中最好几种模式的都逐一进行测试比对 。

 

测试基准报告(与上面无关)

线程数测试模式最大请求数文件大小传输速度总执行时间 
最小最大平均
16rndrw2000010G2.8256Mb/sec200.3292s0.00ms684.51ms55.36ms
16rndwr2000010G2.2149Mb/sec352.7680s0.00ms684.52ms55.37ms
16seqwr2000010G2.3958Mb/sec200.3293s0.00ms684.53ms55.38ms
16seqrd2000010G2.1065Mb/sec125.8323s0.00ms684.54ms55.39ms

备注:IO测试中,最好让线程、最大请求数、文件大小三个值的固定为合适的值,通过改变不同的测试模式进行性能比对 。



测试数据库的OLTP性能

  1. [root@iZ257l556beZ soft]# sysbench --test=oltp help
  2. sysbench 0.4.12: multi-threaded system evaluation benchmark
  3. oltp options:
  4.   --oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
  5.   --oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]
  6.   --oltp-sp-name=STRING name of store procedure to call in SP test mode []
  7.   --oltp-read-only=[on|off] generate only 'read' queries (do not modify database) [off]
  8.   --oltp-skip-trx=[on|off] skip BEGIN/COMMIT statements [off]
  9.   --oltp-range-size=N range size for range queries [100]
  10.   --oltp-point-selects=N number of point selects [10]
  11.   --oltp-simple-ranges=N number of simple ranges [1]
  12.   --oltp-sum-ranges=N number of sum ranges [1]
  13.   --oltp-order-ranges=N number of ordered ranges [1]
  14.   --oltp-distinct-ranges=N number of distinct ranges [1]
  15.   --oltp-index-updates=N number of index update [1]
  16.   --oltp-non-index-updates=N number of non-index updates [1]
  17.   --oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
  18.   --oltp-auto-inc=[on|off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  19.   --oltp-connect-delay=N time in microseconds to sleep after connection to database [10000]
  20.   --oltp-user-delay-min=N minimum time in microseconds to sleep after each request [0]
  21.   --oltp-user-delay-max=N maximum time in microseconds to sleep after each request [0]
  22.   --oltp-table-name=STRING name of test table [sbtest]
  23.   --oltp-table-size=N number of records in test table [10000]
  24.   --oltp-dist-type=STRING random numbers distribution {uniform,gaussian,special} [special]
  25.   --oltp-dist-iter=N number of iterations used for numbers generation [12]
  26.   --oltp-dist-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
  27.   --oltp-dist-res=N percentage of 'special' values to use (for special distribution) [75]
  28. General database options:
  29.   --db-driver=STRING specifies database driver to use ('help' to get list of available drivers)
  30.   --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  31. Compiled-in database drivers:
  32.   mysql - MySQL driver
  33.   pgsql - PostgreSQL driver
  34. mysql options:
  35.   --mysql-host=[LIST,...] MySQL server host [localhost]
  36.   --mysql-port=N MySQL server port [3306]
  37.   --mysql-socket=STRING MySQL socket
  38.   --mysql-user=STRING MySQL user [sbtest]
  39.   --mysql-password=STRING MySQL password []
  40.   --mysql-db=STRING MySQL database name [sbtest]
  41.   --mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
  42.   --mysql-engine-trx=STRING whether storage engine used is transactional or not {yes,no,auto} [auto]
  43.   --mysql-ssl=[on|off] use SSL connections, if available in the client library [off]
  44.   --myisam-max-rows=N max-rows parameter for MyISAM tables [1000000]
  45.   --mysql-create-options=STRING additional options passed to CREATE TABLE []
  46. pgsql options:
  47.   --pgsql-host=STRING PostgreSQL server host [localhost]
  48.   --pgsql-port=N PostgreSQL server port [5432]
  49.   --pgsql-user=STRING PostgreSQL user [sbtest]
  50.   --pgsql-password=STRING PostgreSQL password []
  51.   --pgsql-db=STRING PostgreSQL database name [sbtest]


创建模拟数据

  1. [root@iZ257l556beZ ~]# sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-socket=/home/data/mydata/3307/mysql.sock --mysql-user=root --mysql-password=ESBecs00 --mysql-port=3307 --mysql-host=127.0.0.1 --mysql-db=pt prepare
  2. sysbench 0.4.12: multi-threaded system evaluation benchmark
  3. Creating table 'sbtest'...
  4. Creating 500000 records in table 'sbtest'...


run测试

  1. [root@iZ257l556beZ ~]# sysbench --test=oltp --oltp-table-size=500000 --db-driver=mysql --mysql-socket=/home/data/mydata/3307/mysql.sock --mysql-user=root --mysql-password=ESBecs00 --mysql-port=3307 --mysql-host=127.0.0.1 --mysql-db=pt run>res


查看测试结果

  1. [root@iZ257l556beZ ~]# more res
  2. sysbench 0.4.12: multi-threaded system evaluation benchmark
  3. Running the test with following options:
  4. Number of threads: 1
  5. Doing OLTP test.
  6. Running mixed OLTP test
  7. Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
  8. Using "BEGIN" for starting transactions
  9. Using auto_inc on the id column
  10. Maximum number of requests for OLTP test is limited to 10000
  11. Threads
  12. Done.
  13. OLTP test statistics:
  14.     queries performed:
  15.         read: 140000
  16.         write: 50000
  17.         other: 20000
  18.         total: 210000
  19.     transactions: 10000 (120.50 per sec.)     ----tps      10000/82.9887=120.5
  20.     deadlocks: 0 (0.00 per sec.)
  21.     read/write requests: 190000 (2289.47 per sec.)   ----qps       190000/82.9887
  22.     other operations: 20000 (241.00 per sec.)
  23. Test execution summary:
  24.     total time: 82.9887s
  25.     total number of events: 10000
  26.     total time taken by event execution: 82.9137
  27.     per-request statistics:
  28.          min: 5.98ms
  29.          avg: 8.29ms
  30.          max: 86.63ms
  31.          approx. 95 percentile: 10.78ms
  32. Threads fairness:
  33.     events (avg/stddev): 10000.0000/0.00
  34.     execution time (avg/stddev): 82.9137/0.00

相关推荐