[ PostgreSQL ] 1. vacuum ( vacuum 이 필요한 이유)

 
1. PG 의 MVCC (multi version concurrent control) 구현

오라클은 읽기 일관성을 위한 MVCC 을 undo 세그먼트에 cr 블록을 생성한다. 

postgresql 은 구조상 undo 세그먼트라는 것을 가지고 있지 않아 데이터파일 내에 변경전 값을 저장하게 된다

이것을 dead tuple 이라고 부른다. 시간이 지나 어느곳에도 참조하지 않는 데드튜플은 vacuum 이 정리한다.

* IO 단위인 page 에 변경전 튜플과 현재튜플이 같이 저장되어 효율적이지 않은 단점

 

 
2. PG 의 트랜잭션ID (XID) 특성

데이터의 선후관계 확인을 위해 오라클은 SCN 이라는 것을 사용하는데, postgresql 에서는 Transaction ID 를 사용한다. 

xid 는 unsigned 32 bit 정수로 40억 개 (2^32) 까지 사용 가능하다. 이것을 다쓰면 한바퀴 돌아 순환하는 구조이다. 

튜플은 생성시점의 xid (xmin), 삭제시점의 xid (xmax) 를 가지게 된다. 아래와 같은 구조를 가지게 된다. 

출처 : https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-tuples/slides.html#s1

간단하게 그림으로 표현해본다면..

최초 xid 101 시점에 튜플이 생성되고, 이후 튜플이 업데이트 되면 원본의 xmax 가 103 으로 바뀌고

새롭게 생성된 튜플의 xmin 도 103 이 되고, 업데이트 된 값 'BBB' 가 저장된다. 

xid 102 시점에 조회를 하던 세션은 xmin 값이 자신보다 작은 101 을 가진 튜플의 값을 조회 ('AAA') 한다.

 

3. XID wraparound 

transaction ID 는 무한하지 않기 때문에 40억을 넘어가면 다시 돌게 되어있다. 

한번 순환한 xid 는 과거 시점 xid 가 자신의 xid 보다 클 수 밖에 없어, 그대로 사용하게 되면 이전 시점 데이터를 볼수가 없게 된다. 

그래서 xid 값을 frozen 처리를 하게 되고 이것은  "이 튜플은 아주 오래된 것이므로, 모든 트랜잭션에서 볼 수 있다" 라고 판단하게 된다.

 

 
4. VACUUM 실행 전 확인

데이터 변경이 많은 테이블은 dead tuple 이 많이 발생하게 되어 많은 공간과 IO 의 비효율이 발생 할 수 있다. 

이를 정리해주는 vacuum 작업을 주기적으로 돌려야 한다. (물론 xid 의 frozen 작업도 vacuum 이 처리한다.)

실제 live tuple 에 비해 변경 작업이 많아 dead tuple 이 많이 생성된 경우, 테이블의 전체 크기가 커지게 되는데

이를 bloating 되었다고 표현하며, 주기적으로 관찰할 필요가 있다. 

* Dead Tuple, Live Tuple 확인

-- 1. 통계수집기 활용 방식 : 빠름, 근사치 제공. (vacuum 이후 수치) 
SELECT c.relname AS table_name,
       pg_stat_get_live_tuples(c.oid) AS live_tuple,
       pg_stat_get_dead_tuples(c.oid) AS dead_tuple
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE pg_stat_get_live_tuples(c.oid) > 0
  AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tuple DESC;

-- 1. 통계수집기 활용 방식
  select relname, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup 
 from pg_stat_all_tables 
 where relname = 't1' ;

 

-- 2. pgstattuple 함수를 통한 통계 : 실시간, 정확

 select relname, table_len, tuple_percent, dead_tuple_percent,free_percent
 from( select relname,(pgstattuple(oid)).*
       from pg_class 
       where relkind = 'r' ) a 
 where table_len >= 1048576  -- 100kb
 order by dead_tuple_percent desc limit 10;

 select avail,count(*)
 from pg_freespace('t1') 
 group by avail ;
 

5. VACUUM  문법 및 실행

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

옵션항목은 아래와 같은 것들이 들어간다.

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP { AUTO | ON | OFF }
    PROCESS_MAIN [ boolean ]
    PROCESS_TOAST [ boolean ]
    TRUNCATE [ boolean ]
    PARALLEL integer
    SKIP_DATABASE_STATS [ boolean ]
    ONLY_DATABASE_STATS [ boolean ]
    BUFFER_USAGE_LIMIT size

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]
 

-- ## VACUUM 실행 예시

VACUUM;                -- 전체 테이블에 대해 간단히 실행
VACUUM FULL;           -- 전체 테이블 공간 회수 = 재구축 (배타적 잠금 주의)
 * pg_repack : 온라인 재구축. 짧은 시간 락
 select pg_relation_filepath('t1') --> 재구축 확인

VACUUM ANALYZE;        -- VACUUM 후 통계 정보 갱신 => vacuum + analyze
 * 통계만 = > analyze (verbose) t1;

vacuum [테이블명];         -- 특정 테이블에 대해 실행
vacuum (verbose) t1;
vacuum (analyze) t1;   -- vacuum + analyze
vacuum (freeze, verbose) t1;  -- 강제 freeze
vacuum (skip_locked) t1;     -- lock 대기하지 않고 skip
vacuum (verbose, parallel 4) t1;  -- 인덱스 vacuum 오래걸릴때
vacuum (verbose, index_cleanup off) t1;  -- 인덱스 vacuum 은 off,  default 는 auto

 

-- # wal 발생량 확인
select pg_current_wal_lsn(); -- before
vacuum t1;
select pg_current_wal_lsn(); -- after
select pg_size_pretty(pg_wal_lsn_diff('before','after')));