Socket.io & Mysql: how many connections?

In an app with socket.io and node-mysql module, what's the best way (most optimized and well designed) to use mysql connections when an event is triggered?

  1. create one connection and pass it to the callback
  2. create a connection every time the callback is called

Let's write some code to better explain

Case 1

var connection = mysql.createConnection({
    host     : 'example.org',
    user     : 'bob',
    password : 'secret',
});

io.sockets.on('connection', function cb1(){
    socket.on('event', function cb2(){
        connection.connect(function cb3(){
            connection.query();
        });
    });
});

Case 2

io.sockets.on('connection', function cb1(){
    socket.on('event', function cb2(){
        var connection = mysql.createConnection({
            host     : 'example.org',
            user     : 'bob',
            password : 'secret',
        });
        connection.connect(function cb3(){
            connection.query();
        });
    });
});

My server listen for several socket events and I'm experiencing a lot of timeout errors! I think it could be related to the use of the connections.

Any suggestions?

I think the best would be to have a connection opened all the time and run just query inside socket.io event. Like this:

var connection = mysql.createConnection({
host     : 'example.org',
user     : 'bob',
password : 'secret',
});
connection.connect(function cb3(){
    io.sockets.on('connection', function cb1(){
    socket.on('event', function cb2(){
            connection.query();
        });
    });
});

I would look at connection pooling, which is implemented by node-mysql. This has the advantage of being able to use multiple connections to your database, while keeping the total number of connections limited to prevent flooding the database server.

Another possibility could be node-mysql-queues, which can be used to prevent possible issues with multiple queries run through a single connection.