一则 TCP 缓存超负荷导致的 MySQL 连接中断的案例分析
除了 MySQL 本身之外，如何分析定位其他因素的可能性？
作者：龚唐杰，爱可生 DBA 团队成员，主要负责 MySQL 技术支持，擅长 MySQL、PG、国产数据库。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 1200 字，预计阅读需要 3 分钟。
背景
在执行跑批任务的过程中，应用程序遇到了一个问题：部分任务的数据库连接会突然丢失，导致任务无法完成。从数据库的错误日志中，发现了 Aborted connection 的信息，这说明客户端和服务器之间的通信被异常中断了。
分析
为了找出问题的原因，我们首先根据经验，分析了可能导致连接被 Aborted 的几种常见情况：
- 客户端没有正确地关闭连接，没有调用 mysql_close() 函数。
- 客户端空闲时间超过了 wait_timeout 或 interactive_timeout 参数的秒数，服务器自动断开了连接。
- 客户端发送或接收的数据包大小超过了 max_allowed_packet 参数的值，导致连接中断。
- 客户端试图访问数据库，但没有权限，或者使用了错误的密码，或者连接包不包含正确的信息。
然而，经过排查，发现以上情况都不适用于当前的问题。因为任务在之前都是正常运行的，而且程序也没有变动，所以可以排除第一种情况。查看了 MySQL 的超时参数 wait_timeout 和 interactive_timeout ，发现它们都是 28800，也就是 8 个小时，远远超过了任务执行时间，所以可以排除第二种情况。也检查了客户端和服务器的 max_allowed_packet 参数，发现它们都是 64M，也不太可能超过这个限制，所以可以排除第三种情况。我们也确认了客户端的数据库访问权限，密码，连接包等信息，都是正确的，所以可以排除第四种情况。
到此，我们初步感觉 MySQL 层面应该没有问题，问题可能出在其他地方。
为了进一步定位问题，我们尝试了修改服务器的一些相关内核参数，如下：
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 120
net.core.rmem_default = 2097152
net.core.wmem_default = 2097152
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_syn_backlog = 16384
这些参数主要是为了优化网络连接的性能和稳定性，避免连接被意外关闭或超时。但是，修改后的结果并没有改善，连接还是会异常中断。
最后，我们尝试了进行抓包分析，通过 Wireshark 工具，我们发现了一个异常的现象：服务器会给客户端发送大量的 ACK 包。如下图所示：
这些 ACK 包是 TCP 协议中的确认包，表示服务器已经收到了客户端的数据包，请求客户端继续发送数据。但是，为什么服务器会发送这么多的 ACK 包呢？我们猜测可能是网络有异常，导致客户端接收不到服务器返回的 ACK 包，所以服务器会反复发送 ACK 包，直到超时或收到客户端的响应。但是，经过网络人员的排查，未发现有明显的问题。
继续分析抓包，我们又发现了另一个异常的现象：客户端会给发送服务器一些窗口警告。如下图所示：
这些窗口警告是 TCP 协议中的流量控制机制，表示服务器或客户端的接收窗口已经满了，不能再接收更多的数据。
[TCP Window Full] 是发送端向接收端发送的一种窗口警告，表示已经到数据接收端的极限了
[TCP ZeroWindow] 是接收端向发送端发送的一种窗口警告，告诉发送者，接收端接收窗口已满，暂时停止发送。
根据以上信息，我们推测出了问题的原因：由于 MySQL 需要发送的数据太大，客户端的 TCP 缓存已经满了，所以需要等待客户端把 TCP 缓存里面的数据消化掉，才能继续接收数据。但是，在这段时间内，MySQL 会一直向客户端请求继续发送数据，如果客户端在一定时间内（默认是 60 秒）没有响应，MySQL 就会认为发送数据超时，中断了连接。
为了验证推测，查看 MySQL 的慢日志，发现了很多 Last_errno: 1161 的记录。
这些记录表示 MySQL 在发送数据时遇到了超时错误，而且发现出现的次数和应用程序失败的任务数很接近。根据 MySQL 官网的说明，这个错误的含义是：
Error number: 1161; Symbol: ER_NET_WRITE_INTERRUPTED; SQLSTATE: 08S01
Message: Got timeout writing communication packets
可知这个表示的意思是网络写入中断，而 MySQL 层面有个参数就是控制这个的，所以尝试更改 net_write_timeout 参数为 600，跑批任务正常运行。
所以 MySQL 连接被异常中断的原因在于客户端获取的数据库太大，超过了客户端 TCP 缓存，客户端需要先处理缓存中的数据，在这段时间内，MySQL 会一直向客户端请求继续发送数据，但是客户端 60 秒内一直未能响应，导致 MySQL 发送数据超时，中断了连接。
结论
通过上述的分析和尝试，我们得出了以下的结论：
- 抓包信息中，有很多 ACK 信息是因为客户端的缓存满了不能及时给服务端反馈，所以服务器会反复发送 ACK 信息，直到超过 60 秒（net_write_timeout 默认值是 60），导致 MySQL 把连接中断了。
- 慢日志中，有很多 Last_errno: 1161 的记录，是因为该 SQL 实际已经在 MySQL 中执行完毕了，但是在发送数据到客户端时，由于数据量太大超过了客户端的 TCP 缓存，然后客户端上的应用在 60 秒内未把缓存中的数据处理掉，导致 MySQL 往客户端发送数据超时。
- MySQL 层面调整 net_write_timeout 参数只能缓解这个现象，根因在于单个 SQL 获取的数据量太大，超过了客户端的缓存大小，应用程序不能短时间内处理完缓存中的数据，进而导致后续的数据发送超时。
优化建议
- 业务层面进行分批处理数据，避免单个 SQL 从服务器获取大量的数据，导致客户端的 TCP 缓存不足。
- 提高 MySQL 中的 net_write_timeout 参数或者增加客户端的 TCP 缓存，可缓解此情况的发生，但不能彻底解决该问题，因为数据量太大仍然会影响性能和稳定性。
- 优化 SQL 语句，减少不必要的数据返回，比如使用 LIMIT、WHERE 等条件，或者使用聚合函数，分组函数等，以减少数据量和提高查询效率。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
SQLE 获取
来源：https://my.oschina.net/actiontechoss/blog/11054532
本站大部分文章、数据、图片均来自互联网,一切版权均归源网站或源作者所有。
如果侵犯了您的权益请来信告知我们删除。邮箱：1451803763@qq.com