MGR最优化配置推荐

写本篇文章的动机有三点：

1. 希望给想用MGR的朋友提供一个参数配置参考
2. 知数堂的学生更加需要一份MGR的参数手册
3. 在3306π社区广州站听了万里DB CTO娄帅关于《MGR Best Practice》的分享觉得也是时候总结一下了。

本次内容整理参考了3306π社区广州站《MGR Best Practice》万里DB CTO娄帅分享和一些个人的理解认识。本次总结内容如下：

• 一、MySQL InnoDB Cluster架构
• 二、MGR配置
  • 功能需求配置
  • MGR核心配置
  • 性能优化类参数
  • 配合使用的配置
  • InnoDB配置优化
• 三、MGR监控注意事项
• 四、MGR使用上的限制

一、MySQL InnoDB Cluster架构

上图引用于官方手册。该架构中包含：MySQL router, MySQL Servers, MySQL Shell。 MySQL router: 属于一个TCP模型的Proxy主要用于流量中转及基于端口号的读写分离。在3306π社区广州站听官方人员透漏基于shared方式的router也快要出来了，敬请期待。 MySQL Servers: 该部分即为MySQL Group Replication即MGR，这个MGR属于Single primary模式。 MySQL Shell: 用于搭建和管理MGR集群的一个客户端工具集。该工具基本是整合了原来官方的mysql utilis工具集。

二、MGR配置

目前对于使用MGR还是优先建议使用Single Primary结合mysql router使用。MGR本身的是使用InnoDB，所以对于性能方面优化更多还是要关注InnoDB优化，从另一方面讲MGR也是基于GTID复制的另一种实现，另外使用上也有一些限制，我们可以也可以通过明确相应的约束来避免不支持的功能。

2.1 功能需求配置

以下参数是MGR集群限制的必备参数：

• default_storage_engine=InnoDB 默认引擎使用InnoDB
• server-id 如果没使用mysqlsh搭建MGR，server_id是auto_increment_offset起始值，所以默认推荐使用1-9的数字，如果使用mysqlsh搭建，该值可以随意处理。
• binlog_format=row 使用row格式的binlog,这个基本是新功能的标配
• gtid_mode=on 启用GTID
• enforce_gtid_consistency=on 配合启用GTID使用
• sql_require_primary_key=on 每个表必须有主建
• binlog_cache_size=8M 通过binlog cache size大小把事务限制在8M以内，减少因为写大事务把MGR搞挂的问题。基于这个配置如果遇到大事务应用端直接会收到报错。
• binlog_checksum=none 低于8.0.21版本以前只能使用binlog_checksum=none,高于等于8.0.21可以启用binlog_checksum=crc32
• log_error_verbosity=3 MGR还属于新一代的架构，所以运行中建议把log_error_verbosity设置为3，把运行中的INFO也打印到error log中。

2.2 MGR核心配置

• group_replication_single_primary_mode=ON 推荐使用single primary节点，如果使用mysql router可以透明的实现把写传到mysql上。为了减少BUG，应用上失误建议使用single-primary
• group_replication_consistency=before 在MGR中如果对于single primary又是大部分时间主节点处理业务，该参数可以配置成: EVENTUAL从而获取最佳的性能；配置成before，是为了保证secondary节点在读写操作时，也不会读以延迟的数据; after是必须保证数据在其它节点应用完毕，这个目前不推荐使用；BEFORE_ON_PRIMARY_FAILOVER这个参数只是保证在故障接管时按BEFORE的逻辑走。目前这块比较完善的处理推荐：eventual 和 before，不推荐使用after。
• group_replication_group_seeds=node1_IP:PORT;node2_IP:PORT;node3_IP:port MGR节点成员通信的IP及端口后，所以有成员都要写里面。
• group_replication_bootstrap_group=off 只有在搭建新集群的过程中打开，新集群搭建完成后立刻关闭，避免节点重启后，搭建新的集群
• group_replication_transaction_size_limit=150000000 单个事务大小上限，尽量避免大事务出现，只会在commit时报错，事务执行过程中即使超过阈值也无感知。结合着我们上面提供的binlog_cache_size是8M，则我们在开启binlog的情况，可以把事务限制在8M以内。
• group_replication_communication_max_message_size=10M 大事务拆成多个包传输，拆包的大小为10M。将大事务切分成小包，进行paxos传递。
• group_replication_flow_control_mode=ON 流控开关，触发流控后，只会延迟等待1s。
• group_replication_flow_control_certifier_threshold=25000 触发流控的待认证的队列长度。
• group_replication_flow_control_applier_threshold = 25000 触发流控的待执行的队列长度。

2.3 性能优化类参数

MGR在数据应用实质上还用的sql_thread,所以这块的优化主要是针对复制优化相关的配置。

• binlog_transaction_dependency_tracking=writeset
• transaction_write_set_extraction=XXHASH64 启用sql_thread的writeset功能
• slave_parallel_worker = 8 并行复制中sql_thread线程数,推荐使用CPU core数的2倍即可
• slave_parallel_type=LOGICAL_CLOCK 配置复制基于事务的并行复制。
• slave_preserve_commit_order=ON 保证事务并行提交按主库上执行的顺序进行。
• slave_checkpoint_period=2
• slave_checkpoint_group=256 加快sql_thread执行过的信息更新到相关的表里，方便看到show slave status及相关监控的数据是更加准确的。

2.4 配合使用的配置

• group_replication_exit_state_action=READ_ONLY 集群成员退出或是少数派变为read_only不能提供写服务
• group_replication_unreachable_majority_timeout=10 网络分区时，少数派状等待此时长后，状态变为Error，回滚pending事务
• group_replication_autorejoin_tries=3 自动尝试连入集群的次数，尝试间隔5s（group_replication_member_expel_timeout），如果设置为0，表示禁用尝试。
• group_replication_member_expel_timeout=5 将suspicious节点踢出集群的等待时长，如果网络环境一般，可以适当调大30-60，不要太大。如果设置为0，表示不用等待直接进行ERROR状态
• group_replication_member_weight 选主过程中的节点权重，可以将主机房的节点权重加大
• clone_valid_donor_list 新加入节点，clone全量数据时，选择的donor节点，尽量使用非主节点作为donor节点

2.5 InnoDB配置优化

InnoDB部分参数供大家参考一下，就不在单独解释了。

• innodb_buffer_pool_size = 内存的50%-80%
• innodb_buffer_pool_instances = 4-8
• innodb_data_file_path = ibdata1:1024M:autoextend
• innodb_flush_log_at_trx_commit = 1
• innodb_log_buffer_size = 16M
• innodb_log_file_size = 1024M
• innodb_log_files_in_group = 4
• innodb_max_dirty_pages_pct = 75
• innodb_file_per_table = 1
• innodb_rollback_on_timeout
• innodb_io_capacity = 3000
• transaction_isolation = READ-COMMITTED
• innodb_flush_method = O_DIRECT
• innodb_thread_concurrency= 0
• innodb_print_all_deadlocks =on
• innodb_deadlock_detect =on
• innodb_lock_wait_timeout =30
• innodb_parallel_read_threads=4

三、MGR监控注意事项

• 本节点是不是online及成员角色: select member_host, member_port,member_state, member_role from replication_group_members where member_id=@@server_uuid; 或是 select member_id,member_host, member_port, member_state, member_role from replication_group_members;
• 当前节点是不是可以写： select * from performance_schema.global_variables where variable_name in ('read_only', 'super_read_only');
• 查看节点上的执行队列情况 select count_transactions_in_queue, count_transactions_remote_in_applier_queue from performance_schema.replication_group_member_stats;

四、MGR使用上的限制

1. 使用奇数个节点:3,5,7,9 最多9个成员。
2. 网络稳定，延迟低，尽量避免WAN部署。
3. 尽量使用单主模式。
4. 表必须有主键。
5. 必须使用InnoDB引擎。
6. BINLOG_FORMAT=ROW。
7. 必须启用GTID。
8. 禁止使用外键。
9. 不支持GAP LOCK ，MGR工作在RC模式。
10. 在多主模式下DML和DDL对同一个表的操作，必须在同一个节点进行，否则会导致集群会crash。
11. 在多主模式使用select for update可能会导致整个集群死锁。
12. 如果配合mysqlshell使用MySQL版本需要8.0.20及以上。
13. 不支持serializable事务模式。
14. MGR成员间不支持复制过滤规则。

目前MGR也是官方主推的MySQL InnoDB Cluster主要方案，现在使用的客户比较多，基本可以说比较稳。现在除了官方之外，国内万里数据库也在从事MGR的二次开发工作及商业支持。

如果你对你MGR有较大兴趣，也推荐关注GreatSQL社区公众号

本篇完毕，如果需要完善的地方，可以在评论交流，没有评论的公众号就没有灵魂，一起交流。 同时也希望对更多的朋友有帮助。