MySQL基准测试工具sysbench使用
对于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手册里的测试示例为例,一次测试需要三步,如:
- $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw prepare --准备
- $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run --测试
- $ 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
执行第二步操作测试的结果为:
- $ sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run
- sysbench 0.4.12: multi-threaded system evaluation benchmark
- Running the test with following options:
- Number of threads: 16
- Extra file open flags: 0
- 128 files, 24Mb each
- 3Gb total file size
- Block size 16Kb
- Number of random requests for random IO: 10000 --可通过max-requests参数设置,最大请求数
- Read/Write ratio for combined random IO test: 1.50
- Periodic FSYNC enabled, calling fsync() each 100 requests.
- Calling fsync() at the end of test, Enabled.
- Using synchronous I/O mode
- Doing random r/w test
- Threads
- Done.
- Operations performed: 6000 Read, 4002 Write, 12800 Other = 22802 Total
- Read 93.75Mb Written 62.531Mb Total transferred 156.28Mb (2.8256Mb/sec) ---随机写性能
- 180.84 Requests/sec executed --随机写的iops
- Test execution summary:
- total time: 55.3085s
- total number of events: 10002
- total time taken by event execution: 306.1095
- per-request statistics:
- min: 0.00ms
- avg: 30.60ms
- max: 508.92ms
- approx. 95 percentile: 162.07ms
- Threads fairness:
- events (avg/stddev): 625.1250/66.54
- execution time (avg/stddev): 19.1318/1.07
注:实际测试中最好几种模式的都逐一进行测试比对 。
测试基准报告(与上面无关)
线程数 | 测试模式 | 最大请求数 | 文件大小 | 传输速度 | 总执行时间 | |||
最小 | 最大 | 平均 | ||||||
16 | rndrw | 20000 | 10G | 2.8256Mb/sec | 200.3292s | 0.00ms | 684.51ms | 55.36ms |
16 | rndwr | 20000 | 10G | 2.2149Mb/sec | 352.7680s | 0.00ms | 684.52ms | 55.37ms |
16 | seqwr | 20000 | 10G | 2.3958Mb/sec | 200.3293s | 0.00ms | 684.53ms | 55.38ms |
16 | seqrd | 20000 | 10G | 2.1065Mb/sec | 125.8323s | 0.00ms | 684.54ms | 55.39ms |
备注:IO测试中,最好让线程、最大请求数、文件大小三个值的固定为合适的值,通过改变不同的测试模式进行性能比对 。
测试数据库的OLTP性能
- [root@iZ257l556beZ soft]# sysbench --test=oltp help
- sysbench 0.4.12: multi-threaded system evaluation benchmark
- 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
- pgsql - PostgreSQL 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 []
- pgsql options:
- --pgsql-host=STRING PostgreSQL server host [localhost]
- --pgsql-port=N PostgreSQL server port [5432]
- --pgsql-user=STRING PostgreSQL user [sbtest]
- --pgsql-password=STRING PostgreSQL password []
- --pgsql-db=STRING PostgreSQL database name [sbtest]
创建模拟数据
- [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
- sysbench 0.4.12: multi-threaded system evaluation benchmark
- Creating table 'sbtest'...
- Creating 500000 records in table 'sbtest'...
run测试
- [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
查看测试结果
- [root@iZ257l556beZ ~]# more res
- sysbench 0.4.12: multi-threaded system evaluation benchmark
- Running the test with following options:
- Number of threads: 1
- Doing OLTP test.
- Running mixed OLTP test
- Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
- Using "BEGIN" for starting transactions
- Using auto_inc on the id column
- Maximum number of requests for OLTP test is limited to 10000
- Threads
- Done.
- OLTP test statistics:
- queries performed:
- read: 140000
- write: 50000
- other: 20000
- total: 210000
- transactions: 10000 (120.50 per sec.) ----tps 10000/82.9887=120.5
- deadlocks: 0 (0.00 per sec.)
- read/write requests: 190000 (2289.47 per sec.) ----qps 190000/82.9887
- other operations: 20000 (241.00 per sec.)
- Test execution summary:
- total time: 82.9887s
- total number of events: 10000
- total time taken by event execution: 82.9137
- per-request statistics:
- min: 5.98ms
- avg: 8.29ms
- max: 86.63ms
- approx. 95 percentile: 10.78ms
- Threads fairness:
- events (avg/stddev): 10000.0000/0.00
- execution time (avg/stddev): 82.9137/0.00