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).
Categories: benchmarking, mysql, node.js
you are great! thanks~ helpful!