How to handle connection pooling for massive multi-tenancy/multi-schema environment Here's a quick run down of the situation:


We have 1 Schema per customer.
We have 2000+ customers.
We have 50+ Database servers (with the above schema's distributed
unevenly amongst them).


We are creating a true stateless app frontend (i.e. improving legacy software).  This means that we could have many (100+) app servers dealing with user traffic, with each app server potentially needing to connect to any one of the 50+ servers to pull data at any one time.

The reason for having so many app servers is that the code is extremely computationally intensive.  It may be that eventually we can move towards fewer app servers with more powerful hardware, but we're not quite there yet.

My question:  How does one manage the connection pooling situation in this type of environment?
If we imagine a "good" connection pool is around 64 connections, it doesn't seem feasible to have each app server generate a connection pool to each DB server. It would result in 100+x64 = 6400 persistent connections being made to each DB server... is that too much?

What can be done?  Is there some sort of connection-pool proxy software that can be used?
You have 2000 schemas spread across 50 servers...I don't think there's a connection pooler that exists to handle that type of situation.  You're going to have to roll your own in application code, I believe.

I'd really step back and take a look at your architecture.  What happens if you double the number of customers?  Triple it?  This type of design seems seriously unmanageable and difficult to scale to new levels of business. 

HTH,
Dave Sisk