공부/DATABASE
PostgreSQL Auto Vacuum 정리
무는빼주세요
2025. 4. 19. 19:11
개요
라이브 운영 중 Vacuum 으로 인한 DB CPU 상승을 확인하여 Vacuum에 대해 세부 내용을 파악하고 대책을 마련하고자함
문제 상황
서비스에서 CPU가 증가하며 비교적 증가 시간이 유지되는 상황 확인.
| READ,WRITE IOPS | CPU |
 |
 |
| Performance Insights | Log |
 |
 |
확인 내용
AUTO VACUUM은 3가지 동작 방식이 있다.
1. 일반 AUTOVACUUM
로그 : automatic vacuum table of
2. TXID 관리를 위한 AUTOVACUUM
로그 : automatic vacuum to prevent wraparound table of
3. Eager Mode로 동작하는 AUTO VACUUM
로그 : automatic aggressive vacuum table of
AUTO VACUUM 트리거
아래 3개의 상황 중 하나를 만족할 경우 AUTO VACUUM이 동작합니다.
- age(relfrozenxid) ≥ min(autovacuum_freeze_max_age(클러스터 단위 파라메터), autovacuum_freeze_max_age(테이블 단위 파라메터) (TXID 를 관리하기 위하여 발생하며 AUTOVACUUM 을 비활성화 하더라도 해당 조건에 해당하면 AUTO VACUUM은 동작한다)
- Vacuum threshold = n_ins_since_vacuum (마지막 배큠 이후 입력된 데이터 수) >autovacuum_vacuum_insert_scale_factor * 튜플 + autovacuum_vacuum_insert_threshold (PG 버전 >= 13)
- Vaccum threshold = dead_tuple (데드 튜플)> autovacuum_vacuum_scale_factor * 튜플 + autovacuum_vacuum_threshold
※ Analyze 트리거 Analyze Threshold = mod_since_analyze (마지막 분석 이후 수정된 데이터 수)> autovacuum_analyze_scale_factor * 튜플 + autovacuum_analyze_threshold
AUTO VACUUM 처리 관련 파라메터
autovacuum_max_workers : PostgreSQL 서버가 한번에 최대 돌 수 있는 AUTO VACUUM 수 (기본 값은 3으로 한번에 최대 3개의 테이블 처리가 되며 각각의 AUTO VACUUM 은 싱글 프로세스로 처리가 됨)
autovacuum_naptime : autovacuum 실행 사이의 최소 지연 시간
autovacuum_vacuum_cost_limit : AutoVacuum(Vacuum) 이 한 번 수행될 때 마다 사용할 수 있는 코스트 수
autovacuum_vacuum_cost_delay : AutoVacuum이 autovacuum_vacuum_cost_limit 만큼 완료되면 다음 AutoVacuum은 이 몇 밀리초 동안 sleep
vacuum_cost_page_hit : shared_buffer에 있는 데이터를 Vacuum할 때마다 cost 사용 수치
vacuum_cost_page_miss : 디스크 영역에 있는 데이터를 Vacuum할 때마다 cost 사용 수치
vacuum_cost_page_dirty : Dead Tuple을 Vacuum할 때마다 cost 사용 수치
※
max_parallel_maintenance_workers : 하나의 병렬 처리에 사용 가능한 프로세스 수 (수동 VACUUM, CREATE INDEX 등의 명령어를 수행)
max_parallel_workers : 전체 병렬 처리가 가능한 프로세스의 수
만약 max_parallel_workers = 8, max_parallel_maintenance_workers = 2 라고 가정하면
최대 4개의 Maintenance 작업이 2개 프로세스로 병렬 처리가 가능함
Lazy, Eager VACUUM 의 차이
Lazy Vacuum : 가시성 맵의 0번 비트 (ALL_VISIBLE) 이 0인 페이지만 조회하여 vacuum_freeze_min_age 보다 XMIN이 큰 튜플에 대하여 freeze 처리 합니다.
Eager Vacuum : 가시성 맵의 1번 비트 (ALL Frozen)이 0인 페이지에 대하여 조회하여 vacuum_freeze_min_age 보다 XMIN이 큰 튜플에 대하여 freeze 처리 합니다.
Eager Vacuum 이 동작하는 경우는 다음과 같습니다.
- Auto Vacuum : min(vacuum_freeze_table_age, autovacuum_freeze_max_age(클러스터 단위 파라메터) * 0.95) < age(relfrozenxid), min(autovacuum_freeze_table_age, autovacuum_freeze_max_age(테이블 단위 파라메터) * 0.95) < age(relfrozenxid),
- 수동 Vacuum : vacuum freeze 명령어 사용 (vacuum_freeze_min_age = 0 으로 변경된 후 vacuum 실행), min(vacuum_freeze_table_age, autovacuum_freeze_max_age(클러스터 단위 파라메터) * 0.95) < age(relfrozenxid)
Lazy 모드 작동 예
Eager 모드 작동 예
테스트 내용
| 테스트 주제 | 확인하고자 하는 것 | 테스트 방법 | 결과 | 비고 |
| AUTO VACUUM 트리거 설정에 따른 CPU 등의 차이 | 트리거 값을 별도 조정 없는 상태에서 1개 테이블 장기 부하를 진행하여 CPU 증가 폭 및 쿼리 지연을 확인 |
별도 설정 없이 테이블 반복 입력 3억건 테이블에 입력 부하 진행 |
데이터가 커질 수록 베큠의 시간은 증가하나 최대 사용 CPU는 의미 있는 증가를 확인하기 어려움 | autovacuum_vacuum_insert_scale_factor 의 기본 값은 0.2 로 테이블의 20%에 해당하는 데이터가 입력되면 vacuum 동작 autovacuum_analyze_scale_factor 기본 값은 0.05로 테이블의 5%에 해당하는 데이터가 입력되면 통계 확인 입력 autovacuum_vacuum_insert_scale_factor autovacuum_vacuum_insert_threshold 데드 튜플 autovacuum_vacuum_scale_factor autovacuum_vacuum_threshold 통계 업데이트 autovacuum_analyze_scale_factor autovacuum_analyze_threshold |
| 트리거 값을 낮게 조정하여 빈번한 VACUUM이 발생하게 설정한 후 1개 테이블에 장기 부하를 진행하여 CPU 증가폭 및 쿼리 지연을 확인 |
테이블 별 500만건 입력시 AUTO VACUUM 작동하도록 설정 alter table set (autovacuum_vacuum_insert_scale_factor = 0.0); alter table set (autovacuum_vacuum_insert_threshold = 5000000); |
데이터 규모와 상관 없이 일관된 CPU 증가와 처리 시간을 확인할 수 있음 | ||
| Aggressive Vacuum 의 동작 시기 | Aggressive vacuum 이 어떤 조건에서 발생하는지 확인하고자 합니다 | 아래 상황에서 VACUUM 은 Eager Mode로 진행됩니다. age(relfrozenxid) ≥ min(vacuum_freeze_table_age, autovacuum_freeze_max_age * 0.95) |
vacuum_freeze_table_age (기본값 150,000,00) autovacuum_freeze_max_age (기본값 200,000,000 |
|
| VACUUM 작업간 테이블 AGE 변화 | VACUUM 작업에서 테이블의 AGE가 변경되는 상황을 확인하고자 합니다 | Lazy Mode 로 동작할 때 ALL_VISIBLE 비트가 1인 페이지만 처리하기 때문에 가장 나이가 오래된 튜플이 ALL_VISIBLE = 1 인 페이지에 있다면 FROZEN 대상이 되지 않아서 VACUUM 이후에도 테이블 나이가 줄어들지 않음 |
AUTO VACUUM 트리거 설정에 따른 CPU 등의 차이
→ 수억 단위의 테이블에 대하여 autovacuum_vacuum_insert_scale_factor = 0 으로 지정한 후 autovacuum_vacuum_insert_threshold 값으로 처리.
CPU 변화 추이
동일한 부하를 입력하였을 때 데이터가 커지면서 처리해야할 튜플수가 증가하여 VACUUM 작업 시간이 길어지고 CPU 상승 시간이 길어지는 것 확인
트리거 값을 조정하여 5,000,000 건 입력마다 튜플이 작동하게 하였을 때 VACUUM 빈도는 증가하지만 처리해야할 튜플 수는 줄어들어 CPU 증가가 너무 길게 지속되는 것은 방지 가능
Aggressive Vacuum 의 동작 시기
aggressive Vacuum은 Eager 모드로 스캔해야하는 페이지가 증가하여 VACUUM 시간을 더 길게 유지하게 됨
아래 상황에서 Auto VACUUM 은 Aggressive Mode로 진행됩니다.
age(relfrozenxid) ≥ min(vacuum_freeze_table_age, autovacuum_freeze_max_age * 0.95)
※ age 함수는 현재 txid 와 변수간의 차이를 의미 합니다. ex : age(relfrozenxid) = txid_current() (postgresql에서 가장 최신의 txid) - relfrozenxid (테이블의 가장 오래된 txid)
인터넷에 다양한 자료가 있었지만 다들 조금씩 내용이 다르고 실제로 테스트하였을 때에도 약간씩 다른게 보여서 실제 PostgreSQL 소스 코드를 바탕으로 AutoVacuum이 발생할 때 로그에 남는 2가지 추가적인 방식에 대하여 발동 조건을 확인하고자 하였다.
when every page of the table that might contain unfrozen XIDs is scanned.
This happens when relfrozenxid is more than vacuum_freeze_table_age transactions old, when VACUUM's FREEZE option is used, or when all pages that are not already all-frozen happen to require vacuuming to remove dead row versions.
When VACUUM scans every page in the table that is not already all-frozen, it should set age(relfrozenxid) to a value just a little more than the vacuum_freeze_min_age setting that was used (more by the number of transactions started since the VACUUM started).
VACUUM will set relfrozenxid to the oldest XID that remains in the table, so it's possible that the final value will be much more recent than strictly required.
If no relfrozenxid-advancing VACUUM is issued on the table until autovacuum_freeze_max_age is reached, an autovacuum will soon be forced for the table.
controls when a table is aggressively vacuumed.
All all-visible but not all-frozen pages are scanned if the number of transactions that have passed since the last such scan is greater than vacuum_freeze_table_age minus vacuum_freeze_min_age.
Setting vacuum_freeze_table_age to 0 forces VACUUM to always use its aggressive strategy.
The maximum time that a table can go unvacuumed is two billion transactions minus the vacuum_freeze_min_age value at the time of the last aggressive vacuum.
If it were to go unvacuumed for longer than that, data loss could result. To ensure that this does not happen, autovacuum is invoked on any table that might contain unfrozen rows with XIDs older than the age specified by the configuration parameter autovacuum-freeze-max-age
https://github.com/postgres/postgres/blob/dbd437e670b88ca67f6b2477e853412a0e82a8cc/doc/src/sgml/maintenance.sgml#L639
--automatic to prevent, automatic aggressive 로깅 조건
else if (params->is_wraparound)
{
/*
* While it's possible for a VACUUM to be both is_wraparound
* and !aggressive, that's just a corner-case -- is_wraparound
* implies aggressive. Produce distinct output for the corner
* case all the same, just in case.
*/
if (vacrel->aggressive)
msgfmt = _("automatic aggressive vacuum to prevent wraparound of table \"%s.%s.%s\": index scans: %d\n");
else
msgfmt = _("automatic vacuum to prevent wraparound of table \"%s.%s.%s\": index scans: %d\n");
}
else
{
if (vacrel->aggressive)
msgfmt = _("automatic aggressive vacuum of table \"%s.%s.%s\": index scans: %d\n");
else
msgfmt = _("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n");
}
https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c
--wraparound 조건
/* Force vacuum if table is at risk of wraparound */
xidForceLimit = recentXid - freeze_max_age; (=autovacuum_freeze_max_age)
if (xidForceLimit < FirstNormalTransactionId)
xidForceLimit -= FirstNormalTransactionId;
relfrozenxid = classForm->relfrozenxid;
force_vacuum = (TransactionIdIsNormal(relfrozenxid) && TransactionIdPrecedes(relfrozenxid, xidForceLimit));
if (!force_vacuum)
{
MultiXactId relminmxid = classForm->relminmxid;
multiForceLimit = recentMulti - multixact_freeze_max_age;
if (multiForceLimit < FirstMultiXactId)
multiForceLimit -= FirstMultiXactId;
force_vacuum = MultiXactIdIsValid(relminmxid) &&
MultiXactIdPrecedes(relminmxid, multiForceLimit);
}
*wraparound = force_vacuum;
#define TransactionIdIsNormal(xid) ((xid) >= FirstNormalTransactionId)
bool transactionIdPrecedes(TransactionId id1, TransactionId id2)
{
/*
* If either ID is a permanent XID then we can just do unsigned
* comparison. If both are normal, do a modulo-2^32 comparison.
*/
int32 diff;
if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2))
return (id1 < id2);
diff = (int32) (id1 - id2);
return (diff < 0);
}
relfrozenxid < recentxid - freeze_max_age
freeze_max_age < recentxid - relfrozenxid
autovacuum_freeze_max_age < age(relfrozenxid) --> wraparound = true
is_wraparound = true -> autovacuum_freeze_max_age < age(relfrozenxid)
https://github.com/postgres/postgres/blob/dbd437e670b88ca67f6b2477e853412a0e82a8cc/src/backend/postmaster/autovacuum.c#L2829
해석해보면
is_wraparound = true -> autovacuum_freeze_max_age < age(relfrozenxid)
is_wraparound 는 auto vacuum 에서만 발생
/* user-invoked vacuum is never "for wraparound" */
params.is_wraparound = false;
https://github.com/postgres/postgres/blob/master/src/backend/commands/vacuum.c#L1102
--aggressive 조건
/* Aggressive VACUUM? (must set relfrozenxid >= FreezeLimit) */
bool aggressive;
vacrel->aggressive = vacuum_get_cutoffs(rel, params, &vacrel->cutoffs);
if (freeze_table_age < 0)
freeze_table_age = vacuum_freeze_table_age;
freeze_table_age = Min(freeze_table_age, autovacuum_freeze_max_age * 0.95);
Assert(freeze_table_age >= 0);
aggressiveXIDCutoff = nextXID - freeze_table_age;
if (!TransactionIdIsNormal(aggressiveXIDCutoff))
aggressiveXIDCutoff = FirstNormalTransactionId;
if (TransactionIdPrecedesOrEquals(cutoffs->relfrozenxid, aggressiveXIDCutoff))
return true;
relfrozenxid < nextXID - freeze_table_age = (Min(freeze_table_age, autovacuum_freeze_max_age * 0.95))
-> Min(freeze_table_age, autovacuum_freeze_max_age * 0.95) < age(relfrozenxid)
if (params->options & VACOPT_DISABLE_PAGE_SKIPPING)
{
/*
* Force aggressive mode, and disable skipping blocks using the
* visibility map (even those set all-frozen)
*/
vacrel->aggressive = true;
skipwithvm = false;
}
즉 aggressive 조건은
Min(freeze_table_age, autovacuum_freeze_max_age * 0.95) < age(relfrozenxid)
https://github.com/postgres/postgres/blob/master/src/backend/commands/vacuum.c#L1102
-- 테스트 1 autovacuum_freeze_max_age, vacuum_freeze_table_age 모다 큰 TABLE AGE에서 VACUUM 모드 확인
autovacuum_freeze_max_age : age가 가장 큰 테이블 2개의 사이값인 176,000,000 (가장 큰 테이블인 nxpt_batch_job_states_by_event만 autovacuum이 동작할 것)
vacuum_freeze_table_age : 150,000,000 (해당 값보다 age가 큰 경우 aggressive vacuum 으로 동작할 것)
vacuum_freeze_min_age : 100,000,000 (vacuum 이후 테이블의 age가 100,000,001 이 될 것)
Schema | Name | relfrozenxid | age
--------+----------------------------------+--------------+-----------
public | testTable | 105194471 | 177024253 -- autovacuum 테이블
show autovacuum_freeze_max_age;
autovacuum_freeze_max_age
---------------------------
176000000
show vacuum_freeze_table_age;
vacuum_freeze_table_age
-------------------------
150000000
show vacuum_freeze_min_age;
vacuum_freeze_min_age
-----------------------
100000000
-- 테스트 결과
-- aggressive vacuum이 동작하며 relfrozenxid 증가와 TABLE AGE 감소를 볼 수 있음
2025-03-28 00:54:24 UTC::@:[590]:LOG: automatic aggressive vacuum to prevent wraparound of table "db.public.testTable": index scans: 1
pages: 0 removed, 7047904 remain, 0 skipped due to pins, 3683056 skipped frozen
tuples: 0 removed, 95580850 remain, 0 are dead but not yet removable, oldest xmin: 282230786
buffer usage: 12286734 hits, 1954 misses, 0 dirtied
avg read rate: 0.033 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 90.49 s, system: 1.29 s, elapsed: 458.05 s
WAL usage: 0 records, 0 full page images, 0 bytes
Schema | Name | relfrozenxid | age
--------+----------------------------------+--------------+-----------
public | testTable | 194230786 | 88001692 -- autovacuum 테이블
테스트 2 -- autovacuum_freeze_max_age와 autovacuum_freeze_max_age 사이의 TABLE AGE 에서 VACUUM 모드 확인
autovacuum_freeze_max_age -> 170000000
vacuum_freeze_table_age -> 175000000
alter table testTable set (vacuum_freeze_table_age = 175000000);
alter table testTable set (autovacuum_freeze_max_age = 170000000);
Schema | Name | relfrozenxid | age
--------+----------------------------------+--------------+-----------
public | testTable | 107857525 | 174374953 -- 대상 테이블
vacuum_freeze_table_age < table_age < autovacuum_freeze_max_age
--결과 aggressive vacuum이 동작하며 relfrozenxid 증가와 TABLE AGE 감소를 볼 수 있음
2025-03-28 06:40:27 UTC::@:[21613]:LOG: automatic aggressive vacuum to prevent wraparound of table "db.public.testTable": index scans: 0
pages: 0 removed, 2319303 remain, 0 skipped due to pins, 737616 skipped frozen
tuples: 0 removed, 138196589 remain, 0 are dead but not yet removable, oldest xmin: 282263019
buffer usage: 3169966 hits, 576 misses, 0 dirtied
avg read rate: 0.034 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 26.04 s, system: 0.40 s, elapsed: 133.50 s
WAL usage: 0 records, 0 full page images, 0 bytes
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+-----------
public | testTable | 194263019 | 88000400 -- autovacuum 테이블
-- 테스트 결과
-- 즉 AUTO VACUUM 트리거 중 하나인 AGE(relfrozenxid) > autovacuum_freeze_max_age 인 상황은 항상 aggressive vacuum 이 발생하는 것을 확인 가능
테스트 3 데이터 베이스 나이와 aggressive vacuum 발생 영향도 확인
-- 3-1 데이터 베이스 나이 > VACUUM_FREEZE_TABLE_AGE > 테이블 나이
datname | txid_current | datfrozenxid | age
----------------+--------------+--------------+-----------
db | 282277031 | 109292612 | 172984419
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+-----------
public | testTABLE | 151794361 | 130482701 -- 대상 테이블
172984419 (데이터 베이스 나이) > 13048270 (테이블 나이) > 150,000,000 (vacuum_freeze_table_age)
--수동 베큠 실행 Lazy Mode로 수행이 됨
VACUUM VERBOSE testTABLE
INFO: vacuuming "public.testTABLE"
INFO: "testTABLE": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282277237
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: vacuuming "pg_toast.pg_toast_24824"
INFO: "pg_toast_24824": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282277237
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+-----------
public | testTABLE | 194277237 | 88000036 -- 대상 테이블
-- 3-2 데이터 베이스 나이 > 테이블 나이 > VACUUM_FREEZE_TABLE_AGE
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+-----------
public | testTABLE | 109292612 | 172984450 -- 대상 테이블
172984766 (데이터 베이스 나이) > 172984450 (테이블 나이) > 150,000,000 ( vacuum_freeze_table_age )
-- 수동 베큠 실행 Eager Mode로 수행이 됨
db=> vacuum (verbose) testTABLE;
INFO: aggressively vacuuming "public.testTABLE"
INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO: "testTABLE": found 0 removable, 148233524 nonremovable row versions in 4093831 out of 10781495 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282279264
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 6687664 frozen pages.
0 pages are entirely empty.
CPU: user: 47.18 s, system: 0.62 s, elapsed: 114.72 s.
INFO: vacuuming "pg_toast.pg_toast_24810"
INFO: "pg_toast_24810": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282279430
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+-----------
public | testTABLE | 194279264 | 88000212
-- 테스트 결과
-- Aggressive Vacuum 은 각 테이블 별 나이에 따라서 결정 됨
테스트 4-1 auto vacuum 에서 to prevent wraparound 작동 시점, autovacuum_freeze_max_age의 테이블, 클러스터 단위 파라미터 설정 작동 차이점 vacuum_freeze_table_age 값 상관 관계
vacuum_freeze_table_age = 150,000,000
autovacuum_freeze_max_age(파라메터 설정) = 100,000,000
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+----------
public | testTABLE | 282344088 | 4990237
alter table testTABLE set (autovacuum_freeze_max_age = 4950000); --> 테이블 단위 autovacuum_freeze_max_age 파라미터 조정하여 auto vacuum 발생하도록 유도
2025-04-07 01:29:11 UTC::@:[22951]:LOG: automatic vacuum to prevent wraparound of table "db.public.testTABLE": index scans: 0
pages: 0 removed, 103190 remain, 0 skipped due to pins, 93221 skipped frozen
tuples: 0 removed, 2591389 remain, 0 are dead but not yet removable, oldest xmin: 287334325
buffer usage: 129 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
WAL usage: 0 records, 0 full page images, 0 bytes
--> automatic vacuum to prevent wraparound 동작
db=> vacuum verbose testTABLE;
INFO: vacuuming "public.testTABLE"
INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO: "testTABLE": found 0 removable, 2 nonremovable row versions in 1 out of 103190 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 287334409
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 93221 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.03 s, elapsed: 0.04 s.
INFO: vacuuming "pg_toast.pg_toast_24840"
INFO: "pg_toast_24840": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 287334409
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--> 수동 vacuum 에서 일반 vacuum 으로 동작 min(vacuum_freeze_table_age, autovacuum_freeze_max_age*0.95) 공식이 클러스터 파라미터 단위로 작동하는 것으로 추측 가능
set vacuum_freeze_table_age = 4950000;
--> vacuum_freeze_table_age 값을 낮추어 aggressive vacuum 유도 (파라미터 수정)
automatic aggressive vacuum to prevent wraparound of table "db.public.testTABLE": index scans: 0
pages: 0 removed, 103190 remain, 0 skipped due to pins, 93221 skipped frozen
tuples: 0 removed, 2599540 remain, 0 are dead but not yet removable, oldest xmin: 287334562
buffer usage: 20109 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 0.14 s, system: 0.02 s, elapsed: 0.90 s
WAL usage: 0 records, 0 full page images, 0 bytes
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+----------
public | testTABLE | 287333562 | 1049
--> automatic aggressive to prevent wraparound vacuum 동작 확인
테스트 4-2
vacuum_freeze_table_age = 150,000,000
autovacuum_freeze_max_age(파라메터 설정) = 100,000,000
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+----------
public | testTABLE | 194279264 | 95132833 -- 대상 테이블
vacuum verbose testTABLE;
INFO: aggressively vacuuming "public.testTABLE"
alter table testTABLE set (autovacuum_freeze_max_age = 95000000);
2025-04-07 04:06:40 UTC::@:[32135]:LOG: automatic aggressive vacuum to prevent wraparound of table "db.public.testTABLE": index scans: 0
pages: 0 removed, 10781495 remain, 0 skipped due to pins, 7507616 skipped frozen
tuples: 0 removed, 390219013 remain, 0 are dead but not yet removable, oldest xmin: 289412251
buffer usage: 6561760 hits, 2711 misses, 0 dirtied
avg read rate: 0.070 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 46.92 s, system: 1.89 s, elapsed: 300.51 s
WAL usage: 0 records, 0 full page images, 0 bytes
Schema | Name | relfrozenxid | age
--------+-----------------------------------------+--------------+----------
public | testTABLE | 239412251 | 50000488 -- 대상 테이블
vacuum_freeze_table_age = 150,000,000
autovacuum_freeze_max_age(파라메터 설정) = 100,000,000
테이블의 age가 95,000,000 을 넘겼을 때 수동 vacuum 이 aggressive 동작 -> min(vacuum_freeze_table_age , autovacuum_freeze_max_age *0.95)
autovacuum_freeze_max_age(테이블 단위 설정) = 95,000,000 으로 변경
--> auto vacuum이 aggressive + to prevent wraparound 로 동작
위 내용을 바탕으로 auto vacuum에서 aggressive 한 조건은 min(vacuum_freeze_table_age, autovacuum_freeze_max_age(클러스터 파라미터)*0.95) 로 확인 가능.
to prevent wraparound 는 테이블 단위의 조정으로 트리거 됨
VACUUM 작업간 테이블 AGE 변화
Lazy Mode에서도 테이블 AGE가 변화하는 것에 대하여 확인하고자 하였습니다.
→ Lazy Mode 로 동작할 때 ALL_VISIBLE 비트만 보고 처리하기 때문에 가장 나이가 오래된 튜플이 ALL_VISIBLE = 1 인 페이지에 있다면 FROZEN 대상이 되지 않아서 VACUUM 이후에도 테이블 나이가 줄어들지 않음
→ 결국 Aggressive Vacuum 이 동작하는 것을 원천 차단은 불가능한 케이스가 있을 것으로 보이나 VACUUM 작업이 되면서 ALL_FROZEN = 'F' 인 값이 줄어들게 할 순 있음.
→ Aggressive Vacuum 이 CPU를 장기간 유지하는 것을 방지하기 위해선 vacuum_freeze_table_age, vacuum_freeze_min_age 값을 낮추고 대상 튜플을 줄일 수 있습니다.
→ 해당 상황은 입력만 발생하는 테이블에서 좀 더 의미가 있는 설정으로 생각됩니다.
--VACUUM 이후 FREEZE 되지 않는 케이스가 TABLE AGE에 영향을 주는 것이라고 추측이 되어 AGE(XMIN)를 추적하여 진행을 해보았습니다.
--가용성 맵을 확인하기 위해 Extension을 추가합니다.
CREATE EXTENSION pg_visibility_map;
-- pg_visibility 함수를 사용하여 테이블의 가용성 맵 상태를 확인할 수 있습니다.
select * from pg_visibility('partition_date_test_1') limit 10;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | t | t | t
1 | t | t | t
2 | t | t | t
3 | t | t | t
4 | t | t | t
5 | t | t | t
6 | t | t | t
7 | t | t | t
8 | t | t | t
9 | t | t | t
--blkno = 블록 번호, all_visible (0번 비트), all_frozen (1번 비트)
-- partition_date_test_1 테이블에 입력을 진행하고 VACUUM 을 진행하여 AGE 변화를 확인합니다.
-- 먼저 VACUUM FREEZE 를 통하여 테이블 AGE를 0으로 낮춥니다.
vacuum freeze partition_date_test_1;
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,852
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 0
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282643035 | 11
public | test_table2 | 282517562 | 125484
-- 확인을 용이하게 하기 위해 vacuum_freeze_min_age 값을 조정합니다.
set vacuum_freeze_min_age = 1000;
-- 데이터 2000건 입력
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282643035 | 2053
public | test_table2 | 282517562 | 127526
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,840
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 12
--FROZEN 되지 않은 튜플이 모드 ALL_VISIBLE = 'F' 인 페이지에 존재합니다.
--VACUUM 진행
db=> vacuum verbose partition_date_test_1;
INFO: vacuuming "public.partition_date_test_1"
INFO: "partition_date_test_1": found 0 removable, 2124 nonremovable row versions in 13 out of 1852 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282645116
There were 444 unused item identifiers.
Skipped 0 pages due to buffer pins, 1839 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282644116 | 1004
public | test_table2 | 282517562 | 127558
-- 일반 VACUUM 이 동작한 후 테이블의 AGE가 감소하였습니다.
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,846
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 6
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 0
-- FROZEN 되지 않은 튜플이 모드 ALL_VISIBLE = 'T' 인 페이지에 존재합니다.
-- 데이터 2000건 입력
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282644116 | 3044
public | test_table2 | 282517562 | 129598
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,835
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 5
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 12
-- VACUUM 진행
db=> vacuum verbose partition_date_test_1;
INFO: vacuuming "public.partition_date_test_1"
INFO: "partition_date_test_1": found 0 removable, 2114 nonremovable row versions in 13 out of 1852 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282647177
There were 355 unused item identifiers.
Skipped 0 pages due to buffer pins, 1834 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282644116 | 3064
public | test_table2 | 282517562 | 129618
-- 일반 VACUUM 이후 테이블 AGE가 감소하지 않았습니다.
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,840
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 12
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 0
-- ALL_VISIBLE = 'T' AND ALL_FROZEN = 'F' 인 TUPLE 을 찾아서 UPDATE를 진행하여 ALL_VISIBLE = 'F' AND ALL_VISIBLE ='F' 로 변경합니다.
select * from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
6 | t | f | t
7 | t | f | t
8 | t | f | t
9 | t | f | t
10 | t | f | t
16 | t | f | t
17 | t | f | t
18 | t | f | t
19 | t | f | t
20 | t | f | t
21 | t | f | t
22 | t | f | t
select count(*), min(col1), max(col1) from partition_date_test_1 where ctid >'(6,0)' and ctid <'(23,0)';
count | min | max
-------+--------+--------
2975 | 173005 | 175979
update partition_date_test_1 set col2 ='2024-12-31 09:07:17' WHERE col1 >=173005 and col1 <=175979;
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,819
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 33
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282644116 | 7825
public | test_table2 | 282517562 | 134379
-- 해당 상황에서 VACUUM 을 진행합니다.
db=> vacuum verbose partition_date_test_1;
INFO: vacuuming "public.partition_date_test_1"
INFO: scanned index "partition_date_test_1_pkey" to remove 2975 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: "partition_date_test_1": removed 2975 row versions in 17 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "partition_date_test_1_pkey" now contains 175915 row versions in 1987 pages
DETAIL: 2975 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "partition_date_test_1": found 2975 removable, 3032 nonremovable row versions in 34 out of 1852 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282651957
There were 624 unused item identifiers.
Skipped 0 pages due to buffer pins, 1818 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
Schema | Name | relfrozenxid | age
--------+-----------------------+--------------+--------
public | partition_date_test_1 | 282650957 | 1004
public | test_table2 | 282517562 | 134399
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 't' ; -- 1,835
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 't' and all_frozen = 'f' ; -- 17
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 't' ; -- 0
select count(*) from pg_visibility('partition_date_test_1') where all_visible = 'f' and all_frozen = 'f' ; -- 0
-- 테이블 AGE가 감소하였습니다.