MySQL性能半同步复制VS异步复制

作者: 程序员面试吧 | 来源:发表于2020-07-25 20:35 被阅读0次

MySQL性能半同步复制VS异步复制
微服务MySQL改造
MySQL之：半同步复制
【MySQL】MySQL5.7半同步配置
MySQL半同步复制
mysql-高可用架构类设计中会遇到的问题???
(16)MGR
mysql 异步复制和半同步复制
Mysql 异步/同步/半同步复制
Mysql半同步复制

MySQL复制模式对比啦。都说半同步复制比异步复制性能慢了好多，为什么当前测试却差距这么小呢？
说一千道一万，那到底使用半同步复制好还是使用异步复制好呢？

性能测试报告

复制类型	每秒 TPS	每秒 QPS
半同步复制	760.35	15207.04
异步复制	837.05	16740.90

从当前性能测试来看其实半同步复制与异步复制差距并不大，只是略微有点差距
都说半同步复制比异步复制性能慢了好多，为什么当前测试却差距这么小呢？
原因一：半同步复制时只有一个slave库，同时 rpl_semi_sync_master_wait_for_slave_count 参数也是 1 （表示master接收到几个slave应答后才commit ），如果是一主二从，需要两个slave应答时性能自然会降低一些。

原因二：压测时一直是对master主库进行的，此时slave是没有大量查询的压力，响应接收binlog的ack自然会回复的比较快，如果在slave有较大的压力时，性能也会再次降低。

说一千道一万，那到底使用半同步复制好还是使用异步复制好呢？

举个栗子：如果你是一主三从架构，你可以一个从库使用半同步复制，其余两个从库使用异步复制。当前最大限制保证了数据安全和数据的一致性。

半同步复制压力测试

角色	实例
master	3306
slave	3307

1. 建立GTID半同步复制

# 主库查看GTID开启状态
3306 > show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

# 从库查看GTID开启状态
3307 > show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

# 主库安装半同步插件 -------------------------------
3306 > install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

3306 > select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi_sync%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

3306 > set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

# 从库安装半同步插件 -------------------------------
3307 > install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.06 sec)

3307 > select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi_sync%';
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

3307 > set global rpl_semi_sync_slave_enabled= 1;
Query OK, 0 rows affected (0.00 sec)

# 主库创建复制用户 --------------------------------------------------------------
3306 > grant replication slave on *.* to repl@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 从库建立复制通道
3307 > CHANGE MASTER TO
          MASTER_HOST='10.186.61.30',
          MASTER_USER='repl',
          MASTER_PASSWORD='123456',
          MASTER_PORT=3306,
          MASTER_CONNECT_RETRY=10,
          MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

3307 > start slave;
Query OK, 0 rows affected (0.00 sec)

2. 压力测试半同步模式数据库

》》点击免费领取：【阿里云】深入浅出Kubernetes项目实战手册（超详细127页）
https://apprhKAAI3V6603.h5.xeknow.com/st/9W8SJYDIn

# 主库创建压力测试的数据库
3306 > create database testdb;
Query OK, 1 row affected (0.00 sec)

# 主库创建压力测试用户
3306 > grant all on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

# 模拟测试数据
[root@db ~]# sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.186.61.30 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --oltp-table-size=200000 --oltp-tables-count=8 --threads=4 --events=0 --time=3600 --report-interval=3 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Creating table 'sbtest1'...
Inserting 200000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 200000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 200000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 200000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 200000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 200000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 200000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 200000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...

# 压力测试 60 秒
[root@db ~]# sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.186.61.30 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --oltp-table-size=200000 --oltp-tables-count=8 --threads=4 --events=0 --time=60 --report-interval=3 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 3 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 3s ] thds: 4 tps: 599.32 qps: 11998.32 (r/w/o: 8401.09/2397.26/1199.96) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 4 tps: 740.06 qps: 14808.55 (r/w/o: 10364.19/2964.24/1480.12) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 4 tps: 761.70 qps: 15239.62 (r/w/o: 10668.43/3047.79/1523.39) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 4 tps: 766.66 qps: 15327.62 (r/w/o: 10730.97/3063.32/1533.33) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 770.31 qps: 15407.89 (r/w/o: 10784.03/3083.25/1540.62) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 4 tps: 775.37 qps: 15504.64 (r/w/o: 10854.78/3099.13/1550.73) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 4 tps: 796.65 qps: 15931.60 (r/w/o: 11151.39/3187.25/1592.96) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 4 tps: 766.31 qps: 15325.15 (r/w/o: 10726.31/3065.90/1532.95) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 4 tps: 750.69 qps: 15016.21 (r/w/o: 10512.05/3002.78/1501.39) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 767.35 qps: 15349.98 (r/w/o: 10745.89/3069.40/1534.70) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 4 tps: 744.96 qps: 14894.10 (r/w/o: 10425.70/2978.49/1489.91) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 4 tps: 752.64 qps: 15050.90 (r/w/o: 10535.03/3010.58/1505.29) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 4 tps: 786.04 qps: 15721.48 (r/w/o: 11006.57/3142.83/1572.08) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 4 tps: 782.34 qps: 15649.87 (r/w/o: 10955.81/3129.37/1564.69) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 4 tps: 790.66 qps: 15810.29 (r/w/o: 11063.97/3164.99/1581.33) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 4 tps: 741.67 qps: 14829.09 (r/w/o: 10381.40/2964.35/1483.34) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 4 tps: 773.67 qps: 15474.64 (r/w/o: 10831.31/3095.99/1547.33) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 4 tps: 778.59 qps: 15580.14 (r/w/o: 10908.26/3114.69/1557.18) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 4 tps: 801.73 qps: 16031.28 (r/w/o: 11219.90/3207.92/1603.46) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 761.29 qps: 15228.56 (r/w/o: 10660.79/3045.18/1522.59) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            638806
        write:                           182516
        other:                           91258
        total:                           912580
    transactions:                        45629  (760.35 per sec.)
    queries:                             912580 (15207.04 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0090s
    total number of events:              45629

Latency (ms):
         min:                                    2.87
         avg:                                    5.26
         max:                                  139.25
         95th percentile:                        8.58
         sum:                               239890.13

Threads fairness:
    events (avg/stddev):           11407.2500/82.92
    execution time (avg/stddev):   59.9725/0.00

异步复制压力测试

角色	实例
master	3306
slave	3307

1. 切换GTID异步复制

# 关闭 主库 半同步插件
3306 > set global rpl_semi_sync_master_enabled = 0;
Query OK, 0 rows affected (0.31 sec)

3306 > show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

# 关闭 从库 半同步插件 -------------------------------
3307 > set global rpl_semi_sync_slave_enabled= 0;
Query OK, 0 rows affected (0.00 sec)

3307 > stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

3307 > start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

3307 > show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

2.压力测试异步模式数据库

[root@db ~]# sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=10.186.61.30 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --oltp-table-size=200000 --oltp-tables-count=8 --threads=4 --events=0 --time=60 --report-interval=3 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 3 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 3s ] thds: 4 tps: 805.14 qps: 16122.80 (r/w/o: 11286.63/3224.56/1611.61) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 4 tps: 891.19 qps: 17819.06 (r/w/o: 12475.28/3561.41/1782.37) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 4 tps: 838.69 qps: 16775.78 (r/w/o: 11742.98/3355.42/1677.38) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 4 tps: 855.24 qps: 17100.48 (r/w/o: 11969.37/3420.63/1710.48) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 856.08 qps: 17122.62 (r/w/o: 11987.13/3423.32/1712.16) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 4 tps: 834.35 qps: 16683.96 (r/w/o: 11678.20/3337.39/1668.36) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 4 tps: 836.93 qps: 16733.92 (r/w/o: 11712.34/3347.72/1673.86) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 4 tps: 849.73 qps: 17006.66 (r/w/o: 11906.93/3399.93/1699.80) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 4 tps: 847.67 qps: 16953.77 (r/w/o: 11865.41/3393.02/1695.34) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 841.00 qps: 16808.31 (r/w/o: 11765.98/3360.66/1681.66) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 4 tps: 811.29 qps: 16238.80 (r/w/o: 11366.73/3249.16/1622.91) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 4 tps: 809.04 qps: 16175.85 (r/w/o: 11324.93/3232.84/1618.08) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 4 tps: 843.62 qps: 16870.73 (r/w/o: 11809.68/3373.81/1687.24) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 4 tps: 841.00 qps: 16826.31 (r/w/o: 11778.65/3365.66/1682.00) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 4 tps: 820.69 qps: 16411.52 (r/w/o: 11487.36/3282.77/1641.39) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 4 tps: 830.99 qps: 16623.45 (r/w/o: 11635.18/3326.29/1661.98) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 4 tps: 838.67 qps: 16763.33 (r/w/o: 11734.33/3352.00/1677.00) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 4 tps: 822.67 qps: 16456.73 (r/w/o: 11521.37/3289.68/1645.67) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 4 tps: 834.70 qps: 16696.98 (r/w/o: 11686.78/3340.80/1669.40) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 834.58 qps: 16684.62 (r/w/o: 11679.47/3335.99/1669.16) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            703290
        write:                           200940
        other:                           100470
        total:                           1004700
    transactions:                        50235  (837.05 per sec.)
    queries:                             1004700 (16740.90 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0132s
    total number of events:              50235

Latency (ms):
         min:                                    2.53
         avg:                                    4.78
         max:                                  131.93
         95th percentile:                        7.98
         sum:                               239893.00

Threads fairness:
    events (avg/stddev):           12558.7500/336.17
    execution time (avg/stddev):   59.9732/0.00

》》点击免费领取：【阿里云】深入浅出Kubernetes项目实战手册（超详细127页）
https://apprhKAAI3V6603.h5.xeknow.com/st/9W8SJYDIn

MySQL性能半同步复制VS异步复制
MySQL复制模式对比啦。都说半同步复制比异步复制性能慢了好多，为什么当前测试却差距这么小呢？说一千道一万，那到底...
微服务MySQL改造
MySQL 主从复制之半同步模式MYSQL半同步概念：一般情况下Mysql默认复制模式为异步异步复制模式主服务...
MySQL之：半同步复制
半同步复制默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把 binlog 日志发送给...
【MySQL】MySQL5.7半同步配置
前言：在MySQL主从复制中分为异步复制及半同步复制，不像Oracle分为最大性能模式，最大可用模式，最大保护模式...
MySQL半同步复制
半同步复制从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asyn...
mysql-高可用架构类设计中会遇到的问题???
高可用架构类设计问题一: mysql 的主从复制是如何工作的??? mysql 主从复制的实现原理异步复制半同步...
(16)MGR
问题：MySQL高可用方案，异步和半同步复制，难可用和不丢数据间取舍，MGR基于Paxos多数派，作为半同步复制的...
mysql 异步复制和半同步复制
在MySQL5.5之前，MySQL 的复制是异步操作，主库和从库的数据之间存在一定的延迟，这样存在一个隐患:当在主...
Mysql 异步/同步/半同步复制
Mysql复制异步逻辑 MySQL 默认的复制就是异步的，主库再执行完客户端提交的事务后会立即将结果返回给客户...
Mysql半同步复制
从MySQL5.5开始，MySQL以插件的形式支持半同步复制。如何理解半同步呢？首先我们来看看异步，全同步的概念 ...

MySQL性能半同步复制VS异步复制

性能测试报告

半同步复制压力测试

1. 建立GTID半同步复制

2. 压力测试半同步模式数据库

异步复制压力测试

1. 切换GTID异步复制

2.压力测试异步模式数据库

相关文章

MySQL性能半同步复制VS异步复制

微服务MySQL改造

MySQL之：半同步复制

【MySQL】MySQL5.7半同步配置

MySQL半同步复制

mysql-高可用架构类设计中会遇到的问题???

(16)MGR

mysql 异步复制和半同步复制

Mysql 异步/同步/半同步复制

Mysql半同步复制

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读