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).

Using (and booting from) SATA III drive in Mac OSX Lion

I wondered if I could switch the SATA port from the blue (SATA 3.0Gb/s) to white(SATA 6.0Gb/s) port since my hackintosh has been upgraded to LION so today I did just that and bang I’m on SATA III without any issue. But the weird part is I don’t feel any speed difference. With SATA III

time dd if=/dev/zero bs=1024k of=tstfile count=1024
1024+0 records in
1024+0 records out
1073741824 bytes transferred in 8.016426 secs (133942713 bytes/sec)

real	0m8.030s
user	0m0.004s
sys	0m1.381s

Divide 1024MB by 8 seconds and I got a write speed to 128MB/s on SATA III.

To satisfy my own curiosity, I plugged the hd (a 2TB Hitachi SATA III) back to the SATA II port and ran another write test. And the result turned out to be

time dd if=/dev/zero bs=1024k of=tstfile1 count=1024
1024+0 records in
1024+0 records out
1073741824 bytes transferred in 8.283360 secs (129626362 bytes/sec)

real	0m8.290s
user	0m0.004s
sys	0m1.179s

Which yields a 123MB/s, it’s slower than SATA III but not by much.

Using either port the outcome is pretty close to the results from:
http://www.servethehome.com/hitachi-5k3000-2tb-35-green-drive-silentspin-review-benchmarks/

3 Substring methods benchmark

#!/usr/bin/perl
use strict;
use warnings;

my $number='12345678';
my ($first,$second);
for my $i (1..10_000_000) {
    if( $number =~ /^(\d{3}(\d{5}))$/ ) {
        $first=$1;
        $second=$2;
    }
}
time perl reg.pl

real	0m14.066s
user	0m13.974s
sys	0m0.012s
============================================================
#!/usr/bin/perl
use strict;
use warnings;

my $number='12345678';
my ($first,$second);
for my $i (1..10_000_000) {
    ($first, $second)=unpack('a3a5', $number);
}

time perl unpack.pl

real	0m9.427s
user	0m9.321s
sys	0m0.016s
============================================================
#!/usr/bin/perl
use strict;
use warnings;

my $number='12345678';
my ($first,$second);
for my $i (1..10_000_000) {
    $first=substr($number, 0, 3);
    $second=substr($number, 3, 5);
}

time perl substr.pl

real	0m5.945s
user	0m5.846s
sys	0m0.017s

Seems the winner is the good old substr routine.