Application server connection pool settings that are of concern to the Software Architect:Connection Timeout

If you have any responsibility for the stability/performance of your application -- there are some settings you need to be concerned about. I am familiar with Websphere and most likely there are comparable settings for its competitors. In Websphere's case at least, these settings are not very well documented and it is easy to misunderstand them. In this post, I will address 'Connection Timeout'.

Connection Timeout:If you do a javax.sql.DataSource.getConnection() and there are no connections available -- this is how long Websphere will wait until it throws a timeout exception. The default value is 180 seconds and this is a bad setting.

Why? Because while you are waiting, you are using up a thread in the web-container and this thread will stay active and waiting in line for the next available connection even if the end-user abandons the transaction by hitting stop, back buttons on his browser or even just closing the browser.

You and I may be patient folks (particularly with our own applications) -- but the rest of the world is not.

For a B2C application, and depending on how invested the end-user is in the transaction -- I wouldn't invest a thread past 3 seconds, five at the very most. For Intranet or B2B applications you can increase this -- but I wouldn't go past 15 seconds.

Chances are that the end-user is going to abandon his transaction after waiting the above mentioned times.

What can exacerbate the situation is that after abandoning the transaction, the end-user can keep repeating/retrying -- just chewing up threads on your server. Worse yet, these threads are going to live at least 180 seconds on your server!!

You can easily imagine that with enough users, this can snowball to the point of bringing your server to a complete halt.

You can/should, of course, trap this exception and display some appropriate message to the end-user -- like 'System is busy, please retry later'.. You need to log this event of course. Goes without saying that this logging should in no way be directed to the same database :)

Summary: Will depend on your application -- but set it as low as you can get away with. Don't exceed 3, 5 at the very most for a B2C application -- 15 for Intranet/B2B applications.