I have a .net core API using Entity Framework Core. The DB context is registered in startup.cs like this:
services.AddDbContext<AppDBContext>(options => options.UseSqlServer(connectionString, providerOptions => providerOptions.CommandTimeout(60)));
In connection string I set
Pooling=true;Max Pool Size=100;Connection Timeout=300
The controller calls methods in a service which in turn makes calls to aysnc methods in a repo for data retrieval and processing.
All worked well if concurrent user is under 500 during load testing. However beyond that number I start to see a lot of timeout expired errors. When I checked the database, there’s no deadlock but I could see well over 100 connections in sleeping mode(the API is hosted on two kubernetes pods). I monitored these connections during the testing and it appeared that instead of current sleeping connections being reused, new ones were added to the pool. My understanding is entity framework core manages opening and closing connections but this didn’t seem to be the case. Or am I missing anything?
The error looks like this:
StatusCode":500,"Message":"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. Stack Trace: at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource
1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)n at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)n at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)n at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)n at Microsoft.Data.SqlClient.SqlConnection.Open()n at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)n at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)n at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)n…………………
An example of how the dbcontext was used:
the controller calls a method in a service class:
var result = await _myservice.SaveUserStatusAsync(userId, status);
then in ‘myservice’:
var user = await _userRepo.GetUserAsync(userId); ....set user status to new value and then return await _userRepo.UpdateUserAsync(user);
then in ‘userrepo’:
_context.user.Update(user); var updated = await _context.SaveChangesAsync(); return updated > 0;