基准测试工具
基准测试工具：mysqlslap
是MySQL官方提供的性能基准测试工具，通过客户端模拟工作负载。
其执行包括三个阶段：
1. 创建表结构和加载数据
2. 运行测试
3. 清理数据
#例子1：--only-print 只输出SQL语句并打印 --auto-generate-sql 代表用系统自己生成的SQL脚本来测试
$ mysqlslap --only-print --auto-generate-sql
DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
INSERT INTO t1 VALUES (...);
INSERT INTO t1 VALUES (...);
....
SELECT intcol1,charcol1 FROM t1;
INSERT INTO t1 VALUES (...);
SELECT intcol1,charcol1 FROM t1;
INSERT INTO t1 VALUES (...);
...
DROP SCHEMA IF EXISTS `mysqlslap`;
#例子2：只输出SQL语句，对命令计数并打印
$ mysqlslap --only-print --auto-generate-sql | awk '{print $1" "$2}' | sort | uniq -c | sort -n -r
105 INSERT INTO
5 SELECT intcol1,charcol1
2 DROP SCHEMA
1 use mysqlslap;
1 CREATE TABLE
1 CREATE SCHEMA
说明：
默认，创建和删除数据库mysqlslap 可通过?create-schema=value指定一个不同的数据库名称 在数据加载阶段每个线程执行100个INSERT语句（使用?auto-generate-sql-write-number） 在测试阶段每个线程执行许多INSERT和SELECT语句（使用?auto-generate-sql-loadtype） #例子3：计数INSERT/SELECT语句，--number-of-queries 代表总共要运行多少次查询
$ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 | awk '{print $1}' | egrep '(INSERT|SELECT)' | sort | uniq -c | sort -n -r
154 INSERT INTO
45 SELECT intcol1,charcol1
#例子4：计数INSERT/SELECT语句，--concurrency 代表并发数量
$ mysqlslap --only-print --auto-generate-sql --concurrency=5
--number-of-queries=100 | awk '{print $1}' | egrep '(INSERT|
SELECT)' | sort | uniq -c | sort -n -r
154 INSERT INTO
45 SELECT intcol1,charcol1
#例子5：计数INSERT/SELECT语句，--iterations 代表要运行这些测试多少次
$ mysqlslap --only-print --auto-generate-sql --iteration=5
--number-of-queries=100 | awk '{print $1}' | egrep '(INSERT|
SELECT)' | sort | uniq -c | sort -n -r
770 INSERT INTO
225 SELECT intcol1,charcol1
#例子6：计数INSERT/SELECT语句，--auto-generate-sql-write-number 每个线程生成write sql语句数量
$ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 --auto-generate-sql-write-number=10000 | awk
'{print $1}' | egrep '(INSERT|SELECT)' | sort | uniq -c |
sort -n -r
10054 INSERT
45 SELECT
#例子7：计数INSERT/SELECT语句，--auto-generate-sql-load-type=read 代表要测试的是读还是写还是两者混合的
$ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 ?auto-generate-sql-write-number=10000
--auto-generate-sql-load-type=read | awk '{print $1}' |
egrep '(INSERT|SELECT)' | sort | uniq -c | sort -n -r
9999 INSERT
100 SELECT
#例子8：并发数1,2,3次执行性能比较，共执行查询100次，生成100000个INSERT语句
$ mysqlslap --auto-generate-sql --concurrency=1,2,3 ?-iteration=10 \
--number-of-queries=100 ?auto-generate-sql-write-number=100000
Benchmark
Average number of seconds to run all queries: 4.522 seconds
Minimum number of seconds to run all queries: 4.216 seconds
Maximum number of seconds to run all queries: 4.648 seconds
Number of clients running queries: 1
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 3.025 seconds
Minimum number of seconds to run all queries: 2.737 seconds
Maximum number of seconds to run all queries: 3.227 seconds
Number of clients running queries: 2
Average number of queries per client: 50
Benchmark
Average number of seconds to run all queries: 2.618 seconds
Minimum number of seconds to run all queries: 2.338 seconds
Maximum number of seconds to run all queries: 2.746 seconds
Number of clients running queries: 3
Average number of queries per client: 33
#例子9：比较myisam,i