PostgreSQL DB와 PGBouncer ( + Prepared Statements )

 

 

Client가 PostgreSQL DB에서 Query를 실행한다는 것은 다음과 같다. 

 

1) ClientA가 PostgreSQL DB auth에 성공한다. ( 세션 생성 )

2) PostgreSQL DB 내부에 ClientA 전용 process가 생성된다. ( backend process ) ( idle )

3) ClientA가 ClientA 전용 process 공간에서 BEGIN 으로 트랜잭션을 시작한다. ( idle in transaction ) 

4) ClientA가 쿼리를 실행한다. ( active ) 

5) ClientA가 COMMIT/ROLLBACK 으로 트랜잭션을 종료한다. 

6) ClientA의 backend process/session은 유지되며 다음 요청을 기다린다. ( idle )

7) ClientA connection 이 종료되면 ClientA 전용 process 공간도 제거된다. ( 세션 종료 )

 

 

여기서 가장 큰 문제는 Client와 Process가 1 대 1로 매핑되므로 Client가 10개, 50개, 80개, 100개 이상으로 증가하게 되면, DB는 많은 프로세스를 관리함과 동시에 잦은 컨텍스트 스위칭에 시달려 성능 저하를 겪게 되는 것이다.

 

 

그렇다면

PGBouncer는 이 문제를 어떻게 해결할까?

 

 

 

 

 

 

PGBouncer가 PostgresSQL DB에 미리 고정된 범위의 backend process 생성해놓는다. ( pool 단위 ) 

 

PGBouncer는 PostgresSQL DB 대신 Client와 연결되어, Client를 번갈아 미리 생성해놓은 Backend Process에 할당하여 트랜잭션을 처리한다. Client가 100 개인데,  backend process 가 10개라면 90개는 세션 연결 에러를 일으키지 않고 큐에서 차례를 기다리면 얌전히 대기한다. Client입장에서는 PGBouncer와 정상적으로 연결이 된 상태이기에 연결오류라고 인식하지 않는 것이다. 

 

PGBouncer를 트랜잭션 모드로 동작시키면, 세션 단위가 아닌 트랜잭션 처리 단위 ( BEGIN - COMMIT/ROLLBACK )로 Backend Process를 할당한다.  PGBouncer가 없다면 한 개의 세션에 한 개의 backend process 이지만, PGBouncer를 통한 다면 한 개의 세션동안 처리되는 다수의 트랜잭션 마다  backend process 여러 곳에 할당이 될 수 있는 것이다. 

 

결과적으로

Client 개수가 아무리 많아져도 backend process는 고정된 범위 안의 개수로 유지가 되고 backend process가 종료되지도 않기 때문에 반복적인 컨텍스트 스위칭도 발생하지 않아 DB 리소스 부담이 덜해지는 것이다. 

 

 

 

Prepared Statements

 

PGBouncer에도 한 가지 단점이 있었다. Client는 Prepared Statement를 사용하여 반복적인 쿼리 파싱 및 실행 계획 생성을 방지할 수 있다. 하지만 PGBouncer를 쓰게 되면 문제가 생기는게, Prepared Statement는 backend process 내부에 저장되지만 트랜잭션 마다 backend process가 변경되니, Client가 다음 트랜잭션에서 Prepared Statement를 이용할 수가 없어 비활성화 해야만한다. 그 결과 Client는 쿼리의 컬럼 타입 조회와 같은 메타데이터 조회 쿼리를 반복적으로 수행하게 되고, 이는 실제 비즈니스 쿼리 외의 추가적인 DB 부하로 이어진다.

 

 

 

개인적으로도 PGBouncer 오래된 버전을 쓰고 있어 Prepared Statement 기능을 사용하지 못하니 asyncpg가 typeinfo_tree 쿼리를 비정상적으로 많이 호출하여 DB CPU 부하가 100% 까지 반복적으로 Spike를 쳤다. 

 

다행이도 

PGBouncer는 1.21 버번 이후부터 Prepared Statement 기능을 사용할 수 있게 되었다. 

https://www.postgresql.org/about/news/pgbouncer-1210-released-now-with-prepared-statements-2735/

 

PgBouncer는 Prepared Statement 사용 정보를 추적하고 있다가, backend process가 변경되더라도 해당 backend에 Prepared Statement를 다시 준비(re-prepare)하여 Prepared Statement를 지속적으로 재사용할 수 있도록 한다. asyncpg(Client)와 PgBouncer가  Prepared Statement를 사용할 수 있도록 활성화를 하자 놀랍도록 DB CPU 부하는 감소하였다. 

 

 

 

 

 

 

이처럼 

PGBouncer는 PostgresSQL DB가 안정적으로 프로세스를 관리할 수 있도록 함과 동시에 Prepared Statement 기능까지 갖추어진 효율적인 최적화 도구라 할 수 있다.