So, one of the cool advantages of Node.JS (over PHP for example) is the ability to pool up MYSQL DB connections.

The Reasoning: If you have 1 connection to the database, when you get 500 requests a second, they all have to wait in line for the database. With a connection of X, we can have for instance 10-40 connections open and give each request a random connection so the requests dont stack up behind each other.

The Code:

// Variables:
var http		= require('http');
var sql			= require('mysql');
var cluster 	= require('cluster');
var mysql		= {};
var x			= 0;
var sql_pool	= 25;

// Open up a Pool:
while(x <= sql_pool){
	mysql[x] = sql.createClient({
		host:		'localhost',
		port:		8889,
		user:		'root',
		password:	'root',
		database:	'enthread'
	});
	x++;
}

// HTTP Server:
var server = http.createServer(function(req,res){
	res.writeHead(200,{'Content-Type':'text/plain'});
	query(res);
});

// Run Query:
function query(res){
	mysql[Math.ceil(Math.random()*sql_pool)].query('SELECT * FROM connections WHERE id = '+Math.ceil(Math.random()*20000), function(error, results){
		print_results(results,res);
	});
}

// Print Results:
function print_results(data,res){
	for(var x in data){
		res.write(JSON.stringify(data[x]));
	}
	res.end();
}

// Cluster the Server:
cluster(server)
	.use(cluster.debug())
   	.listen(1337);

This script uses Cluster module and the mysql module. Cluster lets us spawn one node instance per code on your CPU (take full advantage of multicore CPUs).

Basically the sql_pool is the variable of how many connections to open. We can set this from 1 – X (remember MYSQL has a max connection variable).

After some Tinkering, I find 45 connections seem to be pretty good. I have a MYSQL database running in memory with 20,000 rows of simple data, and I was able to push 6,000 requests a second and get an average of 1MS response time. (Yes 1 MILLISECONDS). See attached images.

Machine: MacBook Pro 15″ – Quad Core i7 – 8GB Ram – SSD
Requests: 24,000 completed in 4 Seconds, 240 concurrent connections

With 45 Connections: (1MS Response Time)

With 1 DB Connection (27MS Response Time)

Share This

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

About the author

qrpike