Cloud Composer间歇性出现MySQL 2006连接丢失错误如何解决

运行版本为composer-1.16.5-airflow-1.10.14的Cloud Composer环境时，每日都会间歇性触发如下错误：
(2006, "Lost connection to MySQL server at 'reading initial communication packet', system error: 0")

已尝试的修复操作均未彻底解决问题：

• 清理Airflow元数据、修改业务代码（如将variable.get()替换为Jinja模板）降低数据库负载，故障仍每日出现
• 重启scheduler仅能维持2天正常运行，之后故障复现
• 监控面板显示Airflow数据库CPU、内存使用率始终平稳，但SQL实例会不定期进入不健康状态

完整错误栈 #

Traceback (most recent call last):
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 241, in _do_get
    return self._create_connection()
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/opt/python3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 208, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2006, "Lost connection to MySQL server at 'reading initial communication packet', system error: 0")

该错误触发阶段为新建MySQL连接时、读取服务端初始握手包环节，不属于查询执行阶段的连接中断，因此和数据库CPU、内存负载无直接关联，核心诱因集中在连接池配置不匹配、连接数耗尽、无效连接堆积三类场景，针对composer-1.16.5-airflow-1.10.14版本可按以下顺序修复：

1. 对齐连接池回收周期与Cloud SQL超时配置
   Airflow 1.10.14默认的SQLAlchemy连接回收周期pool_recycle为7200秒，而Cloud SQL for MySQL默认的空闲连接超时为3600秒，两者不匹配会导致连接池缓存大量已被服务端关闭的死连接，取出这类连接发起请求时就会触发断连。
   通过Composer环境变量覆盖以下配置即可解决：

• 设置sql_alchemy_pool_recycle = 1800（30分钟），确保连接在被Cloud SQL回收前就被客户端主动销毁重建
• 设置sql_alchemy_pool_pre_ping = True，每次从连接池取连接时先发送1字节的探活包，直接丢弃失效连接，避免使用死连接

2. 限制各组件连接池大小，避免总连接数打满Cloud SQL配额
   Cloud SQL的最大连接数与实例规格强绑定，Airflow 1.10.14默认不会给scheduler、worker、webserver单独设置连接池上限，多组件同时建立连接时很容易打满配额，此时MySQL会直接静默丢弃新的连接请求，触发初始通信包读取失败错误。
   按组件设置连接池大小，总连接数控制在Cloud SQL实例最大连接数的70%以内即可，常规规格实例可参考以下配置：

• scheduler：sql_alchemy_pool_size = 5
• worker：sql_alchemy_pool_size = 3
• webserver：sql_alchemy_pool_size = 10

3. 降低无效元数据库探活频率
   Airflow 1.10.14版本scheduler默认每5秒发起一次元数据库健康检查，长周期运行下会产生大量TIME_WAIT状态的TCP连接，占满宿主机临时端口后就会导致新连接握手失败。将scheduler_health_check_threshold调整为30秒，可大幅减少无效探活产生的临时连接堆积。

如果暂时无法调整配置，可通过每周定时重启scheduler、worker实例临时规避故障，但该方法无法彻底解决问题，调整上述连接池配置后即可完全消除故障。

内容的提问来源于stack exchange，提问作者Shreya srivastav