[开发测试]mysql数据库性能测试工具推荐

一.sysbench方法如何使用

以下列出了sysbench对测试数据库mysql时常用的选项。

[root@xuexi?~]#?sysbench?--help
Usage:
??sysbench?[options]...?[test_lua]?[lua_options]?[command]

Commands?implemented?by?most?tests:?prepare?run?cleanup?help

通用选项：以下中括号中的值表示默认值
??--threads=N?????????????????????指定线程数[1]
??--events=N??????????????????????限制最大请求数，0表示不限制[0]
??--time=N????????????????????????限制最长执行时间，0表示不限制[10]
????????????????????--events和--time二者选一即可
??--forced-shutdown=STRING????????达到最长执行时间后还需等待多久关闭sysbench
??????????????????????????????????off表示禁用该功能[off]
??--thread-stack-size=SIZE????????每个线程使用的栈空间大小[64K]
??--rate=N????????????????????????平均事务处理速率，0表示不限制[0]
??--report-interval=N?????????????每隔几秒报告一次结果，0表示禁用间隔报告[0]
??--config-file=FILENAME??????????从文件中读取命令行选项
??--tx-rate=N?????????????????????已废弃，是--rate的别名[0]
??--max-requests=N????????????????已废弃，是--events的别名[0]
??--max-time=N????????????????????已废弃，是--time的别名[0]
??--num-threads=N?????????????????已废弃，是--threads的别名[1]
??--db-ps-mode=STRING?????????????是否使用prepare模式的语句?{auto,?disable}?[auto]

mysql相关选项：
??--mysql-host=[LIST,...]??????????MySQL?server?host?[localhost]
??--mysql-port=[LIST,...]??????????MySQL?server?port?[3306]
??--mysql-socket=[LIST,...]????????MySQL?socket
??--mysql-user=STRING??????????????MySQL?user?[sbtest]
??--mysql-password=STRING??????????MySQL?password?[]
??--mysql-db=STRING????????????????MySQL?database?name?[sbtest]
??--mysql-ignore-errors=[LIST,...]?要忽略的错误代码，值可为"all"?[1213,1020,1205]

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

其中，command部分有4类：prepare run cleanup和help：

• prepare：准备数据的命令。例如，在sysbench压力测试之前，需要先准备好测试库、测试表以及测试表中的数据。具体用法见后文。

• run：表示进行压力测试。

• cleanup：清除测试时产生的数据。

• help：输出给定lua脚本的帮助信息。

test_lua是想要使用的lua脚本，如果是rpm包安装的sysbench，则这些脚本都是/usr/share/sysbench目录下。对于一般的数据库测试，只需使用和oltp有关的lua脚本就足够。

options和lua_options是不同的，options是sysbench的选项，lua_options是lua脚本的选项，lua_options应该要放在test_lua的后面(非必需，但建议)。

例如，要查看oltp_common.lua的用法，可以：

sysbench?/usr/share/sysbench/oltp_common.lua?help

二.准备测试数据

首先创建sysbench所需数据库sbtest(这是sysbench默认使用的库名，必须创建测试库)。

mysqladmin?-h127.0.0.1?-uroot?-pP@ssword1!?-P3306?create?sbtest;

然后，准备测试所用的表，这些测试表放在测试库sbtest中。这里使用的lua脚本为/usr/share/sysbench/oltp_common.lua。

sysbench?--mysql-host=127.0.0.1?\
?????????--mysql-port=3306?\
?????????--mysql-user=root?\
?????????--mysql-password=P@ssword1!?\
?????????/usr/share/sysbench/oltp_common.lua?\
?????????--tables=10?\
?????????--table_size=100000?\
?????????prepare

其中--tables=10表示创建10个测试表，--table_size=100000表示每个表中插入10W行数据，prepare表示这是准备数的过程。

mysql>?show?tables?from?sbtest;
+------------------+
|?Tables_in_sbtest?|
+------------------+
|?sbtest1??????????|
|?sbtest10?????????|
|?sbtest2??????????|
|?sbtest3??????????|
|?sbtest4??????????|
|?sbtest5??????????|
|?sbtest6??????????|
|?sbtest7??????????|
|?sbtest8??????????|
|?sbtest9??????????|
+------------------+

mysql>?select?count(*)?from?sbtest.sbtest1;
+----------+
|?count(*)?|
+----------+
|???100000?|
+----------+

如果想要清除这10个表，可使用cleanup命令。

sysbench?--mysql-host=127.0.0.1?\
?????????--mysql-port=3306?\
?????????--mysql-user=root?\
?????????--mysql-password=P@ssword1!?\
?????????/usr/share/sysbench/oltp_common.lua?\
?????????--tables=10?\
?????????cleanup

三.数据库测试和结果分析

需要注意之前使用的lua脚本为oltp_common.lua，它是通用脚本，是被其它lua脚本调用的，不能直接测试。

所以这里用oltp_read_write.lua脚本来做读、写测试。还有很多其它类型的测试，比如只读测试、只写测试、删除测试、大批量插入测试等等。可找到对应的lua脚本进行调用即可。

sysbench?--threads=4?\
?????????--time=20?\
?????????--report-interval=5?\
?????????--mysql-host=127.0.0.1?\
?????????--mysql-port=3306?\
?????????--mysql-user=root?\
?????????--mysql-password=P@ssword1!?\
?????????/usr/share/sysbench/oltp_read_write.lua?\
?????????--tables=10?\
?????????--table_size=100000?\
?????????run

以下是测试返回的结果：

Initializing?worker?threads...

Threads?started!

[?5s?]?thds:?4?tps:?130.16?qps:?2606.30?(r/w/o:?1824.51/520.66/261.13)?lat?(ms,95%):?104.84?err/s:?0.00?reconn/s:?0.00
[?10s?]?thds:?4?tps:?126.74?qps:?2539.17?(r/w/o:?1778.17/507.52/253.47)?lat?(ms,95%):?108.68?err/s:?0.00?reconn/s:?0.00
[?15s?]?thds:?4?tps:?136.54?qps:?2736.34?(r/w/o:?1915.25/548.01/273.07)?lat?(ms,95%):?102.97?err/s:?0.00?reconn/s:?0.00
[?20s?]?thds:?4?tps:?107.44?qps:?2148.65?(r/w/o:?1505.60/428.17/214.89)?lat?(ms,95%):?132.49?err/s:?0.00?reconn/s:?0.00

SQL?statistics:
????queries?performed:
????????read:??????????35098???#?执行的读操作数量
????????write:?????????10028???#?执行的写操作数量
????????other:?????????5014????#?执行的其它操作数量
????????total:?????????50140
????transactions:??????2507???(124.29?per?sec.)????#?执行事务的平均速率
????queries:???????????50140??(2485.82?per?sec.)???#?平均每秒能执行多少次查询
????ignored?errors:????0??????(0.00?per?sec.)
????reconnects:????????0??????(0.00?per?sec.)

General?statistics:
????total?time:??????????????????20.1694s?????#?总消耗时间
????total?number?of?events:??????2507?????????#?总请求数量(读、写、其它)

Latency?(ms):
?????????min:????????????????????????????2.32
?????????avg:???????????????????????????32.13
?????????max:??????????????????????????575.78
?????????95th?percentile:??????????????118.92????#?采样计算的平均延迟
?????????sum:????????????????????????80554.96

Threads?fairness:
????events?(avg/stddev):???????????626.7500/2.49
????execution?time?(avg/stddev):???20.1387/0.04

四.cpu/io/内存等测试

sysbench内置了几项测试指标

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

可直接help输出测试方法。例如，fileio测试。

[root@xuexi?~]#?sysbench?fileio?help
sysbench?1.0.15?(using?bundled?LuaJIT?2.1.0-beta2)

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,mmap}?[sync]
??--file-async-backlog=N????????number?of?asynchronous?operatons?to?queue?per?thread?[128]
??--file-extra-flags=[LIST,...]?list?of?additional?flags?to?use?to?open?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]

例如，创建5个文件，总共1G，每个文件大概200M。

sysbench?fileio?--file-num=5?--file-total-size=1G?prepare

[root@xuexi?~]#?ls?-lh?test*
-rw-------?1?root?root?205M?Jul??8?12:15?test_file.0
-rw-------?1?root?root?205M?Jul??8?12:15?test_file.1
-rw-------?1?root?root?205M?Jul??8?12:15?test_file.2
-rw-------?1?root?root?205M?Jul??8?12:15?test_file.3
-rw-------?1?root?root?205M?Jul??8?12:15?test_file.4

然后，运行测试。

sysbench?--events=5000?\
?????????--threads=16?\
?????????fileio?\
?????????--file-num=5?\
?????????--file-total-size=1G?\
?????????--file-test-mode=rndrw?\
?????????--file-fsync-freq=0?\
?????????--file-block-size=16384?\
?????????run

结果：测试分析

File?operations:
????reads/s:??????????????????????98.67
????writes/s:?????????????????????66.85
????fsyncs/s:?????????????????????6.26

Throughput:?????#?吞吐量
????read,?MiB/s:??????????????????1.54??#?表示读的带宽
????written,?MiB/s:???????????????1.04??#?表示读的带宽

General?statistics:
????total?time:???????????????????12.7426s
????total?number?of?events:???????2117

Latency?(ms):
?????????min:??????????????????????????0.00
?????????avg:?????????????????????????86.66
?????????max:???????????????????????2919.41
?????????95th?percentile:????????????646.19
?????????sum:?????????????????????183460.80

Threads?fairness:
????events?(avg/stddev):??????????132.3125/24.19
????execution?time?(avg/stddev):??11.4663/1.09

再比较cpu性能测试：

[root@xuexi?~]#?sysbench?cpu?--threads=40?--events=10000?--cpu-max-prime=20000?run
sysbench?1.0.15?(using?bundled?LuaJIT?2.1.0-beta2)

Running?the?test?with?following?options:
Number?of?threads:?40
Initializing?random?number?generator?from?current?time

Prime?numbers?limit:?20000

Initializing?worker?threads...

Threads?started!

CPU?speed:
????events?per?second:??2127.81

General?statistics:
????total?time:??????????????????????????4.6986s
????total?number?of?events:??????????????10000

Latency?(ms):
?????????min:????????????????????????????????????1.72
?????????avg:???????????????????????????????????18.16
?????????max:??????????????????????????????????302.17
?????????95th?percentile:??????????????????????110.66
?????????sum:???????????????????????????????181628.49

Threads?fairness:
????events?(avg/stddev):???????????250.0000/30.81
????execution?time?(avg/stddev):???4.5407/0.10