Connection Pool Failing Under Load
Connector/NET 8.0.32 on .NET 4.8, ASP.NET web application (running as Azure App Service). MySQL 5.7 (Azure Database for MySQL)
We've been seeing some unexpected behavior under load, and I wanted to ask if anyone has any suggestions.
We have our connection string set up using connection string builder with these settings:
ConnectionTimeout = 7
KeepAlive = 60
ConnectionLifeTime=180
MaximumPoolSize = 80
We have multiple app server instances running our application, all pointing to the same database. Occasionally, and typically during a busy time of day, only one of the app server instances will fail with this error:
"Error Error: MySql.Data.MySqlClient.MySqlException (0x80004005): error connecting: 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."
Normally that would be pretty simple -- we're out of pool connections. However if we check the server connections in MySQL Workbench, we see lots of connections from that app server instance IP in Sleep state for 300+ seconds. It's like for some reason, the driver is not cleaning up or reusing those old connections. That's why it hits the pool limit -- it's not cleaning up or reusing old connections.
When this happens, it's usually during a busy time, and from only ONE of the app server instances. The others hum along just fine, usually with about 30 connections open at any time time, and really low idle time like a few seconds, because they keep getting reused. Ordinarily I'd point to the database or connectivity as the culprit, but we see it with only one app instance (and it's random which one), and they are all running identical code and have the same user load (randomly distributed).
When we restart that app server instance, all the DB connections from that IP disappear in Workbench, and things start working again like usual.
My hunch is it's some kind of race condition in the connection pool that exhibits itself over time or under load. Anyone have any ideas?
Regards,
Justin