一幻捏、MySQL自帶的壓力測試工具 Mysqlslap
mysqlslap是mysql自帶的基準(zhǔn)測試工具,該工具查詢數(shù)據(jù),語法簡單,靈活容易使用.該工具可以模擬多個客戶端同時并發(fā)的向服務(wù)器發(fā)出查詢更新,給出了性能測試數(shù)據(jù)而且提供了多種引擎的性能比較铣焊。mysqlslap為mysql性能優(yōu)化前后提供了直觀的驗證依據(jù),系統(tǒng)運(yùn)維和DBA人員應(yīng)該掌握一些常見的壓力測試工具,才能準(zhǔn)確的掌握線上數(shù)據(jù)庫支撐的用戶流量上限及其抗壓性等問題。
1、更改其默認(rèn)的最大連接數(shù)
在對MySQL進(jìn)行壓力測試之前，需要更改其默認(rèn)的最大連接數(shù)，如下：
[root@mysql?~]#?vim?/etc/my.cnf
................
[mysqld]
max_connections=1024
[root@mysql?~]#?systemctl?restart?mysqld
查看最大連接數(shù)
mysql>showvariableslike'max_connections';
+-----------------+-------+
|?Variable_name???|?Value?|
+-----------------+-------+
|?max_connections?|?1024??|
+-----------------+--------+
1?row?inset(0.00sec)
進(jìn)行壓力測試：
[root@mysql?~]#?mysqlslap?--defaults-file=/etc/my.cnf?--concurrency=100,200?--iterations=1?--number-int-cols=20?--number-char-cols=30?\
--auto-generate-sql?--auto-generate-sql-add-autoincrement?--auto-generate-sql-load-type=mixed?--engine=myisam,innodb?--number-of-queries=2000?-uroot?-p123?--verbose
上述命令測試說明：模擬測試兩次讀寫并發(fā)，第一次100音诈，第二次200，自動生成SQL腳本绎狭，測試表包含20個init字段改艇，30個char字段，每次執(zhí)行2000查詢請求坟岔。測試引擎分別是myisam谒兄，innodb。（上述選項中有很多都是默認(rèn)值社付，可以省略承疲，如果想要了解各個選項的解釋，可以使用mysqlslap --help進(jìn)行查詢）鸥咖。
測試結(jié)果說明：
Myisam第一次100客戶端同時發(fā)起增查用0.557/s,第二次200客戶端同時發(fā)起增查用0.522/s
Innodb第一次100客戶端同時發(fā)起增查用0.256/s,第二次200客戶端同時發(fā)起增查用0.303/s
可以根據(jù)實際需求燕鸽，一點點的加大并發(fā)數(shù)量進(jìn)行壓力測試。
二啼辣、使用第三方sysbench工具進(jìn)行壓力測試
1啊研、安裝sysbench工具
[root@mysql?~]#?yum?-y?install?epel-release?#安裝第三方epel源
[root@mysql?~]#?yum?-y?install?sysbench?#安裝sysbench工具
[root@mysql?~]#?sysbench?--version?#確定工具已安裝
sysbench?1.0.17
sysbench可以進(jìn)行以下測試：
CPU 運(yùn)算性能測試
磁盤 IO 性能測試
調(diào)度程序性能測試
內(nèi)存分配及傳輸速度測試
POSIX 線程性能測試
數(shù)據(jù)庫性能測試（OLTP 基準(zhǔn)測試，需要通過 /usr/share/sysbench/ 目錄中的 Lua 腳本執(zhí)行，例如 oltp_read_only.lua 腳本執(zhí)行只讀測試）党远。
sysbench 還可以通過運(yùn)行命令時指定自己的 Lua 腳本來自定義測試削解。
2、查看sysbench工具的幫助選項
[root@mysql?~]#?sysbench?--help
Usage:
sysbench?[options]...?[testname]?[command]
Commands?implemented?by?most?tests:?prepare?run?cleanuphelp#?可用的命令沟娱，四個
General?options:#?通用選項
--threads=N?要使用的線程數(shù)氛驮，默認(rèn)?1?個?[1]
--events=N?最大允許的事件個數(shù)?[0]
--time=N?最大的總執(zhí)行時間，以秒為單位?[10]
--forced-shutdown=STRING?在?--time?時間限制到達(dá)后济似，強(qiáng)制關(guān)閉之前等待的秒數(shù)矫废，默認(rèn)“off”禁用（number?of?seconds?towaitafter?the?--timelimitbefore?forcing?shutdown,?or'off'todisable）?[off]
--thread-stack-size=SIZE?每個線程的堆棧大小?[64K]
--rate=N 平均傳輸速率。0?則無限制?[0]
--report-interval=N?以秒為單位定期報告具有指定間隔的中間統(tǒng)計信息?0?禁用中間報告?[0]
--report-checkpoints=[LIST,...]?轉(zhuǎn)儲完整的統(tǒng)計信息并在指定的時間點重置所有計數(shù)器砰蠢。參數(shù)是一個逗號分隔的值列表蓖扑，表示從測試開始經(jīng)過這個時間量時必須執(zhí)行報告檢查點（以秒為單位）。報告檢查點默認(rèn)關(guān)閉台舱。?[]
--debug[=on|off]?打印更多?debug?信息?[off]
--validate[=on|off]?盡可能執(zhí)行驗證檢查?[off]
--help[=on|off]?顯示幫助信息并退出?[off]
--version[=on|off]?顯示版本信息并退出?[off]
--config-file=FILENAME?包含命令行選項的文件
--tx-rate=N?廢棄律杠，改用?--rate?[0]
--max-requests=N?廢棄，改用?--events?[0]
--max-time=N?廢棄柿赊，改用?--time?[0]
--num-threads=N?廢棄，改用?--threads?[1]
Pseudo-Random?Numbers?Generator?options:#?偽隨機(jī)數(shù)發(fā)生器選項
--rand-type=STRING?random?numbers?distribution?{uniform,gaussian,special,pareto}?[special]
--rand-spec-iter=N?number?of?iterations?usedfornumbers?generation?[12]
--rand-spec-pct=N?percentage?of?values?to?be?treated?as'special'(forspecial?distribution)?[1]
--rand-spec-res=N?percentage?of'special'values?to?use?(forspecial?distribution)?[75]
--rand-seed=N?seedforrandom?number?generator.?When?0,?the?current?time?is?used?as?a?RNG?seed.?[0]
--rand-pareto-h=N?parameter?hforpareto?distribution?[0.2]
Log?options:#?日志選項
--verbosity=N?verbosity?level?{5?-?debug,?0?-?only?critical?messages}?[3]
--percentile=N?percentile?to?calculateinlatency?statistics?(1-100).?Use?the?special?value?of?0?todisablepercentile?calculations?[95]
--histogram[=on|off]printlatency?histograminreport?[off]
General?database?options:#?通用的數(shù)據(jù)庫選項
--db-driver=STRING?指定要使用的數(shù)據(jù)庫驅(qū)動程序?('help'to?get?list?of?available?drivers)
--db-ps-mode=STRING?prepared?statements?usage?mode?{auto,disable}?[auto]
--db-debug[=on|off]printdatabase-specific?debug?information?[off]
Compiled-in?database?drivers:#?內(nèi)建的數(shù)據(jù)庫驅(qū)動程序幻枉，默認(rèn)支持?MySQL?和?PostgreSQL
mysql?-?MySQL?driver
pgsql?-?PostgreSQL?driver
mysql?options:#?MySQL?數(shù)據(jù)庫專用選項
--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-ssl[=on|off]?use?SSL?connections,ifavailableinthe?client?library?[off]
--mysql-ssl-cipher=STRING?use?specific?cipherforSSL?connections?[]
--mysql-compression[=on|off]?use?compression,ifavailableinthe?client?library?[off]
--mysql-debug[=on|off]?trace?all?client?library?calls?[off]
--mysql-ignore-errors=[LIST,...]?list?of?errors?to?ignore,?or"all"[1213,1020,1205]
--mysql-dry-run[=on|off]?Dry?run,?pretend?that?all?MySQL?client?API?calls?are?successful?without?executing?them?[off]
pgsql?options:#?PostgreSQL?數(shù)據(jù)庫專用選項
--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]
Compiled-in?tests:#?內(nèi)建測試類型
fileio?-?File?I/Otest
cpu?-?CPU?performancetest
memory?-?Memoryfunctionsspeedtest
threads?-?Threads?subsystem?performancetest
mutex?-?Mutex?performancetest
See'sysbench?<testname>?help'fora?list?of?optionsforeachtest.
3碰声、sysbench測試MySQL數(shù)據(jù)庫性能
1）準(zhǔn)備測試數(shù)據(jù)
#查看sysbench自帶的lua腳本使用方法
[root@mysql?~]#?sysbench?/usr/share/sysbench/oltp_common.lua?help
#必須創(chuàng)建sbtest庫，sbtest是sysbench默認(rèn)使用的庫名
[root@mysql?~]#?mysqladmin?-uroot?-p123?create?sbtest;
#然后熬甫，準(zhǔn)備測試所用的表胰挑，這些測試表放在測試庫sbtest中。這里使用的lua腳本為/usr/share/sysbench/oltp_common.lua椿肩。
[root@mysql?~]#?sysbench?--mysql-host=127.0.0.1?\
--mysql-port=3306?\
--mysql-user=root?\
--mysql-password=123?\
/usr/share/sysbench/oltp_common.lua?\
--tables=10?\
--table_size=100000?\
prepare
#其中--tables=10表示創(chuàng)建10個測試表瞻颂，
#--table_size=100000表示每個表中插入10W行數(shù)據(jù)，
#prepare表示這是準(zhǔn)備數(shù)的過程郑象。
2）確認(rèn)測試數(shù)據(jù)以存在
[root@mysql?~]#?mysql?-uroot?-p123?sbtest;?#登錄到sbtest庫
mysql>showtables;#查看相應(yīng)的表
+------------------+
|?Tables_in_sbtest?|
+------------------+
|?sbtest1?|
|?sbtest10?|
|?sbtest2?|
|?sbtest3?|
|?sbtest4?|
|?sbtest5?|
|?sbtest6?|
|?sbtest7?|
|?sbtest8?|
|?sbtest9?|
+------------------+
10?rows?inset(0.00sec)
mysql>?select?count(*)?from?sbtest1;#隨機(jī)選擇一個表贡这，確認(rèn)其有100000條數(shù)據(jù)
+----------+
|?count(*)?|
+----------+
|?100000?|
+----------+
1?rowinset(0.01?sec)
3）數(shù)據(jù)庫測試和結(jié)果分析
稍微修改下之前準(zhǔn)備數(shù)據(jù)的語句，就可以拿來測試了厂榛。需要注意的是盖矫，之前使用的lua腳本為oltp_common.lua，它是一個通用腳本击奶，是被其它lua腳本調(diào)用的辈双，它不能直接拿來測試。
所以柜砾，我這里用oltp_read_write.lua腳本來做讀湃望、寫測試。還有很多其它類型的測試，比如只讀測試证芭、只寫測試瞳浦、刪除測試、大批量插入測試等等檩帐∈踽＃可找到對應(yīng)的lua腳本進(jìn)行調(diào)用即可。
#執(zhí)行測試命令如下：
[root@mysql?~]#?sysbench?--threads=4?\
--time=20?\
--report-interval=5?\
--mysql-host=127.0.0.1?\
--mysql-port=3306?\
--mysql-user=root?\
--mysql-password=123?\
/usr/share/sysbench/oltp_read_write.lua?\
--tables=10?\
--table_size=100000?\
run
上述命令返回的結(jié)果如下：
[root@mysql?~]#?sysbench?--threads=4?--time=20?--report-interval=5?--mysql-host=127.0.0.1?--mysql-port=3306?--mysql-user=root?--mysql-password=123?/usr/share/sysbench/oltp_read_write.lua?--tables=10?--table_size=100000?run
sysbench?1.0.17?(using?system?LuaJIT?2.0.4)
Running?thetestwith?following?options:
Number?of?threads:?4
Report?intermediate?results?every?5?second(s)
Initializing?random?number?generator?from?current?time
Initializing?worker?threads...
Threads?started!
#以下是每5秒返回一次的結(jié)果湃密，統(tǒng)計的指標(biāo)包括：
#?線程數(shù)诅挑、tps(每秒事務(wù)數(shù))、qps(每秒查詢數(shù))泛源、
#?每秒的讀/寫/其它次數(shù)拔妥、延遲、每秒錯誤數(shù)达箍、每秒重連次數(shù)
[?5s?]?thds:?4?tps:?1040.21?qps:?20815.65?(r/w/o:?14573.17/4161.25/2081.22)?lat?(ms,95%):?7.17?err/s:?0.00?reconn/s:?0.00
[?10s?]?thds:?4?tps:?1083.34?qps:?21667.15?(r/w/o:?15165.93/4334.55/2166.68)?lat?(ms,95%):?6.55?err/s:?0.00?reconn/s:?0.00
[?15s?]?thds:?4?tps:?1121.57?qps:?22429.09?(r/w/o:?15700.64/4485.30/2243.15)?lat?(ms,95%):?6.55?err/s:?0.00?reconn/s:?0.00
[?20s?]?thds:?4?tps:?1141.69?qps:?22831.98?(r/w/o:?15982.65/4566.16/2283.18)?lat?(ms,95%):?6.09?err/s:?0.00?reconn/s:?0.00
SQL?statistics:
queries?performed:
read:?307146#?執(zhí)行的讀操作數(shù)量
write:?87756#?執(zhí)行的寫操作數(shù)量
other:?43878#?執(zhí)行的其它操作數(shù)量
total:?438780
transactions:?21939?(1096.57?per?sec.)#?執(zhí)行事務(wù)的平均速率
queries:?438780?(21931.37?per?sec.)#?平均每秒能執(zhí)行多少次查詢
ignored?errors:?0?(0.00?per?sec.)
reconnects:?0?(0.00?per?sec.)
General?statistics:
total?time:?20.0055s#?總消耗時間
total?number?of?events:?21939#?總請求數(shù)量(讀没龙、寫、其它)
Latency?(ms):
min:?1.39
avg:?3.64
max:?192.05
95th?percentile:?6.67#?采樣計算的平均延遲
sum:?79964.26
Threads?fairness:
events?(avg/stddev):?5484.7500/15.12
execution?time?(avg/stddev):?19.9911/0.00
4缎玫、cpu/io/內(nèi)存等測試
sysbench內(nèi)置的幾個測試指標(biāo)如下：
[root@mysql?~]#?sysbench?--help
..........#?省略部分內(nèi)容
Compiled-in?tests:
fileio?-?File?I/Otest
cpu?-?CPU?performancetest
memory?-?Memoryfunctionsspeedtest
threads?-?Threads?subsystem?performancetest
mutex?-?Mutex?performancetest
可以直接help輸出測試方法硬纤，例如，fileio測試：
[root@mysql?~]#?sysbench?fileio?help
sysbench?1.0.17?(using?system?LuaJIT?2.0.4)
fileio?options:
--file-num=N?number?of?files?to?create?[128]
--file-block-size=N?block?size?to?useinall?IO?operations?[16384]
--file-total-size=SIZE?total?size?of?files?to?create?[2G]
--file-test-mode=STRINGtestmode?{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=Ndofsync()?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?ratioforcombinedtest[1.5]
1）測試io性能
例如赃磨，創(chuàng)建5個文件筝家，總共2G，每個文件大概400M邻辉。
[root@mysql?~]#?sysbench?fileio?--file-num=5?--file-total-size=2G?prepare
[root@mysql?~]#?ll?-lh?test*
-rw-------?1?root?root?410M?May?26?16:05?test_file.0
-rw-------?1?root?root?410M?May?26?16:05?test_file.1
-rw-------?1?root?root?410M?May?26?16:05?test_file.2
-rw-------?1?root?root?410M?May?26?16:05?test_file.3
-rw-------?1?root?root?410M?May?26?16:05?test_file.4
然后運(yùn)行測試：
[root@mysql?~]#?sysbench?--events=5000?\
--threads=16?\
fileio?\
--file-num=5?\
--file-total-size=2G?\
--file-test-mode=rndrw?\
--file-fsync-freq=0?\
--file-block-size=16384?\
run
返回的結(jié)果如下：
Running?thetestwith?following?options:
Number?of?threads:?16
Initializing?random?number?generator?from?current?time
Extra?file?open?flags:?(none)
5?files,?409.6MiB?each
2GiB?total?file?size
Block?size?16KiB
Number?of?IO?requests:?5000
Read/Write?ratioforcombined?random?IOtest:?1.50
Calling?fsync()?at?the?end?oftest,?Enabled.
Using?synchronous?I/O?mode
Doing?random?r/wtest
Initializing?worker?threads...
Threads?started!
File?operations:
reads/s:?9899.03
writes/s:?6621.38
fsyncs/s:?264.33
Throughput:#?吞吐量
read,?MiB/s:?154.66#表示讀帶寬
written,?MiB/s:?103.46#表示寫的帶寬
General?statistics:
total?time:?0.3014s
total?number?of?events:?5000
Latency?(ms):
min:?0.00
avg:?0.81
max:?53.56
95th?percentile:?4.10
sum:?4030.48
Threads?fairness:
events?(avg/stddev):?312.5000/27.64
execution?time?(avg/stddev):?0.2519/0.02
2）測試cpu性能
[root@mysql?~]#?sysbench?cpu?--threads=40?--events=10000?--cpu-max-prime=20000?run
壓測時溪王，還請小心謹(jǐn)慎！Ｖ岛АＳ狻！
