MySQL - 如何诊断警告“连接中止 -(读取通信数据包超时)"的原因 [英] MySQL - How to diagnose cause of warning "Aborted connection - (Got timeout reading communication packets)"

问题描述

我在运行 MySQL 的相当繁忙的 Windows 2008 R2 Web 服务器上遇到了一些问题.

I'm having some problems on our fairly busy Windows 2008 R2 web server, running MySQL.

应用程序间歇性崩溃，日志通常指向 MySQL 作为路由原因，因为应用程序无法连接到 MySQL.这不会影响所有用户，只会影响一些不幸的用户.

Applications are intermittently crashing, and logs are commonly pointing to MySQL as a route cause, in that the applications cannot connect to MySQL. This is not affecting all users, just a few unlucky ones.

在 MySQL 中打开警告日志后，我看到以下警告的重复日志:

Having turned on warning logging in MySQL, I am seeing repeated logs of the following warning:

[Warning] Aborted connection 7153 to db user: 'XXXX' host: 'XXXXXX' (Got timeout reading communication packets)

这里的文档 http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html 提出了一系列与网络相关的潜在原因.

The docs here http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html suggest a range of potential network related causes.

高级网络"下的一些配置:

Some of the configuation under "Advanced Networking":

最大数据包大小:128 M(我们从昨天的 64 M 增加了)等待超时:20最大连接数:1000最大连接数:1000重试次数:5

Max Packet Size: 128 M (We increased this from 64 M yesterday) Wait timeout: 20 Max Connections: 1000 Max Conn per: 1000 Retry count: 5

我的问题是，我怎样才能更好地诊断导致此问题的原因，而不是仅仅玩设置和等待?

My question is, how can I better diagnose what is causing this issue, rather than just playing with settings and waiting?

提前致谢

推荐答案

这是一个棘手的问题，但我们最终发现有几千个明显孤立的 TCP 连接打开(使用任务管理器 > 资源监视器)，这些连接已经由电子邮件发送过程生成.

This was a tricky issue to solve, but we eventually found a couple of thousand apparently orphaned TCP connections open (using Task Manager > Resource Monitor), which had been generated by an email send process.

由于 MySQL 连接依赖 TCP，因此数据库服务器受到了影响.

Since MySQL connections are TCP dependent, the db server was suffering as a result.

这篇关于MySQL - 如何诊断警告“连接中止 -(读取通信数据包超时)"的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！