Database Connection Pool error

[pampua84]

Hello everybody, very often i have this kind of error on indetity server:

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.

and this is the stacktrace:

System.InvalidOperationException: 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. at Microsoft.Data.Common.ADP.ExceptionWithStackTrace(Exception e) --- End of stack trace from previous location --- at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Duende.IdentityServer.EntityFramework.Stores.ClientStore.FindClientByIdAsync(String clientId) in /_/src/EntityFramework.Storage/Stores/ClientStore.cs:line 84 at Duende.IdentityServer.Stores.ValidatingClientStore1.FindClientByIdAsync(String clientId) in //src/IdentityServer/Stores/ValidatingClientStore.cs:line 74 at Duende.IdentityServer.Stores.IClientStoreExtensions.FindEnabledClientByIdAsync(IClientStore store, String clientId) in //src/IdentityServer/Extensions/IClientStoreExtensions.cs:line 27 at Duende.IdentityServer.Validation.ClientSecretValidator.ValidateAsync(HttpContext context) in //src/IdentityServer/Validation/Default/ClientSecretValidator.cs:line 105 at Duende.IdentityServer.Endpoints.TokenEndpoint.ProcessTokenRequestAsync(HttpContext context) in //src/IdentityServer/Endpoints/TokenEndpoint.cs:line 106 at Duende.IdentityServer.Endpoints.TokenEndpoint.ProcessAsync(HttpContext context) in //src/IdentityServer/Endpoints/TokenEndpoint.cs:line 71 at Duende.IdentityServer.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events, IIssuerNameService issuerNameService, IBackChannelLogoutService backChannelLogoutService) in //src/IdentityServer/Hosting/IdentityServerMiddleware.cs:line 103 at Duende.IdentityServer.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events, IIssuerNameService issuerNameService, IBackChannelLogoutService backChannelLogoutService) in //src/IdentityServer/Hosting/IdentityServerMiddleware.cs:line 103 at Duende.IdentityServer.Hosting.MutualTlsEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes) in //src/IdentityServer/Hosting/MutualTlsEndpointMiddleware.cs:line 95 at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Duende.IdentityServer.Hosting.DynamicProviders.DynamicSchemeAuthenticationMiddleware.Invoke(HttpContext context) in //src/IdentityServer/Hosting/DynamicProviders/DynamicSchemes/DynamicSchemeAuthenticationMiddleware.cs:line 48 at Duende.IdentityServer.Hosting.BaseUrlMiddleware.Invoke(HttpContext context) in //src/IdentityServer/Hosting/BaseUrlMiddleware.cs:line 28 at IAM.AuthN.UI.Middlewares.SamlProxyMiddleware.InvokeAsync(HttpContext context) in C:\AG1_v2.165.0_work\617\s\src\Web\IAM.AuthN.UI\Middlewares\SamlProxyMiddleware.cs:line 42 at NWebsec.AspNetCore.Middleware.Middleware.MiddlewareBase.Invoke(HttpContext context) at NWebsec.AspNetCore.Middleware.Middleware.MiddlewareBase.Invoke(HttpContext context) at IAM.AuthN.UI.Middlewares.NotFoundMiddleware.InvokeAsync(HttpContext context) in C:\AG1_v2.165.0_work\617\s\src\Web\IAM.AuthN.UI\Middlewares\NotFoundMiddleware.cs:line 22 at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)

How can I understand what causes a load so high that the connection pool is saturated? Have you ever met such problem? Thank you.

[StuFrankish]

I've recently come across this while we were load testing one of our other apps that relies on APIs we have implemented into our Identity Server application.

We noticed that the default cap for pool size is set by SQL Server to be 100 if you don't specify it in the connection string. So we set Max Pool Size=32767; on the end of our connection string and despite heavy load testing, we have not encountered these issues again and the active connections to our SQL Server have managed themselves back down after the increased load.

[pampua84]

Hello, first of all thanks for the reply, I wanted to ask you if you had ever tried to use such a solution:

https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?tabs=with-di%2Cwith-constant

instead of manually specifying the MaxPoolSize in the connection string. This actually solves the problem, perhaps this could also have been solved by implementing a better horizontal scaling plan on the servers. What do you think about it? Thank you

[josephdecock]

I would also consider adding caching to the configuration stores, if you're not already doing that. In the stack trace originally posted, the timeout is occurring during a query for config data. Normally that configuration changes very infrequently, so caching it makes a lot of sense. Docs on configuration caching can be found here: https://docs.duendesoftware.com/identityserver/v6/data/configuration/#caching-configuration-data.