PgBouncer transaction 모드에서 prepared statement 제대로 쓰는 법
왜 굳이 켜야 하나
PgBouncer 1.21에서 transaction pooling 모드에서도 prepared statement를 쓸 수 있게 된 게 벌써 2년 가까이 됐다. 우리 팀도 작년 가을에 1.22로 올리고 max_prepared_statements를 켰는데, 그동안 마주친 함정 몇 개가 있어서 정리해둔다. 비슷한 마이그레이션을 앞두고 있는 분들에게 도움이 됐으면 한다.
이 글은 "PgBouncer는 뭔가요"부터 시작하지 않는다. 이미 transaction 모드로 PgBouncer를 운영 중이고, 애플리케이션이 prepared statement를 쓰고 있거나 쓰고 싶은 상황을 가정한다.
JDBC, asyncpg, pgx, psycopg3 같은 모던 드라이버는 기본적으로 Parse/Bind/Execute로 분리된 extended query protocol을 쓴다. 매번 SQL 텍스트를 파싱하지 않고 plan을 재사용해서 빠르고, SQL injection 위험도 줄어든다. 그런데 transaction pooling을 쓰면 매 트랜잭션마다 다른 backend 커넥션에 매핑되니까, 한 커넥션에 prepare 해놓은 statement가 다음 트랜잭션에선 사라진다. 그래서 옛날엔 두 가지 중 하나를 선택해야 했다. session pooling을 쓰거나, prepared statement를 끄거나.
session pooling은 backend 커넥션을 트랜잭션 단위가 아니라 클라이언트 세션 단위로 잡는다. 풀이 안 도는 워크로드에선 그냥 PgBouncer를 안 쓰는 거랑 별 차이가 없어진다. 그래서 우리는 transaction 모드를 유지하면서 prepared statement를 살리는 쪽으로 가고 싶었다.
PgBouncer 1.21부터 추가된 max_prepared_statements 설정이 그 답이다. PgBouncer가 클라이언트의 Parse 메시지를 가로채서 자기가 캐시를 들고, 실제 backend 커넥션에 그 statement가 아직 prepare 안 돼있으면 자동으로 prepare 시킨다. 클라이언트는 transaction pooling을 쓰는지 알 필요가 없다.
설정 자체는 간단하다
pgbouncer.ini에 한 줄 추가하면 끝이다.
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200max_prepared_statements는 클라이언트 커넥션 하나당 PgBouncer가 추적할 prepared statement 개수의 상한이다. 0이 기본값이고, 0이면 기능이 꺼져있다. 우리 팀에서는 200으로 시작했다가 트래픽 패턴 보고 500까지 올렸다.
설정값을 정할 때 헷갈리지 말아야 할 게 두 가지 있다.
첫째, 이 값은 PgBouncer의 메모리에 들어가는 캐시 엔트리 수다. backend 커넥션 풀 크기랑은 다르다. backend는 그것대로 default_pool_size로 잡는다.
둘째, 이 값을 너무 작게 잡으면 LRU 방식으로 캐시가 밀려나면서 hit rate가 떨어진다. 애플리케이션이 100개의 unique한 query를 prepare 한다면 200~300이 적절하다. 우리 팀은 ORM이 자동 생성하는 statement까지 합치니까 300개를 넘어서 500으로 올렸다.
함정 1: 드라이버가 명시적 prepare를 안 쓰는 경우
이게 제일 첫 번째로 부딪힌 문제였다. JDBC는 prepareThreshold 설정으로 같은 SQL이 N번 실행되면 그제야 protocol-level prepared statement를 만든다. 기본값이 5다. 즉 처음 4번은 그냥 simple query고, 5번째부터 prepared가 된다.
문제는 PgBouncer 입장에선 명시적인 Parse 메시지가 와야 캐시를 만든다는 거다. 애플리케이션이 매번 simple query로 보내면 PgBouncer는 아무것도 안 캐시한다. 그래서 prepareThreshold를 1로 내리거나, 애초에 PreparedStatement를 직접 만드는 코드 패턴으로 가야 효과를 본다.
우리 팀은 JDBC URL에 이렇게 박았다.
jdbc:postgresql://pgbouncer:6432/app?prepareThreshold=1&preparedStatementCacheQueries=512preparedStatementCacheQueries는 JDBC 클라이언트 쪽 캐시 크기다. PgBouncer의 max_prepared_statements보다 같거나 작게 맞추면 양쪽이 잘 동기화된다. 더 크게 잡으면 클라이언트는 캐시했다고 생각하는데 PgBouncer쪽은 LRU로 밀려나서 매번 다시 prepare 하는 상황이 벌어진다.
함정 2: SET 같은 세션 상태 변경
prepared statement는 살렸지만 SET timezone = 'Asia/Seoul' 같은 세션 변수는 여전히 transaction 모드에선 위험하다. 트랜잭션 밖에서 SET을 때리면 그 backend 커넥션에만 적용되고, 다음 트랜잭션은 다른 커넥션에 가니까 timezone이 또 다르다.
해결책은 두 가지다. 트랜잭션 안에서 SET LOCAL을 쓰거나, 그냥 매 connection 시작 시 PgBouncer가 자동 실행해주는 server_reset_query에 박아놓거나. 우리는 애플리케이션 connection string의 options 파라미터로 -c timezone=Asia/Seoul처럼 startup 시 한 번에 처리하게 바꿨다.
LISTEN/NOTIFY도 transaction 모드에선 동작하지 않는다. 이건 prepared statement랑은 별개 이슈인데, 같이 마이그레이션하다 보면 한 번씩 걸린다. 알림 받는 워크로드는 별도 PgBouncer 인스턴스를 session 모드로 띄우거나, Postgres에 직접 붙는 것 외엔 답이 없다.
함정 3: 모니터링 지표가 새로 필요하다
전에는 backend 커넥션 수랑 wait time만 봤는데, prepared statement를 켜고 나서는 hit/miss 비율을 봐야 한다. 안 그러면 max_prepared_statements가 너무 작아서 LRU에 계속 밀리는 상황을 못 잡는다.
PgBouncer 1.22부터 SHOW STATS 출력에 prepared statement 관련 컬럼이 추가됐다.
-- pgbouncer 콘솔 (보통 :6432 / pgbouncer DB)
SHOW STATS;server_assignment_count랑 같이 보면서 prepared statement cache hit이 95% 이상으로 유지되는지 확인한다. 우리는 prometheus-pgbouncer-exporter에 이 지표를 추가하는 PR을 직접 올려서 쓰고 있다. 떨어지기 시작하면 max값을 올리거나 클라이언트 쪽 cache size를 줄인다.
단계적으로 켜는 걸 권한다
운영 중인 PgBouncer에서 갑자기 max_prepared_statements를 0에서 500으로 올리면, 즉시 모든 새 prepared statement가 PgBouncer 메모리를 먹기 시작한다. 메모리 사용량은 statement 텍스트 길이에 비례하는데, 우리 환경에선 statement 하나당 평균 2~3KB 정도 잡혔다.
또 한 가지, 라이브로 켜면 기존에 떠있던 backend 커넥션은 prepare를 다시 받지 않는다. 캐시는 새로 들어오는 트랜잭션부터 채워진다. 그래서 처음엔 cache hit이 0%에 가깝다가 몇 분에 걸쳐 올라가는 모양이 된다. 이걸 모르고 보면 "어 왜 안 빨라지지" 한다.
우리는 이렇게 했다.
- 스테이지에서 일주일 운영하면서 hit rate랑 메모리 사용량 패턴을 본다
- 운영에선 트래픽이 가장 적은 새벽 시간대에 reload
- 30분간 cache 히트 채워질 때까지 지표 모니터링
- 다음 날 아침 피크 시간에 latency 변화 측정
p95 query latency가 12% 정도 줄었고, Postgres쪽 pg_stat_statements의 plan time 비중도 눈에 띄게 떨어졌다. 가장 큰 효과는 사실 이 둘이 아니라 CPU 사용률이었다. parsing이 확연히 줄어드니까 Postgres 노드의 user CPU가 평균 8%포인트 빠졌다. 우린 그동안 prepared statement를 못 쓴다는 이유로 RDS 인스턴스를 한 단계 크게 잡고 있었는데, 이번에 다운사이즈도 같이 했다.
정리
PgBouncer transaction 모드에서 prepared statement는 이제 그냥 켜도 되는 기능이다. 다만 그냥 max_prepared_statements만 박는다고 효과가 나오진 않는다. 드라이버 쪽 prepareThreshold/cache size 맞추고, 세션 상태 변경 패턴 한 번 점검하고, hit rate 지표를 볼 수 있게 해놓는 것까지가 한 세트라고 보면 된다.
우리 팀에선 이걸 켜고 나서 RDS 인스턴스 크기 다운그레이드까지 같이 가서 비용도 같이 잡혔다. 더 큰 배포에선 어떻게 동작할지는 또 다른 문제일 수 있다. 혹시 max_prepared_statements를 1000 이상 잡고 운영 중인 분 있으면 어떻게 튜닝하셨는지 댓글 남겨주시면 감사하겠다.