MySQL性能測試工具MySQLslap使用實例詳解

MySQLslap是MySQL（了解乾貨請戳：乾貨！超過500行的Mysql學習筆記）自帶的基準測試工具，優點:查詢數據,語法簡單,靈活容易使用。

該工具可以模擬多個客戶端同時並發的向伺服器發出查詢更新,給出了性能測試數據而且提供了多種引擎的性能比較。

msqlslap為mysql性能優化前後提供了直觀的驗證依據,建議系統運維和DBA人員應該掌握一些常見的壓力測試工具,才能準確的掌握線上資料庫支撐的用戶流量上限及其抗壓性等問題。

常用的選項

--concurrency

//並發數量,多個可以用逗號隔開

--engines

//要測試的引擎，可以有多個，用分隔符隔開，如--engines=myisam,innodb

--iterations

//要運行這些測試多少次

--auto-generate-sql

//用系統自己生成的SQL腳本來測試

--auto-generate-sql-load-type

//要測試的是讀還是寫還是兩者混合的（read,write,update,mixed）

--number-of-queries

//總共要運行多少次查詢。每個客戶運行的查詢數量可以用查詢總數/並發數來計算

--debug-info

//額外輸出CPU以及內存的相關信息

--number-int-cols

//創建測試表的int型欄位數量

--number-char-cols

//創建測試表的chat型欄位數量

--create-schema

//測試的database

--query 自己的SQL

//腳本執行測試

--only-print

//如果只想列印看看SQL語句是什麼，可以用這個選項

實例1

說明:測試100個並發線程，測試次數1次，自動生成SQL測試腳本，讀、寫、更新混合測試，自增長欄位，測試引擎為innodb，共運行5000次查詢

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.351 seconds

100個客戶端（並發）同時運行這些SQL語句平均要花0.351秒

Minimum number of seconds to run all queries: 0.351 seconds

Maximum number of seconds to run all queries: 0.351 seconds

Number of clients running queries: 100

總共100個客戶端（並發）運行這些sql查詢

Average number of queries per client:50

每個客戶端（並發）平均運行50次查詢（對應--concurrency=100，--number-of-queries=5000；5000/100=50）

實例2

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100,500,1000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000 --debug-info

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.328 seconds

Minimum number of seconds to run all queries: 0.328 seconds

Maximum number of seconds to run all queries: 0.328 seconds

Number of clients running queries: 100

Average number of queries per client: 50

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.358 seconds

Minimum number of seconds to run all queries: 0.358 seconds

Maximum number of seconds to run all queries: 0.358 seconds

Number of clients running queries: 500

Average number of queries per client: 10

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.482 seconds

Minimum number of seconds to run all queries: 0.482 seconds

Maximum number of seconds to run all queries: 0.482 seconds

Number of clients running queries: 1000

Average number of queries per client: 5

User time 0.21, System time 0.78

Maximum resident set size 21520, Integral resident set size 0

Non-physical pagefaults 12332, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 36771, Involuntary context switches 1396

實例3(自定義sql語句)

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100 --iterations=1 --create-schema=rudao --query=select * from serverlist; --engine=innodb --number-of-queries=5000 --debug-info

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.144 seconds

Minimum number of seconds to run all queries: 0.144 seconds

Maximum number of seconds to run all queries: 0.144 seconds

Number of clients running queries: 100

Average number of queries per client: 50

User time 0.05, System time 0.09

Maximum resident set size 6132, Integral resident set size 0

Non-physical pagefaults 2078, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 6051, Involuntary context switches 90

實例4(指定sql腳本)

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100 --iterations=1 --create-schema=rudao --query=/tmp/query.sql --engine=innodb --number-of-queries=5000 --debug-info

Warning: Using a password on the command line interface can be insecure.

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.157 seconds

Minimum number of seconds to run all queries: 0.157 seconds

Maximum number of seconds to run all queries: 0.157 seconds

Number of clients running queries: 100

Average number of queries per client: 50

User time 0.07, System time 0.08

Maximum resident set size 6152, Integral resident set size 0

Non-physical pagefaults 2107, Physical pagefaults 0, Swaps 0

Blocks in 0 out 0, Messages in 0 out 0, Signals 0

Voluntary context switches 6076, Involuntary context switches 89

更多內容等你來戳：防止黑客SQL注入的方法