mysqlslap是mysql自帶的基準測試工具,優點:查詢資料,語法簡單,靈活容易使用.該工具可以模擬多個客戶端同時併發的向伺服器發出查詢更新,給出了效能測試資料而且提供了多種引擎的效能比較.msqlslap為mysql效能優化前後提供了直觀的驗證依據,建議系統運維和DBA人員應該掌握一些常見的壓力測試工具,才能準確的掌握線上資料庫支撐的使用者流量上限及其抗壓性等問題.
mysql dba技術群 378190849
武漢-linux運維群 236415619
1.工具詳細引數
–help 顯示幫助
–concurrency 代表併發數量,多個可以用逗號隔開
–engines 測試的引擎,多個用分隔符隔開
–iterations 執行這些測試多少次
–auto-generate-sql 系統自己生成的SQL指令碼來測試
–auto-generate-sql-load-type 測試的是讀還是寫還是兩者混合的（read,write,update,mixed）
–number-of-queries 總共要執行多少次查詢
–debug-info 額外輸出CPU以及記憶體的相關資訊
–number-int-cols 建立測試表的 int 型欄位數量
–auto-generate-sql-add-autoincrement 生成的表自動新增auto_increment列
–number-char-cols 測試表的 char 型欄位數量
–create-schema 測試的database
–query 用自定義指令碼執行測試
–only-print 如果只想列印看看SQL語句是什麼,以用這個選項
2.測試例項(單個併發)
[root@node2 bin]# ./mysqlslap -uroot -psystem –concurrency=100 –iterations=1 –auto-generate-sql –auto-generate-sql-load-type=mixed –auto-generate-sql-add-autoincrement –engine=myisam –number-of-queries=10 –debug-info
Warning: Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.209 seconds
Minimum number of seconds to run all queries: 0.209 seconds
Maximum number of seconds to run all queries: 0.209 seconds
Number of clients running queries: 100
Average number of queries per client: 0
User time 0.02, System time 0.09
Maximum resident set size 4684, Integral resident set size 0
Non-physical pagefaults 1476, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 2797, Involuntary context switches 25
[root@node2 bin]#
說明:測試100個併發執行緒,測試次數1次,自動生成SQL測試指令碼,讀,寫,更新混合測試,自增長欄位,測試引擎為myisam,共執行10次查詢,輸出cpu資源資訊
3.測試例項(多個併發)
[root@node2 bin]# ./mysqlslap –concurrency=50,100,200 –iterations=20 –number-int-cols=4 –number-char-cols=35 –auto-generate-sql –auto-generate-sql-add-autoincrement –auto-generate-sql-load-type=read –engine=myisam,innodb –number-of-queries=200 –verbose –socket=/tmp/mysql.sock -uroot -psystem
Warning: Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.758 seconds
Minimum number of seconds to run all queries: 0.671 seconds
Maximum number of seconds to run all queries: 0.870 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.840 seconds
Minimum number of seconds to run all queries: 0.645 seconds
Maximum number of seconds to run all queries: 1.117 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.959 seconds
Minimum number of seconds to run all queries: 0.802 seconds
Maximum number of seconds to run all queries: 1.192 seconds
Number of clients running queries: 200
Average number of queries per client: 1
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.701 seconds
Minimum number of seconds to run all queries: 0.632 seconds
Maximum number of seconds to run all queries: 0.778 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.754 seconds
Minimum number of seconds to run all queries: 0.560 seconds
Maximum number of seconds to run all queries: 0.920 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.860 seconds
Minimum number of seconds to run all queries: 0.709 seconds
Maximum number of seconds to run all queries: 1.173 seconds
Number of clients running queries: 200
Average number of queries per client: 1
[root@node2 bin]#
說明:測試50,100,200個併發,執行20次,儲存引擎myisam和innodb,執行200次查詢
4.測試例項(自定義sql語句)
[root@node2 bin]# ./mysqlslap –concurrency=100 –iterations=1 –query=`select * from tong.t;` –number-of-queries=10 –debug-info -uroot -psystem
Benchmark
Average number of seconds to run all queries: 2.211 seconds
Minimum number of seconds to run all queries: 2.211 seconds
Maximum number of seconds to run all queries: 2.211 seconds
Number of clients running queries: 100
Average number of queries per client: 0
User time 0.05, System time 0.27
Maximum resident set size 7324, Integral resident set size 0
Non-physical pagefaults 1686, Physical pagefaults 33, Swaps 0
Blocks in 7104 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 3449, Involuntary context switches 40
[root@node2 bin]#
5.測試例項(檢測指令碼)
[root@node2 bin]# ./mysqlslap –create=/yourpath/Test1.sql –query=/yourpath/Test2.sql –concurrency=50,100,200 –iterations=20 –engine=myisam,innodb -u root -psystem