mysqlslap工具使用

时间：2010-04-06 来源：pkman110

mysqlslap工具使用

(2008-08-27 14:19:12)

转载

标签：

mysql

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,'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

--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

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