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值得怀疑
|
|
