壓力測試工具之mysqlslap

 

mysqlslap是一個mysql官方提供的壓力測試工具。以下是比較重要的引數：

–defaults-file，配置檔案存放位置

–concurrency，併發數

–engines，引擎

–iterations，迭代的實驗次數

–socket，socket檔案位置

- only-print，只列印測試語句而不實際執行

自動測試：

–auto-generate-sql，自動產生測試SQL

–auto-generate-sql-load-type，測試SQL的型別。型別有mixed，update，write，key，read。

–number-of-queries，執行的SQL總數量

–number-int-cols，表內int列的數量

–number-char-cols，表內char列的數量

執行步驟

工具執行分為4個步驟：

建立資料庫和表

插入資料

執行查詢更新等操作 query

刪除資料庫

指定資料庫的測試：

–create-schema，指定資料庫名稱

–query，指定SQL語句，可以定位到某個包含SQL的檔案

例如：

shell>mysqlslap –defaults-file=/u01/mysql1/mysql/my.cnf –concurrency=25,50 –iterations=1 –create-schema=test –query=/u01/test.sql -S/tmp/mysql1.sock

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

$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’);

DROP SCHEMA IF EXISTS `mysqlslap`;

自定義指令碼

mysqlslap --create=/root/Desktop/test.sql --query=/root/Desktop/test2.sql --concurrency=50,100,200 --iterations=20 --engine=myisam,innodb  --socket=/usr/local/mysql1/var/mysql.sock -u root -p123

/root/Desktop/test.sql

CREATE TABLE `t1` (id varchar(32) primary key,intcol1 INT(32) ,charcol1 VARCHAR(128));

INSERT INTO t1 VALUES (uuid(),1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');

INSERT INTO t1 VALUES (uuid(),822890675,'97RGHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11W');

INSERT INTO t1 VALUES (uuid(),1308044878,'50w46i58Giekxik0cYzfA8BZBLADEg3JhzGfZDoqvQQk0Akcic7lcJInYSsf9wqin6LDC1vzJLkJXKn5onqOy04MTw1WksCYqPl2Jg2eteqOqTLfGCvE4zTZwWvgMz');

INSERT INTO t1 VALUES (uuid(),964445884,'DPh7kD1E6f4MMQk1ioopsoIIcoD83DD8Wu7689K6oHTAjD3Hts6lYGv8x9G0EL0k87q8G2ExJjz2o3KhnIJBbEJYFROTpO5pNvxgyBT9nSCbNO9AiKL9QYhi0x3hL9');

INSERT INTO t1 VALUES (uuid(),1586903190,'lwRHuWm4HE8leYmg66uGYIp6AnAr0BDd7YmuvYqCfqp9EbhKZRSymA4wx6gpHlJHI53DetH9j7Ixar90Jey5outd1ZIAJdJTjMaD7rMiqYXHFhHaB7Xr1HKuqe51GG');

INSERT INTO t1 VALUES (uuid(),962033002,'rfw4egILWisfxPwOc3nJx4frnAwgI539kr5EXFbupSZelM2MHqZEmD6ZNuEZzHib8fqYuHQbdrDND8lXqIdcNbAeWOBLZlpZOX5AoNlQFzpK7QjxcLP0wbWIriYGJL');

INSERT INTO t1 VALUES (uuid(),1910858270,'ksnug3YyANnWWDEJiRkiFC4a3e6KyJ2i3hSjksiuFLHlRXw9yhjDtnfoQd0OouyrcIbCB9zQWG4pf0yTZhaIT67nj7BY21FWJqaWrZxEh13Kt2hRbGl4MsrxsuLmvd');

INSERT INTO t1 VALUES (uuid(),63299708,'FJid3GaHpRC2L6jgirPm5AW3uGGgCloJ5Ww0eNHSiLWvS5bAxto23AxxR6TXr9qofeoAtxWcJsXnxzxmsdhvoekFc5mSES8tyxvsuPK5Hjs7ihtaJaLz5xEh2s1GCA');

INSERT INTO t1 VALUES (uuid(),737703662,'2zxutF6rOqjXYHHzSrKRwAhWCPXTdhNXYKQIRO9sEkFf1YeTGqw40Ta5u6QNfpvC1DWTTXDkFSFHtte9bbDSwgZjmryHglLhqjAKEF4MkJfT49eXcjzZNOG1F6BnsY');

/root/Desktop/test2.sql

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

SELECT intcol1,charcol1 FROM t1;

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 0.128 seconds

        Minimum number of seconds to run all queries: 0.004 seconds

        Maximum number of seconds to run all queries: 0.167 seconds

        Number of clients running queries: 50

        Average number of queries per client: 10

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 0.226 seconds

        Minimum number of seconds to run all queries: 0.164 seconds

        Maximum number of seconds to run all queries: 0.258 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 0.360 seconds

        Minimum number of seconds to run all queries: 0.340 seconds

        Maximum number of seconds to run all queries: 0.411 seconds

        Number of clients running queries: 200

        Average number of queries per client: 10

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 0.161 seconds

        Minimum number of seconds to run all queries: 0.142 seconds

        Maximum number of seconds to run all queries: 0.216 seconds

        Number of clients running queries: 50

        Average number of queries per client: 10

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 0.275 seconds

        Minimum number of seconds to run all queries: 0.180 seconds

        Maximum number of seconds to run all queries: 0.371 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.428 seconds

        Minimum number of seconds to run all queries: 0.369 seconds

        Maximum number of seconds to run all queries: 0.601 seconds

        Number of clients running queries: 200

        Average number of queries per client: 10

優勢：相比sysbench和tpcc-mysql，mysqlslap可以指定執行的sql text，也可自主定義表結構，或者指定自動生成表結構的int/char列數量；

參考資料

http://blog.sina.com.cn/s/blog_5aefd9770100aft8.html

http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html