EF Core and big traffic leads to max pool size was reached error
We’re using ASP.NET Entity Framework Core for querying our MSSQL database in our Web API app. Sometimes when we have big traffic, querying to DB ends with this error:
Timeout expired. 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.
I wonder if our pattern of using DbContext and querying is correct or if I am missing some using/dispose pattern and error is caused by some memory leak (after some research I read then I should not use using because the lifetime is managed by the framework). I am following documentation.
then in controllers I used dbcontext by dependency injection:
Should I use something like:
But I think that this is bad pattern when I am using dependency injection of DbContext .
3 Answers 3
I think problem was caused by storing objects from database context queries to In memory cache. I had one big LINQ query to database context with some other subqueries inside. I called FirstOrDefault() on the end of main query but not inside subqueries. Controller was fine with it, it materialize queries by default.
And there was problem — subqueries were holding connection to database context when they where storing to In memory cache. When I implemented Redis distributed cache, it was first failing on some strange errors. It helps when I write ToList() or FirstOrDefault() to all my subqueries because distributed cache needs materialized objects.
Now I have all my queries materialized explicitly and I got no max pool size was reached error. So that one must be careful when stored objects from database context queries to In memory cache. It is need to materialize all queries to avoid to holding connection somewhere in memory.
You can set the lifetime of the DbContext in your startup.cs, see if this helps:
Also if your query is a simple read you can remove tracking by using .AsNoTracking() .
Another way to improve your throughput is to prevent locks by using a transaction block with IsolationLevel.ReadUncommitted for simple reads. You can also use the Snapshot isolation level — which is slightly more restrictive — if you do not want dirty reads.
Edit : As the author of the question mentioned, the above code is not (yet?) possible in EF Core.
A workaround can be found here using an explicit transaction:
I have not tested this.
Edit 2: Another untested snippet where you can have executed commands to set isolation level:
Edit 3: According to that thread, Transactions will be supported from .NET Core version 1.2 onwards.
@mukundabrt this is tracked by dotnet/corefx#2949. Note that TransactionScope has already been ported to .NET Core but will only be available in .NET Core 1.2.
I am adding an alternative answer, in case anyone lands here with a slightly different root cause, as was the case for my .NET Core MVC application.
In my scenario, the application was producing these «timeout expired. max pool size was reached» errors due to mixed use of async / await and Task.Result within the same controller.
I had done this in an attempt to reuse code by calling a certain asynchronous method in my constructor to set a property. Since constructors do not allow asynchronous calls, I was forced to use Task.Result . However, I was using async Task methods to await database calls within the same controller. We engaged Microsoft Support, and an Engineer helped explain why this happens:
Looks like we are making a blocking call to an Async method inside [. ] constructor.
So, basically something is going wrong in the call to above highlighted async method and because of which all the threads listed above are blocked.
Looking at the threads which are doing same operation and blocked:
85.71% of threads blocked (174 threads)
We should avoid mixing async and blocking code. Mixed async and blocking code can cause deadlocks, more-complex error handling and unexpected blocking of context threads.
Action Plan
Please engage your application team to revisit the application code of above mentioned method to understand what is going wrong.
Also, I would appreciate if you could update your application logic to not mix async and blocking code. You could use await Task instead of Task.Wait or Task.Result.
So in our case, I pulled the Task.Result out of the constructor and moved it into a private async method where we could await it. Then, since I only want it to run the task once per use of the controller, I store the result to that local property, and run the task from within that method only if the property value is null .
In my defense, I expected the compiler would at least throw a warning if mixing async and blocking code is so problematic. However, it seems obvious enough to me, in hindsight!
Перевод pool size limit reached no more connections allowed
Лучший отвечающий
Вопрос
What is maximum allowable value of «Max Pool Size» in a connection string.
Suppose this is my connection string in app.config
What is the «maximum» value i can use instead of «1024». Remember it is maximum value not «default» value.
Ответы
There is no documented limit on Max Pool Size. There is however an exact documented limit on maximum number of concurrent connections to a single SQL Server (32767 per instance, see http://msdn.microsoft.com/en-us/library/ms143432.aspx).
A single ADO.NET pool can only go to a single instance, so your maximum effective limit is therefore 32767.
Все ответы
The maximum number of connections allowed in the pool is 100.
If we try to obtain connections more than max pool size , then ADO.NET waits for Connection Timeout for the connection from the pool . If even after that connection is not available, we get the following exception.
«Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool . This may have occurred because all pool ed connections were in use and max pool size was reached«
Every day its a new learning. Keep Learning!!
If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful
How can I solve a connection pool problem between ASP.NET and SQL Server?
The last few days we see this error message in our website too much:
«Timeout expired. 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.»
We have not changed anything in our code in a while. I revised the code to check open connections which didn’t close, but found everything to be fine.
How can I solve this?
Do I need to edit this pool?
How can I edit this pool’s max number of connections?
What is the recommended value for a high traffic website?
Update:
Do I need to edit something in IIS?
Update:
I found that the number of active connections are anywhere from 15 to 31, and I found that the max allowed number of connections configured in SQL server is more than 3200 connections, is 31 too many or should I edit something in the ASP.NET configration?
22 Answers 22
In most cases connection pooling problems are related to connection leaks. Your application probably doesn’t close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.
You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:
The correct way would be this:
When your function returns a connection from a class method make sure you cache it locally and call its Close method. You’ll leak a connection using this code for example:
The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.
If you use SqlDataReader or a OleDbDataReader , close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.
This article «Why Does a Connection Pool Overflow?» from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:
- Run sp_who or sp_who2 . These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you’ll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
- Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you’re familiar with Profiler, this method is easier than polling by using sp_who.
- Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
- Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.