MySQL效能測試工具之mysqlslap

MySQL效能測試工具之mysqlslap

mysqlslap是mysql內建的基準測試載入器,優點:查詢資料,文法簡單,靈活容易使用.該工具可以類比多個用戶端同時並發的向伺服器發出查詢更新,給出了效能測試資料而且提供了多種引擎的效能比較.msqlslap為mysql效能最佳化前後提供了直觀的驗證依據,建議系統營運和DBA人員應該掌握一些常見的壓力測試工具,才能準確的掌握線上資料庫支撐的使用者資料傳輸量上限及其抗壓性等問題。

mysqlslap 壓力測試工具

MySQL內建的效能壓力測試工具mysqlslap詳解

MySQL 壓力測試工具 mysqlslap 使用總結

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

本文永久更新連結地址：