vacuum / vacuum full 의 차이

POSTGRESQL 2018. 12. 5. 18:20
postgresql 은 update 시 (고유의 MVCC매커니즘에서 기인하여) delete + insert 방식으로 처리하며, 
update를 포함 delete 실행결과 더이상 필요하지 않는 데이터 공간이 재활용되는 시점은 
적절한 scale factor로 구성된 vacuum 프로세스가 수행된 이후입니다. 
vacuum full을 수행했을 경우 새로운 relfilenode명과 함께 파일을 새로 생성하여 디스크 공간을 회수하지만, 
신규 파일쓰기가 완료될때까지 기존 데이터파일은 유지되므로 해당사이즈만큼 디스크 공간을 확보해야 하며, 
Exclusive Lock 이 발생하므로 주의해야 합니다. 

 아래는 pg_filedump 를 활용한 테스트 결과 입니다. 

 1. 소스 다운로드 : https://wiki.postgresql.org/wiki/Pg_filedump 

 2. make 명령으로 소스를 컴파일하여 설치합니다. 
make
make install
3. 설치결과를 확인합니다. 
localhost.localdomain:[/home/data/PG_DATA]pg_filedump -h

Version 11.0 (for PostgreSQL 11.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2018, PostgreSQL Global Development Group

Usage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file

Display formatted contents of a PostgreSQL heap/index/control file
Defaults are: relative addressing, range of the entire file, block
               size as listed on block 0 in the file

The following options are valid for heap and index files:
  -a  Display absolute addresses when formatting (Block header
      information is always block relative)
  -b  Display binary block images within a range (Option will turn
      off all formatting options)
  -d  Display formatted block content dump (Option will turn off
      all other formatting options)
  -D  Decode tuples using given comma separated list of types
      Supported types:
        bigint bigserial bool char charN date float float4 float8 int
        json macaddr name oid real serial smallint smallserial text
        time timestamp timetz uuid varchar varcharN xid xml
      ~ ignores all attributes left in a tuple
  -f  Display formatted block content dump along with interpretation
  -h  Display this information
  -i  Display interpreted item details
  -k  Verify block checksums
  -o  Do not dump old values.
  -R  Display specific block ranges within the file (Blocks are
      indexed from 0)
        [startblock]: block to start at
        [endblock]: block to end at
      A startblock without an endblock will format the single block
  -s  Force segment size to [segsize]
  -t  Dump TOAST files
  -v  Ouput additional information about TOAST relations
  -n  Force segment number to [segnumber]
  -S  Force block size to [blocksize]
  -x  Force interpreted formatting of block items as index items
  -y  Force interpreted formatting of block items as heap items

The following options are valid for control files:
  -c  Interpret the file listed as a control file
  -f  Display formatted content dump along with interpretation
  -S  Force block size to [blocksize]

Report bugs to

4. 테스트를 위해 테이블 및 데이터를 생성합니다. 

postgres=# create table tb_vacuum_test(c1 int, c2 varchar);                                                    
CREATE TABLE
postgres=# insert into tb_vacuum_test select n as c1, md5(n::varchar) as c2 from generate_series(1,10) as s(n);
INSERT 0 10
postgres=# select * from pg_relation_filenode('tb_vacuum_test');
 pg_relation_filenode 
----------------------
                49178
(1 row)
5. checkpoint 를 수행하지 않을경우 신규생성된 데이터가 buffer cache에만 존재하고 파일에 flush 되지 않았기 때문에 즉각적인 확인이 불가합니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:31:20 2018
*******************************************************************
Error: Premature end of file encountered.
6. checkpoint 실행하여 buffer cache 영역의 데이터를 데이터파일로 flush 합니다. 
postgres=# checkpoint;
CHECKPOINT
postgres=#
7. 덤프파일을 확인합니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:31:44 2018
*******************************************************************

Block    0 ********************************************************

 -----
 Block Offset: 0x00000000         Offsets: Lower      64 (0x0040)
 Block: Size 8192  Version    4            Upper    7552 (0x1d80)
 LSN:  logid      1 recoff 0x8cc5d930      Special  8192 (0x2000)
 Items:   10                      Free Space: 7488
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 64

 ------ 
 Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 1 c4ca4238a0b923820dcc509a6f75849b
 Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 2 c81e728d9d4c2f636f067f89cc14862c
 Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 4 a87ff679a2f3e71d9181a67b7542122c
 Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 5 e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 7 8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 8 c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7552 (0x1d80)  Flags: NORMAL
COPY: 10        d3d9446802a44259755d38e6d163e820


*** End of File Encountered. Last Block Read: 0 ***
8. 실제 update 구문이 delete/insert 순서로 처리되는지 확인해 봅니다. 
postgres=# update tb_vacuum_test set c2='a' where c1=1;
UPDATE 1
postgres=# checkpoint;
CHECKPOINT
postgres=#
9. 덤프파일 상 update 대상 row는 그대로 있고 신규 ROW 추가되어 있는 것을 확인할 수 있습니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:34:15 2018
*******************************************************************

Block    0 ********************************************************

 -----
 Block Offset: 0x00000000         Offsets: Lower      68 (0x0044)
 Block: Size 8192  Version    4            Upper    7520 (0x1d60)
 LSN:  logid      1 recoff 0x8cc5dd98      Special  8192 (0x2000)
 Items:   11                      Free Space: 7452
 Checksum: 0x0000  Prune XID: 0x00000290  Flags: 0x0000 ()
 Length (including item array): 68

 ------ 
 Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 1 c4ca4238a0b923820dcc509a6f75849b
 Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 2 c81e728d9d4c2f636f067f89cc14862c
 Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 4 a87ff679a2f3e71d9181a67b7542122c
 Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 5 e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 7 8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 8 c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7552 (0x1d80)  Flags: NORMAL
COPY: 10        d3d9446802a44259755d38e6d163e820
 Item  11 -- Length:   30  Offset: 7520 (0x1d60)  Flags: NORMAL
COPY: 1 a
10. vacuum 실행 시 어떤 변화가 있는지 확인해 봅니다. 
postgres=# vacuum tb_vacuum_test ;
VACUUM
postgres=# checkpoint;
CHECKPOINT
postgres=#
11. 덤프파일 확인결과 update 대상 ROW 플래그가 REDIRECT로 변경되어 있는것을 확인할 수 있습니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:35:12 2018
*******************************************************************

Block    0 ********************************************************

 -----
 Block Offset: 0x00000000         Offsets: Lower      68 (0x0044)
 Block: Size 8192  Version    4            Upper    7584 (0x1da0)
 LSN:  logid      1 recoff 0x8cc5e1d0      Special  8192 (0x2000)
 Items:   11                      Free Space: 7516
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0004 (ALL_VISIBLE)
 Length (including item array): 68

 ------ 
 Item   1 -- Length:    0  Offset:   11 (0x000b)  Flags: REDIRECT
 Item   2 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 2 c81e728d9d4c2f636f067f89cc14862c
 Item   3 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 4 a87ff679a2f3e71d9181a67b7542122c
 Item   5 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 5 e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 7 8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 8 c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 10        d3d9446802a44259755d38e6d163e820
 Item  11 -- Length:   30  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 1 a


*** End of File Encountered. Last Block Read: 0 ***
12. delete로 유효하지 않는 데이터공간을 재현하고 insert로 해당 공간을 재활용하는지 확인해 봅니다. 
postgres=# delete from tb_vacuum_test where c1=2;
DELETE 1
postgres=# insert into tb_vacuum_test values(11,md5('11'));
INSERT 0 1
postgres=# checkpoint;
CHECKPOINT
postgres=#
13. 기존 삭제된 ROW 는 그대로 남아있고 신규생성된 ROW가 append 된것을 확인할 수 있습니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:37:43 2018
*******************************************************************

Block    0 ********************************************************

 -----
 Block Offset: 0x00000000         Offsets: Lower      72 (0x0048)
 Block: Size 8192  Version    4            Upper    7520 (0x1d60)
 LSN:  logid      1 recoff 0x8cc61d20      Special  8192 (0x2000)
 Items:   12                      Free Space: 7448
 Checksum: 0x0000  Prune XID: 0x00000291  Flags: 0x0000 ()
 Length (including item array): 72

 ------ 
 Item   1 -- Length:    0  Offset:   11 (0x000b)  Flags: REDIRECT
 Item   2 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 2 c81e728d9d4c2f636f067f89cc14862c
 Item   3 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 4 a87ff679a2f3e71d9181a67b7542122c
 Item   5 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 5 e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 7 8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 8 c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 10        d3d9446802a44259755d38e6d163e820
 Item  11 -- Length:   30  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 1 a
 Item  12 -- Length:   61  Offset: 7520 (0x1d60)  Flags: NORMAL
COPY: 11        6512bd43d9caa6e02c990b0a82652dca


*** End of File Encountered. Last Block Read: 0 ***
14. vacuum 수행 후 insert 처리결과를 확인합니다. 
postgres=# vacuum tb_vacuum_test ;
VACUUM
postgres=# insert into tb_vacuum_test values(12,md5('12'));
INSERT 0 1
postgres=# checkpoint;
CHECKPOINT
postgres=#
15. 삭제된 ROW 공간을 신규ROW 공간으로 재활용했음을 확인할 수 있습니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:39:10 2018
*******************************************************************

Block    0 ********************************************************

 -----
 Block Offset: 0x00000000         Offsets: Lower      72 (0x0048)
 Block: Size 8192  Version    4            Upper    7520 (0x1d60)
 LSN:  logid      1 recoff 0x8cc64288      Special  8192 (0x2000)
 Items:   12                      Free Space: 7448
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0001 (HAS_FREE_LINES)
 Length (including item array): 72

 ------ 
 Item   1 -- Length:    0  Offset:   11 (0x000b)  Flags: REDIRECT
 Item   2 -- Length:   61  Offset: 7520 (0x1d60)  Flags: NORMAL
COPY: 12        c20ad4d76fe97759aa27a0c99bff6710
 Item   3 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 4 a87ff679a2f3e71d9181a67b7542122c
 Item   5 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 5 e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 7 8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 8 c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 10        d3d9446802a44259755d38e6d163e820
 Item  11 -- Length:   30  Offset: 7648 (0x1de0)  Flags: NORMAL
COPY: 1 a
 Item  12 -- Length:   61  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 11        6512bd43d9caa6e02c990b0a82652dca


*** End of File Encountered. Last Block Read: 0 ***
16. vacuum 이 아닌 vacuum full 을 수행해 봅니다. 
postgres=# vacuum full tb_vacuum_test ;
VACUUM
postgres=#
17. vacuum full 수행결과 신규파일이 생성된 후 이전파일은 삭제되므로 더 이상 데이터가 존재하지 않습니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49178 -exec pg_filedump -D int,varchar {} \;

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49178
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:41:22 2018
*******************************************************************
Error: Unable to read full page header from block 0.
  ===> Read 0 bytes
Error: Premature end of file encountered.
18. 새로 생성된 relfilenode를 확인합니다. 
postgres=# select * from pg_relation_filenode('tb_vacuum_test');
 pg_relation_filenode 
----------------------
                49184
(1 row)
19. 신규생성된 데이터파일로부터 필요하지 않는 디스크 공간은 모두 회수되었음을 확인할 수 있습니다. 
localhost.localdomain:[/home/data/PG_DATA]find . -name 49184 -exec pg_filedump -D int,varchar {} \;  

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/13284/49184
* Options used: -D int,varchar 
*
* Dump created on: Wed Dec  5 08:43:03 2018
*******************************************************************

Block    0 ********************************************************

 -----
 Block Offset: 0x00000000         Offsets: Lower      68 (0x0044)
 Block: Size 8192  Version    4            Upper    7520 (0x1d60)
 LSN:  logid      1 recoff 0x8cc7e720      Special  8192 (0x2000)
 Items:   11                      Free Space: 7452
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 68

 ------ 
 Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 12        c20ad4d76fe97759aa27a0c99bff6710
 Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 4 a87ff679a2f3e71d9181a67b7542122c
 Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 5 e4da3b7fbbce2345d7772b0674a318d5
 Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc
 Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 7 8f14e45fceea167a5a36dedd4bea2543
 Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 8 c9f0f895fb98ab9159f51fd0297e236d
 Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26
 Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 10        d3d9446802a44259755d38e6d163e820
 Item  10 -- Length:   30  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 1 a
 Item  11 -- Length:   61  Offset: 7520 (0x1d60)  Flags: NORMAL
COPY: 11        6512bd43d9caa6e02c990b0a82652dca


*** End of File Encountered. Last Block Read: 0 ***


저작자표시

'POSTGRESQL' 카테고리의 다른 글

Read committed VS Repeatable read  (1) 2019.12.18
은전한닢 형태소 분석기와 full-text-search 모듈 연동  (0) 2018.12.10
gdb를 통한 postgresql 소스 디버깅  (1) 2018.12.05
replication 자동구성 스크립트(테스트 only)  (0) 2018.12.04
신규컬럼 추가 시 default value에 의한 lock 발생현상 및 회피요령  (0) 2018.12.03
,

티스토리툴바