node server/npm mysql/connection pool 구현

  connection pool(CP)의 정의는 '소프트웨어 공학에서 데이터베이스로의 추가 요청이 필요할 때 연결을 재사용할 수 있도록 관리되는 데이터베이스 연결의 캐시'이다.

   사이트에 사용자가 접속하면 백엔드 서버에서 db 서버에 접속해 query를 실행하게 된다. 이때마다, (예를들어) nodeserver와 mysql server을 연결하고 query 수행이 끝나면 연결을 끊는 작업을 한다. 

  CP를 사용하면 이러한 연결/연결끊기의 과정을 최소한으로 줄일 수 있기 때문에, 연결에 소요되는 시간과 사용되는 서버 자원을 아낄 수 있다.

 

node 서버를 AWS EC2 t2.micro를 사용한다고 가정하면, 동시간에 허용된 connection max값은 66개이다. 

mysql에서 아래 쿼리를 실행해보면 해당 DB의 max_connection 값을 확인할 수 있다.

--max connection 확인
show variables like '%max_connections%';
-- MySql접속 Session 확인
show processlist;
-- Connection 수 확인
show status like 'Threads_connected';

node에서 CP세팅 방법은 아래와 같다. 여기서 중요한건 pool을 생성하는 부분(createPool)은 서버가 구동되고 1번만 읽혀야한다.

만약에 router.post("/", (req, res) => {} 안쪽에 mysql.createPool 코드가 들어간다면, post 호출 할때마다 pool을 생성하게 된다. 그렇게 되면 release를 해도 connetion수가 계속 쌓이게 된다. 

아래 npm mysql 모듈에서는 클라이언트에게 connection을 분배할 때 round-robin 알고리즘을 사용한다.

 

<참고> https://www.npmjs.com/package/mysql#pool-options

var express = require("express");
var router = express.Router();
const mysql = require("mysql");

const bodyParser = require("body-parser");

router.use(bodyParser.json());
router.use(bodyParser.urlencoded({ extended: true }));

//pool 생성 ############################### 여기 보세요 #####################
const pool  = mysql.createPool({
  connectionLimit: 66,//최대 연결가는 connetion수 설정 ### 여기 보세요 ###
  waitForConnections: true,
  //true : 사용가능한 connection이 없다면 기다린다, false : 에러처리 ### 여기 보세요 ###
  host: "rds_endpoint",
  port: "3306",
  database: 'rtrod',
  user: "user",
  password: "sdkfsdkk",
});

//connetion을 획득하면 %d부분에 connetion id가 출력된다.  #### 여기 보세요 #####
pool.on('acquire', function (connection) {
  console.log('Connection %d acquired', connection.threadId);
});

//connetion을 끊으면 %d부분에 connetion id가 출력된다.  #### 여기 보세요 #####
pool.on('release', function (connection) {
  console.log('Connection %d released', connection.threadId);
});

//사용 가능한 connetion이 존재하지 않으면 큐에 등록하고 순서를 기다린다. #### 여기 보세요 ####
pool.on('enqueue', function () {
  console.log('Waiting for available connection slot');
});

router.post("/", (req, res) => {
  const mybatisMapper = require("mybatis-mapper");

  var param = req.body;
  var m_typ = req.query.type;
  mybatisMapper.createMapper(["./models/" + param.mapper + ".xml"]);
  
  //질의문 형식
  var format = { language: "sql", indent: "  " };
  var query = mybatisMapper.getStatement(
		...
  );
      
  try {
    //생성된 pool에서 connection을 가져온다.############## 여기 보세요 ###############
    pool.getConnection(function(err,connection){
        if (err) throw err;
        connection.query(query, function(error, results) {
            //조회
            var time2 = new Date();
            ...
            string = JSON.stringify(results);
            var json = JSON.parse(string);
            ...
            //db접속이 성공하던 실패하던 connetion을 끊는다.########## 여기 보세요 ############
            connection.release();
            if (error) {
              throw error
            }
        });// connection.query end
    })// getConnection end
  } catch (error) {
  }
});

module.exports = router;

상황별 CP처리 방식을 예시로 정리해 보았다.

 

<가정>

DB Server(RDS) Max Connection : 66개

Connection Pool limit(connectionLimit) : 6개

 

<순서>

1. 최초에 어플리케이션 서버(사이트)가 켜지면 Connection을 0개 생성한다. 

2. query 3개 동시 실행 > query 완료후 release > connection 3개 생성

   (만약 query가 각각 실행시간이 0.5초이고 1초 간격으로 실행된다면 connetion은 1개만 생성된다. 반환된 연결을 사용하면 되기 때문에)

3. query 2개 동시 실행 > 1에서 생성된 connection중 2개 사용 > query 완료후 release

4. query 4개 동시 실행 > 1에서 생성된 connection중 3개 사용 + connection 1개 추가 = connection 수는 4개

5. 4번에서 실행된 4개 query중 1개 빼고 3개 release > 6개 query 동시 실행 > 

   5-1) 4에서 생성한 connection 4개 중 3개 사용, 1개는 사용중으로 사용불가

         2개 connetion 추가 생성

         이때 1개의 connection이 더 필요한데, 6개의 connection이 모두 사용중이다.

         나머지 1개의 query는 대기상태가 된다.(대기 queue에 push)