6

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!

Peter Andersson
  • 1,947
  • 3
  • 28
  • 44
  • I don't quite understand the architecture of this application. Could you spell it out? What I get from your description is that this is a server with a DB back-end that you contact over the Internet, using some client. If that's the case, the only difference between this and a "Web app" is what port it's running on, no? – Mario Dec 05 '14 at 16:03
  • The questions isnt about architecture, its about Connection Pools. (rude sarcastic comment deleted). The question if whether a Java would benefit from using Connection Pools compared to long running JDBC Connections (one per thread as the question mentions). A Web app scales in a completely different way (normally) where you can have 1000s or more users. Here I know I only have one process, but many threads. Will I still benefit from using Connection Pools? – Peter Andersson Dec 05 '14 at 17:11
  • 1
    If you set a maximum number of connections on the pool, the pool will not create any more. If all the connections at a given point in time are taken by other threads, then the next call to retrieve one will block. – omerkudat Dec 05 '14 at 17:36
  • Finally someone that read the question. Thanks! – Peter Andersson Dec 05 '14 at 18:33

2 Answers2

2

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.

vanOekel
  • 6,358
  • 1
  • 21
  • 56
1

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.

omerkudat
  • 9,371
  • 4
  • 33
  • 42
  • 1
    Thanks for reading and answering my question, instead of meandering into architecture questions! Our app is strictly for in-house use, we use VPN and non-std ports, have firewalls, etc yada yada nothing which is of particular interest for the question. Nothing is exposed so don't worry. If I had 52 hours a day I might write a service for this particular case, but I have plenty of other applications to write, and this app is not critical. I was more looking for a "best practice". I think I understand how to use it now. Thanks again. – Peter Andersson Dec 05 '14 at 18:38