Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Many thanks,

share|improve this question
    
Just curious, why would be fixed number of DB connections are expected here? shouldn't be it dynamic as needed? or should the APP Server concern about which DB server is completing its DB request ? –  Anup Shah Oct 21 '13 at 17:47
add comment

1 Answer

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

share|improve this answer
2  
Yep, it does seem unwieldy if we consider that we expect our customer base to double in size every year. That said, isn't it a common multi-tenancy strategy to have one schema per customer? How does this normally get handled? Also, I should probably add that each schema can and regularly does contain 100s of gigabytes of actual data, so converting to a single monolithic schema might not be the way to play either... –  adewinter Oct 22 '13 at 17:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.