This article is useful for those who use the connection pool in the Nodes.js + mysql module environment. This is the second part of introducing the mysql-pool-booster
module, which allows you to use the connection pool with better performance and functionality than original module.
- Performance booster for the pool of mysql (node.js)
- Try the improved PoolCluster
# PoolCluster?
Have you ever heard about PoolCluster
? This feature was added to mysql
module in 2013, but many people who have used mysql
module for a long time will not know that. (It was my first contribution for open source project.)
PoolCluster
provides the functionality to easily handle multiple connection pools. Please see this official document if you want to know about basic usage and options more.
Suddenly, I thought I had to improve this feature. Just like my fate.
# The beginning of improvement
For example, you have the following server groups.
Server group | Description |
---|---|
core | Core information is stored (Master, Slave) |
data-* | Sharded data is stored |
- Execute an INSERT query from a master server in the core group.
- Get the information from a slave server in the core group.
- Execute a SELECT query from one of servers in the data group.
The implementation using PoolCluster
is as follows.
const poolCluster = mysql.createPoolCluster({...});
poolCluster.add('core-master', {
host : '...',
user : '...',
password : '...'
});
poolCluster.add('core-slave', {
host : '...',
...
});
poolCluster.add('data-math', {
host : '...',
...
});
poolCluster.add('data-english', {
host : '...',
...
});
poolCluster.getConnection('core-master', (err, connection) => {
connection.query('INSERT log ...');
connection.release();
});
poolCluster.getConnection('core-slave', (err, connection) => {
connection.query('SELECT category FROM data_info WHERE seq=?', [seq], (err, results) => {
connection.release();
const clusterId = `data-${results[0].category}`;
poolCluster.getConnection(clusterId, (err, connection) => {
connection.query('SELECT ...', (err, rows) => {
// ...
});
});
});
});
I thought there were a lot of things to improve.
# The direction of improvement
Three functions have been added.
1. Create with options
I usually define the configuration as JSON, so adding a node using the add
function is inconvenient. That’s because I need to make additional codes (logic to read and initialize the configuration). Now, you can create it with options(nodes
> clusterId
) instead of using add
function.
// original version using the function
const cluster = mysql.createPoolCluster({...});
cluster.add('master', {
host : '...',
...
});
cluster.add('slave', {
host : '...',
...
});
// booster version using the options
const cluster = mysql.createPoolCluster({
....,
nodes : [{
clusterId : 'master',
host : '...',
...
}, {
clusterId : 'slave',
host : '...',
...
}]
});
2. Writer & Reader
In many cases, many people will use the connection pool consisting of a master and a slave server. In this case, I think it’s easier to use by accessing the concept of Writer and Reader rather than the ID base defined. You can set it up with options(nodes
> clusterType
) or functions(addWriter
, addReader
, add
).
/**
* 4 nodes are created.
* # Writer : No ID
* # Reader : main, sub, sub2
*/
const cluster = mysql.createPoolCluster({
....,
nodes : [{
clusterType : 'writer', // without clusterId
host : '...',
...
}, {
clusterType : 'reader', // with clusterId
clusterId : 'main',
host : '...',
...
}
});
cluster.addReader('sub', {
host : '...',
...
});
cluster.add({
clusterType : mysql.CLUSTER_TYPE.READER, // mysql.CLUSTER_TYPE.WRITER
clusterId : 'sub2',
host : '...',
...
});
// You can get a connection of the Writer group's nodes right away.
// the same as cluster.getWriter().getConnection(...)
cluster.getWriterConnection((err, connection) => {
...
});
// You can get a connection from all of the Reader group's nodes (`main`,` sub`, `sub2`)
cluster.getReaderConnection((err, connection) => {
...
});
// You can get a connection from specific Reader group's nodes (`sub`, `sub2`)
// the same as cluster.getReader('sub*').getConnection(function(err, connection)
cluster.getReaderConnection('sub*', (err, connection) => {
...
});
3. Sharding
If you want to use the application-level sharding, all you need to do is set it up with options(shardings
) or addSharding
function. In complex cases, I think you’d better use another professional sharding platform.
const cluster = mysql.createPoolCluster({
....,
nodes : [{
clusterId : 'old',
host : '...',
...
}, {
clusterId : 'new',
host : '...',
...
}],
shardings : {
byUserSeq : (user) => {
return user.seq > 10000000 ? 'new' : 'old';
}
}
});
cluster.addSharding('byTwoParams', (a, b) => {
return a + b > 10 ? 'new' : 'old';
});
You can use the getSharding
or getShardingConnection
(getShardingReaderConnection
, getShardingWriterConnection
) function with argument to get a connection.
const user = {
seq : 50000
};
// The connection is based on the user parameter. (In this case, 'old')
// the same as cluster.getSharding('byUserSeq', user).getConnection(...)
cluster.getShardingConnection('byUserSeq', user, (err, connection) => {
...
});
// The argument must be an array type if there is more than one.
cluster.getShardingConnection('byTwoParams', [valueA, valueB], (err, connection) => {
...
});
// You must use getReaderConnection() or getWriterConnection() if the nodes are defined as Writer&Reader.
// the same as cluster.getShardingReaderConnection('byUserNumber', user, ...)
cluster.getSharding('byUserNumber', user).getReaderConnection((err, connection) => {
...
});
# The result of improvement
Here’s an example of what you’ve seen in the previous version. Do you think it’s a little simpler?
const poolCluster = mysql.createPoolCluster({
...
nodes : [{
clusterType : mysql.CLUSTER_TYPE.WRITER,
clusterId : 'core',
host : '...',
...
}, {
clusterType : mysql.CLUSTER_TYPE.READER,
clusterId : 'core',
host : '...',
...
}, {
clusterId : 'data-math',
host : '...',
...
}, {
clusterId : 'data-english',
host : '...',
...
}],
shardings : {
dataByCategory : (dataInfo) => `data-${dataInfo.category}`
}
});
poolCluster.getWriterConnection((err, connection) => {
connection.query('INSERT log ...');
connection.release();
});
poolCluster.getReaderConnection((err, connection) => {
connection.query('SELECT category FROM data_info WHERE seq=?', [seq], (err, results) => {
connection.release();
poolCluster.getShardingConnection('dataByCategory', results, (err, connection) => {
connection.query('SELECT ...', (err, rows) => {
// ...
});
});
});
});
# Using the improved version
Install the mysql-pool-booster
module with the mysql
module installed,
npm install mysql-pool-booster
If you convert an existing mysql object, you can use the improved features.
let mysql = require('mysql');
// Converting an existing mysql object
const MysqlPoolBooster = require('mysql-pool-booster');
mysql = MysqlPoolBooster(mysql);
// Use it just as you used it
mysql.createPool({ ... });
mysql.createPoolCluster({ ... });
# If you have any problems
Please leave an issue in github or send me an e-mail. I’ll do my best to solve your problem as soon as possible. I have tried to make it work without problems, but since it’s a new project that has just started, please apply it to your service after enough verification steps.
I hope this helps. thanks.