Multithreaded app for writing to a MySQL database

Question

I have a multi-threaded app (WCF) that writes to a MySQL db. I want to use MySQL's built in connection pooling abilities.

As far as I can tell, to do that, I should set MySqlConnectionStringBuilder.MinimumPoolSize to some value approximately equal to the number of threads I connect. Then Open/Close the connection for each call to the db. Is this correct? If not, what is the proper way to use pooling?

Here is the function I use to send data to the db. It gets called by many threads throughout the day. Hundreds, maybe thousands of times per day.

private static void execute(MySqlCommand cmd)
{
 try
 {
 MySqlConnectionStringBuilder cnx = new MySqlConnectionStringBuilder();
 cnx.Server = MySqlServer;
 cnx.Database = Database;
 cnx.UserID = UserId;
 cnx.Password = Pass;
 cnx.MinimumPoolSize = 100;
 cmd.Connection = new MySqlConnection(cnx.ToString());
 cmd.Connection.Open();
 cmd.ExecuteNonQuery();
 }
 catch (MySqlException e)
 {
 System.Console.WriteLine(e.Message);
 }
 finally
 {
 if (null != cmd.Connection)
 {
 cmd.Connection.Close();
 }
 }
}

Answer 1

Yes, your code will use pooling.

You might, for the sake of server efficiency, use a smaller MinimumPoolSize (say, 20), and a larger MaximumPoolSize (something a little larger than your maximum number of threads).

You probably don't need all those connections all the time. If your threads do anything significant between their uses of the function you've shown, a significant number of your connections will be idle.

Unless you're sure you need all those connections all the time, you should reduce the RAM and thread burden on your server with a smaller MinimumPoolSize.