[PostgreSQL - Vacuum Lock 확인]

Vacuum이란?

Vacuum은 PostgreSQL의 MVCC 구현 방법이 Oracle이나 MySQL 등 다른 DBMS와 다르고 그 차이로 인해 발생하는 문제점을 해결하기 위한 PostgreSQL만의 특별한 동작인데요.

Vacuum을 DB단에서 자동으로 수행하는 동작을 AutoVacuum이라고 하며 Vacuum & AutoVacuum(이하 Vacuum으로 통일)을 통해 아래 4가지 작업을 수행합니다.

- 임계치 이상으로 발생한 Dead Tuple을 정리하여 FSM (Free Space Map) 으로 반환
- Transaction ID Wraparound 방지
- 통계정보 갱신
- visibility map을 갱신하여 index scan 성능 향상

 

위 4개의 동작 모두 중요하지만 그 중 특히 중요한 것은 Dead Tuple을 정리하는 동작과 Trasaction ID Wraparound 방지 동작입니다.

 

 Vacuum 종류

1) Vacuum
- 삭제 된 데이터나 Update된 데이터의 이전 버전을 마킹 하여 그 공간을 다시 사용할 수 있게 해 주는 기본 Vacuum 입니다.

2) Vacuum Freeze
- 삭제 된 데이터만이 아니라 남아있는 데이터에 대해서도 XID를 2로 변경해 주어 앞으로 XID wrap around가 발생하더라도 문제가 없도록 해 줍니다.

3) Vacuum (prevent wrap around)
 - XID Wrap around가 다가오는 상황에서 아직 freeze 되지 않은 데이터가 남아 있을 경우 강제로 vacuum freez를 시도 하는데 이 때 실행되는 vacuum 작업이 VACUUM (prevent wrap around) 입니다.

4) Vacuum Full
- 삭제된 데이터를 마킹만 하는게 아니라 아예 정상적인 데이터만으로 테이블을 새로 만들어서 빈공간까지 회수하는 작업, Table 전체에 Lock을 걸기 때문에 운영중에 실행하면 안됩니다.

5) Auto Vacuum
  - 내부 알고리즘으로 필요에 따라 자동으로 실행되는 Vacuum 입니다.
  - 기본적으로 실시간(주기적)으로 Vacuum 작업(ex, vacuum만 실행, vacuum full아님) 실시하며, autovacuum_freeze_max_age에 도달하면 강제로 Vacuum 작업(ex, vacuum freeze)을 실시하게 됩니다.

 

 사전 작업

ㅁ 테스트 데이터 생성

kim=# create table test (i numeric);
CREATE TABLE


## 100만건 행 삽입
[postgres@localhost ~]$ vi kim.sh
for i in {1..1000000}
do
psql -U postgres -d kim -c "insert into test$i values ($i)"
done


kim=# select count(*) from test;
  count  
---------
1000000
(1 row)

 

ㅁ autovacuum 실행 조건 확인

autovacuum 파라미터확인

 

ㅁ autovacuum 대상 확인

postgres=# select schemaname, relname, n_dead_tup from pg_stat_user_tables where n_dead_tup > 0 and (last_autovacuum is null or last_autoanalyze is null);
schemaname | relname | n_dead_tup
------------+---------+------------
(0 rows)

 

ㅁ autovacuum OFF

alter system set autovacuum=OFF;

 

 

 본 테스트

ㅁ delete 30만건

kim=# delete from test where i between 300000 and 599999;

kim=# select count(*) from test;
count  
--------
700000
(1 row)

 

ㅁ vacuum 대상 확인

kim=# select schemaname, relname, n_dead_tup from pg_stat_user_tables where n_dead_tup > 0 and (last_autovacuum is null or last_autoanalyze is null);
schemaname | relname | n_dead_tup
------------+---------+------------
public     | test    |     300000
(1 row)

 

ㅁ vacuum실행 & DML수행

## insert 스크립트 실행
[postgres@localhost ~]$ sh +x kim.sh
#!/bin/bash

for i in {1000001..1300000}
do
psql -U postgres -d kim -c "insert into test values ($i)"
done


## vacuum 수행
kim=# vacuum freeze verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 9 row versions in 1328 pages
INFO:  "test": found 9 removable, 701405 nonremovable row versions in 4431 out of 4431 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.13s/0.07u sec elapsed 0.25 sec.
INFO:  vacuuming "pg_toast.pg_toast_16385"
INFO:  index "pg_toast_16385_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_16385": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

 

ㅁ dead tuple 삭제 확인

kim=# select schemaname, relname, n_dead_tup from pg_stat_user_tables where n_dead_tup > 0 and (last_autovacuum is null or last_autoanalyze is null);
schemaname | relname | n_dead_tup
------------+---------+------------
(0 rows)

 

ㅁ 테스트 결과

- vacuum full이 아닌 일반 vacuum 수행시 DDL은 불가하며 select, DML은 lock없이 가능합니다.