mysql connections. Should I keep it alive or start a new connection before each transaction?

Question

I'm doing my first foray with mysql and I have a doubt about how to handle the connection(s) my applications has.

What I am doing now is opening a connection and keeping it alive until I terminate my program. I do a mysql_ping() every now and then and the connection is started with MYSQL_OPT_RECONNECT.

The other option (I can think of), would be to start a new connection before doing anything that requires my connection to the database and closing it after I'm done with it.

What are the pros and cons of these two approaches? what are the "side effects" of a long connection? What is the most used method of handling this?

Cheers ;)

---

Some extra details

At this point I am keeping the connection alive and I ping it every now and again to now it's status and reconnect if needed.

In spite of this, when there is some consistent concurrency with queries happening in quick succession, I get a "Server has gone away" message and after a while the connection is re-established.

I'm left wondering if this is a side effect of a prolonged connection or if this is just a case of bad mysql server configuration.

Any ideas?

Answer 1

In general there is quite some amount of overhead incurred when opening a connection. Depending on how often you expect this to happen it might be ok, but if you are writing any kind of application that executes more than just a very few commands per program run, I would recommend a connection pool (for server type apps) or at least a single or very few connections from your standalone app to be kept open for some time and reused for multiple transactions.

That way you have better control over how many connections get opened at the application level, even before the database server gets involved. This is a service an application server offers you, but it can also be rolled up rather easily if you want to keep it smaller.

Apart from performance reasons a pool is also a good idea to be prepared for peaks in demand. When a lot of requests come in and each of them tries to open a separate connection to the database - or as you suggested even more (per transaction) - you are quickly going to run out of resources. Keep in mind that every connection consumes memory inside MySQL!

Also you want to make sure to use a non-root user to connect, because if you don't (I think it is tied to the MySQL SUPER privilege), you might find yourself locked out. MySQL reserves at least one connection for an administrator for problem fixing, but if your app connects with that privilege, all connections would already be used up when you try to put out the fire manually.

Answer 2

Unless you are worried about having too many connections open (i.e. over 1,000), you she leave the connection open. There is overhead in connecting/reconnecting that will only slow things down. If you know you are going to need the connection to stay open for a while, run this query instead of pinging periodically:

SET SESSION wait_timeout=#

Where # is the number of seconds to leave an idle connection open.