mysqlslap工具介绍

工具运行分为4个步骤：
创建数据库和表
插入数据
执行查询更新等操作 query（测试的主要内容）以下称为查询
删除数据库

1.使用自动生成的脚本测试
mysqlslap --help可以查看具体的参数的用法

例子：mysqlslap --concurrency=50,100,200 --iterations=20 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=read --engine=myisam,innodb --number-of-queries=200 --verbose --socket=/usr/local/mysql1/var/mysql.sock -u root -p123
使用系统自带的脚本测试，增加auto_increment一列，int4列char35列，测试2种引擎myisam,innodb读的性能分别用50，100，200个客户端对服务器进行测试总共200个查询语句 执行20次查询
加个--only-print不连接数据库只打印要做什么      

Benchmark
               Running for engine myisam
               Average number of seconds to run all queries: 0.897 seconds
               Minimum number of seconds to run all queries: 0.081 seconds
               Maximum number of seconds to run all queries: 1.239 seconds
               Number of clients running queries: 50
               Average number of queries per client: 4
###### 50个并发客户端 平均每个客户端4个查询 20次查询中最少的时间是0.081秒、 最多1.239秒、平均0.897秒。
Benchmark
               Running for engine myisam
               Average number of seconds to run all queries: 1.544 seconds
               Minimum number of seconds to run all queries: 0.000 seconds
               Maximum number of seconds to run all queries: 4.174 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: 1.671 seconds
               Minimum number of seconds to run all queries: 0.005 seconds
               Maximum number of seconds to run all queries: 5.724 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: 1.074 seconds
               Minimum number of seconds to run all queries: 0.000 seconds
               Maximum number of seconds to run all queries: 1.994 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: 1.088 seconds
               Minimum number of seconds to run all queries: 0.000 seconds
               Maximum number of seconds to run all queries: 1.793 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: 1.415 seconds
               Minimum number of seconds to run all queries: 0.006 seconds
               Maximum number of seconds to run all queries: 2.212 seconds
               Number of clients running queries: 200
               Average number of queries per client: 1


2.自定义脚本

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,'mxvtvmC9127qJNm06sGB8R92 q2j7vTiiITRDGXM9ZLzkdekb WtmXKwZ2qG1llkRw5m9DHOFi lEREk3q7oce8O3BEJC0woJsm 6uzFAEynLH2xCsw1KQ1lT4zg 9rdxBL');
INSERT INTO t1 VALUES (uuid(),822890675,'97RGHZ65mNzkSrYT3zWoSbg9 cNePQr1bzSk81qDgE4Oanw3r nPfGsBHSbnu1evTdFDe83ro9 w4jjteQg4yoo9xHck3WNqzs5 4W5zEm92ikdRF48B2oz3m8gM BAl11W');
INSERT INTO t1 VALUES (uuid(),1308044878,'50w46i58Giekxik0cYzfA8BZ BLADEg3JhzGfZDoqvQQk0Akc ic7lcJInYSsf9wqin6LDC1vz JLkJXKn5onqOy04MTw1WksCY qPl2Jg2eteqOqTLfGCvE4zTZ wWvgMz');
INSERT INTO t1 VALUES (uuid(),964445884,'DPh7kD1E6f4MMQk1ioopsoII coD83DD8Wu7689K6oHTAjD3H ts6lYGv8x9G0EL0k87q8G2Ex Jjz2o3KhnIJBbEJYFROTpO5p NvxgyBT9nSCbNO9AiKL9QYhi 0x3hL9');
INSERT INTO t1 VALUES (uuid(),1586903190,'lwRHuWm4HE8leYmg66uGYIp6 AnAr0BDd7YmuvYqCfqp9EbhK ZRSymA4wx6gpHlJHI53DetH9 j7Ixar90Jey5outd1ZIAJdJT jMaD7rMiqYXHFhHaB7Xr1HKu qe51GG');
INSERT INTO t1 VALUES (uuid(),962033002,'rfw4egILWisfxPwOc3nJx4fr nAwgI539kr5EXFbupSZelM2M HqZEmD6ZNuEZzHib8fqYuHQb drDND8lXqIdcNbAeWOBLZlpZ OX5AoNlQFzpK7QjxcLP0wbWI riYGJL');
INSERT INTO t1 VALUES (uuid(),1910858270,'ksnug3YyANnWWDEJiRkiFC4a 3e6KyJ2i3hSjksiuFLHlRXw9 yhjDtnfoQd0OouyrcIbCB9zQ WG4pf0yTZhaIT67nj7BY21FW JqaWrZxEh13Kt2hRbGl4Msrx suLmvd');
INSERT INTO t1 VALUES (uuid(),63299708,'FJid3GaHpRC2L6jgirPm5AW3 uGGgCloJ5Ww0eNHSiLWvS5bA xto23AxxR6TXr9qofeoAtxWc JsXnxzxmsdhvoekFc5mSES8t yxvsuPK5Hjs7ihtaJaLz5xEh 2s1GCA');
INSERT INTO t1 VALUES (uuid(),737703662,'2zxutF6rOqjXYHHzSrKRwAhW CPXTdhNXYKQIRO9sEkFf1YeT Gqw40Ta5u6QNfpvC1DWTTXDk FSFHtte9bbDSwgZjmryHglLh qjAKEF4MkJfT49eXcjzZNOG1 F6BnsY');
/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




--auto-generate-sql

当没有提供脚本的时候用系统自动生成的SQL脚本

--auto-generate-sql-add-autoincrement

增加auto_increment一列

--auto-generate-sql-execute-number=N

自动生成的查询的个数

--auto-generate-sql-guid-primary

增加GUID-based主键

--auto-generate-sql-load-type=type
read         查询      
write       插入
key           读主键
update     更新主键
mixed       一半插入一半查询

--auto-generate-sql-secondary-indexes=N

增加二级索引的个数 默认是0

--auto-generate-sql-unique-query-number=N

不同的查询数量 默认值是10

--auto-generate-sql-write-number=N

每个线程执行多少行插入默认是100

--auto-generate-sql-unique-write-number=N

  对--auto-generate-sql-write-number执行N个不同的插入语句默认是10

--commit=N

提交之前执行N个语句 默认是0

--compress

压缩所有客户端和服务器之间传送的信息

--concurrency=N

N个客户端执行查询语句，并发的数量

--create=value

用来创建表的语句的文件或者字符串

--create-schema=value

用来测试的数据库名 默认是mysqlslap

--csv

用逗号隔开格式的输出结果   没有值就输出到标准输出

--debug-info

打印内存和CPU信息

--delimiter

文件中的SQL语句使用分割符号

--detach=N

每N个语句断开在打开连接

--engine

表所使用的引擎，多个时用逗号隔开


--iterations

测试的次数

--number-char-cols=N

varchar列的个数   --auto-generate-sql

--number-int-cols=N

int列的个数 --auto-generate-sql

--number-of-queries=N

限制每个客户端查询的次数

--only-print

不连接数据库只打印要做什么

--host
--password
--pipe
--port
--protocol
--socket
--user
这些参数都是连接参数使用
--post-query=value

测试完成以后执行的SQL语句的文件或者字符串 这个过程不影响时间计算

--post-system=str

测试完成以后执行的系统语句 这个过程不影响时间计算

--pre-query

测试执行之前执行的SQL语句的文件或者字符串 这个过程不影响时间计算

--pre-system

测试执行之前执行的系统语句 这个过程不影响时间计算

--silent

不输出

--verbose

输出更多的信息

更多的参数可以参考http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html#option_mysqlslap_number-of-queries


总结：经过测试得出如下结论
对于工具评价:工具可以模拟多个客户端同时并发的向服务器发出查询更新，给出了性能测试数据。而且提供了多种引擎的比较。

本文转自 fenghao.cn 51CTO博客，原文链接：http://blog.51cto.com/linuxguest/426003，如需转载请自行联系原作者