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

쿼리 시간이 어느정도 느려지면, 쿼리 실행문을 로그에 남기기 위해 postgresql.conf에 설정값 추가한다.

log_min_duration_statement = 5000

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

SELECT pg_reload_conf();

pg_reload_conf
----------------
 t
(1 row)

 

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

ALTER DATABASE test SET log_min_duration_statement = 5000;

ALTER DATABASE

 

남긴 로그 확인

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

 

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

postgresql.conf에 auto_explain 라이브러리 추가

session_preload_libraries = 'auto_explain';

 

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

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해줘야한다. 

shared_preload_libraries = 'pg_stat_statements'

 

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

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

americanopeople=# CREATE EXTENSION pg_stat_statements;

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

 

 

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

출처: https://kwomy.tistory.com/70?category=851266 [Kwomy's DB World]