How Long Does MySql.Data Keep Idle Connections in the Pool?

After a burst, a connection pool holds a lot of open connections. How long do they stay? I opened 8, closed them, and counted them every minute: they go after about 3 minutes, down to Minimum Pool Size.

The Test

This opens 8 connections at once, closes them, then counts them on the server each minute. The counting uses a connection string with no database, which makes a separate pool, so it doesn't disturb what it's counting:

min-pool.cs

#:package MySql.Data@26.10.0

using MySql.Data.MySqlClient;

var connectionString = Environment.GetEnvironmentVariable("MYSQL_CONNECTION_STRING")
    ?? throw new InvalidOperationException("Set the MYSQL_CONNECTION_STRING environment variable first.");

// Opens 8 connections at once, closes them, then prints how many stay open as time passes.
var open = new List<MySqlConnection>();
for (var i = 0; i < 8; i++)
{
    var connection = new MySqlConnection(connectionString);
    await connection.OpenAsync();
    open.Add(connection);
}

async Task PrintCountAsync(string label)
{
    await using var check = new MySqlConnection(connectionString.Replace("Database=inventory;", ""));
    await check.OpenAsync();
    await using var command = new MySqlCommand(
        "SELECT COUNT(*) FROM performance_schema.threads WHERE PROCESSLIST_USER = 'pool_demo' AND PROCESSLIST_DB = 'inventory'",
        check);
    Console.WriteLine($"{label}: {await command.ExecuteScalarAsync()} connections");
}

await PrintCountAsync("All 8 open");

foreach (var connection in open)
{
    await connection.DisposeAsync();
}

await PrintCountAsync("All 8 closed");

for (var minute = 1; minute <= 5; minute++)
{
    await Task.Delay(TimeSpan.FromMinutes(1));
    await PrintCountAsync($"After {minute} min");
}

The account here is pool_demo, with SELECT on inventory and on performance_schema.threads.

With the Default

Output, with no Minimum Pool Size

All 8 open: 8 connections
All 8 closed: 8 connections
After 1 min: 8 connections
After 2 min: 8 connections
After 3 min: 8 connections
After 4 min: 0 connections
After 5 min: 0 connections

Closing all 8 didn't close any: they went back to the pool. Between 3 and 4 minutes, they were gone. An earlier test of a running API, polling every 15 seconds, had 20 connections until 183 seconds, and none at 198.

With Minimum Pool Size=3

Connection string

…;SslMode=Required;Minimum Pool Size=3

Output

All 8 open: 8 connections
All 8 closed: 8 connections
After 1 min: 8 connections
After 2 min: 8 connections
After 3 min: 8 connections
After 4 min: 3 connections
After 5 min: 3 connections

It kept 3, as asked. Those stay open however long they're idle.

Why About 3 Minutes

Oracle's documentation explains it: "Connector/NET runs a background job every three minutes and removes connections from pool that have been idle (unused) for more than three minutes." The driver's source has the same number, 180 seconds, and no connection string option changes it. So the exact moment depends on when the job next runs, which is why it's "about".

Minimum Pool Size: Should You Set It?

It keeps connections ready, so the first request after a quiet spell doesn't pay for opening one. The price is connections held open for ever. Things to know:

• It doesn't open them at startup. With Minimum Pool Size=5, the API held no connections until its first request, and that request opened all 5, taking 0.65 seconds in all.
• Above Maximum Pool Size, it's quietly lowered to match.
• For most apps, leave it at 0. A connection takes a fraction of a second to open. Set it if that delay matters after quiet periods.

Does It Conflict with wait_timeout?

MySQL closes a connection after wait_timeout, 8 hours by default. The pool closes its idle ones long before that. With a minimum set, connections could reach it, and the driver copes: I set wait_timeout to 5 seconds, and the next request got a new connection, with no error.

Common Mistakes

Mistake	What happens	Do this instead
Reading Sleep connections as a leak	That's the pool, for up to 3 minutes	Check again later
A high minimum, to feel safe	Connections sit idle for ever, and count against max_connections	Leave it at 0
Expecting closing to close the connection	It goes back to the pool	Clear the pool if you need that

Troubleshooting

Symptom	Likely cause	Fix
Connections stay for hours	Minimum Pool Size, or they're being used	Check the setting and PROCESSLIST_TIME
The first request after a quiet spell is slow	The pool emptied	Set a small minimum, if it matters
ERROR 1142 from the counting query	The account can't read performance_schema.threads	Grant SELECT on it, or use an administrator

Conclusion

MySql.Data closes idle connections after about 3 minutes, down to Minimum Pool Size, and you can't change the timing. For most apps, the default of 0 is right.

Additional Resources

• Managing a Connection Pool in Connector/NET — the three minutes, quoted above.
• MySqlPoolManager.cs, tag 26.10.0 — the job.

Related

• How to See the MySql.Data Connection Pool Working
• How to Set Connection Lifetime in MySql.Data
• How to Change wait_timeout in MySQL