Bug #76939 Cleanup of idle connections does not work in all cases
Submitted: 5 May 2015 12:58 Modified: 6 May 2015 5:15
Reporter: Alexander Reinert (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.9.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[5 May 2015 12:58] Alexander Reinert
Description:
I noticed, that the cleanup of idle connections does not work on my typical workload:
We have times, when there are many multiple connections. And we have times, when there is only one connection, but the connection is opened and closed in regular time of about 5 secs. (No real workload, only the monitoring)

In this case the connector opens up many connections on the high workload (as it should), but on the low workload it uses the queue of the pool and "iterates" through the connections, so that they do not have a high idle count and never gettings released.

I've attached a sample code, which reproduces the problem, in the second region there should only be one active connection to the server after some minutes, but even after one hour there will be 20 active connections.

For us this is a serious problem because of the lack of server resources.

How to repeat:
void Main()
{
	#region Simulate high load time with many concurrent connections
	List<MySqlConnection> connections = new List<MySqlConnection>();
	for (int i = 0; i < 20; i++)
	{
		MySqlConnection con = new MySqlConnection("<connection string>");
		con.Open();
		connections.Add(con);
	}
	
	foreach (MySqlConnection con in connections)
	{
		con.Close();
	}
	#endregion
	
	#region Simulate low load time with regular use of only one connection
	while (true)
	{
		using (MySqlConnection con = new MySqlConnection("<connection string>"))
		{
			con.Open();
		}
		Thread.Sleep(5000);
	}
	#endregion
}

Suggested fix:
Do not use a FIFO mechanism, instead use a LIFO mechanism for the connection pool.
I will add a patch for this.
[5 May 2015 12:59] Alexander Reinert
my solution for the issue

Attachment: mysql-pool.patch (application/octet-stream, text), 2.70 KiB.

[5 May 2015 13:00] Alexander Reinert
my solution for the issue

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-pool.patch (application/octet-stream, text), 2.70 KiB.

[6 May 2015 5:15] Chiranjeevi Battula
Hello  	Alexander Reinert,

Thank you for the report and contribution.

Thanks,
Chiranjeevi.