Node.js MySQL2 Connection Pool Clustering

mysql multiple connection pool clustering

기존에는 mysqlconnection 의 execute 함수 안에서 하나의 커넥션만 사용했으나, 변경 후에 select_execute 함수를 만들어서 슬레이브 디비들을 순차적으로 번갈아가며 연결하는 방식으로 변경(기존 셀렉 쿼리들 변경 필요)

기존 코드(1 pool connection)

javascript

import mysql from "mysql2/promise";
import config from "../config";

const { host, port, user, password, database, connectionLimit } = config.mysql;

let pool: mysql.Pool;

export const init = async () => {
  try {
    pool = mysql.createPool({
      connectionLimit,
      host,
      user,
      password,
      database,
      port,
    });

    console.debug("MySql Adapter Pool generated successfully");
  } catch (error: any) {
    console.error("[mysql.connector][init][Error]: ", error);
    throw new Error(error);
  }
};

export const getConnect = async () => {
  return await pool.getConnection();
};

/**
 * executes SQL queries in MySQL db
 *
 * @param {string} query - provide a valid SQL query
 * @param {string[] | Object} params - provide the parameterized values used
 * in the query
 */
export const execute = async (
  query: string,
  params?: string[] | Object
): Promise<any> => {
  const conn = await pool.getConnection();
  try {
    if (!pool) {
      throw new Error(
        "Pool was not created. Ensure pool is created when running the app."
      );
    }
    const [results] = await conn.execute(query, params);
    return results;
  } catch (error: any) {
    console.error("[mysql.connector][execute][Error]: ", error);
    console.error("[query]: ", query, "[params]: ", params);
    throw new Error(error);
  } finally {
    conn.release();
  }
};

변경 코드 (3 pool connection)

javascript

import mysql from "mysql2/promise";
import config from "../config";

const {
  host,
  slave_1_host,
  slave_2_host,
  port,
  user,
  password,
  database,
  connectionLimit,
} = config.mysql;

let pool: mysql.Pool;
let poolCluster: mysql.PoolCluster;

export const init = async () => {
  try {
    pool = mysql.createPool({
      connectionLimit,
      host,
      user,
      password,
      database,
      port,
    });
    poolCluster = mysql.createPoolCluster({ restoreNodeTimeout: 1000 });
    poolCluster.add("MASTER", {
      host,
      connectionLimit,
      user,
      password,
      database,
      port,
    });
    poolCluster.add("SLAVE1", {
      host: slave_1_host,
      user,
      password,
      database,
      port,
    });
    poolCluster.add("SLAVE2", {
      host: slave_2_host,
      user,
      password,
      database,
      port,
    });

    console.debug("MySql Adapter Pool generated successfully");
  } catch (error: any) {
    console.error("[mysql.connector][init][Error]: ", error);
    throw new Error(error);
  }
};

export const getConnect = async () => {
  return await pool.getConnection();
};

/**
 * executes SQL queries in MySQL db
 *
 * @param {string} query - provide a valid SQL query
 * @param {string[] | Object} params - provide the parameterized values used
 * in the query
 */
export const execute = async (
  query: string,
  params?: string[] | Object
): Promise<any> => {
  const pool = await poolCluster.of("MASTER");
  const conn = await pool.getConnection();
  try {
    if (!poolCluster) {
      throw new Error(
        "Pool was not created. Ensure pool is created when running the app."
      );
    }
    const [results] = await conn.execute(query, params);
    return results;
  } catch (error: any) {
    console.error("[mysql.connector][execute][Error]: ", error);
    console.error("[query]: ", query, "[params]: ", params);
    throw new Error(error);
  } finally {
    conn.release();
  }
};

export const select_execute = async (
  query: string,
  params?: string[] | Object
): Promise<any> => {
  let pool = await poolCluster.of("SLAVE*", "RR");
  let conn = await pool.getConnection();
  try {
    if (!poolCluster) {
      throw new Error(
        "Pool was not created. Ensure pool is created when running the app."
      );
    }
    const [results] = await conn.execute(query, params);
    return results;
  } catch (error: any) {
    console.error("[mysql.connector][execute][Error]: ", error);
    console.error("[query]: ", query, "[params]: ", params);
    if (!pool) {
      pool = await poolCluster.of("SLAVE*", "RR");
    }
    if (!conn) {
      conn = await pool.getConnection();
    }
    await select_execute(query, params);
  } finally {
    conn.release();
  }
};

Node.js MySQL2 Connection Pool Clustering ​

mysql multiple connection pool clustering ​

Node.js MySQL2 Connection Pool Clustering

mysql multiple connection pool clustering