PgBouncer transaction pooling, prepared statement 함정에서 빠져나온 이야기

 

지난주 화요일 오후, 모니터링 알람이 울렸다. PgBouncer 앞단의 connection 사용량이 평소 대비 3배 가까이 튀어 있었고, 어플리케이션 쪽 P99 레이턴시는 슬슬 200ms를 넘기고 있었다. 트래픽은 평소 수준. 이상한 점은 RDS Postgres의 active connection 수가 거의 변동이 없다는 거였다. 즉, 클라이언트 → PgBouncer 사이에서 뭔가 막혀 있다는 뜻이다.

이번 글은 그날 밤 새벽 2시까지 잡고 있던 이 문제를 어떻게 추적했는지, 그리고 결국 prepared statement와 transaction pooling이 충돌하던 지점을 어떻게 풀어냈는지에 대한 기록이다. 결론부터 말하면, 우리가 두 달 전에 한 PgBouncer 버전 업그레이드가 진짜 원인이었다.

처음에는 익숙한 의심부터 했다

처음 알람이 떴을 때 머리에 떠오른 가설은 셋이었다.

첫째, 어디선가 connection leak이 났다. 누군가 transaction을 안 닫은 코드를 머지했을 가능성. 그런데 어플리케이션 메트릭을 보니 동일 시간대에 transaction duration 평균은 오히려 살짝 줄어 있었다. 이건 아닌 듯.

둘째, 슬로우 쿼리가 늘었다. 그런데 pg_stat_statements를 까보니 상위 쿼리 분포가 평소와 동일했다. 새로운 쿼리가 끼어든 것도 아니고, 어느 쿼리 하나가 비대해진 것도 아니었다.

셋째, 누군가 prepared statement를 마구 만들고 있다. 사실 이게 정답에 가장 가까웠는데, 그땐 별로 의심하지 않았다. 우리 서비스는 transaction pooling 모드를 쓰고 있었고, "transaction pooling에서는 prepared statement를 못 쓴다"가 내 머릿속의 상식이었기 때문이다. 그래서 자연스럽게 후순위로 밀려났다.

근데 이 "상식"이라는 게 사실 2년 전 기준이었다는 게 함정이었다.

진짜 원인을 찾기까지

새벽 12시쯤, 일단 PgBouncer 쪽 stats를 다 까봤다. SHOW STATS, SHOW POOLS, SHOW SERVERS 다.

psql -h pgbouncer-host -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;

여기서 흥미로운 걸 발견했다. cl_waiting 컬럼이 평소 0이던 게 30~40 사이를 왔다갔다 하고 있었다. 클라이언트가 서버 연결을 받기 위해 대기 중이라는 뜻. 그런데 sv_active는 풀 사이즈에 거의 도달해 있었고, sv_idle은 0에 가까웠다. 풀이 꽉 차서 새 클라이언트가 못 들어오고 있는 상황.

그 시점에 동료 한 명이 슬랙으로 "혹시 두 달 전에 PgBouncer 1.18에서 1.22로 올렸던 거랑 관련 있을까요?"라고 물어왔다. 솔직히 그 순간 좀 멘탈이 흔들렸다. 업그레이드 직후엔 아무 문제 없었기 때문에 그 가능성을 고려하지 않고 있었다.

근데 흔들리는 김에 release note를 다시 봤다. 1.21에서 transaction pooling 모드에 prepared statement 지원이 추가됐다는 항목이 있었다. max_prepared_statements 라는 새 설정으로 켜고 끄는 구조였다. 기본값은 0, 즉 off.

여기서 두 가지가 머리에서 부딪혔다. 우리는 max_prepared_statements를 명시적으로 설정한 적이 없었다. 그러면 기본값 0으로 돌고 있을 텐데, 왜 prepared statement가 문제가 되지?

검증을 위해 어플리케이션 쪽 ORM 설정을 다시 확인했다. 우리는 Java 진영 HikariCP + JDBC 조합인데, JDBC 드라이버의 prepareThreshold 설정이 5로 잡혀 있었다. 즉 같은 쿼리가 5번 이상 호출되면 자동으로 server-side prepared statement로 승격된다는 의미.

여기서 무엇이 일어나고 있었느냐. PgBouncer는 max_prepared_statements=0 이라 prepared statement를 트래킹하지 않는다. transaction pooling 모드에서는 매 transaction마다 다른 server connection이 할당될 수 있는데, 어플리케이션이 PREPARE name AS ... 을 한 connection에서 했다고 해도 다음 transaction에서는 그 prepared statement가 없는 다른 server connection이 잡힐 수 있다. 그러면 클라이언트는 prepared statement "S_3" does not exist 같은 에러를 받는다.

JDBC 드라이버는 이 에러를 받으면 statement를 invalidate하고 재시도한다. 그런데 재시도 과정에서 connection을 새로 잡거나, 일부 코드 패스에서는 transaction을 새로 시작하기도 한다. 트래픽이 어느 임계점을 넘어가면 이 재시도가 쌓이면서 풀이 막히는 패턴이었다.

왜 두 달 동안은 멀쩡했냐. 사실 멀쩡하지 않았다. 메트릭을 과거로 돌려보니 잔잔하게 spike가 있었는데, 알람 임계치 밑이라 알아채지 못했던 것 뿐이었다. 이날은 마침 한 캠페인 트래픽이 겹치면서 임계치를 넘어버렸다.

두 가지 해결책 사이에서 고민하다

해결책은 두 갈래였다.

첫째, 어플리케이션 측에서 prepared statement를 끄는 것. JDBC URL에 prepareThreshold=0 박는 단순한 방법. 안전하지만, 일부 핫한 쿼리에서 plan caching의 이점을 포기해야 한다. 우리 서비스에서 그 손해가 얼만지는 실측해봐야 했다.

둘째, PgBouncer 측에서 max_prepared_statements를 켜는 것. 1.21부터 추가된 기능이라 우리 1.22 버전에서도 쓸 수 있다. 켜면 PgBouncer가 prepared statement를 추적해서 다른 server connection으로 transaction이 라우팅돼도 알아서 다시 PREPARE를 날려준다.

# pgbouncer.ini
[databases]
mydb = host=db-host port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 100

처음엔 둘째가 더 깔끔해 보였다. 어플리케이션을 안 건드려도 되고, prepared statement의 이점도 살릴 수 있으니까. 근데 두 가지가 마음에 걸렸다.

하나는 메모리. max_prepared_statements는 server connection 하나당 LRU 캐시 사이즈인데, 우리 풀이 적당히 크다 보니 누적 메모리 사용량이 어느 정도일지 감이 안 왔다. PgBouncer는 single-threaded라 메모리가 어느 임계 넘어가면 latency에 직접 영향 준다.

다른 하나는 운영 리스크. 이 기능을 production에서 본격적으로 검증한 사례를 사내에서 아무도 못 가지고 있었다. 같은 시점에 신뢰할 수 있는 벤치마크 데이터도 부족했다. 새벽 2시에 production PgBouncer 설정을 만질 자신은 없었다.

그래서 단기 처방으로는 첫 번째를 택했다. JDBC URL에 prepareThreshold=0을 박고 어플리케이션을 롤링 재시작. 30분 뒤 connection 사용량이 평소 수준으로 돌아왔고, 그날 밤은 그렇게 끝났다.

그 뒤에 한 것들과 남은 것들

다음 날 회고에서 몇 가지를 정리했다.

운영 환경에서 prepareThreshold=0으로 두는 게 단기적으로는 정답이었지만, 장기적으로는 PgBouncer의 max_prepared_statements를 켜는 게 맞다는 결론에 도달했다. 핫한 OLTP 쿼리에서 prepare 안 하면 결국 PG planner가 매번 도는데, 우리 워크로드 특성상 이게 누적되면 DB CPU에 부담이 된다. 실제로 며칠 지나니 RDS CPU 사용률이 미세하게 올라간 게 보이기 시작했다.

그래서 지금은 staging에서 max_prepared_statements=200 으로 띄워놓고 부하 테스트를 돌리고 있다. PgBouncer 메모리 사용량과 latency를 같이 보면서 적절한 값을 찾는 중. 일주일쯤 더 검증한 뒤 production에 단계적으로 롤아웃할 생각이다.

또 하나, 모니터링 대시보드에 PgBouncer의 cl_waiting, sv_active, sv_idle을 명시적으로 띄워놨다. 처음에 너무 RDS 쪽 메트릭만 보다가 한 시간을 날렸다. PgBouncer는 어플리케이션 입장에서는 DB지만, DBA 입장에서는 어플리케이션이다. 양쪽 다 봐야 한다.

마지막으로 교훈 하나. "transaction pooling에서는 prepared statement 못 쓴다"는 더 이상 맞는 말이 아니다. 1.21 이후로는 조건부로 쓸 수 있고, 1.22~1.23으로 가면서 안정성도 올라왔다. 머릿속에 박혀 있는 DB 운영 상식 중에 2년 이상 묵은 건 한 번씩 의심해볼 필요가 있다는 걸 새삼 느꼈다.

혹시 비슷한 환경에서 max_prepared_statements를 production에서 굴리고 계신 분 있으면 후기 좀 공유해주시면 좋겠다. 댓글이든 메일이든.