Constant MySQL Connection or Connect when needed.

Question

I am building a little daemon which periodically (every 30 seconds) checks for new data and enters it in a local MySQL Database.

I was just wondering whether it was better to create a connection to the database when the application launches and always use that connection throughout the application until it is closed, or if it should only open a connection when there is new data, close it after the data has been added and then repeat this when there is new data 30 seconds later?

Thank you.

Answer 1

I would recommend that you do whatever you find easiest to code. Don't waste any time trying to solve what will most likely be a non-problem.

If it turns out there is any difficulty with contention, connection limits or other such things you can fix it later.

Answer 2

That depends.

In your case the performance won't matter, since you won't be performing thousands of queries/logins per second and the new connection/login overhead is in (tens of) milliseconds.

If you use a single connection, you have to make sure your daemon handles sudden disconnections from the MySQL side and is able to recover from there. Also if you ever move your application so your application would be on a different server than the MySQL, then many firewalls can drop prolonged connections every now and then.

If you create a new connection every time and then disconnect when finished, things like firewalls cleaning up old connections won't bite you so easily.