多租户:每个租户的单个数据库 [英] Multi-tenancy: Individual database per tenant

问题描述

我们正在开发一个多租户应用程序.关于体系结构，我们设计了用于业务逻辑的共享中间层，并为每个租户设计了一个数据库用于数据持久性.也就是说，业务层将为每个租户与数据库服务器建立一组连接(连接池).这意味着应用程序为每个租户维护一个单独的连接池.如果我们预期有大约5000个租户，则此解决方案需要较高的资源利用率(每个租户的应用程序服务器和数据库服务器之间的连接)，这会导致性能问题.

We are developing a multi-tenant application. With respect to architecture, we have designed shared middle tier for business logic and one database per tenant for data persistence. Saying that, business tier will establish set of connections (connection pool) with the database server per tenant. That means application maintain separate connection-pool for each tenant. If we expect around 5000 tenants, then this solution needs high resource utilization (connections between app server and database server per tenant), that leads to performance issue.

我们通过保留公共连接池来解决此问题.为了在不同数据库之间维护单个连接池，我们创建了一个名为"App-master"的新数据库.现在，我们始终先连接到"App-master"数据库，然后将其更改为租户特定的数据库.这就解决了我们的连接池问题.

We have resolved that by keeping common connection pool. In order to maintain single connection pool across different databases, we have created a new database called ‘App-master’. Now, we always connect to the ‘App-master’ database first and then change the database to tenant specific database. That solved our connection-pool issue.

此解决方案与本地数据库服务器完美配合.但是它不适用于Azure Sql，因为它不支持更改数据库.

This solution works perfectly fine with on-premise database server. But it does not work with Azure Sql as it does not support change database.

提前了解如何维护连接池或更好的方法/最佳实践以应对这种多租户方案.

Appreciate in advance to suggest how to maintain connection pool or better approach / best practice to deal with such multi-tenant scenario.

推荐答案

在使用带有独立数据库的多个租用方案之前，我已经看到了此问题.有两个重叠的问题.每个租户的Web服务器数量，以及租户总数.第一个是更大的问题-如果您正在通过ADO.net连接池缓存数据库连接，则任何特定客户连接进入与其数据库建立开放连接的Web服务器的可能性与您的Web服务器数量成反比有.扩展越多，由于Web服务器代表他们进行与数据库的初始连接，每个给定的客户将看到更多的每次呼叫(而不是初始登录)延迟.每次对非粘性，高度扩展的Web服务器层的调用都将越来越不可能找到可重用的现有开放数据库连接.

I have seen this problem before with multiple tenancy schemes with separate databases. There are two overlapping problems; the number of web servers per tenant, and the total number of tenants. The first is the bigger issue - if you are caching database connections via ADO.net connection pooling then the likelihood of any specific customer connection coming into a web server that has an open connection to their database is inversely proportional to the number of web servers you have. The more you scale out, the more any given customer will notice a per-call (not initial login) delay as the web server makes the initial connection to the database on their behalf. Each call made to a non-sticky, highly scaled, web server tier will be decreasingly likely to find an existing open database connection that can be reused.

第二个问题仅仅是您的池中有如此多连接的一种，并且这可能造成内存压力或性能下降.

The second problem is just one of having so many connections in your pool, and the likelihood of this creating memory pressure or poor performance.

您可以通过建立数量有限的数据库应用程序服务器(简单的WCF端点)来解决"第一个问题，这些服务器代表您的Web服务器执行数据库通信.每个WCF数据库应用程序服务器均服务于一个已知的客户连接池(东部区域转到服务器A，西部区域转到服务器B)，这意味着对于任何给定请求，连接池命中的可能性非常高.这也使您可以分别扩展对数据库的访问权限，以访问HTML呈现Web服务器(数据库是您最关键的性能瓶颈，因此这可能不是一件坏事).

You can "solve" the first problem by establishing a limited number of database application servers (simple WCF endpoints) which carry out database communications on behalf of your web server. Each WCF database application server serves a known pool of customer connections (Eastern Region go to Server A, Western Region go to Server B) which means a very high likelihood of a connection pool hit for any given request. This also allows you to scale access to the database separately to access to HTML rendering web servers (the database is your most critical performance bottleneck so this might not be a bad thing).

第二种解决方案是通过NLB路由器使用特定于内容的路由.这些基于内容路由流量，并允许您按客户分组(西部地区，东部地区等)细分Web服务器层，因此，每组Web服务器的活动连接数要少得多，并且获得连接的可能性也会相应增加.开放和未使用的连接.

A second solution is to use content specific routing via a NLB router. These route traffic based on content and allow you to segment your web server tier by customer grouping (Western Region, Eastern Region etc) and each set of web servers therefore has a much smaller number of active connections with a corresponding increase in the likelihood of getting an open and unused connection.

这两个问题通常都是缓存问题，您将其扩展为完全不粘手"的体系结构越多，则任何调用访问缓存数据的可能性就越小-无论是缓存数据库连接还是读取缓存数据.管理用户连接以最大限度地提高缓存命中的可能性，对于保持高性能非常有用.

Both these problems are issues with caching generally, the more you scale out as a completely "unsticky" architecture, the less likelihood that any call will hit cached data - whether that is a cached database connection, or read-cached data. Managing user connections to allow for maximum likelihood of a cache hit would be useful to maintain high performance.

这篇关于多租户:每个租户的单个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！