[PostgreSQL] RDS 쿼리 성능 분석 방법 : pg_stat_statements 설치, 고려사항 및 주요 지표

1. RDS에서의 쿼리 성능 분석, pg_stat_statements란?

RDS에서는 외부 익스텐션 사용이 제한된다. 쿼리 통계 및 성능에 대한 지표를 알 수 있는 여러 가지 익스텐션이 있지만, 그중 pg_stat_statements가 허용된다. pg_stat_statements의 원리, 사용법, 주요 지표에 대해 알아보자.

 

pg_stat_statements는 실행된 쿼리에 대한 통계를 수집하고 저장한다. 다음은 저장되는 주요 지표들이고, 이를 통해 쿼리 성능을 분석하고 최적화할 수 있다. 

• 쿼리빈도
• 쿼리 실행시간
• 쿼리 리소스 사용량 (CPU, 디스크I/O, 버퍼 히트 등)
• 쿼리 텍스트 저장

2. 설치 

설치가 되어있지않다면 익스텐션을 먼저 설치하면 된다.

CREATE EXTENSION pg_stat_statements;

만약 설치가 안된다면, RDS 데이터베이스가 할당된 파라미터 그룹에서 "shared_preload_libraries" 파라미터에 "pg_stat_statements"추가되어 있는지 확인하자

3. 상세 지표

SELECT * FROM pg_stat_statements;

컬럼	설명
userid	쿼리 실행 사용자ID, 어떤 쿼리를 어떤 사용자가 사용했는지 추적 가능
dbid	쿼리 실행 데이터베이스 ID, 동일 인스턴스 에 여러 데이터베이스를 사용하는 경우, 데이터베이스 실행 추적 가능
queryid	쿼리 텍스트의 해쉬 값, 동일 쿼리 식별 가능 (쿼리가 변형되어도 ID를 통해 유사쿼리 그룹화 가능)
query	실제 실행된 쿼리 텍스트
calls	쿼리가 실행된 횟수
total_time	쿼리의 총 실행 시간 (ms), 모든 호출의 실행시간 합산으로 쿼리의 전체 리소스 소비량을 알 수 있음
min_time	쿼리 실행 최소 시간 (ms)
max_time	쿼리 실행 최대 시간 (ms)
mean_time	쿼리 실행 평균 시간 (ms)
stddev_time	쿼리 실행 표준편차 ( 실행 시간의 변동성 판단 가능, 변동성이 크다면 특정 상황에서 성능문제 발생할 수 있음 )
rows	쿼리 실행 결과 행 수
shared_blks_read	공유 메모리에서 블록이 히트된 횟수, 메모리에서 쿼리가 얼마나 효율적으로 데이터를 읽었는지 파악 가능
shared_blks_dirtied	디스크에서 읽은 공유 블록의 수 (값이 높으면 디스크 I/O가 많이 발생)
shard_blks_written	쿼리 실행중 수정된 공유블록의 수, (데이터 변경이 얼마나 발생했는지 확인 가능)
local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written	shared_ 헤더의 값들과 유사, 공유 메모리가 아닌 로컬 메모리에서의 지표
temp_blks_read	쿼리 실행 중 임시 테이블에서 읽은 블록 수 (높으면 쿼리가 임시 테이블을 많이 사용중)
temp_blks_written	쿼리 실행 중 임시 테이블에 기록된 블록 수 (높다면 쿼리 최적화 필요)
blk_read_time	블록을 읽는데 총 걸린 시간 (ms) 쿼리가 I/O작업에서 얼마나 많은 시간을 소비했는지 확인 가능
blk_write_time	블록을 쓰는데 걸린 총 시간 (ms) 데이터 쓰기 작업이 성능에 얼마나 영향을 미쳤는지 파악 가능

 

4. 어떤 지표를 봐야 하나?

4-1. 쿼리 실행시간 관련

쿼리의 실행시간은 절대적인 기준으로 판단할 수 없다. 빠를수록 좋겠지만 "5초 이상은 무조건 튜닝이 필요한 쿼리"라고 할 수 없다는 것이다. 시스템 자원, 쿼리 플래닝 및 테스트를 통해 파악된 해당 쿼리의 "예상된" 소모 시간 대비, 혹은 "기존" 소모시간 대비 분석을 해야 한다.

• total_time : 쿼리가 총 소모한 시간이 특정 시점부터 급격히 증가하면 확인 대상
• mean_time : 쿼리의 평균 실행시간이 특정 시점부터 급격히 증가하면 확인 대상
• max_time : 쿼리 최대 실행시간이 특정 상황에서 비정상적으로 오래 시간이 걸린다면 확인 대상
• calls : 쿼리 호출 횟수로, 예상 수치보다 자주 호출되거나, 자주 호출되는데 실행시간이 길다면 확인 대상

4-2. I/O 문제

디스크 읽기/쓰기 작업이 과도하게 발생하는 쿼리는 성능 문제를 일으킬 수 있다.

• shared_blks_read, shared_blks_write : 디스크에 읽고, 쓰는 공유 블록의 수로, 급격히 증가한다면 읽기/쓰기 작업이 과도하게 발생한 것을 의미하므로 확인 대상
• temp_blks_read : 임시 테이블에서 읽은 블록수가 크다면 임시테이블 사용이 빈번한 쿼리로 확인대상
• blk_write_time : 블록을 쓰는데 소요된 총시간으로 특정 시점부터 급격히 증가한다면 확인 대상

4-3. 변동성 및 불안정성

쿼리의 성능이 일관적이지 않다면 특정 상황에만 문제가 될 수 있는 쿼리로, 성능 문제를 일으킬 수 있다.

• stddev_time : 쿼리 실행시간의 표준편차, 값이 크다면 성능이 일관되지 않다는 의미로 확인 대상
• max_time : 쿼리 실행 최대시간, 특정 시점에서만 비정상적으로 실행시간 증가한다면 확인 대상

5. 주의사항

• pg_stat_statements는 쿼리에 대한 추가 통계를 수집하기 때문에 성능에 약간의 오버헤드를 유발할 수 있다. 일반적으로 시스템 운영에 영향을 줄정도로 크지 않지만, 매우 트래픽이 많은 시스템은 주의가 필요하다.
• 통계가 계속 축적되기 때문에 설정에 따라 디스크 사용량이 증가할 수 있다. pg_stat_statements_max (기본값 : 5000)을 적절히 조정하여 관리해야 한다.
• pg_stat_statements를 통해 실행되는 쿼리문 자체가 저장되기에 민감 데이터가 노출되지 않도록 관리해야 한다. (사용자 권한을 제한 권고)
• 누적된 집계 외에 특정 시점 이후의 성능을 분석하고 싶다면 통계 초기화해야 한다.

SELECT pg_stat_statements_reset();

6. 정리

pg_stat_statements의 지표를 통해 쿼리 성능 분석이 가능하다. 특히 쿼리 실행시간, 디스크 I/O, 변동성의 관점에서 쿼리 분석을 시작하는 것이 좋다. 트래픽이 아주 많은 시스템의 경우 익스텐션 설치만으로도 오버헤드가 발생할 수 있으니 충분한 테스트가 필요하다. 모든 지표들이 객관적인 정답을 가지고 있진 않기에, 현재 운영 중인 서비스의 시스템 자원, 쿼리 플래닝 및 테스트를 통해 파악된 해당 쿼리의 "예상된" 소모 시간과 "기존" 소모시간을 지속적으로 관리하여 예외적인 상황들을 빠르게 인지하고 대응하는 것이 중요하다.