大家了解比较多apache的官方压力测试工具ab。其实MySQL从5.1.4开始提供一个官方的MySQL压力测试工具mysqlslap,模拟多个并发客户端访问mysql。
mysqlslap --help获取参数列表,更多请参考 官方手册
测试的过程需要生成测试表,插入测试数据,这个mysqlslap可以自动生成,默认生成一个mysqlslap的schema,如果已经存在则先删除,这里要注意了,不要用–create-schema指定已经存在的库,否则后果可能很严重。可以用–only-print来打印实际的测试过程:
$./mysqlslap -a --only-print
DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');
。。。。
INSERT INTO t1 VALUES (364531492,'qMa5SuKo4M5OM7ldvisSc6WK9rsG9E8sSixocHdgfa5uiiNTGFxkDJ4EAwWC2e4NL1BpAgWiFRcp1zIH6F1BayPdmwphatwnmzdwgzWnQ6SRxmcvtd6JRYwEKdvuWr');
DROP SCHEMA IF EXISTS `mysqlslap`;
可以看到最后由删除一开始创建的schema的动作,整个测试完成后不会在数据库中留下痕迹。假如我们执行一次测试,分别50和100个并发,执行1000次总查询,那么:
$./mysqlslap -a –c=50,100 --number-of-queries 1000 --debug-info -p
(我的DB连接需要密码)
Benchmark
Average number of seconds to run all queries: 0.147 seconds
Minimum number of seconds to run all queries: 0.147 seconds
Maximum number of seconds to run all queries: 0.147 seconds
Number of clients running queries: 1
Average number of queries per client: 1000
User time 0.03, System time 0.01
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 426, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 3268, Involuntary context switches 10
上结果可以看出,50和100个并发分别得到一次测试结果(Benchmark),并发数越多,执行完所有查询的时间越长。为了准确起见,可以多迭代测试几次:
$ mysqlslap -a –concurrency=50,100 –number-of-queries 1000 -iterations=5 --debug-info -p
Benchmark
Average number of seconds to run all queries: 0.380 seconds
Minimum number of seconds to run all queries: 0.377 seconds
Maximum number of seconds to run all queries: 0.385 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Average number of seconds to run all queries: 0.447 seconds
Minimum number of seconds to run all queries: 0.444 seconds
Maximum number of seconds to run all queries: 0.451 seconds
Number of clients running queries: 100
Average number of queries per client: 10
User time 1.44, System time 0.67
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 17922, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 36796, Involuntary context switches 4093
测试同时不同的存储引擎的性能进行对比:
$./mysqlslap -a -c=50,100 --number-of-queries 1000 -i 5 --engine=myisam,innodb --debug-info -p
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.086 seconds
Minimum number of seconds to run all queries: 0.078 seconds
Maximum number of seconds to run all queries: 0.093 seconds
Number of clients running queries: 2
Average number of queries per client: 500