[PostgreSQL] Slow Query를 감지하기 위한 3가지 방법

• target:  Slow Query 감지

 

---

• method: 

1. Turn on the slow query log

1) 전체 DB에 적용

전통적인 방법으로 쿼리가 특정 시간보다 오래걸리면 로그에 한 줄을 전송

기본적으로 slow query log는 비활성화 상태이기에 아래와 같이 활성화 필요

# postgresql.conf
# 로그 생성 시, 최소 쿼리 수행 시간(단위: ms)
log_min_duration_statement = 5000

DBMS에서 postgresql.conf를 reload

# SELECT pg_reload_conf();
pg_reload_conf
---------------
t
(1 row)

---

2) 특정 DB에만 적용

특정 사용자 or 특정 DB에 대해서만 변경하는 경우

# ALTER DATABASE [데이터베이스 명] SET log_min_duration_statement = 5000;
ALTER DATABASE

- 장점:

• 단일 쿼리인 Slow Query를 즉시 검사 가능

- 단점:

• 그다지 느리지 않은 수많은 쿼리로 인한 성능 저하 감지 불가

 

---

2. unstable execution plans

쿼리 수행시간이 특정 임계시간을 초과하면 PostgreSQL은 로그 파일에 기록하기 위해서 'auto_explain' 라이브러리 설정

# postgresql.conf
session_preload_libraries = 'auto_explain';

 

DBMS에서 postgresql.conf를 reload

# SELECT pg_reload_conf();
pg_reload_conf
---------------
t
(1 row)

 

쿼리 수행시간이 특정 임계시간을 초과하면 PostgreSQL에서 로그에 기록하도록 설정

# LOAD 'auto_explain';
LOAD
# SET auto_explain.log_analyze TO on;
SET
# SET auto_explain.log_min_duration TO 500;
SET

 

- 장점:

• slow query를 로그에 기록함으로써, 해당 쿼리에 대한 정보(발생일시, 수행 시간, 쿼리정보 등)을 나중에도 확인 가능

- 단점:

• auto_explain 또한 log_min_duration을 사용함으로써, 특정 임계시간 이내로 수행되는 쿼리지만, 반복호출로 인한 성능 저하는 감지 불가

 

---

3. pg_stat_statements

postgres.conf의 shared_preload_libraries에 'pg_stat_statements' 라이브러리 설정

shared_preload_libraries = 'pg_stat_statements'

postgresql 서버를 재가동

 

쿼리에 대한 정보를 확인하기 위해서 pg_stat_statements 뷰 생성

# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
# \d pg_stat_statements
View "public.pg_stat_statements"

 

Column	Type	Collation	Nullable	Default
userid	oid
dbid	oid
toplevel	bool
queryid	bigint
query	text
plans	bigint
total_plan_time	double precision
min_plan_time	double precision
max_plan_time	double precision
mean_plan_time	double precision
stddev_plan_time	double precision
calls	bigint
total_exec_time	double precision
min_exec_time	double precision
max_exec_time	double precision
mean_exec_time	double precision
stddev_exec_time	double precision
rows	bigint
shared_blks_hit	bigint
shared_blks_read	bigint
shared_blks_dirties	bigint
shared_blks_written	bigint
local_blks_hit	bigint
local_blks_read	bigint
local_blks_dirtied	bigint
local_blks_written	bigint
temp_blks_read	bigint
temp_blks_written	bigint
blk_read_time	double precision
blk_write_time	double precision
wal_records	bigint
wal_fpi	bigint
wal_bytes	numeric

※ table UPDATED 26. April. 2022!

 

- 장점:

• 특정 쿼리의 전체 런타임, 런타임 분포 및 수행 빈도수 확인 가능
• 'auto_explain' 라이브러리에서 검출하기 어려운 fast query의 반복호출로 인한 성능저하를 발견할 수 있음
• 다양한 유형의 쿼리의 I/O 동작에 대한 정보 제공

- 단점:

• 특정 Slow Query에 대한 추적이 어려울 수 있음

 

---

• warning: 

PostgreSQL 버전업으로 인하여 게시글의 내용과 다를 수 있음

ex) pg_stat_statements VIEW에서 제공하는 컬럼의 일부 이름이 PG12와 PG13에서 변경 및 추가

PG 12	PG 13
total_time	total_plan_time
	total_exec_time

---

• source:

https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/

3 ways to detect slow queries in PostgreSQL - CYBERTEC

 

https://pu3vig.tistory.com/107

[PostgreSQL] 쿼리 실행 통계 확인을 위한 pg_stat_monitor