前言

mysqlslap是mysql自帶的一款性能壓測工具，通過模擬多個並發客戶端訪問MySQL來進行壓力測試，同時提供了詳細的數據性能報告。此工具可以自動生成測試表和數據，並且可以模擬讀寫、混合讀寫、查詢等不同的使用場景，也能夠很好的對比多個存儲引擎在相同環境的並發壓力下的性能差異。本文是使用的《分散式服務架構——原理、設計與實踐》的案例。

單線程測試

λ mysqlslap -a -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.141 seconds
Minimum number of seconds to run all queries: 0.141 seconds
Maximum number of seconds to run all queries: 0.141 seconds
Number of clients running queries: 1
Average number of queries per client: 0

測試結果顯示單線程連接一次伺服器需要141ms。

多線程測試

λ mysqlslap -a -c 100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.500 seconds
Minimum number of seconds to run all queries: 5.500 seconds
Maximum number of seconds to run all queries: 5.500 seconds
Number of clients running queries: 100
Average number of queries per client: 0

可以看到使用100個線程同時連接一次伺服器需要5500ms，同單線程比，響應時間大幅度提升，這個不知道是什麼原因，需要後續查詢下。

多次測試求平均值

λ mysqlslap -a -i 10 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.370 seconds
Minimum number of seconds to run all queries: 0.140 seconds
Maximum number of seconds to run all queries: 1.203 seconds
Number of clients running queries: 1
Average number of queries per client: 0

最小響應時間和單線程測試一樣，但是平均響應時間還是有差，我的資料庫可能被下毒了。

讀操作的性能

λ mysqlslap -a -c10 --number-of-queries=1000 --auto-generate-sql-load-type=read -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.062 seconds
Minimum number of seconds to run all queries: 0.062 seconds
Maximum number of seconds to run all queries: 0.062 seconds
Number of clients running queries: 10
Average number of queries per client: 100

讀操作在並發數是10，進行1000次的讀操作，每次的讀需要62ms。

寫操作的性能

λ mysqlslap -a -c10 --number-of-queries=1000 --auto-generate-sql-load-type=write -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 11.875 seconds
Minimum number of seconds to run all queries: 11.875 seconds
Maximum number of seconds to run all queries: 11.875 seconds
Number of clients running queries: 10
Average number of queries per client: 100

毫無疑問，我這台機子的瓶頸在寫，平均寫時間要花掉11.875秒，蒼天啊，下面用混合的方式印證下。

混合操作性能

λ mysqlslap -a -c10 --number-of-queries=1000 --auto-generate-sql-load-type=mixed -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.844 seconds
Minimum number of seconds to run all queries: 5.844 seconds
Maximum number of seconds to run all queries: 5.844 seconds
Number of clients running queries: 10
Average number of queries per client: 100

基本上，在混合模式下的性能和在多線程情況下的性能是相同，推測在單線程的時候，是隨機讀寫，在多線程下是混合模式操作的，對單線程進行反覆的執行，印證了，單線程是隨機讀寫測試的。

不同資料庫引擎的性能

λ mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --engine=myisam,innodb --iterations=5
-uroot -proot
mysqlslap: [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.115 seconds
Minimum number of seconds to run all queries: 0.109 seconds
Maximum number of seconds to run all queries: 0.125 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.121 seconds
Minimum number of seconds to run all queries: 0.094 seconds
Maximum number of seconds to run all queries: 0.156 seconds
Number of clients running queries: 100
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 4.434 seconds
Minimum number of seconds to run all queries: 3.797 seconds
Maximum number of seconds to run all queries: 5.421 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 3.759 seconds
Minimum number of seconds to run all queries: 2.969 seconds
Maximum number of seconds to run all queries: 4.250 seconds
Number of clients running queries: 100
Average number of queries per client: 10

上面現實了myisam和innodb兩種引擎的性能，具體差異需要查閱mysql的原理的書籍查找原因。

總結

資料庫作為應用系統的性能的重要一環，掌握其資料庫的性能測試方法有助於判斷系統的性能瓶頸所在，已經系統架構中，資料庫的部署。

附錄

mysqlslap測試的參數的解釋，我們可以通過使用mysqlslap --help來顯示使用方法：

Default options are read from the following files in the given order:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

--c代表並發數量，多個可以用逗號隔開，concurrency=10,50,100, 並發連接線程數分別是10、50、100個並發。

--engines代表要測試的引擎，可以有多個，用分隔符隔開。

--iterations代表要運行這些測試多少次。

-- a 指的是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列，從5.1.18版本開始

--number-char-cols 創建測試表的 char 型欄位數量。

--create-schema 測試的schema，MySQL中schema也就是database。

--query 使用自定義腳本執行測試，例如可以調用自定義的一個存儲過程或者sql語句來執行測試。

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

本文最後參考了：https://blog.csdn.net/jjwen/article/details/51569234

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！