Description:
We have upgraded MySQL Connector/Net from 8.0.11 to 9.1.0 and are observing strange behavior with connection pooling. Specifically, we are setting the MinPoolSize to 20, and while the application server initially creates 20 connections, these connections are reduced to 2-3 after a short period. The MinPoolSize setting is not consistently honoured.
Our wait_timeout and interactive_timeout are set to 600 seconds, and all connections are dropped around 630+ seconds. When we changed these parameters to 24 hours, the MinPoolSize was consistently maintained at 20 connections.
This behavior does not seem correct as the connector should always honour the MinPoolSize configuration.
How to repeat:
#####Setup a Local Mysql Server:#####
Pull the MySQL 8.0.28 Docker Image (Our DB uses MySQL 8.0.28)
docker pull mysql:8.0.28
Use the docker run command to start a MySQL container.
docker run --name mysql8028-server -e MYSQL_ROOT_PASSWORD=Password1 -e MYSQL_DATABASE=testdb -e MYSQL_USER=testuser -e MYSQL_PASSWORD=Password1 -p 3306:3306 -d mysql:8.0.28
Verify the MySQL Server
docker ps
Pull the MySQL 8 Docker Image
docker exec -it mysql8028-server mysql -u root -p
enter Password1
#####Adjust MySQL Timeout Settings:#####
by default: wait_timeout and interactive_timeout is 28800 seconds = 8 hours.
Shorter the timeout to 60 seconds for demo.
#####Demo Code: Verifying MySql.Data Pooling Behavior#####
Create a Console app in VisualStudio
Install Different Versions of MySql.Data:
Use NuGet to install a specific version:
dotnet add package MySql.Data --version 8.0.29
or
dotnet add package MySql.Data --version 9.1.0
#####Copy the below codes into the demo app:#####
#######################################################
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace Mysql.Data.Demo
{
class Program
{
static async Task Main(string[] args)
{
// Get the MySql.Data assembly
Assembly mysqlDataAssembly = typeof(MySqlConnection).Assembly;
// Get the version from the assembly
Version version = mysqlDataAssembly.GetName().Version;
string connectionString =
"Server=localhost;Port=3306; UID=testuser; PWD=Password1; CharSet=utf8mb4;sslMode=REQUIRED;Pooling=true;MinPoolSize=5;MaxPoolSize=10;";
Console.WriteLine($"MySql.Data version: {version}; Server settings:wait_time: 60; interactive_timeout: 60; ConnectionString: {connectionString};");
Console.WriteLine($"Time: {DateTime.Now}; Demo starting...\n");
// Create connections to warm up the pool
await WarmUpConnectionPool(connectionString);
Console.WriteLine($"Time: {DateTime.Now};Monitoring connection pool behavior...");
Console.WriteLine("(Press Ctrl+C to stop)");
// Periodically log connection IDs and count of active connections
await MonitorConnectionPool(connectionString);
}
static async Task WarmUpConnectionPool(string connectionString)
{
Console.WriteLine($"Time: {DateTime.Now};Warming up connection pool...");
List<int> connectionIds = new List<int>();
for (int i = 0; i < 5; i++) // Match MinPoolSize
{
int connectionId = await UseConnectionAsync(connectionString, i);
if (!connectionIds.Contains(connectionId))
{
connectionIds.Add(connectionId);
}
// Short delay to ensure connections are created
await Task.Delay(200);
}
Console.WriteLine($"Time: {DateTime.Now}; Warm-up complete. Connections initialized.");
}
static async Task MonitorConnectionPool(string connectionString)
{
while (true)
{
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Query all active connections
using (var command = new MySqlCommand("SHOW PROCESSLIST;", connection))
using (var reader = await command.ExecuteReaderAsync())
{
List<int> connectionIds = new List<int>();
while (await reader.ReadAsync())
{
// Collect connection IDs for this user
string user = reader["User"].ToString();
if (user == "testuser")
{
int connectionId = Convert.ToInt32(reader["Id"]);
connectionIds.Add(connectionId);
}
}
Console.WriteLine($"Time: {DateTime.Now}; Active Connections: {connectionIds.Count} | Connection IDs: {string.Join(", ", connectionIds)}");
}
}
// Pause between monitoring cycles
await Task.Delay(5000);
}
}
static async Task<int> UseConnectionAsync(string connectionString, int iteration)
{
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
// Query the connection ID
using (var command = new MySqlCommand("SELECT CONNECTION_ID();", connection))
{
var connectionId = Convert.ToInt32(await command.ExecuteScalarAsync());
Console.WriteLine($"Time: {DateTime.Now}; Warm-up Iteration {iteration}: connection.OpenAsync() then execute SELECT CONNECTION_ID(), get Connection ID = {connectionId}");
return connectionId;
}
}
}
}
}
#######################################################
#####Run with the version MySql.Data 8.0.29, below is the result:#####
Refer attached screenshot 1.
#####Run with the version MySql.Data 9.1.0, below is the result:#####
Refer attached screenshot 2.
#####Observation:#####
#####8.0.29:
Connections will rotate evenly as each is dequeued and enqueued.
The number of active connections matches MinPoolSize consistently, even during periods of inactivity.
#####9.1.0:
Recent connections are reused preferentially, leaving older ones idle.
The number of active connections drop below MinPoolSize as idle connections are not retained after wait_timeout.
#####Why different behaviors?#####
https://github.com/mysql/mysql-connector-net/commit/305f5376d0599684d12e0dc53b3ed377c3cc07...
In this change, the intention was (https://dev.mysql.com/doc/relnotes/connector-net/en/news-8-0-30.html)
The mechanism used to clean up idle connections in the connection pool performed poorly. This fix changes the idle list from type Queue<T> to type LinkedList<T> to reduce the overall number of idle connections remaining in the pool after a connection surge is over and the load requirement is reduced. (Bug #106368, Bug #33935441)
However, this change led to a difference in how the pooled connection is obtained from the pool. See method "GetPooledConnection". In L114, it changed from
version < 8.0.30 (say old)
driver = _idlePool.Dequeue();
to
version >= 8.0.30 (say new)
driver = _idlePool.Last.Value; _idlePool.RemoveLast();
and till now.
The key difference here is that, in the old version, obtaining a connection from the pool behaves like this
get a connection from the head of queue (queue.Dequeue())
after finishing, return the connection to the end of the queue (queue.Enqueue())
However in the new one, it becomes
get a connection from the end of the queue(linklist.RemoveLast())
after finishing, return the connection to the end of the queue (linklist.AddLast())
This led to a difference that, in the old version, all the connections (min pool size) take turns in performing database commands, however, in the new version, it always prefer the recent-used connections to take the priority to perform database commands. So in our case, those connections at the first half of the "linklist" eventually become unusable due to no activity.
Suggested fix:
Should change getting a connection from last of the list to first of list
https://github.com/mysql/mysql-connector-net/blob/9.x/MySQL.Data/src/MySqlPool.cs#L135
#######################################################
driver = _idlePool.Last.Value;
_idlePool.RemoveLast();
#######################################################
See connection is getting from the last of the _idlePool list, should it change to get from the first of the list?
#######################################################
driver = _idlePool.First.Value;
_idlePool.RemoveFirst();
#######################################################
Description: We have upgraded MySQL Connector/Net from 8.0.11 to 9.1.0 and are observing strange behavior with connection pooling. Specifically, we are setting the MinPoolSize to 20, and while the application server initially creates 20 connections, these connections are reduced to 2-3 after a short period. The MinPoolSize setting is not consistently honoured. Our wait_timeout and interactive_timeout are set to 600 seconds, and all connections are dropped around 630+ seconds. When we changed these parameters to 24 hours, the MinPoolSize was consistently maintained at 20 connections. This behavior does not seem correct as the connector should always honour the MinPoolSize configuration. How to repeat: #####Setup a Local Mysql Server:##### Pull the MySQL 8.0.28 Docker Image (Our DB uses MySQL 8.0.28) docker pull mysql:8.0.28 Use the docker run command to start a MySQL container. docker run --name mysql8028-server -e MYSQL_ROOT_PASSWORD=Password1 -e MYSQL_DATABASE=testdb -e MYSQL_USER=testuser -e MYSQL_PASSWORD=Password1 -p 3306:3306 -d mysql:8.0.28 Verify the MySQL Server docker ps Pull the MySQL 8 Docker Image docker exec -it mysql8028-server mysql -u root -p enter Password1 #####Adjust MySQL Timeout Settings:##### by default: wait_timeout and interactive_timeout is 28800 seconds = 8 hours. Shorter the timeout to 60 seconds for demo. #####Demo Code: Verifying MySql.Data Pooling Behavior##### Create a Console app in VisualStudio Install Different Versions of MySql.Data: Use NuGet to install a specific version: dotnet add package MySql.Data --version 8.0.29 or dotnet add package MySql.Data --version 9.1.0 #####Copy the below codes into the demo app:##### ####################################################### using System; using System.Collections.Generic; using System.Reflection; using System.Threading.Tasks; using MySql.Data.MySqlClient; namespace Mysql.Data.Demo { class Program { static async Task Main(string[] args) { // Get the MySql.Data assembly Assembly mysqlDataAssembly = typeof(MySqlConnection).Assembly; // Get the version from the assembly Version version = mysqlDataAssembly.GetName().Version; string connectionString = "Server=localhost;Port=3306; UID=testuser; PWD=Password1; CharSet=utf8mb4;sslMode=REQUIRED;Pooling=true;MinPoolSize=5;MaxPoolSize=10;"; Console.WriteLine($"MySql.Data version: {version}; Server settings:wait_time: 60; interactive_timeout: 60; ConnectionString: {connectionString};"); Console.WriteLine($"Time: {DateTime.Now}; Demo starting...\n"); // Create connections to warm up the pool await WarmUpConnectionPool(connectionString); Console.WriteLine($"Time: {DateTime.Now};Monitoring connection pool behavior..."); Console.WriteLine("(Press Ctrl+C to stop)"); // Periodically log connection IDs and count of active connections await MonitorConnectionPool(connectionString); } static async Task WarmUpConnectionPool(string connectionString) { Console.WriteLine($"Time: {DateTime.Now};Warming up connection pool..."); List<int> connectionIds = new List<int>(); for (int i = 0; i < 5; i++) // Match MinPoolSize { int connectionId = await UseConnectionAsync(connectionString, i); if (!connectionIds.Contains(connectionId)) { connectionIds.Add(connectionId); } // Short delay to ensure connections are created await Task.Delay(200); } Console.WriteLine($"Time: {DateTime.Now}; Warm-up complete. Connections initialized."); } static async Task MonitorConnectionPool(string connectionString) { while (true) { using (var connection = new MySqlConnection(connectionString)) { await connection.OpenAsync(); // Query all active connections using (var command = new MySqlCommand("SHOW PROCESSLIST;", connection)) using (var reader = await command.ExecuteReaderAsync()) { List<int> connectionIds = new List<int>(); while (await reader.ReadAsync()) { // Collect connection IDs for this user string user = reader["User"].ToString(); if (user == "testuser") { int connectionId = Convert.ToInt32(reader["Id"]); connectionIds.Add(connectionId); } } Console.WriteLine($"Time: {DateTime.Now}; Active Connections: {connectionIds.Count} | Connection IDs: {string.Join(", ", connectionIds)}"); } } // Pause between monitoring cycles await Task.Delay(5000); } } static async Task<int> UseConnectionAsync(string connectionString, int iteration) { using (var connection = new MySqlConnection(connectionString)) { await connection.OpenAsync(); // Query the connection ID using (var command = new MySqlCommand("SELECT CONNECTION_ID();", connection)) { var connectionId = Convert.ToInt32(await command.ExecuteScalarAsync()); Console.WriteLine($"Time: {DateTime.Now}; Warm-up Iteration {iteration}: connection.OpenAsync() then execute SELECT CONNECTION_ID(), get Connection ID = {connectionId}"); return connectionId; } } } } } ####################################################### #####Run with the version MySql.Data 8.0.29, below is the result:##### Refer attached screenshot 1. #####Run with the version MySql.Data 9.1.0, below is the result:##### Refer attached screenshot 2. #####Observation:##### #####8.0.29: Connections will rotate evenly as each is dequeued and enqueued. The number of active connections matches MinPoolSize consistently, even during periods of inactivity. #####9.1.0: Recent connections are reused preferentially, leaving older ones idle. The number of active connections drop below MinPoolSize as idle connections are not retained after wait_timeout. #####Why different behaviors?##### https://github.com/mysql/mysql-connector-net/commit/305f5376d0599684d12e0dc53b3ed377c3cc07... In this change, the intention was (https://dev.mysql.com/doc/relnotes/connector-net/en/news-8-0-30.html) The mechanism used to clean up idle connections in the connection pool performed poorly. This fix changes the idle list from type Queue<T> to type LinkedList<T> to reduce the overall number of idle connections remaining in the pool after a connection surge is over and the load requirement is reduced. (Bug #106368, Bug #33935441) However, this change led to a difference in how the pooled connection is obtained from the pool. See method "GetPooledConnection". In L114, it changed from version < 8.0.30 (say old) driver = _idlePool.Dequeue(); to version >= 8.0.30 (say new) driver = _idlePool.Last.Value; _idlePool.RemoveLast(); and till now. The key difference here is that, in the old version, obtaining a connection from the pool behaves like this get a connection from the head of queue (queue.Dequeue()) after finishing, return the connection to the end of the queue (queue.Enqueue()) However in the new one, it becomes get a connection from the end of the queue(linklist.RemoveLast()) after finishing, return the connection to the end of the queue (linklist.AddLast()) This led to a difference that, in the old version, all the connections (min pool size) take turns in performing database commands, however, in the new version, it always prefer the recent-used connections to take the priority to perform database commands. So in our case, those connections at the first half of the "linklist" eventually become unusable due to no activity. Suggested fix: Should change getting a connection from last of the list to first of list https://github.com/mysql/mysql-connector-net/blob/9.x/MySQL.Data/src/MySqlPool.cs#L135 ####################################################### driver = _idlePool.Last.Value; _idlePool.RemoveLast(); ####################################################### See connection is getting from the last of the _idlePool list, should it change to get from the first of the list? ####################################################### driver = _idlePool.First.Value; _idlePool.RemoveFirst(); #######################################################