[PostgreSQL] pg_stat_statements 설정하기

pg_stat_statements 확장은 각 쿼리의 실행 횟수, 총 시간, 평균 시간 등을 추적합니다. 이 정보를 사용하여 CPU 사용을 간접적으로 추정할 수 있습니다.

 

pg_stat_statements 의 relation을 찾을 수 없음 

postgres=# SELECT * FROM pg_stat_statements;
오류:  "pg_stat_statements" 이름의 릴레이션(relation)이 없습니다
줄 1: SELECT * FROM pg_stat_statements;

 

pg_stat_statements  확장팩 설치 

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

 

 

postgresql.conf 파일 설정

┌─[postgres][~/15/data]
└─▪  cat postgresql.conf | grep shared_pre
#shared_preload_libraries = ''	# (change requires restart)
shared_preload_libraries = 'pg_stat_statements'

 

재기동이 필요

postgres=# select * from pg_stat_statements;
오류:  pg_stat_statements must be loaded via shared_preload_libraries

 

재기동

┌─[postgres][~]
└─▪  pg_ctl restart
서버를 멈추기 위해 기다리는 중.... 완료
서버 멈추었음
서버를 시작하기 위해 기다리는 중....2024-07-15 11:11:11.601 KST [40025] 로그:  서버 로그를 로그 수집 프로세스로 보냅니다.
2024-07-15 11:11:11.601 KST [40025] 힌트:  이제부터 서버 로그는 "log" 디렉터리에 보관됩니다.
 완료
서버 시작됨

 

 

 

출력확인

postgres=# \x auto
칼럼 단위 보기 기능을 자동으로 지정 함.
postgres=#  select * from pg_stat_statements;
-[ RECORD 1 ]----------+---------------------------------
userid                 | 10
dbid                   | 5
toplevel               | t
queryid                | -2360274071763417063
query                  | select * from pg_stat_statements
plans                  | 0
total_plan_time        | 0
min_plan_time          | 0
max_plan_time          | 0
mean_plan_time         | 0
stddev_plan_time       | 0
calls                  | 5
total_exec_time        | 0.897284
min_exec_time          | 0.091294
max_exec_time          | 0.25709099999999996
mean_exec_time         | 0.1794568
stddev_exec_time       | 0.06191412999760232
rows                   | 4
shared_blks_hit        | 0
shared_blks_read       | 0
shared_blks_dirtied    | 0
shared_blks_written    | 0
local_blks_hit         | 0
local_blks_read        | 0
local_blks_dirtied     | 0
local_blks_written     | 0
temp_blks_read         | 0
temp_blks_written      | 0
blk_read_time          | 0
blk_write_time         | 0
temp_blk_read_time     | 0
temp_blk_write_time    | 0
wal_records            | 0
wal_fpi                | 0
wal_bytes              | 0
jit_functions          | 0
jit_generation_time    | 0
jit_inlining_count     | 0
jit_inlining_time      | 0
jit_optimization_count | 0
jit_optimization_time  | 0
jit_emission_count     | 0
jit_emission_time      | 0

 

 

 

일반 User가 pg_stat_statements 뷰 보는 법

user의 database에 확장팩이 설치되어 있어야한다 

suadb=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

 

postgres=# grant pg_monitor to sua;
GRANT ROLE

권한부여 

 

 

 

 

 

 

 

 

pg_stat_statements 생성쿼리를 보면 function으로 만들어진거다 

CREATE VIEW pg_stat_statements AS
  SELECT * FROM pg_stat_statements(true);

 

function 생성 쿼리를 보면

/* Now redefine */
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
    OUT userid oid,
    OUT dbid oid,
    OUT queryid bigint,
    OUT query text,
    OUT calls int8,
    OUT total_time float8,
    OUT min_time float8,
    OUT max_time float8,
    OUT mean_time float8,
    OUT stddev_time float8,
    OUT rows int8,
    OUT shared_blks_hit int8,
    OUT shared_blks_read int8,
    OUT shared_blks_dirtied int8,
    OUT shared_blks_written int8,
    OUT local_blks_hit int8,
    OUT local_blks_read int8,
    OUT local_blks_dirtied int8,
    OUT local_blks_written int8,
    OUT temp_blks_read int8,
    OUT temp_blks_written int8,
    OUT blk_read_time float8,
    OUT blk_write_time float8
)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'pg_stat_statements_1_3'
LANGUAGE C STRICT VOLATILE;