mysql-pool-booster
This is for those who use the connection pool in Node.js + mysql module environment. By adding just a few lines, you can use the connection pool with better performance and useful options than orginal. Please try it once if you're already using the pool of mysql.
If you don't know mysql
module, you should check out mysql first.
Table of Contents
- How to use
- Performance Improvements
- Useful options
- Advanced PoolCluster
- Additional Features
- Restrictions on use
How to use
- Install
$ npm install mysql-pool-booster
- Convert
mysql
var mysql = ; // Converting an existing mysql objectvar MysqlPoolBooster = ;mysql = ; // You can use it just as you used itmysql;
- Use the
mysql
just as it used to be
Performance Improvements
Applying without additional work, your service can be improved 80% or more efficiently.
This is the result of a performance measurement code that handles 30,000 requests.
Original | Booster Version | |
---|---|---|
Processing per second | 5,877 | 10,650 (181% ↑) |
You can experience a better effect if your service connects to remote mysql server and handles heavy concurrent requests.
Useful options
It gives you the flexibility to run the pool with many useful options similar to Java's DBCP.
Option | Default | Description |
---|---|---|
queueTimeout | 0 (off) | The maximum number of milliseconds that the queued request will wait for a connection when there are no available connections. If set to 0 , wait indefinitely. |
testOnBorrow | true | Indicates whether the connection is validated before borrowed from the pool. If the connection fails to validate, it is dropped from the pool. |
testOnBorrowInterval | 20000 | The number of milliseconds that indicates how often to validate if the connection is working since it was last used. If set too low, performance may decrease on heavy loaded systems. If set to 0 , It is checked every time. |
initialSize | 0 (off) | The initial number of connections that are created when the pool is started. If set to 0 , this feature is disabled. |
maxIdle | 10 | The maximum number of connections that can remain idle in the pool. If set to 0 , there is no limit. |
minIdle | 0 (off) | The minimum number of connections that can remain idle in the pool. |
maxReuseCount | 0 (off) | The maximum connection reuse count allows connections to be gracefully closed and removed from the connection pool after a connection has been borrowed a specific number of times. If set to 0 , this feature is disabled. |
timeBetweenEvictionRunsMillis | 0 (off) | The number of milliseconds to sleep between runs of examining idle connections. The eviction timer will remove existent idle conntions by minEvictableIdleTimeMillis or create new idle connections by minIdle . If set to 0 , this feature is disabled. |
minEvictableIdleTimeMillis | 1800000 | The minimum amount of time the connection may sit idle in the pool before it is eligible for eviction due to idle time. If set to 0 , no connection will be dropped. |
numTestsPerEvictionRun | 3 | The number of connections to examine during each run of the eviction timer (if any). |
Advanced PoolCluster
mysql
module has a useful feature called PoolCluster
to easily handle multiple hosts. If you want to know how to use it, please see this official document first. mysql-pool-booster
provides more advanced ways to use it.
Create with options
You can create it with options(nodes
> clusterId
) instead of using add
function. I think this is more useful if you're using the JSON config.
// original version using the functionvar cluster = mysql; cluster; cluster; // booster version using the optionsvar cluster = mysql;
Writer & Reader
In many cases, PoolCluster
is used for the purpose of using the master and slave connection pools, so it provides the concept of Writer
and Reader
for easier use. 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 */var cluster = mysql; cluster; cluster; // You can get a connection of the Writer group's nodes right away.// the same as cluster.getWriter().getConnection(...)cluster; // You can get a connection from all of the Reader group's nodes (`main`,` sub`, `sub2`)cluster; // You can get a connection from specific Reader group's nodes (`sub`, `sub2`)// the same as cluster.getReader('sub*').getConnection(function(err, connection)cluster;
Sharding
If you want to use the application-level sharding simply, 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.
// Create with optionsvar cluster = mysql; // Add with functionscluster;
You can use the getSharding
or getShardingConnection
(getShardingReaderConnection
, getShardingWriterConnection
) function with argument to get a connection.
var user = seq : 50000; // The connection is based on the user parameter. (In this case, 'old')// the same as cluster.getSharding('byUserSeq', user).getConnection(...)cluster; // The argument must be an array type if there is more than one.cluster; // You have to use getReaderConnection() or getWriterConnection() if the nodes are defined as Writer&Reader.// the same as cluster.getShardingReaderConnection('byUserNumber', user, ...)cluster;
Additional Features
prepared
The pool will emit a prepared
event when the pool is ready to use.
If initialSize
is set, this is called after all initial connections are created.
pool;
eviction
The pool will emit a eviction
event when the eviction timer runs.
pool;
Monitoring the status of a pool
If you want to know about the status of the pool, use the getStatus
method. The result of method consists of 4 values(all, use, idle, queue).
var status = pool;console;console;console;console;
Restrictions on use
It passes all tests provided by the mysql
module and provides 100% compatibility. However, there may be some problem if you are using it incorrectly such as accessing private underscore-prefix properties. For example.
// It's a misuse. you must not access private variablesif pool_allConnectionslength > 0 ... // You should use the following method insteadif poolall > 0 ...