pg_stat_statements 활용(통계 정보)

pg_stat_statements는 PostgreSQL 데이터베이스에서 실행되는 SQL 쿼리에 대한 자세한 통계를 제공하는 PostgreSQL용 확장입니다. 이 확장은 성능 모니터링 및 문제 해결에 유용함.

 

방법

1) postgresql.con 파일에 아래 내용 추가

shared_preload_libraries = 'pg_stat_statements'

#선택사항
pg_stat_statements.max = 10000  # Default is 5000
pg_stat_statements.track = 'all'  # Default is 'top'
pg_stat_statements.track_utility = on  # Default is off

• pg_stat_statements.save: 'on'인 경우 서버를 다시 시작할 때 통계를 저장합니다(기본값은 'on').
• pg_stat_statements.track: 추적할 쿼리를 제어합니다(예: all, top, none).

1-1) pg 재구동.(재기동 해야 적용됨.)

pg_ctl restart

 

2) extension 설치

CREATE EXTENSION pg_stat_statements;

 

3) 수집된 통계 보기 

-- 밀리초단위
SELECT
    query,
    calls,
    total_exec_time AS total_time,
    rows,
    100.0 * total_exec_time / sum(total_exec_time) OVER () AS percent_total_time,
    mean_exec_time AS mean_time,
    stddev_exec_time AS stddev_time,
    min_exec_time AS min_time,
    max_exec_time AS max_time
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

--초단위
   SELECT
    query,
    calls "호출횟수", 
    total_exec_time / 1000 AS "총 소요시간", 
    rows, 
    100.0 * total_exec_time / sum(total_exec_time) OVER () AS "총 실행 시간의 백분율", 
    mean_exec_time / 1000 AS "소요된 평균 시간", 
    stddev_exec_time / 1000 AS "실행 시간의 표준 편차",
    min_exec_time / 1000 AS "소요된 최소 시간", 
    max_exec_time / 1000 AS "소요된 최대 시간" 
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

--유저명, db명 포함해서 확인방법
   SELECT
    rolname AS username, -- User name who executed the query
    datname AS database_name, -- Database name where the query was executed
    query,
    calls "호출횟수", 
    total_exec_time / 1000 AS "총 소요시간", 
    rows, 
    100.0 * total_exec_time / sum(total_exec_time) OVER () AS "총 실행 시간의 백분율", 
    mean_exec_time / 1000 AS "소요된 평균 시간", 
    stddev_exec_time / 1000 AS "실행 시간의 표준 편차",
    min_exec_time / 1000 AS "소요된 최소 시간", 
    max_exec_time / 1000 AS "소요된 최대 시간" 
FROM
    pg_stat_statements
JOIN
    pg_database ON pg_stat_statements.dbid = pg_database.oid
JOIN
    pg_roles ON pg_stat_statements.userid = pg_roles.oid
ORDER BY
    total_exec_time DESC
LIMIT 10;

• query: SQL 쿼리의 텍스트입니다.
• calls: 이 쿼리가 실행된 횟수입니다.
• total_exec_time: 이 쿼리를 실행하는 데 소요된 총 시간(밀리초 단위)
• rows: 이 쿼리로 검색되거나 영향을 받는 총 행 수입니다.
• percent_total_time: 이 쿼리가 차지하는 총 실행 시간의 백분율입니다.
• mean_exec_time: 이 쿼리를 실행하는 데 소요된 평균 시간(밀리초 단위/초단위)다.
• stddev_exec_time: 이 쿼리에 대한 실행 시간의 표준 편차(밀리초 단위 /초단위 )
• min_exec_time: 이 쿼리를 실행하는 데 소요된 최소 시간(밀리초 /초단위 )
• max_exec_time: 이 쿼리를 실행하는 데 소요된 최대 시간(밀리초 단위 /초단위 )

 

4) 통계 재설정

SELECT pg_stat_statements_reset();

 

 

pg_stat_statements를 사용하면 느리거나 자주 실행되는 쿼리를 식별하여 PostgreSQL 데이터베이스의 성능을 효과적으로 모니터링하고 최적화할 수 있습니다.