MySQL复制模式对比，都说半同步复制比异步复制性能慢了好多，为什么当前测试却差距这么小呢？

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

性能测试报告

复制类型每秒 TPS每秒 QPS
半同步复制760.3515207.04
异步复制837.0516740.90

•从当前性能测试来看其实半同步复制与异步复制差距并不大，只是略微有点差距•都说半同步复制比异步复制性能慢了好多，为什么当前测试却差距这么小呢？

原因一：半同步复制时只有一个slave库，同时 rpl_semi_sync_master_wait_for_slave_count 参数也是 1 （ 表示master接收到几个slave应答后才commit ），如果是一主二从，需要两个slave应答时性能自然会降低一些。

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

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

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

半同步复制压力测试

角色实例
master3306
slave3307

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. 压力测试半同步模式数据库

# 主库创建压力测试的数据库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 preparesysbench 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 runsysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:Number of threads: 4Report 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.00SQL 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

异步复制压力测试

角色实例
master3306
slave3307

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 runsysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:Number of threads: 4Report 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.00SQL 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


©著作权归作者所有：来自51CTO博客作者mob604756edd67c的原创作品，如需转载，请注明出处，否则将追究法律责任

更多相关文章

  1. java实操｜mysql数据增量同步到kafka
  2. 文件夹管理： mv cp rm touch mkdir LS 创建文件夹 移动 删除
  3. PHP的Excel导出与导入
  4. rsync+inotify实现服务器的实时同步
  5. Redis搭建和主从复制
  6. Redis常见问题
  7. 由于Linux系统文件同步时突然断电导致系统进入none模式问题详解
  8. postgresql12 流复制搭建
  9. [灾备]数据库容灾方式

随机推荐

  1. C#开发之微信小程序发送模板消息功能
  2. 在.NET Core类库中使用EF Core迁移数据库
  3. C#对Word文档的创建、插入表格、设置样式
  4. Asp.NET控制文件上传的大小方法(超简单)_
  5. c++如何获取数值极值的办法
  6. ADO调用分页查询存储过程的实例讲解_实用
  7. 关于c++中的引用总结
  8. asp.net部署到IIS常见问题的解决方法_实
  9. 实例详解sort()函数的原理和使用方法
  10. 基于MVC5中的Model层开发数据注解_实用技