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

DB ์ธ์‚ฌ์ด๋“œ | PostgreSQL Vacuum - 4. Visibility Map

by EXEM 2022. 4. 29.

๐Ÿ“ข ๋ณธ ๋ฌธ์„œ์—์„œ๋Š” Visibility Map (์ดํ•˜ VM)์ด๋ผ๋Š” ๋ฉ”ํƒ€ ํŒŒ์ผ์— ๋Œ€ํ•œ ๋‚ด์šฉ์„ ๋‹ค๋ฃน๋‹ˆ๋‹ค. ์šฐ์„  Vacuum์˜ ๊ธฐ์ค€์ •๋ณด๋กœ ํ™œ์šฉํ•˜๋Š” โ‘ VM File์ด๋ž€ ๋ฌด์—‡์ธ์ง€ ์•Œ์•„๋ณด๊ณ , โ‘ก์‚ฌ์šฉ์ž ์กฐ์ž‘์— ๋”ฐ๋ผ ์–ด๋–ป๊ฒŒ ๋ณ€๊ฒฝ๋˜๋ฉฐ, ์–ด๋–ป๊ฒŒ ๊ด€์ฐฐํ•  ์ˆ˜ ์žˆ๋Š”์ง€ ์•Œ์•„๋ณด๋„๋ก ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

Visibility Map

VM File์ด๋ž€?

VM์ด๋ž€ Heap Relation(*ํ…Œ์ด๋ธ”)์„ ๊ตฌ์„ฑํ•˜๋Š” ๊ฐœ๋ณ„ ํŽ˜์ด์ง€์˜ ์ƒํƒœ๋ฅผ 2๊ฐœ์˜ Bit๊ฐ’์œผ๋กœ ํ‘œํ˜„ํ•˜๋Š” ๋ฉ”ํƒ€์ •๋ณด ํŒŒ์ผ์ด๋ฉฐ ํŽ˜์ด์ง€๊ฐ€ ํฌํ•จํ•˜๋Š” Tuple(Row)๋“ค์˜ ์ƒํƒœ ์ •๋ณด๋ฅผ ์ถ•์•ฝํ•ด์„œ ๋‹ด๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.

VM ํŒŒ์ผ์€ Relation OID์— ํ•ด๋‹นํ•˜๋Š” ์ˆซ์ž์— ์ ‘๋ฏธ์–ด "_vm"์„ ๋ถ™์ธ ํ˜•ํƒœ๋กœ ๊ด€๋ฆฌ๋˜๋ฉฐ ์‚ฌ์šฉ์ž๊ฐ€ VACUUM ๋˜๋Š” VACUUM FREEZE์™€ ๊ฐ™์€ ๋ช…์‹œ์ ์ธ Vacuum์ž‘์—…์„ ์ˆ˜ํ–‰ํ•˜๊ฑฐ๋‚˜ Autovacuum Launcher Process์— ์˜ํ•ด Autovacuum ์ด ์ˆ˜ํ–‰๋  ๋•Œ์—๋„ ๊ฐฑ์‹ ๋ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ PostgreSQL์—์„œ Relation๊ณผ Page๋Š” ๊ฐ๊ฐ Table, Block์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค

VM์ด ๊ด€๋ฆฌํ•˜๋Š” ์ •๋ณด

VM์€ PostgreSQL 9.6 ์ด์ „ ๋ฒ„์ „๊นŒ์ง€ Data ํŽ˜์ด์ง€ ๋‹น 1 Bit์˜ Flag ๊ฐ’์„ ํ†ตํ•ด ALL_VISIBLE ์ •๋ณด๋งŒ์„ ํ‘œํ˜„ํ–ˆ์—ˆ์Šต๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ ์ดํ›„ ๋ฒ„์ „๋ถ€ํ„ฐ ์„ฑ๋Šฅ ํ–ฅ์ƒ์„ ๋ชฉ์ ์œผ๋กœ ํŽ˜์ด์ง€ ๋‹น 2 Bit๋กœ ํ™•์žฅํ•˜์—ฌ ALL_FROZEN ์ •๋ณด๊นŒ์ง€ ํ‘œํ˜„ํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ๊ฐ Bit๋“ค์ด ๋‚˜ํƒ€๋‚ด๋Š” ์˜๋ฏธ๋Š” ์•„๋ž˜์™€ ๊ฐ™์Šต๋‹ˆ๋‹ค.

1st Bit : ALL_VISIBLE

ํŽ˜์ด์ง€์— ํฌํ•จ๋œ Row๊ฐ€ ๋ชจ๋“  Transaction์— ๋ณด์ด๋Š” ์ƒํƒœ์ธ์ง€ ์—ฌ๋ถ€ (Boolean)๋ฅผ ๋‚˜ํƒ€๋‚ด๋ฉฐ, Dead Tuple(Row)์˜ ์กด์žฌ ์—ฌ๋ถ€๋ฅผ ์•Œ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์ด๋•Œ, ALL_VISIBLE=1(True)์ธ ๊ฒฝ์šฐ๋ผ๋ฉด ํ•ด๋‹น ํŽ˜์ด์ง€์˜ ๋ชจ๋“  Row๊ฐ€ Visible ํ•œ์ƒํƒœ๋กœ, Dead Tuple์ด ์—†๋Š” ์ƒํƒœ๋ฅผ ์˜๋ฏธํ•˜์ง€๋งŒ, 0(False)์ธ ๊ฒฝ์šฐ๋ผ๋ฉด Dead Tuple์ด ์กด์žฌํ•˜๊ฑฐ๋‚˜, ์•„์ง Vacuum์ด ์ˆ˜ํ–‰๋˜์ง€ ์•Š์•„ ๋ชจ๋ฅด๋Š” ์ƒํƒœ๊นŒ์ง€ ํฌํ•จ๋ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ PostgreSQL์—์„œ Row(Tuple)์€ Live ์ƒํƒœ์™€ Dead ์ƒํƒœ๋กœ ๋‚˜๋‰˜๋ฉฐ, Update/Delete๋“ฑ์— ์˜ํ•ด ๋” ์ด์ƒ ์ฐธ์กฐ๋˜์ง€ ์•Š์€ Tuple์„ Dead Tuple์ด๋ผ ๋ถ€๋ฆ…๋‹ˆ๋‹ค.

2nd Bit : ALL_FROZEN

ALL_VISIBLE ๊ฐ’์ด True์ธ ๊ฒฝ์šฐ์—๋งŒ ํ•ด๋‹น์‚ฌํ•ญ์ด ์žˆ์œผ๋ฉฐ, ํŽ˜์ด์ง€์— ํฌํ•จ๋œ ๋ชจ๋“  Row๊ฐ€ Freeze ๋˜์—ˆ๋Š”์ง€ ์—ฌ๋ถ€ (Boolean)๋ฅผ ํ‘œํ˜„ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ Freeze๋ž€ Frozen XID๋ผ๋Š” ํŠน์ˆ˜ํ•œ XID๋ฅผ ๋ถ€์—ฌํ•˜๋Š” ์ž‘์—…์„ ๋งํ•ฉ๋‹ˆ๋‹ค.

 

2 Bit๊ฐ’์œผ๋กœ ์ด๋ฃจ์–ด์ง„ VM์€ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋ชฉ์ ์œผ๋กœ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

  • VACUUM Performance : Vacuum์ˆ˜ํ–‰ ์‹œ ALL_VISIBLE ๊ฐ’์ด False์ธ ํŽ˜์ด์ง€๋งŒ ๋Œ€์ƒ์œผ๋กœ ํ•˜๊ธฐ ๋•Œ๋ฌธ์— ๋ถˆํ•„์š”ํ•œ ํŽ˜์ด์ง€์— ๋Œ€ํ•œ ์ ‘๊ทผ์„ ์ค„์ž„์œผ๋กœ์จ Vacuum์„ฑ๋Šฅ ํ–ฅ์ƒ
  • Freezing Performance : Transaction ID Wraparound๋ฅผ ํ”ผํ•˜๊ธฐ ์œ„ํ•ด Row๋“ค์„ Freezing ํ•˜๋Š” ์ž‘์—…์˜ ๊ฒฝ์šฐ ALL_FROZEN ๊ฐ’์ด True ์ธ ํŽ˜์ด์ง€๋ฅผ Skip ํ•˜๋ฏ€๋กœ ์ž‘์—… ์‹œ๊ฐ„์ด ๋‹จ์ถ•๋จ
  • Query Performance : Index Only Scan์‹œ (Covering Index) ๋Œ€์ƒ ํŽ˜์ด์ง€์˜ ALL_VISIBLE ๊ฐ’์ด True ์ธ ๊ฒฝ์šฐ Table Look-up์„ ํ”ผํ•  ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ SQL์˜ ์„ฑ๋Šฅ ํ–ฅ์ƒ
๐Ÿ“Œ ์•ˆํƒ€๊น๊ฒŒ๋„ PostgreSQL์—์„œ Index Only Scan์„ ์œ ๋„ํ•˜๊ธฐ ์œ„ํ•œ ์ ์ ˆํ•œ ๊ตฌ์„ฑ์„ ํ•˜์˜€๋”๋ผ๋„ Table Look-up์ด ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋Š” ์ฝ์–ด์•ผ ํ• , ํ˜น์€ ์ฝ์ง€ ๋ง์•„์•ผ ํ•  Row์ธ์ง€๋ฅผ ํŒ๋‹จํ•  ์ •๋ณด๊ฐ€ Index ์ž์ฒด์— ์กด์žฌํ•˜์ง€ ์•Š๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค. PostgreSQL์—์„œ๋Š” ์ด๋Ÿฌํ•œ ๋ฌธ์ œ๋ฅผ ํ”ผํ•˜๊ธฐ ์œ„ํ•ด SQL ์ˆ˜ํ–‰ ์‹œ์ ์— VM์˜ ALL_VISIBLE ๊ฐ’์„ ํ™•์ธํ•˜์—ฌ Table Look-up์—ฌ๋ถ€๋ฅผ ๊ฒฐ์ •ํ•˜๊ฒŒ ๋ฉ๋‹ˆ๋‹ค.

 

VM์˜ ๋ณ€ํ™”์™€ ๊ด€์ฐฐ

pg_visibility

VM์˜ 2 Bit๊ฐ’์€ pg_visibility๋ผ๋Š” Extension์„ ํ†ตํ•ด ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. pg_visibility('relation name') ํ˜•ํƒœ๋กœ ์‚ฌ์šฉ์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.

create table test_vm (c integer) ;

insert into test_vm select generate_series(1,1001) as c;

SELECT blkno, all_visible, all_frozen FROM pg_visibility('test_vm');
 blkno | all_visible | all_frozen |
-------+-------------+------------+
     0 | f           | f          |
     1 | f           | f          |
     2 | f           | f          |
     3 | f           | f          |
     4 | f           | f          |
(5 rows)

-- Freezing ์ž‘์—… ์ˆ˜ํ–‰

vacuum freeze test_vm

SELECT blkno, all_visible, all_frozen FROM pg_visibility('test_vm');
 blkno | all_visible | all_frozen |
-------+-------------+------------+
     0 | t           | t          |
     1 | t           | t          |
     2 | t           | t          |
     3 | t           | t          |
     4 | t           | t          |
(5 rows)

VM์˜ ๋ณ€ํ™”

pg_visibility๋ฅผ ํ†ตํ•ด ๊ด€์ฐฐ ๊ฐ€๋Šฅํ•œ VM์˜ 2 Bit๊ฐ’์€ DML ๋ฐ Vacuum ๊ด€๋ จ ์ž‘์—… ์‹œ ์•„๋ž˜ ๊ทธ๋ฆผ๊ณผ ๊ฐ™์ด ๋ณ€ํ™”ํ•˜๋ฉฐ, ์•„๋ž˜์™€ ๊ฐ™์€ ํŠน์ง•์„ ๊ฐ–์Šต๋‹ˆ๋‹ค.

  • Vacuum ๊ด€๋ จ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•˜์ง€ ์•Š์€ ์ตœ์ดˆ ์ƒ์„ฑ๋œ ํŽ˜์ด์ง€์˜ VM ๊ฐ’์€ [0,0]์ž…๋‹ˆ๋‹ค
  • Vacuum ๊ด€๋ จ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•œ ์ดํ›„ ํŽ˜์ด์ง€์˜ VM ๊ฐ’์€ [1,0] ๋˜๋Š” [1,1]์ž…๋‹ˆ๋‹ค.
  • Vacuum Freeze ์ž‘์—… ์‹œ ์ด์ „ ์ƒํƒœ์— ๊ด€๊ณ„์—†์ด [1,1]์˜ VM ์ƒํƒœ๊ฐ€ ๋ฉ๋‹ˆ๋‹ค.
  • DML ์ˆ˜ํ–‰ ์‹œ ์ด์ „ ์ƒํƒœ์— ๊ด€๊ณ„์—†์ด [0,0]์˜ VM ์ƒํƒœ๊ฐ€ ๋ฉ๋‹ˆ๋‹ค.
  • Vacuum Full ์ˆ˜ํ–‰ ์‹œ ์ด์ „ ์ƒํƒœ์— ๊ด€๊ณ„์—†์ด [0,0]์˜ VM ์ƒํƒœ๊ฐ€ ๋ฉ๋‹ˆ๋‹ค. (์žฌ๊ตฌ์„ฑ)

 

VM์„ ๋งˆ์ง€๋ง‰์œผ๋กœ Vacuum์„ ์ดํ•ดํ•˜๊ธฐ ์œ„ํ•œ ๊ธฐ๋ฐ˜ ์ง€์‹๋“ค์— ๋Œ€ํ•œ ์ •๋ฆฌ๋ฅผ ๋งˆ๋ฌด๋ฆฌํ•˜๋ฉฐ, ๋‚จ์€ ๋ฌธ์„œ๋“ค์—์„œ๋Š” ์‹ค์ œ Vacuum ๋ฐ Autovacuum์˜ ๋™์ž‘ ๊ณผ์ •์— ๋Œ€ํ•ด ๋‹ค๋ฃจ๋„๋ก ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

 

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

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

 

 

 

๋Œ“๊ธ€0