释放双眼，带上耳机，听听看~！
用mysqlslap进行mysql压力测试
mysqlslap官方文档
mysqlslap菜鸟译文
概述和测试环境
压测的目的是为了尽量模拟真实情况。测试的表都是由10个int型字段和10个字符串型字段组成。每个测试项目都测试了myisam和innodb两个引擎。测试的方法都是用两个线程并发，一共跑10000个请求。
压测的机器用的是自己的笔记本。CPU是Intel(R) Core(TM)2 Duo CPU P8400， 4G内存， SATA硬盘。
mysql服务器端和压测的客户端跑在同一台机器上。mysql的版本是5.5.
用到的mysqlslap参数解释如下：
–concurrency=2 两个线程
–iterations=1 执行一次
–number-int-cols=10 10个int型字段
–number-char-cols=10 10个字符串字段
-a 等同于–auto-generate-sql
–auto-generate-sql-add-autoincrement 创建auto increment的主键
–auto-generate-sql-secondary-indexes=2 创建2列索引
–auto-generate-sql-guid-primary 创建guid作为主键
–number-of-queries=10000 所有并发线程的请求次数和
–auto-generate-sql-load-type=write/key/read/update/mixed 测试的query类型
–engine=myisam,innodb 存储引擎
写性能测试
写性能测试1，不开binlog, guid做主键, 无索引
测试命令：
| 1 | mysqlslap –concurrency=2\ |
1 | 1 |
| 2 | –iterations=1\ |
1 | 1 |
| 3 | –number-int-cols=10\ |
1 | 1 |
| 4 | –number-char-cols=10\ |
1 | 1 |
| 5 | -a\ |
1 | 1 |
| 6 | –auto-generate-sql-guid-primary\ |
1 | 1 |
| 7 | –number-of-queries=10000\ |
1 | 1 |
| 8 | –auto-generate-sql-load-type=write\ |
1 | 1 |
| 9 | –engine=myisam,innodb\ |
1 | 1 |
结果：
| 01 | Benchmark |
1 | 1 |
| 02 | Runningforengine myisam |
1 | 1 |
| 03 | Average number of seconds to run all queries: 6.110 seconds |
1 | 1 |
| 04 | Minimum number of seconds to run all queries: 6.110 seconds |
1 | 1 |
| 05 | Maximum number of seconds to run all queries: 6.110 seconds |
1 | 1 |
| 06 | Number of clients running queries: 2 |
1 | 1 |
| 07 | Average number of queries per client: 5000 |
1 | 1 |
| 08 |
1 | 1 |
| 09 | Benchmark |
1 | 1 |
| 10 | Runningforengine innodb |
1 | 1 |
| 11 | Average number of seconds to run all queries: 10.291 seconds |
1 | 1 |
| 12 | Minimum number of seconds to run all queries: 10.291 seconds |
1 | 1 |
| 13 | Maximum number of seconds to run all queries: 10.291 seconds |
1 | 1 |
| 14 | Number of clients running queries: 2 |
1 | 1 |
| 15 | Average number of queries per client: 5000 |
1 | 1 |
用myisam每秒处理1639次。
用innodb每秒处理971次。
真实环境中一般会用binlog做数据备份与同步，所以性能会有所下降，有binlog的情况请继续看后面的实验。
写性能测试2， 开binlog, guid做主键， 无索引
测试命令与“写性能测试1”相同，
结果如下：
| 01 | Benchmark |
1 | 1 |
| 02 | Runningforengine myisam |
1 | 1 |
| 03 | Average number of seconds to run all queries: 14.809 seconds |
1 | 1 |
| 04 | Minimum number of seconds to run all queries: 14.809 seconds |
1 | 1 |
| 05 | Maximum number of seconds to run all queries: 14.809 seconds |
1 | 1 |
| 06 | Number of clients running queries: 2 |
1 | 1 |
| 07 | Average number of queries per client: 5000 |
1 | 1 |
| 08 |
1 | 1 |
| 09 | Benchmark |
1 | 1 |
| 10 | Runningforengine innodb |
1 | 1 |
| 11 | Average number of seconds to run all queries: 26.721 seconds |
1 | 1 |
| 12 | Minimum number of seconds to run all queries: 26.721 seconds |
1 | 1 |
| 13 | Maximum number of seconds to run all queries: 26.721 seconds |
1 | 1 |
| 14 | Number of clients running queries: 2 |
1 | 1 |
| 15 | Average number of queries per client: 5000 |
1 | 1 |
myisam每秒处理675次
innodb每秒处理374次
写性能测试3， 开binlog, guid做主键， 有索引
现实情况中，有索引的情况较多。采用
–auto-generate-sql-secondary-indexes=2 创建两列索引。
命令如下:
| 01 | mysqlslap –concurrency=2\ |
1 | 1 |
| 02 | –iterations=1\ |
1 | 1 |
| 03 | –number-int-cols=10\ |
1 | 1 |
| 04 | –number-char-cols=10\ |
1 | 1 |
| 05 | -a\ |
1 | 1 |
| 06 | –auto-generate-sql-guid-primary\ |
1 | 1 |
| 07 | –auto-generate-sql-secondary-indexes=2\ |
1 | 1 |
| 08 | –number-of-queries=10000\ |
1 | 1 |
| 09 | –auto-generate-sql-load-type=write\ |
1 | 1 |
| 10 | –engine=myisam,innodb\ |
1 | 1 |
结果如下：
| 01 | Benchmark |
1 | 1 |
| 02 | Runningforengine myisam |
1 | 1 |
| 03 | Average number of seconds to run all queries: 16.693 seconds |
1 | 1 |
| 04 | Minimum number of seconds to run all queries: 16.693 seconds |
1 | 1 |
| 05 | Maximum number of seconds to run all queries: 16.693 seconds |
1 | 1 |
| 06 | Number of clients running queries: 2 |
1 | 1 |
| 07 | Average number of queries per client: 5000 |
1 | 1 |
| 08 |
1 | 1 |
| 09 | Benchmark |
1 | 1 |
| 10 | Runningforengine innodb |
1 | 1 |
| 11 | Average number of seconds to run all queries: 30.418 seconds |
1 | 1 |
| 12 | Minimum number of seconds to run all queries: 30.418 seconds |
1 | 1 |
| 13 | Maximum number of seconds to run all queries: 30.418 seconds |
1 | 1 |
| 14 | Number of clients running queries: 2 |
1 | 1 |
| 15 | Average number of queries per client: 5000 |
1 | 1 |
myisam每秒处理599次。
innodb每秒处理329次。
写性能测试4，开binglog，auto increment主键，有索引
命令如下：
| 01 | mysqlslap –concurrency=2\ |
1 | 1 |
| 02 | –iterations=1\ |
1 | 1 |
| 03 | –number-int-cols=10\ |
1 | 1 |
| 04 | –number-char-cols=10\ |
1 | 1 |
| 05 | -a\ |
1 | 1 |
| 06 | –auto-generate-sql-add-autoincrement\ |
1 | 1 |
| 07 | –auto-generate-sql-secondary-indexes=2\ |
1 | 1 |
| 08 | –number-of-queries=10000\ |
1 | 1 |
| 09 | –auto-generate-sql-load-type=write\ |
1 | 1 |
| 10 | –engine=myisam,innodb\ |
1 | 1 |
结果如下：
| 01 | Benchmark |
1 | 1 |
| 02 | Runningforengine myisam |
1 | 1 |
| 03 | Average number of seconds to run all queries: 16.785 seconds |
1 | 1 |
| 04 | Minimum number of seconds to run all queries: 16.785 seconds |
1 | 1 |
| 05 | Maximum number of seconds to run all queries: 16.785 seconds |
1 | 1 |
| 06 | Number of clients running queries: 2 |
1 | 1 |
| 07 | Average number of queries per client: 5000 |
1 | 1 |
| 08 |
1 | 1 |
| 09 | Benchmark |
1 | 1 |
| 10 | Runningforengine innodb |
1 | 1 |
| 11 | Average number of seconds to run all queries: 28.809 seconds |
1 | 1 |
| 12 | Minimum number of seconds to run all queries: 28.809 seconds |
1 | 1 |
| 13 | Maximum number of seconds to run all queries: 28.809 seconds |
1 | 1 |
| 14 | Number of clients running queries: 2 |
1 | 1 |
| 15 | Average number of queries per client: 5000 |
1 | 1 |
myisam每秒处理595次。
innodb每秒处理347次。
读性能测试
在真实的情况中，我们最主要的读操作其实就是用主键去查找表中的一行。 我主要是对这种行为进行测试。所以采用的load-type = key ,而不是read。 read是对全表进行读取，可是实际上这种情况很少出现。
读性能测试1，guid主键
命令如下：
| 01 | mysqlslap –concurrency=2\ |
1 | 1 |
| 02 | –iterations=1\ |
1 | 1 |
| 03 | –number-int-cols=10\ |
1 | 1 |
| 04 | –number-char-cols=10\ |
1 | 1 |
| 05 | -a\ |
1 | 1 |
| 06 | –auto-generate-sql-guid-primary\ |
1 | 1 |
| 07 | –auto-generate-sql-unique-query-number=10000\ |
1 | 1 |
| 08 | –auto-generate-sql-load-type=key\ |
1 | 1 |
| 09 | –number-of-queries=10000\ |
1 | 1 |
| 10 | –engine=myisam,innodb\ |
1 | 1 |
结果如下：
| 01 | Benchmark |
1 | 1 |
| 02 | Runningforengine myisam |
1 | 1 |
| 03 | Average number of seconds to run all queries: 4.215 seconds |
1 | 1 |
| 04 | Minimum number of seconds to run all queries: 4.215 seconds |
1 | 1 |
| 05 | Maximum number of seconds to run all queries: 4.215 seconds |
1 | 1 |
| 06 | Number of clients running queries: 2 |
1 | 1 |
| 07 | Average number of queries per client: 5000 |
1 | 1 |
| 08 |
1 | 1 |
| 09 | Benchmark |
1 | 1 |
| 10 | Runningforengine innodb |
1 | 1 |
| 11 | Average number of seconds to run all queries: 3.917 seconds |
1 | 1 |
| 12 | Minimum number of seconds to run all queries: 3.917 seconds |
1 | 1 |
| 13 | Maximum number of seconds to run all queries: 3.917 seconds |
1 | 1 |
| 14 | Number of clients running queries: 2 |
1 | 1 |
| 15 | Average number of queries per client: 5000 |
1 | 1 |
myisam每秒处理2372次
innodb每秒处理2553次
读性能测试2，auto increment主键
命令如下
| 01 | mysqlslap –concurrency=1\ |
1 | 1 |
| 02 | –iterations=1\ |
1 | 1 |
| 03 | –number-int-cols=10\ |
1 | 1 |
| 04 | –number-char-cols=10\ |
1 | 1 |
| 05 | -a\ |
1 | 1 |
| 06 | –auto-generate-sql-add-autoincrement\ |
1 | 1 |
| 07 | –auto-generate-sql-unique-query-number=10000\ |
1 | 1 |
| 08 | –auto-generate-sql-load-type=key\ |
1 | 1 |
| 09 | –number-of-queries=10000\ |
1 | 1 |
| 10 | –engine=myisam,innodb\ |
1 | 1 |
结果如下：
| 01 | Benchmark |
1 | 1 |
| 02 | Runningforengine myisam |
1 | 1 |
| 03 | Average number of seconds to run all queries: 4.555 seconds |
1 | 1 |
| 04 | Minimum number of seconds to run all queries: 4.555 seconds |
1 | 1 |
| 05 | Maximum number of seconds to run all queries: 4.555 seconds |
1 | 1 |
| 06 | Number of clients running queries: 1 |
1 | 1 |
| 07 | Average number of queries per client: 10000 |
1 | 1 |
| 08 |
1 | 1 |
| 09 | Benchmark |
1 | 1 |
| 10 | Runningforengine innodb |
1 | 1 |
| 11 | Average number of seconds to run all queries: 4.402 seconds |
1 | 1 |
| 12 | Minimum number of seconds to run all queries: 4.402 seconds |
1 | 1 |
| 13 | Maximum number of seconds to run all queries: 4.402 seconds |
1 | 1 |
| 14 | Number of clients running queries: 1 |
1 | 1 |
| 15 | Average number of queries per client: 10000 |
1 | 1 |
myisam每秒处理2195次。
innodb每秒处理2273次。
实验结果
| 项目 | myisam每秒吞吐量 | innodb每秒吞吐量 |
| 写性能测试1，不开binlog, guid主键, 无索引 | 1639 | 971 |
| 写性能测试2，开binlog,guid主键，无索引 | 675 | 374 |
| 写性能测试3， 开binlog, guid做主键， 有索引 | 599 | 329 |
| 写性能测试4，开binglog，auto increment主键，有索引 | 595 | 347 |
| 读性能测试1，guid主键 | 2372 | 2553 |
| 读性能测试2，auto increment主键 | 2195 | 2273 |
1 | 1 |
结论
1 在开启了binlog后， mysql写性能下降60%
2 myisam与innodb相比，写的速度更快（快40%）, 读的速度差不多。
转载
转载地址
