mysql 패키지) connection pool의 필요성 및 생성

mysql

mysqljs/mysql

[node.js] mysql 사용부터 pool 관리까지

 

 

 

sequelize는 mysql2 혹은 mysql 패키지와 함께 사용되는 것이 일반적이므로 살펴보자. 사실은 일 때문에 억지로라도 My SQL을 공부해야 한다.

 

내가 받은 프로젝트의 일부 코드이다. 하나씩 훑어내려가보자.

const mysql = require("mysql");
const util = require("util");
const dotenv = require("dotenv");

dotenv.config();

const { DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE } = process.env;

// mysql 연결
const pool = mysql.createPool({
  connectionLimit: 5000,
  host: DB_HOST,
  user: DB_USER,
  password: DB_PASSWORD,
  database: DB_DATABASE,
  multipleStatements: true
});
console.log(DB_HOST);
console.log(pool);

// db 연결
pool.getConnection((err, connection) => {
  if (err) {
    switch (err.code) {
      case "PROTOCOL_CONNECTION_LOST":
        console.error("Database connection was closed.");
        break;
      case "ER_CON_COUNT_ERROR":
        console.error("Database has too many connections.");
        break;
      case "ECONNREFUSED":
        console.error("Database connection was refused.");
        break;
    }
  }
  if (connection) return connection.release();
});

pool.query = util.promisify(pool.query);

pool.getConnection = util.promisify(pool.getConnection);

pool.transaction = async queries => {
  const connection = await pool.getConnection();
  try {
    connection.beginTransaction = util.promisify(connection.beginTransaction);
    connection.commit = util.promisify(connection.commit);
    await connection.beginTransaction();
    connection.query = util.promisify(connection.query);
    for (let i in queries) {
      const query = queries[i];
      const result = await connection.query(query.q, query.d);
      queries[i] = result;
    }
    await connection.commit();
    connection.release();
    return queries;
  } catch (e) {
    connection.rollback = util.promisify(connection.rollback);
    await connection.rollback();
    connection.release();
    throw e;
  }
};

module.exports = pool;

 

 

mysql 연결 관련 트러블 슈팅

 

에러 : ER_NOT_SUPPORTED_AUTH_MODE

해결 : ALTER USER '사용할 유저'@'localhost' IDENTIFIED WITH mysql_native_password BY '당신의 비밀번호'

 

에러 : ER_BAD_DB_ERROR: Unknown database 'xxx'

해결 : 해당 DB가 없다는 것이므로 shell에서 동일한 이름의 DB를 생성하면 됩니다.

 

connection pool의 필요성 및 생성

 

일반적으로 'Connection'이라 함은 DB에 접속 -> SQL 문을 날리고 -> 결과를 받은 후 -> 연결 종료의 플로우를 따른다. 결과를 받은 후 커넥션을 닫지 않으면 리소스를 불필요하게 낭비하는 것이다.

 

mysql 패키지 공식 문서에도 살펴보면 connect를 하고(접속) -> sql 문을 날리고 -> end를 통해 connection을 종료한다

커넥션 옵션은 아래에서 확인할 수 있습니다.

https://github.com/mysqljs/mysql#connection-options

var mysql      = require('mysql');

// mysql 객체 생성
var connection = mysql.createConnection({
  ...생략
});

// 연결. 에러 콜백을 받는다.
connection.connect(function(err) {
 // 에러 핸들링
});

// 쿼리
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

// Connection 종료. 에러 콜백을 받는다.
connection.end(function(err) {
  // The connection is terminated now
});

 

문제는 이러한 플로우를 수행하는 인원이 많아지게 되면 (위 코드에서는 5000명을 한도로 보고 있다) 이러한 접속-쿼리-결과-close 를 요청 만큼 반복하는 것이고, 다른 사람의 커넥션이 처리될 때까지 기다려야 하게 된다.

 

이러한 커넥션을 동시에 처리하기 위해서는 Pool을 구성해야 한다. Pool의 제한 인원에 해당 되는 것은 동일한 Connection으로 관리하고, 처리가 끝나면 Connection에 들어오지 못한 대기 인원을 Pool에 포함시켜서 처리한다.

 

평소보다 사이트의 인원 중 Pool의 인원을 초과하는 인원이 들어오게 되면 정보가 늦게 도착하는 이유가 이런 이유에서이다.

출처 : https://brownbears.tistory.com/289

 

 

 

 

공식 문서에서도 one-by-one이 아니라 Pooling 기능을 지원합니다.

 

Rather than creating and managing connections one-by-one, this module also provides built-in connection pooling using mysql.createPool(config) - https://github.com/mysqljs/mysql#pooling-connections

 

var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

 

 

그런데 Pool에 있어서는 일반 connection과 달리 shortcut을 제공합니다.

일반적인 Connection과 마찬가지로 연결 -> 쿼리 -> 종료의 flow를 따릅니다.

pool.getConnection() -> connection.query() -> connection.release()

 

getConnection을 통해 pool을 생성하고 그 내부에서 query와 release를 이용합니다.

커넥션 옵션은 일반 옵션의 모든 걸 다 쓸 수 있습니다.

// pool 생성
var pool  = mysql.createPool(...);

// getConnection
pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected!

  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

 

만약 Pool을 강제로 끄고 싶다면 connection.destroy()을 사용하면 됩니다.

 

Connection은 Lazy한 방식으로 만들어 집니다. 그러니까, 코드 실행시 연결되는 것이 아니라 필요할 때 연결된다는 것입니다. 

 

 

Pool events

Pool이 만들어진 후 쿼리를 처리하면서 발생하는 이벤트들입니다. 이벤트 도중 인터셉트하여 특정한 로직을 적용하고 싶으면 다음 이벤트를 이용하면 됩니다.

 

 

acquire

The pool will emit an acquire event when a connection is acquired from the pool.

This is called after all acquiring activity has been performed on the connection, right before the connection is handed to the callback of the acquiring code.

 

pool.on('acquire', function (connection) {
  console.log('Connection %d acquired', connection.threadId);
});

 

connection

 

The pool will emit a connection event when a new connection is made within the pool. If you need to set session variables on the connection before it gets used, you can listen to the connection event.

pool.on('connection', function (connection) { connection.query('SET SESSION auto_increment_increment=1') });

enqueue

The pool will emit an enqueue event when a callback has been queued to wait for an available connection.

pool.on('enqueue', function () { console.log('Waiting for available connection slot'); });

release

The pool will emit a release event when a connection is released back to the pool. This is called after all release activity has been performed on the connection, so the connection will be listed as free at the time of the event.

pool.on('release', function (connection) { console.log('Connection %d released', connection.threadId); });