萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> Mysql壓測工具mysqlslap工具使用方法詳解

Mysql壓測工具mysqlslap工具使用方法詳解

mysqlslap是mysql中的一個壓力測試工具了它可以測試單mysql服務器也可以測試集群服務器,下面一聚教程小伙伴就為各位介紹mysqlslap工具使用詳解。

mysqlslap是從5.1.4版開始的一個MySQL官方提供的壓力測試工具。通過模擬多個並發客戶端訪問MySQL來執行壓力測試,同時詳細的提供了“高負荷攻擊MySQL”的數據性能報告。並且能很好的對比多個存儲引擎在相同環境下的並發壓力性能差別。通過mysqlslap –help可以獲得可用的選項,這裡列一些主要的參數,更詳細的說明參考官方手冊。如果是系統自帶或者使用rpm包安裝的mysql,安裝了MySQL-client端的包就有mysqlslap這個工具。如果是源碼安裝mysqlslap就直接在bin目錄下,如果是rpm安裝,需要單獨安裝rpm包。

mysqlslap參數詳細說明:

–auto-generate-sql, -a 自動生成測試表和數據,表示用mysqlslap工具自己生成的SQL腳本來測試並發壓力。
–auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環境是讀操作還是寫操作還是兩者混合的。取值包括:read,key,write,update和mixed(默認)。
–auto-generate-sql-add-auto-increment 代表對生成的表自動添加auto_increment列,從5.1.18版本開始支持。
–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參數指定的值做為分隔符。例如:–concurrency=100,200,500。
–engine=engine_name, -e engine_name 代表要測試的引擎,可以有多個,用分隔符隔開。例如:–engines=myisam,innodb。
–iterations=N, -i N 測試執行的迭代次數,代表要在不同並發環境下,各自運行測試多少次。
–only-print 只打印測試語句而不實際執行。
–detach=N 執行N條語句後斷開重連。
–debug-info, -T 打印內存和CPU的相關信息。

mysqlslap測試舉例

單線程測試。測試做了什麼。

# mysqlslap -a -uroot -p123456

多線程測試。使用–concurrency來模擬並發連接。

# mysqlslap -a -c 100 -uroot -p123456

迭代測試。用於需要多次執行測試得到平均值。

# mysqlslap -a -i 10 -uroot -p123456
# mysqlslap —auto-generate-sql-add-autoincrement -a -uroot -p123456
# mysqlslap -a –auto-generate-sql-load-type=read -uroot -p123456
# mysqlslap -a –auto-generate-secondary-indexes=3 -uroot -p123456
# mysqlslap -a –auto-generate-sql-write-number=1000 -uroot -p123456
# mysqlslap –create-schema world -q "select count(*) from City" -uroot -p123456
# mysqlslap -a -e innodb -uroot -p123456
# mysqlslap -a –number-of-queries=10 -uroot -p123456

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

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

執行一次測試,分別50和100個並發,執行1000次總查詢:

# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info -uroot -p123456

50和100個並發分別得到一次測試結果(Benchmark),並發數越多,執行完所有查詢的時間越長。為了准確起見,可以多迭代測試幾次:

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

如下圖是執行/usr/local/mysql/bin/mysqlslap -a -c 500 -i 10 -uroot -p

再補充一些測試實例:用自帶的SQL腳本來測試。

MySQL版本為5.1.23

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=50,100,200 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=200 --debug-info -uroot -p1 -S/tmp/mysql_3310.sock
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.063 seconds
        Minimum number of seconds to run all queries: 0.063 seconds
        Maximum number of seconds to run all queries: 0.063 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.070 seconds
        Minimum number of seconds to run all queries: 0.070 seconds
        Maximum number of seconds to run all queries: 0.070 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.092 seconds
        Minimum number of seconds to run all queries: 0.092 seconds
        Maximum number of seconds to run all queries: 0.092 seconds
        Number of clients running queries: 200
        Average number of queries per client: 1
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.115 seconds
        Minimum number of seconds to run all queries: 0.115 seconds
        Maximum number of seconds to run all queries: 0.115 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.134 seconds
        Minimum number of seconds to run all queries: 0.134 seconds
        Maximum number of seconds to run all queries: 0.134 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.192 seconds
        Minimum number of seconds to run all queries: 0.192 seconds
        Maximum number of seconds to run all queries: 0.192 seconds
        Number of clients running queries: 200
        Average number of queries per client: 1
User time 0.06, System time 0.15
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 5803, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 8173, Involuntary context switches 528

我來解釋一下結果的含義。拿每個引擎最後一個Benchmark示例。對於INNODB引擎,200個客戶端同時運行這些SQL語句平均要花0.192秒。相應的MYISAM為0.092秒。

2、用我們自己定義的SQL 腳本來測試。

這些數據在另外一個MySQL實例上。版本為5.0.45先看一下這兩個表的相關數據。
1)、總記錄數。

mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='article';
+--------+
| rows     |
+--------+
| 296693 |
+--------+
1 row in set (0.01 sec)
mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='category';
+------+
| rows |
+------+
|  113  |
+------+
1 row in set (0.00 sec)

2)、總列數。

mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'article';
+--------------+
| column_total |
+--------------+
|           32           |
+--------------+
1 row in set (0.01 sec)
mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'category';
+--------------+
| column_total |
+--------------+
|            9            |
+--------------+
1 row in set (0.01 sec)

3)、調用的存儲過程

DELIMITER $$
DROP PROCEDURE IF EXISTS `t_girl`.`sp_get_article`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_article`(IN f_category_id int,
IN f_page_size int, IN f_page_no int
)
BEGIN
  set @stmt = 'select a.* from article as a inner join ';
  set @stmt = concat(@stmt,'(select a.aid from article as a ');
  if f_category_id != 0 then
    set @stmt = concat(@stmt,' inner join (select cid from category where cid = ',f_category_id,' or parent_id = ',f_category_id,') as b on a.category_id = b.cid');
  end if;
  if f_page_size >0 && f_page_no > 0 then
    set @stmt = concat(@stmt,' limit ',(f_page_no-1)*f_page_size,',',f_page_size);
  end if; 

  set @stmt = concat(@stmt,') as b on (a.aid = b.aid)');
  prepare s1 from @stmt;
  execute s1;
  deallocate prepare s1;
  set @stmt = NULL;
END$$
DELIMITER ;

4)、我們用mysqlslap來測試

以下得這個例子代表用mysqlslap來測試並發數為25,50,100的調用存儲過程,並且總共調用5000次。

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --query='call t_girl.sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info  -uroot -p -S/tmp/mysql50.sock
Enter password:
Benchmark
        Average number of seconds to run all queries: 3.507 seconds
        Minimum number of seconds to run all queries: 3.507 seconds
        Maximum number of seconds to run all queries: 3.507 seconds
        Number of clients running queries: 25
        Average number of queries per client: 200

平均每個並發運行200個查詢用了3.507秒。

Benchmark
        Average number of seconds to run all queries: 3.742 seconds
        Minimum number of seconds to run all queries: 3.742 seconds
        Maximum number of seconds to run all queries: 3.742 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100
Benchmark
        Average number of seconds to run all queries: 3.697 seconds
        Minimum number of seconds to run all queries: 3.697 seconds
        Maximum number of seconds to run all queries: 3.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 50
User time 0.87, System time 0.33
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1877, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 27218, Involuntary context switches 3100

看一下SHOW PROCESSLIST 結果

mysql> show processlist;
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host               | db                 | Command | Time  | State              | Info                                                                                                 |
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
…………
| 3177 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3178 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3179 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3181 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3180 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3182 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3183 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3187 | root | %                  | t_girl             | Query   |     0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3186 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3194 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3203 | root | %                  | t_girl             | Query   |     0 | NULL               | deallocate prepare s1                                                                                |
…………
| 3221 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3222 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3223 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3224 | root | %                  | t_girl             | Query   |     0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3225 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
| 3226 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from |
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
55 rows in set (0.00 sec)

上面的測試語句其實也可以這樣寫

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --create-schema='t_girl' --query='call sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info  -uroot -p -S/tmp/mysql50.sock

小總結一下。mysqlslap對於模擬多個用戶同時對MySQL發起“進攻”提供了方便。同時詳細的提供了“高負荷攻擊MySQL”的詳細數據報告。而且如果你想對於多個引擎的性能。這個工具再好不過了。

對於程序員和DBA,可以把自己寫的SQL語句寫入到一個腳本中去,然後再通過mysqlslap工具對這些語句進行高並發的測試,大家就能通過報告找出在高並發下導致MYSQL運行緩慢的原因,這個工具非常有用喲

copyright © 萬盛學電腦網 all rights reserved