5

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?

  • 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
  • I realize this is an old question but it will be useful if you can share what you ended up doing. – Zaid Masud May 8 '18 at 20:00
  • You can have another service layer between app-servers and db. These service layers won't do computation work but just fetch data on behalf of app-servers. – Ashish Negi May 26 '18 at 9:58
1

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

  • 3
    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
1

I would suggest 1 database with a separate user for each tenant. Use MySQL user security to segment the data.

  1. Create a database user for each tenant
  2. Add a tenant_id column (VARCHAR) to every table
  3. Use a trigger to populate the tenant_id column with the current database user on INSERT
  4. Create a view for every table that filters to rows where id_tenant = current_database_user (don't include the tenant_id column in this view)
  5. Restrict the tenant database user to only use these views

Then you will only have 1 database to maintain and scale. However, connection pooling is still a potential problem as subsequent requests could use different database users. Depending on the pool implementation this may not be a problem though.

I've documented this approach in my blog here: https://opensource.io/it/mysql-multi-tenant/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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