1

I'm using a multi-tenant architecture folowing the article Dynamic DataSource Routing, but creating new tenants (datasources) dynamically (on user registration).

Everything is running ok, but I'm worried with scalabillity. The app is read heavy and today we have 10 tenants but we will open the app to public and this number will increase a lot.

Each user datasource is created using the following code:

            BasicDataSource ds = new org.apache.commons.dbcp.BasicDataSource();
            ds.setDriverClassName(Driver.class.getName());
            ds.setUsername(dsUser);
            ds.setPassword(dsPassword);
            ds.setPoolPreparedStatements(true);
            ds.setMaxActive(5);
            ds.setMaxIdle(2);
            ds.setValidationQuery("SELECT 1+1");
            ds.setTestOnBorrow(true);

It means it is creating at least 2 and a maximum of 5 connections per user.

How much connections and schemas does this architecture support by MySQL server (4 CPUs 2.3Mhz/8GB Ram/80GB SSD) and how can I improve it by changing datasource parameters or mysql configuration?

I know this answer depends of a lot of additional information, just ask in the comments.

Italo Borssatto
  • 15,044
  • 7
  • 62
  • 88
  • is your app read heavy or write heavy? – Sam Dec 06 '13 at 19:37
  • then you can always scale horizontally and add read slaves to handle more concurrency. Look up master slave replication in mysql – Sam Dec 06 '13 at 19:52
  • Yes, probably I'll do that, but I want to have an idea of when I'll need to do that. I don't know MySQL deeply to know the limits of datasources that I can have. – Italo Borssatto Dec 06 '13 at 19:53
  • connection limit is not a huge limitation that you have to worry about at this point. monitor everything and use caching you can serve a lot of users on a single server. Memory usage is more of a concern than connection. – Sam Dec 06 '13 at 19:56
  • And is there a limit of opened schemas and tables at the same time? – Italo Borssatto Dec 06 '13 at 19:58
  • read here for more info http://stackoverflow.com/questions/1202322/mysql-error-too-many-connections – Sam Dec 06 '13 at 20:02

1 Answers1

0

In most cases you will not have more than 300 connections/second. That is if you add good caching mechanisms like memcached. if you are having more than 1000 connections/sec you should consider persistent connections and connection pools.

Sam
  • 2,761
  • 3
  • 19
  • 30
  • And how much datasources like that I'll be able to have running at the same time? Does MySQL has a limit of connections? – Italo Borssatto Dec 06 '13 at 19:52
  • there's a max_connection variable that you can change but with 200 connections per second you can serve thousands of users – Sam Dec 06 '13 at 19:54
  • The org.apache.commons.dbcp.BasicDataSource keeps opened connections with the server, in my example they are 2 by user. So, I'll have to make a single connection for each user access to reach that scalability of thousands? – Italo Borssatto Dec 06 '13 at 19:57
  • when you use caching for most operations users don't use a connection but memory to retrieve data. yes cause all of the users are not hitting the db and it is not thousands every second. – Sam Dec 06 '13 at 19:59