Connection pool for dynamic database connections

12
votes

The problem setup is based on a webservice (Spring/Java, Tomcat7 and MySql) where every user gets their own database, hence each request needs their own connection. As all databases are created dynamically during runtime, configuring them statically before startup is not an option.

To optimise database connection usage, an implementation of a database connection pool would be great, right?

With Java/Spring: How would I create a connection pool for dynamic databases? I am a bit struck by the lack of clean options here!

Problem: Tomcat's Connection Pool (and as far as i understand C3P0 as well) treats each new DataSource instance as a whole new connection pool -> stack-reference

  1. Is it a good idea to create a static datasource with a generic MySql connection (without specifing the database on connection) and use a connection pool with this datasource together with adapted SQL statements?
    stack-reference
  2. What about developing a custom persistent database based datasource pool? Any experience with performance here? Any advice? Any libraries that do that?
  3. Or would it be feasable to workaround Tomcat's DataSource problem by creating Tomcat JNDI Datasources dynamically by manipulating it's context.xml dynamically from Java?
  4. I can't believe that there aren't more plain/simple solutions for this. Grails/Hibernate struggles with this, Java/JDBC struggles with this, ... is it such a rare use-case to separate userdata on a user basis by creating user specific databases dynamically? If so, what would be a better setup?

EDIT

  1. Another option is the suggestion from @M.Deinum to use a single configured datasource and dynamically hotswap it for the right connection ->M.Deinum Blog and stack-reference.
    How does that perform with a connection pool like the ones above?
3
javamysqltomcatjdbcconnection-pooling
Not sure how hibernate struggles with this as that comes out of the box with multi tenancy support, which is basically what you want. – M. Deinum
Care to give an example? Would be delighted to see a working solution! My experience with hibernate multi tenant support let me understand that datasources still must be present/configurated before startup. How can i use hibernate without such datasources and create my connections dynamically? – N.R.
And yes I have experience with it even before multitenancy was a thing, I blogged about it too. It was for both frontend, backend or datasources... We replaced 50 application instances with a single one... In 2006... (Blogged about it later). – M. Deinum
ok, thanks for the hibernate help! Also thanks for your blog entry! In your blog you describe a solution using HotSwappableTargetSources. Isn't that a variation of the options (1-3) above? How does this perform for stateless REST and connection pooling? – N.R.
Quite well with several thousands of concurrent users :)... But as mentioned that was before multi tenancy was hot. I would suggest trying what is available first before trying your own. (We needed more then only hibernate like templating the website, for which we integrated with Springs Theming support). – M. Deinum

3 Answers

3
votes

I believe that HikariCP works without having to specify a single database.

1
votes

Once the databases are created in runtime, you have to create the pools also in runtime. I am afraid the spring infrastructure is not giving you any help here, as it is tuned for the usual static use case.

I'd have a map of pools:

  • have a 
     Map < connectionUrlString,List< c3poPool > > map
  • when requesting a connection, get the corresponding c3po pool from the map
  • and you can get the best of both worlds, since the real connection pool for each dynamically created database is handled by a c3po instance, but you can create new instances in runtime

This works as a low-level solution. If you want to go further, you can wrap this logic into a db connection provider, and register that as a "driver". This way any part of your application requests a new connection, you can just return one connection from the existing pools (and if a totally new connection is requested, create a new pool for that).

0
votes

First than all, sorry for my english, i'm improving every day.

In my experience, I had a similar situation and it was resolve with spring framework. Let me explain you how you'd solve that question.

  1. Make a spring config file with these characteristics: a) A resource loader: This one is the responsible of load properties from configurations files or from database, those properties will be the appropriates to establish the database connection. b) A pool database configuration parameterized with the properties that you'll load.

  2. Create a locator class: In this class you'll need a HashMap

  3. Use the multi context feature of spring: The idea is assign a code to every one connection that you establish and later load that connection like an application context with spring, then in the locator class, put in the map that context and use it as frequent as you need.

I think is you follow these steps, you can create dynamic pool or database connection as you want.