Cheapest way to to determine if a MySQL connection

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.

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

Thanks for all the great discussion, especially @Vlad!

Answer 1

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

Answer 2

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.

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.