MySql connection, can I leave it open?

Question

Is it smart to keep the connection open throughout the entire session? I made a C# application that connects to a MySql database, the program both reads and writes to it and the application has to be running about 10 hours a day non-stop.

Are there any risk attached to keeping the connection open instead of calling the close() function every time after you've plucked something from the database and opening it again when you need something new?

Answer 1

Leaving a connection open for a while is fine, as long as:

1. you don't have so many concurrently idle connections that you hit the MySQL connection limit;

2. you don't leave it open for hours without doing anything. The default MySQL connection wait_timeout is 8 hours; leave a connection inactive for that long and when you next come to use it you'll get a “MySQL server has gone away” error.

Answer 2

Since you're using ADO.NET, you can use ADO.NET's inbuilt connection pooling capabilities. Actually, let me refine that: you must always use ADO.NET's inbuilt connection pooling capabilities. By doing so you will get the .NET runtime to transparently manage your connections for you in the background. It will keep the connections open for a while even if you closed them and reuse them if you open a new connection. This is really fast stuff.

Make sure to mention in your connection string that you want pooled connections as it might not be the default behaviour.

You only need to create connections locally when you need them, since they're pooled in the backrgound so there's no overhead in creating a new connection:

using (var connection = SomeMethodThatCreatesAConnectionObject())
{
    // do your stuff here
    connection.Close(); // this is not necessary as
                        // Dispose() closes it anyway
                        // but still nice to do.
}

That's how you're supposed to do it in .NET.

Answer 3

Yes you can, provided:

• You will reconnect if you lose the connection
• You can reset the connection state if something strange happens
• You will detect if the connection "goes quiet", for example if a firewall timeout occurs

Basically it requires a good deal of attention to failure cases and correct recovery; connecting and disconnecting often is a lot easier.

Answer 4

I think, if there is a connection pooling mechanism, you'd better close the connection.

One reason for it is that you do not need to re-check if your connection is still alive or not.