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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.