MGR最优化配置推荐-一一网

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

希望给想用MGR的朋友提供一个参数配置参考
知数堂的学生更加需要一份MGR的参数手册
在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;