【Mysql】sysbench基准测试工具

最新推荐文章于 2024-09-14 11:25:42 发布

cml2016

最新推荐文章于 2024-09-14 11:25:42 发布

阅读量146

点赞数

文章标签： lua 数据库

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

测试f ileio
[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测试中，最好让线程、最大请求数、文件大小三个值的固定为合适的值，通过改变不同的测试模式进行性能比对。

测试数据库的OLT P性能

[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 --oltp-read-only=off --oltp-dist-type=uniform --num-threads=10 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 --oltp-read-only=off --oltp-dist-type=uniform --num-threads=10 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

sysbench5.0改进版本

sysbench 0.5相比0.4版本的主要变化是，oltp测试结合了lua脚本，不需要修改源码，通过自定义lua脚本就可以实现不同业务类型的测试。同时0.5相比0.4需要消耗更多的cpu资源。

1、查看帮助

sysbench --help

sysbench --test=fileio help

可以查看 fileio、 cpu、 memory、 threads、 mutex的详细参数，0.4版本还可以查看oltp的参数
这里和0.4版本不同，这里--test不再有
oltp - OLTP test这个项目，而是换成了对应的lua脚本路径（0.5版本rpm包安装的情况下，默认lua脚本放在/usr/share/doc/sysbench/tests/db下）

可以查看：
[root@VM-MYSQL-TEST-LiDan db]# ll /usr/share/doc/sysbench/tests/db
total 44
-rw-r--r-- 1 root root 3585 Sep 7 2012 common.lua
-rw-r--r-- 1 root root 340 Sep 7 2012 delete.lua
-rw-r--r-- 1 root root 830 Sep 7 2012 insert.lua
-rw-r--r-- 1 root root 2925 Sep 7 2012 oltp.lua
-rw-r--r-- 1 root root 342 Sep 7 2012 oltp_simple.lua
-rw-r--r-- 1 root root 425 Sep 7 2012 parallel_prepare.lua
-rw-r--r-- 1 root root 343 Sep 7 2012 select.lua
-rw-r--r-- 1 root root 3964 Sep 7 2012 select_random_points.lua
-rw-r--r-- 1 root root 4066 Sep 7 2012 select_random_ranges.lua
-rw-r--r-- 1 root root 343 Sep 7 2012 update_index.lua
-rw-r--r-- 1 root root 552 Sep 7 2012 update_non_index.lua
如果对lua熟悉也可以自行修改lua脚本来满足自己的测试需求。
参数说明：

--oltp-table-size：指定表的大小，即表的行数
--oltp-tables-count：表的个数

--mysql-table-engine：指定存储引擎，如myisam，innodb，heap，ndbcluster，bdb，maria，falcon，pbxt

--mysql-db：指定在哪个数据库创建测试表，默认为sbtest库，需要提前创建好

--test：指定Lua脚本，参数选项大部分同老版本的--test=oltp help

--db-driver：指定驱动，默认为Mysql

--myisam-max-rows：指定Myisam表的MAX_ROWS选项

--oltp-secondary：测试表将使用二级索引KEY xid (ID) 替代 PRIMARY KEY (ID)，innodb引擎内部为每个表创建唯一6字节的主键索引

--oltp-auto-inc：设置id列为auto-incremental，值为on或off，默认为on
--report-interval=10：每隔多久打印一次统计信息，单位秒，0.5版本新增
--rand-init=on：是否随机初始化数据，如果不随机化那么初始好的数据每行内容除了主键不同外其他完全相同。
--rand-type=special：数据分布模式，special表示存在热点数据，uniform表示非热点数据模式
--mysql-table-engine=xxx：表的存储引擎类型，innodb、myisam、tokudb这些都可以
1准备数据：
[root@localhost /root ]# sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp_tables_count=5 --report-interval=5 --oltp-table-size=1000000 --num-threads=50 --mysql-user=root --mysql-password=ESBecs00 --mysql-table-engine=INNODB --rand-init=on --mysql-host=127.0.0.1 --mysql-port=3308 --mysql-db=test prepare
sysbench 0.5: multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'

2 压测
[root@localhost /root ]# sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua --oltp_tables_count=4 --report-interval=5 --oltp-table-size=1000000 --num-threads=50 --mysql-user=root --mysql-password=ESBecs00 --mysql-table-engine=INNODB --rand-init=on --mysql-host=127.0.0.1 --mysql-port=3308 --mysql-db=test --oltp-read-only=off run --和上面一样，控制读写与并发线程数

3 清理环境
sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp_tables_count=5 --report-interval=5 --oltp-table-size=1000000 --num-threads=50 --mysql-user=root --mysql-password=ESBecs00 --mysql-table-engine=INNODB --rand-init=on --mysql-host=127.0.0.1 --mysql-port=3308 --mysql-db=test cleanup

参考文档
http://my.oschina.net/anthonyyau/blog/290030 ---sysbench
http://www.oschina.net/translate/mysql-performance-compare-between-5-6-and-5-5?cmp ----好的一篇测试mysql的性能的文章
http://www.361way.com/sysbench-mem-cpu-fileio/3411.html
http://www.jb51.net/article/65702.htm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1981855/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-1981855/