I have a busy ASP.NET 5 Core app (thousands of requests per second) that uses SQL Server. Recently we decided to try to switch some hot code paths to async database access and... the app didn't even start. I get this error:
The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
And I see the number of threads in the thread pool growing to 40... 50... 100...
The code pattern we use is fairly simple:
using (var cn = new SqlConnection(connenctionStrng))
{
cn.Open();
var data = await cn.QueryAsync("SELECT x FROM Stuff WHERE id=@id"); //QueryAsync is from Dapper
}
I made a process dump and all threads are stuck on the cn.Open() line, just sitting there and waiting.
This mostly happens during application "recycles" on IIS, when the app process is restarted and HTTP requests are queued from one process to another. Resulting in tens of thousands requests in the queue, that need to be processed.
Well, yeah,, I get it. I think I know what's happening. async makes the app scale more. And while the database is busy responding to my query, the control is returned to other threads. Which try to open more, and more, and more connections in parallel. The connection pool maxes out. But why the closed connections are not returned to the pool immediately after the work is finished?
Switching from async to "traditional" code fixes the problem immediately.
What are my options?
- Increasing max pool size from the default
100? Tried200, didn't help. Should I try, like,10000? - Using
OpenAsyncinstead ofOpen? Didn't help. - I thought I'm running into this problem https://github.com/dotnet/SqlClient/issues/18 but nope, I'm on a newer version of
SqlClientand it's said to be fixed. Supposedly. - Not use
asyncwith database access at all? Huh... - Do we really have to come up with our own throttling mechanisms when using
asynclike this answer suggests? I'm surprised there's no built-in workaround...
P.S. Taking a closer look at the process dump - I checked the Tasks report and discovered literally tens of thousands blocked tasks in the waiting state. And there's exactly 200 db-querying tasks (which is the size of connection pool) waiting for queries to finish.