My Books

My Slides

rss 아이콘 이미지

이전 포스팅: [PostgreSQL] 아무도 자세히 알려주지 않았던 Autovacuum의 위험성



1. Vacuum 성능 향상을 위해 9.6에 추가된 기능


이 포스팅은 이전 포스팅과 연결된 내용이므로, 이전 포스팅을 읽고 오시는 것이 좋습니다. 이번 시간에는 9.6의 향상된 Vacuum 성능에 대해서 설명합니다. 9.6은 Visibility Map 내의 Frozen Bit를 이용해서 Vacuum 대상 페이지를 선별하는 알고리즘을 적용했습니다. 즉, 해당 페이지가 'frozen 상태'라면 베큠 대상에서 제외한다는 것입니다. 아이디어는 간단하지만 그 효과는 매우 뛰어납니다. (그림-1~그림-3 참조)


그림-1. 1월 파티션에 20만건 입력 및 Autovacuum 수행 후


그림-2. 2월 파티션에 16만건 입력 및 Autovacuum 수행 후

그림-3. 3월 파티션에 16만건 입력 및 Autovacuum 수행 후


Note
XID Wraparound 문제를 해결하기 위한 다양한 알고리즘이 개발되고 있는 이유는 XMIN/XMAX 값이 4바이트 정수 형이기 때문입니다. 만일 8바이트 정수형을 적용했다면 Anti-Wraparound를 위한 Vacuum은 사실상 필요가 없습니다. (초당 1천 TPS인 경우, 6억년 이후에 Wraparound 발생) 하지만 8바이트 정수형을 적용할 경우, 레코드 크기가 현재보다 8바이트만큼 커지므로 테이블 크기가 크게 증가하게 됩니다. 이러한 이유로 8바이트 정수형을 적용하는 것은 논의에서 배제된 것 같습니다. 또 하나 생각해볼 수 있는 방법은 오라클의 Undo와 같이 Wrap#를 이용하는 것입니다. 즉, wraparound가 발생할 때 마다 Wrap#를 1 증가시키고, XID를 비교할 때 "Wrap# + XMIN"의 조합을 이용하는 방법입니다. Wrap#를 2바이트 정수형으로 사용할 경우, 초당 1천 TPS인 환경에서 대략 9천년 이후에나 Wraparound가 발생합니다. 8바이트 정수형을 사용하는 것에 비해서는 비교적 크기 증가가 적은 방법입니다. 어떤 방법이던 PostgreSQL의 향후 버전에는 Anti-Wraparound를 위한 Vacuum 자체가 완전히 사라지기를 기대해봅니다.




2. FROZEN 비트를 이용한 알고리즘 소개


알고리즘 자체는 아주 단순합니다. 블록 내에 모든 레코드들의 XMIN 값이 테이블의 FrozenXID 값보다 작다면, Visibility Map 내의 해당 페이지에 대한 "Frozen Bit"를 1 (True)로 설정하는 것입니다. 따라서 향후 수행되는 Vacuum은 "frozen bit"가 0 (False)인 페이지만을 대상으로 하면 됩니다 (그림-4 참조)


그림-4. FROZEN 비트를 이용한 알고리즘 개요 


 

Note
오라클 엑사데이터의 스토리지 인덱스도 이와 유사한 알고리즘을 사용합니다. 스토리지 인덱스 역시 인덱스 칼럼을 기준으로 페이지 별로 MIN/MAX 값을 관리함으로써 액세스하는 대상 페이지를 줄이겠다는 개념입니다. DBMS도 사람이 개발하는 소프트웨어인만큼 좋은 알고리즘은 서로 간에 많이 차용해서 사용하는 것 같습니다.




3. 테스트 세부 내역


테스트 세부 내역은 다음과 같습니다. 이전 포스팅과 거의 유사합니다만, Frozen Bit를 확인하는 부분이 추가되었으므로 해당 부분만 주의 깊게 보시면 될 것 같습니다.


3-1. 파라미터 설정

-- 파라미터 수정 (/opt/PostgreSQL/9.6/data/postgresql.conf)
autovacuum_freeze_max_age=200000 # <-- (2억) 
vacuum_freeze_min_age=40000      # <-- (5천만)
vacuum_freeze_table_age=140000   # <-- (1억5천만)
log_autovacuum_min_duration=0    # <--(-1)
log_rotation_size=200MB          # <--(10MB)


3-2. 마스터 테이블 및 파티션 테이블 생성

-- 마스터 테이블 생성 
create table p1 
(
    c1       integer ,
	logdate  date,
	dummy    char(2000)
);

-- 파티션 테이블 생성 
create table p1_y201601 (CHECK ( logdate >= DATE '2016-01-01' AND logdate < DATE '2016-02-01' )) inherits (p1);
create table p1_y201602 (CHECK ( logdate >= DATE '2016-02-01' AND logdate < DATE '2016-03-01' )) inherits (p1);
create table p1_y201603 (CHECK ( logdate >= DATE '2016-03-01' AND logdate < DATE '2016-04-01' )) inherits (p1);
create table p1_y201604 (CHECK ( logdate >= DATE '2016-04-01' AND logdate < DATE '2016-05-01' )) inherits (p1);
create table p1_y201605 (CHECK ( logdate >= DATE '2016-05-01' AND logdate < DATE '2016-06-01' )) inherits (p1);
create table p1_y201606 (CHECK ( logdate >= DATE '2016-06-01' AND logdate < DATE '2016-07-01' )) inherits (p1);
create table p1_y201607 (CHECK ( logdate >= DATE '2016-07-01' AND logdate < DATE '2016-08-01' )) inherits (p1);
create table p1_y201608 (CHECK ( logdate >= DATE '2016-08-01' AND logdate < DATE '2016-09-01' )) inherits (p1);
create table p1_y201609 (CHECK ( logdate >= DATE '2016-09-01' AND logdate < DATE '2016-10-01' )) inherits (p1);
create table p1_y201610 (CHECK ( logdate >= DATE '2016-10-01' AND logdate < DATE '2016-11-01' )) inherits (p1);
create table p1_y201611 (CHECK ( logdate >= DATE '2016-11-01' AND logdate < DATE '2016-12-01' )) inherits (p1);
create table p1_y201612 (CHECK ( logdate >= DATE '2016-12-01' AND logdate < DATE '2017-01-01' )) inherits (p1);


3-3. 트리거 함수 및 트리거 생성

-- 트리거 함수 생성 
CREATE OR REPLACE FUNCTION p1_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF    ( NEW.logdate >= DATE '2016-01-01' AND NEW.logdate <  DATE '2016-02-01') THEN INSERT INTO p1_y201601 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2016-02-01' AND NEW.logdate <  DATE '2016-03-01') THEN INSERT INTO p1_y201602 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-03-01' AND NEW.logdate <  DATE '2016-04-01') THEN INSERT INTO p1_y201603 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-04-01' AND NEW.logdate <  DATE '2016-05-01') THEN INSERT INTO p1_y201604 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-05-01' AND NEW.logdate <  DATE '2016-06-01') THEN INSERT INTO p1_y201605 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-06-01' AND NEW.logdate <  DATE '2016-07-01') THEN INSERT INTO p1_y201606 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-07-01' AND NEW.logdate <  DATE '2016-08-01') THEN INSERT INTO p1_y201607 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-08-01' AND NEW.logdate <  DATE '2016-09-01') THEN INSERT INTO p1_y201608 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-09-01' AND NEW.logdate <  DATE '2016-10-01') THEN INSERT INTO p1_y201609 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-10-01' AND NEW.logdate <  DATE '2016-11-01') THEN INSERT INTO p1_y201610 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-11-01' AND NEW.logdate <  DATE '2016-12-01') THEN INSERT INTO p1_y201611 VALUES (NEW.*);
	ELSIF ( NEW.logdate >= DATE '2016-12-01' AND NEW.logdate <  DATE '2017-01-01') THEN INSERT INTO p1_y201612 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the p1_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;


3-4. TXID 증가를 위해 DBLINK를 이용한 LOOP COMMIT 프로시저 생성

-- INSERT 트리거 생성
CREATE TRIGGER insert_p1_trigger
    BEFORE INSERT ON p1
    FOR EACH ROW EXECUTE PROCEDURE p1_insert_trigger();

-- insert_p1 프로시저 생성 
CREATE OR REPLACE FUNCTION insert_p1(v_c1 integer, v_logdate date, v_dummy char) RETURNS VOID AS $$
BEGIN
       PERFORM dblink('myconn','INSERT INTO P1 VALUES ('||''''||v_c1||''''||','||''''||v_logdate||''''||','||''''||v_dummy||''''||')');
	  
       PERFORM dblink('myconn','COMMIT;');
END;
$$ LANGUAGE plpgsql;

-- loop_insert_p1 프로시저 생성 
CREATE or replace FUNCTION loop_insert_p1(v_logdate date, v_end integer) RETURNS VOID AS $$
BEGIN  
    insert into p1 select generate_series(1,5000000), v_logdate, 'dummy'; 	
    FOR i in 1..v_end LOOP
        PERFORM insert_p1(i, v_logdate, 'dummy');
    END LOOP;
END;
$$ LANGUAGE plpgsql;


3-5. 익스텐션 설치

-- DBLINK 익스텐션 설치
create extension dblink;

-- PG_VISIBILITY 익스텐션 설치
create extension pg_visibility;


3-6. 1월 파티션에 20만건 입력

-- 현재 시점에서 테이블 나이 확인 
$ get_time.sh
 txid_current
--------------
        1,784

-- 현재 테이블 나이 확인 		
$ get_info.sh
  relname   | age | relfrozenxid | reltuples |  size   | last_autoanalze | last_autovacuum
------------+-----+--------------+-----------+---------+-----------------+-----------------
 p1_y201601 |  26 |        1,759 |         0 | 0 bytes |                 |
 p1_y201602 |  25 |        1,760 |         0 | 0 bytes |                 |
 p1_y201603 |  24 |        1,761 |         0 | 0 bytes |                 |
 p1_y201604 |  23 |        1,762 |         0 | 0 bytes |                 |
 p1_y201605 |  22 |        1,763 |         0 | 0 bytes |                 |
 p1_y201606 |  21 |        1,764 |         0 | 0 bytes |                 |
 p1_y201607 |  20 |        1,765 |         0 | 0 bytes |                 |
 p1_y201608 |  19 |        1,766 |         0 | 0 bytes |                 |
 p1_y201609 |  18 |        1,767 |         0 | 0 bytes |                 |
 p1_y201610 |  17 |        1,768 |         0 | 0 bytes |                 |
 p1_y201611 |  16 |        1,769 |         0 | 0 bytes |                 |
 p1_y201612 |  15 |        1,770 |         0 | 0 bytes |                 |

-- 20만건 입력 
select dblink_connect('myconn','dbname=test port=5436 user=postgres password=oracle');
select loop_insert_p1('2016-01-02 00:00:00', 200000);

-- 20만건 입력 후 TXID_CURRENT 확인  
$ get_time.sh
 txid_current
--------------
      201,790

-- Autovacuum 수행 종료 후 테이블 나이 및 TXID_CURRENT() 확인 	  
$ get_info.sh
  relname   |  age   | relfrozenxid | reltuples |  size   |   last_autoanalze   |   last_autovacuum
------------+--------+--------------+-----------+---------+---------------------+---------------------
 p1_y201601 | 40,001 |      161,790 |   5.2e+06 | 380 MB  | 2016-09-06 12:21:55 | 2016-09-06 12:25:15
 p1_y201602 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201603 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201604 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201605 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201606 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201607 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201608 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201609 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201610 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201611 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03
 p1_y201612 | 40,000 |      161,791 |         0 | 0 bytes |                     | 2016-09-06 12:23:03

$ get_time.sh
 txid_current
--------------
      201,791

-- Autovacuum 수행 시간 확인 
2016-09-06 12:21:55 KST LOG:  automatic analyze of table "test.public.p1_y201601" system usage: CPU 0.07s/0.29u sec elapsed 21.81 sec
2016-09-06 12:25:15 KST LOG:  automatic vacuum of table "test.public.p1_y201601": index scans: 0
        pages: 0 removed, 48599 remain, 0 skipped due to pins, 0 skipped frozen
        tuples: 0 removed, 5200000 remain, 0 are dead but not yet removable
        buffer usage: 63817 hits, 33441 misses, 48604 dirtied
        avg read rate: 1.609 MB/s, avg write rate: 2.339 MB/s
        system usage: CPU 1.35s/0.34u sec elapsed 162.35 sec

-- FROZEN 여부 확인 
-- 0~48223 블록까지 "frozen" 상태가 됨
-- 48224~48598 블록은 "frozen" 상태가 아님 
-- 이렇게 일부가 "unfrozen" 상태인 이유는 vacuum_freeze_min_age=4만으로 설정했기 때문 임
-- 만일 해당 파라미터 값을 0으로 설정하면 모든 블록이 "frozen" 상태가 됨
select * from pg_visibility_map_summary('p1_y201601');
 all_visible | all_frozen
-------------+------------
       48599 |      48224
	   
-- 블록 별로 Frozen 여부를 확인한 결과, 48224~48598 블록은 unfrozen	상태인 것을 알 수 있음   
select * from pg_visibility_map('p1_y201601') where all_frozen='f';
 blkno | all_visible | all_frozen
-------+-------------+------------
 48224 | t           | f
... 
 48598 | t           | f

-- 48224 블록 내부를 확인한 결과, 
-- 해당 페이지의 XMIN 값 중에서 테이블의 RelfrozenXID 보다 큰 값이 존재하는 것을 확인함
select t_ctid,
       case lp_flags
           when 0 then 'Unused'
           when 1 then 'Normal'
           when 2 then 'Redirect to ' || lp_off
           when 3 then 'Dead'
        end,
        t_xmin,
        t_xmax
from    heap_page_items(get_raw_page('p1_y201601',48224));

   t_ctid    |  case  | t_xmin | t_xmax
-------------+--------+--------+--------
 (48224,1)   | Normal | 161757 |      0
 (48224,2)   | Normal | 161758 |      0
 (48224,3)   | Normal | 161759 |      0
...
 (48224,34)  | Normal | 161790 |      0     --------------> 해당 테이블의 relfrozenxid 
 (48224,35)  | Normal | 161791 |      0
...
 (48224,105) | Normal | 161861 |      0
 (48224,106) | Normal | 161862 |      0
 (48224,107) | Normal | 161863 |      0


3-7. 2월 파티션에 16만건 입력

-- 16만건 입력 
select loop_insert_p1('2016-02-02 00:00:00', 160000);

-- 16만건 입력 후 TXID_CURRENT() 확인
$ get_time.sh
 txid_current
--------------
      361,796

-- Autovacuum 수행 종료 후 테이블 나이 및 TXID_CURRENT() 확인 
$ get_info.sh
  relname   |  age   | relfrozenxid |  reltuples  |  size   |   last_autoanalze   |   last_autovacuum
------------+--------+--------------+-------------+---------+---------------------+---------------------
 p1_y201601 | 40,001 |      321,797 | 5.19991e+06 | 380 MB  | 2016-09-06 12:21:55 | 2016-09-06 13:07:25
 p1_y201602 | 40,001 |      321,797 | 5.15997e+06 | 377 MB  | 2016-09-06 13:10:05 | 2016-09-06 13:09:38
 p1_y201603 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201604 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201605 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201606 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201607 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201608 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201609 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201610 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201611 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39
 p1_y201612 | 40,001 |      321,797 |           0 | 0 bytes |                     | 2016-09-06 13:07:39

$ get_time.sh
 txid_current
--------------
      361,798
	  
-- Autovacuum 수행 시간 확인 
2016-09-06 13:07:25 KST LOG:  automatic vacuum of table "test.public.p1_y201601": index scans: 0
        pages: 0 removed, 48599 remain, 0 skipped due to pins, 48224 skipped frozen
        tuples: 0 removed, 5199908 remain, 0 are dead but not yet removable
        buffer usage: 407 hits, 392 misses, 376 dirtied
        avg read rate: 2.407 MB/s, avg write rate: 2.308 MB/s
        system usage: CPU 0.01s/0.00u sec elapsed 1.27 sec

2016-09-06 13:09:38 KST LOG:  automatic vacuum of table "test.public.p1_y201602": index scans: 0
        pages: 0 removed, 48225 remain, 0 skipped due to pins, 0 skipped frozen
        tuples: 0 removed, 5160000 remain, 0 are dead but not yet removable
        buffer usage: 63862 hits, 32619 misses, 40725 dirtied
        avg read rate: 1.915 MB/s, avg write rate: 2.391 MB/s
        system usage: CPU 0.82s/0.79u sec elapsed 133.06 sec

2016-09-06 13:10:05 KST LOG:  automatic analyze of table "test.public.p1_y201602" system usage: CPU 0.02s/0.23u sec elapsed 26.47 sec

-- FROZEN 여부 확인 
select * from pg_visibility_map_summary('p1_y201601');
 all_visible | all_frozen
-------------+------------
       48599 |      48599

select * from pg_visibility_map_summary('p1_y201602');
 all_visible | all_frozen
-------------+------------
       48225 |      47850


3-8. 3월 파티션에 16만건 입력

-- 16만건 입력 
select loop_insert_p1('2016-03-02 00:00:00', 160000);

-- 16만건 입력 후 TXID_CURRENT() 확인
$ get_time.sh
 txid_current
--------------
      521,802
	  
-- Autovacuum 수행 종료 후 테이블 나이 및 TXID_CURRENT() 확인 
$ get_info.sh
  relname   |  age   | relfrozenxid |  reltuples  |  size   |   last_autoanalze   |   last_autovacuum
------------+--------+--------------+-------------+---------+---------------------+---------------------
 p1_y201601 | 40,003 |      481,802 | 5.19982e+06 | 380 MB  | 2016-09-06 12:21:55 | 2016-09-06 13:18:54
 p1_y201602 | 40,003 |      481,802 |  5.1599e+06 | 377 MB  | 2016-09-06 13:10:05 | 2016-09-06 13:18:55
 p1_y201603 | 40,003 |      481,802 | 5.15997e+06 | 377 MB  | 2016-09-06 13:21:53 | 2016-09-06 13:21:30
 p1_y201604 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201605 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201606 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201607 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201608 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:18:55
 p1_y201609 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201610 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201611 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09
 p1_y201612 | 40,003 |      481,802 |           0 | 0 bytes |                     | 2016-09-06 13:19:09

$ get_time.sh
 txid_current
--------------
      521,805

-- Autovacuum 수행 시간 확인 
2016-09-06 13:18:54 KST LOG:  automatic vacuum of table "test.public.p1_y201601": index scans: 0
        pages: 0 removed, 48599 remain, 0 skipped due to pins, 48598 skipped frozen
        tuples: 0 removed, 5199815 remain, 0 are dead but not yet removable
        buffer usage: 30 hits, 21 misses, 0 dirtied
        avg read rate: 54.706 MB/s, avg write rate: 0.000 MB/s
        system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

2016-09-06 13:18:55 KST LOG:  automatic vacuum of table "test.public.p1_y201602": index scans: 0
        pages: 0 removed, 48225 remain, 0 skipped due to pins, 47850 skipped frozen
        tuples: 0 removed, 5159898 remain, 0 are dead but not yet removable
        buffer usage: 386 hits, 392 misses, 378 dirtied
        avg read rate: 2.539 MB/s, avg write rate: 2.448 MB/s
        system usage: CPU 0.01s/0.00u sec elapsed 1.20 sec

2016-09-06 13:21:30 KST LOG:  automatic vacuum of table "test.public.p1_y201603": index scans: 0
        pages: 0 removed, 48225 remain, 0 skipped due to pins, 0 skipped frozen
        tuples: 0 removed, 5160000 remain, 0 are dead but not yet removable
        buffer usage: 63699 hits, 32780 misses, 48230 dirtied
        avg read rate: 1.658 MB/s, avg write rate: 2.439 MB/s
        system usage: CPU 0.82s/0.72u sec elapsed 154.49 sec

2016-09-06 13:21:53 KST LOG:  automatic analyze of table "test.public.p1_y201603" system usage: CPU 0.00s/0.30u sec elapsed 23.45 sec

-- FROZEN 여부 확인 
select * from pg_visibility_map_summary('p1_y201601');
 all_visible | all_frozen
-------------+------------
       48599 |      48599

select * from pg_visibility_map_summary('p1_y201602');
 all_visible | all_frozen
-------------+------------
       48225 |      48225

select * from pg_visibility_map_summary('p1_y201603');
 all_visible | all_frozen
-------------+------------
       48225 |      47850



글을 마치며


이제 Vacuum에 대해서 하나의 수수께끼만이 남았습니다. XMIN 값이 4바이트 정수의 범위를 넘어서면, 다시 말해 wraparound가 발생하면 XMIN 값이 FROZEN XID (2)로 변경될까요? 이 문제를 풀기위해서 1주일간 테스트를 진행했지만 원하는 결과를 얻지 못했습니다. 이 부분이 정리되면 Vacuum 로직에 대해서 깔끔히 정리할 예정입니다.

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

OLTP 시스템 튜닝에 있어서 가장 중요하고도 어려운 숙제는 싱글 블록 IO (인덱스를 이용한 테이블 액세스시 발생하는 디스크 Random IO)를 얼마만큼 최소화 할 수 있는지 입니다.


일반적인 디스크 환경에서 싱글 블록 IO의 성능은 대략 초당 100블록 정도입니다. 따라서, 만일 1,000건의 레코드에 대한 테이블 액세스 시에 모두 디스크 IO가 발생한다면 해당 쿼리의 성능은 10초 정도가 소요됩니다. 이는 온라인 시스템에 있어서는 참을 수 없을 만큼 답답한 속도입니다.


따라서, 이런 문제를 해결하기 위한 다양한 방법이 존재합니다. KEEP 버퍼를 이용해서 테이블, 인덱스 블록들을 메모리에 상주시킴으로써 디스크 IO를 최대한 제거하거나, 인덱스 클러스터링 팩터를 향상시키기 위해 테이블을 인덱스 칼럼 순으로 재 생성하거나, IOT, 클러스터 인덱스 등의 시도 또한 싱글 블록 IO를 효과적으로 처리하기 위한 튜닝 방법의 일환이라고 할 수 있습니다.


그리고 쿼리의 조건절 및 SELECT 절에 사용되는 칼럼의 수가 상대적으로 적다면, 해당 칼럼들을 이용한 결합 인덱스로 생성함으로써 테이블 액세스를 제거하는 방법 또한 존재합니다. 이러한 용도의 인덱스를 Covered Index(또는 Covering Index)라고 하며 해당 인덱스를 이용한 액세스 방법을 Index Only Scan이라고 합니다.


Note
싱글 블록 IO 지연으로 발생하는 성능 이슈들은 SSD를 도입하면 자연스럽게 해결됩니다. 최근에 발표된 EMC DSSD D5의 스펙을 보면 10M IOPS, 최대 100 microsecond latency, 144 TB 용량을 지원합니다. 기존의 HDD의 랜덤 IO latency가 0.01초(대략 초당 100블록)인 것과 비교해보면 EMC D5의 랜덤 IO latency는 0.0001초 (대략 초당 10,000블록)이므로 수치상으로만 보면 100배가 빨라집니다. 아직까지는 가격이라는 진입장벽이 존재합니다만, ROI와 TCO등을 고려했을 때는 SSD 도입이 나쁘지 않은 선택일 수 있습니다. 이런 것을 보면 튜닝 방식도 유행과 같이 돌고 도는 것 같습니다. 초기의 리소스 증설에 의한 튜닝에서 애플리케이션/쿼리 튜닝으로 변모했다가, 데이터 량의 증가와 사용자 요구 수준의 향상으로 인해 다시금 리소스 증설 또는 어플라이언스의 도입 등을 통한 튜닝으로 변화하는 추세인 것 같습니다. 이런 맥락에서 보면 쿼리 튜닝만을 무기로 갖고 있는 튜너들의 입지는 점차로 좁아질 수 밖에 없습니다. 이를 극복하기 위해서는 다양한 DBMS를 다룰 수 있는 능력과 전체적인 아키텍처와 동작원리의 이해를 통한 거시적인 관점의 튜닝을 할 수 있는 능력을 배양해야만 할 것 같습니다. (사설이 너무 길어졌습니다.:))




1. Index Only Scan의 특이점


PostgreSQL의 경우, Covered Index를 생성하더라도 항상 Index Only Scan을 수행하는 것은 아닙니다. (일반적인 제약사항은 논외로 하고, 조건절 및 SELECT절 칼럼이 모두 인덱스에 포함된 경우라 할지라도) 그 이유는 인덱스 리프 블록내에 저장된 레코드들은 MVCC를 위한 정보가 존재하지 않기 때문입니다. 즉, 인덱스 리프 블록에 저장된 레코드들에 대한 조회 가능 여부는 테이블 액세스 후에 해당 레코드의 XMIN/XMAX 정보를 확인해야만 합니다. 하지만 이렇다면 애써 생성한 Covered Index를 활용할 수도 없을 뿐 아니라 성능 이슈를 해결하기도 힘듭니다. 이러한 구조적인 한계점을 보완하기 위해 PostgreSQL은 Visibility Map이라는 아키텍처를 제공합니다.



2. Visibility Map 이란?


Visibility Map은 테이블 page당 두개의 Bit를 가지고 있습니다.

  • 1번째 Bit는 해당 Page가 모든 트랜잭션에 'Visible' 한지의  판단하는 용도입니다. 1로 설정되었다면, 해당 Page 내의 모든 레코드는 모든 트랜잭션에서 볼 수 있는 상태입니다. 0이라면 해당 페이지 내에는 변경된 레코드가 존재한다는 의미입니다. 이는 오라클의 Dirty 블록이라고 이해하시면 됩니다.
  • 2번째 Bit는 9.6부터 추가된 Bit로써 모든 레코드가 'Frozen' 상태인지를 판단하는 용도입니다. 1로 설정되었다면, 해당 Page 내의 모든 레코드는 Frozen 상태이므로 Anti-Wraparound를 위한 베큠 대상에서 제외됩니다. 이는 배큠 성능 향상을 위해 9.6에서 도입된 기능입니다.

그렇다면 PostgreSQL은 어떻게 Visibility Map을 이용해서 Index Only Scan을 처리할까요?



3. Visibility Map을 이용한 Index Only Scan 동작 방식


테이블의 어떠한 블록에도 변경사항이 없다면, MVCC 체크를 위해 테이블 레코드를 액세스할 필요가 없습니다. 이러한 경우에는 인덱스만을 이용한 Index Only Scan으로 동작합니다. 즉, Visibility Map내의 1번째 Bit를 체크한 후, 모두 1 (True) 이라면 Index Only Scan을 수행하고 그렇지 않다면 테이블 액세스가 필요하다는 의미입니다.



4. pg_visiblity extension 및 explain을 이용한 테스트 수행


pg_visibility extension은 visibility map의 내용을 확인할 수 있는 기능을 제공합니다. 그리고 explain의 BUFFERS 옵션을 이용하면 쿼리 수행 시에 사용된 IO 블록 수를 확인할 수 있습니다. 이를 이용해서 다음과 같은 테스트를 수행합니다.


4-1. pg_visibility 익스텐션 설치 및 테이블/인덱스 생성 

-- 익스텐션 설치
create extension pg_visibility; 

-- 테이블 생성 및 데이터 입력
create table s1 (c1 integer, c2 integer, c3 integer, dummy char(10));
insert into s1 select generate_series(1,1000000), generate_series(1,1000000), generate_series(1,1000000), 'dummy';

-- c1,c2,c3 칼럼을 구성된 인덱스 생성 
create index s1_idx01 on s1 (c1, c2, c3);


4.2 Visibility Map 확인

-- 현재 S1 테이블은 Vacuum 수행 전이므로 all_visible=true인 블록이 0개임
-- 즉, Index Only Scan을 수행할 수 없는 상태임
select * from pg_visibility_map_summary('s1');
 all_visible | all_frozen
-------------+------------
           0 |          0


4.3 테스트를 위해 seq_scan=off 설정  

set enable_seqscan=off;


4.4 테스트 쿼리 수행

-- 현재 모든 블록의 all_visible=false이므로 
-- 조건절 및 SELECT 절 칼럼이 모두 인덱스에 포함되어 있지만 Index Only Scan으로 처리되지 않음 
explain (BUFFERS TRUE, analyze) 
select c1, c2, c3 from s1 
where c1 between 1 and 10000
and c1 <> 0;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on s1  (cost=131.67..6494.54 rows=4975 width=12) (actual time=0.678..2.445 rows=10000 loops=1)
   Recheck Cond: ((c1 >= 1) AND (c1 <= 10000))
   Heap Blocks: exact=64
   Buffers: shared hit=105
   ->  Bitmap Index Scan on s1_idx01  (cost=0.00..130.43 rows=5000 width=0) (actual time=0.663..0.663 rows=10000 loops=1)
         Index Cond: ((c1 >= 1) AND (c1 <= 10000))
         Buffers: shared hit=41
 Planning time: 0.137 ms
 Execution time: 3.457 ms


4.5 베큠 수행 후 Visibility Map 확인

-- 베큠 수행 
vacuum s1;

-- 배큠 수행 후 all_visible=true인 블록이 6,370개로 변경됨
-- 즉, 모든 블록이 all_visible=true로 변경됨
select * from pg_visibility_map_summary('s1');
 all_visible | all_frozen
-------------+------------
        6370 |       6370


4.6 쿼리 수행

-- Index Only Scan으로 수행됨
explain (BUFFERS TRUE, analyze) 
select c1, c2, c3 from s1 
where c1 between 1 and 10000
and c1 <> 0;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using s1_idx01 on s1  (cost=0.42..398.04 rows=10383 width=12) (actual time=0.011..1.662 rows=10000 loops=1)
   Index Cond: ((c1 >= 1) AND (c1 <= 10000))
   Heap Fetches: 0
   Buffers: shared hit=42
 Planning time: 0.098 ms
 Execution time: 2.543 ms


4.7 S1 테이블 업데이트 수행 후 Visibility Map 확인

-- S1 테이블 변경 
update s1 set c2=0 where c1 between 1 and 6000; 

-- 현재 Visibility Map 확인
-- 확인 결과, 0~38번 블록까지는 all_visible=false이고 그 이후는 true임 
 
 select * from pg_visibility('s1');

 blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     0 | f           | f          | f
     1 | f           | f          | f
     2 | f           | f          | f
...
    38 | f           | f          | f   ---------------> 38번 블록까지 f 
    39 | t           | t          | t    --------------> 39번 블록부터 t
    40 | t           | t          | t
    41 | t           | t          | t


4.8 쿼리 수행

-- 현재 상태에서 기존 쿼리를 수행하면 다시 테이블을 액세스함
explain (BUFFERS TRUE, analyze) 
select c1, c2, c3 from s1 
where c1 between 1 and 10000
and c1 <> 0;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on s1  (cost=245.02..7134.34 rows=9424 width=12) (actual time=1.352..3.422 rows=10000 loops=1)
   Recheck Cond: ((c1 >= 1) AND (c1 <= 10000))
   Heap Blocks: exact=103
   Buffers: shared hit=190
   ->  Bitmap Index Scan on s1_idx01  (cost=0.00..242.67 rows=9424 width=0) (actual time=1.231..1.231 rows=16000 loops=1)
         Index Cond: ((c1 >= 1) AND (c1 <= 10000))
         Buffers: shared hit=87
 Planning time: 0.110 ms
 Execution time: 4.329 ms

-- all_visible=true인 블록들을 조회할 때도 테이블 액세스가 발생함
-- 이를 통해, 쿼리 실행계획은 블록 레벨이 아닌 전체 레벨에서 관리됨을 확인 
explain (BUFFERS TRUE, analyze) 
select c1, c2, c3 from s1 
where c1 between 20001 and 30000
and c1 <> 0;  
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on s1  (cost=244.44..7135.40 rows=9367 width=12) (actual time=0.609..2.533 rows=10000 loops=1)
   Recheck Cond: ((c1 >= 20001) AND (c1 <= 30000))
   Heap Blocks: exact=65
   Buffers: shared hit=106
   ->  Bitmap Index Scan on s1_idx01  (cost=0.00..242.10 rows=9367 width=0) (actual time=0.594..0.594 rows=10000 loops=1)
         Index Cond: ((c1 >= 20001) AND (c1 <= 30000))
         Buffers: shared hit=41
 Planning time: 0.078 ms
 Execution time: 3.466 ms


4.9 베큠 수행 후 Visibility Map 확인

-- 다시 베큠 수행
vacuum s1;

-- 다시 모든 블록의 all_visible=true로 변경된 것을 확인
 select * from pg_visibility('s1');
  
 blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     0 | t           | t          | t
     1 | t           | t          | t
     2 | t           | t          | t
     3 | t           | t          | t
     4 | t           | t          | t
     5 | t           | t          | t


4.10 쿼리 수행

-- Index Only Scan으로 수행됨
-- 하지만, UPDATE로 인한 인덱스 블록 증가로 인해 IO 블록 수가 증가함 
-- 기존 42->88블록으로 증가 

explain (BUFFERS TRUE, analyze) 
select c1, c2, c3 from s1 
where c1 between 1 and 10000
and c1 <> 0;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using s1_idx01 on s1  (cost=0.42..359.21 rows=9368 width=12) (actual time=0.013..1.674 rows=10000 loops=1)
   Index Cond: ((c1 >= 1) AND (c1 <= 10000))
   Heap Fetches: 0
   Buffers: shared hit=88
 Planning time: 0.132 ms
 Execution time: 2.566 ms

-- UPDATE와 관련없는 블록들에 대해서는 기존과 같이 42블록만을 Index Only Scan으로 수행됨
explain (BUFFERS TRUE, analyze) 
select c1, c2, c3 from s1 
where c1 between 20001 and 30000
and c1 <> 0; 
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using s1_idx01 on s1  (cost=0.42..357.92 rows=9311 width=12) (actual time=0.016..1.561 rows=10000 loops=1)
   Index Cond: ((c1 >= 20001) AND (c1 <= 30000))
   Heap Fetches: 0
   Buffers: shared hit=42
 Planning time: 0.077 ms
 Execution time: 2.640 ms



5. 테스트 요약


상기 테스트를 요약하면 다음과 같습니다. (세부 내용은 표-1 참조)

  1. Index Only Scan은 Visibility Map을 참고해서 수행된다.
  2. Index Only Scan은 테이블 내의 변경된 블록 1개라도 있으면 동작하지 않는다.
  3. 만일, 테이블 내의 변경된 블록이 있은 경우에는 Vacuum을 수행해야만 한다. 
  4. 단, Vacuum 수행 시에 이전에 수행되고 있던 트랜잭션이 없어야 한다. (만일, 있다면 Visibility Map은 true로 변경되지 않는다)

이와 더불어, 인덱스 칼럼에 변경작업이 수행된 경우에는 PostgreSQL의 구조적인 특징으로 인해 IO 블록 수가 증가하게 됩니다. (표-1에서 42->88블록으로 증가한 부분 참조) 이 부분에 대해서는 별도 포스팅하도록 하겠습니다.


표-1. Index Only Scan 테스트 요약표


글을 쓰다보니 너무 길어졌습니다. 긴 글 읽어주셔서 감사합니다. :)


저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License


 

티스토리 툴바