Bug #24373 High CPU utilization when no idle connection
Submitted: 16 Nov 2006 21:35 Modified: 27 Feb 2007 11:19
Reporter: Karl Seguin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version:5.0.x OS:Microsoft Windows (Win32)
Assigned to: CPU Architecture:Any
Tags: .net, pooling, timeout

[16 Nov 2006 21:35] Karl Seguin
Description:
MySqlPool.GetConnection runs a very tight loop until there's either a timeout or a connection is retrieved from the pool.

Specifically:
while (driver == null && (Environment.TickCount - startTime) < timeOut)
{
   driver = GetPooledConnection();
}

private Driver GetPooledConnection()
{
   while (idlePool.Count <= 0)
   {
      if (inUsePool.Count == maxSize)
      {
         return null;
      }
      CreateNewPooledConnection();
   }
   return CheckoutConnection();
}

 
If there are no idle connections in idlePool and we are using the maximum amount of connections, we'll just spin our wheels in the loop.

This can have a pretty harsh performance impact on a system which is pushing up against it's connection limit.

How to repeat:
This code will reproduce the problem each time. Using a profiler, perfmon or just task-manager, we can see the impact on performance this code will have.  I used snippet compiler, so WL and RL just map to Console.WriteLine and Console.ReadLine .

private const string _connectionString = "XXXXXX;Maximum Pool Size=1";
public static void Main()
{
   MySqlConnection connection1 = null;      
   MySqlConnection connection2 = null;   
   try
   {
      connection1 = GetConnection();
      connection2 = GetConnection();
   }
   catch(Exception ex)
   {
      WL(ex);
   }
   finally
   {
      if (connection1 != null)
      {
         connection1.Dispose();
      }
      if (connection2 != null)
      {
         connection2.Dispose();
      }
   }
     
   WL("Done");
   RL();
}

private static MySqlConnection GetConnection()
{
   MySqlConnection connection = new MySqlConnection(_connectionString);
   connection.Open();
   return connection;
}

Suggested fix:
I don't know what people think about sleeping inside the while loop...

Ideally it'd be nice to see this block and wait for a signal that a connection has become available.

From a performance standpoint, it'd be nice if CreateNewPooledConnection() actually Checkedout and returned the connection directly. As is, 1 thread can keep creating new pool connections while other threads sneak in and steal them.
[16 Nov 2006 21:45] Karl Seguin
Sorry, the last point about "CreateNewPooledConnection" was just an unrelated remark...
[22 Nov 2006 12:21] Tonci Grgin
Karl, thanks for your excellent problem report.
Verified as described by reporter with latest sources. Until timeout expires, CPU is locked at 100%:

Connecting to Mysql DB
error connecting: Timeout expired.  The timeout period elapsed prior to obtainin
g a connection from the pool.  This may have occurred because all pooled connect
ions were in use and max pool size was reached.
[22 Nov 2006 12:22] Tonci Grgin
Test case

Attachment: Program.cs (text/plain), 1.50 KiB.

[22 Feb 2007 20:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20413
[22 Feb 2007 20:07] Reggie Burnett
Fixed in 1.0.10 and 5.0.4
[22 Feb 2007 20:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20420
[27 Feb 2007 11:19] MC Brown
A note has been added to the 5.0.4 and 1.0.10 changelogs.