《Mysql入門MySQL壓力測試方法 如何使用mysqlslap測試MySQL的壓力?》要點：
本文介紹了Mysql入門MySQL壓力測試方法 如何使用mysqlslap測試MySQL的壓力?，希望對您有用。如果有疑問，可以聯系我們。

其實mysql測試也沒有這么復雜,除了一些常用的select\insert\update\deletc這些外,其實測試他的并發量才是最重要的.比如在連接數1K的時候,并發量能否滿足當前哀求\服務器性能、內存CPU使用情況.說白了,測試mysql就是測試他的配置文件和并發量及服務器性能.MYSQL必讀

一、工具
首選工具mysql自帶的：mysqlslapMYSQL必讀

Cauto-generate-sql, -a
自動生成測試表和數據MYSQL必讀

Cauto-generate-sql-load-type=type
測試語句的類型.取值包括：read,key,write,update和mixed(默認).MYSQL必讀

Cnumber-char-cols=N, -x N
自動生成的測試表中包含多少個字符類型的列,默認1MYSQL必讀

Cnumber-int-cols=N, -y N
自動生成的測試表中包含多少個數字類型的列,默認1MYSQL必讀

Cnumber-of-queries=N
總的測試查詢次數(并發客戶數×每客戶查詢次數)MYSQL必讀

Cquery=name,-q
使用自定義腳本執行測試,例如可以調用自定義的一個存儲過程或者sql語句來執行測試.MYSQL必讀

Ccreate-schema
測試的schema,MySQL中schema也就是databaseMYSQL必讀

Ccommint=N
多少條DML后提交一次MYSQL必讀

Ccompress, -C
如果服務器和客戶端支持都壓縮,則壓縮信息傳遞MYSQL必讀

Cconcurrency=N, -c N
并發量,也就是模擬多少個客戶端同時執行select.可指定多個值,以逗號或者Cdelimiter參數指定的值做為分隔符MYSQL必讀

Cengine=engine_name, -e engine_name
創建測試表所使用的存儲引擎,可指定多個MYSQL必讀

Citerations=N, -i N
測試執行的迭代次數MYSQL必讀

Cdetach=N
執行N條語句后斷開重連MYSQL必讀

Cdebug-info, -T
打印內存和CPU的信息MYSQL必讀

Conly-print
只打印測試語句而不實際執行MYSQL必讀

―――――――――――――――――――――――――――――――-
測試的過程需要生成測試表,插入測試數據,這個mysqlslap可以自動生成,默認生成一個mysqlslap的schema,如果已經存在則先刪除,這里要注意了,不要用Ccreate-schema指定已經存在的庫,否則后果可能很嚴重.可以用Conly-print來打印實際的測試過程：MYSQL必讀

# mysqlslap -a Conly-printMYSQL必讀

DROP SCHEMA IF EXISTS `mysqlslap`;
 CREATE SCHEMA `mysqlslap`;
 use mysqlslap;
 CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
 INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRD9rdxBL');
 …
 SELECT intcol1,charcol1 FROM t1;
 INSERT INTO t1 VALUES (364531492,'qMa5SuKo4M5OM7ldvisSc6WK9rsNTGFxkDJ4EAwW');
 DROP SCHEMA IF EXISTS `mysqlslap`;

可以看到最后由刪除一開始創建的schema的動作,整個測試完成后不會在數據庫中留下痕跡.假如我們執行一次測試,分別50和100個并發,執行1000次總查詢,那么：MYSQL必讀

# mysqlslap -uroot -p123456 -a Cconcurrency=50,100 Cnumber-of-queries 1000 Cdebug-infoMYSQL必讀

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: 20MYSQL必讀

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: 10MYSQL必讀

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 681MYSQL必讀

以上結果可以看出,50和100個并發分別得到一次測試結果(Benchmark),并發數越多,執行完所有查詢的時間越長.為了準確起見,可以多迭代測試幾次:MYSQL必讀

# mysqlslap -a Cconcurrency=50,100 Cnumber-of-queries 1000 Citerations=5 Cdebug-infoMYSQL必讀

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: 20MYSQL必讀

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: 10MYSQL必讀

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 4093MYSQL必讀

測試同時不同的存儲引擎的性能進行對比：
# mysqlslap -uroot -p123456 -a -concurrency=50,100 Cnumber-of-queries 1000 Citerations=5 Cengine=myisam,innodb Cdebug-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: 20MYSQL必讀

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: 10MYSQL必讀

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: 20MYSQL必讀

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: 10MYSQL必讀

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 10326MYSQL必讀

# mysqladmin -uroot -p123456 -i 10 extended status 每10秒刷新一次MYSQL必讀

Aborted_connects 嘗試已經失敗的MySQL服務器的連接的次數.
Connections 試圖連接MySQL服務器的次數.
Created_tmp_tables 當執行語句時,已經被創造了的隱含臨時表的數量.
Delayed_insert_threads 正在使用的延遲插入處理器線程的數量.
Delayed_writes 用INSERT DELAYED寫入的行數.
Delayed_errors 用INSERT DELAYED寫入的發生某些錯誤(可能重復鍵值)的行數.
Flush_commands 執行FLUSH命令的次數.
Handler_delete 哀求從一張表中刪除行的次數.
Handler_read_first 哀求讀入表中第一行的次數.
Handler_read_key 哀求數字基于鍵讀行.
Handler_read_next 哀求讀入基于一個鍵的一行的次數.
Handler_read_rnd 哀求讀入基于一個固定位置的一行的次數.
Handler_update 哀求更新表中一行的次數.
Handler_write 哀求向表中插入一行的次數.
Key_blocks_used 用于關鍵字緩存的塊的數量.
Key_read_requests 哀求從緩存讀入一個鍵值的次數.
Key_reads 從磁盤物理讀入一個鍵值的次數.
Key_write_requests 哀求將一個關鍵字塊寫入緩存次數.
Key_writes 將一個鍵值塊物理寫入磁盤的次數.
Max_used_connections 同時使用的連接的最大數目.
Not_flushed_key_blocks 在鍵緩存中已經改變但是還沒被清空到磁盤上的鍵塊.
Not_flushed_delayed_rows 在INSERT DELAY隊列中等待寫入的行的數量.
Open_tables 打開表的數量.
Open_files 打開文件的數量.
Open_streams 打開流的數量(主要用于日志記載)
Opened_tables 已經打開的表的數量.
Questions 發往服務器的查詢的數量.
Slow_queries 要花超過long_query_time時間的查詢數量.
Threads_connected 當前打開的連接的數量.
Threads_running 不在睡眠的線程數量.
Uptime 服務器工作了多少秒.MYSQL必讀

維易PHP培訓學院每天發布《Mysql入門MySQL壓力測試方法 如何使用mysqlslap測試MySQL的壓力?》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/13975.html