确定MySQL连接是否仍处于活动状态的最便宜方法 [英] Cheapest way to to determine if a MySQL connection is still alive

问题描述

我有一个用于基于Web的数据服务的MySQL连接池.开始服务请求时，它将使用池中的连接以使用.问题是，如果自从使用该特定连接以来一直存在明显的暂停，则服务器可能已超时并关闭了其末端.我希望能够在池管理代码中检测到这一点.

I have a pool of MySQL connections for a web-based data service. When it starts to service a request, it takes a connection from the pool to use. The problem is that if there has been a significant pause since that particular connection has been used, the server may have timed it out and closed its end. I'd like to be able to detect this in the pool management code.

诀窍是这样的:我在其中进行编码的环境仅使我在连接中使用了非常抽象的API.我基本上只能执行SQL语句.我无权访问实际的套接字，也无权直接访问MySQL客户端API.

The trick is this: The environment in which I'm coding gives me only a very abstract API into the connection. I can basically only execute SQL statements. I don't have access to the actual socket or direct access to the MySQL client API.

因此，问题是:我可以在连接上执行以确定其是否正常运行的最便宜的MySQL语句是什么?例如，SELECT 1;应该可以工作，但是我想知道是否还有更便宜的东西?也许什至没有涉及，但是可以在MySQL客户端库中处理并有效地回答了相同的问题?

So, the question is: What is the cheapest MySQL statement I can execute on the connection to determine if it is working. For example SELECT 1; should work, but I'm wondering if there is something even cheaper? Perhaps something that doesn't even go across the wire, but is handled in the MySQL client lib and effectively answers the same question?

说明::我不关心检查MySQL服务器是否正在运行，或者它的数据库配置是否足以回答查询.如果这些事情失败了，那么服务执行的后续SQL将获取并处理适当的错误.我只真正关心TCP连接是否打开……因为如果服务器关闭了TCP连接，则Web服务的SQL将收到一个错误，表示只是重新连接并重试"，并且一旦关闭该连接将很不方便.服务代码的污点.

Clarification: I'm not concerned about checking if the MySQL server is running, or if it's database configuration is up enough to answer queries. If those things are down, then the subsequent SQL the service executes will get and handle the appropriate error. I'm only really concerned with if the TCP connection is open… since if the server closed it, then the web service's SQL will get an error that means "just reconnect and try again", and that would be inconvenient to do once down in the muck of the service code.

关闭:/* ping */ hack正是我一直在寻找的东西，但是可惜只能通过JDBC来使用.通读该黑客的文档，很明显，正是出于我想要它的相同原因，它被放置在了完全的位置.出于好奇，我正在使用 HDBC-mysql .我要请HDBC-mysql的作者添加直接或通过类似的方法调用mysql_ping()的方法.

Closure: The /* ping */ hack is exactly the sort of thing I was looking for, but alas is only available via JDBC. Reading through the docs for that hack, it is clear it was put there for exactly the same reason I wanted it. For the curious, I'm working in Haskel, using HDBC and HDBC-mysql. I'm going to ask the author of HDBC-mysql to add a way to call mysql_ping() either directly or via a similar hack.

弗拉德(Vlad)的DO 1也是我所追求的，并且由于JDBC之外无法使用其他技巧，因此我将使用它.

Vlad's DO 1 was also the kind of thing I was after, and since the other hack isn't available outside of JDBC, I'll be using it.

感谢所有精彩的讨论，尤其是@Vlad！

Thanks for all the great discussion, especially @Vlad!

推荐答案

您在不进行布线的情况下将不知道连接的真实状态，并且SELECT 1是一个很好的候选者(可以说，您可以想出一条更短的命令来解析，而花费的时间更少，但是与网络甚至是环回延迟相比，节省下来的钱都微不足道.)

You will not know the real state of the connection without going over the wire, and SELECT 1 is a good enough candidate (arguably you could come up with a shorter command which takes less time to parse, but compared to network or even loopback latency those savings would be insignificant.)

话虽如此，我会认为在从池中检出连接之前 ping通连接不是最佳方法.

This being said, I would argue that pinging a connection before checking it out from the pool is not the best approach.

您可能应该简单地让连接池管理器强制执行自己的保持活动(超时)策略，以避免被服务器断开连接(缺少更严重的干预性连接问题，这可能会影响您无论如何都会在常规操作过程中出现问题-并且连接池管理器也将无法提供帮助)，以及为了不占用数据库(不必要地考虑文件句柄和内存使用情况)

You should probably simply have your connection pool manager enforce its own keep-alive (timeout) policy to avoid being disconnected by the server (short of a more serious intervening connectivity issue, which could affect you smack in the middle of regular operations anyway -- and which your connection pool manager would be unable to help with anyway), as well as in order not to hog the database (think filehandles and memory usage) needlessly.

因此，在我看来，在从池中检出连接之前，对连接条件进行什么值测试值得怀疑.在将连接检入池之前，应该先测试连接状态 ，但这可以通过在出现SQL硬错误(或等效异常)时简单地将连接标记为脏连接来隐式完成(除非您使用的API已经向您公开了类似is-bad的调用.)

It is therefore questionable, in my opinion, what value testing for connectivity condition before checking out a connection from the pool really has. It may be worth testing connection status before a connection is checked in back into the pool, but that can be done implicitly by simply marking the connection as dirty when an SQL hard error (or equivalent exception) arises (unless the API you are using already exposes a is-bad-like call for you.)

因此，我建议:

• 实施客户端保持活动策略
• 从池中检出连接时不执行任何检查
• 在将连接返回到池之前执行脏检查
• 让应用程序代码处理其他(非超时)异常连接条件

从您的评论中可以看出，您确实真的想要ping连接(我认为这是因为您对MySQL服务器上的超时特性没有完全的控制或了解)或中介网络设备(例如代理服务器等)

It would appear from your comments that you really really want to ping the connection (I assume that is because you don't have full control over, or knowledge of, timeout characteristics on the MySQL server or intervening network equipment such as proxies etc.)

在这种情况下，您可以使用 DO 1 作为SELECT 1的替代；它是 marginally 更快-解析时间较短，并且它不会返回实际数据(尽管您将获取TCP ack，所以您仍将进行往返验证连接仍在建立.)

In this case you can use DO 1 as an alternative to SELECT 1; it is marginally faster -- shorter to parse, and it does not return actual data (although you will get the TCP acks, so you will still do the roundtrip validating that the connection is still established.)

关于 Joshua的帖子，以下是各种情况下的数据包捕获跟踪:

Regarding Joshua's post, here's packet capture traces for various scenarios:

SELECT 1;
13:51:01.463112 IP client.45893 > server.mysql: P 2270604498:2270604511(13) ack 2531191393 win 1460 <nop,nop,timestamp 2983462950 59680547>
13:51:01.463682 IP server.mysql > client.45893: P 1:57(56) ack 13 win 65306 <nop,nop,timestamp 59680938 2983462950>
13:51:01.463698 IP client.45893 > server.mysql: . ack 57 win 1460 <nop,nop,timestamp 2983462951 59680938>

DO 1;
13:51:27.415520 IP client.45893 > server.mysql: P 13:22(9) ack 57 win 1460 <nop,nop,timestamp 2983488906 59680938>
13:51:27.415931 IP server.mysql > client.45893: P 57:68(11) ack 22 win 65297 <nop,nop,timestamp 59681197 2983488906>
13:51:27.415948 IP client.45893 > server.mysql: . ack 68 win 1460 <nop,nop,timestamp 2983488907 59681197>

mysql_ping
14:54:05.545860 IP client.46156 > server.mysql: P 69:74(5) ack 78 win 1460 <nop,nop,timestamp 2987247459 59718745>
14:54:05.546076 IP server.mysql > client.46156: P 78:89(11) ack 74 win 65462 <nop,nop,timestamp 59718776 2987247459>
14:54:05.546092 IP client.46156 > server.mysql: . ack 89 win 1460 <nop,nop,timestamp 2987247459 59718776>

如您所见，

除了mysql_ping数据包是5个字节而不是DO 1;的9个字节的事实之外，往返的次数(因此，由网络引起的延迟)是完全相同的.与mysql_ping相比，使用DO 1支付的唯一额外费用是对DO 1的解析，这是微不足道的.

As you can see, except for the fact that the mysql_ping packet is 5 bytes instead of DO 1;'s 9 bytes, the number of roundtrips (and consequently, network-induced latency) is exactly the same. The only extra cost you are paying with DO 1 as opposed to mysql_ping is the parsing of DO 1, which is trivial.

这篇关于确定MySQL连接是否仍处于活动状态的最便宜方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！