Clearing connection pool

[lsy]

i'm running a multi-threading program... each time there might have 200 (each thread 1 )connection connect to the MySQL db. After few conection is created an exception occur...
"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".
i'm using mysqlclient connect to the db... is there anywhere to clear the connection pool?

[boudino]

You don't need to clear it, but return connections to it if they are no more needed. And surely, set the max pool size to 200. (Although I think it is not good idea to have 200 open connections simultaneously. Are you sure about your design?)

[lsy]

You don't need to clear it, but return connections to it if they are no more needed. And surely, set the max pool size to 200. (Although I think it is not good idea to have 200 open connections simultaneously. Are you sure about your design?)

after i increase the poolsize encounter another error...
"Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug"

[boudino]

The OS has limits on resources. Creating 200 threads consumes too many resources, so OS denied it. As I've said: reconsider your desing.

[Arjay]

Lsy, post your code that uses the mySql database. You need to make sure you are closing your connections when you are finished with the connection.

Also, check out ThreadPool.QueueUserWorkItem. 200 threads for a process is excessive (and unless you are on an 8 proc machine or better, you are not likely to see a speed improvement because of the amount of context switching that will occur between all the threads). Leveraging the QueueUserWorkItem will allow the OS to choose the number of threads to utilize.

[lsy]

try
            {
                using (myConn = new MySqlConnection(GV.m_gConStr))
                {
                    myConn.Open();
                    sSqlStmt = "SELECT * FROM TABLE";                    
                    myCmd = new MySqlCommand(sSqlStmt, myConn);
                
                    while (bRunning)
                    {
                        myDReader = myCmd.ExecuteReader();
                       	while (myDReader.Read())
                        {
                            //LOOP MY RECORD
                        }         
                       
                        myDReader.Close();
                        myDReader = null;                        
                    }                   
                }
            }
            catch (Exception x)
            {
                string ex = string.Format("An error occurred while Process with main program. ");
                ex += string.Format("\nFor SQL [{0}]", x);
                throw new Exception(ex, x);
                Console.WriteLine(ex);   
                
            }
                
            finally
            {       
                  myConn.Close();
                  myConn.Dispose();
            }

[Arjay]

The code highlighted in red below looks problematic.

try
            {
                using (myConn = new MySqlConnection(GV.m_gConStr))
                {
                    myConn.Open();
                    sSqlStmt = "SELECT * FROM TABLE";                    
                    myCmd = new MySqlCommand(sSqlStmt, myConn);
                
                    while (bRunning)
                    {
                        myDReader = myCmd.ExecuteReader();
                       	while (myDReader.Read())
                        {
                            //LOOP MY RECORD
                        }         
                       
                        myDReader.Close();
                        myDReader = null;                        
                    }                    
                }
            }
            catch (Exception x)
            {
                string ex = string.Format("An error occurred while Process with main program. ");
                ex += string.Format("\nFor SQL [{0}]", x);
                throw new Exception(ex, x);
                Console.WriteLine(ex);   
                
            }
                
            finally
            {       
                  myConn.Close();
                  myConn.Dispose();
            }

Also, potentially the code above can leak resources because the myDRReader object may not get closed if an exception occurs.

I tried to look up the MySql commands to see if they implemented IDisposable but I didn't have any luck. You can find out by right clicking on the MySql objects and choose 'go to declaration'. If they do implement IDisposable you can use the using block to help free the resources.

try
{
  using (MySqlConnection myConn = new MySqlConnection(GV.m_gConStr))
  {
    myConn.Open();
    sSqlStmt = "SELECT * FROM TABLE";                    
    
    using( MySqlCommand myCmd = new MySqlCommand(sSqlStmt, myConn) )
    {
       using( MySqlDataReader myDReader = myCmd.ExecuteReader() )
       {
         while (myDReader.Read())
         {
            //LOOP MY RECORD
         }         
       }
    }
  }
}
catch (Exception x)
{
  // Error code                
}

[lsy]

i need the bRunning to keep the thread running.

[Arjay]

i need the bRunning to keep the thread running.

You have a problem with your design.

[lsy]

You have a problem with your design.

Can you please help to highlight?

[Arjay]

Can you please help to highlight?

What you need to do is explain what you are trying to accomplish.

I hear you say you want to keep the thread running, but the following code will only cause the CPU to spike while it's spinning in this loop.

Furthermore, what is the point to this code? Will the myCmd.ExecuteReader() return anything different each time through the loop. From what I see it won't.

while (bRunning)
{
  myDReader = myCmd.ExecuteReader();
  while (myDReader.Read())
  {
    //LOOP MY RECORD
  }         
                       
  myDReader.Close();
  myDReader = null;                        
}

[lsy]

What you need to do is explain what you are trying to accomplish.

I hear you say you want to keep the thread running, but the following code will only cause the CPU to spike while it's spinning in this loop.

Furthermore, what is the point to this code? Will the myCmd.ExecuteReader() return anything different each time through the loop. From what I see it won't.

while (bRunning)
{
  myDReader = myCmd.ExecuteReader();
  while (myDReader.Read())
  {
    //LOOP MY RECORD
  }         
                       
  myDReader.Close();
  myDReader = null;                        
}

i would like to keep query data from db in every few seconds... every few seconds the data inside the db will be change, that why in every loop i need to myCmd.ExecuteReader(); for reexecuting the reader and get new set of record...
the looping inside bRunning is keep my program always running until someone close it.

[foamy]

i would like to keep query data from db in every few seconds... every few seconds the data inside the db will be change, that why in every loop i need to myCmd.ExecuteReader(); for reexecuting the reader and get new set of record...
the looping inside bRunning is keep my program always running until someone close it.

If I were you, I'd create a Timer and execute the query-method every 5 seconds or so... The code you have now, puts far too much pressure on the CPU and the database...

[Arjay]

As mentioned, your code will slam the CPU because it won't execute every few seconds it will execute as fast as it can. Furthermore, it will error if the connection to the database goes away.

If you want to run it every few seconds, then use the timer approach as suggested or you can use WaitForSingle/MultipleObject with a 5 second timeout.

But polling the database this frequently is generally never a good idea.

Investigate if mySql has the ability for you to register for a table change event. If so, you can get notified when data has changed in one or more tables and then can act accordingly (without polling).

[TheCPUWizard]

Did you know that they developed Triggers for a reason (other than just something to keep them busy....) ???

http://dev.mysql.com/doc/refman/5.0/...e-trigger.html