MySQL数据库连接超时问题排查报告

1、问题描述

边端设备访问云端过程中有概率出现MySQL数据库连接超时报错，具体报错代码如下：

[2024-08-13 13:47:44,036] ERROR in app: Exception on /est-tasks/start [POST] Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute result = self._query(query) File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query conn.query(q) File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result result.read() File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 748, in _read_packet raise err.OperationalError( pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query') The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1463, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 872, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 870, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 855, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) # type: ignore[no-any-return] File "/flask/app/api/est_task.py", line 99, in send_test_start RequestDao().create([request_record]) File "/flask/app/dao/base_dao.py", line 268, in create model = schema.load(obj, partial=True) File "/usr/local/lib/python3.10/site-packages/marshmallow_sqlalchemy/load_instance_mixin.py", line 100, in load return super().load(data, **kwargs) File "/usr/local/lib/python3.10/site-packages/marshmallow/schema.py", line 722, in load return self._do_load( File "/usr/local/lib/python3.10/site-packages/marshmallow/schema.py", line 897, in _do_load result = self._invoke_load_processors( File "/usr/local/lib/python3.10/site-packages/marshmallow/schema.py", line 1095, in _invoke_load_processors data = self._invoke_processors( File "/usr/local/lib/python3.10/site-packages/marshmallow/schema.py", line 1225, in _invoke_processors data = processor(data, many=many, **kwargs) File "/usr/local/lib/python3.10/site-packages/marshmallow_sqlalchemy/load_instance_mixin.py", line 76, in make_instance instance = self.instance or self.get_instance(data) File "/usr/local/lib/python3.10/site-packages/marshmallow_sqlalchemy/load_instance_mixin.py", line 60, in get_instance return self.session.get(self.opts.model, filters) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/scoping.py", line 1060, in get return self._proxied.get( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3637, in get return self._get_impl( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3817, in _get_impl return db_load_fn( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 694, in load_on_pk_identity session.execute( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2306, in execute return self._execute_internal( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal result: Result[Any] = compile_state_cls.orm_execute_statement( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement result = conn.execute( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1422, in execute return meth( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection return connection._execute_clauseelement( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement ret = self._execute_context( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context return self._exec_single_context( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context self._handle_dbapi_exception( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute result = self._query(query) File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query conn.query(q) File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result result.read() File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 748, in _read_packet raise err.OperationalError( sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: SELECT core_device_request.device_number AS core_device_request_device_number, core_device_request.request_id AS core_device_request_request_id, core_device_request.request_type AS core_device_request_request_type, core_device_request.start_time AS core_device_request_start_time, core_device_request.end_time AS core_device_request_end_time, core_device_request.state AS core_device_request_state, core_device_request.created_at AS core_device_request_created_at, core_device_request.created_by AS core_device_request_created_by, core_device_request.updated_at AS core_device_request_updated_at, core_device_request.updated_by AS core_device_request_updated_by, core_device_request.is_deleted AS core_device_request_is_deleted FROM core_device_request WHERE core_device_request.request_id = %(pk_1)s] [parameters: {'pk_1': '99da21c6-e76f-4595-b0a6-32061934938d'}] (Background on this error at: https://sqlalche.me/e/20/e3q8)

该报错在云端所有接口调用中都有出现，出现频率不定，有时频发有时偶发，并且具有两个特点

1. 发生超时错误后不进行任何操作，一段时间后会服务自动恢复正常

2. 发生超时错误后重启数据库或licloud-api服务，服务立即恢复正常

3. 发生超时错误时licloud-api所在主机与MySQL数据库主机之间网络连接正常

4. 发生超时错误时可以正常使用Navicat、DataGrip等GUI工具连接至MySQL数据库

2、排查过程

频繁发生超时问题后技术部采取了如下措施进行问题排查

2.1 协调移动云技术支持排查网络日志

最初发生该问题时，怀疑是移动云服务器网络连接问题，联系移动云技术支持人员进行问题排查，跟踪观察了licloud-api服务器与mysql服务器之间的网络流量，未发现有明显问题，根据移动云技术人员的建议，修改了如下配置：

2.1.1 排查网络带宽问题

1. 数据传输速度：如果数据库服务器和客户端之间的网络带宽有限，大数据的传输速度可能会变慢。这可能导致客户端在尝试读取或写入大量数据时超过了 net_read_timeout 或 net_write_timeout 的设置，从而导致连接超时。

2. 响应时间：网络带宽限制可能导致数据包的延迟增加，尤其是在数据量大或者连接数多的情况下。这可能使得客户端请求的响应时间变长，进而可能触发超时设置

解决方案: 增加网络带宽从原有的50MB,增加到100M

超时问题没有改善

2.1.2 修改无状态安全组

由于底层设备有个会话3600s老化的机制,两端没有交互的话会话是会断开的,所以需要配置keepalived或者改成无状态安全组.

配置内核里的这个参数net.ipv4.tcp_keepalive_time,内核会发送保活探测包检查连接是否仍然有效

net.ipv4.tcp_keepalive_time 
net.ipv4.tcp_keepalive_intvl 
net.ipv4.tcp_keepalive_probes

 

 

超时问题没有改善

2.1.3 交互与非交互超时时间过长导致

mysql数据库服务器上部署的数据库访问异常问题，经过前期的排查服务器网络、服务器操作系统的TCP连接保活参数、数据库服务端的会话超时时间参数；

1、排查服务器网络正常，排除这个主机网络原因

2、根据我们的经验，可能是访问链路上不同节点的超时时间不一致导致MySQL会话超时，前期记录的数据是安全组存在3600s的超时时间、服务器系统默认是7200s的会话超时时间，数据库服务端配置的是30天的会话超时时间，会出现TCP连接长时间没有交互的情况下，安全组已经中断连接的情况下，服务器和数据库服务端的连接依旧保活；7月29日调整了服务器操作系统TCP会话保活探测时间、8月2日调整了数据库服务端的会话超时时间为1200s，

[mysqld] wait_timeout = 1200 interactive_timeout = 1200 
#这里是8小时 我们改成了3小时

2.2 网络配置、服务配置排查优化

查阅网络资料以及参考移动云技术人员的建议，考虑了如下一些可能原因并进行了针对性优化

2.2.1 查询中大量数据被发送，由于数据传输时间不够导致

增加net_read_timeout的值。

修改 MySQL 的配置文件（通常是 my.cnf 或 my.ini 文件）。

在配置文件中，找到 [mysqld] 部分，并添加或修改以下行：

iniCopy Code [mysqld]net_read_timeout = 60 #这里是60 我们修改成了600

保存文件后，重新启动 MySQL 服务

超时问题没有改善

2.2.2 交互与非交互超时时间过短导致

1. wait_timeout：

   1. 作用：控制非交互式连接的超时时间。这类连接通常是由应用程序或脚本通过连接池管理的，比如 Web 应用与数据库之间的连接。

   2. 影响：如果一个连接在设定的 wait_timeout 秒内没有任何活动（例如查询、更新等），MySQL 会自动关闭这个连接。这可以避免长时间空闲的连接占用数据库资源。

2. interactive_timeout：

   1. 作用：控制交互式连接的超时时间。这类连接通常是用户直接通过命令行或 GUI 工具与数据库交互的连接。

   2. 影响：与 wait_timeout 类似，但 interactive_timeout 更适用于用户交互时的连接。如果一个用户连接在设定的 interactive_timeout 秒内没有任何活动，MySQL 会自动关闭这个连接。

在 MySQL 的配置文件（如 my.cnf 或 my.ini）中，设置 wait_timeout,interactive_timeout：

[mysqld] wait_timeout = 31536000 interactive_timeout = 31536000

# 这里是八小时 我们改成了30天

超时问题没有改善

2.2.3 初次连接时，连接时间设定太少

增加connect_timeout的值改善。

永久改变 connect_timeout 的值，使其在 MySQL 重启后仍然生效，需要修改 MySQL 的配置文件（通常是 my.cnf 或 my.ini 文件）。

在配置文件中，找到 [mysqld] 部分，并添加或修改以下行：

iniCopy Code [mysqld]connect_timeout = 20 # 这里是20 我们设置成120

保存文件后，重新启动 MySQL 服务

超时问题没有改善

2.2.4 BLOB值太大的问题

调整配置文件max_allowed_packet。

就更新大量的数据来说，可以进行两个方面的设置：将系统变量net_read_timeout设置得大一点，再将配置文件中的max_allowed_packet设置大一点。

max_allowed_packet 是 MySQL 服务器允许的最大数据包大小。默认情况下，这个值为 4MB。如果你的更新操作涉及到更大的数据量，例如大型 BLOB 数据的插入或更新，可能需要增加这个参数的设置。

在 MySQL 的配置文件（如 my.cnf 或 my.ini）中，设置 max_allowed_packet：

[mysqld] max_allowed_packet = 4M # 这里是4M ，设置 max_allowed_packet 为 100MB

超时问题没有改善

2.2.5 索引、连接池问题

根据移动云技术人员建议，考虑数据库创建时的索引和连接池配置

在flask项目中添加了如下代码；

SQLALCHEMY_ENGINE_OPTIONS = { "poolclass": QueuePool, "pool_size": 10, "max_overflow": 20, "pool_timeout": 30, "connect_args": {"connect_timeout": 10}, "pool_recycle": 8, }

后续没有发现过超时问题

2.3 腾讯云/阿里云服务器测试

为确定是移动云服务器还是代码导致报错，将相同代码分别部署在腾讯云、阿里云服务器上进行长时间测试，从2024年8月9日下午2-3时至2024年8月13日下午2-3时，期间每隔三分钟向云服务器服务发送一次模拟容量检测请求

从结果来看，腾讯云、阿里云服务器均未出现数据库连接超时及其他错误

 

2.4 本地开发服务器测试

为保障产品部正常进行新版上位机、边端的联调测试，将相同代码部署在本地开发服务器上

2024年8月8日至2024年8月13日的联调测试中未出现过数据库超时问题

3、问题分析及解决方案

根据排查及复现过程进行推论分析，可以确定造成数据库连接超时的原因是：Flask项目中没有正确配置Flask-sqlalchemy连接池参数，尤其是连接池中的连接回收时间，使得MySQL与Flask-sqlalchemy之间的连接被提前意外关闭

具体来说：

• 云端工作过程中，flask项目通过sqlalchemy和pymysql模块与mysql数据库建立连接，该连接以连接池的形式被sqlalchemy模块创建、维护

• sqlalchemy默认不会对连接池中的连接进行回收，即不会主动关闭长时间未进行任何操作、通讯的空闲连接

• mysql默认会关闭28800秒（8小时）内未进行任何操作、通讯的空闲连接

综合上述三点，当sqlalchemy连接池中某个连接在8小时内都没有任何操作时，mysql会主动关闭该连接，但是sqlalchemy并不知道该连接已经被关闭，在下一次需要连接数据库时仍然会调用一个实际数据库上已经被关闭的连接，就会出现连接mysql数据库超时的报错。

根据上述分析，在移动云和腾讯云主机上修改sqlalchemy的pool_recycle参数为-1（默认不回收连接，等价于超时时间为无限长）、mysql的wait_timeout为10秒，进行长时间测试，成功复现了数据库超时问题。

上述问题的解决方案是合理设置sqlalchemy连接池回收时间、mysql数据库的等待超时时间，目前将sqlalchemy的pool_recycle设置为1200秒，mysql数据库的wait_timeout设置为1800秒，在测试中未再发现超时报错。