ProxySQL

ProxySQL은 MySQL, MariaDB, Percona Server를 위한 고성능 오픈소스 데이터베이스 프록시입니다. 애플리케이션과 데이터베이스 서버 사이에 위치하여 쿼리 라우팅, 커넥션 풀링(Connection Multiplexing), 쿼리 캐싱, 페일오버 등의 기능을 제공합니다. C++로 작성되어 매우 빠르며, 런타임 중에 설정 변경이 가능한 것이 큰 특징입니다.

ProxySQL의 핵심 기능 중 하나는 지능형 쿼리 라우팅입니다. 쿼리 규칙(Query Rules)을 통해 SELECT 쿼리는 읽기 전용 복제본(Read Replica)으로, INSERT/UPDATE/DELETE는 마스터(Primary)로 자동 라우팅할 수 있습니다. 이를 통해 마스터의 부하를 줄이고 복제본을 효율적으로 활용할 수 있습니다. 또한 정규표현식으로 특정 쿼리 패턴을 매칭하여 다른 서버 그룹으로 보내거나, 쿼리를 재작성(Query Rewrite)할 수도 있습니다.

커넥션 멀티플렉싱은 ProxySQL의 또 다른 강점입니다. 애플리케이션에서 수천 개의 연결이 들어와도 ProxySQL은 소수의 연결만으로 백엔드 데이터베이스와 통신합니다. MySQL의 max_connections 제한을 우회하고 데이터베이스 서버의 메모리 사용량을 크게 줄일 수 있습니다. 특히 PHP, Node.js처럼 연결을 자주 생성/해제하는 환경에서 효과적입니다.

ProxySQL은 자동 페일오버와 헬스체크 기능도 내장하고 있습니다. 백엔드 서버의 상태를 주기적으로 모니터링하고, 장애가 발생하면 자동으로 해당 서버를 제외합니다. Galera Cluster나 Group Replication과 연동할 때는 클러스터 상태에 따라 동적으로 서버 그룹을 조정합니다. Admin 인터페이스를 통해 서비스 중단 없이 설정을 변경하고 새 서버를 추가할 수 있습니다.

ProxySQL 설치 및 초기 설정

# Ubuntu/Debian 설치
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list
apt-get update
apt-get install -y proxysql

# 서비스 시작
systemctl start proxysql
systemctl enable proxysql

# Admin 인터페이스 접속 (기본 포트 6032)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '

# Docker로 실행
docker run -d --name proxysql \
  -p 6033:6033 \
  -p 6032:6032 \
  -v /path/to/proxysql.cnf:/etc/proxysql.cnf \
  proxysql/proxysql

백엔드 MySQL 서버 등록

-- ProxySQL Admin 인터페이스에서 실행

-- 백엔드 서버 추가 (hostgroup 10: 쓰기, hostgroup 20: 읽기)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
  (10, 'mysql-primary.example.com', 3306, 100, 1000),      -- Primary (쓰기)
  (20, 'mysql-replica1.example.com', 3306, 100, 1000),     -- Replica 1
  (20, 'mysql-replica2.example.com', 3306, 100, 1000);     -- Replica 2

-- 모니터링 사용자 설정 (MySQL 서버에서 미리 생성 필요)
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

-- 애플리케이션 사용자 추가
INSERT INTO mysql_users (username, password, default_hostgroup, max_connections) VALUES
  ('app_user', 'app_password', 10, 500);

-- 설정 적용
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;

-- 영구 저장 (디스크에 저장)
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;

읽기/쓰기 분리 쿼리 규칙 설정

-- 쿼리 규칙: SELECT는 읽기 그룹(20)으로, 나머지는 쓰기 그룹(10)으로
DELETE FROM mysql_query_rules;

-- 규칙 1: SELECT ... FOR UPDATE는 쓰기 그룹으로 (잠금 필요)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- 규칙 2: SELECT ... LOCK IN SHARE MODE도 쓰기 그룹으로
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (110, 1, '^SELECT.*LOCK IN SHARE MODE', 10, 1);

-- 규칙 3: 일반 SELECT는 읽기 그룹으로
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (200, 1, '^SELECT', 20, 1);

-- 규칙 4: 트랜잭션 시작은 쓰기 그룹으로
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (300, 1, '^(BEGIN|START TRANSACTION)', 10, 1);

-- 규칙 5: 특정 테이블 쿼리는 특정 서버로 (예: 분석 쿼리)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (400, 1, '.*analytics_.*', 30, 1);  -- 분석 전용 서버 그룹

-- 설정 적용
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 쿼리 규칙 확인
SELECT rule_id, match_pattern, destination_hostgroup, hits FROM mysql_query_rules;

쿼리 캐싱 및 성능 최적화

-- 쿼리 캐시 활성화
UPDATE global_variables SET variable_value=1 WHERE variable_name='mysql-query_cache_size_MB';

-- 특정 쿼리를 캐시에 저장 (TTL 60초)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES (500, 1, '^SELECT.*FROM products WHERE category_id', 60000, 1);

-- 느린 쿼리 로깅 (500ms 이상)
UPDATE global_variables SET variable_value=500 WHERE variable_name='mysql-long_query_time';

-- 커넥션 풀 설정
UPDATE global_variables SET variable_value=100 WHERE variable_name='mysql-max_connections';
UPDATE global_variables SET variable_value=10 WHERE variable_name='mysql-free_connections_pct';

-- 쿼리 재작성 (예: 인덱스 힌트 추가)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply)
VALUES (600, 1,
  'SELECT \* FROM orders WHERE user_id',
  'SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id',
  1);

LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;

Python 애플리케이션 연동

import pymysql
from contextlib import contextmanager
from typing import Optional
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class ProxySQLConnection:
    """ProxySQL을 통한 MySQL 연결 관리"""

    def __init__(
        self,
        host: str = "proxysql.example.com",
        port: int = 6033,  # ProxySQL 클라이언트 포트
        user: str = "app_user",
        password: str = "app_password",
        database: str = "myapp"
    ):
        self.config = {
            "host": host,
            "port": port,
            "user": user,
            "password": password,
            "database": database,
            "charset": "utf8mb4",
            "cursorclass": pymysql.cursors.DictCursor,
            "autocommit": True,
        }

    @contextmanager
    def get_connection(self, read_only: bool = False):
        """
        컨텍스트 매니저로 연결 관리
        read_only=True면 ProxySQL이 자동으로 읽기 복제본으로 라우팅
        """
        conn = pymysql.connect(**self.config)
        try:
            if read_only:
                # 읽기 전용 힌트 (ProxySQL 쿼리 규칙으로 라우팅)
                with conn.cursor() as cursor:
                    cursor.execute("SET SESSION TRANSACTION READ ONLY")
            yield conn
        finally:
            conn.close()

    def execute_read(self, query: str, params: tuple = None) -> list:
        """읽기 쿼리 실행 (Replica로 라우팅)"""
        with self.get_connection(read_only=True) as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, params)
                result = cursor.fetchall()
                logger.info(f"Read query executed: {query[:50]}...")
                return result

    def execute_write(self, query: str, params: tuple = None) -> int:
        """쓰기 쿼리 실행 (Primary로 라우팅)"""
        with self.get_connection(read_only=False) as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, params)
                conn.commit()
                logger.info(f"Write query executed: {query[:50]}...")
                return cursor.lastrowid

    def execute_transaction(self, queries: list[tuple[str, tuple]]) -> bool:
        """트랜잭션으로 여러 쿼리 실행 (Primary로 라우팅)"""
        with self.get_connection(read_only=False) as conn:
            conn.autocommit = False
            try:
                with conn.cursor() as cursor:
                    for query, params in queries:
                        cursor.execute(query, params)
                conn.commit()
                logger.info(f"Transaction completed: {len(queries)} queries")
                return True
            except Exception as e:
                conn.rollback()
                logger.error(f"Transaction failed: {e}")
                raise


# 사용 예시
if __name__ == "__main__":
    db = ProxySQLConnection()

    # 읽기 쿼리 - ProxySQL이 Replica로 라우팅
    users = db.execute_read(
        "SELECT id, name, email FROM users WHERE status = %s LIMIT 10",
        ("active",)
    )
    print(f"Found {len(users)} users")

    # 쓰기 쿼리 - ProxySQL이 Primary로 라우팅
    new_user_id = db.execute_write(
        "INSERT INTO users (name, email, status) VALUES (%s, %s, %s)",
        ("New User", "new@example.com", "active")
    )
    print(f"Created user with ID: {new_user_id}")

    # 트랜잭션 - Primary로 라우팅
    db.execute_transaction([
        ("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1)),
        ("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2)),
        ("INSERT INTO transactions (from_id, to_id, amount) VALUES (%s, %s, %s)", (1, 2, 100)),
    ])

모니터링 및 통계 조회

-- ProxySQL Admin에서 실행

-- 서버 상태 확인
SELECT hostgroup_id, hostname, port, status, weight,
       Queries, Bytes_data_sent, Bytes_data_recv
FROM stats_mysql_connection_pool;

-- 쿼리 규칙 히트 수 확인
SELECT rule_id, match_pattern, hits, destination_hostgroup
FROM mysql_query_rules
WHERE hits > 0
ORDER BY hits DESC;

-- 사용자별 쿼리 통계
SELECT username, frontend_connections, queries
FROM stats_mysql_users;

-- 느린 쿼리 분석
SELECT hostgroup, schemaname, username, digest_text,
       count_star, sum_time, min_time, max_time
FROM stats_mysql_query_digest
WHERE sum_time > 1000000  -- 1초 이상 총 실행시간
ORDER BY sum_time DESC
LIMIT 20;

-- 커넥션 풀 상태
SELECT * FROM stats_mysql_global;

-- 실시간 처리량
SELECT * FROM stats_mysql_commands_counters;