[book] PostgreSQL 9.6 성능 이야기 - vacuum

 

postgresql 중 vacuum 이란 것에 대해서 정리

 

• vaccum 이란 불필요한 데이터를 정리하고 압축해서 디스크공간의 효율성을 높인다
• 오래된 트랜잭션을 정리하는 작업도 수행

Multi-Version Concurrency Control = 동시에 여러 개의 데이터 버전을 제공할수 있는 기법

오라클의 MVCC와 postgresql MVCC의 차이

특성	PostgreSQL	Oracle
MVCC 구현 방식	같은 row의 여러 버전이 테이블에 함께 저장됨 Dead tuple은 vacuum으로 정리	Undo 세그먼트에 이전 버전 저장 자동으로 관리됨
공간 관리	vacuum 작업 필요 관리자의 주기적인 관리 필요 테이블 bloat 발생 가능	Undo 세그먼트 자동 관리 별도 관리 작업 불필요 테이블 크기 안정적
동시성 처리	읽기 작업이 쓰기를 차단하지 않음 높은 읽기 성능	Undo 세그먼트 경합 가능 대규모 트랜잭션 시 부하 발생
리소스 사용	Vacuum으로 인한 추가 부하 더 많은 저장 공간 필요	Undo 세그먼트 관리 오버헤드 더 효율적인 공간 사용
관리 복잡도	Vacuum 설정과 모니터링 필요 autovacuum 튜닝 필요	상대적으로 단순 Undo retention 설정만 필요

 

주요 차이점:

1. 구조적 차이:

• PostgreSQL: 테이블 내에 모든 버전 관리 <<  이 점이 가장 큰 차이점이면서 vaccum이 필요한 이유
• Oracle: 별도의 Undo 세그먼트에서 관리

1. 관리 측면:

• PostgreSQL: Vacuum 작업 필요, 더 많은 관리 필요
• Oracle: 자동 관리, 상대적으로 관리 부담 적음

1. 성능 특성:

• PostgreSQL: 읽기 작업에 최적화
• Oracle: 전반적으로 균형 잡힌 성능

 

---

Vaccum이 필요한 이유 

 

1. 이전 버전의 데이터를 테이블 블록 내에 저장하기 때문

 

postgresql 은 테이블내에 모든 버전의 데이터를 저장하므로 공간의 효율성이 떨어진다.

이를 해결하기위해서는 아래 2가지 방법이 존재한다.

 

--1. 이전 버전 데이터를 삭제 > 공간확보 
vaccum (freeze)

--2. 이전 버전 데이터를 삭제 + 공간 압축
vaccum full

 

2. 레코드별로 트랜잭션 ID를 관리하기 때문 

 

위에 있는 1의 이유로 레코드별로 XID를 관리해야한다.

그래야 시점에 맞는 데이터를 추출할 수 있다.

XID가 레코드별로 존재하기때문에 공간에 대한 비효율이 더 커진다.

그래서 XID는 4byte를 사용하기로 하였다.

하지만 여기서 문제점은 트랜잭션마다 +1이 증가하는데 4byte로 관리할 수 있는 트랜잭션의 갯수는 43억개이다.

50일 후에는 43억을 소진하게된다. 

 

소진 후 다시 1로 돌아가서 트랜잭션을 관리해야하는데 해결방법이 2가지

• 한바퀴 회전할 떄마다 wrap 번호를 증가시키기
• 한바퀴 회전하기전에 이전 xid를 특정값으로 변경, flag 처리 << postgresql가 채택한 방법

 

Frozen XID에 대해서 미리 알아야함,

 

postgresql 에는 3개 유형의 XID가 있다.

XID 유형	값	특징	용도
Bootstrap XID	1	데이터베이스 초기화 시에만 사용 시스템 카탈로그 생성에 사용 변경 불가능	데이터베이스 클러스터 초기화 기본 시스템 객체 생성 pg_class pg_attribute pg_type 등의 기본 시스템 테이블 생성
Frozen XID	2	Anti-wraparound Vacuum 영구적으로 유효한 상태 모든 트랜잭션에서 visible vacuum freeze의 결과	XID wraparound 방지 오래된 트랜잭션 처리 시스템 안정성 유지
Normal XID	3~	일반적인 트랜잭션에 사용 순차적으로 증가 재사용 가능	일반적인 데이터베이스 작업 CRUD 작업 트랜잭션 처리

 

-- Frozen 관련 파라미터 확인
SHOW vacuum_freeze_min_age;
SHOW vacuum_freeze_table_age;
SHOW autovacuum_freeze_max_age;

-- 테이블의 relfrozenxid 확인
SELECT relname, age(relfrozenxid) as xid_age, 
       pg_size_pretty(pg_total_relation_size(relname::regclass)) as size
FROM pg_class 
WHERE relkind = 'r' 
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY xid_age DESC;

-- 수동으로 freeze 실행
VACUUM (FREEZE, VERBOSE) table_name;

-- freeze 진행 상황 모니터링
SELECT pid, state, wait_event_type, query, xact_start
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%';

-- 데이터베이스별 oldest XID 확인
SELECT datname, age(datfrozenxid) as age
FROM pg_database
ORDER BY age DESC;

-- freeze 카운터 확인
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       vacuum_count, autovacuum_count,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY n_dead_tup DESC;

---

Vaccum

vaccum <테이블명> 

 

vaccum을 하면서 select, dml 작업을 할 수 있다.

하지만 vaccum full을 하면서 select, dml 작업을 할 수 없다. << select 하는 세션도 대기하게 된다.

 

vaccum 작업시에 redo는 생성하지 않는다.

vaccum freeze 시에 redo가 발생한다.

vaccum full시에 redo가 발생한다.

 

vaccum freeze와 vaccum full 의 차이

특성	VACUUM FREEZE	VACUUM FULL
주요 목적	- 트랜잭션 ID aging 방지 - XID wraparound 예방 - 오래된 트랜잭션 ID를 Frozen 상태로 변경	- 물리적 공간 회수 - 테이블 완전 재작성
테이블 잠금	- ACCESS SHARE lock - 읽기/쓰기 가능	-  ACCESS EXCLUSIVE lock - 읽기/쓰기 불가
디스크 공간	- OS에 반환하지 않음 - 빈 공간 재사용 가능	-  OS에 공간 반환 - 테이블 크기 최소화
수행 시간	- 상대적으로 빠름 - 온라인 수행 가능	-  매우 오래 걸림 - 전체 테이블 복사
부하	- 중간 정도 - 다른 작업 영향 적음	-  매우 높음 - I/O 부하 큼

 

 

• autovacuum을 적절히 설정
• 주기적인 ANALYZE 실행
• 대규모 DELETE 작업 후 수동 VACUUM 고려

 

---

AGE

-- 현재 트랜잭션 ID와의 차이가 age
age = current_xid - frozen_xid

 

postgresql은 데이터베이스, 테이블, 레코드 별로 나이를 관리한다.

트랜잭션이 발생할 때마다 age값이  +1 이 증가한다. 

 

나이순으로 확인해보면 데이터베이스 > 테이블 > 레코드 이다.

 

● 관련 파라미터

 

vacuum_freeze_min_age 파라미터 (deault : 5000000 ) <<  이 값의 수치보다 age가 많은 레코드가 xid frozen 대상

vacuum_freeze_table_age 파라미터 (deault : 150000000 ) 

autovacuum_freeze_max_age 파라미터 (deault : 200000000 ) 

 

●  Freeze 발생 조건:

• vacuum_freeze_min_age보다 오래된 XID
• vacuum_freeze_table_age에 도달한 테이블
• autovacuum_freeze_max_age 도달 시 강제 실행

-- 데이터베이스 전체의 age 확인
SELECT datname, age(datfrozenxid) as transaction_age
FROM pg_database
ORDER BY transaction_age DESC;

-- 테이블별 age 확인
SELECT schemaname, relname, n_live_tup, n_dead_tup, 
       age(relfrozenxid) as xid_age,
       round(100 * n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY xid_age DESC;

-- autovacuum 동작 상태 확인
SELECT relname, last_vacuum, last_autovacuum, 
       vacuum_count, autovacuum_count,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables;

-- 오래된 XID를 가진 테이블 찾기
SELECT c.oid::regclass as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
AND greatest(age(c.relfrozenxid),age(t.relfrozenxid)) > 100000000
ORDER BY age DESC;

 

 

• autovacuum 파라미터 적절히 설정
• 큰 테이블은 더 낮은 임계값 설정
• 정기적인 모니터링 수행
• age가 높은 테이블 우선 처리

 

 

 

---

AutoVaccum

 

---

참고용

 

 

필드	크기	comment
xmin	4 bytes	해당 레코드를 생성한 트랜잭션 ID
xmax	4 bytes	해당 레코드를 삭제하거나 업데이트한 트랜잭션 ID (활성 상태면 0)
cmin	4 bytes	트랜잭션 내에서 생성 명령 ID
cmax	4 bytes	트랜잭션 내에서 삭제 명령 ID
ctid	6 bytes	물리적 레코드 위치 (블록 번호, tuple 인덱스)
infomask	2 bytes	플래그 비트들 (HEAP_XMIN_COMMITTED, HEAP_XMIN_INVALID 등)
infomask2	2 bytes	추가 플래그 비트들
t_bits	variable	NULL 비트맵