| OS: linux 7.5 DBMS: PostgreSQL 14.9 |
[목차여기]
PostgreSQL은 기본적으로 SQL 실행계획을 저장하지 않습니다.
운영 시 SQL 실행 계획이 변경되었는지 확인하는 것은 중요한 부분 중 하나인데 그게 불가능한 것이죠.
pg_store_plans이라는 extension을 사용하면 이 부분이 해결가능한데 pg_store_plans을 설치하고 간단하게 사용해 보는 테스트를 진행해 보겠습니다.
rpm 파일 다운
- pg_store_plans rpm 파일 다운 사이트
https://github.com/ossc-db/pg_store_planshttps://github.com/ossc-db/pg_store_plans/releases
- 플랫폼에 해당하는 rpm 다운로드
rpm 설치
[root@svr1 ~]# rpm -qa | grep store
abrt-addon-pstoreoops-2.1.11-50.0.1.el7.x86_64
[root@svr1 ~]#
[root@svr1 ~]#
[root@svr1 ~]# rpm -Uvh pg_store_plans14-1.6.1-1.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:pg_store_plans14-1.6.1-1.el7 ################################# [100%]
[root@svr1 ~]# rpm -qa | grep store
abrt-addon-pstoreoops-2.1.11-50.0.1.el7.x86_64
pg_store_plans14-1.6.1-1.el7.x86_64
[root@svr1 ~]#
postgresql.conf 파일 파라미터 수정(DB재기동 시 적용됨)
테스트는 pg_store_plans.min_duration = 1000(1초 이상 실행SQL만 수집) 파라미터만 변경하고 나머지는 그대로 진행하였음
| shared_preload_libraries = 'pg_stat_statements,timescaledb,pg_hint_plan,pg_store_plans' pg_store_plans.min_duration = 1000 |
-- 파라미터 확인
SELECT name, setting, unit
FROM pg_catalog.pg_settings
WHERE name LIKE '%pg_store%'
;
name | setting | unit
--------------------------------+---------+------
pg_store_plans.log_analyze | off |
pg_store_plans.log_buffers | off |
pg_store_plans.log_timing | on |
pg_store_plans.log_triggers | off |
pg_store_plans.log_verbose | off |
pg_store_plans.max | 1000 |
pg_store_plans.max_plan_length | 5000 |
pg_store_plans.min_duration | 1000 |
pg_store_plans.plan_format | text |
pg_store_plans.plan_storage | file |
pg_store_plans.save | on |
pg_store_plans.track | top |
(12 rows)
pg_store_plans extension 생성
-- 설치된 extension 조회
SELECT name, default_version,installed_version
FROM pg_available_extensions
WHERE name LIKE '%store%'
;
name | default_version | installed_version
----------------+-----------------+-------------------
hstore | 1.8 |
hstore_plperl | 1.0 |
hstore_plperlu | 1.0 |
pg_store_plans | 1.6.1 |
(4 rows)
-- pg_store_plans 설치
sk1=# create extension pg_store_plans ;
CREATE EXTENSION
-- 설치 확인
SELECT name, default_version,installed_version
FROM pg_available_extensions
WHERE name LIKE '%store%'
;
name | default_version | installed_version
----------------+-----------------+-------------------
hstore | 1.8 |
hstore_plperl | 1.0 |
hstore_plperlu | 1.0 |
pg_store_plans | 1.6.1 | 1.6.1
(4 rows)
테스트 SQL 실행
SELECT count(*) FROM tab100 a, tab100 b ;
count
----------
68442529
(1 row)
Time: 5378.473 ms (00:05.378)
저장된 실행계획 조회
- pg_stat_statements 와 조인하여 SQL별 실행 계획 조회
SELECT s.queryid, s.query
, p.planid
, p.plan
, p.calls AS "plan calls"
, s.calls AS "stmt calls"
, round(s.mean_exec_time::numeric/1000,2) AS avg_exec_sec
, (s.shared_blks_hit + s.shared_blks_read) shared_blks_io
, p.first_call, p.last_call
FROM pg_stat_statements s, pg_store_plans p
WHERE s.queryid = p.queryid
;
queryid | query | planid | plan | plan calls | stmt calls | avg_exec_sec | shared_blks_io | first_call | last_call
----------------------+-----------------------------------------+------------+-------------------------------------------------------------------------------+------------+------------+--------------+----------------+-------------------------------+-------------------------------
-8852661820973207002 | SELECT count(*) FROM tab100 a, tab100 b | 2186412635 | Aggregate (cost=1027208.08..1027208.09 rows=1 width=8) +| 1 | 6 | 5.34 | 2304 | 2023-12-05 17:34:03.126315+09 | 2023-12-05 17:34:03.126315+09
| | | -> Nested Loop (cost=0.00..856101.76 rows=68442529 width=0) +| | | | | |
| | | -> Seq Scan on tab100 a (cost=0.00..274.73 rows=8273 width=0) +| | | | | |
| | | -> Materialize (cost=0.00..316.10 rows=8273 width=0) +| | | | | |
| | | -> Seq Scan on tab100 b (cost=0.00..274.73 rows=8273 width=0) | | | | | |
-8852661820973207002 | explain (analyze, buffers) +| 2186412635 | Aggregate (cost=1027208.08..1027208.09 rows=1 width=8) +| 1 | 1 | 24.66 | 384 | 2023-12-05 17:34:03.126315+09 | 2023-12-05 17:34:03.126315+09
| SELECT count(*) FROM tab100 a, tab100 b+| | -> Nested Loop (cost=0.00..856101.76 rows=68442529 width=0) +| | | | | |
| ; | | -> Seq Scan on tab100 a (cost=0.00..274.73 rows=8273 width=0) +| | | | | |
| | | -> Materialize (cost=0.00..316.10 rows=8273 width=0) +| | | | | |
| | | -> Seq Scan on tab100 b (cost=0.00..274.73 rows=8273 width=0) | | | | | |
(2 rows)
- 컬럼 세로로 결과 출력(\x on)
-- 컬럼 세로 모드로 출력
test=# \x on
Expanded display is on.
SELECT s.queryid, s.query
, p.planid
, p.plan
, p.calls AS "plan calls"
, s.calls AS "stmt calls"
, round(s.mean_exec_time::numeric/1000,2) AS avg_exec_sec
, (s.shared_blks_hit + s.shared_blks_read) shared_blks_io
, p.first_call, p.last_call
FROM pg_stat_statements s, pg_store_plans p
WHERE s.queryid = p.queryid
;
-[ RECORD 1 ]--+------------------------------------------------------------------------------
queryid | -8852661820973207002
query | SELECT count(*) FROM tab100 a, tab100 b
planid | 2186412635
plan | Aggregate (cost=1027208.08..1027208.09 rows=1 width=8) +
| -> Nested Loop (cost=0.00..856101.76 rows=68442529 width=0) +
| -> Seq Scan on tab100 a (cost=0.00..274.73 rows=8273 width=0) +
| -> Materialize (cost=0.00..316.10 rows=8273 width=0) +
| -> Seq Scan on tab100 b (cost=0.00..274.73 rows=8273 width=0)
plan calls | 1
stmt calls | 6
avg_exec_sec | 5.34
shared_blks_io | 2304
first_call | 2023-12-05 17:34:03.126315+09
last_call | 2023-12-05 17:34:03.126315+09
-[ RECORD 2 ]--+------------------------------------------------------------------------------
queryid | -8852661820973207002
query | explain (analyze, buffers) +
| SELECT count(*) FROM tab100 a, tab100 b +
| ;
planid | 2186412635
plan | Aggregate (cost=1027208.08..1027208.09 rows=1 width=8) +
| -> Nested Loop (cost=0.00..856101.76 rows=68442529 width=0) +
| -> Seq Scan on tab100 a (cost=0.00..274.73 rows=8273 width=0) +
| -> Materialize (cost=0.00..316.10 rows=8273 width=0) +
| -> Seq Scan on tab100 b (cost=0.00..274.73 rows=8273 width=0)
plan calls | 1
stmt calls | 1
avg_exec_sec | 24.66
shared_blks_io | 384
first_call | 2023-12-05 17:34:03.126315+09
last_call | 2023-12-05 17:34:03.126315+09
기타
- pg_store_plans 테이블 데이터 초기화
| SELECT pg_store_plans_reset(); |
-- pg_store_plans 테이블 데이터 초기화
test=# SELECT pg_store_plans_reset();
-[ RECORD 1 ]--------+-
pg_store_plans_reset |
test=# select count(*) from pg_store_plans ;
count
-------
0
(1 row)
- pg_store_plans 뷰 컬럼 속성
| 컬럼명 | 속성 | 설명 |
| userid | oid | OID of user who executed the statement |
| dbid | oid | OID of database in which the statement was executed |
| queryid | bigint | Core-generated query ID. If compute_query_id is set to "no", pg_store_plan is silently disabled. This is usable as the join key with pg_stat_statements. |
| planid | bigint | Plan hash code, computed from the normalized plan representation. |
| plan | text | Text of a representative plan. The format is specified by the configuration parameter pg_store_plans.plan_format. |
| calls | bigint | Number of times executed |
| total_time | double precision | Total time spent in the statement using the plan, in milliseconds |
| rows | bigint | Total number of rows retrieved or affected by the statement using the plan |
| shared_blks_hit | bigint | Total number of shared block cache hits by the statement using the plan |
| shared_blks_read | bigint | Total number of shared blocks read by the statement using the plan |
| shared_blks_dirtied | bigint | Total number of shared blocks dirtied by the statement using the plan |
| shared_blks_written | bigint | Total number of shared blocks written by the statement using the plan |
| local_blks_hit | bigint | Total number of local block cache hits by the statement using the plan |
| local_blks_read | bigint | Total number of local blocks read by the statement using the plan |
| local_blks_dirtied | bigint | Total number of local blocks dirtied by the statement using the plan |
| local_blks_written | bigint | Total number of local blocks written by the statement using the plan |
| temp_blks_read | bigint | Total number of temp blocks read by the statement using the plan |
| temp_blks_written | bigint | Total number of temp blocks written by the statement using the plan |
| blk_read_time | double precision | Total time the statement using the plan spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
| blk_write_time | double precision | Total time the statement using the plan spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
| temp_blk_read_time | double precision | Total time the statement using the plan spent reading temporary file blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
| temp_blk_write_time | double precision | Total time the statement using the plan spent writing temporary file blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
| first_call | timestamp with time zone | Timestamp for the least recently call of the query using this plan. |
| last_call | timestamp with time zone | Timestamp for the most recently call of the query using this plan. |
- pg_store_plans 관련 파라미터
| 이름 | 기본값 | 설명 |
| pg_store_plans.log_analyze | off | Use EXPLAIN ANALYZE for plan logging. |
| pg_store_plans.log_buffers | off | Log buffer usage. |
| pg_store_plans.log_timing | on | Log timings. |
| pg_store_plans.log_triggers | off | Log trigger trace. |
| pg_store_plans.log_verbose | off | Set VERBOSE for EXPLAIN on logging. |
| pg_store_plans.max | 1000 | Sets the maximum number of plans tracked by pg_store_plans. |
| pg_store_plans.max_plan_length | 5000 | Sets the maximum length of plans stored by pg_store_plans. |
| pg_store_plans.min_duration | 0 | Minimum duration to record plan in milliseconds. |
| pg_store_plans.plan_format | text | Selects which format to be appied for plan representation in pg_store_plans. |
| pg_store_plans.plan_storage | file | Selects where to store plan texts. |
| pg_store_plans.save | on | Save pg_store_plans statistics across server shutdowns. |
| pg_store_plans.track | top | Selects which plans are tracked by pg_store_plans. |
'DBMS > PostgreSQL' 카테고리의 다른 글
| [PostgreSQL] unlogged/logged table 성능비교 (0) | 2023.12.14 |
|---|---|
| [PostgreSQL] 테이블 데이터 text 파일로 저장하기(COPY 명령) (0) | 2023.12.12 |
| [PostgreSQL] pg_basebackup 사용 백업/복구 테스트 (2) | 2023.11.24 |
| [PostgreSQL] 아카이브 모드 설정 (1) | 2023.11.19 |
| [PostgreSQL] pg_hint_plan 설치하여 SQL hint 사용 (0) | 2023.11.16 |
댓글