PostgreSQL은 테이블에 INSERT/UPDATE/DELETE 등이 발생할 때 변경사항을 WAL에 기록합니다.
테이블 변경 사항을 logging 하지 않으려고 할 때 unlogged 옵션을 사용 할 수 있습니다.
- WAL에 변경사항을 로깅하지 않아 일반 테이블보다 좋은 성능
- 테이블 문제 발생 시 복구 불가능
- unlogged → logged 변경 시 로그 발생 및 테이블 lock(SELECT 불가)
- replication 구성 환경에서 read 노드로 복제 안됨
- 중요하지 않은 임시 데이터 같은 경우 쓸만할 듯
1. unlogged ↔ logged 변경 명령어
| -- unlogged로 변경 alter table {테이블명} set unlogged ; -- logged로 변경 alter table {테이블명} set logged ; |
2. insert 테스트
1) 인덱스 없이 insert
100만건 테이블 생성
CREATE UNLOGGED TABLE tmp1 AS
SELECT
gs as idx,
'test String' || gs AS test_string,
md5(random()::text) AS random_string
FROM
generate_series(1, 1000000) AS gs;
test=# select count(*) from tmp1 ;
count
---------
1000000
(1 row)
Time: 101.169 ms
test=# select * from tmp1 limit 3 ;
idx | test_string | random_string
-----+--------------+----------------------------------
1 | test String1 | c733eb2ccee1b4cf99f493e9a66f9469
2 | test String2 | 3ad09b7a0717969a209838fd0e853c57
3 | test String3 | b7426fc002d31b0ec0fbf2a9c4d9b6a4
(3 rows)
-- unlogged/logged 테이블 생성
test=# create table log_t as select * from tmp1 where 1=0 ;
SELECT 0
Time: 17.790 ms
sk1=# create unlogged table unlog_t as select * from tmp1 where 1=0 ;
SELECT 0
Time: 19.643 ms
-- 사이즈 조회
SELECT nspname,
relname,
relpersistence,
pg_size_pretty(pg_relation_size(C.oid)) as size,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
and relname like '%log%'
ORDER BY pg_relation_size(C.oid) DESC
;
nspname | relname | relpersistence | size | total_size
---------+---------+----------------+---------+------------
public | log_t | p | 0 bytes | 8192 bytes
public | unlog_t | u | 0 bytes | 16 kB
(2 rows)
unlogged 테이블 insert
100만건 insert 시 WAL 0개 사용, 수행시간 0.3초
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000009
(1 row)
Time: 0.249 ms
-- unlogged table insert
test=# insert into unlog_t select * from tmp1 ;
INSERT 0 1000000
Time: 341.279 ms
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000009
(1 row)
Time: 0.365 ms
logged 테이블 insert
100만건 insert 시 WAL 6개 (96MB)사용, 수행시간 2초
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000009
(1 row)
-- insert
test=# insert into log_t select * from tmp1 ;
INSERT 0 1000000
Time: 2499.762 ms (00:02.500)
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
00000001000000000000000F
(1 row)
Time: 0.276 ms
-- WAL 정보 확인 (6개 로그 사용됨)
test=# select * from pg_ls_waldir() ;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000009 | 16777216 | 2023-12-12 14:21:33+09
00000001000000000000000A | 16777216 | 2023-12-12 14:21:33+09
00000001000000000000000B | 16777216 | 2023-12-12 14:21:33+09
00000001000000000000000C | 16777216 | 2023-12-12 14:21:33+09
00000001000000000000000D | 16777216 | 2023-12-12 14:21:34+09
00000001000000000000000E | 16777216 | 2023-12-12 14:21:34+09
00000001000000000000000F | 16777216 | 2023-12-12 14:21:35+09
(7 rows)
Time: 0.506 ms
-- 사이즈 조회
test=# SELECT nspname,
test-# relname,
test-# relpersistence,
test-# pg_size_pretty(pg_relation_size(C.oid)) as size,
test-# pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
test-# FROM pg_class C
test-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
test-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
test-# AND nspname !~ '^pg_toast'
test-# and relname like '%log%'
test-# ORDER BY pg_relation_size(C.oid) DESC
test-# ;
nspname | relname | relpersistence | size | total_size
---------+---------+----------------+-------+------------
public | log_t | p | 81 MB | 81 MB
public | unlog_t | u | 81 MB | 81 MB
(2 rows)
Time: 14.268 ms2) 인덱스 생성 후 insert 테스트
인덱스 생성
인덱스 로깅 속성은 테이블 따라감
test=# select count(*) from log_t ;
count
-------
0
(1 row)
Time: 0.740 ms
test=# select count(*) from unlog_t ;
count
-------
0
(1 row)
-- 인덱스 생성
-- unlogged table에 생성한 인덱스는 unlogged 상태
test=# create index log_t_ix1 on log_t(random_string) ;
CREATE INDEX
Time: 18.725 ms
test=# create index unlog_t_ix1 on unlog_t(random_string) ;
CREATE INDEX
Time: 28.428 ms
test=# SELECT nspname,
test-# relname,
test-# relpersistence,
test-# pg_size_pretty(pg_relation_size(C.oid)) as size,
test-# pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
test-# FROM pg_class C
test-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
test-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
test-# AND nspname !~ '^pg_toast'
test-# and relname like '%log%'
test-# ORDER BY pg_relation_size(C.oid) DESC
test-# ;
nspname | relname | relpersistence | size | total_size
---------+-------------+----------------+------------+------------
public | log_t_ix1 | p | 8192 bytes | 8192 bytes
public | unlog_t_ix1 | u | 8192 bytes | 16 kB
public | log_t | p | 0 bytes | 16 kB
public | unlog_t | u | 0 bytes | 32 kB
(4 rows)
Time: 1.084 ms
unlogged 테이블 insert
100만건 insert 시 WAL 0개 사용, 수행시간 4초
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
00000001000000000000000F
(1 row)
Time: 0.222 ms
-- unlogged table insert
test=# insert into unlog_t select * from tmp1 ;
INSERT 0 1000000
Time: 4891.764 ms (00:04.892)
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
00000001000000000000000F
(1 row)
Time: 0.342 ms
logged 테이블 insert
100만건 insert 시 WAL 12개(192MB) 사용, 수행시간 13초
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000084
(1 row)
Time: 0.213 ms
test=#
test=# insert into log_t select * from tmp1 ;
INSERT 0 1000000
Time: 13538.927 ms (00:13.539)
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000093
(1 row)
Time: 0.271 ms
test=#
-- WAL 정보 확인
test=# select * from pg_ls_waldir() ;
name | size | modification
--------------------------+----------+------------------------
...
000000010000000000000084 | 16777216 | 2023-12-13 10:27:41+09
000000010000000000000086 | 16777216 | 2023-12-13 10:27:43+09
000000010000000000000087 | 16777216 | 2023-12-13 10:27:44+09
000000010000000000000088 | 16777216 | 2023-12-13 10:27:45+09
000000010000000000000089 | 16777216 | 2023-12-13 10:27:46+09
00000001000000000000008A | 16777216 | 2023-12-13 10:27:47+09
00000001000000000000008B | 16777216 | 2023-12-13 10:27:48+09
00000001000000000000008C | 16777216 | 2023-12-13 10:27:49+09
00000001000000000000008D | 16777216 | 2023-12-13 10:27:50+09
00000001000000000000008E | 16777216 | 2023-12-13 10:27:50+09
00000001000000000000008F | 16777216 | 2023-12-13 10:27:52+09
000000010000000000000090 | 16777216 | 2023-12-13 10:27:52+09
000000010000000000000091 | 16777216 | 2023-12-13 10:27:54+09
000000010000000000000092 | 16777216 | 2023-12-13 10:27:54+09
000000010000000000000093 | 16777216 | 2023-12-13 10:27:56+09
000000010000000000000094 | 16777216 | 2023-12-12 15:23:04+09
000000010000000000000095 | 16777216 | 2023-12-12 15:24:48+09
000000010000000000000096 | 16777216 | 2023-12-12 15:24:48+09
00000001000000000000007F | 16777216 | 2023-12-13 10:25:08+09
000000010000000000000081 | 16777216 | 2023-12-13 10:25:10+09
000000010000000000000083 | 16777216 | 2023-12-13 10:25:12+09
000000010000000000000085 | 16777216 | 2023-12-13 10:27:42+09
(34 rows)
Time: 0.420 ms
test=#
test=# SELECT nspname,
test-# relname,
test-# relpersistence,
test-# pg_size_pretty(pg_relation_size(C.oid)) as size,
test-# pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
test-# FROM pg_class C
test-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
test-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
test-# AND nspname !~ '^pg_toast'
test-# and relname like '%log%'
test-# ORDER BY pg_relation_size(C.oid) DESC
test-# ;
nspname | relname | relpersistence | size | total_size
---------+-------------+----------------+-------+------------
public | unlog_t | p | 81 MB | 137 MB
public | log_t | p | 81 MB | 154 MB
public | log_t_ix1 | p | 74 MB | 74 MB
public | unlog_t_ix1 | p | 56 MB | 56 MB
(4 rows)
Time: 13.067 ms
반응형
3. unlogged <-> logged 변경
unlogged ↔ logged로 테이블 속성 변경 테스트
logged로 변경
; logged 테이블에 데이터를 적재하는 것과 비슷하게 로그를 사용하고 시간이 소요 되었음
; WAL 10개 사용, 수행시간 11초
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
00000001000000000000001E
(1 row)
-- alter table
test=# alter table unlog_t set logged ;
ALTER TABLE
Time: 11206.760 ms (00:11.207)
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000028
(1 row)
Time: 0.344 ms
-- WAL 정보 확인(10개 로그 사용됨)
test=# select * from pg_ls_waldir() ;
name | size | modification
--------------------------+----------+------------------------
...
00000001000000000000001E | 16777216 | 2023-12-12 14:41:12+09
00000001000000000000001F | 16777216 | 2023-12-12 14:41:12+09
000000010000000000000020 | 16777216 | 2023-12-12 14:41:13+09
000000010000000000000021 | 16777216 | 2023-12-12 14:41:14+09
000000010000000000000022 | 16777216 | 2023-12-12 14:41:15+09
000000010000000000000023 | 16777216 | 2023-12-12 14:41:17+09
000000010000000000000024 | 16777216 | 2023-12-12 14:41:17+09
000000010000000000000025 | 16777216 | 2023-12-12 14:41:19+09
000000010000000000000026 | 16777216 | 2023-12-12 14:41:20+09
000000010000000000000027 | 16777216 | 2023-12-12 14:41:21+09
000000010000000000000028 | 16777216 | 2023-12-12 14:41:33+09
(26 rows)
Time: 12.420 ms
test=# SELECT nspname,
test-# relname,
test-# relpersistence,
test-# pg_size_pretty(pg_relation_size(C.oid)) as size,
test-# pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
test-# FROM pg_class C
test-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
test-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
test-# AND nspname !~ '^pg_toast'
test-# and relname like '%log%'
test-# ORDER BY pg_relation_size(C.oid) DESC
test-# ;
nspname | relname | relpersistence | size | total_size
---------+-------------+----------------+-------+------------
public | log_t | p | 81 MB | 154 MB
public | unlog_t | p | 81 MB | 137 MB
public | log_t_ix1 | p | 74 MB | 74 MB
public | unlog_t_ix1 | p | 56 MB | 56 MB
(4 rows)
Time: 0.931 ms
unlogged로 변경
WAL 0개 사용, 수행시간 2초
test=# alter table unlog_t set unlogged ;
ALTER TABLE
Time: 2664.238 ms (00:02.664)
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000028
(1 row)
Time: 0.343 ms
-- 사이즈 조회
test=# SELECT nspname,
test-# relname,
test-# relpersistence,
test-# pg_size_pretty(pg_relation_size(C.oid)) as size,
test-# pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
test-# FROM pg_class C
test-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
test-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
test-# AND nspname !~ '^pg_toast'
test-# and relname like '%log%'
test-# ORDER BY pg_relation_size(C.oid) DESC
test-# ;
nspname | relname | relpersistence | size | total_size
---------+-------------+----------------+-------+------------
public | log_t | p | 81 MB | 154 MB
public | unlog_t | u | 81 MB | 137 MB
public | log_t_ix1 | p | 74 MB | 74 MB
public | unlog_t_ix1 | u | 56 MB | 56 MB
(4 rows)
Time: 13.755 ms
다시 logged로 변경
WAL 10개 사용, 수행시간 6초
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000028
(1 row)
-- alter table
test=# alter table unlog_t set logged ;
ALTER TABLE
Time: 6075.491 ms (00:06.075)
-- 현재 WAL 파일 확인
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000000000032
(1 row)
Time: 0.395 ms
-- WAL 정보 확인(10개 로그 사용됨)
test=# select * from pg_ls_waldir() ;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000030 | 16777216 | 2023-12-12 14:49:25+09
000000010000000000000031 | 16777216 | 2023-12-12 14:49:25+09
000000010000000000000032 | 16777216 | 2023-12-12 14:49:26+09
...
000000010000000000000028 | 16777216 | 2023-12-12 14:49:20+09
000000010000000000000029 | 16777216 | 2023-12-12 14:49:20+09
00000001000000000000002A | 16777216 | 2023-12-12 14:49:21+09
00000001000000000000002B | 16777216 | 2023-12-12 14:49:21+09
00000001000000000000002C | 16777216 | 2023-12-12 14:49:21+09
00000001000000000000002D | 16777216 | 2023-12-12 14:49:22+09
00000001000000000000002E | 16777216 | 2023-12-12 14:49:22+09
00000001000000000000002F | 16777216 | 2023-12-12 14:49:25+09
(26 rows)
Time: 12.879 ms
unlogged <-> logged 반복 수행
test=# alter table unlog_t set logged ;
ALTER TABLE
Time: 7530.221 ms (00:07.530)
test=# alter table unlog_t set unlogged ;
ALTER TABLE
Time: 4699.298 ms (00:04.699)
test=# alter table unlog_t set logged ;
ALTER TABLE
Time: 8728.388 ms (00:08.728)
test=# alter table unlog_t set unlogged ;
ALTER TABLE
Time: 4856.248 ms (00:04.856)
test=# alter table unlog_t set logged ;
ALTER TABLE
Time: 7560.224 ms (00:07.560)
4. unlogged → logged 변경 시 table lock 상태
로깅 속성 변경 시 table lock 발생하여 다른 세션에서 select 불가
액티브 세션
dt | datname | pid | usesysid | usename | tx_act_time | sql_act_time | state | state_change | wait_event_type | wait_event | application_name | backend_type | client_hostname | query | query_start | lock_holder_pid | backend_xid | backend_xmin
---------------------+---------+-------+----------+----------+-------------+--------------+--------+-----------------------+-----------------+------------+------------------+----------------+-----------------+----------------------------------+-------------------------------+-----------------+-------------+--------------
2023-12-12-15:00:04 | sk1 | 18553 | 10 | postgres | 00:00:00 | 00:00:00 | active | 2023-12-12 15:00:03of | IO | WALSync | psql | client backend | localhost | alter table unlog_t set logged ; | 2023-12-12 15:00:03.968203+09 | | 951 | 951
(1개 행)
dt | datname | pid | usesysid | usename | tx_act_time | sql_act_time | state | state_change | wait_event_type | wait_event | application_name | backend_type | client_hostname | query | query_start | lock_holder_pid | backend_xid | backend_xmin
---------------------+---------+-------+----------+----------+-------------+--------------+--------+-----------------------+-----------------+------------+------------------+----------------+-----------------+---------------------------------------+-------------------------------+-----------------+-------------+--------------
2023-12-12-15:00:05 | sk1 | 23645 | 10 | postgres | 00:00:00 | 00:00:00 | active | 2023-12-12 15:00:04of | Lock | relation | psql | client backend | localhost | select now(), count(*) from unlog_t ; | 2023-12-12 15:00:04.469336+09 | | | 951
2023-12-12-15:00:05 | sk1 | 18553 | 10 | postgres | 00:00:01 | 00:00:01 | active | 2023-12-12 15:00:03of | IO | WALSync | psql | client backend | localhost | alter table unlog_t set logged ; | 2023-12-12 15:00:03.968203+09 | | 951 | 951
(2개 행)
dt | datname | pid | usesysid | usename | tx_act_time | sql_act_time | state | state_change | wait_event_type | wait_event | application_name | backend_type | client_hostname | query | query_start | lock_holder_pid | backend_xid | backend_xmin
---------------------+---------+-------+----------+----------+-------------+--------------+--------+-----------------------+-----------------+------------+------------------+-----------------+-----------------+---------------------------------------+-------------------------------+-----------------+-------------+--------------
2023-12-12-15:00:06 | sk1 | 23669 | 10 | postgres | 00:00:02 | 00:00:02 | active | 2023-12-12 15:00:05of | | | psql | parallel worker | localhost | alter table unlog_t set logged ; | 2023-12-12 15:00:03.968203+09 | | | 951
2023-12-12-15:00:06 | sk1 | 23645 | 10 | postgres | 00:00:02 | 00:00:02 | active | 2023-12-12 15:00:04of | Lock | relation | psql | client backend | localhost | select now(), count(*) from unlog_t ; | 2023-12-12 15:00:04.469336+09 | | | 951
2023-12-12-15:00:06 | sk1 | 18553 | 10 | postgres | 00:00:02 | 00:00:02 | active | 2023-12-12 15:00:03of | | | psql | client backend | localhost | alter table unlog_t set logged ; | 2023-12-12 15:00:03.968203+09 | | 951 | 951
(3개 행)
dt | datname | pid | usesysid | usename | tx_act_time | sql_act_time | state | state_change | wait_event_type | wait_event | application_name | backend_type | client_hostname | query | query_start | lock_holder_pid | backend_xid | backend_xmin
---------------------+---------+-------+----------+----------+-------------+--------------+--------+-----------------------+-----------------+------------+------------------+----------------+-----------------+---------------------------------------+-------------------------------+-----------------+-------------+--------------
2023-12-12-15:00:07 | sk1 | 23645 | 10 | postgres | 00:00:03 | 00:00:03 | active | 2023-12-12 15:00:04of | Lock | relation | psql | client backend | localhost | select now(), count(*) from unlog_t ; | 2023-12-12 15:00:04.469336+09 | | | 951
2023-12-12-15:00:07 | sk1 | 18553 | 10 | postgres | 00:00:03 | 00:00:03 | active | 2023-12-12 15:00:03of | | | psql | client backend | localhost | alter table unlog_t set logged ; | 2023-12-12 15:00:03.968203+09 | | 951 | 951
(2개 행)
dt | datname | pid | usesysid | usename | tx_act_time | sql_act_time | state | state_change | wait_event_type | wait_event | application_name | backend_type | client_hostname | query | query_start | lock_holder_pid | backend_xid | backend_xmin
---------------------+---------+-------+----------+----------+-------------+--------------+--------+-----------------------+-----------------+------------+------------------+----------------+-----------------+---------------------------------------+-------------------------------+-----------------+-------------+--------------
2023-12-12-15:00:08 | sk1 | 23645 | 10 | postgres | 00:00:04 | 00:00:04 | active | 2023-12-12 15:00:04of | Lock | relation | psql | client backend | localhost | select now(), count(*) from unlog_t ; | 2023-12-12 15:00:04.469336+09 | | | 951
2023-12-12-15:00:08 | sk1 | 18553 | 10 | postgres | 00:00:04 | 00:00:04 | active | 2023-12-12 15:00:03of | IO | WALSync | psql | client backend | localhost | alter table unlog_t set logged ; | 2023-12-12 15:00:03.968203+09 | | 951 | 951
(2개 행)
lock 조회
schemaname | relname | locktype | page | virtualtransaction | pid | mode | granted
------------+--------------+----------+------+--------------------+-------+---------------------+---------
pg_catalog | pg_class | relation | | 11/303 | 23672 | AccessShareLock | t
pg_catalog | pg_index | relation | | 11/303 | 23672 | AccessShareLock | t
pg_catalog | pg_namespace | relation | | 11/303 | 23672 | AccessShareLock | t
public | unlog_t | relation | | 10/342 | 23669 | ShareLock | t
public | unlog_t | relation | | 9/1196 | 23645 | AccessShareLock | f
public | unlog_t | relation | | 8/70 | 18553 | ShareLock | t
public | unlog_t | relation | | 8/70 | 18553 | AccessExclusiveLock | t
(7개 행)
schemaname | relname | locktype | page | virtualtransaction | pid | mode | granted
------------+--------------+----------+------+--------------------+-------+---------------------+---------
pg_catalog | pg_class | relation | | 10/344 | 23682 | AccessShareLock | t
pg_catalog | pg_index | relation | | 10/344 | 23682 | AccessShareLock | t
pg_catalog | pg_namespace | relation | | 10/344 | 23682 | AccessShareLock | t
public | unlog_t | relation | | 9/1196 | 23645 | AccessShareLock | f
public | unlog_t | relation | | 8/70 | 18553 | ShareLock | t
public | unlog_t | relation | | 8/70 | 18553 | AccessExclusiveLock | t
(6개 행)
schemaname | relname | locktype | page | virtualtransaction | pid | mode | granted
------------+--------------+----------+------+--------------------+-------+---------------------+---------
pg_catalog | pg_class | relation | | 10/350 | 23697 | AccessShareLock | t
pg_catalog | pg_index | relation | | 10/350 | 23697 | AccessShareLock | t
pg_catalog | pg_namespace | relation | | 10/350 | 23697 | AccessShareLock | t
public | unlog_t | relation | | 9/1196 | 23645 | AccessShareLock | f
public | unlog_t | relation | | 8/70 | 18553 | ShareLock | t
public | unlog_t | relation | | 8/70 | 18553 | AccessExclusiveLock | t
(6개 행)
schemaname | relname | locktype | page | virtualtransaction | pid | mode | granted
------------+--------------+----------+------+--------------------+-------+-----------------+---------
pg_catalog | pg_class | relation | | 12/18 | 23714 | AccessShareLock | t
pg_catalog | pg_index | relation | | 12/18 | 23714 | AccessShareLock | t
pg_catalog | pg_namespace | relation | | 12/18 | 23714 | AccessShareLock | t
public | unlog_t | relation | | 10/357 | 23712 | AccessShareLock | t
public | unlog_t | relation | | 11/310 | 23711 | AccessShareLock | t
public | unlog_t | relation | | 9/1196 | 23645 | AccessShareLock | t
(6개 행)
lock wait 조회
select count(*) ... 세션이 alter table ... 작업이 끝나길 기다림(lock wait 발생)
pid | blocked_by | query | duration | state | usename | client_addr | application_name | terminate
-------+-------------+---------------------------------------+-----------+--------+----------+-------------+------------------+-------------------------------------
18553 | 18553 | alter table unlog_t set logged ; | 00:00:00s | active | postgres | | psql | select pg_terminate_backend(18553);
23645 | 18553>23645 | select now(), count(*) from unlog_t ; | 00:00:00s | active | postgres | | psql | select pg_terminate_backend(23645);
(2개 행)
pid | blocked_by | query | duration | state | usename | client_addr | application_name | terminate
-------+-------------+---------------------------------------+-----------+--------+----------+-------------+------------------+-------------------------------------
18553 | 18553 | alter table unlog_t set logged ; | 00:00:01s | active | postgres | | psql | select pg_terminate_backend(18553);
23645 | 18553>23645 | select now(), count(*) from unlog_t ; | 00:00:01s | active | postgres | | psql | select pg_terminate_backend(23645);
(2개 행)
pid | blocked_by | query | duration | state | usename | client_addr | application_name | terminate
-------+-------------+---------------------------------------+-----------+--------+----------+-------------+------------------+-------------------------------------
18553 | 18553 | alter table unlog_t set logged ; | 00:00:02s | active | postgres | | psql | select pg_terminate_backend(18553);
23645 | 18553>23645 | select now(), count(*) from unlog_t ; | 00:00:02s | active | postgres | | psql | select pg_terminate_backend(23645);
(2개 행)
pid | blocked_by | query | duration | state | usename | client_addr | application_name | terminate
-------+-------------+---------------------------------------+-----------+--------+----------+-------------+------------------+-------------------------------------
18553 | 18553 | alter table unlog_t set logged ; | 00:00:03s | active | postgres | | psql | select pg_terminate_backend(18553);
23645 | 18553>23645 | select now(), count(*) from unlog_t ; | 00:00:03s | active | postgres | | psql | select pg_terminate_backend(23645);
(2개 행)
pid | blocked_by | query | duration | state | usename | client_addr | application_name | terminate
-------+-------------+---------------------------------------+-----------+--------+----------+-------------+------------------+-------------------------------------
18553 | 18553 | alter table unlog_t set logged ; | 00:00:04s | active | postgres | | psql | select pg_terminate_backend(18553);
23645 | 18553>23645 | select now(), count(*) from unlog_t ; | 00:00:04s | active | postgres | | psql | select pg_terminate_backend(23645);
(2개 행)'DBMS > PostgreSQL' 카테고리의 다른 글
| [PostgreSQL] ANY 연산자 (1) | 2024.01.01 |
|---|---|
| [PostgreSQL] 자동 증가(Auto Increment) 컬럼 사용법 (1) | 2023.12.15 |
| [PostgreSQL] 테이블 데이터 text 파일로 저장하기(COPY 명령) (0) | 2023.12.12 |
| [PostgreSQL] SQL 실행계획 관리(pg_store_plans) (2) | 2023.12.05 |
| [PostgreSQL] pg_basebackup 사용 백업/복구 테스트 (2) | 2023.11.24 |
댓글