最近,将部分开发和测试环境的mysql升级到5.7之后,今天抽时间测试了下5.6和5.7 PK查询的性能,使用mysqlslap进行测试,测试结果发现在低配下,percona 5.6.31大约比5.7.15快20-30%左右,percona 5.7.15比mysql企业版5.7.16快10%左右,如下:
--mysql企业版
mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
--percona 5.7.15
[root@dev-server1 ~]# mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 66.628 seconds
Minimum number of seconds to run all queries: 66.628 seconds
Maximum number of seconds to run all queries: 66.628 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@dev-server1 ~]# mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 66.278 seconds
Minimum number of seconds to run all queries: 66.278 seconds
Maximum number of seconds to run all queries: 66.278 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
--percona 5.6.31
[root@dev-server1 ~]# mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 55.878 seconds
Minimum number of seconds to run all queries: 55.878 seconds
Maximum number of seconds to run all queries: 55.878 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@dev-server1 ~]# mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 57.169 seconds
Minimum number of seconds to run all queries: 57.169 seconds
Maximum number of seconds to run all queries: 57.169 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@dev-server1 ~]# mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 51.820 seconds
Minimum number of seconds to run all queries: 51.820 seconds
Maximum number of seconds to run all queries: 51.820 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@dev-server1 ~]# mysqlslap -h172.18.30.62 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 52.817 seconds
Minimum number of seconds to run all queries: 52.817 seconds
Maximum number of seconds to run all queries: 52.817 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
因为mysql 5.7相比mysql 5.6增加了很多新的特性、同时新增了很多后台线程,所以在达到mysql 5.6的最大并发之前,mysql 5.6在性能上比mysql 5.7更高并不奇怪。因为我们大量服务器部署于阿里云和虚拟化环境,所以不打算也不计划测试在64GB以上的环境。后续会测试在16C/16GB和16C/32GB环境下两者的情况。
上述测试基于centos 7.2,linux内核3.10。
同时,需要注意的是,在不同的vm实例中,即使配置和型号完全相同,也有可能出现TPS上下相差10-20%的情况,这一点尤其需要注意,因为任何时候cpu/内存/服务器进程/线程等都可能在不同程度的运行,即使是空环境亦如此。另外,还需要注意不同的linux/内核版本可能导致性能相差比较大。
在另一个centos 6.3,内核2.6.32的VM实例中,percona 5.6.31的测试如下(平均TPS达到22000)(7.0相同配置下平均18000左右):
[root@dev-server1 ~]# mysqlslap -h172.18.30.193 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -pLd123123
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 42.936 seconds
Minimum number of seconds to run all queries: 42.936 seconds
Maximum number of seconds to run all queries: 42.936 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@dev-server1 ~]# mysqlslap -h172.18.30.193 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -pLd123123
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 43.359 seconds
Minimum number of seconds to run all queries: 43.359 seconds
Maximum number of seconds to run all queries: 43.359 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@dev-server1 ~]# mysqlslap -h172.18.30.193 -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -pLd123123
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 47.033 seconds
Minimum number of seconds to run all queries: 47.033 seconds
Maximum number of seconds to run all queries: 47.033 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
------------晚上临时找了两个环境,测试了下centos 6.5 8C/16GB阿里云下,如下-------------------
percona 5.7.15
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 28.632 seconds
Minimum number of seconds to run all queries: 28.632 seconds
Maximum number of seconds to run all queries: 28.632 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=400 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 26.167 seconds
Minimum number of seconds to run all queries: 26.167 seconds
Maximum number of seconds to run all queries: 26.167 seconds
Number of clients running queries: 400
Average number of queries per client: 2500
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# 2016-11-10T11:37:06.099118Z mysqld_safe mysqld from pid file /usr/local/Percona-Server-5.7.15-9-Linux.x86_64.ssl101/data/iZbp14bngbtxcrrha173qvZ.pid ended
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=400 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 26.266 seconds
Minimum number of seconds to run all queries: 26.266 seconds
Maximum number of seconds to run all queries: 26.266 seconds
Number of clients running queries: 400
Average number of queries per client: 2500
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=400 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 25.370 seconds
Minimum number of seconds to run all queries: 25.370 seconds
Maximum number of seconds to run all queries: 25.370 seconds
Number of clients running queries: 400
Average number of queries per client: 2500
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]#
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=600 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 24.399 seconds
Minimum number of seconds to run all queries: 24.399 seconds
Maximum number of seconds to run all queries: 24.399 seconds
Number of clients running queries: 600
Average number of queries per client: 1666
=====================
percona 5.6.31
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=200 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 29.882 seconds
Minimum number of seconds to run all queries: 29.882 seconds
Maximum number of seconds to run all queries: 29.882 seconds
Number of clients running queries: 200
Average number of queries per client: 5000
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=400 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 25.210 seconds
Minimum number of seconds to run all queries: 25.210 seconds
Maximum number of seconds to run all queries: 25.210 seconds
Number of clients running queries: 400
Average number of queries per client: 2500
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=400 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 26.084 seconds
Minimum number of seconds to run all queries: 26.084 seconds
Maximum number of seconds to run all queries: 26.084 seconds
Number of clients running queries: 400
Average number of queries per client: 2500
[root@iZbp11t4fzcyfwa9k5eb77Z ~]# mysqlslap -hXXX -P3306 --concurrency=600 --create-schema='spider' --query='select * from test_250byte where id=1;' --number-of-queries=1000000 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 24.896 seconds
Minimum number of seconds to run all queries: 24.896 seconds
Maximum number of seconds to run all queries: 24.896 seconds
Number of clients running queries: 600
Average number of queries per client: 1666
基本上可以看出,在8C的配置下,mysql 5.6和5.7的性能已经比较接近的,按说配置再增加的情况下,5.7的扩展性会比5.6更好,且新增的很多额外线程的消耗会被抵销。