Would a connection Pool benefit a multithreaded Java program

Question

I have a java process that starts about 60 threads that each access a MySql database.

Would I benefit from using a Connection Pool like C3P0? Or is it meant only for Web apps (that scale to lots of users) ?

Today we have long-living JDBC Connections (one per thread), and my plan was to instead get a Connection from the Connection Pool before every SQL query/insert.

I wonder whether that would make our application more stable? Also, if I configure it to match the max number of connections in the database, will the thread have to wait until there is a free connection? The documentation isnt very clear (at least not for me).

Any guidance is appreciated!

Answer 1

You probably can benefit from a connection pool. The "Communications link failure" together with long-lived JDBC connections makes me suspect the connection is broken after some time of not being used (idle).

A database connection pool like HikariCP does 2 things for you that can help:

• check a connection is valid before handing it out. If it is not valid, it is discarded and another one or a new connection that is valid is handed out. This is all done by the pool, your application does not have to take care of this.
• keep connections healthy by closing idle connections ("idleTimeout") and cycling long-lived connections ("maxLifetime"). The latter is especially useful when bad network components (firewalls) drop any connection that is open for longer than, let's say, 30 minutes.(*)

If all connections from the pool are used, a thread might have to wait ("connectionTimeout"). But if your pool has a proper maximum size ("maximumPoolSize") this will rarely be a long time. It does require your application to minimize the time it uses a connection: between getting a connection and closing it (which returns the connection to the pool), your application should mostly/only perform database actions. A side effect will be that you will need far less connections: where you use 60 now, you might find that you only need 6 in the pool. Some performance testing is needed to determine the proper "maximumPoolSize" for your application.

I suggest you try an "unplug" test with and without a connection pool. Run your application and give it something to do, unplug the network cable, than plug the network cable back in and see how long it takes your application to recover. In the pool-case, you should see your application functioning normally again as soon as the pool is able to create a new connection to the database.

(*) There is another reason for cycling connections: some queries may produce temporary data on the database server side and the database server may keep this around for as long as the connection is alive. This could result in an ever increasing memory usage by the database server. I have not seen this happen, but I know others have. A "maxLifetime" option is very useful in such a case.

Answer 2

Putting aside the questions of where your application is running and whether you have your database exposed to the internet, I don't think adding a connection pool will fix your problem, but it could improve your application.

I'm guessing that your spurious errors are happening when you are using your database connection. I don't recognize your particular error, but it sounds like a connection failure of some sort, which could happen if you had unreliable or slow links between you and the database. The pool wouldn't help here because it is a pool of connections. Once you obtain the connection, you don't know whether it will then fail or not for the same reasons.

However, if you do use a pool, then you don't have to keep the connection open for extended periods. With a pool, you ask for a connection, and one will be created if none is available. After you return the connection, it might be (disconnected) and disposed if it hasn't been used for a while. Unless your application is constant using every connection, then this would be good for both your app and the server.

Even here, you have to do something extra to handle the failure. Let's say you have taken a connection from the pool, and it has subsequently failed. You could close it, and ask the pool for a new connection (there should be some API in the pool to get rid of that connection.) A new connection might be in a better state.

Finally, consider perhaps not using JDBC over the internet. As other people are likely to point out, this is exposing yourself to unnecessary risk. Perhaps use a webservice of some kind to read and write data over secure https and a more restricted interface.