Connection timeouts connecting to Azure Database for MySql Flexible Server (with apparently no resource exhaustion)

Fabricio Rodriguez 1 Reputation point
2024-04-03T11:56:16.6833333+00:00

We have an ASP .Net 8 Web API hosted on Azure App Service (Windows). It connects to an Azure Database for MySql Flexible Server v5.7, using Pomelo.EntityFrameworkCore.MySql 8.0.2. We are not using Private Access/VNet. It is a multi-tenant application, and all databases are InnoDB databases.

When we were on the older Azure Database for MySql SINGLE server, we never had any connection issues. It had 4 vCores and 32 GB RAM. Since we moved to the newer Azure Database for MySql FLEXIBLE server, also with 4 vCores and 32 GB RAM, we occasionally (and by this, I mean perhaps a dozen times a day) get connection timeout errors like these:

MySqlConnector.MySqlException (0x80004005): Connect Timeout expired. ---> MySqlConnector.MySqlException (0x80004005): Connect Timeout expired. at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/ServerSession.cs:line 1046 at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int64 startingTimestamp, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/ServerSession.cs:line 425 at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 932

at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 938
```> ```vba
> at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 951
> ``````yaml
at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 419
```> ```powershell
> at Microsoft.EntityFrameworkCore.ServerVersion.AutoDetect(String connectionString)
> at PropWorx.API.Extensions.ConfigureExtensions.<>c__DisplayClass5_0.<DbContextConfiguration>b__0(DbContextOptionsBuilder options) in C:\Users\fabsr\source\repos\PropWorx.API\PropWorx.API\Extensions\ConfigureExtensions.cs:line 150
> at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.CreateDbContextOptions[TContext]
> at ResolveService(ILEmitResolverBuilderRuntimeContext, ServiceProviderEngineScope)
> at ResolveService(ILEmitResolverBuilderRuntimeContext, ServiceProviderEngineScope)
> at ResolveService(ILEmitResolverBuilderRuntimeContext, ServiceProviderEngineScope)
> at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
> at lambda_method134(Closure, Object, HttpContext, IServiceProvider)
> at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
> at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
> at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
> at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
> at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
> ```When this happens, it happens for around a minute, and it affects all users trying to connect at that point. Moreover, although I do see an increase in queries being executed at that point in time (perhaps double the usual) - which most likely is related to the connection timeout - both CPU and RAM usage are low (around 15%) and the connection count is way below the limit, at around 500 connections (max_connections is 2731).

The connection string I'm using in the Web API is pretty basic:


```javascript
$"Server={serverAddress};UserID={username};Password={password};Database={databaseName};SslMode=Required;TreatTinyAsBoolean=true;ConvertZeroDateTime=true;"

I have also tried increasing the conenct_timeout from the default of 10 seconds to 30 seconds, with no apparent difference.

I am using EnableRetryOnFailure:

DbContextOptions<TenantContext> options = new DbContextOptionsBuilder<TenantContext>()
    .UseMySql(connectionString,
        new MySqlServerVersion(ServerVersion.AutoDetect(connectionString)),
        builder => builder.EnableRetryOnFailure())
    .Options;

My question is, what else could be the culprit? What else should I look at?

Nothing has changed in our API code. The only thing that's changed is the type of MySql Server.

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
710 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,199 questions
Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
6,826 questions
{count} votes