MySQL壓力測試工具mysqlslap

原文出處：http://www.ningoo.net/html/2008/mysql_load_test_tool_mysqlslap.html

MySQL從5.1.4版開始帶有一個壓力測試工具mysqlslap，通過模拟多個并發用戶端通路mysql來執行測試，使用起來非常的簡單。通過mysqlslap --help可以獲得可用的選項，這裡列一些主要的參數，更詳細的說明參考官方手冊。

–auto-generate-sql, -a

自動生成測試表和資料

–auto-generate-sql-load-type=type

測試語句的類型。取值包括：read，key，write，update和mixed(預設)。

–number-char-cols=N, -x N

自動生成的測試表中包含多少個字元類型的列，預設1

–number-int-cols=N, -y N

自動生成的測試表中包含多少個數字類型的列，預設1

–number-of-queries=N

總的測試查詢次數(并發客戶數×每客戶查詢次數)

–query=name,-q

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

–create-schema

測試的schema，MySQL中schema也就是database

–commint=N

多少條DML後送出一次

–compress, -C

如果伺服器和用戶端支援都壓縮，則壓縮資訊傳遞

–concurrency=N, -c N

并發量，也就是模拟多少個用戶端同時執行select。可指定多個值，以逗号或者–delimiter參數指定的值做為分隔符

–engine=engine_name, -e engine_name

建立測試表所使用的存儲引擎，可指定多個

–iterations=N, -i N

測試執行的疊代次數

–detach=N

執行N條語句後斷開重連

–debug-info, -T

列印記憶體和CPU的資訊

–only-print

隻列印測試語句而不實際執行

測試的過程需要生成測試表，插入測試資料，這個mysqlslap可以自動生成，預設生成一個mysqlslap的schema，如果已經存在則先 删除，這裡要注意了，不要用–create-schema指定已經存在的庫，否則後果可能很嚴重。可以用–only-print來列印實際的測試過程：

以下如提示access denied 應加上參數 -uxxxx -pxxxx

$mysqlslap -a –only-print

DROP SCHEMA IF EXISTS `mysqlslap`;

CREATE SCHEMA `mysqlslap`;

use mysqlslap;

CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));

INSERT INTO t1 VALUES (1804289383,’mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL’);

…

SELECT intcol1,charcol1 FROM t1;

INSERT INTO t1 VALUES (364531492,’qMa5SuKo4M5OM7ldvisSc6WK9rsG9E8sSixocHdgfa5uiiNTGFxkDJ4EAwWC2e4NL1BpAgWiFRcp1zIH6F1BayPdmwphatwnmzdwgzWnQ6SRxmcvtd6JRYwEKdvuWr’);

可以看到最後由删除一開始建立的schema的動作，整個測試完成後不會在資料庫中留下痕迹。假如我們執行一次測試，分别50和100個并發，執行1000次總查詢，那麼：

$mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info

Benchmark

Average number of seconds to run all queries: 0.375 seconds

Minimum number of seconds to run all queries: 0.375 seconds

Maximum number of seconds to run all queries: 0.375 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Average number of seconds to run all queries: 0.453 seconds

Minimum number of seconds to run all queries: 0.453 seconds

Maximum number of seconds to run all queries: 0.453 seconds

Number of clients running queries: 100

Average number of queries per client: 10

User time 0.29, System time 0.11

Maximum resident set size 0, Integral resident set size 0

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

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

Voluntary context switches 7319, Involuntary context switches 681

上結果可以看出，50和100個并發分别得到一次測試結果(Benchmark)，并發數越多，執行完所有查詢的時間越長。為了準确起見，可以多疊代測試幾次:

$ mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –debug-info

Average number of seconds to run all queries: 0.380 seconds

Minimum number of seconds to run all queries: 0.377 seconds

Maximum number of seconds to run all queries: 0.385 seconds

Average number of seconds to run all queries: 0.447 seconds

Minimum number of seconds to run all queries: 0.444 seconds

Maximum number of seconds to run all queries: 0.451 seconds

User time 1.44, System time 0.67

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

Voluntary context switches 36796, Involuntary context switches 4093

測試同時不同的存儲引擎的性能進行對比：

$ mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info

Running for engine myisam

Average number of seconds to run all queries: 0.200 seconds

Minimum number of seconds to run all queries: 0.188 seconds

Maximum number of seconds to run all queries: 0.210 seconds

Average number of seconds to run all queries: 0.238 seconds

Minimum number of seconds to run all queries: 0.228 seconds

Maximum number of seconds to run all queries: 0.251 seconds

Running for engine innodb

Minimum number of seconds to run all queries: 0.370 seconds

Maximum number of seconds to run all queries: 0.379 seconds

Average number of seconds to run all queries: 0.443 seconds

Minimum number of seconds to run all queries: 0.440 seconds

Maximum number of seconds to run all queries: 0.447 seconds

User time 2.83, System time 1.66