MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Vladislav Vaintroub Date:October 9 2009 4:04pm
Subject:bzr push into connector-net-trunk branch (vvaintroub:778 to 779)
View as plain text  
  779 Vladislav Vaintroub	2009-10-09
      Update CHANGES, mention pool cleanup

    modified:
      CHANGES
  778 Vladislav Vaintroub	2009-10-09 [merge]
      merge

    added:
      MySql.Data/Provider/Source/common/Utilities.cs
    renamed:
      MySql.Data/Provider/Source/cf/Stopwatch.cs => MySql.Data/Provider/Source/common/LowResolutionStopwatch.cs
    modified:
      CHANGES
      MySql.Data/Provider/MySql.Data.CF.csproj
      MySql.Data/Provider/MySql.Data.csproj
      MySql.Data/Provider/Properties/Resources.Designer.cs
      MySql.Data/Provider/Properties/Resources.resx
      MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs
      MySql.Data/Provider/Source/MySqlStream.cs
      MySql.Data/Provider/Source/TimedStream.cs
      MySql.Data/Provider/Source/common/SharedMemoryStream.cs
      MySql.Data/Provider/Source/common/StreamCreator.cs
      MySql.Data/Provider/Source/common/LowResolutionStopwatch.cs
=== modified file 'CHANGES'
--- a/CHANGES	2009-10-08 15:55:20 +0000
+++ b/CHANGES	2009-10-09 16:04:21 +0000
@@ -1,4 +1,5 @@
 Version 6.2.0
+- we now cleanup idle connections in the pool, if they were not used for too long
 - refactored handling of connection and command timeouts
   to use timeout on the underlying stream
 - completely refactored MySqlConnectionStringBuilder

=== modified file 'MySql.Data/Provider/Source/Driver.cs'
--- a/MySql.Data/Provider/Source/Driver.cs	2009-09-24 21:59:05 +0000
+++ b/MySql.Data/Provider/Source/Driver.cs	2009-10-05 22:09:22 +0000
@@ -48,12 +48,24 @@ namespace MySql.Data.MySqlClient
         protected Hashtable charSets;
         protected bool hasWarnings;
         protected long maxPacketSize;
+        private DateTime idleSince;
+
 #if !CF
         protected MySqlPromotableTransaction currentTransaction;
         protected bool inActiveUse;
 #endif
         protected MySqlPool pool;
 
+        /// <summary>
+        /// For pooled connections, time when the driver was
+        /// put into idle queue
+        /// </summary>
+        public DateTime IdleSince
+        {
+            get { return idleSince; }
+            set { idleSince = value; }
+        }
+
         public Driver(MySqlConnectionStringBuilder settings)
         {
             encoding = Encoding.GetEncoding(1252);
@@ -150,7 +162,7 @@ namespace MySql.Data.MySqlClient
             return (string) serverProps[key];
         }
 
-        public bool IsTooOld()
+        public bool ConnectionLifetimeExpired()
         {
             TimeSpan ts = DateTime.Now.Subtract(creationTime);
             if (Settings.ConnectionLifeTime != 0 &&

=== modified file 'MySql.Data/Provider/Source/MySqlPool.cs'
--- a/MySql.Data/Provider/Source/MySqlPool.cs	2009-08-03 16:37:40 +0000
+++ b/MySql.Data/Provider/Source/MySqlPool.cs	2009-10-05 22:09:22 +0000
@@ -27,6 +27,8 @@ using MySql.Data.MySqlClient.Properties;
 
 namespace MySql.Data.MySqlClient
 {
+
+
 	/// <summary>
 	/// Summary description for MySqlPool.
 	/// </summary>
@@ -42,6 +44,11 @@ namespace MySql.Data.MySqlClient
         private int available;
         private AutoResetEvent autoEvent;
 
+        private void EnqueueIdle(Driver driver)
+        {
+            driver.IdleSince = DateTime.Now;
+            idlePool.Enqueue(driver);
+        }
 		public MySqlPool(MySqlConnectionStringBuilder settings)
 		{
 			minSize = settings.MinimumPoolSize;
@@ -58,7 +65,7 @@ namespace MySql.Data.MySqlClient
 
 			// prepopulate the idle pool to minSize
             for (int i = 0; i < minSize; i++)
-                idlePool.Enqueue(CreateNewPooledConnection());
+               EnqueueIdle(CreateNewPooledConnection());
 
             procedureCache = new ProcedureCache((int)settings.ProcedureCacheSize);
         }
@@ -112,7 +119,7 @@ namespace MySql.Data.MySqlClient
             lock ((idlePool as ICollection).SyncRoot)
             {
                 if (HasIdleConnections)
-                    driver = (Driver)idlePool.Dequeue();
+                    driver = idlePool.Dequeue();
             }
 
             // Obey the connection timeout
@@ -173,7 +180,7 @@ namespace MySql.Data.MySqlClient
                     inUsePool.Remove(driver);
             }
 
-            if (driver.IsTooOld() || beingCleared)
+            if (driver.ConnectionLifetimeExpired() || beingCleared)
             {
                 driver.Close();
                 Debug.Assert(!idlePool.Contains(driver));
@@ -182,7 +189,7 @@ namespace MySql.Data.MySqlClient
             {
                 lock ((idlePool as ICollection).SyncRoot)
                 {
-                    idlePool.Enqueue(driver);
+                    EnqueueIdle(driver);
                 }
             }
 
@@ -280,5 +287,45 @@ namespace MySql.Data.MySqlClient
                 // be destroyed.
             }
         }
-	}
+
+        /// <summary>
+        /// Remove expired drivers from the idle pool
+        /// </summary>
+        /// <returns></returns>
+        /// <remarks>
+        /// Closing driver is a potentially lengthy operation involving network
+        /// IO. Therefore we do not close expired drivers while holding 
+        /// idlePool.SyncRoot lock. We just remove the old drivers from the idle
+        /// queue and return them to the caller. The caller will need to close 
+        /// them (or let GC close them)
+        /// </remarks>
+        internal List<Driver> RemoveOldIdleConnections()
+        {
+            List<Driver> oldDrivers = new List<Driver>();
+            DateTime now = DateTime.Now;
+
+            lock ((idlePool as ICollection).SyncRoot)
+            {
+                // The drivers appear to be ordered by their age, i.e it is
+                // sufficient to remove them until the first element is not
+                // too old.
+                while(idlePool.Count > minSize)
+                {
+                    Driver d = idlePool.Peek();
+                    DateTime expirationTime = d.IdleSince.Add(
+                        new TimeSpan(0,0, MySqlPoolManager.maxConnectionIdleTime));
+                    if (expirationTime.CompareTo(now) < 0)
+                    {
+                        oldDrivers.Add(d);
+                        idlePool.Dequeue();
+                    }
+                    else
+                    {
+                        break;
+                    }
+                }
+            }
+            return oldDrivers;
+        }
+    }
 }

=== modified file 'MySql.Data/Provider/Source/MySqlPoolManager.cs'
--- a/MySql.Data/Provider/Source/MySqlPoolManager.cs	2009-08-19 21:36:48 +0000
+++ b/MySql.Data/Provider/Source/MySqlPoolManager.cs	2009-10-05 22:09:22 +0000
@@ -21,6 +21,7 @@
 using System.Collections;
 using System.Diagnostics;
 using System.Collections.Generic;
+using System.Threading;
 
 namespace MySql.Data.MySqlClient
 {
@@ -32,6 +33,14 @@ namespace MySql.Data.MySqlClient
         private static Hashtable pools = new Hashtable();
         private static List<MySqlPool> clearingPools = new List<MySqlPool>();
 
+        // Timeout in seconds, after which an unused (idle) connection 
+        // should be closed.
+        static internal int maxConnectionIdleTime = 180;
+
+
+        private static Timer timer = new Timer(new TimerCallback(CleanIdleConnections),
+            null, maxConnectionIdleTime*1000, maxConnectionIdleTime*1000);
+
         public static MySqlPool GetPool(MySqlConnectionStringBuilder settings)
         {
             string text = settings.ConnectionString;
@@ -120,5 +129,25 @@ namespace MySql.Data.MySqlClient
             Debug.Assert(clearingPools.Contains(pool));
             clearingPools.Remove(pool);
         }
+
+        /// <summary>
+        /// Remove drivers that have been idle for too long.
+        /// </summary>
+        public static void CleanIdleConnections(object obj)
+        {
+            List<Driver> oldDrivers = new List<Driver>();
+            lock (pools.SyncRoot)
+            {
+                foreach (string key in pools.Keys)
+                {
+                    MySqlPool pool = (pools[key] as MySqlPool);
+                    oldDrivers.AddRange(pool.RemoveOldIdleConnections());
+                }
+            }
+            foreach(Driver driver in oldDrivers)
+            {
+                driver.Close();
+            }
+        }
     }
 }
\ No newline at end of file

=== modified file 'MySql.Data/Tests/Source/PoolingTests.cs'
--- a/MySql.Data/Tests/Source/PoolingTests.cs	2009-10-05 18:06:21 +0000
+++ b/MySql.Data/Tests/Source/PoolingTests.cs	2009-10-05 22:09:22 +0000
@@ -25,7 +25,7 @@ using NUnit.Framework;
 using System.Reflection;
 using System.Collections;
 using System.Collections.Generic;
-
+ 
 namespace MySql.Data.MySqlClient.Tests
 {
 	/// <summary>
@@ -297,6 +297,74 @@ namespace MySql.Data.MySqlClient.Tests
 						c.Close();*/
 		}
 
+        bool IsConnectionAlive(int serverThread)
+        {
+            MySqlDataAdapter da = new MySqlDataAdapter("SHOW PROCESSLIST", conn);
+            DataTable dt = new DataTable();
+            da.Fill(dt);
+            foreach (DataRow row in dt.Rows)
+                if ((long)row["Id"] == serverThread)
+                    return true;
+            return false;
+        }
+
+        [Test]
+        public void CleanIdleConnections()
+        {
+            string assemblyName = typeof(MySqlConnection).Assembly.FullName;
+            string pmName = String.Format("MySql.Data.MySqlClient.MySqlPoolManager, {0}", assemblyName);
+
+            Type poolManager = Type.GetType(pmName, false);
+            FieldInfo poolManagerTimerField = poolManager.GetField("timer",
+                BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance);
+            FieldInfo poolManagerMaxConnectionIdleTime = 
+                poolManager.GetField ("maxConnectionIdleTime", 
+                BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance);
+
+            Timer poolManagerTimer = (Timer)poolManagerTimerField.GetValue(null);
+            int origMaxConnectionIdleTime = (int) poolManagerMaxConnectionIdleTime.GetValue(null);
+
+
+            try
+            {
+                // Normally, idle connection would expire after 3 minutes and would
+                // be cleaned up by timer that also runs every 3 minutes.
+                // Since we do not want to wait that long during a unit tests,
+                // we use tricks.
+                // - temporarily  reduce max.idle time for connections down to 1
+                // second
+                // - temporarily change cleanup timer to run each second.
+
+                poolManagerMaxConnectionIdleTime.SetValue(null, 1);
+                poolManagerTimer.Change(1000,1000);
+
+                int threadId = -1;
+                using (MySqlConnection c = new MySqlConnection(GetPoolingConnectionString()))
+                {
+                    c.Open();
+                    threadId = c.ServerThread;
+                }
+
+                // Pooled connection should be still alive
+                Assert.IsTrue(IsConnectionAlive(threadId));
+
+                // Let the idle connection expire and let cleanup timer run.
+                Thread.Sleep(2500);
+
+                // The connection that was pooled must be dead now
+                Assert.IsFalse(IsConnectionAlive(threadId));
+
+            }
+            finally
+            {
+                // restore values for connection idle time and timer interval
+                poolManagerMaxConnectionIdleTime.SetValue(null, origMaxConnectionIdleTime);
+                poolManagerTimer.Change(origMaxConnectionIdleTime*1000, 
+                    origMaxConnectionIdleTime*1000);
+            }
+        }
+
+
 		[Test]
 		public void ClearPool()
 		{
@@ -396,5 +464,6 @@ namespace MySql.Data.MySqlClient.Tests
             MySqlConnection.ClearPool(c1);
             MySqlConnection.ClearPool(c2);
         }
+
     }
 }


Attachment: [text/bzr-bundle] bzr/vvaintroub@mysql.com-20091009160421-pqcuryazfnheme8o.bundle
Thread
bzr push into connector-net-trunk branch (vvaintroub:778 to 779)Vladislav Vaintroub9 Oct