쿼리성능 분석 툴 pg_stat_statements

pg_stat_statements는 PostgreSQL의 확장 모듈로, SQL 쿼리 실행 통계를 수집하고 조회할 수 있도록 합니다.

주요 기능

1. 쿼리 성능 분석:
   • 실행된 쿼리의 호출 횟수, 평균 실행 시간, 총 시간 등 다양한 성능 통계를 제공합니다.
2. 리소스 사용 모니터링:
   • CPU 사용량, I/O 횟수 등을 추적하여 리소스 소비가 많은 쿼리를 식별합니다.
3. 튜닝 도구:
   • 성능 저하를 유발하는 쿼리를 최적화하는 데 도움을 줍니다.

설치

CREATE EXTENSION pg_stat_statements;

shared_preload_libraries = 'pg_stat_statements' -- postgresql.conf 재시작 필요

 

위 처럼 db에서 활성화 해주면 되고 db마다 활성화가 필요하기 때문에 

template1 에다 설치해놓고 새로운 DB를 생성 할때 마다 template1을 참조해서 생성한다면 신규DB에 pg_stat_statements extention이 설치 됩니다.

기존에 설치 되어 있는 DB에는 create extension pg_stat_statements; 명령으로 설치를 따로 해줘야 합니다.

 

확인

select * from public.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) 데이터 쓰기 작업이 성능에 얼마나 영향을 미쳤는지 파악 가능

 

활용방안

/* 수행시간이 제일 긴 쿼리를 상위 50개만 조회 */
select a.userid
     , b.usename
     , a.dbid
     , c.datname
     , a.queryid
     , substr(a.query, 1, 100) as query
     , a.calls
     , a.total_exec_time
     , a.min_exec_time
     , a.max_exec_time
     , a.rows
  from public.pg_stat_statements a
  join pg_catalog.pg_user b on a.userid = b.usesysid
  join pg_catalog.pg_stat_database c on a.dbid = c.datid
 order by a.max_exec_time desc
 limit 50

/*가장 자주 실행된 쿼리 확인*/
SELECT query, calls, total_time, mean_time, rows 
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

--calls 값이 지나치게 높으면 캐싱 또는 배치 처리와 같은 최적화 방법 고려.

/*가장 느린 쿼리 확인*/
SELECT query, calls, total_time, mean_time, rows 
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 5;

--mean_time이 높은 쿼리는 인덱스 추가, 쿼리 리팩토링, 테이블 파티셔닝 등을 고려.

/*가장 많은 리소스를 소모한 쿼리 확인*/
SELECT query, calls, shared_blks_hit, shared_blks_read, shared_blks_written, total_time 
FROM pg_stat_statements
ORDER BY shared_blks_read + shared_blks_written DESC
LIMIT 5;

--shared_blks_read가 높으면 디스크 I/O 부하를 줄이기 위해 인덱스 최적화, 캐싱 적용 검토.
--shared_blks_written이 높으면 체크포인트나 쓰기 빈도를 조정.

 

주의사항

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

SELECT pg_stat_statements_reset();

 

 

 

 

 

참조

https://junhkang.tistory.com/94