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