Azure for MySQL单服务器偶发连接超时问题排查求助

我的Python（FastAPI）应用部署在AKS多副本集群中，对接Azure for MySQL单服务器管理的MySQL 8.0数据库，用于电商网站的商品目录同步与用户事件记录。应用日常时段使用率高、夜间几乎闲置，整体运行正常，但偶发出现错误码2003的连接超时错误，提示"Can't connect to MySQL server on ... (timed out)"。

当前使用sqlalchemy 1.4.41搭配pymysql 1.0.2，已设置pool_pre_ping=True来规避Azure MySQL低wait_timeout导致的连接失效问题，数据库连接配置代码如下：

self.engine = create_engine(
    db_uri,
    pool_pre_ping=True,
    connect_args={
        # To enable SSL, we need to pass the following non-empty dict.
        # We don't actually need any real SSL certificate (ssl_ca) or keys.
        "ssl": {"ssl_ca": ""}
    }
)
self.session = sessionmaker(autoflush=False, autocommit=False, bind=self.engine)

完整错误栈信息：

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 404, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/message_logger.py", line 86, in __call__
    raise exc from None
  File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/message_logger.py", line 82, in __call__
    await self.app(scope, inner_receive, inner_send)
  File "/usr/local/lib/python3.9/site-packages/fastapi/applications.py", line 270, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.9/site-packages/prometheus_fastapi_instrumentator/middleware.py", line 103, in __call__
    raise exc
  File "/usr/local/lib/python3.9/site-packages/prometheus_fastapi_instrumentator/middleware.py", line 101, in __call__
    await self.app(scope, receive, send_wrapper)
  File "/usr/local/lib/python3.9/site-packages/asgi_correlation_id/middleware.py", line 81, in __call__
    await self.app(scope, receive, handle_outgoing_request)
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
    raise exc
  File "/usr/local/lib/python3.9/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/usr/local/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 680, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 275, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 231, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 162, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/usr/local/lib/python3.9/site-packages/starlette/concurrency.py", line 41, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
  File "/usr/local/lib/python3.9/site-packages/anyio/to_thread.py", line 31, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
  File "/usr/local/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
    return await future
  File "/usr/local/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 867, in run
    result = context.run(func, *args)
  File "/app/./main.py", line 147, in register_session_event
    return handle_register_event(db, session_event)
  File "/app/./modules/events.py", line 61, in handle_register_event
    session.commit()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
    self._transaction.commit(_to_root=self.future)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3386, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3526, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3486, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 212, in save_obj
    for (
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 373, in _organize_states_for_save
    for state, dict_, mapper, connection in _connections_for_states(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1737, in _connections_for_states
    connection = uowtransaction.transaction.connection(base_mapper)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 626, in connection
    return self._connection_for_bind(bind, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 735, in _connection_for_bind
    conn = self._parent._connection_for_bind(bind, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3315, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 320, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 884, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 491, in checkout
    rec._checkin_failed(err, _fairy_was_created=False)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 488, in checkout
    dbapi_connection = rec.get_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 646, in get_connection
    self.__connect()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 678, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 673, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '<omitted>.mysql.database.azure.com' (timed out)")

已排查情况 #

• 错误为连接超时，触发于Web服务器处理请求10秒后，该时长为pymysql默认connect_timeout值；
• 错误发生时，包括出错Pod在内的所有Pod仍能正常处理其他请求，数据库处于可用状态；
• 错误并非高负载时触发，CPU、内存、IOPS、活跃连接等指标均正常，错误时间与高负载时段不匹配。

可能原因及解决方法 #

原因1：Azure MySQL单服务器的临时网络波动或连接节流 #

Azure云服务可能存在跨区域/可用区的临时网络抖动，或者单服务器的连接数阈值触发软限制（即使指标显示正常，可能存在瞬时的连接队列阻塞）。

解决方法：

• 调整pymysql的连接超时时间，避免10秒默认值直接触发超时，在connect_args中添加"connect_timeout": 15；
• 给SQLAlchemy连接池配置重试机制，设置pool_recycle（建议小于Azure MySQL的wait_timeout，比如设为300秒），同时升级SQLAlchemy到1.4.x最新版本，确保pool_pre_ping的重试逻辑正常工作；
• 检查Azure MySQL的连接节流日志，查看是否有瞬时的连接拒绝记录。

原因2：AKS Pod的网络策略或DNS解析延迟 #

AKS集群中，Pod的DNS解析可能存在偶发延迟，或者网络策略的临时规则生效导致出站连接受阻。

解决方法：

• 在Pod中添加定期的DNS解析测试脚本，验证对Azure MySQL域名的解析耗时；
• 检查AKS的网络策略，确保允许Pod访问Azure MySQL的3306端口；
• 考虑使用Azure MySQL的私有端点，减少跨公网的网络波动影响。

原因3：SQLAlchemy连接池的闲置连接复用问题 #

虽然设置了pool_pre_ping=True，但闲置过久的连接可能在ping操作时遇到网络超时，而此时连接池正在尝试创建新连接，导致瞬时的连接等待超时。

解决方法：

• 调整连接池的pool_size和max_overflow参数，根据Pod的QPS合理设置，避免连接池耗尽；
• 配置pool_timeout参数，设置连接池获取连接的等待超时时间（比如设为5秒），避免长时间等待无效连接；
• 开启连接池的reset_on_return=True，确保归还到池中的连接状态正常。

原因4：pymysql的SSL连接配置问题 #

当前SSL配置"ssl": {"ssl_ca": ""}可能存在兼容性问题，导致SSL握手阶段偶发超时。

解决方法：

• 使用Azure官方提供的SSL证书（DigiCertGlobalRootCA.pem），将证书文件打包到镜像中，配置"ssl": {"ssl_ca": "/path/to/DigiCertGlobalRootCA.pem"}；
• 测试关闭SSL连接（仅用于排查，生产环境不建议），验证是否还会出现超时问题。

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