multi tenants connection pool size setup

[Jmeter Tea]

I'm building a multi tenant REST server application with Spring 2.x, Hibernate 5.x, Spring Data REST, Mysql 5.7. Spring 2.x uses Hikari for connection pooling.

I'm going to use a DB per tenant approach, so every tenant will have his own database.

For 100 tenants I would like to think that a db.r4.large (2vcore, 15,25GB RAM and fast disk access ) with Aurora should be enough (about 150€/month).

According to the formula to size a connection pool:

connections = ((core_count * 2) + effective_spindle_count)

I should have 2core*2 + 1 = 5 connections in the pool.

From what I got, this should be the max connections in the pool to maximize performance on that DB instance.

how can I create a separate connection pool for each tenant seen that I should only use 5 connection in total?

It seems not possible to me. Even if I assign 2 connections to each tenant, I would have 200 connections to the DBMS!!

According to this question, on a db.r4.large instance I could have at max 1300 connections, so seems the instance should face quite well the load. But it seems a bad practice use hundreds connections to the db

Is there any suggestions for setting connection pool size in multi tenant environment?

[Brett Wooldridge]

See this question and answer.

[Daniele Renda]

Hi Brett,

I posted the original question on stackoverflow (https://stackoverflow.com/questions/52728639/connection-pooling-in-multi-tenant-app-shared-pool-vs-pool-per-tenant).

I read your reply to the answer you pointed out. I also read this one https://github.com/brettwooldridge/HikariCP/issues/330

Everything very useful but I'm not able to close the circle yet with all these informations.

Just to have some real numbers, I pick up the original example I made and I try to split my question in subtopics:

Scenario

We are speaking about multi-tenant web application (Angular on the front end, Spring Boot server side)

DBMS: AWS RDS Aurora db.r4.large (2vcore, 15,25GB RAM and fast disk access ) - that according to this can handle 1000 connections.

Tenants: 100 (customers)

Users: let's say each tenant has 3 working users

Multi tenant approach: DB per tenant on the same RDS instance

Average query time: 30ms

Average queries per second: 100(tenants) x 3(users per tenant) x5(average queries for each user) = 1500

Let's forget about load balancing for the moment.

Defining maximum number of connections to the db to maximize TPS

The first question is: how to define the max number of connections to use?

The first thing maybe is not very clear to me is: how to define the maximum number of connections to the DB said that in the actual scenario I can reach the limit of 1000 connections. But from what I got I should use a much less value to maximize the transactions per second.

So, what is the "right" value to use and the maximum number of transactions for seconds possible with that value?

Pool per tenant or shared pool?

Defined the max number of connections, in this scenario is better having a shared pool or a pool per tenant?

Because the pool size should be low, I guess having a pool per tenant is an hard approach to follow. Just having 2-5 connections per tenant, I would have 200/500 connections and accoring to the video you posted here I would say it's not a good approach.

On the other hand, having a shared pool implies it should be able to manage the load generated from users handling also spikes that occasionally could happen. So, should the shared pool size be adaptive (maybe on the number or tenants)?

So here the question is: is better a pool per tenant or a shared pool? Which is the best approach to size it?

Final considerations to scale up

As final consideration, to scale the application up, I was thinking to have many RDS instances in which I store a subset of tenants. In this way (if the response to the previous question is to use a shared pool) I would have a shared pool for each RDS instance.

Is this a good approach to permit to handle thousands of tenants?

Thanks in advance for your useful hints.

[Daniele Renda]

Hi, any hint about this?

Thanks very much!