고트래픽 애플리케이션을 위한 PgBouncer로 PostgreSQL 연결 풀링 구성하기
PostgreSQL에 대한 PgBouncer 연결 풀링을 구성하여 수천 개의 동시 연결을 처리하고, 리소스 오버헤드를 줄이며, 애플리케이션 성능을 극적으로 향상시키는 방법을 알아보세요.
PgBouncer를 사용하여 고트래픽 애플리케이션을 위한 PostgreSQL 연결 풀링 구성
소개
PostgreSQL 데이터베이스가 높은 연결 볼륨을 처리할 때, 성능이 빠르게 저하될 수 있습니다. 각 데이터베이스 연결은 시스템 자원을 소비하며, PostgreSQL은 동시 연결에 대한 실제 제한이 있습니다. PgBouncer는 경량 연결 풀러로, 데이터베이스 연결 풀을 유지하고 클라이언트 애플리케이션에 효율적으로 분배함으로써 이 문제를 해결합니다.
연결 풀링의 중요성
연결 문제
- 리소스 오버헤드: 각 PostgreSQL 백엔드 프로세스는 5-10MB의 메모리를 소비합니다
- 연결 제한: 기본 max_connections는 일반적으로 100-200입니다
- 시작 비용: 새로운 연결 생성은 각각 1-5ms가 소요됩니다
- 컨텍스트 스위칭: 너무 많은 프로세스는 CPU 스래싱을 유발합니다
PgBouncer의 이점
- 데이터베이스 연결 수를 10-100배 줄입니다
- 최소한의 오버헤드로 수천 개의 클라이언트 연결을 가능하게 합니다
- 피크 로드 동안 연결 대기열을 제공합니다
- 다양한 사용 사례를 위한 여러 풀링 모드를 지원합니다
설치 및 기본 설정
PgBouncer 설치
Ubuntu/Debian에서:
sudo apt update
sudo apt install pgbouncer
CentOS/RHEL에서:
sudo yum install pgbouncer
macOS에서:
brew install pgbouncer
디렉토리 구조
/etc/pgbouncer/
├── pgbouncer.ini # 주요 구성
└── userlist.txt # 인증 자격 증명
구성 파일 설정
기본 pgbouncer.ini 구성
[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db
[pgbouncer]
; 연결 풀링 모드
pool_mode = transaction
; 최대 연결
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
; 네트워킹
listen_addr = 0.0.0.0
listen_port = 6432
; 인증
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; 로깅
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
; 성능
max_prepared_statements = 0
풀링 모드 이해
1. 세션 풀링 (pool_mode = session)
- 동작: 클라이언트에게 전체 세션 동안 연결 할당
- 사용 사례: 임시 테이블, 준비된 문장을 사용하는 애플리케이션
- 효율성: 낮음 (1:1 연결 비율)
pool_mode = session
2. 트랜잭션 풀링 (pool_mode = transaction) - 권장
- 동작: 각 트랜잭션 후 연결을 풀에 반환
- 사용 사례: 짧은 트랜잭션을 사용하는 대부분의 웹 애플리케이션
- 효율성: 높음 (10-100배 감소)
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000
3. 문장 풀링 (pool_mode = statement)
- 동작: 각 문장 후 연결 반환
- 사용 사례: 트랜잭션 없이 간단한 읽기 전용 쿼리
- 효율성: 최대 (하지만 매우 제한적)
pool_mode = statement
; 주의해서 사용 - 다중 문장 트랜잭션을 깨뜨림
인증 설정
userlist.txt 생성
PgBouncer는 별도의 인증 파일을 필요로 합니다. MD5 해시를 생성하고 userlist.txt에 추가하세요.
예제 userlist.txt:
"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"
PostgreSQL auth_query 사용 (고급)
인증을 위해 PostgreSQL을 직접 쿼리합니다:
auth_type = md5
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
고트래픽을 위한 최적 구성
연결 풀 크기 조절
풀 크기 조절 공식:
default_pool_size = (num_cores × 2) + effective_spindle_count
4코어 서버에 SSD의 경우:
default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000
완전한 프로덕션 구성
[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15
[pgbouncer]
pool_mode = transaction
; 연결 제한
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600
; 네트워킹
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192
; 보안
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options
; 로깅
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
; 성능
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120
애플리케이션 연결 문자열
PgBouncer 전
# 직접 PostgreSQL 연결
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"
PgBouncer 후
# PgBouncer를 통해 연결
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"
모니터링 및 관리
관리 콘솔 명령
PgBouncer 관리 콘솔에 연결:
psql -h localhost -p 6432 -U pgbouncer pgbouncer
필수 명령:
-- 풀 통계 표시
SHOW POOLS;
-- 활성 연결 표시
SHOW CLIENTS;
SHOW SERVERS;
-- 구성 표시
SHOW CONFIG;
-- 구성 다시 로드
RELOAD;
일반적인 문제 해결
문제 1: "더 이상 연결이 허용되지 않음"
해결 방법:
max_client_conn = 5000
default_pool_size = 50
문제 2: 높은 cl_waiting 수
해결 방법:
- 풀 크기 증가
- 느린 쿼리 최적화
- 예비 풀 추가
문제 3: 준비된 문장 오류
해결 방법:
max_prepared_statements = 0
성능 영향 예시
PgBouncer 전
- 500개의 동시 요청 → 500개의 PostgreSQL 연결
- 데이터베이스 로드: 95% CPU, 8GB RAM
- 응답 시간: 평균 250ms
PgBouncer 후
- 500개의 동시 요청 → 25개의 PostgreSQL 연결
- 데이터베이스 로드: 35% CPU, 1GB RAM
- 응답 시간: 평균 80ms
- 결과: 3배 빠름, 70% 적은 리소스 사용
결론
PgBouncer는 PostgreSQL 애플리케이션을 확장하는 데 필수적입니다. 연결 오버헤드를 90% 이상 줄이고, 10-100배 더 많은 클라이언트를 지원하며, 응답 시간을 크게 개선합니다. 트랜잭션 풀링 모드로 시작하고 모니터링을 기반으로 조정하세요.