Node中MySQL連接池的更換以及MySQL連接池集群的使用
做業(yè)務(wù)邏輯筐赔，無(wú)論你在用戶前面擺多少層花哨的東西捌锭，說(shuō)到底是用戶在跟數(shù)據(jù)庫(kù)交流昼汗。
好像每做一個(gè)項(xiàng)目朗涩，數(shù)據(jù)庫(kù)連接池都不可能不用尸诽，而不同的每種語(yǔ)言和不同的每種數(shù)據(jù)庫(kù)連接池的調(diào)研與選擇又不得不花去幾天時(shí)間圾笨，真正寫出來(lái)，測(cè)試是不是真正符合你的需求又是幾天時(shí)間逊谋。
在Node中擂达，我最初選擇的是generic pool，原因是上次假期調(diào)用到胶滋，有看了下主頁(yè)板鬓，感覺(jué)更新及時(shí)，還挺不錯(cuò)究恤，所以就用了俭令，使用的代碼如下：
/**
* Created by randy on 2017/6/29.
*/
var genericPool = require('generic-pool');
var DbDriver = require('mysql');
var settings = require('./settings');
/**
* Step 1 - Create pool using a factory object
*/
const factory = {
create: function () {
return new Promise(function (resolve, reject) {
var client = DbDriver.createConnection({
host: settings.mysql.host,
port: settings.mysql.port,
database: settings.mysql.database,
user: settings.mysql.user,
password: settings.mysql.password,
charset: settings.mysql.charset
});
client.connect(function (err) {
if (err) {
console.log("SQL CONNECT ERROR: ", err);
} else {
console.log("SQL CONNECT SUCCESSFUL.");
resolve(client)
}
});
})
},
destroy: function (client) {
return new Promise(function (resolve) {
client.on('end', function () {
resolve()
});
client.disconnect()
})
},
error: function () {
console.info('AAAAAAAA');
this.create();
}
};
var opts = {
max: 10, // maximum size of the pool
min: 5, // minimum size of the pool
// acquireTimeoutMillis: 2,
idleTimeoutMillis: 1000,
log: true
};
var myPool = genericPool.createPool(factory, opts);
/**
* Step 2 - Use pool in your code to acquire/release resources
*/
// acquire connection - Promise is resolved
// once a resource becomes available
const resourcePromise = myPool.acquire()
.then(function (resource) {
console.log('resource');
})
.catch(function (err) {
console.log(err);
return myPool.drain()
});
module.exports = {
doGetQuery: function (res, procName, handler) {
var paraArray = [];
var stmt = "CALL " + procName + "()";
resourcePromise.then(function (client) {
client.query(stmt, paraArray, function (err, rows, fields) {
if (err) {
throw err;
}
handler(res, rows);
//myPool.release(client);
});
})
.catch(function (err) {
// handle error - this is generally a timeout or maxWaitingClients
// error
});
},
doPostQuery: function (req, res, procName, handler) {
req.on("data", function (chunk) {
var params = JSON.parse(chunk.toString());
var paraArray = [];
var stmt = "CALL " + procName + "(";
for (var param in params) {
stmt = stmt + "?, ";
paraArray.push(params[param]);
}
stmt = stmt.substring(0, stmt.length - 2) + ")";
resourcePromise.then(function (client) {
client.query(stmt, paraArray, function (err, rows, fields) {
if (err) {
throw err;
}
handler(res, rows);
//myPool.release(client);
});
})
.catch(function (err) {
// handle error - this is generally a timeout or maxWaitingClients
// error
});
}
)
}
};
/**
* Step 3 - Drain pool during shutdown (optional)
*/
// Only call this once in your application -- at the point you want
// to shutdown and stop using this pool.
/*
myPool.drain().then(function() {
myPool.clear();
});
*/
無(wú)奈事實(shí)證明，這個(gè)連接池會(huì)在MySQL服務(wù)端主動(dòng)關(guān)閉連接后部宿，不僅不自動(dòng)重連抄腔，還會(huì)把Node進(jìn)程弄掛瓢湃，不知是我設(shè)置有問(wèn)題還是別的，正好有個(gè)契機(jī)調(diào)研到MySQL模塊自帶連接池也不錯(cuò)赫蛇，完全逆反常理绵患，真乃良心模塊。
var mysql = require('mysql');
var settings = require("./settings");
var config = {
host: settings.mysql.host,
port: settings.mysql.port,
database: settings.mysql.database,
user: settings.mysql.user,
password: settings.mysql.password,
charset: settings.mysql.charset,
acquireTimeout: settings.mysql.acquireTimeout,
waitForConnections: settings.mysql.waitForConnections,
connectionLimit: settings.mysql.connectionLimit,
queueLimit: settings.mysql.queueLimit
};
var pool = mysql.createPool(config);
pool.getConnection(function (err, connection) {
console.log(err);
});
module.exports = {
doGetQuery: (res, procName, handler) => {
let paraArray = [];
let stmt = "CALL " + procName + "()";
pool.getConnection(function (err, connection) {
connection.query(stmt, paraArray, function (err, rows, fields) {
if (err) {
throw err;
}
handler(res, rows);
});
connection.release();
});
},
doPostQuery: function (req, res, procName, handler) {
req.on("data", function (chunk) {
let params = JSON.parse(chunk.toString());
let paraArray = [];
let stmt = "CALL " + procName + "(";
for (let param in params) {
stmt = stmt + "?, ";
paraArray.push(params[param]);
}
stmt = stmt.substring(0, stmt.length - 2) + ")";
pool.getConnection((err, connection) => {
connection.query(stmt, paraArray, function (err, rows, fields) {
if (err) {
throw err;
}
handler(res, rows);
});
connection.release();
});
}
)
}
};
至此悟耘，連接池完畢落蝙，相比Generic pool，MySQL Pool有個(gè)缺點(diǎn)是沒(méi)有常用連接池的一些配置暂幼，比如最小連接數(shù)筏勒，網(wǎng)上有人總結(jié)出數(shù)據(jù)庫(kù)連接池常用配置：
{
connectionLimit : 50,
queueWaitTimeout : 10000, // Same as acquireTimeout.
pingCheckInterval : 10000, // The connection used in 10 seconds is reused without ping check.
startConnections : 10, // 10 connections are created when the pool is started.
minSpareConnections : 10, // 10 spare connections should be kept in the pool at all times.
maxSpareConnections : 20, // No more than 20 spare connections.
spareCheckInterval : 300000 // Check the spare connections every 5 minutes.
}
不過(guò)問(wèn)題不大，后來(lái)根據(jù)需求旺嬉，又為MySQL Pool 加了集群管行，這是在看Node MySQL時(shí)無(wú)意發(fā)現(xiàn)的，這可是個(gè)好東西邪媳，加上加上：
var mysql = require('mysql');
var settings = require("./settings");
var poolcfg = require("./msqNodeCfg");
var poolCluster = mysql.createPoolCluster();
poolCluster.add(settings.mysqlPoolCluster);
poolCluster.add('X', poolcfg.node01);
poolCluster.add('Y', poolcfg.node02);
poolCluster.add('Z', poolcfg.node03);
module.exports = {
doGetQuery: (res, procName, handler) => {
let paraArray = [];
let stmt = "CALL " + procName + "()";
poolCluster.getConnection(function (err, connection) {
connection.query(stmt, paraArray, function (err, rows, fields) {
if (err) {
throw err;
}
handler(res, rows);
});
connection.release();
});
},
doPostQuery: function (req, res, procName, handler) {
req.on("data", function (chunk) {
let params = JSON.parse(chunk.toString());
let paraArray = [];
let stmt = "CALL " + procName + "(";
for (let param in params) {
stmt = stmt + "?, ";
paraArray.push(params[param]);
}
stmt = stmt.substring(0, stmt.length - 2) + ")";
poolCluster.getConnection((err, connection) => {
connection.query(stmt, paraArray, function (err, rows, fields) {
if (err) {
throw err;
}
handler(res, rows);
});
connection.release();
});
}
)
}
};
配置文件如下：
主配置文件：
mysql: {
host: "127.0.0.1",
port: 3306,
user: "root",
password: "root",
database: "db_test",
charset: "utf8"
},
mysqlPool: {
acquireTimeout: 10000,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
},
mysqlPoolCluster: {
canRetry: true,
removeNodeErrorCount: 5,
restoreNodeTimeout: 0,
defaultSelector: "RR"
}
集群節(jié)點(diǎn)配置文件：
var settings = require("./settings");
module.exports = {
node01: {
host: '192.168.1.240',
port: settings.mysql.port,
database: settings.mysql.database,
user: settings.mysql.user,
password: "root",
charset: settings.mysql.charset,
acquireTimeout: settings.mysqlPool.acquireTimeout,
waitForConnections: settings.mysqlPool.waitForConnections,
connectionLimit: settings.mysqlPool.connectionLimit,
queueLimit: settings.mysqlPool.queueLimit
},
node02: {
host: "192.168.1.122",
port: settings.mysql.port,
database: settings.mysql.database,
user: settings.mysql.user,
password: "root",
charset: settings.mysql.charset,
acquireTimeout: settings.mysqlPool.acquireTimeout,
waitForConnections: settings.mysqlPool.waitForConnections,
connectionLimit: settings.mysqlPool.connectionLimit,
queueLimit: settings.mysqlPool.queueLimit
},
node03: {
host: '192.168.1.105',
port: settings.mysql.port,
database: settings.mysql.database,
user: settings.mysql.user,
password: "root",
charset: settings.mysql.charset,
acquireTimeout: settings.mysqlPool.acquireTimeout,
waitForConnections: settings.mysqlPool.waitForConnections,
connectionLimit: settings.mysqlPool.connectionLimit,
queueLimit: settings.mysqlPool.queueLimit
}
};
