17. PostgreSQL - vacuum full

concurrent vacuum은 DB운영 시 꼭 필요한 요소이지만 미사용 공간을 회수하지 못하는 문제가 있음.

그래서 vacuum full 명령을 수행할 필요가 있음.

 

table reorg 작업을 못해 발생할 수 있는 문제점

아래 그림은 delete 명령 수행으로 dead tuple이 발생하고 concurrent vacuum에 의해 dead tuple이 정리된 상태

 

 

table size 유지로 인한 문제점

      disk 공간 낭비

      DB performance에 악영향을 미칠 수 있음.

           위 그림에서 tuple 3개를 읽으려면 3개의 page를 읽어야 함.

 

 

vacuum full 사용 시 주의점.

    table에 vacuum full 명령 수행 시 어떤 transaction도 read/write 할 수 없음.(online 작업 불가)

    table 크기에 최대 2배의 공간이 필요하기 때문에 여유공간이 있어야 함.

 

vacuum full 동작 방식

 

(1) vacuum full을 수행하려는 table에 대해 AccessExclusiveLock을 획득하고 새로운 table file 생성

(2) live tuple을 새 table file로 복사

(3) old file 제거, table과 연관된 index rebuild 수행, FSM/VM 갱신, system catalog statistics 갱신

(4) AccessExclusiveLock 해제

(5) t_xmin 확인 후 가능한 경우 clog files 와 pages 정리

vacuum full 수행 판단 기준

pg_freespacemap extension 설치 후 page 내 freespace가 얼마나 있는지 확인 후 결정

 

psql -d testdb -c "CREATE EXTENSION pg_freespacemap"

psql -d testdb -U test -c "drop table test_tab"
psql -d testdb -U test -c "create table test_tab(num serial, num2 int, aa timestamp , bb text, cc text)"

psql -d testdb -U test -c "
insert into test_tab (num2,aa, bb,cc)
select
    i,
    now(),
    md5(random()::text),
    md5(random()::text)
from generate_series(1, 1000000) s(i)
"

psql -d testdb

SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('test.test_tab');

number of pages | Av. freespace size | Av. freespace ratio 
-----------------+--------------------+---------------------
           14286 | 32 bytes           |                0.39

psql -d testdb -U test -c "delete from test_tab where num <= 300000"


psql -d testdb 

SELECT relname, n_live_tup, n_dead_tup,n_tup_ins, n_tup_upd ,n_tup_del , n_tup_ins + n_tup_del + n_tup_upd as sum_idu, to_char(last_autovacuum,'mm-dd hh24:mi:ss') as last_autovacuum, to_char(last_autoanalyze,'mm-dd hh24:mi:ss') as last_autoanalyze, autovacuum_count,autoanalyze_count
FROM pg_stat_all_tables
where schemaname in ('test')
and relname ~ 'test_tab';
 relname  | n_live_tup | n_dead_tup | n_tup_ins | n_tup_upd | n_tup_del | sum_idu | last_autovacuum | last_autoanalyze | autovacuum_count | autoanalyze_count 
----------+------------+------------+-----------+-----------+-----------+---------+-----------------+------------------+------------------+-------------------
 test_tab |     700000 |          0 |   1000000 |         0 |    300000 | 1300000 | 12-25 11:48:00  | 12-25 11:48:00   |                2 |                 2
(1 row)


#### autovacuum에 의해 정리된 후 확인
SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('test.test_tab');

 number of pages | Av. freespace size | Av. freespace ratio 
-----------------+--------------------+---------------------
           14286 | 2470 bytes         |               30.16


#### autovacuum이 수행되서 vacuum을 수행해도 변화는 없음.
psql -d testdb -U test -c "VACUUM test_tab"


#### page별로 ratio 확인 가능
#### 3만개를 지웠으니 앞쪽 page는 빈공간이 많은 것으로 나오는게 당연함.
SELECT *, round(100 * avail/8192 ,2) as "freespace ratio"
FROM pg_freespace('test.test_tab');

blkno | avail | freespace ratio 
-------+-------+-----------------
     0 |  8160 |           99.00
     1 |  8160 |           99.00
     2 |  8160 |           99.00
     3 |  8160 |           99.00
... 생략...
  4284 |  8160 |           99.00
  4285 |  5632 |           68.00
  4286 |    32 |            0.00
  4287 |    32 |            0.00
  4288 |    32 |            0.00
  4289 |    32 |            0.00
... 생략...
 14284 |    32 |            0.00
 14285 |  2336 |           28.00


psql -d testdb -U test -c "VACUUM FULL test_tab"


##### table reorag 작업 진행 후 확인
##### 작업 이후 page 내 빈 공간이 없는것을 확인할 수 있음.
psql -d testdb 
SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('test.test_tab');
       
 number of pages | Av. freespace size | Av. freespace ratio 
-----------------+--------------------+---------------------
           10000 | 0 bytes            |                0.00
           
           
SELECT *, round(100 * avail/8192 ,2) as "freespace ratio"
FROM pg_freespace('test.test_tab');

 blkno | avail | freespace ratio 
-------+-------+-----------------
     0 |     0 |            0.00
     1 |     0 |            0.00
     2 |     0 |            0.00
     3 |     0 |            0.00
... 생략...
  9997 |     0 |            0.00
  9998 |     0 |            0.00
  9999 |     0 |            0.00
(10000 rows)