본문 바로가기

소프트웨어-이야기/데이터 저장소 + 시각화

(PostgreSQL) 슬로우쿼리를 잡아내는 3가지 방법

해당 글은 Weekly Postgres에서 보내준 3 WAYS TO DETECT SLOW QUERIES IN POSTGRESQL을 보고 정리한 글입니다 😀


슬로우쿼리를 잡아내는 3가지 방법

PostgreSQL에서 슬로우쿼리를 잡아내는 방법은 크게 3가지가 있다.

1. 슬로우 쿼리가 발생하면 로그 남기기

2. 쿼리 실행계획 로그에 남기기

3. 쿼리 실행 통계 보기


1. 슬로우 쿼리가 발생하면 로그 남기기

어느정도 느려지면, 쿼리 실행문을 로그에 남길건지 postgresql.conf에 설정값을 추가해줘야한다.

1
log_min_duration_statement = 5000


그리고 config를 reload 해주면 된다.

1
2
3
4
5
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)


특정 데이터베이스, 특정 유저 권한에서 발생한 슬로우쿼리만 모니터링 할 수도 있다.

1
2
postgres=# ALTER DATABASE test SET log_min_duration_statement = 5000;
ALTER DATABASE


그러면 이렇게 로그가 남는다.


이렇게 하면, 부하를 유발하는 단일 쿼리를 파악하기 쉽다. 그러나 처리 시간은 빠르지만, 여러번 호출되서 부하를 발생시키는 쿼리 실행을 인지하기는 어렵다는 단점이 있다. 


2. 쿼리 실행계획 로그에 남기기

postgresql.conf에 auto_explain 라이브러리를 추가해주거나, 

1
session_preload_libraries = 'auto_explain';

라이브러리를 직접 로드한 후, 설정 값들을 쿼리문으로 명시해주면 사용할 수 있다.

1
2
3
4
5
6
americanopeople=# LOAD 'auto_explain';
LOAD
americanopeople=# SET auto_explain.log_analyze TO on;
SET
americanopeople=# SET auto_explain.log_min_duration TO 500;
SET


그러면 이렇게 실행계획이 로그에 남는다.


실행계획을 로그에 남기면, 당시의 쿼리 실행 계획을 볼 수 있단 장점이 있다.

롱쿼리가 발생한 이후, 데이터가 더 쌓이거나, 삭제되면 문제가 된 순간의 실행계획을 알 수 없다. 때문에 이를 확인할 수 있단 장점이 있다.


그런데 EXPLAIN ANALYZE 명령문을 기반으로 로그를 남기기 때문에, 롱쿼리를 다시 실행시킨단 리스크가 있다.

( 만약 롱쿼리가 갑자기 몰리는 상황에서, 로그를 남기기 위해 EXPLAIN ANLAYZE 쿼리가 날라간다면, 롱쿼리가 2배로 날라가는거여서, 문제를 더 키울 수 있다. )

그리고 단일 롱쿼리만 파악할 수 있기 때문에, 짧지만 여러번 호출되서 문제를 일으키는 쿼리를 알 수 없단 단점이 있다.


3. 쿼리 실행 통계 보기

postgres.conf에 설정값을 추가해주고, postgresql 서버를 재가동해줘야한다. 

쿼리 실행 통계 라이브러리는 shared memory를 사용하기 때문에, 해당 모듈을 추가 / 삭제할 때는 항상 서버를 restart해줘야한다. 


1
shared_preload_libraries = 'pg_stat_statements'


실행계획 로깅 관련 라이브러리는 session_preload_libraries에 라이브러리를 추가해준거고, 이건 shared_preload_libraries에 라이브러리를 추가해준거다.

그리고 쿼리 실행문으로 pg_stat_statements extension을 import해주면 사용할 수 있다. 

1
americanopeople=# CREATE EXTENSION pg_stat_statements;


이 방식을 사용하면, 빨리 실행되지만 부하를 일으키는 쿼리를 파악하기 좋단 장점이 있다. 

pg_stat_statements VIEW를 조회하면, 쿼리들의 실행 통계를 볼 수 있다. ( 호출 수, 최대 / 최소 소요시간 등등.. )


3 WAYS TO DETECT SLOW QUERIES IN POSTGRESQL

Optimizing PostgreSQL queries using pg_stat_statements

[PostgreSQL] pg_stat_statements


<정리 끝 🤓>