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?

You're going to need to post more than pseudo-code to get help with this. — JohnnyHK, Nov 26 '12 at 14:45
How long does the query take when you run it at the command line, instead of via node? — S M, Nov 28 '12 at 02:33
Thank you all for your help. 20s taken, I run a query with 100 concurrent request. I except it will take 10s. But actually it take 20s. I don't know why? I already set max connections to 10, It will have 10 mysql connection standby, the QPS must be 10. Can anyone tell me why? — Garbin, Nov 30 '12 at 13:33
how do you create this 100 requests? If its 100 browser tabs, then each fires 2 requests ( url + favicon ) — Andrey Sidorov, Dec 24 '12 at 08:29
can you post crash stack trace? I don't see why it can crash here — Andrey Sidorov, Dec 24 '12 at 08:42

score 1 · Answer 1 · answered Dec 24 '12 at 05:11

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

answered Dec 24 '12 at 05:11

Jirilmon

1,924
1
12
13

Might be better off using `node-mysql`'s `createPool({})` function to create a pool of connections – Dominic Farolino Feb 16 '16 at 04:26

score 1 · Answer 2 · answered Jan 07 '13 at 22:16

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~

Why is MySQL in Node.js so slow?

2 Answers2