PostgreSQL의 pool을 관리하는 라이브러리
Connection Pool를 관리하여 postgresql의 부하를 줄여줄 수 있다.
PgBouncer는 PostgreSQL을 위한 **경량(lightweight) 커넥션 풀러(Connection Pooler)**입니다. 수많은 클라이언트(애플리케이션)로부터 오는 데이터베이스 연결 요청을 소수의 실제 데이터베이스 연결로 효율적으로 중계하여, PostgreSQL 서버의 부하를 줄이고 전체 시스템의 성능을 향상시키는 역할을 합니다.
쉽게 비유하자면, 인기 있는 클럽 입구에서 입장을 관리하는 **가드(Bouncer)**와 같습니다. 수백 명의 손님(애플리케이션 프로세스)이 한 번에 입장하려 하면 클럽 내부(DB 서버)가 혼잡해져 마비됩니다. 이때 가드(PgBouncer)가 입구에서 손님들을 잠시 대기시킨 뒤, 내부의 정해진 인원(실제 DB 연결)에 맞춰 순서대로 입장시키는 것과 같은 원리입니다.
## 목적: 왜 PgBouncer를 사용하는가?
PostgreSQL은 클라이언트가 연결을 요청할 때마다 새로운 프로세스를 fork()하여 생성합니다. 이 방식은 연결마다 독립적인 메모리 공간을 할당하여 안정적이지만, 연결 수가 많아지면 다음과 같은 문제가 발생합니다.
- 메모리 사용량 급증: 각 연결 프로세스가 수 MB의 메모리를 차지하여 서버 리소스가 빠르게 고갈됩니다.
- CPU 부하 증가: 수많은 프로세스를 관리하기 위한 컨텍스트 스위칭 비용이 커집니다.
- 연결 시간: 새로운 연결을 생성하고 인증하는 과정 자체에 시간이 소요됩니다.
PgBouncer는 이 문제를 해결하기 위해 미리 정해진 개수의 실제 DB 연결(Pool)을 만들어두고, 수많은 클라이언트의 요청을 이 풀 내에서 재사용합니다. 이로써 DB 서버는 소수의 연결만 관리하면 되므로 위에서 언급한 문제들이 해결됩니다.
## 핵심 기능
1. 커넥션 풀링 (Connection Pooling)
PgBouncer의 가장 핵심적인 기능으로, 세 가지 모드를 제공합니다.
- session (세션 풀링): 가장 일반적이고 안전한 기본값입니다. 클라이언트가 접속을 요청하면 풀에서 실제 DB 연결을 하나 할당해주고, 클라이언트가 접속을 끊을 때까지 그 연결을 유지합니다. 클라이언트가 접속을 끊으면 사용했던 DB 연결은 정리된 후 다시 풀에 반납됩니다.
- transaction (트랜잭션 풀링): 더 공격적인 풀링 방식입니다. 클라이언트가 트랜잭션(BEGIN ~ COMMIT/ROLLBACK)을 실행하는 동안에만 실제 DB 연결을 할당합니다. 트랜잭션이 끝나면 즉시 연결을 풀에 반납하므로 연결 효율이 매우 높습니다. 단, 세션 기반의 일부 기능(예: SET, Prepared Statement 등) 사용에 제약이 있습니다.
- statement (구문 풀링): 가장 공격적인 방식입니다. 모든 SQL 구문이 자동으로 트랜잭션으로 처리되며, 구문 하나가 실행될 때마다 연결을 할당하고 반납합니다. 다중 구문 트랜잭션이 불가능하므로 거의 사용되지 않습니다.
2. 투명성 (Transparency)
애플리케이션은 PgBouncer의 존재를 알 필요가 없습니다. 기존 코드에서 DB 접속 포트만 PgBouncer의 포트(기본 6432)로 변경하면 즉시 적용할 수 있습니다.
3. 관리자 콘솔
psql을 통해 PgBouncer의 가상 데이터베이스에 접속하여 SHOW POOLS;, SHOW STATS; 같은 명령어로 현재 풀의 상태, 통계 등을 실시간으로 모니터링할 수 있습니다.
## 장점 👍
- 성능 향상 및 자원 절약: DB 서버의 연결 부하를 획기적으로 줄여 CPU와 메모리 사용량을 크게 절약하고, 짧은 쿼리들의 응답 속도를 향상시킵니다.
- 데이터베이스 보호: 순간적으로 애플리케이션 연결이 폭주하더라도 PgBouncer가 중간에서 완충 역할을 하여 DB 서버가 다운되는 것을 막아줍니다.
- 투명한 운영: 애플리케이션 코드 수정이 거의 필요 없어 도입이 매우 간편합니다.
- 높은 안정성과 성숙도: 오랫동안 널리 사용되며 안정성이 검증된 도구입니다.
## 단점 👎
- 단일 실패 지점 (SPOF): PgBouncer 프로세스가 다운되면 DB로의 모든 연결이 끊어집니다. 이 때문에 중요한 시스템에서는 PgBouncer 자체를 이중화(HA)하여 구성하기도 합니다.
- 별도의 설치 및 관리: 애플리케이션과 별도로 설치, 설정, 모니터링해야 하는 대상이 하나 더 늘어납니다.
- 제한적인 기능: PgBouncer는 커넥션 풀링에만 집중합니다. 쿼리를 분석하여 다른 DB로 보내주는 읽기/쓰기 분리나 로드 밸런싱 기능은 제공하지 않습니다.
## 결론
PgBouncer는 수백~수천 개의 동시 연결이 필요한 웹 애플리케이션, 마이크로서비스 환경에서 PostgreSQL을 운영할 때 사실상 필수적인 구성 요소입니다. 간단한 설정만으로 데이터베이스의 확장성과 안정성을 크게 높일 수 있는 강력한 도구입니다.
PgBouncer 실행
PgBouncer 사용법은 크게 설치, 설정, 실행, 애플리케이션 연결 네 단계로 나뉩니다. PgBouncer는 애플리케이션과 실제 데이터베이스 사이에 위치하는 '중개인' 역할을 합니다.
애플리케이션 <--- (포트 6432) ---> PgBouncer <--- (포트 5432) ---> PostgreSQL
## 1단계: PgBouncer 설치 ⚙️
터미널에 다음 명령어를 입력하여 PgBouncer를 설치합니다.
sudo apt-get update
sudo apt-get install pgbouncer설치가 완료되면 설정 파일들은 보통 /etc/pgbouncer/ 디렉터리에 위치합니다.
## 2단계: PgBouncer 설정
가장 중요한 단계입니다. 두 개의 파일을 수정해야 합니다.
- pgbouncer.ini: 메인 설정 파일
- userlist.txt: 접속을 허용할 사용자 목록
pgbouncer.ini 파일 수정
sudo nano /etc/pgbouncer/pgbouncer.ini 명령으로 파일을 열고 아래 핵심 항목들을 수정합니다.
[databases]
# 이 부분에 '가상 데이터베이스 이름 = 실제 연결 정보'를 입력합니다.
# 애플리케이션은 'mydb'라는 이름으로 PgBouncer에 접속하게 됩니다.
mydb = host=127.0.0.1 port=5432 dbname=real_database_name
[pgbouncer]
# PgBouncer가 어느 주소와 포트에서 요청을 받을지 설정합니다.
listen_addr = *
listen_port = 6432
# 인증 방식 설정. 'md5'가 가장 일반적입니다.
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
# 로그 파일 위치
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
# 관리자 계정. psql -p 6432 pgbouncer 로 접속 시 사용됩니다.
admin_users = postgres
# 풀링 모드. 'session'이 가장 안전하고 일반적인 기본값입니다.
pool_mode = session
- [databases] 섹션이 핵심입니다. mydb는 애플리케이션이 접속할 때 사용할 별칭이며, 오른편에는 PgBouncer가 실제 PostgreSQL에 접속할 때 사용할 정보를 적습니다.
mydb -> 명칭은 임의로 하며, 보통 사용할 db_name 과 일치시킨다. - listen_port = 6432: 애플리케이션이 접속할 포트입니다.
- auth_file: 바로 다음에 설정할 사용자 인증 파일의 경로입니다.
- pool_mode: session (권장), transaction, statement 세 가지 모드가 있습니다. 특별한 이유가 없다면 session을 사용하세요.
userlist.txt 파일 수정
sudo nano /etc/pgbouncer/userlist.txt 명령으로 파일을 열고, 애플리케이션이 PgBouncer에 접속할 때 사용할 사용자 이름과 비밀번호를 추가합니다.
# "사용자이름" "비밀번호"
"myuser" "mypassword"
"anotheruser" "anotherpassword"
중요: 여기에 적는 사용자 정보는 실제 PostgreSQL 데이터베이스에도 존재해야 합니다. PgBouncer는 이 정보를 이용해 애플리케이션을 인증한 후, pgbouncer.ini에 설정된 정보로 실제 DB에 연결합니다.
## 3단계: PgBouncer 실행 🚀
설정을 마쳤으면 PgBouncer 서비스를 시작하고, 시스템 부팅 시 자동으로 실행되도록 설정합니다.
# PgBouncer 서비스 재시작하여 설정 적용
sudo systemctl restart pgbouncer
# 시스템 부팅 시 자동 실행 설정
sudo systemctl enable pgbouncer
# 서비스 상태 확인
sudo systemctl status pgbouncer
active (running) 메시지가 보이면 성공적으로 실행된 것입니다.
## 4단계: 애플리케이션 연결 수정 🐍
이제 마지막으로, 파이썬 애플리케이션의 데이터베이스 연결 정보만 수정하면 됩니다.
기존 코드 (SQLAlchemy 기준):
# 실제 DB에 바로 연결
DB_URL = "postgresql+psycopg2://myuser:mypassword@localhost:5432/real_database_name"
engine = create_engine(DB_URL)
수정된 코드:
# PgBouncer에 연결
# 호스트는 동일, 포트는 6432, 데이터베이스 이름은 pgbouncer.ini에서 정한 별칭('mydb')
DB_URL = "postgresql+psycopg2://myuser:mypassword@localhost:6432/mydb"
engine = create_engine(DB_URL)
이것으로 끝입니다. 애플리케이션을 실행하면 이제 모든 데이터베이스 연결은 PgBouncer를 통해 안전하고 효율적으로 관리됩니다. 각 프로세스가 create_engine을 호출하더라도 PgBouncer가 중간에서 연결을 재사용해주므로 DB 부하가 급격히 줄어듭니다.
## 핵심 팁 💡
- 방화벽 확인: 서버 방화벽에서 PgBouncer의 listen_port(기본 6432)가 열려있는지 확인하세요.
- 로그 확인: 문제가 발생하면 pgbouncer.ini에 설정된 logfile을 확인하는 것이 디버깅의 첫걸음입니다. (/var/log/pgbouncer/pgbouncer.log)
- 관리자 콘솔: psql -p 6432 -U postgres pgbouncer 명령으로 PgBouncer 관리자 콘솔에 접속하여 SHOW POOLS;, SHOW STATS; 같은 명령으로 현재 연결 상태를 모니터링할 수 있습니다.
. 여러 설정을 사용할 경우: 연결의 '목적'을 설명하는 이름 짓기
하나의 실제 데이터베이스에 여러 애플리케이션이 접속하거나, 용도에 따라 다른 풀(Pool) 설정을 적용하고 싶을 때 유용합니다.
- pgbouncer.ini 설정:
-
Ini, TOML
[databases] # 웹 서비스용 풀은 넉넉하게 설정 webapp_pool = host=127.0.0.1 dbname=production_db pool_size=50 # 데이터 분석용 배치 작업 풀은 적게 설정 batch_pool = host=127.0.0.1 dbname=production_db pool_size=5 - 애플리케이션 연결 코드:
-
Python
# 웹 서비스에서는 webapp_pool 별칭 사용 DB_URL_WEB = "postgresql+psycopg2://user:pass@localhost:6432/webapp_pool" # 배치 작업에서는 batch_pool 별칭 사용 DB_URL_BATCH = "postgresql+psycopg2://user:pass@localhost:6432/batch_pool" - 이유: 별칭만 봐도 어떤 용도의 연결인지 명확히 알 수 있고, 각 용도에 맞게 pool_size 같은 PgBouncer 설정을 다르게 적용할 수 있습니다.
3. 읽기/쓰기 분리 시: 역할 명시하기
데이터베이스를 읽기/쓰기용(Primary)과 읽기 전용(Replica)으로 분리해서 사용할 때, 별칭에 역할을 명시하는 것이 매우 중요합니다.
- pgbouncer.ini 설정:
-
Ini, TOML
[databases] # 쓰기/읽기 모두 가능한 Primary DB main_db_rw = host=10.0.0.1 dbname=main_db # 읽기만 가능한 Replica DB main_db_ro = host=10.0.0.2 dbname=main_db - 이유: 개발자가 실수로 읽기 전용 DB에 쓰기 작업을 시도하는 것을 방지할 수 있습니다. 코드에서 main_db_rw와 main_db_ro 중 어떤 연결을 사용해야 할지 명확하게 구분할 수 있습니다.
pool_size=50 를 pgbouncer에서 정해줄 때, 모든 프로세스들의 합 이상으로 해줘야돼? 어떤 기준으로 해줘야돼?
아니요, 절대 아닙니다. PgBouncer의 pool_size는 애플리케이션의 총 프로세스 수와는 다른 기준으로 정해야 합니다. 이 둘을 혼동하는 것이 가장 흔한 실수 중 하나입니다.
pool_size의 기준은 데이터베이스(DB) 서버가 동시에 처리할 수 있는 최적의 쿼리 수이며, 이는 주로 DB 서버의 CPU 코어 수에 의해 결정됩니다.
## pool_size 설정 기준
PgBouncer의 pool_size는 하나의 (사용자, 데이터베이스) 쌍에 대해 PgBouncer가 실제 DB 서버와 맺는 물리적인 연결의 최대 개수를 의미합니다.
1. 핵심 기준: DB 서버의 CPU 코어 수 🖥️
- 최적의 pool_size: (DB 서버의 CPU 코어 수 x 2) 에서 시작하는 것을 권장합니다.
- 예시: DB 서버가 8코어 CPU를 가지고 있다면, pool_size는 16 정도로 설정하고 성능을 테스트하는 것이 좋습니다.
- 이유: CPU 코어 수만큼의 쿼리가 동시에 실행될 때 DB 서버는 최고의 성능을 냅니다. 여기에 약간의 여유(I/O 대기 등)를 두어 곱하기 2를 합니다. 이 수치를 넘어가면 오히려 DB 서버에 과도한 컨텍스트 스위칭(Context Switching)이 발생하여 성능이 저하됩니다.
2. 왜 프로세스 총합보다 훨씬 작아야 하는가?
애플리케이션 프로세스는 대부분의 시간 동안 아무 일도 하지 않고 대기(idle) 상태에 있습니다. 500개의 프로세스가 동시에 실행되더라도, 실제로 DB에 쿼리를 날리는 순간은 아주 짧고 분산되어 있습니다.
- PgBouncer의 역할: 500개의 프로세스로부터 오는 "가끔씩의" 쿼리 요청을 받아서, 미리 만들어 둔 소수의 실제 DB 연결(예: 16개) 중 하나에 빠르게 할당해주고 결과를 돌려주는 것입니다.
- 만약 pool_size를 500으로 설정하면?: PgBouncer가 DB 서버에 500개의 실제 연결을 생성하려고 시도합니다. 이는 PgBouncer를 사용하지 않는 것과 마찬가지로 DB 서버를 다운시키는 원인이 됩니다.
## 실용적인 설정 가이드
- 시작점 정하기: DB 서버의 사양을 확인하고 아래 공식으로 시작점을 계산하세요.
- pool_size = (DB 서버 CPU 코어 수 x 2)
- default_pool_size 설정: pgbouncer.ini 파일에서 default_pool_size 값을 위에서 계산한 값으로 설정합니다.
- [databases] 섹션에서 각 데이터베이스별로 pool_size를 다르게 지정할 수도 있습니다.
-
Ini, TOML
[pgbouncer] ; ... 기타 설정 ... default_pool_size = 16 - 모니터링 및 튜닝: 애플리케이션에 부하를 주면서 PgBouncer 관리자 콘솔에 접속하여 상태를 확인합니다.
- 접속: psql -p 6432 -U pgbouncer pgbouncer
- 명령어: SHOW POOLS;
- 확인할 지표: cl_active (클라이언트 연결 수)가 sv_active (서버 연결 수)보다 훨씬 큰지 확인합니다. sv_active가 pool_size에 계속 근접해 있다면 pool_size를 조금씩 늘리는 것을 고려할 수 있습니다.
## 결론
PgBouncer의 pool_size는 애플리케이션 프로세스의 수가 아니라, DB 서버의 처리 능력에 맞춰야 합니다. 수백, 수천 개의 프로세스가 있더라도 pool_size는 보통 15에서 100 사이의 값을 갖는 경우가 대부분입니다. 이 점을 기억하는 것이 PgBouncer를 효과적으로 사용하는 핵심입니다.
그러면, pgbouncer를 통해 관리되고 있는 pool을 어떻게 확인해?
PgBouncer가 관리하는 커넥션 풀의 상태는 PgBouncer의 관리자 콘솔에 접속하여 확인할 수 있습니다. PostgreSQL의 기본 클라이언트인 psql을 사용하여 특별한 가상 데이터베이스 pgbouncer에 접속하면 됩니다.
## 1. 관리자 콘솔 접속하기
터미널에서 아래 명령어를 실행하여 PgBouncer 관리자 콘솔에 접속합니다.
- 포트(-p): pgbouncer.ini에 설정된 listen_port (기본값: 6432)
- 사용자(-U): pgbouncer.ini에 admin_users로 지정된 사용자 (예: postgres)
psql -p 6432 -U postgres pgbouncer
접속에 성공하면 pgbouncer=> 와 같은 프롬프트가 나타납니다.
## 2. 풀 상태 확인 명령어 실행하기
관리자 콘솔에서 아래와 같은 SHOW 명령어를 사용하여 풀의 상태를 실시간으로 확인할 수 있습니다.
가장 중요한 명령어: SHOW POOLS;
현재 설정된 모든 풀의 상세한 상태를 보여줍니다.
pgbouncer=> SHOW POOLS;
실행 결과 예시:
database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait | pool_mode
----------+------------+-----------+------------+-----------+---------+---------+---------+-----------
mydb | myuser | 10 | 0 | 5 | 3 | 0 | 0 | session
pgbouncer| pgbouncer | 1 | 0 | 1 | 0 | 0 | 0 | statement
(2 rows)
결과 해석 (핵심 지표) 💡
- database: pgbouncer.ini에 설정한 데이터베이스 별칭 (예: mydb)
- user: 해당 풀에 연결된 사용자
- cl_active: 현재 PgBouncer에 연결된 클라이언트(애플리케이션 프로세스)의 수입니다.
- cl_waiting: 서버 연결을 할당받지 못하고 대기 중인 클라이언트의 수입니다. 이 수치가 계속 0 이상이라면 pool_size가 작다는 신호일 수 있습니다.
- sv_active: 현재 PgBouncer가 실제 PostgreSQL 서버와 맺고 있는 활성 연결의 수입니다. 이 값은 pool_size를 초과할 수 없습니다.
- sv_idle: 실제 DB와 연결은 되어 있지만, 현재는 아무 클라이언트에게도 할당되지 않고 쉬고 있는 연결의 수입니다. 새로운 요청이 오면 이 연결이 즉시 사용됩니다.
- maxwait: 대기 중인 클라이언트 중 가장 오래 기다린 시간(초)입니다.
## 3. 기타 유용한 명령어
- SHOW STATS;
- 총 요청 수, 주고받은 데이터 양, 평균 쿼리 시간 등 통계 정보를 보여줍니다.
SQLpgbouncer=> SHOW STATS; - SHOW CLIENTS;
- 현재 연결된 모든 클라이언트의 상세 정보(IP 주소, 연결 시간 등)를 보여줍니다.
SQLpgbouncer=> SHOW CLIENTS; - SHOW SERVERS;
- PgBouncer가 실제 PostgreSQL 서버와 맺고 있는 연결의 상세 정보를 보여줍니다.
SQLpgbouncer=> SHOW SERVERS;
## 핵심 확인 사항
PgBouncer를 모니터링할 때 가장 중요하게 봐야 할 것은 SHOW POOLS; 명령어의 cl_waiting 값입니다. 이 값이 계속해서 증가한다면, 애플리케이션의 요청 속도를 PgBouncer의 풀이 감당하지 못하고 있다는 의미이므로 pool_size를 늘리는 것을 고려해야 합니다.
psql -p 6432 -U cgns pgbouncer
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.6432" failed: FATAL: not allowed
FATAL: not allowed 에러는 cgns 사용자가 PgBouncer의 관리자 콘솔에 접속할 권한이 없기 때문에 발생합니다.
이 문제를 해결하려면 pgbouncer.ini 설정 파일에 cgns 사용자를 관리자로 명시적으로 추가해야 합니다.
## 해결 방법
- pgbouncer.ini 파일 열기 터미널에서 아래 명령어로 PgBouncer 설정 파일을 엽니다.
-
Bash
sudo nano /etc/pgbouncer/pgbouncer.ini - admin_users 설정 수정 파일 내에서 [pgbouncer] 섹션을 찾아 admin_users 항목에 cgns를 추가합니다. 이미 다른 사용자가 있다면 쉼표(,)로 구분하여 추가합니다.
- stats_users라는 항목이 있다면, 여기에도 cgns를 추가해 주는 것이 좋습니다. 이 항목은 통계 정보만 볼 수 있는 읽기 전용 관리자 목록입니다.
[pgbouncer] ; ... 다른 설정들 ... admin_users = postgres, cgns
- stats_users라는 항목이 있다면, 여기에도 cgns를 추가해 주는 것이 좋습니다. 이 항목은 통계 정보만 볼 수 있는 읽기 전용 관리자 목록입니다.
- PgBouncer 재시작 설정 파일을 저장하고 나온 뒤, 아래 명령어로 PgBouncer 서비스를 재시작하여 변경사항을 적용합니다.
sudo systemctl restart pgbouncer
위 단계를 마친 후 다시 접속을 시도하면 정상적으로 관리자 콘솔에 접근할 수 있습니다.