Bug #66472 Better MySqlPoolManager IdleCleanUpTimer at startup
Submitted: 21 Aug 2012 1:40 Modified: 3 Jan 2013 0:10
Reporter: Poul Bak Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.4.5 OS:Any
Assigned to: Gabriela Martinez Sanchez CPU Architecture:Any
Tags: 6.4.5, IdleCleanUpTimer

[21 Aug 2012 1:40] Poul Bak
Description:
Typically when a program starts up, it creates connections very soon after.
This is especially true for asp.net, that usually starts up when receiving a request.
On a low traffic site, the app might now go idle.
These connections are created right after the timer starts, so when the timer fires 3 minutes later, they will not be old enough to remove, in fact the first timer event will never remove any connections. They will grow almost 6 minutes old before removed.

How to repeat:
Start an asp.net site (one that uses Mysql - doh). Only one request.
Use for example phpMyAdmin ('Status' tab) to see connections to the server.
Watch how the first connections will become almost 6 minutes old before removed.

Suggested fix:
Small change: System.Threading.Timer can have both an initial timeout and an interval.
We simply set the initial timeout to a value 15 seconds higher. Now connections made during the first 15 seconds might be removed on first timer event, if the app is idle.

In 'MySqlPoolManager.cs', line 40:

    static internal int maxConnectionIdleTime = 180;
    // First timeout in seconds;
    static internal int firstIdleTime = 195;

Line 61, change to:

    private static Timer timer = new Timer(new TimerCallback(CleanIdleConnections),
      null, firstIdleTime * 1000, maxConnectionIdleTime * 1000);

That's all.
[21 Aug 2012 2:13] Poul Bak
MySqlPoolManager.cs after change

Attachment: MySqlPoolManager.cs (text/plain), 6.20 KiB.

[21 Aug 2012 3:34] Poul Bak
The change should be able to go into earlier versions of .Net connector too.
[28 Aug 2012 14:54] Poul Bak
I should mention that I have run this code for a week now in production.
[5 Oct 2012 21:24] Gabriela Martinez Sanchez
A fix for this bug has been commited and pushed in the following branches 6.5 and 6.6. To be release in the upcoming versions 6.5.5 and 6.6.3
[3 Jan 2013 0:10] John Russell
Added to changelog for 6.4.6, 6.5.5, 6.6.4: 

When an application starts up, creates a connection, and then goes
idle after a single database operation, the connections are now
cleaned up more quickly: typically after an idle time of 3 minutes
rather than 6 minutes. This optimization is especially useful for
ASP.net applications on low-traffic sites.