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

If the application is using the connection there is no reason to close it. If you don't need the connection you should close it. If you were to have multiple applications connect to the database, you have a fixed number of connections to that database. That's why it's better to close when you are done and reopen when you need it.

Answer 2

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 3

From a security point of view, I'd say its better to close it after a query, just to be sure that no other program can inject it's own things into the opened connection.

As performance is conered, it is clearly better to have the connection opened through the whole time.

Your choice^^

Answer 4

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 5

No, I don't see any reason why not to leave a connection open and re-use it: after all, this is the whole point behind the various connection-pool technologies that are about (although these are generally reserved for multi-threaded situations where works are all operating on the same data source).

But, to expand on the answer by bobince, - just beacause you are not closing the connection, don't assume that something else won't: the connection could timeout, there could be connection issues or a hundred and one other reasons why your connection dies. You need to assume that the connection may not be there, and add logic to code for this exception-case.

Answer 6

It is not good practise in my opinion to keep the connections open. Another aspect that speaks for closing connections every time is scaleability. It might be fine now to leave it open but what if you app is used by twice 3-times the amount of users. It's a pain in the neck to go back and change all the code. (i know i've done it :-)

Answer 7

Hi,

Your problem will be solved if you use connection pooling in your code. You don't need to open and close connection so you save precious resources which are used while opening a connection. You just return the connection to a pool which when requested for a connection returns back a idle connection.

Of course I am of the opinion, get an instance of the connection, use it, commit/rollback your work and return it to the pool. I would not suggest keeping the connection open for so long.

Answer 8

One thing I didn't see in the other answers, yet: In case you have prepared statements or temporary tables they might block server resources till the connection is closed. But on the other hand it can be useful to keep the connection around for some time instead of recreating them every few moments.

Answer 9

You'll pay a performance penalty if you're constantly opening and closing connections. It might be wise to use connection pooling and a short wait_timeout if you are concerned that too many running copies of your app will eat up too many database connections.

Answer 10

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 11

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.