Whether the connection pool will use a multithreaded Java program
I have a Java process that starts about 60 threads, each accessing a MySql database.
Can I use a connection pool like C3P0? Or does this only mean for web applications (which scale to a large number of users)?
We have longstanding JDBC connections today (one per thread) and my plan was to instead get the connection from the connection pool before every SQL query / insert.
I wonder if this will make our application more stable? Also, if I tune it to the maximum number of connections in the database, will the thread wait until there is a free connection? The documentation is not very clear (at least not for me).
Any guidance would be greatly appreciated!
source to share
Questions aside about where your application is running and whether you have a database online, I don't think adding a connection pool will fix your problem, but it might improve your application.
I am assuming that your false errors are happening when you are using a database connection. I won't acknowledge your specific error, but it looks like a connection failure that can happen if you had unreliable or slow connections between you and the database. Pooling won't help here because it's a pool of connections. Once you get a connection, you don't know if it will work after that or not for the same reasons.
However, if you are using a pool, you do not need to open the connection for a long time. With a pool, you request a connection, and each will be created if not available. After you get the connection back, it can be disconnected and removed if it hasn't been used for a while. If your application is not persistent on every connection, this will be useful for both your application and the server.
Even here, you have to do something extra to deal with this glitch. Let's say you took a connection from the pool and it subsequently failed. You can close it and set a pool for the new connection (in which case there must be some API in the pool to get rid of this connection.) The new connection may be in a better state.
Finally, consider maybe not using JDBC over the internet. As other people point out, this exposes itself to unnecessary risk. Perhaps use some kind of web service to read and write data over secure https and a more limited interface.
source to share
You can probably use connection pooling. "Failed communication" along with long lasting JDBC connections makes me suspect that the connection is dropping after a while (not being used).
A database connection pool like HikariCP does 2 things for you that might help:
- test the connection before transferring it. If it is invalid, it is discarded and another or new connection is distributed. This is all done by the pool, your application shouldn't care about that.
- keep connections healthy by closing idle connections ("idleTimeout") and cyclic long-lived connections ("maxLifetime"). The latter is especially useful when bad network components (firewalls) remove any connection that is open for longer than, say, 30 minutes. (*)
If all connections from the pool are being used, the thread can wait ("connectionTimeout"). But if your pool has the correct "maximumPoolSize" it will rarely be long lasting. This requires your application to minimize the time it uses the connection: between getting the connection and closing (which returns the pool connection), your application has to basically / do database actions. The side effect is that you need much fewer connections: now you are using 60, you may find that you only need 6 in the pool. Some performance testing is needed to determine the correct "maximum package" for your application.
I suggest you try to "disable" the test with and without connection pooling. Launch the app and let it do something, unplug the network cable and then plug the network cable back in and see how long it takes to restore your app. In the case of the pool, you should see that your application is functioning normally again as soon as the pool can create a new connection to the database.
(*) There is another reason for round robin connections: some queries can create temporary data on the database server side, and the database server can maintain this as long as the connection is alive. This can lead to increased memory usage by the database server. I have not seen this, but I know others. The "maxLifetime" option is very useful in this case.
source to share