Menu
I'm working on a node.js application that connects to a MySQL server. The following likely isn't node.js-specific, though.
Currently, my code initializes a MySQL connection at the application start-up, and then it uses that connection every time it needs to make a query.
The issue I'm facing with my approach is that the connection tends to close after a period of time. I'm not sure how long that period of time is, but it seems to be at least several hours. I'm also not sure whether it's caused by inactivity.
In any case, I'm wondering what would be a better approach for managing MySQL connections long-term. Of possible approaches, I've considered:
Simply checking before each query to see whether the connection is still valid. If not, reconnect before executing the query.
Pooling MySQL connections. Would this be overkill for a fairly small application?
Periodically (every hour or so), execute a query, in case this is occurring due to inactivity. However, this doesn't remedy the situation in possible cases not caused by inactivity.
Connect and disconnect before/after queries. Bad idea because of the overhead involved.
I'm leaning toward using one of the first two options, as you might imagine. Which of the options would be most reliable and efficient?
970
To prevent these connections being automatically closed, the connector can be configured to keep the connection alive by submitting a simple SELECT statement (actually SELECT 'KEEP_ALIVE';) periodically to ensure that the MySQL timeout is not reached and the connection closed.
Absolutely it is safe to do this. This is how client-server applications work. If you are using a three-tier application, the application server will keep a pool of connections open anyway.
Do I need to close the database every time a request? will it consume more resources with current code?or flask will auto disconnect? If you're accessing MySQL directly, like you do in your example code above, then yes, you'll need to disconnect explicitly.
MySQL has its wait_timeout variable default value set to 28800 seconds (8 hours).
Consider the database as your data treasury. Hence, it is in your interest to keep traffic through the open door to a minimum. Therefore I suggest you to open the DB connection as late as possible, ideally just right before the query execution, and closing it right after it again.
The best practice for Node.js seems to be to use a connection pool. See Node.js MySQL Needing Persistent Connection.
The default timeout for idle connections is 28800 seconds, and is configurable with the wait_timeout variable. See http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
