NodeでのMySQL接続プールの交換およびMySQL接続プールクラスタの使用

11154 ワード

NodeでのMySQL接続プールの交換およびMySQL接続プールクラスタの使用
ビジネスロジックを行うには、ユーザーの前にどれだけ派手なものを並べても、結局はユーザーがデータベースと交流しています.
プロジェクトを行うたびに、データベース接続プールを使用することはできませんが、異なる言語と異なるデータベース接続プールの調査と選択には数日かかり、本当に書かなければなりません.テストは本当にあなたのニーズに合っているかどうか、また数日かかります.
Nodeでは、私が最初に選んだのはgeneric poolで、前回の休暇中に呼び出されたので、ホームページを見て、更新がタイムリーで、悪くないと感じました.

 /**
 * 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();
});
*/

残念なことに、この接続プールはMySQLサービス側が自発的に接続を閉じた後、自動的に再接続しないだけでなく、Nodeプロセスを掛けることも証明されています.私が設定した問題なのか、それとも他の問題なのか、ちょうどMySQLモジュールが接続プールを持っていることを調査するきっかけがあります.

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 poolMySQL Poolと比較して、最小接続数などの共通接続プールの構成がないという欠点があります.ネット上では、データベース接続プールの共通構成をまとめています.

{
  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.
}

しかし、問題は大きくありません.その後、需要に応じて、MySQL Poolにクラスタを追加しました.これはNode MySQLを見たときに何気なく発見されました.これはいいものです.加えて:

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"
}

クラスタノードプロファイル:

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
    }
};

主な参考:

generic pool

Node MySQL

Nodejs mysql pool使用例

MySQLのwait_timeout

GetMessageとPeekMessageの違い

Mysqlがサポートするデータ型