Why is MySQL in Node.js so slow?

Question

My Node.js Code is like below

CODE1: below

var http=require('http');
var MySQL = require('mysql');

mysql = MySQL.createConnection(...)

http.createServer(function(req, res){
    // the query will take several seconds
    mysql.query("SELECT SLEEP(1)", function....)
});
http.listen(...);

The problem is the server will be crash when I refresh the page too fast. I think is the node-mysql module's problem, it process the query in a queue.So I try to create a connection pool.

CODE2: below

....
var pool = require('generic-pool');
var mp   = pool.Pool({
    ...
    create: function(cb){
        client = MySQL.createConnection(...);
        cb(null, client)
    },
    max: 10, // up to 10 connection
    min: 2,
    ...
});
....
    mp.acquire(function(err, mysql){

        // the query will take several seconds
        mysql.query("SELECT SLEEP(1)", function....)
        mp.release(mysql);
    });
....

But the problem still here, Why? How can I fix this.

EDIT: I launch 100 requests with 100 concurrency, 10 seconds expected. But it take 20 seconds. Why? Is The pool only support up to 5 connection?

Answer 1

Connection pools are a good solution to handle multiple concurrent requests. But instead of using 'Generic resource pool', why can't we use a mysql-specific pool?

This link talks about, 'node-mysql-pool' which is a MySQL connection pool for node.js

Answer 2

Disclaimer: I wrote the module to solve this kind of problem.

npm install mysql-simple-pool

Now you can configure your connection pool. I use maximum 100 connections.

var Pool = require('mysql-simple-pool');
var pool = new Pool(100, {
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'test'
});

Now you can write a test function that will put this to the test.

function test() {
    var counter = 0;
    var start = new Date().getTime();
    for (var xa = 0; xa < 10; xa++) {
        pool.query('SELECT SLEEP(1)', function(err, results) {
            counter++;
            if (counter == 10) {
                var end = new Date().getTime();
                console.log('Time spend is ' + (end - start) + 'ms');
                test();
            }
        });
    }
}
test();

And this is the output...

Time spend is 1044ms
Time spend is 1006ms
Time spend is 1005ms
Time spend is 1006ms
Time spend is 1007ms
Time spend is 1005ms
Time spend is 1005ms
Time spend is 1004ms
Time spend is 1005ms
Time spend is 1005ms

The first time around it spends some time establishing the connections. Hope this helps~