写在前面
这次压测只用了一台ECS，也没有调整各种压测比和压测参数，把实例压到最高；这里的压测只是为了说明不同的参数对性能影响，要想得出一个结论，必须要在可对比的情况下，比如：实例规格一样，从压测机到被压测机网络延迟一样，MySQL的配置文件一样等等，否则都是耍流氓。
压测环境
被压测的RDS实例规格
MySQL 5.7 物理机版 新规格8核32G
MySQL 5.7 物理机版 新规格4核16GRDS主要参数配置
MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache");
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| have_query_cache | YES |
| innodb_flush_log_at_trx_commit | 1|2 |
| query_cache_type | OFF |
| rpl_semi_sync_master_enabled | ON|OFF |
| rpl_semi_sync_slave_enabled | OFF |
| sync_binlog | 1|1000 |
+--------------------------------+-------+关于参数
innodb_flush_log_at_trx_commit的作用可以参考：
MySQL · 参数故事 · innodb_flush_log_at_trx_commit
sync_binlog的作用可以参考：
MySQL · 答疑解惑 · binlog 位点刷新策略
MySQL · 引擎特性 · InnoDB 事务子系统介绍
半同步的作用可以参考：
MySQL · 源码分析 · MySQL BINLOG半同步复制数据安全性分析
MySQL · 源码分析 · MySQL 半同步复制数据一致性分析
网络延迟
从压测机ping RDS
64 bytes from *** (***): icmp_seq=1 ttl=64 time=1.69 ms
64 bytes from *** (***): icmp_seq=2 ttl=64 time=1.72 ms
64 bytes from *** (***): icmp_seq=3 ttl=64 time=1.72 ms
64 bytes from *** (***): icmp_seq=4 ttl=64 time=1.73 ms
64 bytes from *** (***): icmp_seq=5 ttl=64 time=1.73 ms
64 bytes from *** (***): icmp_seq=6 ttl=64 time=1.72 ms
64 bytes from *** (***): icmp_seq=7 ttl=64 time=1.73 ms
64 bytes from *** (***): icmp_seq=8 ttl=64 time=1.73 ms
64 bytes from *** (***): icmp_seq=9 ttl=64 time=1.72 ms压测方法
sysbench /usr/share/sysbench/*** \
--mysql-host=*** \
--mysql-port=3306 \
--mysql-user=*** \
--mysql-password='***' \
--mysql-db=*** \
--db-driver=mysql \
--tables=10 \
--table-size=1000000 \
--report-interval=10 \
--threads=128 \
--time=120 prepare/run/cleanup压测结果
8核32G 读写场景
双1
MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache");
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| have_query_cache | YES |
| innodb_flush_log_at_trx_commit | 1 |
| query_cache_type | OFF |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | OFF |
| sync_binlog | 1 |
+--------------------------------+-------+
6 rows in set (0.00 sec)
oltp_read_write.lua
SQL statistics:
transactions: 262843 (2187.92 per sec.)
queries: 5256860 (43758.44 per sec.)
General statistics:
total time: 120.1320s
total number of events: 262843
Latency (ms):
min: 20.91
avg: 58.45
max: 339.70
95th percentile: 86.00
sum: 15361894.55
# mysqlslap -a --concurrency=128 --number-of-queries 10000 --iterations=5 --engine=innodb --debug-info -u*** -p*** -h*** -P3306 --create-schema=***
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 11.504 seconds
Minimum number of seconds to run all queries: 9.150 seconds
Maximum number of seconds to run all queries: 16.737 seconds
Number of clients running queries: 128
Average number of queries per client: 78 sync_binlog:1000和trx_commit=2
MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache");
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| have_query_cache | YES |
| innodb_flush_log_at_trx_commit | 2 |
| query_cache_type | OFF |
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| sync_binlog | 1000 |
+--------------------------------+-------+
6 rows in set (0.00 sec)
Threads started!
#oltp_read_write.lua
SQL statistics:
queries performed:
read: 3809666
write: 1088476
other: 544238
total: 5442380
transactions: 272119 (2266.76 per sec.)
queries: 5442380 (45335.11 per sec.)
General statistics:
total time: 120.0462s
total number of events: 272119
Latency (ms):
min: 20.08
avg: 56.45
max: 339.44
95th percentile: 84.47
sum: 15361371.70
# mysqlslap -a --concurrency=128 --number-of-queries 10000 --iterations=5 --engine=innodb --debug-info -u*** -p*** -h*** -P*** --create-schema=***
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 9.238 seconds
Minimum number of seconds to run all queries: 8.394 seconds
Maximum number of seconds to run all queries: 9.624 seconds
Number of clients running queries: 128
Average number of queries per client: 78 只读场景
#oltp_point_select.lua
SQL statistics:
queries performed:
read: 11432749
write: 0
other: 0
total: 11432749
transactions: 11432749 (95255.06 per sec.)
queries: 11432749 (95255.06 per sec.)
General statistics:
total time: 120.0208s
total number of events: 11432749
Latency (ms):
min: 0.90
avg: 1.34
max: 18.23
95th percentile: 1.79
sum: 15351945.95 4核16G读写场景
双1
MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache");
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| have_query_cache | YES |
| innodb_flush_log_at_trx_commit | 1 |
| query_cache_type | OFF |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | OFF |
| sync_binlog | 1 |
+--------------------------------+-------+
oltp_read_write.lua
SQL statistics:
queries performed:
read: 1643950
write: 469700
other: 234850
total: 2348500
transactions: 117425 (977.46 per sec.)
queries: 2348500 (19549.17 per sec.)
General statistics:
total time: 120.1314s
total number of events: 117425
Latency (ms):
min: 28.19
avg: 130.88
max: 446.28
95th percentile: 155.80
sum: 15368416.16
# mysqlslap -a --concurrency=128 --number-of-queries 10000 --iterations=5 --engine=innodb --debug-info -u*** -p*** -h*** -P3306 --create-schema=***
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 12.546 seconds
Minimum number of seconds to run all queries: 12.445 seconds
Maximum number of seconds to run all queries: 12.657 seconds
Number of clients running queries: 128
Average number of queries per client: 78 sync_binlog:1000和trx_commit=2
mysql> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache");
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| have_query_cache | YES |
| innodb_flush_log_at_trx_commit | 2 |
| query_cache_type | OFF |
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| sync_binlog | 1000 |
+--------------------------------+-------+
oltp_read_write.lua
SQL statistics:
queries performed:
read: 1650866
write: 471676
other: 235838
total: 2358380
transactions: 117919 (981.66 per sec.)
queries: 2358380 (19633.27 per sec.)
General statistics:
total time: 120.1198s
total number of events: 117919
Latency (ms):
min: 21.31
avg: 130.32
max: 439.36
95th percentile: 155.80
sum: 15366638.87 只读场景
oltp_point_select.lua
SQL statistics:
queries performed:
read: 4924841
write: 0
other: 0
total: 4924841
transactions: 4924841 (41031.29 per sec.)
queries: 4924841 (41031.29 per sec.)
General statistics:
total time: 120.0248s
total number of events: 4924841
Latency (ms):
min: 0.90
avg: 3.12
max: 65.22
95th percentile: 28.16
sum: 15356974.94 性能对比图
