๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
์—‘์…ˆ ๊ฒฝ์Ÿ๋ ฅ/DB ์ธ์‚ฌ์ด๋“œ

DB ์ธ์‚ฌ์ด๋“œ | PostgreSQL Vacuum - 6. Autovacuum

by EXEM 2022. 4. 29.

๐Ÿ“ข Manual Vacuum์— ์ด์–ด, ๋ณธ ๋ฌธ์„œ ์—ญ์‹œ โ‘ Autovacuum์ด ๋ฌด์—‡์ธ์ง€ ์•Œ์•„๋ณด๊ณ , โ‘กAutovacuum์˜ ๋™์ž‘ ์กฐ๊ฑด ๋ฐ โ‘ขAutovacuum ํ”„๋กœ์„ธ์Šค์— ์˜ํ•ด ์ˆ˜ํ–‰๋˜๋Š” Anti-Wraparound Vacuum์— ๋Œ€ํ•ด ์•Œ์•„๋ณด๋„๋ก ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ๋งˆ์ง€๋ง‰์œผ๋กœ โ‘ฃAutovacuum ์ „์ฒด ํ”„๋กœ์„ธ์Šค์— ๋Œ€ํ•ด ํ™•์ธํ•ด๋ณด๋„๋ก ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

Autovacuum

Autovacuum์ด๋ž€ Vacuum์ˆ˜ํ–‰์„ ์ž๋™ํ™”ํ•ด์ฃผ๋Š” ๊ธฐ๋Šฅ์„ ์ด์•ผ๊ธฐํ•ฉ๋‹ˆ๋‹ค. Autovacuum๊ธฐ๋Šฅ์€ Autovacuum Daemon์ด๋ผ๊ณ  ๋ถˆ๋ฆฌ๋Š” ์—ฌ๋Ÿฌ ๊ฐœ์˜ ํ”„๋กœ์„ธ์Šค๋“ค์— ์˜ํ•ด ์ˆ˜ํ–‰๋˜๋ฉฐ, ์ด ์ค‘ Autovacuum Launcher ํ”„๋กœ์„ธ์Šค๋Š” Worker ํ”„๋กœ์„ธ์Šค๋ฅผ ๊ด€๋ฆฌํ•˜๊ณ  ์ง€์‹œํ•˜๋Š” ์—ญํ• ์„ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

Autovacuum์ด ๋™์ž‘ํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” AUTOVACUUM, TRACK_COUNTS Parameter๊ฐ€ ์ผœ์ ธ ์žˆ์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

์ด๋•Œ Autovacuum Launcher ํ”„๋กœ์„ธ์Šค๋Š” AUTOVACUUM_NAPTIME (Sec)์„ ์ฃผ๊ธฐ๋กœ ์ตœ๋Œ€ AUTOVACUUM_MAX_WORKERS ๊ฐœ์ˆ˜๋งŒํผ์˜ Worker ํ”„๋กœ์„ธ์Šค๋ฅผ ๊นจ์›Œ์„œ Vacuum์ž‘์—…์„ ์ˆ˜ํ–‰ํ•˜๊ฒŒ ๋ฉ๋‹ˆ๋‹ค.

Parameter  Default Value
autovacuum on
track_counts on
autovacuum_naptime 60
autovacuum_max_workers 3
๐Ÿ“Œ ๋‹จ, Cluster ๋‚ด์— ํ•œ ๊ฐœ ์ด์ƒ(N)์˜ Database๋ฅผ ์šด์˜ํ•  ๊ฒฝ์šฐ ๋™์ž‘ ์ฃผ๊ธฐ์™€ ํ”„๋กœ์„ธ์Šค๊ฐ€ N๊ฐœ๋กœ ๋ถ„์‚ฐ๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ฆ‰, Database๋ฅผ ์ถ”๊ฐ€ํ•˜๋Š” ๊ฒฝ์šฐ ์ดˆ๊ธฐ ๊ด€๋ จ Parameter์˜ ์ฆ๊ฐ€๋„ ๊ณ ๋ คํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

Trigger

Autovacuum์˜ ๋Œ€์ƒ์ด ๋˜๋Š” ์กฐ๊ฑด์€ ํ…Œ์ด๋ธ”์˜ ๋ณ€ํ™”๋Ÿ‰์ด ์ •ํ•ด์ง„ ์ž„๊ณ„์น˜๋ฅผ ์ดˆ๊ณผํ•œ ๊ฒฝ์šฐ์ž…๋‹ˆ๋‹ค.

์•ž์„œ TRACK_COUNTS๋ฅผ on์œผ๋กœ ์„ค์ •ํ•ด์•ผ ํ•˜๋Š” ์ด์œ ๊ฐ€ ์—ฌ๊ธฐ ์žˆ๋Š”๋ฐ, ํ•ด๋‹น Parameter๋ฅผ ์ผฐ์„ ๋•Œ DML์— ์˜ํ•œ ๋ณ€ํ™”๋Ÿ‰์„ ์ถ”์ ํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ ์ด๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ Autovacuum์˜ ๋Œ€์ƒ ์—ฌ๋ถ€๋ฅผ ํŒ๋ณ„ํ•  ์ˆ˜ ์žˆ๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค.

TRACK_COUNTS๋ฅผ ํ‚จ ์ƒํƒœ์—์„œ Table์˜ ์ „์ฒด Row์ˆ˜์™€, DML๋ฐœ์ƒ๋Ÿ‰์„ ์กฐํšŒํ•˜๋Š” ๋ฐฉ๋ฒ•์€ ๋‹ค์Œ๊ณผ ๊ฐ™์Šต๋‹ˆ๋‹ค.

# ์ด Row ์ˆ˜ ์กฐํšŒ
select
    relname,
    reltuples
from
    pg_class
where
    relname = 'test_vm'

relname|reltuples|
-------+---------+
test_vm|   4004.0|

# DML๋ฐœ์ƒ๋Ÿ‰
select
    relname ,
    n_tup_ins , --Insert Rows
    n_tup_del , --Delete Rows
    n_tup_upd , -- Update Rows
    last_autovacuum 
from
    pg_stat_all_tables
where
    relname = 'test_vm'

relname|n_tup_ins|n_tup_del|n_tup_upd|last_autovacuum              |
-------+---------+---------+---------+-----------------------------+
test_vm|     1001|        0|        0|2022-04-05 13:47:54.920 +0900|

 

์ด๋ ‡๊ฒŒ ์ธก์ •๋œ pg_stat_all_tables.n_tup_xxx ๊ฐ’๋“ค์ด ์•„๋ž˜ ๊ณต์‹์— ์˜ํ•ด ๊ณ„์‚ฐ๋œ ์ˆ˜์น˜๋ณด๋‹ค ๋†’์€ ๊ฒฝ์šฐ Autovacuum์˜ ๋Œ€์ƒ์œผ๋กœ ์„ ์ •๋ฉ๋‹ˆ๋‹ค.

# Delete + Update ๋ฐœ์ƒ Rows์™€ ์•„๋ž˜ ๊ณต์‹ ๋น„๊ต
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples

# Insert ๋ฐœ์ƒ rows์™€ ์•„๋ž˜ ๊ณต์‹ ๋น„๊ต
autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * pg_class.reltuples

์ž„๊ณ„์น˜ ๊ณต์‹์„ ์ž˜ ์‚ดํŽด๋ณด๋ฉด INSERT๋ผ๋Š” ํ‚ค์›Œ๋“œ๊ฐ€ ๋“ค์–ด๊ฐ„ ๊ณต์‹๊ณผ ์•„๋‹Œ ๊ฒƒ์œผ๋กœ ๋‚˜๋‰˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

๊ทธ ์ด์œ ๋Š” 13 ๋ฒ„์ „ ์ด์ „๊นŒ์ง€ Insert์กฐ์ž‘๊ณผ ๊ด€๋ จ๋œ Autovacuum Parameter๊ฐ€ ์กด์žฌํ•˜์ง€ ์•Š์•˜๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค. ์ฆ‰, 13 ์ด์ „ ๋ฒ„์ „์—์„œ๋Š” Delete์™€ Update ๋“ฑ Dead Tuple์ด ๋ฐœ์ƒํ•˜์ง€ ์•Š๋Š” Insert-Only Table์˜ ๊ฒฝ์šฐ Autovacuum์˜ ๋Œ€์ƒ์ด ๋  ์ˆ˜ ์—†๋Š” ๊ณ ์งˆ์ ์ธ ๋ฌธ์ œ๊ฐ€ ์žˆ์—ˆ์œผ๋ฉฐ Manual Vacuum์„ ํ†ตํ•œ ์‚ฌ์šฉ์ž ๊ด€๋ฆฌ๊ฐ€ ๋ถˆ๊ฐ€ํ”ผํ–ˆ์Šต๋‹ˆ๋‹ค.

ํ•˜์ง€๋งŒ 13 ๋ฒ„์ „๋ถ€ํ„ฐ ๋“ฑ์žฅํ•œ Parameter์ธ AUTOVACUUM_VACUUM_INSERT_SCALE_FACTOR, AUTOVACUUM_VACUUM_INSERT_THRESHOLD ๋•๋ถ„์— Insert-Only Table ์—ญ์‹œ Autovacuum์— ๋Œ€ํ•œ Trigger๊ฐ€ ๊ฐ€๋Šฅํ•ด์กŒ์Šต๋‹ˆ๋‹ค.

Parameter  Default Value
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
autovacuum_vacuum_insert_scale_factor 0.2
autovacuum_vacuum_insert_threshold 10000

 

Anti-Wraparound Vacuum

์•ž์„œ Autovacuum์„ ์‚ฌ์šฉํ•˜๊ธฐ ์œ„ํ•œ ์ „์ œ์กฐ๊ฑด์œผ๋กœ AUTOVACUUM, TRACK_COUNTS๋ฅผ ์ด์•ผ๊ธฐํ–ˆ์Šต๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ, ํ•ด๋‹น Parameter๋ฅผ Disable ์‹œํ‚ค๋”๋ผ๋„ ํŠน์ • ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๊ฒฝ์šฐ Autovacuum์— ์˜ํ•œ ์ž๋™ Vacuum์€ ์ˆ˜ํ–‰๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์‚ฌ์šฉ์ž๊ฐ€ ์ฃผ๊ธฐ์ ์œผ๋กœ Vacuum์„ ์ˆ˜ํ–‰ํ•˜์ง€๋„ ์•Š๊ณ , Autovacuum๊ธฐ๋Šฅ ์—ญ์‹œ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋Š” ํ™˜๊ฒฝ์ด ์žˆ๋‹ค๊ณ  ๊ฐ€์ •ํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ๊ณต๊ฐ„๊ด€๋ฆฌ์™€ ์„ฑ๋Šฅ ํ–ฅ์ƒ์ด๋ผ๋Š” Vacuum์˜ ์„ ํ•œ ์˜ํ–ฅ๋ ฅ์€ ๋‘˜์งธ ์น˜๋”๋ผ๋„, Database์˜ ์ •ํ•ฉ์„ฑ์„ ํ›ผ์†์‹œํ‚ฌ ์ˆ˜ ์žˆ๋Š” XID Wraparound ์ƒํ™ฉ์„ ๋ฏธ์—ฐ์— ๋ฐฉ์ง€ํ•˜๊ธฐ ์œ„ํ•œ ์ตœ์†Œํ•œ์˜ ์•ˆ์ „์žฅ์น˜๋Š” ํ•„์š”ํ•ด ๋ณด์ž…๋‹ˆ๋‹ค.

 

์ด๋ฅผ ์œ„ํ•ด PostgreSQL์€ Autovacuum๊ธฐ๋Šฅ์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋”๋ผ๋„ ํŠน์ • ์ƒํ™ฉ ๋ฐœ์ƒ ์‹œ Autovacuum ํ”„๋กœ์„ธ์Šค์— ์˜ํ•œ Anti-Wraparound Vacuum์„ ์ž๋™์œผ๋กœ ์ˆ˜ํ–‰ํ•˜๊ฒŒ ํ•˜์˜€์œผ๋ฉฐ, ํ•ด๋‹น ์กฐ๊ฑด์€ Table์˜ Age๊ฐ€ AUTOVACUUM_FREEZE_MAX_AGE๋ฅผ ์ดˆ๊ณผํ•œ ์‹œ์ ์ž…๋‹ˆ๋‹ค.

AUTOVACUUM_FREEZE_MAX_AGE๋Š” ์•ž์„œ Eager Mode์™€ Lazy Mode์˜ ์„ ํƒ ๊ธฐ์ค€์œผ๋กœ ์ด์•ผ๊ธฐํ•œ VACUUM_FREEZE_TABLE_AGE๋ณด๋‹ค ํฐ ์ˆ˜์น˜์ด๋ฉฐ XID Wraparound์ƒํ™ฉ์„ ์ €์ง€ํ•˜๊ธฐ ์œ„ํ•œ ๋งˆ์ง€๋ง‰ ๋ฐฉ์–ด ๋ผ์ธ์œผ๋กœ ๋ณผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Parameter  Default Value
autovacuum_freeze_max_age 200000000
vacuum_freeze_table_age 150000000
๐Ÿ“Œ vacuum_freeze_table_age๊ฐ€ autovacuum_freeze_max_age๋ณด๋‹ค ์ปค์งˆ ์ˆ˜ ์žˆ๋‹ค๋ฉด Manual Vacuum์˜ Eager Mode๋Š” ์˜๋ฏธ๊ฐ€ ์—†์–ด์ง‘๋‹ˆ๋‹ค. ์ด๋Ÿฌํ•œ ์ด์œ ๋กœ vacuum_freeze_table_age๋ฅผ ์•„๋ฌด๋ฆฌ ํฌ๊ฒŒ ๋ณ€๊ฒฝํ•ด๋„ autovacuum_freeze_max_age์˜ 95% ๋กœ ์ œํ•œ๋ฉ๋‹ˆ๋‹ค.

 

Autovacuum Process

Autovacuum์˜ ๋™์ž‘ ๊ณผ์ •์„ ์ •๋ฆฌํ•˜๋ฉด ์•„๋ž˜์™€ ๊ฐ™์Šต๋‹ˆ๋‹ค. Manual Vacuum๊ณผ ๋น„์Šทํ•˜์ง€๋งŒ ๋” ๋งŽ์€ Parameter์— ์˜ํ•ด ์ œ์–ด๋จ์„ ์•Œ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Parameter  Default Value
vacuum_freeze_min_age 50000000
vacuum_freeze_table_age 150000000
autovacuum_freeze_max_age 200000000
autovacuum on
track_count on
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
autovacuum_vacuum_insert_scale_factor 0.2
autovacuum_vacuum_insert_threshold 10000

์ž„๊ณ„์น˜ ๊ณ„์‚ฐ ๊ณต์‹

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples
autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_ scale_factor * pg_class.reltuples

 

๊ธฐํš ๋ฐ ๊ธ€ | ๊ธฐ์ˆ ๊ธฐํšํŒ€

์ด๋ฏธ์ง€ ์ œ์ž‘ | ๋””์ž์ธ๊ทธ๋ฃน ์ด๋ฏผ์„

 

 

 

 

๋Œ“๊ธ€4