mysql 压测结果_用mysqlslap压测mysql

参考文献：http://my.oschina.net/costaxu/blog/108568

上面网友详细的列举了用mysqlslap对mysql的压力测试结果，我也照葫芦画瓢试了一次，结果如下：

以下命令代表：用10个并发连接，对一个有10个int列，10个varchar列，两个索引的表进行写操作

mysqlslap --concurrency=10 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-add-autoincrement --number-of-queries=5000 --auto-generate-sql-load-type=write -auto-generate-sql-secondary-indexex=2 --engine=myisam,innodb -h10.210.214.93 -P3306 -uroot -proot --debug-info

结果：

Benchmark

Runningforengine myisam

Average number of seconds to run all queries:0.694seconds

Minimum number of seconds to run all queries:0.694seconds

Maximum number of seconds to run all queries:0.694seconds

Number of clients running queries:10Average number of queries per client:500Benchmark

Runningforengine innodb

Average number of seconds to run all queries:3.446seconds

Minimum number of seconds to run all queries:3.446seconds

Maximum number of seconds to run all queries:3.446seconds

Number of clients running queries:10Average number of queries per client:500

TPS:

myisam=7205

innodb=1451

通过改变参数--concurrency=50和--concurrency=100来更改并发数，分别试验两次，结果如下：

mysqlslap --concurrency=50 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-add-autoincrement --number-of-queries=50000 --auto-generate-sql-load-type=write -auto-generate-sql-secondary-indexex=2 --engine=myisam,innodb -h10.210.214.93 -P3306 -uroot -proot --debug-infoBenchmark

Runningforengine myisam

Average number of seconds to run all queries:8.991seconds

Minimum number of seconds to run all queries:8.991seconds

Maximum number of seconds to run all queries:8.991seconds

Number of clients running queries:50Average number of queries per client:1000Benchmark

Runningforengine innodb

Average number of seconds to run all queries:46.858seconds

Minimum number of seconds to run all queries:46.858seconds

Maximum number of seconds to run all queries:46.858seconds

Number of clients running queries:50Average number of queries per client:1000Benchmark

Runningforengine myisam

Average number of seconds to run all queries:9.687seconds

Minimum number of seconds to run all queries:9.687seconds

Maximum number of seconds to run all queries:9.687seconds

Number of clients running queries:50Average number of queries per client:1000Benchmark

Runningforengine innodb

Average number of seconds to run all queries:57.171seconds

Minimum number of seconds to run all queries:57.171seconds

Maximum number of seconds to run all queries:57.171seconds

Number of clients running queries:50Average number of queries per client:100mysqlslap--concurrency=50 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-add-autoincrement --number-of-queries=50000 --auto-generate-sql-load-type=write -auto-generate-sql-secondary-indexex=2 --engine=myisam,innodb -h10.210.214.93 -P3306 -uroot -proot --debug-infoBenchmark

Runningforengine myisam

Average number of seconds to run all queries:7.060seconds

Minimum number of seconds to run all queries:7.060seconds

Maximum number of seconds to run all queries:7.060seconds

Number of clients running queries:100Average number of queries per client:500Benchmark

Runningforengine innodb

Average number of seconds to run all queries:44.711seconds

Minimum number of seconds to run all queries:44.711seconds

Maximum number of seconds to run all queries:44.711seconds

Number of clients running queries:100Average number of queries per client:500Benchmark

Runningforengine myisam

Average number of seconds to run all queries:6.730seconds

Minimum number of seconds to run all queries:6.730seconds

Maximum number of seconds to run all queries:6.730seconds

Number of clients running queries:100Average number of queries per client:500Benchmark

Runningforengine innodb

Average number of seconds to run all queries:39.591seconds

Minimum number of seconds to run all queries:39.591seconds

Maximum number of seconds to run all queries:39.591seconds

Number of clients running queries:100Average number of queries per client:500

并发50 myisam   TPS= 5354

并发50 innodb    TPS= 961

并发100 myisam TPS= 7252

并发100 innodb  TPS= 1186

下面又测试了读的性能：

mysqlslap --concurrency=50 --iterations=2 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-add-autoincrement --auto-generate-sql-unique-query-number=10000 --auto-generate-sql-load-type=key --number-of-queries=50000 --engine=myisam,innodb -h10.210.214.93 -P3306 -uroot -proot --debug-infoBenchmark

Runningforengine myisam

Average number of seconds to run all queries:4.172seconds

Minimum number of seconds to run all queries:4.071seconds

Maximum number of seconds to run all queries:4.274seconds

Number of clients running queries:50Average number of queries per client:1000Benchmark

Runningforengine innodb

Average number of seconds to run all queries:4.094seconds

Minimum number of seconds to run all queries:4.070seconds

Maximum number of seconds to run all queries:4.118seconds

Number of clients running queries:50Average number of queries per client:1000mysqlslap--concurrency=100 --iterations=2 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-add-autoincrement --auto-generate-sql-unique-query-number=50000 --auto-generate-sql-load-type=key --number-of-queries=50000 --engine=myisam,innodb -h10.210.214.93 -P3306 -uroot -proot --debug-infoBenchmark

Runningforengine myisam

Average number of seconds to run all queries:4.212seconds

Minimum number of seconds to run all queries:4.134seconds

Maximum number of seconds to run all queries:4.290seconds

Number of clients running queries:100Average number of queries per client:500Benchmark

Runningforengine innodb

Average number of seconds to run all queries:4.232seconds

Minimum number of seconds to run all queries:4.125seconds

Maximum number of seconds to run all queries:4.339seconds

Number of clients running queries:100Average number of queries per client:500

具体数值不用算了，TPS都在10000+

结论：

myisam和innodb两种引擎下，读时性能差不多，写时myisam比innodb要强很多

另外，读时TPS=10000+，这个结果我压根就不信，完全随机的读硬盘的话，硬盘的IOPS大概也就几百左右，因此10000+的结果原因可能为：

a: 测试表中数据太少，数据基本都被缓存了，直接都是从缓存中读的数据

b:mysqlslap生成的SQL语句不够随机，读时永远都是那么几条数据，肯定就命中缓存了

所以以上结果仅供参考罢了。。。。

参考文献中的作者思路很清晰，但用并发数=2来测试未免有点太小气了，所以他的数值肯定没有什么参考价值了