Max connect timeout reached while reaching hostgroup 10 after 10000ms

原创

刘枫_Leo 2021-12-13 20:11:49 博主文章分类：故障处理 ©著作权

©著作权归作者所有：来自51CTO博客作者刘枫_Leo的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.应用日志报错

MySql.Data.MySqlClient.MySqlException (0x80004005): Max connect timeout reached while reaching hostgroup 10 after 10000ms”，同时MGR会有一个节点被剔除。

2. 架构 MGR+Proxy MySql 单写多读

3.数据库报错

2021-12-09T10:16:17.417127Z 131247 [Note] Aborted connection 131247 to db: 'uibot_rpa' user: 'root' host: '192.168.17.41' (Got an error reading communication packets)
2021-12-09T10:16:17.520003Z 158153 [Note] Aborted connection 158153 to db: 'uibot_rpa' user: 'root' host: '192.168.17.41' (Got an error reading communication packets)
2021-12-09T10:16:21.104089Z 136874 [Note] Aborted connection 136874 to db: 'uibot_rpa' user: 'root' host: '192.168.17.42' (Got an error reading communication packets)
2021-12-09T10:16:21.342577Z 294755 [Note] Aborted connection 294755 to db: 'uibot_rpa' user: 'root' host: '192.168.17.42' (Got an error reading communication packets)
2021-12-09T10:16:28.315281Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5948ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2021-12-09T10:16:28.507170Z 0 [ERROR] Plugin group_replication reported: 'Member was expelled from the group due to network failures, changing member status to ERROR.'
2021-12-09T10:16:28.653245Z 0 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2021-12-09T10:16:30.990102Z 0 [ERROR] Plugin group_replication reported: 'Member was expelled from the group due to network failures, changing member status to ERROR.'
2021-12-09T10:16:30.990617Z 0 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2021-12-09T10:16:59.623028Z 310224 [Note] Aborted connection 310224 to db: 'uibot_rpa' user: 'root' host: '192.168.17.42' (Got an error reading communication packets)
2021-12-09T10:30:47.436867Z 310590 [Note] Got an error reading communication packets
2021-12-09T10:36:03.573860Z 310725 [Note] Got an error reading communication packets

4.初步判断

当应用日志出现报错时伴随着 MGR 掉1台。
MGR在掉之前 有个日志输出"[Note] InnoDB: page_cleaner: 1000ms intended loop took 5948ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)"
判断 ：单写多读 一般这个日志提示的数据库IO压力告警，如果是IO或参数设置 导致数据库长时间无法应答MGR的心跳，MGR重新选择主库。这个过程 应用就会出现连接超时。

5.参数优化

innodb_buffer_pool_size=空间设置为物理内存的50%-75%
innodb_buffer_pool_instances=2
innodb_log_file_size=512M  /*需要看目前设置，在决定大小*/
innodb_log_files_in_group=2
innodb_page_cleaners=2