 | Too Many MySQL Connections Open MySqlAdministrator, click on the Server Connections link, and view the active Threads. Web-based ASP .NET applications seem to incorrectly handle connection pooling, and thus leave too many connections in a Sleep state while the Time elapsed continues increasing. These connections would normally be reused by the .NET database driver, and occassionally they are. However, sometimes new connections are opened and existing sleeping connections are forgotten. They will either time out by the server's default connection timeout or simply remain until you pass the connection limit and your application fails. |
Connection Pooling in MySQL with .NET All .NET database drivers implement connection pooling in MySQL. This includes ODBC, ADO, MySQLConnector, and MySQLDriver. However, there appears to be a flaw in how pooling is handled. Normally, a connection is opened when you call MyConn.Open(). You will see the connection active in MySQLAdministrator and a query is executed. The connection is then placed in the Sleep state upon calling MyConn.Close() (rather than actually being closed), awaiting another query from the ASP .NET application. When a new request arrives with the MyConn.Open(), the existing MySQL connection is used, without having to open a new connection. This increases database performance and speed. The problem in web-based .NET applications is that the driver appears to lose pointers to existing database connections and fails to close them properly. Setting a flag in the database connection string to disable MySQL connection pooling does not appear to work in a web-based ASP .NET application. ie. MySQL ODBC Connection String Driver={MySQL ODBC 3.51 Driver};Server=yourserver.com;Database=yourdatabase;User=databaseuser;Password=databasepassword;Pooling=false; Taking Matters into Your Own Hand Since .NET won't properly close connections that have timed out nor handle the pooled connections properly, we need to implement code that will manage the sleeping connections ourselves. This is required to prevent ASP .NET MySQL applications from failing with the "Too Many Connections" error. There are two possible solutions: 1. Killing the connection after closing it - the idea is that each time you open and close a connection, you add a block of code to physically kill the MySQL process ID. One drawback of this is that you, in effect, remove the advantage of MySQL connection pooling. This may also add an additional burden on the MySQL database of killing the connections each and every time you open one. MyConn.Open();
...
MyConn.Close();
MyConn.Kill(); // This routine would be coded by you 2. A better solution is to write a connection management routine that occasionally scans for sleeping connections which have surpassed a realistic timeout value, and kill them ourselves. The good news is that MySQL offers the "show processlist" and "kill process_id" command, which allows us to take control of the problem in ASP .NET. For example:
show processlist
kill 9012345 Killing Sleeping Connections in C# ASP .NET The code routine below searches all MySQL connections under your username and kills all sleeping connections which have elapsed a specific number of seconds. For example, all connections in a Sleep state that have a Time value greater than 100 seconds should be closed, and are thus in a good position to be killed. The code example below assumes you are using ODBC to connect to MySQL, but can be easily changed to your desired database driver with a search and replace. using System.Data.Odbc;
using System.Data; /// <summary>
/// This function checks for any sleeping connections beyond a reasonable time and kills them.
/// Since .NET appears to have a bug with how pooling MySQL connections are handled and leaves
/// too many sleeping connections without closing them, we will kill them here.
/// </summary>
/// iMinSecondsToExpire - all connections sleeping more than this amount in seconds will be killed.
/// <returns>integer - number of connections killed</returns>
static public int KillSleepingConnections(int iMinSecondsToExpire)
{
string strSQL = "show processlist";
System.Collections.ArrayList m_ProcessesToKill = new ArrayList(); OdbcConnection myConn = new OdbcConnection(Global.strDBServer);
OdbcCommand myCmd = new OdbcCommand(strSQL, myConn);
OdbcDataReader MyReader = null; try
{
myConn.Open(); // Get a list of processes to kill.
MyReader = myCmd.ExecuteReader();
while (MyReader.Read())
{
// Find all processes sleeping with a timeout value higher than our threshold.
int iPID = Convert.ToInt32(MyReader["Id"].ToString());
string strState = MyReader["Command"].ToString();
int iTime = Convert.ToInt32(MyReader["Time"].ToString()); if (strState == "Sleep" && iTime >= iMinSecondsToExpire && iPID > 0)
{
// This connection is sitting around doing nothing. Kill it.
m_ProcessesToKill.Add(iPID);
}
} MyReader.Close();
foreach (int aPID in m_ProcessesToKill)
{
strSQL = "kill " + aPID;
myCmd.CommandText = strSQL;
myCmd.ExecuteNonQuery();
}
}
catch (Exception excep)
{
}
finally
{
if (MyReader != null && !MyReader.IsClosed)
{
MyReader.Close();
} if (myConn != null && myConn.State == ConnectionState.Open)
{
myConn.Close();
}
} return m_ProcessesToKill.Count;
}
// Call it with the following code to kill sleeping connections >= 100 seconds.
KillSleepingConnections(100);  | The above code first calls a "show processlist", which tells MySQL to return a recordset containing all active connections. These connections can be in a variety of states such as: Opening, Closing, Executing Query, Sleep, etc. The function specifically looks for connections in the Sleep state. It then checks the Time value returned by the query to see how long the connection has been sleeping. The process ID (thread id) of connections which exceed the timeout value are recorded in an array list. We then close the process list connection and now loop through the array list of connections that need to be killed. We issue a "kill processid" command which effectively closes and deletes the connection from the MySQL database. |
|