写在前面
这次压测只用了一台ECS&＃xff0c;也没有调整各种压测比和压测参数&＃xff0c;把实例压到最高&＃xff1b;这里的压测只是为了说明不同的参数对性能影响&＃xff0c;要想得出一个结论&＃xff0c;必须要在可对比的情况下&＃xff0c;比如&＃xff1a;实例规格一样&＃xff0c;从压测机到被压测机网络延迟一样&＃xff0c;MySQL的配置文件一样等等&＃xff0c;否则都是耍流氓。
压测环境
被压测的RDS实例规格
MySQL 5.7 物理机版 新规格8核32G
MySQL 5.7 物理机版 新规格4核16G
RDS主要参数配置
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");
&＃43;--------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;--------------------------------&＃43;-------&＃43;
| 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 |
&＃43;--------------------------------&＃43;-------&＃43;
关于参数
网络延迟
从压测机ping RDS
64 bytes from *** (***): icmp_seq&＃61;1 ttl&＃61;64 time&＃61;1.69 ms
64 bytes from *** (***): icmp_seq&＃61;2 ttl&＃61;64 time&＃61;1.72 ms
64 bytes from *** (***): icmp_seq&＃61;3 ttl&＃61;64 time&＃61;1.72 ms
64 bytes from *** (***): icmp_seq&＃61;4 ttl&＃61;64 time&＃61;1.73 ms
64 bytes from *** (***): icmp_seq&＃61;5 ttl&＃61;64 time&＃61;1.73 ms
64 bytes from *** (***): icmp_seq&＃61;6 ttl&＃61;64 time&＃61;1.72 ms
64 bytes from *** (***): icmp_seq&＃61;7 ttl&＃61;64 time&＃61;1.73 ms
64 bytes from *** (***): icmp_seq&＃61;8 ttl&＃61;64 time&＃61;1.73 ms
64 bytes from *** (***): icmp_seq&＃61;9 ttl&＃61;64 time&＃61;1.72 ms
压测方法
sysbench /usr/share/sysbench/*** \
--mysql-host&＃61;*** \
--mysql-port&＃61;3306 \
--mysql-user&＃61;*** \
--mysql-password&＃61;&＃39;***&＃39; \
--mysql-db&＃61;*** \
--db-driver&＃61;mysql \
--tables&＃61;10 \
--table-size&＃61;1000000 \
--report-interval&＃61;10 \
--threads&＃61;128 \
--time&＃61;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");
&＃43;--------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;--------------------------------&＃43;-------&＃43;
| 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 |
&＃43;--------------------------------&＃43;-------&＃43;
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&＃61;128 --number-of-queries 10000 --iterations&＃61;5 --engine&＃61;innodb --debug-info -u*** -p*** -h*** -P3306 --create-schema&＃61;***
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&＃61;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");
&＃43;--------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;--------------------------------&＃43;-------&＃43;
| 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 |
&＃43;--------------------------------&＃43;-------&＃43;
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&＃61;128 --number-of-queries 10000 --iterations&＃61;5 --engine&＃61;innodb --debug-info -u*** -p*** -h*** -P*** --create-schema&＃61;***
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");
&＃43;--------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;--------------------------------&＃43;-------&＃43;
| 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 |
&＃43;--------------------------------&＃43;-------&＃43;
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&＃61;128 --number-of-queries 10000 --iterations&＃61;5 --engine&＃61;innodb --debug-info -u*** -p*** -h*** -P3306 --create-schema&＃61;***
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&＃61;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");
&＃43;--------------------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;--------------------------------&＃43;-------&＃43;
| 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 |
&＃43;--------------------------------&＃43;-------&＃43;
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
性能对比图
京公网安备 11010802041100号