11. [최적화8] PostgreSQL 최적화를 통한 WebSocket 서버 성능 향상

1. 왜 PgBouncer를 도입했는가?

문제점

t2.micro (1vCPU, 1GB RAM) 환경에서 PostgreSQL 직접 연결 방식은 다음과 같은 문제가 발생

1. 커넥션 오버헤드: 애플리케이션의 동시 연결 수 증가 시 PostgreSQL이 부하를 견디기 어려움
2. 메모리 부족: 커넥션 유지 시 메모리 소비 증가 → OOM(Out of Memory) 발생 가능성 증가
3. 트랜잭션 지연: WebSocket 기반의 실시간 애플리케이션에서 다수의 클라이언트 요청을 처리할 때 연결 지연 및 타임아웃 발생

2. PgBouncer vs. Pgpool-II 비교

PostgreSQL 성능 최적화를 위해 일반적으로 PgBouncer 또는 Pgpool-II를 선택

기능 비교	PgBouncer	Pgpool-II
주요 기능	단순 커넥션 풀링 (Connection Pooling)	커넥션 풀링 + 로드 밸런싱 + 리플리케이션 관리
트랜잭션 단위 연결	✅ 가능	✅ 가능
쿼리 캐싱	❌ 불가능	✅ 가능
로드 밸런싱	❌ 없음	✅ 있음
설정 복잡성	간단	복잡
리소스 사용량	가벼움 (Low Overhead)	무거움 (High Overhead)

선택 이유 → PgBouncer

1. 단순한 커넥션 풀링만 필요하기 때문에 PgBouncer가 더 적합
2. t2.micro는 리소스가 제한적이므로, 더 가벼운 PgBouncer 사용
3. 웹 애플리케이션(WebSocket 기반)이라 빠른 트랜잭션 처리가 중요 → pgbouncer의 transaction 모드가 유리

3. PgBouncer 설정 및 최적화

설치 및 설정

sudo apt install pgbouncer -y  # PgBouncer 설치
sudo nano /etc/pgbouncer/pgbouncer.ini  # 설정 파일 수정

적용 전후 성능 비교

PgBouncer를 도입하여 부하 테스트(Artillery)를 진행한 결과

항목	PgBouncer 적용 전	PgBouncer 적용 후	변화
총 소요 시간	3분 11초	3분 7초	4초 단축
WebSocket 에러	36건	12건	감소 (-24건)
응답 시간 (최대)	35.9ms	54.1ms	증가 (+18.2ms)
응답 시간 (평균)	0.5ms	0.4ms	개선 (-0.1ms)
완료된 유저 수	834명	855명	증가 (+21명)
실패한 유저 수	96명	75명	감소 (-21명)
- WebSocket 에러 감소 및 성공한 요청 증가 → 트랜잭션 처리 속도 개선
- 최대 응답 시간이 증가했지만 평균 응답 시간은 개선

PgBouncer 설정 파일 (pgbouncer.ini)

[databases]
chatpostgres = host=10.0.9.28 port=5432 dbname=chatpostgres user=chatadmin password=1234

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 10
reserve_pool_size = 5
server_idle_timeout = 60
server_lifetime = 300
server_reset_query = DISCARD ALL

PgBouncer 실행 및 PostgreSQL과 연동

sudo systemctl restart pgbouncer

주요 설정 값과 선택 이유

1. PostgreSQL 메모리 최적화 (RAM 1GB 기준)

shared_buffers = 128MB

• 기본값 32MB → 128MB로 증가
• PostgreSQL에서 데이터 캐싱을 담당하는 기본 메모리 버퍼 크기
• RAM의 12~15% 수준으로 설정하는 것이 권장됨
• 이유
  • 너무 낮으면 디스크 I/O 증가 → 성능 저하
  • 너무 높으면 시스템의 다른 프로세스가 사용할 메모리가 부족해짐
  • 1GB RAM 기준 128MB가 적절한 값

work_mem = 4MB

• 기본값 1MB → 4MB로 증가
• 쿼리당 사용 가능한 메모리 (JOIN, ORDER BY, DISTINCT, GROUP BY 시 활용)
• 이유
  • 작은 값: 디스크 스와핑 발생 → 쿼리 속도 저하
  • 너무 큰 값: 다중 쿼리 실행 시 메모리 부족
  • 보통 1GB RAM 환경에서는 2~8MB가 적절 → 4MB 선택

maintenance_work_mem = 32MB

• 기본값 16MB → 32MB로 증가
• VACUUM, CREATE INDEX 작업 시 사용되는 메모리
• 이유
  • 기본값이 너무 낮으면 인덱스 생성 속도가 느려짐
  • 너무 높으면 다른 프로세스에 영향
  • 소규모 DB에서는 32MB~64MB가 적절 → 32MB 선택

---

2. 커넥션 최적화 (PgBouncer 기반)

max_connections = 20

• 기본값 100 → 20으로 줄임
• 이유
  • PostgreSQL은 커넥션이 많아질수록 오버헤드 증가
  • PgBouncer를 사용해 커넥션 풀링을 적용할 예정 → 불필요하게 높은 max_connections을 줄여 리소스 절약
  • 20~30개가 일반적인 t2.micro 수준의 적정값

effective_cache_size = 512MB

• PostgreSQL의 실행 계획 최적화 참고용 설정
• 이유
  • 이 값은 PostgreSQL이 사용 가능한 OS 레벨의 캐시 크기를 가정하는 값
  • 실제 캐시를 할당하지 않지만, 쿼리 플래너가 캐싱 가능한 데이터 양을 판단하는 기준
  • RAM의 50% 수준이 일반적이므로 512MB로 설정

---

3. WAL(Write-Ahead Logging) 튜닝 (쓰기 부하 최적화)

wal_level = minimal

• 기본값 replica → minimal
• 이유
  • 복제 및 PITR(Point-In-Time Recovery)을 사용하지 않는 경우
  • minimal 모드는 로그 크기를 줄이고 I/O 부하 감소
  • 주의: WAL 로그가 적어 복구 기능이 제한될 수 있음 (백업 활용 필요)

synchronous_commit = off

• 기본값 on → off
• 이유
  • 쓰기 성능 향상
  • off로 설정하면 트랜잭션이 완료되기 전에 WAL이 디스크에 기록되기 전에도 클라이언트에 응답 → 쓰기 성능 증가
  • 데이터 손실 위험 존재, 하지만 트랜잭션이 적은 서비스에서는 유용

checkpoint_timeout = 10min

• 기본값 5min → 10min
• 이유
  • 체크포인트 빈도를 줄여 I/O 부하 완화
  • 너무 길면 복구 시간이 증가할 수 있음 → 적절한 균형 필요
  • 일반적으로 10~15분이 적절한 값

---

4. 자동 Vacuum 최적화 (데이터 변경이 적은 환경)

autovacuum = on

• 자동 Vacuum을 활성화하여 불필요한 디스크 사용량 증가 방지

autovacuum_naptime = 30s

• 기본값 1min → 30s
• 이유
  • 자주 실행하여 테이블 부하를 줄이는 목적
  • 테이블 크기가 작을 때 더 효과적

autovacuum_vacuum_cost_limit = 2000

• 기본값 200 → 2000으로 증가
• 이유
  • Vacuum 실행 시 한 번에 처리할 수 있는 데이터 크기 증가
  • 기본값이 너무 낮아 불필요한 오버헤드 발생 가능

---

5. PgBouncer 최적화

listen_addr = 0.0.0.0

• 외부에서 PgBouncer 접근을 허용하기 위해 설정

auth_type = md5 & auth_file = /etc/pgbouncer/userlist.txt

• PostgreSQL과의 보안 강화를 위해 md5 인증 사용

pool_mode = transaction

• 이유
  • session 모드는 클라이언트가 연결을 오래 유지해 비효율적
  • transaction 모드는 트랜잭션 단위로 연결을 관리 → 효율적인 연결 재사용 가능

max_client_conn = 100

• 최대 클라이언트 연결 수
• max_connections(PostgreSQL 기본값 20)보다 높게 설정하여 PgBouncer가 더 많은 클라이언트를 처리 가능

default_pool_size = 10

• 기본 커넥션 풀 크기
• max_connections을 초과하지 않도록 10개로 제한

reserve_pool_size = 5

• 예비 커넥션 풀 크기
• 커넥션이 부족할 경우 대비

server_idle_timeout = 60

• 유휴 연결을 60초 유지 후 해제
• 장시간 사용되지 않는 커넥션을 정리하여 리소스 절약

server_lifetime = 300

• 서버 연결을 5분(300초) 유지 후 새로운 연결로 교체
• 장기적인 커넥션 누적을 방지

server_reset_query = DISCARD ALL

• 연결 재사용 시 세션 정리
• 기존 세션 설정값을 초기화하여 문제 예방

---

t2.micro 환경에 맞춘 최적화 포인트

• 메모리 최적화: shared_buffers, work_mem, maintenance_work_mem 조정
• 연결 최적화: max_connections = 20 & PgBouncer 적용
• 쓰기 부하 완화: wal_level = minimal, synchronous_commit = off
• 자동 Vacuum 개선: autovacuum_naptime, autovacuum_vacuum_cost_limit 조정

이 설정을 적용하면

• 불필요한 디스크 I/O 감소
• 메모리 부족 현상 완화
• PostgreSQL 성능 최적화
• 트랜잭션 처리량 증가

---

4. scram-sha-256 → md5 변경 이유

PostgreSQL 10부터 기본 인증 방식이 scram-sha-256으로 변경되었지만, PgBouncer는 scram-sha-256을 직접 지원하지 않음

해결 방법

1. PostgreSQL pg_hba.conf에서 scram-sha-256을 md5로 변경

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    host    all             all             0.0.0.0/0               md5

2. 사용자 비밀번호를 md5 형식으로 변경:결과값을 /etc/pgbouncer/userlist.txt에 추가:

    echo -n "yourpassword" | md5sum | awk '{print $1}'

    "chatadmin" "md5비밀번호해시값"
   

5. PostgreSQL 최적화

PgBouncer 적용과 함께 PostgreSQL 설정도 최적화하여 성능을 더욱 개선

최적화된 PostgreSQL 설정 (postgresql.conf)

# 1. 메모리 사용 최적화
shared_buffers = 128MB          # RAM 1GB 환경에 적합
work_mem = 4MB                  # 쿼리당 메모리 사용량 제한
maintenance_work_mem = 32MB      # 인덱스 생성 시 사용 메모리

# 2. 커넥션 최적화
max_connections = 20             # PgBouncer를 사용하므로 20으로 제한
effective_cache_size = 512MB      # 캐시 크기 조정

# 3. WAL(쓰기 성능 향상)
wal_level = minimal               # 로그 크기 감소
synchronous_commit = off          # 성능 향상
checkpoint_timeout = 10min        # 체크포인트 빈도 조정

# 4. 자동 Vacuum 최적화
autovacuum = on
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 2000

설정 변경 이유

1. 메모리 최적화 → RAM 1GB 환경에 맞춰 shared_buffers, work_mem 조정
2. WAL 설정 조정 → wal_level = minimal을 통해 로그 크기 감소로 쓰기 성능 향상
3. Vacuum 최적화 → autovacuum_naptime = 30s 설정하여 데이터 정리 작업을 자주 수행

결론

• PgBouncer 도입 후 성능이 향상
• WebSocket 에러 감소, 트랜잭션 처리 속도 증가
• PostgreSQL 설정 최적화를 통해 더욱 효과적인 리소스 활용 가능
• scram-sha-256 대신 md5를 선택하여 PgBouncer와의 호환성 해결

추후 추가 작업

1. pg_stat_statements 확장을 활성화하여 쿼리 성능 분석
2. EXPLAIN ANALYZE를 활용하여 느린 쿼리 최적화
3. 필요 시 t2.small 이상 업그레이드 고려