ProxySQL을 사용하면 DB Connection을 줄일 수 있다고 ?

 

 

목차

 

개요 

FastAPI를 사용해 API를 만든 뒤 locust를 통해 성능 테스트를 진행했다. “대략 이 정도 TPS가 나오는구나”라고 확인한 뒤 MySQL의 Connection도 이상은 없는지 관심이 갔다.

 

SQLAlchemy에 pool_size를 2로 설정하고 uvicorn worker를 4개로 설정한 상황이다. MySQL 커넥션이 최대 8개(2*4)까지 늘어나는 현상을 보고 “무난하네”라고 생각할 때쯤 의문이 생겼다.

서버(FastAPI)를 증가시킨다면 MySQL 커넥션 자체는 계속 늘어나는거 아닌가?

 

즉, Application Server의 수가 증가할 수록 MySQL 서버와 맺게 되는 커넥션 수가 늘어난다는 문제가 존재하는 것이다.

 

MySQL Connnection이 급격하고 대량으로 늘어났던 경험은 없지만 그래도 뭔가 있지 않을까 싶어 해결책을 조사하던 찰나에 “ProxySQL”이라는 것을 알게 되었다.

 

1. ProxySQL?

ProxySQL의 공식 홈페이지에서는 ProxySQL을 다음과 같이 설명하고 있다.

ProxySQL is an open source high performance, high availability, database protocol aware proxy for MySQL

ProxySQL은 MySQL을 위한 오픈 소스 고성능, 고가용성, 데이터베이스 프로토콜 인식 프락시입니다. 

 

위와 같은 정의만 봐서는 ProxySQL이 정확히 뭘 제공하는지 와닿지 않는다. 좀 더 찾아본 결과 ProxySQL에서 제공하는 기능에는 다음과 같은 것들이 있었다.

1. Connection Multiplexing
2. Query Routing
3. Monitoring

이 중 필자가 눈여겨본 것은 “개요”에서 언급한 MySQL에 대한 Connection 관리 기능이 “Connection Multiplexing”과 밀접하단 부분이다.

 

“Connection Multiplexing”에 대한 설명을 ProxySQL 홈페이지에서는 다음과 같이 설명한다.

ProxySQL Connection Multiplexing drastically reduces connection usage and load by intelligently serving multiple frontend connections using a single backend connection.

ProxySQL Connection Multiplexing는 단일 백엔드 연결을 사용하여 여러 프론트엔드 연결을 지능적으로 제공함으로써 연결 사용량과 부하를 크게 줄입니다.

 

이제 ProxySQL에 대한 환경을 구성하고 실제 Connection을 관리하는 기능이 있는지 알아보자.

 

 

2. ProxySQL Setup

2.1 docker-compose를 이용한 Setting

ProxySQL이 MySQL에 대해 가정하는 것이 MySQL Replication 구조인 듯싶다. 그래서 docker-compose를 구성할 때도 Replication을 구성할 수 있도록 작성했다.

version: '3.7'

services:
  mysql-master:
    image: mysql:latest
    platform: linux/amd64
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1234
      MYSQL_DATABASE: demo
    ports:
      - "3306:3306"
    volumes:
      - ./mysql-master:/var/lib/mysql
    networks:
      - proxynet

  mysql-slave1:
    image: mysql:latest
    platform: linux/amd64
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1234
      MYSQL_DATABASE: demo
    ports:
      - "3307:3306"
    volumes:
      - ./mysql-slave1:/var/lib/mysql
    networks:
      - proxynet

  mysql-slave2:
    image: mysql:latest
    platform: linux/amd64
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1234
      MYSQL_DATABASE: demo
    ports:
      - "3308:3306"
    volumes:
      - ./mysql-slave2:/var/lib/mysql
    networks:
      - proxynet

  proxysql:
    image: proxysql/proxysql
    restart: always
    ports:
      - "6033:6033"
      - "6032:6032"
    volumes:
      - ./proxysql.cnf:/etc/proxysql.cnf
    networks:
      - proxynet

  proxyweb:
    image: proxyweb/proxyweb
    platform: linux/amd64
    restart: always
    environment:
      - PROXYSQL_HOST=proxysql
      - PROXYSQL_PORT=6032
    ports:
      - "5000:5000"
    depends_on:
      - proxysql
    networks:
      - proxynet

networks:
  proxynet:
    driver: bridge

 

2.2 ProxySQL setup

proxysql에는 설정 파일이 필요하다.

 

앞서 기재한 docker-compose.yml에서 proxysql.conf이라는 파일이다. 이 파일에는 다음과 같은 내용이 들어간다.

datadir="/var/lib/proxysql"
admin_variables=
{
  admin_credentials="admin:admin;radmin:radmin"
  admin-stats_credentials="stats:stats;rstats:rstats"
  mysql_ifaces="0.0.0.0:6032"
  web_enables="true"
}
mysql_variables=
{
  threads=4
  max_connections=200
  default_query_delay=0
  default_query_timeout=36000000
  have_compress=true
  poll_timeout=2000
  interfaces="0.0.0.0:6033;/path/to/proxysql.sock"
  default_schema="information_schema"
  stacksize=1048576
  server_version="5.5.30"
  monitor_username="monitor"
  monitor_password="monitor"
  monitor_history=600000
  monitor_connect_interval=60000
  monitor_ping_interval=10000
  monitor_read_only_interval=1500
  monitor_read_only_timeout=500
  connect_retries_on_failure=10
}

mysql_servers =
( {address="mysql-master", port=3306, hostgroup=0, max_connections=200} )

mysql_users:(
  {
    username="root"
    password="root"
    default_hostgroup=0
    active=1
  },
  {
    username="stnduser1"
    password="stnduser1"
    default_hostgroup=0
    active=1
  }
)

위 파일을 통해 Query Routing도 가능하다. 이는 이 링크를 참고하자. 삽질 중에 한 가지 특이사항이 있었는데 위 파일에서 server_version을 설정하지 않아 SQLAlchemy가 동작하지 않는 이슈였다.

 

2.3 MySQL-Master 설정

MySQL Replication 구조라서 Master, Slave에서 각각 뭔가를 설정해 줘야 하는데 것처럼 보일 수 있다.

 

그러나 이 포스팅의 목적은 MySQL Connection이 ProxySQL을 통해서 관리가 일어나는지를 보는 것이기 때문에 Slave에 들어가서 Master에 대한 설정을 따로 잡아주진 않았다. 하지만 mysql-master에서 설정해줘야 하는 부분이 존재하는데 이는 proxysql이 mysql-master로 접속할 수 있는 계정의 권한을 mysql-master에 잡아줘야 한다는 점이다.

 

proxysql.conf에서 확인할 수 있듯 “stnduser1”과 “monitor” 계정을 mysql-master에 접속해 설정해 주자.

# stnduser1
mysql> CREATE USER 'stnduser1'@'%' IDENTIFIED BY 'stnduser1';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'stnduser1'@'%';

# Monito
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%';

 

2.4 proxyweb

docker-compose.yml 구성에서 proxyweb이란 게 존재하는데 proxysql을 모니터링하기 위한 GUI 도구이다. 설정에서 알 수 있다시피 5000번 포트로 접근할 수 있다  docker-compose를 이용하는 것이기에 proxyweb에서는 proxysql과 통신할 수 있게 다음과 같이 설정을 해주자.

 

 

3. Connection이 관리되는지 확인해 보자.

proxysql을 띄웠으니 이제 Connection의 변화가 있는지 확인해 볼 차례이다. 똑같이 FastAPI + SQLAlchemy를 이용해서 실험해 볼 것이고 ProxySQL과 MySQL-Master을 대상으로 설정을 잡은 뒤 Locust를 통해 Connection의 변화가 있는지 확인해 보자.

 

3.1 SQLAlchemy과 Uvicorn 옵션 설정

ProxySQL과 MySQL-Master에 대해 테스트를 진행하기 전 SQLAlchemy에 대한 옵션은 다음과 같음을 참고하자.

pool_pre_ping=True,
pool_recycle=3600,
pool_size=2,
max_overflow=0,
pool_timeout=60,
echo=True

또한 Uvicorn은 worker를 12개로 설정했다.

uvicorn application:demoapplication --workers 12

추가로 날리는 쿼리는 단순한 SELECT 문이다.

select * from tb_test;

 

3.2 MySQL-Master을 대상으로 테스트

MySQL-Master를 대상으로 연결하는 것은 기존에 DataBase에 대해 연결을 설정하는 것과 비슷하다.

drivername = "mysql+mysqldb"
username = "stnduser1"
password = "stnduser1"
host = "0.0.0.0"
port = 3306

SQLAlchemy의 pool_size가 2개에 worker를 12개로 설정했으니 예상되는 최대 커넥션 수는 24개이다. MySQL-Master에 연결한 뒤 확인해 보니 실제 MySQL의 Connection은 예상대로 24개로 확인됐다.

 

 

3.3 ProxySQL을 대상으로 테스트

ProxySQL을 대상으로 연결하는 것은 6033 port를 사용해야 한다.

drivername = "mysql+mysqldb"
username = "stnduser1"
password = "stnduser1"
host = "0.0.0.0"
port = 6033

앞서 MySQL-master의 Connection의 수인 24개보다 이하의 Connection이 잡힌다면 ProxySQL의 Connection Multiplexing은 “개요”에서 언급한 필자의 의문을 해결해 줄 수 있는 부분이다.

 

MySQL-Master에 연결한 뒤 확인해 보니 Connection 24개에서 20개로 줄었다.

 

 

마치며

proxysql을 띄우기 위해 많은 삽질이 있었다. docker-compose를 이용한 시도 중에서 개별 container에 붙어 cli를 이용해 삽질도 했는데 대부분 설정파일(proxysql.conf)의 옵션을 설정함으로써 해결할 수 있었다.

 

또한 DataBase에 직접 요청하는 것보다 ProxySQL을 통해 요청을 하면 Connection의 수가 줄어드는 것까지 확인했지만 4개 정도 차이가 나는 걸 보면 “왜 4개일까?”라는 궁금증이 생기지만 “개요”에서 언급한 의문을 풀 수 있었다는 것에 의미를 둬야겠다.

 

---