[MySQL] #2 ProxySQL를 통한 DB Read/Write 분리 및 캐싱

들어가며

이전 글에서 MySQL Replication(Source / Replica)을 구성하여 데이터가 복제되는 구조를 확인했습니다.

하지만 실제 서비스에서는 Replication를 구성하는 것만으로는 부족합니다.

애플리케이션이 직접 다음과 같은 사항을 판단해야 한다면 관리가 어려워집니다.

• 어떤 쿼리를 Source로 보낼지
• 어떤 쿼리를 Replica로 보낼지
• 장애 서버를 어떻게 제외할지
• DB 연결 수를 어떻게 제어할지

이 문제를 해결하는 대표적인 솔루션이 ProxySQL 입니다.

ProxySQL은 DB 앞단에 위치하여 SQL을 분석하고, 상황에 따라 적절한 DB 서버로 전달하는 Layer 7 DB Proxy 입니다.

ProxySQL 역할

ProxySQL은 MySQL 앞단에 위치하는 DB Proxy로서 다음 역할을 수행합니다.

• Read / Write 분리 및 Query Routing: 트래픽을 분류하여 적절한 DB로 전달
• Connection Pooling: 연결 재사용으로 성능 최적화
• Query Cache: 동일한 쿼리의 응답을 캐싱하여 부하 감소
• Failover 대응 및 Load Balancing: 장애 발생 시 자동 제외 및 분산
• 통계 수집(Query Digest): 쿼리별 실행 횟수 및 시간 모니터링

 

ProxySQL 3계층 설정 구조

ProxySQL은 운영 안정성을 위해 Memory, Runtime, Disk로 나뉘는 3계층 구조를 가집니다.

설정을 변경해도 즉시 서비스에 반영되지 않도록 하여 실수를 방지하는 안전장치 역할을 합니다.

계층	설명
Memory	설정 수정 및 테스트를 수행하는 작업 공간입니다.
Runtime	메모리의 설정 중 실제 트래픽 처리에 적용된 상태입니다.
Disk	설정을 영구적으로 저장하는 SQLite 기반 공간으로, 재시작 시에도 설정을 유지합니다.

 

설정 반영 흐름

Memory (작업 및 테스트) ---LOAD--> Runtime (서비스 적용) ---SAVE--> Disk (영구 저장)

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

접속 포트 이해

6032 (Admin Port): ProxySQL 자체를 관리하는 포트 (유저, 호스트 그룹, 쿼리 규칙 등을 설정)

docker exec -it proxysql mysql -uadmin -padmin -P6032 --ssl=0

 

6033 (Application/User Port): 애플리케이션 및 사용자가 실제 DB 연결에 사용하는 포트 (내부적으로 MySQL 서버로 라우팅됨)

mysql -uroot -proot -h 127.0.0.1 -P6033

 

            [관리자]
               ↓
       Port 6032 (Admin)
           ProxySQL
               ↑
      Port 6033 (App/User)
               ↓
      ┌──────────────────┐
      │ Master / Replica │
      └──────────────────┘

 

Hostgroup 구성 및 설계

호스트 그룹(Hostgroup) 설계

트래픽의 성격에 따라 서버 그룹을 분리합니다.

Hostgroup	역할	대상 서버
10	Source (Write 전용)	mysql-master
20	Replica (Read 전용)	mysql-replica1, mysql-replica2

 

구조 요약

사용자는 ProxySQL에 접속하지만 실제 쿼리는 내부적으로 MySQL 서버로 전달됩니다.

ProxySQL 내부에서는 Query Rule에 따라 Source/Replica로 라우팅됩니다.

Application
     ↓
 ProxySQL
   ├─ HG10 → mysql-master
   └─ HG20 → mysql-replica1 / replica2

 

서버 등록

ProxySQL Admin(Port 6032)에 접속하여 Source, Replica 서버를 등록합니다.

docker exec -it proxysql mysql -uadmin -padmin -P6032 --ssl=0

-- Source 서버 등록 (HG 10), Replica 서버 등록 (HG 20)
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
(10, 'mysql-master', 3306),
(20, 'mysql-replica1', 3306),
(20, 'mysql-replica2', 3306);

-- 설정 반영 (Memory -> Runtime)
LOAD MYSQL SERVERS TO RUNTIME;

-- 설정 저장 (Memory -> Disk)
SAVE MYSQL SERVERS TO DISK;

 

등록된 서버 확인

SELECT * FROM mysql_servers;

+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mysql-master   | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica1 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica2 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)

 

사용자 등록

ProxySQL Admin(Port 6032)에 접속하여 사용자를 등록합니다.

애플리케이션(또는 테스트 도구)이 ProxySQL을 통해 DB에 접속할 때 사용할 계정이며, 실제 MySQL에 생성한 계정과 동일해야 합니다.

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('root', 'root', 10);

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

 

Query Rule 설정 (라우팅 규칙)

ProxySQL은 정규표현식(Regex)의 rule_id 순서대로 쿼리를 검사(Level 1 -> 5)하므로, 우선순위가 높은 규칙이 먼저 매칭되도록 설계해야 합니다.

단순한 SELECT 구문 외에도 정합성이 중요한 쿼리들은 반드시 Source로 라우팅되도록 설정해야 합니다.

• HG 10 (Source)
  • 트랜잭션 관련 쿼리: BEGIN, START TRANSACTION, COMMIT, ROLLBACK
  • 잠금 쿼리: FOR UPDATE, LOCK IN SHARE MODE (데이터 정합성이 중요하므로 Source로 라우팅)
  • 세션/함수 의존성 쿼리: LAST_INSERT_ID(), @변수 등 (복제 지연 및 세션 동기화 이슈 방지)
  • 시스템 관리 쿼리: SET, SHOW, DESCRIBE, EXPLAIN
  • 변경 및 DML/DDL: INSERT, UPDATE, DELETE 등
• HG 20 (Replica)
  • 위의 조건에 해당하지 않는 일반적인 읽기 전용 SELECT 쿼리만 분기합니다.

Query Rules 적용 및 저장

ProxySQL Admin(Port 6032)을 통해 아래의 쿼리를 실행하여 규칙을 설정합니다.

-- 필요 시 초기화 수행
-- DELETE FROM mysql_query_rules;

-- [LEVEL 1] 정합성 필수: 잠금 및 트랜잭션 (반드시 Master)
-- (?i)는 대소문자 무시, \s*는 앞공백 허용
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(10, 1, '(?i)^\s*SELECT.*FOR UPDATE', 10, 1),
(20, 1, '(?i)^\s*SELECT.*LOCK IN SHARE MODE', 10, 1),
(25, 1, '(?i)^\s*(BEGIN|START TRANSACTION|COMMIT|ROLLBACK)', 10, 1);

-- [LEVEL 2] 세션/함수/변수 의존성 (반드시 Source)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(30, 1, '(?i)(LAST_INSERT_ID|GET_LOCK|CONNECTION_ID|DATABASE)\s*\(', 10, 1),
(35, 1, '@[a-zA-Z0-9_]+', 10, 1);

-- [LEVEL 3] 시스템 및 관리 쿼리 (반드시 Source)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(50, 1, '(?i)^\s*(SET|SHOW|DESCRIBE|EXPLAIN)', 10, 1);

-- [LEVEL 4] 읽기 분산 (Replica로 보내는 유일한 관문)
-- 위에서 걸러지지 않은 모든 일반 SELECT만 20번 그룹으로 보냄
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(100, 1, '(?i)^\s*SELECT', 20, 1);

-- [LEVEL 5] Fallback (나머지 모든 쿼리: INSERT, UPDATE, DELETE, DDL 등)
-- "안전하게 Source로" 원칙에 따라 나머지는 모두 10번행
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(200, 1, '.*', 10, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

 

적용된 Query Rule 확인

설정이 올바르게 적용되었는지 확인하기 위해 아래 쿼리를 실행하여 조회합니다.

SELECT
  r.rule_id,
  r.match_pattern,
  r.destination_hostgroup,
  s.hostname
FROM mysql_query_rules r
LEFT JOIN mysql_servers s
  ON r.destination_hostgroup = s.hostgroup_id
ORDER BY r.rule_id;

+---------+-----------------------------------------------------------+-----------------------+----------------+
| rule_id | match_pattern                                             | destination_hostgroup | hostname       |
+---------+-----------------------------------------------------------+-----------------------+----------------+
| 10      | (?i)^\s*SELECT.*FOR UPDATE                                | 10                    | mysql-master   |
| 20      | (?i)^\s*SELECT.*LOCK IN SHARE MODE                        | 10                    | mysql-master   |
| 25      | (?i)^\s*(BEGIN|START TRANSACTION|COMMIT|ROLLBACK)         | 10                    | mysql-master   |
| 30      | (?i)(LAST_INSERT_ID|GET_LOCK|CONNECTION_ID|DATABASE)\s*\( | 10                    | mysql-master   |
| 35      | @[a-zA-Z0-9_]+                                            | 10                    | mysql-master   |
| 50      | (?i)^\s*(SET|SHOW|DESCRIBE|EXPLAIN)                       | 10                    | mysql-master   |
| 100     | (?i)^\s*SELECT                                            | 20                    | mysql-replica1 |
| 100     | (?i)^\s*SELECT                                            | 20                    | mysql-replica2 |
| 200     | .*                                                        | 10                    | mysql-master   |
+---------+-----------------------------------------------------------+-----------------------+----------------+
9 rows in set (0.001 sec)

 

Read / Write 분리 테스트

ProxySQL Application(Port 6033)에 접속하여 Write/ Read 작업이 올바르게 분리 되었는지 확인합니다.

INSERT 문은 데이터를 변경하는 쓰기(Write) 작업입니다.

쿼리 규칙(Query Rule)에 의해 이 쿼리가 Write 작업임을 감지하고, Source(Master, Hostgroup 10)로 라우팅합니다.

SELECT 문은 읽기(Read) 작업입니다.

ProxySQL은 이 쿼리를 읽기 작업으로 판단하여 Replica(Hostgroup 20)로 라우팅합니다.

mysql -uroot -proot -h 127.0.0.1 -P6033

mysql> USE testdb;
Database changed

mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

-- Write → Source
mysql> INSERT INTO t1 VALUES (2);
Query OK, 1 row affected (0.01 sec)

-- Read → Replica
mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

 

Source(mysql-master), Replica1(mysql-replica1), Replica2(mysql-replica2) 서버 각각에서 t1 테이블을 조회해 봅니다.

모든 서버에서 INSERT된 데이터(id = 2)가 동일하게 조회되어 데이터 동기화가 정상임을 최종적으로 확인할 수 있습니다.

mysql>  SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

 

Query Digest 통계 확인

ProxySQL Admin(Port 6032)에서 확인하면 INSERT는 HG10(Source)으로, SELECT는 HG20(Replica)으로 라우팅되었음을 확인할 수 있습니다.

docker exec -it proxysql mysql -uadmin -padmin -P6032 --ssl=0

MySQL [(none)]> SELECT hostgroup, schemaname, username, digest_text, count_star
FROM stats_mysql_query_digest
ORDER BY count_star DESC;
+-----------+--------------------+----------+--------------------------------------------------------------------------+------------+
| hostgroup | schemaname         | username | digest_text                                                              | count_star |
+-----------+--------------------+----------+--------------------------------------------------------------------------+------------+
...
| 20        | testdb             | root     | SELECT * FROM t1                                                         | 2          |
...
| 10        | testdb             | root     | INSERT INTO t1 VALUES (?)                                                | 1          |
...
+-----------+--------------------+----------+--------------------------------------------------------------------------+------------+

 

Query Cache 활용

ProxySQL 자체의 Query Cache 기능을 테스트합니다.

ProxySQL Admin(Port 6032) 접속 후 SELECT 쿼리에 대해 5초(5000ms) 캐싱을 설정합니다.

-- 일반 SELECT 결과 5초 캐싱
UPDATE mysql_query_rules
SET cache_ttl=5000
WHERE rule_id=100;

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

 

캐싱 효과 확인

ProxySQL Application(Port 6033) 접속 후 쿼리를 연속으로 실행해 봅니다.

mysql> SELECT * FROM t1 WHERE id < 5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM t1 WHERE id < 5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE id < 5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

 

캐싱 결과 확인

ProxySQL Admin(Port 6032) 접속하여 통계를 확인합니다.

첫 번째 쿼리만 실제 DB에 접근하며, 이후 동일한 쿼리는 ProxySQL이 캐시로 응답합니다.

SELECT hostgroup, digest_text, count_star, sum_time
FROM stats_mysql_query_digest
ORDER BY count_star DESC;

+-----------+---------------------------------------------------------------------------+------------+----------+
| hostgroup | digest_text                                                                                             | count_star | sum_time |
+-----------+---------------------------------------------------------------------------+------------+----------+
...
| -1        | SELECT * FROM t1 WHERE id < ?                                                                           | 2          | 0        |
...
| 20        | SELECT * FROM t1 WHERE id < ?                                                                           | 1          | 5013     |
...
+-----------+---------------------------------------------------------------------------+------------+----------+

 

Cache Stale 현상

TTL(유효 시간) 내에 INSERT가 발생해도 이전 캐시 데이터를 보여줄 수 있습니다. 5초 뒤 다시 수행하면 최신 데이터가 반영되는 것을 확인할 수 있습니다.

mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (3);
Query OK, 1 row affected (0.03 sec)

--5초 TTL 안이면 INSERT 후에도 이전 결과 보일 수 있음 이것이 Cache stale 데이터
mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

-- 5초 후 다시 수행 시 최신 데이터 반영 확인
mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

 

장애 (Failover) 대응 테스트

테스트 전 ProxySQL  Admin(Port 6032) 접속하여 서버 상태를 확인합니다.

MySQL [(none)]> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mysql-master   | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica1 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica2 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.002 sec)

 

 

Replica1 장애 테스트

1. Replica1 컨테이너 중지

docker stop mysql-replica1

2. ProxySQL 런타임 서버 상태 확인 (Admin Port 6032)

MySQL [(none)]> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mysql-master   | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica1 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica2 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.002 sec)

 

3. 동작 확인 (Appplication Port 6033)

mysql-replica1 장애 발생 후 mysql-replica1이 SHUNNED 처리되며 트래픽이 mysql-replica2로 자동 우회됩니다.

mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE id = 1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MySQL [(none)]> SELECT hostgroup, schemaname, username, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC;
+-----------+--------------------+----------+---------------------------------------------------------------------------------------------------------+------------+
| hostgroup | schemaname         | username | digest_text                                                                                             | count_star |
+-----------+--------------------+----------+---------------------------------------------------------------------------------------------------------+------------+
...
| 20        | testdb             | root     | SELECT * FROM t1 WHERE id = ?                                                                           | 1          |
...
+-----------+--------------------+----------+---------------------------------------------------------------------------------------------------------+------------+

 

Source 장애 테스트

1. Source 컨테이너 중지

docker stop mysql-master

 

2. ProxySQL 런타임 서버 상태 확인 (Admin Port 6032)

MySQL [(none)]> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mysql-master   | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica1 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-replica2 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.005 sec)

 

3. 동작 확인 (Appplication Port 6033)

• INSERT: Source에 의존하므로 에러 발생
• SELECT: Replica2를 통해 서비스 지속 가능

mysql> INSERT INTO t1 VALUES (4);
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    5
Current database: testdb

ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 10 after 10000ms
mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

 

4. Connection Pool 확인 (Admin Port 6032)

Source의 장애 시 ConnERR가 증가하며, 장애 복구 시 docker start  명령어를 통해 다시 컨테이너를 구동합니다.

MySQL [(none)]> SELECT * FROM stats_mysql_connection_pool;
+-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status  | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 10        | mysql-master   | 3306     | SHUNNED | 0        | 0        | 1      | 4       | 1           | 6       | 0                 | 117             | 57              | 0          |
| 20        | mysql-replica1 | 3306     | SHUNNED | 0        | 0        | 1      | 0       | 1           | 3       | 0                 | 61              | 36              | 0          |
| 20        | mysql-replica2 | 3306     | ONLINE  | 0        | 1        | 1      | 0       | 1           | 9       | 0                 | 247             | 108             | 0          |
+-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
3 rows in set (0.001 sec)

 

docker start mysql-master
docker start mysql-replica1