MySQL壓力測試工具

MySQL壓力測試工具

 

mysqlslap –create-schema=example –query="SELECT * FROM group_message force index(group_message_author_subject) WHERE author = '3' subject LIKE 'weiurazs%'" –iterations=10000

測試的過程需要生成測試表,插入測試數據,這個mysqlslap可以自動生成,默認生成一個mysqlslap的schema,如果已經存在則先刪除,這裡要註意瞭,不要用–create-schema指定已經存在的庫,否則後果可能很嚴重。可以用

–only-print來打印實際的測試過程:

  www.aiwalls.com  

$mysqlslap -a –only-print

DROP SCHEMA IF EXISTS mysqlslap;

CREATE SCHEMA mysqlslap;

use mysqlslap;

CREATE TABLE g_user (user_id INT(32) ,user_name VARCHAR(128));

INSERT INTO g_user VALUES (1,'liuxiaobin');

SELECT user_id,user_name FROM g_user;

INSERT INTO g_user VALUES (2,'liudabin');

DROP SCHEMA IF EXISTS mysqlslap;

 

可以看到最後由刪除一開始創建的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

  www.aiwalls.com  

Benchmark

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

Benchmark

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

Number of clients running queries: 50

Average number of queries per client: 20

 

Benchmark

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

Number of clients running queries: 100

Average number of queries per client: 10

  www.aiwalls.com  

User time 1.44, System time 0.67

Maximum resident set size 0, Integral resident set size 0

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

Blocks in 0 out 0, Messages in 0 out 0, Signals 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

Benchmark

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

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.238 seconds

Minimum number of seconds to run all queries: 0.228 seconds

Maximum number of seconds to run all queries: 0.251 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: 0.375 seconds

Minimum number of seconds to run all queries: 0.370 seconds

Maximum number of seconds to run all queries: 0.379 seconds

Number of clients running queries: 50

Average number of queries per client: 20

  www.aiwalls.com  

Benchmark

Running for engine innodb

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

Number of clients running queries: 100

Average number of queries per client: 10

 

User time 2.83, System time 1.66

Maximum resident set size 0, Integral resident set size 0

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

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

Voluntary context switches 87306, Involuntary context switches 10326

 

MySQL從5.1.4版開始帶有一個壓力測試工具mysqlslap,通過模擬多個並發客戶端訪問mysql來執行測試,使用起來非常的簡單。通過mysqlslap –help可以獲得可用的選項,

 

–auto-generate-sql, -a

自動生成測試表和數據

  www.aiwalls.com  

–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參數指定的值做為分隔符

  www.aiwalls.com  

–engine=engine_name, -e engine_name

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

 

–iterations=N, -i N

測試執行的迭代次數

 

–detach=N

執行N條語句後斷開重連

 

–debug-info, -T

打印內存和CPU的信息

 

–only-print

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

 

 

作者 bengda

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *