Using Node.js with MySQL Module I have done some testing. Under a real word situation stress on the server from multiple connections could result in a database connection failure. The answer is pooled connections. Here is my test code.

Non-pooled Connection

// test-sync.js
var express = require('express')
var app = express()

app.get('/test-sync', function (req, res) {
// console.log('1. Received Get')
 res.send('Hello World!')

var mysql = require('mysql');
var connection = mysql.createConnection({
 host : 'localhost',
 user : 'mazu',
 password : '',
 database : 'mazudb'
});
connection.connect(function(err){
if(!err) {
// console.log("2. Database Connected"); 
} else {
 console.log("Error connecting database ... nn"); 
}
});

sql="SELECT * FROM `test-mysql-Customers` LIMIT 2"
connection.query(sql, function(err, rows, fields) {
connection.end();
 if (!err) {
// console.log("3. SQL Completed");
} else
 console.log('Error while performing Query.');
 });
});

app.listen(4200, function () {
 console.log('Example app listening on port 4200!')
})

Pooled Connection

var express = require("express");
var mysql = require('mysql');
var app = express();

var pool = mysql.createPool({
 connectionLimit : 100, //important
 host : 'localhost',
 user : 'mazu',
 password : '',
 database : 'mazudb',
 debug : false
});

function handle_database(req,res) {
 
 pool.getConnection(function(err,connection){
 if (err) {
 res.json({"code" : 100, "status" : "Error in connection database"});
 var tlog = json({"code" : 100, "status" : "Error in connection database"});
 console.log(tlog);
 return;
 }

connection.setMaxListeners(0)

// console.log('connected as id ' + connection.threadId + ' connection.getMaxListeners()' + connection.getMaxListeners());
 
 sql="SELECT * FROM `test-mysql-Customers` LIMIT 2"

connection.query(sql,function(err,rows){
 connection.release();
 if(!err) {
 res.json(rows);
 } 
 });

connection.on('error', function(err) { 
 var tlog = json({"code" : 100, "status" : "Error in connection database"});
 console.log(tlog);
 res.json({"code" : 200, "status" : "Error in connection database"});
 return; 
 });
 });
}

app.get("/test-async",function(req,res){-
 handle_database(req,res);
});

app.listen(4200);

The primary difference between the two methods is the first one does a synchronous creatConnection, connect, query, and end; while the second pooled method does a createPool connection that creates a queue for the queries. When a get request is received it then does a getConnection that uses one process from the pooled process queue, it does its query, and finally a release of that one process in the pooled queue.

A stress test on these methods results in similar throughput. Essentially the same number of transactions can get through but with non-pooled connections, the likelihood of database connection errors is higher. I used a siege tool stress test with the following

siege -c200 -t60s -d3 http://localhost:4200/test-sync

It resulted in about 8000 hits but on the synchronous non-pooled method it resulted in about 16 database connection error, while the pooled method resulted in no connection errors. This test was with an Amazon EC t2.micro with Linux and an external RDS MySQL database. Obviously, database connection errors are bad! This proves a pooled connection is the way to go.

Advertisements