c3p0 and MySQL, smart configuration.

I recently answered a question related to c3p0 and MySQL on stackoverflow. There are a lot of misconceptions about c3p0 properties in relation to database properties. I will try to explain them in detail. There is no code here so if you are looking for an integration code then you know what to do.

Let's first understand a few mysql properties which directly effects your application datasource configuration.

interactive_timeout : (Default 28800 sec) interactive_timeout is for mysql shell sessions in seconds like mysqldump or mysql command-line tools. Most of the time this is set to higher value because you don't want it to get disconnected while you are doing something on mysql cli. This does not really effect your app config but to avoid confusion with regards to wait_timeout.

wait_timeout : (Default 28800 sec, AWS RDS 300 sec) The amount of seconds of inactivity that MySQL will wait before it closes a connection on a non-interactive connections. i.e connected from java application.

Now let's understand c3p0 properties in relation with the above DB properties.

maxIdleTime: (Default: 0) Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
This refers to how long a connection object can be usable and will be available in pool. Once the timeout is over c3p0 will destroy or recycle it.

Now the problem comes when you have maxIdleTime higher than the wait_timeout. let's say if the mxIdleTime : 50 secs and wait_timeout : 40 sec then there is a chance that you will get Connection timeout exception: Broken Pipe if you try to do any operation in last 10 seconds. So maxIdelTime should always be less than wait_timeout.

Everything is easy up until now. Here comes the real problem.

Any of the data source properties(eg. maxIdleTime) only affects the connections which are in pool so if your application has acquired a connection, keeps it idle for more than maxIdleTime and then tries to do any operation, it will definitely get "Broken Pipe". It's like maxIdleTime is a warranty period of a connection.
You can reproduce this easily by following these steps. 1) set wait_timeout to 15 sec 2) Set maxIdleTime to 14sec 3) acquire a connection 4) Thread.sleep(16000). 5) run any query on the connection...  Saw that?

There is no ideal wait_timeout. You have figure it out yourself based on your application requirement. It is good to have higher wait_timeout on mysql but It's not always practical especially when you have an application already built.
If you are thinking of changing it then you have to make sure that in your application you are not keeping any connections idle for more that wait_timeout.

This is especially important when you are not doing connection management manually for example when you use Spring transnational API. Spring starts transaction when you enter an @Transaction annotated method so it acquires a connection from pool. If you are making any web service call or reading some file in the transactional method which takes more time than wait_time out then you are screwed.

I have faced this issue before. In one of the application there was a cron which would do order processing. To make it faster I used batch processing. Once a batch of customers is retrieved and the code does some processing(no db calls), And after that when It tried to save all the orders there was a "Broken Pipe". The problem was my wait_timeout was 1 minute and order processing was taking more than that. So I had to increase it to 2 minutes. I could have reduced the batch size but that was making the overall process slower.

You also have to consider that acquiring a connection is expensive task and if you have wait_timeout too low then it beats the whole purpose of having a connection pool, as it will frequently try to acquire new connections.

Ideal Timeout

There is no such thing as ideal timeout but here's some of the steps you can follow to better manage it.

• When development is in progress set the maxIdleTime, and wait_timeout to as low as possible, this will make developers write better queries. If they don't, it will throw "Broken Pipe"

• If you have long running transactions for example if you are running a cron to run some batch processes, then move those process to separate application.

• If you are not doing transaction management manually in your application i.e spring transactional API. then make sure that the method marked transaction does not do anything which would take long time for example calling a webservice or reading a file etc.

• Longer transaction would also lead to Deadlocks(Lock wait timeout) errors because if a transaction is in progress which is doing any updates then other transaction will wait and fail once the innodb_lock_wait_timeout is exceeded.

Ways to configure c3p0

• Manually control wait_timeout

• In this configuration you use maxIdleTime in the configuration and set it lesser than the wait_timeout that you have set in mysql.

• This way you can be sure that the connection will be open longer than the wait_timeout and thus avoid broken pipe.

• This method is quite simple, you have full control over the wait time. The major drawback is that it is db property agnostic i.e you application will fail if it not in sync with wait_timeout on db side.

• Let c3p0 take care of connection life.

• In this type you use many other c3p0 property except maxIdleTime as below.

• testConnectionOnCheckin validates the connection when it is returned to the pool. testConnectionOnCheckOut, although would ensure active connections before use, would be too expensive to do.

• idleConnectionTestPeriod sets a limit to how long a connection will stay idle before testing it. Without preferredTestQuery, the default is DatabaseMetaData.getTables() - which is database agnostic, and although a relatively expensive call, is probably fine for a relatively small database. If you're paranoid about performance use a query specific to your database (i.e. preferredTestQuery="SELECT 1")

• maxIdleTimeExcessConnections will bring back the connection count back down to minPoolSize after a spike in activity.

• This method works fine and does not depend on db configuration much but there is so much overheads, for example it will run an extra query every time the application is tries to get a connection, for a normal web application it is huge because if there are 1K user online any making 2K clicks every minute it will execute extra query every time, because normal application would acquire a lock and release it before sending a response.

Ideaapplied Technologies provides comprehensive QA services. Visit us at ideaapplied.com to know more about our services.