multithreading - 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.