Scalability of a multi-tenant approach using MySQL and Spring

Question

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.

Answer 1

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.