Impact of ADO.NET pools when working with lots of databases

[RaulRG]

I'm sorry for writing this here, but I don't know where to post my question. I am architecting an Azure solution for my company and thinking about using elastic pools and the elastic-db-tools. We will need more than 1,000 databases for our multi-tenant solution.

The server(s) for the Business logic will be stateless and potentially connect to any of these databases (shards). What is the impact when using that many different databases? I found no Information about the elastic-db-tools and the ADO.NET pools. Even if we use the same Connection string for a given database (same credentials) how will the pool work with that many different Connections? Do you increase somehow the size of the pool? What is the Limit?

[RaulRG]

As it seems this project is not very active. My question is quite important for me as my company will not try to migrate our on-premises solution to Azure if we cannot use elastic pools for our customers. If someone reads this an could point me to the right direction I would be very grateful.

[jaredmoo]

A connection pool is unique per connection string, so each shard will have its own connection pool.

You should not need to increase connection pool size because each connection pool will have a limit of 100 by default, which is usually more than enough since you will usually not have more than 100 concurrent connections to a single shard.

[RaulRG]

Thank you very much @jaredmoo . Does this mean that a stateless server could potentially open 100,000 connections to the databases? (100 Connections x 1,000 databases) What would be the impact?

We analyzed our current on-premises solution and usually we don't need more than 30 concurrent connections to serve all requests. Even if we reduced the pool size per database to 30 this could mean 30,000 connections.

[jaredmoo]

No, it could not open that many, because you will run out of ports on the server machine. TCP/IP has a limit of 65,536 ports and many of them are reserved for system use. If you plan to have that many connections, the recommendation is to use a load balancer to partition requests between app servers so that each app server only connects to a limited subset of shards. That way each app server will tend to connect to the same shard more frequently so you can get more efficiency from the connection pool.

[RaulRG]

I thought the impact would be more in the memory use side. I don't think that the server is going to use a different port for every database connection.

We are speaking about stateless webapi servers that will service lots (thousands) of clients. The servers will need to connect to the tenant databases (the shards). Why should they need to open a port for every database connection? I am confused.

[jameswolfisz]

@RaulRG you will need a port available on the host to open a connection to the database. If you have too many connections open at some point you might not be able to open a new one.

Since you will have at least 1 connection string for each tenants database, you are going to end up with lots of connection pools (connection pool fragmentation) and open connections. The whole point of a connection pool is to store connections to be reused. When you close a connection in ADO.NET that is part of a pool, generally it's not actually closed, instead it's just added added back to the pool. This saves you creating and opening the connection again to make the next request.

What @jaredmoo is suggesting is to use an Azure Load Balancer to divide up your tenants into groups e.g. group 1 which goes to app servers 1,2,3,4 which will only connect to the databases for tenants 1-10, group 2 which goes to app servers 5,6,7,8 which will only connect to the databases for tenants 11-20 and so on.

This will reduce connection pool fragmentation as well as ensure you never run out of ports. You also get other benefits by using Azure Load Balancer like health checks and latency statistics. I expect you will probably be using at least one load balancer that sits over the top your applications anyway so might as well take advantage of them.

You could turn off connection pooling but I think your performance would suffer greatly if you make a lot of requests to the databases.

[RaulRG]

Thank you very much for your explanation @jameswolfisz.

Excuse my lack of knowledge about how the system works. I thought that the clients to Azure SQL Server would be something more "stateless" and just request with an ephemeral or short lived port for the response. Now I undersand that the client will keep an open port for every connection!! This will indeed be too much.

[stuartpa]

ADO.Net Connection Pooling ages out unused pooled connections in 4 - 8 minutes, so as long as the number of concurrent requests across all mid-tier nodes to each database server is less than the number of ephemeral ports available on each database server in an 8 minute window, you will be fine. I've seen the default configurations work for all but very largest web sites at peak times (hosting several million concurrent users). I have also seen many folks run into connection pool exhaustion and fragmentation on smaller sites and it has always been a mid-tier coding mistake. For your scenario of 1000 databases I would not expect any problems with the default and any issues would likely be mid-tier code bugs. Just make sure you always return connections to the pool as fast as you can (i.e. always dispose, or .close as soon as possible), don't rely on finalizers. In extreme situations, in any given 8 minute window, it is possible to run into port exhaustion (somewhere between 32k and 64k) between the mid-tier service VIP and the databases, due to the n * m matrix (n = mid tier nodes behind one VIP, and m = database nodes). Introducing a front end load balancer to load balance across more than once instance of the mid-tier service can work around this issue (you get another VIP load of ports), as well as session stickyness between the user-agent (browser, customer app) and the mid-tier they connect into, to reduce the number of mid-tier to back end database connections.

[RaulRG]

Thank you very much @stuartpa !! This was a great answer. I think we will use elastic pools with a given number of databases (say 100) and reserved business servers for them. We can then scale out as needed and they will not need to serve all 1000 databases but just the databases in a pool. We will do some testing as we don't want to over-provision (too expensive)

[dchathu]

@jaredmoo @jameswolfisz @stuartpa How much of memory will a default sized connection pool be using? It seems that for 1,000 databases ADO.NET will create 1,000 connection pools. Since the pool is in the memory of the host, will the host run out of memory?

[stuartpa]

Connection Pools only exist for the duration of connections plus 4 to 8 minutes. So you'll only get as many connection pools as databases a given mid-tier is currently connecting to (plus 4 to 8 minutes), this keeps any memory usage to be minimal.