0

I have an application that connects to databases based on a json string that contains connection information. The json is sent just when the application starts.

How do you manage such connection pools? Because I can get multiple configurations that point to the same database with the same user. I know I cannot create new connection every time since it's an expensive operation so I use connection pools (Apache DBCP2).

But when two pools connect to the same database I sometimes get "too many connections" error since the pool size of two pools is greater than the max allowed connections.

Is holding the connection pools in a HashMap where the connection string is used as the key a bad idea?

Config looks like this (very simplified json):

{
   dbconfig: [
      { database 1 ... },
      { database 2 ... },
      { database 3 ... }
   ],
   otherconfig: { ... }
}

The database 1 and 2 config point to the same database, or the same server but a different database

So my application spawns three threads that receive data on a predefined port. I don't know what database connections I will get and how many worker threads will I spawn. And my question is if there is some kind of pooling framework at which I can throw the configs and it will take care of the sharing of connections and I can just request the connection from it.

Currently with my design I would create 3 pools from which 2 pools are accessing the same server and sometimes the same database and that's why I get the "too many connections".

2 Answers 2

1

First off, you should be closing connections once you are done using them. That will help take care of the "too many connections" errors you're seeing. At an application level, you need to be releasing any shared resources you have as soon as you're done using them.

Second, it sounds like you're trying to duplicate the work that the underlying connection pool manager can provide for you. Specifically, at the application level, don't worry about the cost involved with opening / closing connections - that's what the pool manager handles for you. Because you haven't been closing off the connections once done, you're making it harder for the pool manager to be of use to you.

It's not clear if you have multiple, separate applications using separate pool managers or separate applications using the same pool manager. Regardless, using a HashMap with the connection string is a bad idea. At a minimum, you'll create inter-application communication issues. In the worst case, you're going to have to try and coordinate resources (connections) across applications in a web-server which is going to be problematic at best.

Finally, if you need to have multiple pools connecting to the same database server and the total number of connections across those pools is exceeding what the DB server can handle then you need to change that configuration on the DB server. If you truly need 11 connections and the DB server is only giving you 10, then you need to change the DB server.


To Summarize:

  • Close the connections already. You're creating potentially huge headaches in the name of perceived performance savings.

  • Change the DB server settings if you're still running out of connections.

1
  • Thanks for the input, i updated my question with more details. And i always do the open + close pair of connections and statement resources. It's just when two threads get the the same config they create a pool for themselves and then i have two pools that point to the same location.
    – Gabriel
    Dec 3, 2014 at 18:37
1

Your problem is no programming problem, it's a configuration problem.

1) Configure your database (server) according to your needs, e.g. see MySql documentation. You could use two different users for one database, you want to access with two different datasources. Then configure quota in the DB and in your application; say 200 Connections each user. So there should be no problem of running out of connections.

The same goes for the third connection pool.

2) Change your Connection Pool to something different Tomcat (There are reasons for why you want to change on the page or read this) or C3PO.

Is holding the connection pools in a HashMap where the connection string is used as the key a bad idea?

I see no reason for that. You define 3 independend Datasources. Done. What is the usecase for the hashmap?

3) Think of using a framework which does the dirty work for you: e.g. Spring Data JPA. See further Spring JDBC for the lowlevel work JdbcTemplate leaves near to no wishes open. There is no need to get your hands dirty. Without a framework there is too much boilerplate to write and the work is error prone.

1
  • 1.) will have to talk to the db admin, but i think i will have no luck with him. 2.) C3PO was exactly what i was searching for! 3.) it's an old project that i'm rewriting and unfortunately had no time to invest into learning the frameworks. But I do have approval to rewrite it so i'll utilize Spring for sure!
    – Gabriel
    Dec 3, 2014 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.