MySQLSlap是從MySQL的5.1.4版引入的壓力測(cè)試工具。該工具我們可以在Mysql的安裝目錄的bin目錄里面找到盼忌，如C:\Program Files\MySQL\MySQL Server 5.7\bin>换怖。
名詞解釋
-
TPS：Transactions Per Second（每秒傳輸?shù)氖挛锾幚韨€(gè)數(shù)）快集，這是指服務(wù)器每秒處理的事務(wù)數(shù)笛臣，支持事務(wù)的存儲(chǔ)引擎如InnoDB等特有的一個(gè)性能指標(biāo)。TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME -
QPS：Queries Per Second（每秒查詢處理量）同時(shí)適用與InnoDB和MyISAM 引擎尸饺。QPS=QUESTIONS/UPTIME - 等待時(shí)間：執(zhí)行Sql等待返回結(jié)果之間的等待時(shí)間
主要功能
- 創(chuàng)建測(cè)試庫(kù)进统、測(cè)試表、測(cè)試表結(jié)構(gòu) 浪听；
- 運(yùn)行負(fù)載測(cè)試螟碎，可以使用多個(gè)并發(fā)客戶端連接；
- 測(cè)試環(huán)境清理（刪除創(chuàng)建的數(shù)據(jù)馋辈、表等抚芦，斷開(kāi)連接）;
主要參數(shù)
| ............................................................參數(shù) | 作用 |
|---|---|
--create-schema=name |
指定測(cè)試的數(shù)據(jù)庫(kù)名，默認(rèn)是mysqlslap迈螟。 |
--engine=engine_name,-e engine_name
|
代表要測(cè)試表的引擎叉抡，可以有多個(gè)用逗號(hào)分隔符隔開(kāi)，如：--engines=myisam,innodb答毫。 |
--concurrency=N,-c N
|
模擬N個(gè)客戶端并發(fā)執(zhí)行褥民。可指定多個(gè)值洗搂，以逗號(hào)隔開(kāi)消返，如：--concurrency=1,50,100,200。 |
--number-of-queries=N |
總的測(cè)試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))耘拇，比如并發(fā)是10撵颊，總次數(shù)是100，那么10個(gè)客戶端各執(zhí)行10個(gè) |
--iterations=N,-i N
|
迭代執(zhí)行的次數(shù)惫叛，即重復(fù)的次數(shù)（相同的測(cè)試進(jìn)行N次倡勇，求一個(gè)平均值），指的是整個(gè)步驟的重復(fù)次數(shù)嘉涌，包括準(zhǔn)備數(shù)據(jù)妻熊、測(cè)試load、清理仑最。 |
--commit=N |
執(zhí)行N條DML后提交一次扔役。 |
--auto-generate-sql, -a
|
自動(dòng)生成測(cè)試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來(lái)測(cè)試并發(fā)壓力警医。 |
--auto-generate-sql-load-type=name |
測(cè)試語(yǔ)句的類型亿胸。代表要測(cè)試的環(huán)境是讀操作還是寫操作還是兩者混合的。 取值包括： read (scan tables), write (insert into tables), key (read primary keys), update (update primary keys), or mixed (half inserts, half scanning selects). 默認(rèn)值是：mixed预皇。 |
--auto-generate-sql-add-auto-increment |
對(duì)生成的表自動(dòng)添加auto_increment列损敷。 |
--number-char-cols=name,-x N
|
自動(dòng)生成的測(cè)試表中包含N個(gè)字符類型的列，默認(rèn)1深啤。 |
--number-int-cols=name,-y N
|
自動(dòng)生成的測(cè)試表中包含N個(gè)數(shù)字類型的列拗馒，默認(rèn)1。 |
--debug-info |
打印內(nèi)存和CPU的信息溯街。 |
--compress, -C
|
如果服務(wù)器和客戶端支持都?jí)嚎s诱桂，則壓縮信息傳遞。 |
--only-print |
只打印測(cè)試語(yǔ)句而不實(shí)際執(zhí)行呈昔。 |
命令示例
- 并發(fā)數(shù)100挥等，循環(huán)10次，主動(dòng)建表和生成數(shù)據(jù)堤尾，指定表引擎為innodb肝劲，總的查詢次數(shù)為100：
mysqlslap -uroot -proot --concurrency=100 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=100
-uroot -proot：是數(shù)據(jù)庫(kù)用戶名和密碼
輸出結(jié)果：
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> ./mysqlslap -uroot -proot --concurrency=100 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=100
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.175 seconds
Minimum number of seconds to run all queries: 0.031 seconds
Maximum number of seconds to run all queries: 1.078 seconds
Number of clients running queries: 100
Average number of queries per client: 1
-
Running for engine innodb：運(yùn)行的存儲(chǔ)引擎是innodb。 -
Average number of seconds to run all queries: 0.175 seconds：運(yùn)行所有查詢的平均秒數(shù)。 -
Minimum number of seconds to run all queries: 0.031 seconds：運(yùn)行所有查詢的最小秒數(shù)辞槐。 -
Maximum number of seconds to run all queries: 1.078 seconds：運(yùn)行所有查詢的最大秒數(shù)掷漱。 -
Number of clients running queries: 100：運(yùn)行查詢的客戶端數(shù)（并發(fā)數(shù)）。 -
Average number of queries per client: 1：平均每個(gè)客戶的查詢數(shù)量榄檬。
- 分別執(zhí)行1,50,100,200的并發(fā)卜范，循環(huán)3次，表有5個(gè)字符串字段鹿榜，5個(gè)int型字段海雪，自動(dòng)創(chuàng)建建數(shù)據(jù)，新增自動(dòng)自動(dòng)增長(zhǎng)列舱殿，分別測(cè)試myisam,innodb兩個(gè)引擎奥裸，指定測(cè)試庫(kù)的名稱為enjoytest1，輸出內(nèi)存情況
mysqlslap -uroot -proot --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --create-schema='enjoytest1' --debug-info
輸出結(jié)果：
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> ./mysqlslap -uroot -proot --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --create-schema='enjoytest1'
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.010 seconds
Minimum number of seconds to run all queries: 0.000 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.375 seconds
Minimum number of seconds to run all queries: 0.031 seconds
Maximum number of seconds to run all queries: 1.047 seconds
Number of clients running queries: 50
Average number of queries per client: 0
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.041 seconds
Minimum number of seconds to run all queries: 0.015 seconds
Maximum number of seconds to run all queries: 0.093 seconds
Number of clients running queries: 100
Average number of queries per client: 0
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.427 seconds
Minimum number of seconds to run all queries: 0.015 seconds
Maximum number of seconds to run all queries: 1.125 seconds
Number of clients running queries: 200
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.010 seconds
Minimum number of seconds to run all queries: 0.000 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.494 seconds
Minimum number of seconds to run all queries: 0.156 seconds
Maximum number of seconds to run all queries: 1.172 seconds
Number of clients running queries: 50
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.343 seconds
Minimum number of seconds to run all queries: 0.328 seconds
Maximum number of seconds to run all queries: 0.359 seconds
Number of clients running queries: 100
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.250 seconds
Minimum number of seconds to run all queries: 0.593 seconds
Maximum number of seconds to run all queries: 1.579 seconds
Number of clients running queries: 200
Average number of queries per client: 0
