[PostgreSQL] vacuum을 하는 이유 // vacuum, vacuum full 테스트

 

Postgresql은 oracle, mysql 과는 조금 다른 MVCC 구현 방식 이라고 한다.

짧게 정리를 하자면, 

데이터 페이지 내에 변경되기 이전 Tuple과 변경된 신규 Tuple을 같은 page에 저장하고

Tuple별로 생성된 시점과 변경된 시점을 기록 및 비교하는 방식으로 MVCC를 제공한다.

 

그리고 Tuple이 생성되거나 변경된 시점을 각 Tuple 내 xmin, xmax라는 메타데이터 field에 기록하여 어떤 Tuple을 읽을 수 있는지 버전 관리를 하게 된다. 

 

==

• xmin – Tuple을 insert하거나 update하는 시점의 Transaction ID를 갖는 메타데이터
  Insert의 경우 insert된 신규 Tuple의 xmin에 해당 시점의 Transaction ID가 할당됨
  UPDATE의 경우 update된 신규 Tuple의 xmin에 해당 시점의 Transaction ID가 할당됨
• xmax – Tuple을 delete하거나 update하는 시점의 Transaction ID를 갖는 메타데이터
  Delete의 경우 변경되기 이전 Tuple의 xmax에 해당 시점의 Transaction ID가 할당됨
  UPDATE의 경우 변경되기 이전 Tuple의 xmax와 update된 신규 Tuple의 xmin에는 해당 시점의 Transaction ID가 할당됨
  update된 신규 Tuple의 xmax에는 NULL이 할당됨

==

 

Vacuum 을 하는 이유?

PostgreSQL의 Update 방식

1. FSM 공간 확보 (Free Space Map)  

2. FSM 안에 update된 데이터를 기록한다.

3. update된 데이터가 기록되면 update 이전의 원본 Tuple을 가리키던 포인터를 

새로 update된 Tuple로 가리키도록 변경한다.

 

이 세 번의 과정을 거치면서, 포인터가 가리키던 Tuple이 FSM에 Update 되면, 기존 Tuple은 어디에도 참조되지 않는 Dead Tuple이 된다.

 

그래서 vacuum을 하는데, vacuum을 해주지 않으면 Dead Tuple이 차지 하는 쓸데없는 공간만 계속 쌓일 뿐이다.

쓸데 없는 공간을 차지 하는 Dead Tuple 때문에, DB는 계속 쓸데 없는 page를 읽게 되고 쿼리 성능에 악영향을 준다.

 

==

PostgreSQL이 데이터를 읽는 방식?

PostgrsSQL도 다른 DB와 마찬가지로 (다른 DB와는 달리 Block 단위로 메모리에 올리지 않는다.) page 단위로 데이터를 읽어와서 메모리에 쌓는다.

메모리에 쌓을 때, Live Tuple과 Dead Tuple을 함께 쌓아올린다는 것이 문제점이다.

그래서 page를 읽을 때 Disk I/O 사용률이 더 많이 필요로 해지는 것이 또 문제점이다.
-디스크 사용률 증가 이슈는 큰 이슈

 

 출처 : 우아한기술블로그

 

왼쪽 그림에서는 live tuple만 읽기 때문에 1~8까지 page를 읽는데 그다지 오래 걸리지 않는다.

오른쪽 그림에서는 page안에 live Tuple과 dead Tuple이 같이 있는데, 1~8까지 읽으려면 2배의 페이지를 읽게 되는 것이 문제점의 결론이다.

 

 

 

vacuum 및 vacuum full 테스트

=======================vacuum 및 vacuum full 테스트=================================

 

 

 

//테이블 카운트

ontune=# SELECT count(*) from realtimepid_24082013; 
 count                                                                                                         
-------
 35188
(1개 행)
작업시간: 6.268 ms

 

// Tuple 삭제

// 맨위의 _agentid 행을 제외하고 중복된 행을 모두 삭제한다.
ontune=# WITH ranked_agents AS (                   
ontune(#     SELECT ctid,
ontune(#            ROW_NUMBER() OVER (PARTITION BY _agentid ORDER BY ctid) AS rn
ontune(#     FROM realtimepid_24082013
ontune(# )
ontune-# DELETE FROM realtimepid_24082013
ontune-# USING ranked_agents
ontune-# WHERE realtimepid_24082013.ctid = ranked_agents.ctid
ontune-#   AND ranked_agents.rn > 1;
DELETE 35184
작업시간: 160.652 ms

 

// 테이블 카운트 및 시간 확인
ontune=# select count(*) from realtimepid_24082013;
 count                                                                                                         
-------
     4
(1개 행)
작업시간: 3.728 ms

 

// 테이블에서 살아있는 Tuple과 죽은 Tuple의 갯수를 확인
ontune=# select relname, n_live_tup, n_Dead_tup from pg_stat_user_tables where relname = 'realtimepid_24082013'; 
       relname        | n_live_tup | n_dead_tup                                                                
----------------------+------------+------------
 realtimepid_24082013 |          4 |      35184
(1개 행)

 

// n_live_tup 대비 n_dead_tup 배수 확인
ontune=# select relname, n_live_tup, n_Dead_tup, n_Dead_tup / (n_live_tup::float) as ratio  from pg_stat_user_tables where relname = 'realtimepid_24082013'; 
       relname        | n_live_tup | n_dead_tup | ratio                                                        
----------------------+------------+------------+-------
 realtimepid_24082013 |          4 |      35184 |  8796

 

//테이블 사이즈 확인

ontune=# \dt+ realtimepid_24082013; 
                                     릴레이션 목록                                                             
 스키마 |         이름         |  형태  | 소유주 | 지속성 | 접근 방법 |  크기   | 설명
--------+----------------------+--------+--------+--------+-----------+---------+------
 public | realtimepid_24082013 | 테이블 | ontune | 영구   | heap      | 4072 kB |
(1개 행)


=================vacuum 수행===============

ontune=# \timing
작업수행시간 보임
ontune=# vacuum realtimepid_24082013; 
VACUUM
작업시간: 5.557 ms

 

//죽은 Tuple 갯수 확인
ontune=# select relname, n_live_tup, n_Dead_tup from pg_stat_user_tables where relname = 'realtimepid_24082013';
       relname        | n_live_tup | n_dead_tup                                                                
----------------------+------------+------------
 realtimepid_24082013 |          4 |          0
(1개 행) 작업시간: 1.935 ms

 

 

//테이블 카운트 확인//vacuum 후에 조회 시간이 단축된 것을 확인 (같은 page 공간에 Dead Tuple이 모두 vacuum 되었으니 참조하지 않고 조회해서 시간이 단축된 것으로 생각하면 될 듯 하다.)

ontune=# select count(*) from realtimepid_24082013; 
 count                                                                                                         
-------
     4
(1개 행)
작업시간: 0.729 ms

 

 

//사이즈 확인 

//원래 vacuum 으로는 물리 디스크 사이즈는 줄어들지 않는다고 했는데, 나의 경우는 테이블의 물리 사이즈로 줄어든 것으로 확인할 수 있다.

ontune=# \dt+ realtimepid_24082013; 
                                    릴레이션 목록                                                              
 스키마 |         이름         |  형태  | 소유주 | 지속성 | 접근 방법 | 크기  | 설명
--------+----------------------+--------+--------+--------+-----------+-------+------
 public | realtimepid_24082013 | 테이블 | ontune | 영구   | heap      | 64 kB |
(1개 행)

 

 

=================vacuum full 수행===============

 

 

//vacuum full 수행

ontune=# vacuum full realtimepid_24082013; 
VACUUM
작업시간: 44.033 ms

 

//사이즈 확인

//vacuum 과 달리 vacuum full을 사용하니 사이즈가 확 줄어든 것을 또 확인할 수 있었다.
ontune=# \dt+ realtimepid_24082013;        
                                      릴레이션 목록                                                            
 스키마 |         이름         |  형태  | 소유주 | 지속성 | 접근 방법 |    크기    | 설명
--------+----------------------+--------+--------+--------+-----------+------------+------
 public | realtimepid_24082013 | 테이블 | ontune | 영구   | heap      | 8192 bytes |
(1개 행)

-

참고 자료

우아한테크

https://techblog.woowahan.com/9478/

PostgreSQL Vacuum에 대한 거의 모든 것 | 우아한형제들 기술블로그