Why is MySQL in Node.js so slow?

Question

My Node.js code looks 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 a server crash when I refresh the page too fast. I think this is a node-mysql module problem, it processes the request in the queue. Therefore, I am trying 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 is still here, why? How can I fix this.

EDIT: I run 100 requests with 100 concurrency expected 10 seconds. But it will take 20 seconds. What for? Does the pool support only up to 5 connections?

Answer 1

Connection pools are a good solution for handling multiple simultaneous requests. But instead of using the "Shared Resource Pool", why can't we use the mysql specific pool?

This one talks about node-mysql-pool ", which is the MySQL connection pool for node.js

Answer 2

Disclaimer: I wrote a module to solve this problem.

 npm install mysql-simple-pool 

Now you can configure the connection pool. I use a maximum of 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 in 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 result ...

 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 

For the first time, some time is spent around him establishing connections. Hope this helps ~