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

MySQL基准测试工具sysbench使用

相关推荐