SELECT 1 from DUAL: MySQL

Question

In looking over my Query log, I see an odd pattern that I don't have an explanation for.

After practically every query, I have "select 1 from DUAL".

I have no idea where this is coming from, and I'm certainly not making the query explicitly.

The log basically looks like this:

    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    10 Query       SELECT some normal query
    10 Query       select 1 from DUAL
    ...etc...

Has anybody encountered this problem before?

MySQL Version: 5.0.51

Driver: Java 6 app using JDBC. mysql-connector-java-5.1.6-bin.jar

Connection Pool: commons-dbcp 1.2.2

The validationQuery was set to "select 1 from DUAL" (obviously) and apparently the connection pool defaults testOnBorrow and testOnReturn to true when a validation query is non-null.

One further question that this brings up for me is whether or not I actually need to have a validation query, or if I can maybe get a performance boost by disabling it or at least reducing the frequency with which it is used. Unfortunately, the developer who wrote our "database manager" is no longer with us, so I can't ask him to justify it for me. Any input would be appreciated. I'm gonna dig through the API and google for a while and report back if I find anything worthwhile.

EDIT: added some more info

EDIT2: Added info that was asked for in the correct answer for anybody who finds this later

Answer 1

It could be coming from the connection pool your application is using. We use a simple query to test the connection.

Just had a quick look in the source to mysql-connector-j and it isn't coming from in there.

The most likely cause is the connection pool.

Common connection pools:

commons-dbcp has a configuration property validationQuery, this combined with testOnBorrow and testOnReturn could cause the statements you see.

c3p0 has preferredTestQuery, testConnectionOnCheckin, testConnectionOnCheckout and idleConnectionTestPeriod

For what's it's worth I tend to configure connection testing and checkout/borrow even if it means a little extra network chatter.

Answer 2

I have performed 100 inserts/deltes and tested on both DBCP and C3PO.

DBCP :: testOnBorrow=true impacts the response time by more than 4 folds.

C3P0 :: testConnectionOnCheckout=true impacts the response time by more than 3 folds.

Here are the results : DBCP – BasicDataSource

Average time for 100 transactions ( insert operation ) testOnBorrow=false :: 219.01 ms testOnBorrow=true :: 1071.56 ms

Average time for 100 transactions ( delete opration ) testOnBorrow=false :: 223.4 ms testOnBorrow=true :: 1067.51 ms

C3PO – ComboPooledDataSource Average time for 100 transactions ( insert operation ) testConnectionOnCheckout=false :: 220.08 ms testConnectionOnCheckout=true :: 661.44 ms

Average time for 100 transactions ( delete opration ) testConnectionOnCheckout=false :: 216.52 ms testConnectionOnCheckout=true :: 648.29 ms

Conculsion : Setting testOnBorrow=true in DBCP or testConnectionOnCheckout=true in C3PO impacts the performance by 3-4 folds. Is there any other setting that will enhance the performance.

-Durga Prasad