Handling mysql db connection

Question

I am working on a high-performance real-time app, with node.js and mysql as backend.

In order to increase performance, I have a separate node.js process updating underlaying mysql bd. Update requests are enqueued to garantee sequencial execution (a must).

I am thinking about keeping a permanently open db connection in this process in order not to lose time on openning it on each request.

Other DB-requests (updates or reads) are served from the web-server node-js instance directly, possibly in parallel. These db-connections are of course created/freed in each request.

Do you see some cons of this approach?

UPDATE:

Important additional information. I've chosen this stand-alone process solution basically because of the following reason...

the logic that must be executed before each update is relatively complex and depends on data-structure in the database. Several additional queries would be necessary before each update. This stand-alone process has the complete data-structure in-memory and can perform these check-ups very fast and with no db access (performance boost).

Answer 1

Another con to your approach.

MySQL is notorious for closing connections that have been open for a long time, based on timeouts.

Lost connection to MySQL server during query

@duffymo is correct: using short-lived connections from a pool is much more likely to keep working for hundreds of hours than a long-opened connection.

node.js + mysql connection pooling

I wonder: you say that sequential execution is a must. Large scale DBMSs (including MySQL on a large server) are very competent at handling concurrent queries from multiple connections without corrupting data. Your system will probably be more robust if you can work out exactly what is mandatory about the sequencing of your updates. If you can implement that sequencing in the SQL itself, or possibly in some transactions, you'll have a more failure-resistant system than if you insist that only one process do the updates. Single-purpose processes like the one you mention aren't easy to debug in system test: they are notorious for failing after hundreds of hours for all kinds of reasons. When they fail in production, everybody's scrambling to restore them so nobody has time to troubleshoot them.

Answer 2

I see cons.

Transactions are the biggest one. What happens if an UPDATE fails? Do you rollback and try again? Put the message back on the queue?

I think a better approach would be to open, use, and close connections in the narrowest scope possible. Create and maintain a pool of connections to amortize the cost of creation over many transactions.

I'd recommend looking into vert.x instead of node.js. It's node.js for the JVM. It uses non-blocking I/O and a ring-buffer event bus to guarantee sequential, fast operations.