Mysql: [NEWBIE QUESTION] How to use single connection over cluster workers?

[overflowz]

I'm trying to build app, where there will be one single pool connection which will be shared across the workers, but I have no idea how to.

var cluster = require('cluster');
var mysql = require('mysql');

var db = mysql.createPool({ ... }); // this is executed for every worker, which isn't what I want.
if(cluster.isMaster) {
  for(var i = 0; i < 2; i++) {
    cluster.fork();
  }
} else {
  db.pool(); // random db operations
}

How should I achieve one single connection, which will be shared over all other workers?

NOTES: also asked here: http://stackoverflow.com/questions/39215741/single-db-connection-in-node-cluster

Regards.

[sidorares]

I don't think it's easy out of the box. You would have to get file descriptor of the socket, send it to worker, create mysql client in the worker without actually connecting, create socket using FD you passed, mark client state as "already authenticated". Oh, and much worse: your workers must agree that only one socket at a time is sending query. Not worth doing at all

What might work is that you only have connection in master, listen for messages from workers, make queries on their behalf and send results back to workers. Use built-in node messaging or some kind of message queue (there are many on npm)

[overflowz]

Actually that's a great idea though, thanks!

[overflowz]

Let's say, what if I have multiple connections from each worker, by performance, which will be faster one? Assuming it'll do huge load of queries.

[dougwilson]

If you end up passing everything back and forth between worker and master, it will be much slower; each worker being able to directly write to the socket will be the fastest.

[overflowz]

And having multiple connections from each worker isn't considered as a bad practice?

[dougwilson]

I've never had issues with it, as long as the workload is evenly distributed across your workers, enough traffic will end up with an even use of the connections, even if each worker has it's own pool.

[overflowz]

Thank you for the tips!

[sidorares]

I had issued on a big machines where having a pool on each worker (36 of them) can quickly make mysql server hit max_connection limit. And mysql is quite dumb about new connections - it's full new OS thread (at least one) per connection, consuming 2mb memory or more, even when it's idle.

[dougwilson]

Ah, 36 workers :)? By that scale I have never not been using slave replica servers, which distribute the MySQL load. Writing to a master I usually do with only 1-2 connections per worker, but reading from slaves usually 20 per worker. It has served me well so far, lol.

[dougwilson]

The main thing is a lot of this highly depends on your setup, so there isn't a good one-size-fits-all answer. Certainly if you are constrained to a single MySQL server and need to scale out Node.js a load, the overhead of message passing may be an acceptable trade off :)

[sidorares]

another solution might be to put some kind of smart mysql proxy that would be able to handle lot's of incoming connections but make just a few to a real database and somehow multiplex them