Cheapest way to determine if a MySQL connection is still alive

Question

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.

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.

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?

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.

Lindi

Is it even possible to determine if the server is running if nothing goes to it to ask for something back? – Antisthenes

Answer 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.)

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.

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.)

I would therefore recommend:

• implementing a client-side keep-alive policty
• not performing any checks when checking out connections from the pool
• performing dirty checks before a connection is returned to the pool
• let the application code deal with other (non-timeout) exceptional connection conditions

UPDATE

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.)

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.)

UPDATE 2

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>

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.

Gann

Even if the pool has it's own timeout (it does), the server can choose to close the socket before that timeout. Then the pool has a socket that isn't open. I also already handle the issue of closing down sockets that resulted in a hard SQL error rather than returning them, so the pool will reconnect next time it needs a connection. The issue arises when a good connection is put back into the pool, and then, while waiting to be reused (within the pool's timeout), the server closes the socket. – Lindi

When and why would the server choose to arbitrarily close that socket? Is server timeout non-deterministic, or do you want a generic mechanism that will work even when the keep-alive is (erroneously) set to be greater than the server (or intervening proxy etc.) idle connection timeout? – Gann

Yes, the later -- I want a generic solution. This code will go into a very large deployment with many different configurations - and it will be best to be defensive against unreasonable configurations. – Lindi

Aha - missed your DO 1 suggestion. I think that is the best I can do for now. Thanks for all your input and discussion! – Lindi

Answer 2

I'm not sure what API you are currently using (or what language), but for Java, there is a special trick the JDBC driver can do.

The standard test query is:

select 1

as you've indicated. If you modify it to:

/* ping */ select 1

the JDBC driver will notice this, and send only a single packet to the MySQL server to get a response.

I learned about this at a Sun 'Deep Dive' episode titled MySQL Tips for Java Developers With Mark Matthews.

Even if you aren't using Java, maybe this same trick has been implemented in other mysql drivers? I assume the server would need to be aware of this special packet so it can send a response...

Stratosphere

This is exactly the sort of thing I was looking for. Alas, it is only available via the JDBC driver. (I went and looked at the source code: It is implemented in the Connector/J code in jdbc/StatementImpl.java, and not in any of the other Connector or client libs.) – Lindi

@Joshua, mysql_ping, SELECT 1 and DO 1 actually cause the exact same number of TCP packets to be exchanged over the wire (or socket), the only difference would be in parsing (no statement parsing required for mysql_ping.) – Gann

Answer 3

"Connection" in this case has multiple meanings. MySQL listens on a socket- that's the network-level "connection." MySQL maintains "database connections," which include a context for query execution and other overhead.

If you just want to know if the service is listening, you should be able to execute a network-level call to see if the port (don't know what the default is) is listening on the target IP. If you want to get the MySQL engine to respond, I think your SELECT 1 idea is good- it doesn't actually fetch any data from the database but does confirm that the engine is spun up and responding.

Virilism

I'm looking to test that the socket is still open. If the server at the other end is down, then the failure at the application level is fine. If the server is up, but the socket closed, I want to catch that before I hand the connection to the application, since it can't retry easily (sigh!). – Lindi