Hi Davy,
thanks for the patch .Did you measure performance improvements by the patch? There is another side of checking for ping, it means slight performance loss in normal case, due to startsWith checking.

You can try "do 1" instead of "select 1" (I dunno if that works , since it does not create result set). "do 1" is a bit faster

http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf is the presentation given by Mark. This is the only one of this "performance" kind that I know of .There is no mentioning of /* ping */ here.

Hello Vladislav,

We did not measure the performance difference with the MariaDB Java Client, but we did test this (last year or so) when we reconfigured Connector/J to use this. The actual performance improvement was marginal, but then again, Connector/J already had this minor overhead ... The main improvement was that when we analyzed our queries with pt-query-digest, "select 1" obviously disappeared from the query-top-20 that pt-query-digest reports. I think this feature is also not so much about raw performance, as it is about higher scalability: less queries that reach the SQL layer means more clients can pound the server.

As for the performance loss in the normal case: the startswith() method does not allocate any memory, and it fails-fast: so for every normal query, it will fail with the first character that it compares (which surprisingly happens to be the last one of "/* ping */"). So I believe the overhead is neglect-able, but I guess there is more optimization possible: first check: queryString.charAt(0) == '/'. That's literally in the Connector/J manual:

http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html

"do 1" is not an option I'm afraid: commons-dbcp checks that the validation query returns a result set with at least one row. Without that, it considers the validation query to have failed.

That's indeed the presentation I was thinking of, but I guess my memory failed me. I must have picked this up somewhere else: either from the Connector/J manual itself, or from the Percona consulting we had last year.

Hi Davy,

Thanks for the explanation. Frankly speaking, I hesitate to implement this optimization at the moment- to me, it is a little obscure one. As you say, this does not bring much, and my measurements confirm it. If the main benefit is not seeing the certain query in some tool, perhaps the best for everyone would be talking to the tool author about filtering certain queries, like "/* ping / select 1" ,or / ping / / pt-query-ignore */ select 1 , or something like that

I can confirm that nothing bad happens if query goes to SQL layer - there are no locks taken, so scalability is fine. If this query becomes a performance problem, to me it would be quite an indicator that validation query is just being called too often, and there are likely other ways to reduce the frequency.

@Davy: Why don't you use "isValid(...)" call from jdbc4. It already does a protocol.ping() - just what you are looking for - you may need to adapt your pooling code, but that shouldn't be a problem.

At the time we were using a pre-jdbc4 connection pool, which (obviously) couldn't be configured to use Connection.isValid(int timeout). We are using a connection pool that does use this now, so feel free to close this ticket as "Won't fix".

As mentionned in previous post, Connection.isValid() is doing a ping (ping in mysql protocol, not network ping). Connection pool using JDBC4 Validation are using automatically this Connection.isValid().
Closing as won't fix