mysql_ping挂起与Amazon RDS [英] mysql_ping hangs with Amazon RDS

问题描述

我想使从一个PHP工人外部Ubuntu的服务器上运行的持久连接到MySQL数据库的RDS。作为连接的一部分，我先检查mysql_ping。如果ping返回true，然后用当前的连接，否则创建一个新的连接。

I am trying to make a persistent connection to MySQL RDS database from a php worker running on an external ubuntu server. As a part of connection, i first check for "mysql_ping". If the ping returns true, then use the current connection, else make a new connection.

这是我现在面临的问题是，当我开始我的工作人员，在连接建立的罚款，并可以很好的一段时间，也就是我可以重复的查询到RDS数据库成功地。但大约15分钟后，当我提出一个要求，那么mysql_ping挂起，因此PHP脚本本身挂起。在进一步的调试，我发现mysql_ping响应回来假936秒后。

The problem that I am facing is that when I start my worker, the connection establishes fine, and works well for some time, i.e. I can make repeated queries to the RDS database sucessfully. However around 15 minutes later, if I make a request, then the "mysql_ping" hangs, and consequently the php script itself hangs. On further debugging, I found that the mysql_ping response came back as false after 936 seconds.

所需的权限数据库访问外部服务器在那里，在这么长的时间（当脚本挂），我能够成功连接到用mysql命令行选项的RDS数据库。另外，如果我杀了工人，并再次重新启动它，它能够成功连接一次，但15分钟左右后，问题就来了。

The required permissions for db access from external server are there and during all this time (while the script is hanging), I am able to successfully connect to the RDS database using mysql command line options. Also, if I kill the worker and restart it again, it is able to connect successfully once again, but the problem comes back after 15 minutes or so.

有什么需要在RDS服务器设置进行更改，以避免这个问题？

Is there anything that needs to be changed in the RDS server settings to avoid this problem??

问候， 卡皮尔

推荐答案

我找不到文档中引证，但我的经验表明，EC2的总体网络基础设施（其中包括RDS和可能的任何其他AWS运行上的每个用户配置的虚拟机，如果不是所有AWS的，当然服务似乎并没有被严格限制在EC2实例）实现了全状态数据包检测，并会忘记，一个TCP连接是有效的绝对空闲几分钟后......使得您所描述的行为。

I can't find a citation in the documentation, but my experience suggests that the networking infrastructure of EC2 in general (which would include RDS and likely any other AWS service that runs on virtual machines that are provisioned per-customer, if not all of AWS, and certainly does not appear to be limited strictly to "EC2 instances") implements stateful packet inspection, and will "forget" that a TCP connection is valid after a few minutes of absolute idleness... causing the behavior you describe.

在连接两端的机器可以确信连接仍然存在，但网络不会允许流量在它们之间传递，因为在SPI环境下的TCP会话没有发现，他们创建的，并且只能在网络看到在一开始的连接创建（ SYN ，SYN / ACK，ACK ）。我原来遇到过这个问题，在EC2（不RDS），但MySQL的服务器会很惊讶，如果根本的原因是不一样的。

The machines on both ends of the connection may be convinced that the connection is still there, but the network will not allow the traffic to pass between them, because TCP sessions in an SPI environment are not discovered, they're created, and can only be created when the network sees the connection at the very beginning (SYN, SYN/ACK, ACK). I originally encountered this issue with MySQL servers in EC2 (not RDS) but would be very surprised if the underlying cause is not the same.

有两种可能的方法来解决这一点。

There are two possible approaches to work around this.

如果你的PHP机器的Linux，配置内核，以保持连接活着层4这一变化将是不可见的，你在这个意义上，这些保活不会改变的时间值在 SHOW PROCESSLIST列在睡眠连接，因为它不会重置量时间连接已闲置7层......但应避免超时从AWS基础设施，如果管理MySQL的连接库设置套接字选项正确地利用它。

If your PHP machine is Linux, configure the kernel to keep the connections alive at layer 4. This change will be invisible to you in the sense that these keepalives won't change the value in the Time column in SHOW PROCESSLIST for connections in Sleep because it won't reset the amount of time the connection has been idle at layer 7 ... but it should avoid the timeouts from the AWS infrastructure if the libraries managing the MySQL connections are setting the socket options correctly to take advantage of it.

http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/ usingkeepalive.html 介绍了如何设置此生活，以及如何使其在重新引导持久性。

http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html explains how to set this up live, and how to make it persistent across reboots.

如果做不到这一点，其他的选项是强制MySQL关闭连接的早于网络超时的，这样PHP服务器将立即认识到，它试图说服在一个封闭的插座。这听起来可能违反直觉缩短超时而不是lengthing，但缩短超时应当引起你的ping测试非常快，如果一个会话空闲时间过长，这也（本质）解决的问题失败，假设神智在PHP客户端库。一旦你的应用程序是忙，连接将presumably很少有空闲的时间足够长，达到超时。

Failing that, the other option is to force MySQL to close the connection sooner than the network timeout so that the PHP machine will immediately recognize that it's trying to talk on a closed socket. It may sound counter-intuitive to shorten a timeout rather than lengthing it, but shortening the timeout should cause your ping test to fail very quickly if a session has been idle too long, which also (essentially) "solves" the problem, assuming sanity in the PHP client library. Once your application is busier, the connections will presumably seldom be idle long enough to reach the timeout.

MySQL服务器有两种不同的空闲超时时间设置：的 WAIT_TIMEOUT （非互动环节，即从code连接，如PHP）和<一href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_interactive_timeout"相对=nofollow> 的interactive_timeout （从查询浏览器和命令行客户端），但服务器只知道区别，因为客户端库已通知服务器哪个连接它的建立的类型。假设你的客户端库使用了正确的设置，那么 WAIT_TIMEOUT 就是你要找的人。将其设置为低于900的值应该解决这个问题，如果在Linux内核更改TCP存活的设置没有。但是请注意，这使得后的变化，只有将来的连接将受到影响 - 连接已建立时所做的更改仍然会与当前值运行，默认为8小时（28800秒）。这些为你的实例都在RDS参数组配置。

MySQL Server has two different idle timeout settings: wait_timeout (for non-interactive sessions, i.e., connections from code, like PHP) and interactive_timeout (from query browsers and the command line client) but the server only knows the difference because the client library has to notify the server which type of connection it's establishing. Assuming your client library uses the correct setup, then wait_timeout is the one you're looking for. Setting this to a value below 900 should resolve the issue if changing the TCP keepalive settings in the Linux kernel doesn't. Note, though, that after making the change, only future connections will be impacted -- connections already established when the change is made will still be running with the current value, which defaults to 8 hours (28800 seconds). These are configurable in the RDS Parameter Group for your instance.

有类似的行为在AWS文档提示这里，以及需要进行调整，以改变TCP持久连接，如果你在Windows上运行PHP的服务器，而不是Linux的，因为我认为上面... Windows的注册表设置，即使文章是专讲红移和外部静止EC2它连接似乎验证这一根本性问题如以上所讨论

There are hints of similar behavior in the AWS docs here, along with the Windows registry settings that need to be adjusted to change TCP keepalives if you're running the PHP server on Windows, instead of Linux, as I assumed above... even though the article is specifically about Redshift and connections external to EC2 it still seems to validate the underlying issue as discussed above.

这篇关于mysql_ping挂起与Amazon RDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！