Home > benchmarking, mysql, node.js > Node.js: improve mysql query performance with mysql-pool and cluster

Node.js: improve mysql query performance with mysql-pool and cluster

Prior testing:

npm install mysql -g
npm install mysql-pool -g
npm install express -g
npm install cluster -g

Code that archives best result:

require.paths.push('/usr/local/lib/node_modules');
var express=require('express');
var cluster=require('cluster');
var MySQLPool = require("mysql-pool").MySQLPool;
var pool = new MySQLPool({
  poolSize: 10,
  user:     'node',
  password: 'node',
  database: 'testdb'
});

function symsearch(q, res) {
	var query=pool.query(
		   "select symbol,company_name from symbols where symbol like ? limit 10",
			['%'+q+'%'],
			function selectcb(err, results, fields) {
				if(err) {throw err;}
				console.log("Searching Result for "+q);
				res.send( results );
			}
	);

}

var app=express.createServer();
app.get('/sym/:q', function( req, res) {
	var q=req.params.q || '';
	if(q.length>0) symsearch(q, res);
});

cluster(app).listen(3000);

Benchmark command:

ab -n 10000 -c 250 http://localhost:3000/sym/<pattern>

Results:
With the js code above, I am able to achieve 1300/sec on a dual-core 2.9GHz Virtualbox vm (ubuntu 10.10 32bit). When the same test is conducted on Apache2 + php5, I am getting about 800/sec. If I opt-for the single connection mode (use ‘mysql’ instead of ‘mysql-pool’), the result is only a little better than apache + php combo. The use of node module cluster is to take advantage of the dual-core cpu (node would run with in single-core mode due to its single-threaded nature).

Advertisements
Categories: benchmarking, mysql, node.js
  1. superpeace
    August 26, 2012 at 8:29 pm

    you are great! thanks~ helpful!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: