mysql4核_RDSMySQL5.74核和8核共享规格性能压测阿里云开发者社区

写在前面

这次压测只用了一台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

性能对比图