From: Date: February 22 2007 9:52pm
Subject: Connector/NET commit: r606 - in branches/1.0: . TestSuite mysqlclient
List-Archive: http://lists.mysql.com/commits/20420
X-Bug: 24373
Message-Id: <200702222052.l1MKqSsc029747@bk-internal.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Modified:
   branches/1.0/CHANGES
   branches/1.0/TestSuite/PoolingTests.cs
   branches/1.0/mysqlclient/MySql.Data.csproj
   branches/1.0/mysqlclient/MySqlPool.cs
   branches/1.0/mysqlclient/Resources.Designer.cs
   branches/1.0/mysqlclient/Resources.resx
Log:
Bug #24373 High CPU utilization when no idle connection 

Fixed this and improved the overall pooling code by using a semaphore to control access to the pooled connections.

Modified: branches/1.0/CHANGES
===================================================================
--- branches/1.0/CHANGES	2007-02-22 20:06:20 UTC (rev 605)
+++ branches/1.0/CHANGES	2007-02-22 20:52:27 UTC (rev 606)
@@ -2,12 +2,12 @@
     Bugs
     ----
     Bug #25605 BINARY and VARBINARY is returned as a string 
+    Bug #24373 High CPU utilization when no idle connection 
     
     Other
     -----
     Added "Use Procedure Bodies" connection string option to allow calling procedures without
        using procedure metadata (if possible).
-    
 
 Version 1.0.9
     

Modified: branches/1.0/TestSuite/PoolingTests.cs
===================================================================
--- branches/1.0/TestSuite/PoolingTests.cs	2007-02-22 20:06:20 UTC (rev 605)
+++ branches/1.0/TestSuite/PoolingTests.cs	2007-02-22 20:52:27 UTC (rev 606)
@@ -224,6 +224,39 @@
 
 
 
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
 
 
 
\ No newline at end of file

Modified: branches/1.0/mysqlclient/MySql.Data.csproj
===================================================================
--- branches/1.0/mysqlclient/MySql.Data.csproj	2007-02-22 20:06:20 UTC (rev 605)
+++ branches/1.0/mysqlclient/MySql.Data.csproj	2007-02-22 20:52:27 UTC (rev 606)
@@ -273,6 +273,11 @@
                     BuildAction = "Compile"
                 />
                 <File
+                    RelPath = "common\Semaphore.cs"
+                    SubType = "Code"
+                    BuildAction = "Compile"
+                />
+                <File
                     RelPath = "common\SharedMemoryStream.cs"
                     SubType = "Code"
                     BuildAction = "Compile"

Modified: branches/1.0/mysqlclient/MySqlPool.cs
===================================================================
--- branches/1.0/mysqlclient/MySqlPool.cs	2007-02-22 20:06:20 UTC (rev 605)
+++ branches/1.0/mysqlclient/MySqlPool.cs	2007-02-22 20:52:27 UTC (rev 606)
@@ -35,6 +35,8 @@
 		private int minSize;
 		private int maxSize;
 		private ProcedureCache procedureCache;
+		private Semaphore poolGate;
+		private Object lockObject;
 
 		public MySqlPool(MySqlConnectionString settings)
 		{
@@ -49,8 +51,12 @@
 				CreateNewPooledConnection();
 
 			procedureCache = new ProcedureCache(settings.ProcedureCacheSize);
+			poolGate = new Semaphore(maxSize, maxSize);
+			lockObject = new Object();
 		}
 
+		#region Properties
+
 		public MySqlConnectionString Settings
 		{
 			get { return settings; }
@@ -71,119 +77,108 @@
 			get { return procedureCache; }
 		}
 
-		/*		private int CheckConnections() 
-				{
-					int freed = 0;
-					lock (inUsePool.SyncRoot) 
-					{
-						for (int i=inUsePool.Count-1; i >= 0; i--) 
-						{
-							Driver d = (inUsePool[i] as Driver);
-							if (! d.Ping()) 
-							{
-								inUsePool.RemoveAt(i);
-								freed++;
-							}
-						}
-					}
-					return freed;
-				}
-		*/
-		private Driver CheckoutConnection()
+		/// <summary>
+		/// It is assumed that this property will only be used from inside an active
+		/// lock.
+		/// </summary>
+		private bool HasIdleConnections
 		{
-			lock (idlePool.SyncRoot)
+			get { return idlePool.Count > 0; }
+		}
+
+		/// <summary>
+		/// It is assumed that this property will only be used from inside an active
+		/// lock.
+		/// </summary>
+		private bool HasRoomForConnections
+		{
+			get
 			{
-				if (idlePool.Count == 0) return null;
-				Driver driver = (Driver)idlePool.Dequeue();
+				if ((inUsePool.Count + idlePool.Count) == maxSize)
+					return false;
+				return true;
+			}
+		}
 
-				// if the user asks us to ping/reset pooled connections
-				// do so now
-				if (settings.ResetPooledConnections)
-				{
-					if (!driver.Ping())
-					{
-						driver.Close();
-						return null;
-					}
-					driver.Reset();
-				}
+		#endregion
 
-				lock (inUsePool.SyncRoot)
+		private Driver CheckoutConnection()
+		{
+			Driver driver = (Driver)idlePool.Dequeue();
+
+			// if the user asks us to ping/reset pooled connections
+			// do so now
+			if (settings.ResetPooledConnections)
+			{
+				if (!driver.Ping())
 				{
-					inUsePool.Add(driver);
+					driver.Close();
+					return null;
 				}
-				return driver;
+				driver.Reset();
 			}
+
+			inUsePool.Add(driver);
+
+			return driver;
 		}
 
+		/// <summary>
+		/// It is assumed that this method is only called from inside an active lock.
+		/// </summary>
 		private Driver GetPooledConnection()
 		{
-			while (true)
-			{
-				if (idlePool.Count > 0)
-					return CheckoutConnection();
-
-				// if idlepool == 0 and inusepool == max, then we can't create a new one
-				if (inUsePool.Count == maxSize)
-					return null;
-
+			// if we don't have an idle connection but we have room for a new
+			// one, then create it here.
+			if (!HasIdleConnections)
 				CreateNewPooledConnection();
-			}
+
+			return CheckoutConnection();
 		}
 
+		/// <summary>
+		/// It is assumed that this method is only called from inside an active lock.
+		/// </summary>
 		private void CreateNewPooledConnection()
 		{
-			lock (idlePool.SyncRoot)
-				lock (inUsePool.SyncRoot)
-				{
-					// first we check if we are allowed to create another
-					if ((inUsePool.Count + idlePool.Count) == maxSize)
-						return;
-
-					Driver driver = Driver.Create(settings);
-
-					idlePool.Enqueue(driver);
-				}
+			Driver driver = Driver.Create(settings);
+			idlePool.Enqueue(driver);
 		}
 
 		public void ReleaseConnection(Driver driver)
 		{
-            lock (idlePool.SyncRoot)
-                lock (inUsePool.SyncRoot)
-                {
-                    inUsePool.Remove(driver);
+			lock (lockObject)
+			{
+				if (!inUsePool.Contains(driver))
+					return;
 
-                    if (driver.IsTooOld && driver.IsOpen)
-                        driver.Close();
+				inUsePool.Remove(driver);
+				if (driver.IsTooOld)
+					driver.Close();
+				else
+					idlePool.Enqueue(driver);
 
-                    if (!NeedConnections) return;
-
-                    if (!driver.IsOpen)
-                        driver.Open();
-                    idlePool.Enqueue(driver);
-                }
+				// we now either have a connection available or have room to make
+				// one so we release one slot in our semaphore
+				poolGate.Release();
+			}
 		}
 
-		public Driver GetConnection()
+		public Driver GetConnection() 
 		{
-			Driver driver = null;
+			int ticks = (int)settings.ConnectionTimeout * 1000;
 
-			int start = Environment.TickCount;
-			int ticks = settings.ConnectionTimeout * 1000;
+			// wait till we are allowed in
+			bool allowed = poolGate.WaitOne(ticks, false);
+			if (! allowed)
+				throw new MySqlException(Resources.TimeoutGettingConnection);
 
-			// wait timeOut seconds at most to get a connection
-			while (driver == null && (Environment.TickCount - start) < ticks)
-				driver = GetPooledConnection();
-
-			// if pool size is at maximum, then we must have reached our timeout so we simply
-			// throw our exception
-			if (driver == null)
-				throw new MySqlException("error connecting: Timeout expired.  The timeout period elapsed " +
-					"prior to obtaining a connection from the pool.  This may have occurred because all " +
-					"pooled connections were in use and max pool size was reached.");
-
-			return driver;
+			// if we get here, then it means that we either have an idle connection
+			// or room to make a new connection
+			lock (lockObject)
+			{
+				return GetPooledConnection();
+			}
 		}
-
 	}
 }

Modified: branches/1.0/mysqlclient/Resources.Designer.cs
===================================================================
--- branches/1.0/mysqlclient/Resources.Designer.cs	2007-02-22 20:06:20 UTC (rev 605)
+++ branches/1.0/mysqlclient/Resources.Designer.cs	2007-02-22 20:52:27 UTC (rev 606)
@@ -404,6 +404,14 @@
             }
         }
 
+		internal static string TimeoutGettingConnection
+		{
+			get 
+			{
+				return ResourceManager.GetString("TimeoutGettingConnection", resourceCulture);
+			}
+		}
+
         internal static string UnableToConnectToHost
         {
             get

Modified: branches/1.0/mysqlclient/Resources.resx
===================================================================
--- branches/1.0/mysqlclient/Resources.resx	2007-02-22 20:06:20 UTC (rev 605)
+++ branches/1.0/mysqlclient/Resources.resx	2007-02-22 20:52:27 UTC (rev 606)
@@ -255,4 +255,7 @@
 	<data name="NoBodiesAndTypeNotSet" d2p1:space="preserve" xmlns:d2p1="http://www.w3.org/XML/1998/namespace">
 		<value>When calling stored procedures and 'Use Procedure Bodies' is false, all parameters must have their type explicitly set.</value>
 	</data>
+	<data name="TimeoutGettingConnection" d2p1:space="preserve" xmlns:d2p1="http://www.w3.org/XML/1998/namespace">
+		<value>error connecting: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.</value>
+	</data>
 </root>
\ No newline at end of file