mysql blgg_压力测试工具之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列数量；

参考资料