mysql性能測試工具之mysqlslap

    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

 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

 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

 Average number of queries per client: 2

 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

 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

 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

 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

說明:測試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

 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

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