My Books

My Slides

rss 아이콘 이미지

Search

'PostgreSQL'에 해당되는 글 21건

  1. 2017.07.31 2017년 8월 PostgreSQL 1-Day 튜닝 교육 자료입니다.
  2. 2017.07.11 Modulo 연산을 이용한 PostgreSQL XID 비교 로직 설명
  3. 2017.06.10 PostgreSQL 1-Day 교육 자료 업데이트되었습니다. (2)
  4. 2017.05.24 PostgreSQL 10 - Parallel Index Scan 소개 (1)
  5. 2017.05.24 PostgreSQL 10 - Partition Wise Join (PWJ) 및 조인 Pruning 테스트 (1)
  6. 2017.05.23 PostgreSQL 9.6 - 조인 Pruning이 수행되지 않는 문제점 해결 방법
  7. 2017.05.23 PostgreSQL 10 베타 버전을 소스 파일로 설치하는 방법
  8. 2017.04.27 PostgreSQL 1-Day 튜닝 교육을 시작합니다.
  9. 2017.04.24 개발자를 위한 PostgreSQL 튜닝 워크샵 교육 자료
  10. 2016.09.11 PostgreSQL 서적 집필을 시작했습니다. (3)
  11. 2016.09.11 [PostgreSQL] Vacuum의 마지막 수수께끼였던 FrozenXID에 대한 고찰
  12. 2016.09.07 [PostgreSQL 9.6] Visibility Map 내의 FROZEN 비트를 이용한 Vacuum 성능 향상에 대한 고찰
  13. 2016.09.06 [PostgreSQL] 아무도 자세히 알려주지 않았던 Autovacuum의 위험성 (3)
  14. 2016.09.04 [PostgreSQL] Visibility Map을 이용한 Index Only Scan 동작 방식 분석
  15. 2016.09.03 [PostgreSQL] Partial Index를 이용한 흥미로운 튜닝 방법
  16. 2016.09.03 PostgreSQL에서 TXID_CURRENT()와 DBLINK를 이용해서 XID를 아주 빠르게 증가시키는 방법
  17. 2016.08.31 PostgreSQL의 pageinspect extension을 이용한 블록 덤프 방법
  18. 2016.08.31 PostgreSQL의 dblink extension을 이용한 autonomous 트랜잭션 구현 방법
  19. 2016.08.31 PostgreSQL에서 external (foreign) 테이블 생성 방법 및 이를 이용한 fulltime.sh 포팅 방안
  20. 2016.08.31 [PostgreSQL 9.6 New Feature] wait_event_type 및 wait_event 칼럼을 이용한 대기이벤트 모니터링

2017년 8월 PostgreSQL 1-Day 튜닝 교육 자료입니다.

PostgreSQL 2017.07.31 20:46 Posted by 시연아카데미

2017년 8월 교육을 위해서 기존 교육 자료에 아래의 내용을 추가했습니다.

 

아키텍처

  •  PostgreSQL Plan Caching과 Oracle Shared Pool 비교
  •  Unlogged 테이블, Temporary 테이블, 일반 테이블 속성 및 성능 비교
  •  Modulo 연산을 이용한 XID 비교 로직 
  •  파티션 개수에 따른 입력 성능 테스트 (버전 9.6 및 10)

 

옵타마이저

  • 클래식 해시 조인 수행 절차 
  • Grace 해시 조인 수행 절차 
  • Simple View Merging 예제 변경  
  • JPPD 예제 변경 및 JPPD 구현 방안 추가

 

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

Modulo 연산을 이용한 PostgreSQL XID 비교 로직 설명

PostgreSQL 2017.07.11 23:11 Posted by 시연아카데미

PostgreSQL은 XID 간의 비교를 위해서 Modulo 연산을 사용합니다. 

 

개념은 간단합니다.

 

현재 XID 기준으로 2^31 (대략 20억) 이전 데이터는 OLD 데이터이고, 2^31 이후 데이터는 NEW 데이터라는 개념입니다.

 

그런데 수치를 대입해서 계산해보면 이해가 되지 않는 부분이 있습니다.

 

그동안은 그냥 그러려니 하고 넘겼던 부분인데, 6월 수강자 분중에서 이 부분을 질문하신 분이 계셔서 조금 더 자세히 정리했습니다.

 

Modulo 연산을 이용한 PosgreSQL XID 비교 로직 설명 from 시연아카데미

 

D1, ID2 값을 입력하면서 직접 확인해보고 싶은 분들은 아래의 C-Source를 이용해보세요.

 

컴파일 및 사용 예제

 

$ cc -o comp comp.c

$ comp 4294967295 3
id1=(unsigned int) 4294967295            id1=(int) -1
id2=(unsigned int) 3                          id2=(int) 3
[DEBUG] diff = (int) (id1 - id2) => [-4]
[DEBUG] return (diff < 0)
[1] = Result of TransactionIdPrecedes(id1,id2)

 

comp.c

 

#include <stdio.h>
#include <stdlib.h>
#include<stdbool.h>

typedef unsigned int TransactionId;
#define TransactionIdIsNormal(xid)           ((xid) >= 3)

bool
TransactionIdPrecedes(TransactionId id1, TransactionId id2)
{
        /*
         ** If either ID is a permanent XID then we can just do unsigned
         ** comparison.  If both are normal, do a modulo-2^32 comparison.
         **/
        int           diff;

        if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2)) {
                printf("[DEBUG] id1 or id2 isn't a Normal XID, Therefore, it does not perform 2^31 modular operations\n");
                printf("[DEBUG] return (id1 < id2), id1=%u id2=%u\n", id1, id2);
                return (id1 < id2);
        }

        diff = (int) (id1 - id2);
        printf("[DEBUG] diff = (int) (id1 - id2) => [%d] \n", (int) (id1 - id2));
        printf("[DEBUG] return (diff < 0) \n");

        return (diff < 0 );
}

main(int argc, char *argv[])
{
        TransactionId id1;
        TransactionId id2;
        int res;

        if (argc != 3) {
                printf("usage> comp <id1> <id2>\n");
                exit(-1);
        }

        id1 = atoi(argv[1]);
        id2 = atoi(argv[2]);

        printf("id1=(unsigned int) %u\t\t id1=(int) %d\n", id1, id1);
        printf("id2=(unsigned int) %u\t\t id2=(int) %d\n", id2, id2);

        printf("[%d] = Result of TransactionIdPrecedes(id1,id2)\n", TransactionIdPrecedes(id1, id2));
}

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

PostgreSQL 1-Day 교육 자료 업데이트되었습니다.

PostgreSQL 2017.06.10 23:29 Posted by 시연아카데미

2017년 6월 교육을 위해서 기존 교육 자료에 파티션, Outer Join, 통계 정보등의 내용을 추가했습니다.

 

 

 

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

PostgreSQL 10 - Parallel Index Scan 소개

PostgreSQL 2017.05.24 22:37 Posted by 시연아카데미

이번 시간에 소개할 내용은 PostgreSQL 10 버전부터 지원하는 Parallel Index Scan 기능입니다.

 

기능을 확인하기 전까지는 ORACLE의 Parallel Index Fast Full Scan 기능과 유사할 것으로 생각했는데, 조금 더 스마트합니다. (박수!!)

 

PostgreSQL 10 버전의 Parallel Index Scan 기능은 Index Range Scan시에도 병렬 처리가 가능합니다.

 

즉, ORACLE은 Index Fast Full Scan 방식을 이용해서 전체 인덱스 블록을 스캔할 때만 병렬 처리가 가능하지만, PostgreSQL은 인덱스의 특정 범위에 대한 병렬 처리도 가능합니다.

 

알고리즘은 다음과 같습니다.

  1. 조건절 범위에 해당하는 "Start 리프 블록"과 "End 리프 블록"의 위치를 계산합니다.
  2. 처리 범위가 인덱스 병렬 처리를 할 정도로 큰지 확인합니다.
  3. 만일 크다면, 크기에 따라서 Worker 개수를 설정한 후에, "Start" ~ "End" 범위를 나눠서 처리합니다.
  4. 만일 작다면, 싱글 프로세스로 처리합니다. 
      

이처럼, 인덱스 Range 스캔 시에도 병렬 처리를 지원함으로써 튜닝 시에 더욱 다양한 방법을 적용할 수 있게 됐습니다. (튜너 입장에서는 좋은 무기를 하나 얻은 셈입니다)

 

그럼 테스트 내용을 살펴보겠습니다.

 

 

테스트 환경 구성

 

테스트 환경은 이전 포스트에서 사용한 환경을 이용합니다.

 

그리고 np1 테이블의 c1 칼럼에 인덱스를 생성합니다.

 

create index np1_c1_idx on np1(c1);

 

Parallel 처리와 관련된 파라미터 확인 및 설정 변경

 

아래의 파라미터 중에서 Worker 개수와 관련된 파라미터인 max_parallel_workers는 24로, max_parallel_workers_per_gather는 8로 변경한 후에 재기동합니다.

 

postgres=# select name, setting, unit from pg_settings where name like '%parallel%';
              name               | setting | unit
---------------------------------+---------+------
 force_parallel_mode             | off     |
 max_parallel_workers            | 8       |
 max_parallel_workers_per_gather | 2       |
 min_parallel_index_scan_size    | 64      | 8kB
 min_parallel_table_scan_size    | 1024    | 8kB

 

 

-- 재기동 후에 변경 내용 확인

 

재기동 후에 파라미터가 정상적으로 변경된 것을 확인합니다.

 

postgres=# select name, setting, unit from pg_settings where name like '%parallel%';
              name               | setting | unit
---------------------------------+---------+------
 force_parallel_mode             | off     |
 max_parallel_workers            | 24      |
 max_parallel_workers_per_gather | 8       |
 min_parallel_index_scan_size    | 64      | 8kB
 min_parallel_table_scan_size    | 1024    | 8kB

 

-- min_parallel_table_scan_size 파라미터의 의미

 

해당 파라미터는 테이블 병렬 처리 여부를 결정하는 테이블 최소 크기를 정의합니다. 기본설정값은 8MB입니다. 즉, 8MB 이상이면 병렬 처리 대상입니다. 그리고 해당 크기의 3배수 단위로 Worker가 1개씩 추가되며, 최대 7까지 할당됩니다.

 

-- min_parallel_index_scan_size 파라미터의 의미

 

해당 파라미터는 인덱스 병렬 처리 여부를 결정하는 인덱스 최소 크기를 정의합니다. 기본설정값은 512kB입니다. 즉, 512kB 이상이면 병렬 처리 대상입니다. 그리고 해당 크기의 3배수 단위로 Worker가 1개씩 추가되며, 최대 7까지 할당됩니다.

 

 

Parallel Index Scan 동작 방식 세부 내용

 

테이블 병렬 처리는 통계 정보를 이용합니다. 그런데 인덱스 병렬 처리는 조건절에 해당하는 범위를 이용해서 결정합니다. 이 부분은 소스를 잠깐 보는 게 도움이 될 것 같습니다.

 

아래 소스를 보면, 처리 대상 인덱스 페이지수를 계산한 결과를 index_pages 변수에 저장하고, 해당 값과 min_parallel_index_scan_size 값을 비교합니다.

 

그리고 min_parallel_index_scan_size * 3을 수행하면서 Worker 개수를 1씩 증가한다는 것을 알 수 있습니다.

 

그리고 소스의 아랫 부분을 보면, Worker의 개수는 "테이블 병렬 처리를 위해 계산된 수치와 인덱스 병렬 처리를 위해 계산된 수치 중에서 더 작은 수치로 설정"한다는 것을 알 수 있습니다.

 

src/backend/optimizer/path/allpaths.c

if (index_pages >= 0)
{
        int                     index_parallel_workers = 1;
        int                     index_parallel_threshold;

        /* same calculation as for heap_pages above */
		index_parallel_threshold = Max(min_parallel_index_scan_size, 1);        
		while (index_pages >= (BlockNumber) (index_parallel_threshold * 3)) 
        {
                index_parallel_workers++;
                index_parallel_threshold *= 3;
                if (index_parallel_threshold > INT_MAX / 3)
                        break;          /* avoid overflow */
        }		

        if (parallel_workers > 0) 
                parallel_workers = Min(parallel_workers, index_parallel_workers);
        else
                parallel_workers = index_parallel_workers;

 

Parallel Worker 개수 산정 방식

 

테이블과 인덱스 스캔 시에 사용되는 Parallel Worker 개수는 다음과 같습니다.

 

표-1. Parallel Worker 개수

 

테스트 #1: Parallel Index Scan으로 수행 (범위는 백만)

 

아래의 쿼리를 수행하면, Worker 프로세스는 3개가 론칭됩니다. 왜 3개인지 살펴볼까요?

 

테이블 블록 수는 23,249이므로, 표-1에 따라서 Worker는 3개입니다.

 

그리고 인덱스 처리 범위는 5,465이므로, 표-1에 따라서 Worker는 5개입니다. 3개와 5개 중에서 최솟값을 선택하므로 3개의 Worker가 론칭되는 것입니다.

 

 
explain (analyze, buffers) 
select count(*) from np1 where c1 between 1000000 and 2000000 and dummy='dummy';

[DEBUG_INDEX_PQ] heap_pages=[23249.000000] min_parallel_table_scan_size=[1024]  index_pages=[2703.000000] min_parallel_index_scan_size=[64]

                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=32710.82..32710.83 rows=1 width=8) (actual time=263.027..263.027 rows=1 loops=1)
   Buffers: shared hit=12035
   ->  Gather  (cost=32710.50..32710.81 rows=3 width=8) (actual time=263.014..263.023 rows=4 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         Buffers: shared hit=12035
         ->  Partial Aggregate  (cost=31710.50..31710.51 rows=1 width=8) (actual time=250.533..250.533 rows=1 loops=4)
               Buffers: shared hit=11612
               ->  Parallel Index Scan using np1_c1_idx on np1  (cost=0.43..30915.98 rows=317806 width=0) (actual time=0.032..186.220 rows=250000 loops=4)
                     Index Cond: ((c1 >= 1000000) AND (c1 <= 2000000))
                     Filter: (dummy = 'dummy'::bpchar)
                     Buffers: shared hit=11612
 Planning time: 0.123 ms
 Execution time: 264.310 ms

 

테스트 #2: Serial Index Scan으로 수행 (범위는 2백만)

 

Parallel Index Scan의 성능을 확인해볼까요? max_parallel_workers_per_gather 파라미터를 0으로 설정한 후에 Serial Index Scan으로 수행하면 Parallel Index Scan보다 50% 이상 느립니다.

 

물론, 처리 범위와 환경에 따라서 성능 차이는 다르겠지만, Parallel Index Scan은 매우 효과적인 튜닝 방안인 것으로 보입니다.

 

set max_parallel_workers_per_gather=0;

explain (analyze, buffers)
select count(*) from np1 where c1 between 1000000 and 2000000 and dummy='dummy';
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=41721.38..41721.39 rows=1 width=8) (actual time=431.104..431.104 rows=1 loops=1)
   Buffers: shared hit=9105
   ->  Index Scan using np1_c1_idx on np1  (cost=0.43..39258.39 rows=985198 width=0) (actual time=0.044..299.974 rows=1000001 loops=1)
         Index Cond: ((c1 >= 1000000) AND (c1 <= 2000000))
         Filter: (dummy = 'dummy'::bpchar)
         Buffers: shared hit=9105
 Planning time: 0.119 ms
 Execution time: 431.169 ms

 

참고로, Parallel Index Scan은 Index Only Scan 방식 및 Bitmap Index Scan 방식에서도 동작합니다.

 

 

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

이전 포스트에서 언급한 것과 같이 PostgreSQL은 v9.6까지 파티션 조인 Pruning을 제공하지 않습니다.

 

그런데 EDB 사에서 작성한 문서 (여기 참조)를 보면, 10 버전부터는 선언적인 파티션 기능을 제공할 뿐 아니라 Partition Wise Join (이하 PWJ)도 제공한다고 설명하고 있습니다.

 

매우 반가운 소식이죠.

 

기쁜 마음에, PostgreSQL 베타 10 버전을 설치한 후에 PWJ와 조인 Pruning을 테스트했습니다.

 

음. 예상과 달리 PWJ도 지원되지 않고 조인 Pruning도 여전히 지원되지 않습니다. (정식 버전이 나오면 다시 테스트해봐야겠습니다)

 

테스트 결과를 살펴보겠습니다.

 

 

파티션용 테이블 환경 구성

 

파티션 생성 방법은 PARTITION BY 키워드를 이용합니다. 그리고 각 파티션은 PARTITION OF 키워드를 이용해서 생성합니다.

 

파티션 생성 시에 주의할 점은 "TO" 뒤에 지정된 값은 해당 값을 포함하지 않는다는 점입니다.

 

예를 들어, 2017년 1월 파티션은 "TO" 값을 ('2017-02-01')로 지정해야 합니다.

 

즉, ORACLE로 치면 "VALUES LESS THAN TO_DATE('2017-02-01', 'YYYY-MM-DD')"과 같은 의미입니다.

 

drop table p1 cascade;
drop table p2 cascade;

create table p1 (
    c1        integer,
    logdate   date,
    dummy     char(10)
) partition by range (logdate);

create table p2 (
    c1        integer,
    logdate   date,
	amount    integer,
    dummy     char(10)
) partition by range (logdate);


create table p1_y201701 partition of p1 for values from ('2017-01-01') to ('2017-02-01');
create table p1_y201702 partition of p1 for values from ('2017-02-01') to ('2017-03-01');
create table p1_y201703 partition of p1 for values from ('2017-03-01') to ('2017-04-01');
create table p1_y201704 partition of p1 for values from ('2017-04-01') to ('2017-05-01');
create table p1_y201705 partition of p1 for values from ('2017-05-01') to ('2017-06-01');
create table p1_y201706 partition of p1 for values from ('2017-06-01') to ('2017-07-01');
create table p1_y201707 partition of p1 for values from ('2017-07-01') to ('2017-08-01');
create table p1_y201708 partition of p1 for values from ('2017-08-01') to ('2017-09-01');
create table p1_y201709 partition of p1 for values from ('2017-09-01') to ('2017-10-01');
create table p1_y201710 partition of p1 for values from ('2017-10-01') to ('2017-11-01');
create table p1_y201711 partition of p1 for values from ('2017-11-01') to ('2017-12-01');
create table p1_y201712 partition of p1 for values from ('2017-12-01') to ('2018-01-01');

create table p2_y201701 partition of p2 for values from ('2017-01-01') to ('2017-02-01');
create table p2_y201702 partition of p2 for values from ('2017-02-01') to ('2017-03-01');
create table p2_y201703 partition of p2 for values from ('2017-03-01') to ('2017-04-01');
create table p2_y201704 partition of p2 for values from ('2017-04-01') to ('2017-05-01');
create table p2_y201705 partition of p2 for values from ('2017-05-01') to ('2017-06-01');
create table p2_y201706 partition of p2 for values from ('2017-06-01') to ('2017-07-01');
create table p2_y201707 partition of p2 for values from ('2017-07-01') to ('2017-08-01');
create table p2_y201708 partition of p2 for values from ('2017-08-01') to ('2017-09-01');
create table p2_y201709 partition of p2 for values from ('2017-09-01') to ('2017-10-01');
create table p2_y201710 partition of p2 for values from ('2017-10-01') to ('2017-11-01');
create table p2_y201711 partition of p2 for values from ('2017-11-01') to ('2017-12-01');
create table p2_y201712 partition of p2 for values from ('2017-12-01') to ('2018-01-01');

-- p1 테이블에 일자별로 만건 입력 

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p1 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, 'dummy');
	 end loop;
  end loop;
end $$;
	
-- p2 테이블에 일자별로 만건 입력

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p2 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, j, 'dummy');
	 end loop;
  end loop;
end $$;

 

Non-파티션 테이블 환경 구성

 

파티션 테이블의 테스트 결과와 비교하기 위해 Non-파티션 테이블을 생성합니다.

 

drop table np1;
drop table np2;

create table np1 (
    c1        integer,
    logdate   date,
    dummy     char(10)
);

create table np2 (
    c1        integer,
    logdate   date,
	amount    integer,
    dummy     char(10)
);

-- np1 테이블에 일자별로 만건 입력 

do $$	
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into np1 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, 'dummy');
	 end loop;
  end loop;
end $$;

-- np2 테이블에 일자별로 만건 입력

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into np2 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, j, 'dummy');
	 end loop;
  end loop;
end $$;

 

통계 정보 생성 및 크기 확인

 

월별 파티션 테이블 크기 및 건수를 확인합니다.

 

analyze np1;
analyze np2;

analyze p1;
analyze p2;

analyze p1_y201701; 
analyze p1_y201702;
analyze p1_y201703;
analyze p1_y201704;
analyze p1_y201705;
analyze p1_y201706;
analyze p1_y201707;
analyze p1_y201708;
analyze p1_y201709;
analyze p1_y201710;
analyze p1_y201711;
analyze p1_y201712;

analyze p2_y201701; 
analyze p2_y201702;
analyze p2_y201703;
analyze p2_y201704;
analyze p2_y201705;
analyze p2_y201706;
analyze p2_y201707;
analyze p2_y201708;
analyze p2_y201709;
analyze p2_y201710;
analyze p2_y201711;
analyze p2_y201712;

select relname, relpages, reltuples::integer 
from   pg_class
where  relname like 'p1%' or
       relname like 'p2%' or 
	   relname like 'np%'
order by 1;
  relname   | relpages | reltuples
------------+----------+-----------
 np1        |    23249 |   3650000
 np2        |    23249 |   3650000
 p1         |        0 |         0
 p1_y201701 |     1975 |    310000
 p1_y201702 |     1784 |    280000
 p1_y201703 |     1975 |    310000
 p1_y201704 |     1911 |    300000
 p1_y201705 |     1975 |    310000
 p1_y201706 |     1911 |    300000
 p1_y201707 |     1975 |    310000
 p1_y201708 |     1975 |    310000
 p1_y201709 |     1911 |    300000
 p1_y201710 |     1975 |    310000
 p1_y201711 |     1911 |    300000
 p1_y201712 |     1975 |    310000
 p2         |        0 |         0
 p2_y201701 |     1975 |    310000
 p2_y201702 |     1784 |    280000
 p2_y201703 |     1975 |    310000
 p2_y201704 |     1911 |    300000
 p2_y201705 |     1975 |    310000
 p2_y201706 |     1911 |    300000
 p2_y201707 |     1975 |    310000
 p2_y201708 |     1975 |    310000
 p2_y201709 |     1911 |    300000
 p2_y201710 |     1975 |    310000
 p2_y201711 |     1911 |    300000
 p2_y201712 |     1975 |    310000

 

PQ Disable

 

1차 테스트 시에는 PQ 수행을 방지하기 위해서 max_parallel_workers_per_gather 파라미터를 0으로 변경합니다. (기본설정값: 2)

 

 

Non-파티션 테이블간에 전체 레코드 조인

 

Non-파티션 테이블 간의 조인 수행 결과, WORK_MEM을 대략 171MB 사용합니다.

 

set work_mem='200MB';
 
explain (analyze, buffers) 
select count(*)
from   np1 a, np2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=229123.24..229123.25 rows=1 width=8) (actual time=5541.242..5541.242 rows=1 loops=1)
   Buffers: shared hit=46498
   ->  Hash Join  (cost=114499.00..229098.19 rows=10019 width=0) (actual time=1976.580..5091.775 rows=3650000 loops=1)
         Hash Cond: ((a.logdate = b.logdate) AND (a.c1 = b.c1))
         Buffers: shared hit=46498
         ->  Seq Scan on np1 a  (cost=0.00..59749.00 rows=3650000 width=8) (actual time=0.009..508.609 rows=3650000 loops=1)
               Buffers: shared hit=23249
         ->  Hash  (cost=59749.00..59749.00 rows=3650000 width=8) (actual time=1965.800..1965.800 rows=3650000 loops=1)
               Buckets: 4194304  Batches: 1  Memory Usage: 175347kB
               Buffers: shared hit=23249
               ->  Seq Scan on np2 b  (cost=0.00..59749.00 rows=3650000 width=8) (actual time=0.024..657.624 rows=3650000 loops=1)
                     Buffers: shared hit=23249
 Planning time: 0.090 ms
 Execution time: 5551.308 ms

 

 

파티션 테이블간에 전체 레코드 조인

 

그럼 파티션 테이블 간에 조인을 수행해볼까요? 만일 PWJ로 수행된다면 수행 속도도 더 빠르고 WORK_MEM도 더 적게 쓸 것이 분명합니다.
PJW는 파티션 단위로 조인을 수행하므로, 더 적은 WORK_MEM을 가지고 더 빠르게 수행된다는 장점이 있으니까요.

그런데 아래의 수행 결과를 보면 수행 속도도 더 느리고 WORK_MEM도 기존과 동일한 171MB를 사용합니다.

 

set work_mem='200MB';

explain (analyze, buffers) 
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=229131.00..229131.01 rows=1 width=8) (actual time=7544.717..7544.717 rows=1 loops=1)
   Buffers: shared hit=46506
   ->  Hash Join  (cost=114503.00..229106.00 rows=10000 width=0) (actual time=2846.786..7058.230 rows=3650000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=46506
         ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.008..1348.818 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2_y201701 b  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..54.608 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.044..52.027 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.015..54.587 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.011..53.759 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.018..60.088 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.012..53.712 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.028..54.309 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.011..66.090 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.019..53.649 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.011..57.762 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.014..51.902 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.012..54.026 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=59753.00..59753.00 rows=3650000 width=8) (actual time=2834.113..2834.113 rows=3650000 loops=1)
               Buckets: 4194304  Batches: 1  Memory Usage: 175347kB
               Buffers: shared hit=23253
               ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.038..1359.414 rows=3650000 loops=1)
                     Buffers: shared hit=23253
                     ->  Seq Scan on p1_y201701 a  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.021..54.720 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201702 a_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.014..52.013 rows=280000 loops=1)
                           Buffers: shared hit=1784
                     ->  Seq Scan on p1_y201703 a_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..58.126 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201704 a_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.011..54.138 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201705 a_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.013..57.886 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201706 a_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..54.615 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201707 a_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..56.619 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201708 a_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..54.834 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201709 a_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.009..54.792 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201710 a_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.009..57.051 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201711 a_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.009..64.463 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201712 a_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.009..54.757 rows=310000 loops=1)
                           Buffers: shared hit=1975
 Planning time: 0.434 ms
 Execution time: 7550.042 ms

 

PQ로 수행 - 파티션 테이블간에 전체 레코드 조인

 

설마 PWJ가 PQ인 경우에만 동작하는 걸까요?

 

max_parallel_workers_per_gather 파라미터를 24로 설정한 후에 다시 테스트를 수행했습니다.

 

PQ를 사용해도 PWJ는 동작하지 않습니다.

 

속도도 아주 조금 빨라졌을 뿐이고 WORK_MEM도 기존과 동일하게 171MB를 시용합니다.

 

제가 기대했던 것은 파티션마다 1개의 Parallel Worker가 병렬로 (즉, 12개가 동시에) 해시 조인을 수행하는 것이었으나, 아래의 Explain 결과를 보면 Parallel Worker는 1개만 론칭됩니다.

 

Parallel Worker가 1개만 론칭되는 이유는 파티션 1개의 크기가 15MB이기 때문입니다.

 

즉, Parallel Worker의 개수를 정하는 기준은 쿼리에 사용되는 파티션 개수가 아닌, 개별 파티션의 크기임을 알 수 있습니다. (이점은 개선이 필요할것 같습니다)

 

참고로 Parallel 처리 수행 여부를 결정하는 기준은 테이블 크기이고, 9.6 버전까지는 min_parallel_relation_size 파라미터 (기본설정값: 8MB)였으나, 10 버전부터는 min_parallel_table_scan_size 파라미터 (기본설정값: 1024 * 8Kb)를 사용합니다. 그리고 파라미터값의 3배수마다 Parallel Worker를 1개씩 더 할당하고, 최대 할당 개수는 7개입니다.

 

set work_mem='200MB';

explain (analyze, buffers) 
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=192506.14..192506.15 rows=1 width=8) (actual time=5991.005..5991.006 rows=1 loops=1)
   Buffers: shared hit=70074
   ->  Gather  (cost=192506.03..192506.14 rows=1 width=8) (actual time=5965.123..5990.999 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared hit=70074
         ->  Partial Aggregate  (cost=191506.03..191506.04 rows=1 width=8) (actual time=5962.763..5962.763 rows=1 loops=2)
               Buffers: shared hit=69844
               ->  Hash Join  (cost=114503.00..191491.32 rows=5882 width=0) (actual time=3328.256..5687.283 rows=1825000 loops=2)
                     Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
                     Buffers: shared hit=69844
                     ->  Append  (cost=0.00..44723.59 rows=2147061 width=8) (actual time=0.014..785.853 rows=1825000 loops=2)
                           Buffers: shared hit=23253
                           ->  Parallel Seq Scan on p2_y201701 b  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.012..35.618 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201702 b_1  (cost=0.00..3431.06 rows=164706 width=8) (actual time=0.012..30.147 rows=140000 loops=2)
                                 Buffers: shared hit=1784
                           ->  Parallel Seq Scan on p2_y201703 b_2  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..34.888 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201704 b_3  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.006..33.808 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201705 b_4  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..38.101 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201706 b_5  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.008..33.637 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201707 b_6  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..35.359 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201708 b_7  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.008..34.228 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201709 b_8  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.009..33.737 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201710 b_9  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.008..34.722 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201711 b_10  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.007..34.295 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201712 b_11  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..33.017 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                     ->  Hash  (cost=59753.00..59753.00 rows=3650000 width=8) (actual time=3311.668..3311.668 rows=3650000 loops=2)
                           Buckets: 4194304  Batches: 1  Memory Usage: 175347kB
                           Buffers: shared hit=46506
                           ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.037..1636.855 rows=3650000 loops=2)
                                 Buffers: shared hit=46506
                                 ->  Seq Scan on p1_y201701 a  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.036..71.783 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201702 a_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.032..63.401 rows=280000 loops=2)
                                       Buffers: shared hit=3568
                                 ->  Seq Scan on p1_y201703 a_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.019..71.326 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201704 a_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.020..67.097 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201705 a_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.029..71.276 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201706 a_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.021..66.124 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201707 a_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.038..66.720 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201708 a_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.022..75.394 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201709 a_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.021..66.812 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201710 a_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.022..70.167 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201711 a_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.022..94.346 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201712 a_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.029..78.436 rows=310000 loops=2)
                                       Buffers: shared hit=3950
 Planning time: 0.313 ms
 Execution time: 6027.321 ms

 

조인 Pruning 테스트

 

아래의 수행 결과를 보면, 조인 Pruning 또한 동작하지 않는 것을 알 수 있습니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31';
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=129466.00..129466.01 rows=1 width=8) (actual time=3613.260..3613.260 rows=1 loops=1)
   Buffers: shared hit=25228, temp read=10163 written=10157
   ->  Hash Join  (cost=12486.00..129441.00 rows=10000 width=0) (actual time=1066.850..3569.146 rows=310000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=25228, temp read=10163 written=10157
         ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.017..1411.893 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2_y201701 b  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.016..61.122 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.010..55.294 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.008..57.616 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.007..58.504 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..61.685 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.014..64.099 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..62.020 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..59.570 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.013..60.802 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.011..69.859 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.007..57.168 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..60.932 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=6625.00..6625.00 rows=310000 width=8) (actual time=224.806..224.806 rows=310000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 4055kB
               Buffers: shared hit=1975, temp written=793
               ->  Append  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.015..130.284 rows=310000 loops=1)
                     Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201705 a  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.014..72.681 rows=310000 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                           Buffers: shared hit=1975
 Planning time: 0.787 ms
 Execution time: 3613.353 ms

 

Partition WISE Join 테스트 - 파티션 1개

 

PWJ가 지원되면, 아래와 같은 쿼리의 성능도 매우 빨라집니다.

 

즉, P1 테이블은 반드시 전체 스캔을 해야하나, P2 테이블은 a.C1 조건에 해당되는 P1_Y201701 파티션과 매칭되는 P2_Y201701 파티션만 액세스하면 되기 때문인데요.

 

아래 결과를 보면, P2 테이블의 모든 파티션을 액세스합니다. 즉, PWJ가 지원되지 않는다는 것을 알 수 있습니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.c1 between 1 and 300000;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=200620.27..200620.28 rows=1 width=8) (actual time=3494.830..3494.830 rows=1 loops=1)
   Buffers: shared hit=46506, temp read=10139 written=10133
   ->  Hash Join  (cost=83678.26..200595.27 rows=10000 width=0) (actual time=466.617..3458.329 rows=300000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=46506, temp read=10139 written=10133
         ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.007..1321.298 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2_y201701 b  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..53.575 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.008..57.478 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..56.152 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.006..52.546 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..57.502 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.007..53.565 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..55.867 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..56.451 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.012..55.934 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..59.008 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..59.359 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.051..64.638 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=78003.00..78003.00 rows=300151 width=8) (actual time=466.224..466.224 rows=300000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 3947kB
               Buffers: shared hit=23253, temp written=769
               ->  Append  (cost=0.00..78003.00 rows=300151 width=8) (actual time=0.007..383.795 rows=300000 loops=1)
                     Buffers: shared hit=23253
                     ->  Seq Scan on p1_y201701 a  (cost=0.00..6625.00 rows=300140 width=8) (actual time=0.007..65.470 rows=300000 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 10000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201702 a_1  (cost=0.00..5984.00 rows=1 width=8) (actual time=22.058..22.058 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 280000
                           Buffers: shared hit=1784
                     ->  Seq Scan on p1_y201703 a_2  (cost=0.00..6625.00 rows=1 width=8) (actual time=21.949..21.949 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201704 a_3  (cost=0.00..6411.00 rows=1 width=8) (actual time=22.552..22.552 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201705 a_4  (cost=0.00..6625.00 rows=1 width=8) (actual time=24.022..24.022 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201706 a_5  (cost=0.00..6411.00 rows=1 width=8) (actual time=28.022..28.022 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201707 a_6  (cost=0.00..6625.00 rows=1 width=8) (actual time=23.800..23.800 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201708 a_7  (cost=0.00..6625.00 rows=1 width=8) (actual time=28.858..28.858 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201709 a_8  (cost=0.00..6411.00 rows=1 width=8) (actual time=26.004..26.004 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201710 a_9  (cost=0.00..6625.00 rows=1 width=8) (actual time=24.742..24.742 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201711 a_10  (cost=0.00..6411.00 rows=1 width=8) (actual time=21.484..21.484 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201712 a_11  (cost=0.00..6625.00 rows=1 width=8) (actual time=22.455..22.455 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
 Planning time: 0.477 ms
 Execution time: 3494.921 ms

 

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


이번 시간에 간단히 살펴볼 내용은 조인 Pruning이 수행되지 않는 문제점을 해결하는 방법입니다.

 

내용은 매우 간단합니다.

 

모든 테이블에 상수 조건을 입력하면 됩니다. (간단하죠?)

 

테스트를 통해 살펴보겠습니다.

 

 

파티션용 테이블 환경 구성

 

P1, P2, 2개의 월별 파티션 테이블 및 필요한 오브젝트를 생성합니다.

 

-- 파티션 테이블 p1, p2 생성 

drop table p1 cascade;
drop table p2 cascade;

create table p1 (
    c1        integer,
    logdate   date,
    dummy     char(10)
);

create table p2 (
    c1        integer,
    logdate   date,
	amount    integer,
    dummy     char(10)
);

create table p1_y201701 (CHECK( logdate >= DATE '2017-01-01' AND logdate < DATE '2017-02-01')) INHERITS (p1);
create table p1_y201702 (CHECK( logdate >= DATE '2017-02-01' AND logdate < DATE '2017-03-01')) INHERITS (p1);
create table p1_y201703 (CHECK( logdate >= DATE '2017-03-01' AND logdate < DATE '2017-04-01')) INHERITS (p1);
create table p1_y201704 (CHECK( logdate >= DATE '2017-04-01' AND logdate < DATE '2017-05-01')) INHERITS (p1);
create table p1_y201705 (CHECK( logdate >= DATE '2017-05-01' AND logdate < DATE '2017-06-01')) INHERITS (p1);
create table p1_y201706 (CHECK( logdate >= DATE '2017-06-01' AND logdate < DATE '2017-07-01')) INHERITS (p1);
create table p1_y201707 (CHECK( logdate >= DATE '2017-07-01' AND logdate < DATE '2017-08-01')) INHERITS (p1);
create table p1_y201708 (CHECK( logdate >= DATE '2017-08-01' AND logdate < DATE '2017-09-01')) INHERITS (p1);
create table p1_y201709 (CHECK( logdate >= DATE '2017-09-01' AND logdate < DATE '2017-10-01')) INHERITS (p1);
create table p1_y201710 (CHECK( logdate >= DATE '2017-10-01' AND logdate < DATE '2017-11-01')) INHERITS (p1);
create table p1_y201711 (CHECK( logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01')) INHERITS (p1);
create table p1_y201712 (CHECK( logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01')) INHERITS (p1);

create table p2_y201701 (CHECK( logdate >= DATE '2017-01-01' AND logdate < DATE '2017-02-01')) INHERITS (p2);
create table p2_y201702 (CHECK( logdate >= DATE '2017-02-01' AND logdate < DATE '2017-03-01')) INHERITS (p2);
create table p2_y201703 (CHECK( logdate >= DATE '2017-03-01' AND logdate < DATE '2017-04-01')) INHERITS (p2);
create table p2_y201704 (CHECK( logdate >= DATE '2017-04-01' AND logdate < DATE '2017-05-01')) INHERITS (p2);
create table p2_y201705 (CHECK( logdate >= DATE '2017-05-01' AND logdate < DATE '2017-06-01')) INHERITS (p2);
create table p2_y201706 (CHECK( logdate >= DATE '2017-06-01' AND logdate < DATE '2017-07-01')) INHERITS (p2);
create table p2_y201707 (CHECK( logdate >= DATE '2017-07-01' AND logdate < DATE '2017-08-01')) INHERITS (p2);
create table p2_y201708 (CHECK( logdate >= DATE '2017-08-01' AND logdate < DATE '2017-09-01')) INHERITS (p2);
create table p2_y201709 (CHECK( logdate >= DATE '2017-09-01' AND logdate < DATE '2017-10-01')) INHERITS (p2);
create table p2_y201710 (CHECK( logdate >= DATE '2017-10-01' AND logdate < DATE '2017-11-01')) INHERITS (p2);
create table p2_y201711 (CHECK( logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01')) INHERITS (p2);
create table p2_y201712 (CHECK( logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01')) INHERITS (p2);

-- p1 파티션용 트리거 함수 생성 

CREATE OR REPLACE FUNCTION p1_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

    IF    ( NEW.logdate >= DATE '2017-01-01' AND NEW.logdate < DATE '2017-02-01') THEN INSERT INTO p1_y201701 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-02-01' AND NEW.logdate < DATE '2017-03-01') THEN INSERT INTO p1_y201702 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-03-01' AND NEW.logdate < DATE '2017-04-01') THEN INSERT INTO p1_y201703 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-04-01' AND NEW.logdate < DATE '2017-05-01') THEN INSERT INTO p1_y201704 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-05-01' AND NEW.logdate < DATE '2017-06-01') THEN INSERT INTO p1_y201705 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-06-01' AND NEW.logdate < DATE '2017-07-01') THEN INSERT INTO p1_y201706 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-07-01' AND NEW.logdate < DATE '2017-08-01') THEN INSERT INTO p1_y201707 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-08-01' AND NEW.logdate < DATE '2017-09-01') THEN INSERT INTO p1_y201708 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-09-01' AND NEW.logdate < DATE '2017-10-01') THEN INSERT INTO p1_y201709 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-10-01' AND NEW.logdate < DATE '2017-11-01') THEN INSERT INTO p1_y201710 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-11-01' AND NEW.logdate < DATE '2017-12-01') THEN INSERT INTO p1_y201711 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-12-01' AND NEW.logdate < DATE '2018-01-01') THEN INSERT INTO p1_y201712 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the p1_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- p2 파티션용 트리거 함수 생성 

CREATE OR REPLACE FUNCTION p2_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

    IF    ( NEW.logdate >= DATE '2017-01-01' AND NEW.logdate < DATE '2017-02-01') THEN INSERT INTO p2_y201701 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-02-01' AND NEW.logdate < DATE '2017-03-01') THEN INSERT INTO p2_y201702 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-03-01' AND NEW.logdate < DATE '2017-04-01') THEN INSERT INTO p2_y201703 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-04-01' AND NEW.logdate < DATE '2017-05-01') THEN INSERT INTO p2_y201704 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-05-01' AND NEW.logdate < DATE '2017-06-01') THEN INSERT INTO p2_y201705 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-06-01' AND NEW.logdate < DATE '2017-07-01') THEN INSERT INTO p2_y201706 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-07-01' AND NEW.logdate < DATE '2017-08-01') THEN INSERT INTO p2_y201707 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-08-01' AND NEW.logdate < DATE '2017-09-01') THEN INSERT INTO p2_y201708 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-09-01' AND NEW.logdate < DATE '2017-10-01') THEN INSERT INTO p2_y201709 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-10-01' AND NEW.logdate < DATE '2017-11-01') THEN INSERT INTO p2_y201710 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-11-01' AND NEW.logdate < DATE '2017-12-01') THEN INSERT INTO p2_y201711 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-12-01' AND NEW.logdate < DATE '2018-01-01') THEN INSERT INTO p2_y201712 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the p2_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
 
-- p1 파티션용 INSERT 트리거 생성

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

CREATE TRIGGER insert_p2_trigger
    BEFORE INSERT ON p2
    FOR EACH ROW EXECUTE PROCEDURE p2_insert_trigger();
	
-- p1 테이블에 일자별로 만건 입력 

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p1 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, 'dummy');
	 end loop;
  end loop;
end $$;
	
-- p2 테이블에 일자별로 만건 입력

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p2 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, j, 'dummy');
	 end loop;
  end loop;
end $$;

 

 

통계 정보 생성 및 크기 확인

 

월별 파티션 테이블에 제대로 데이터가 입력됐는지 확인합니다.

 

analyze p1;
analyze p2;

analyze p1_y201701; 
analyze p1_y201702;
analyze p1_y201703;
analyze p1_y201704;
analyze p1_y201705;
analyze p1_y201706;
analyze p1_y201707;
analyze p1_y201708;
analyze p1_y201709;
analyze p1_y201710;
analyze p1_y201711;
analyze p1_y201712;

analyze p2_y201701; 
analyze p2_y201702;
analyze p2_y201703;
analyze p2_y201704;
analyze p2_y201705;
analyze p2_y201706;
analyze p2_y201707;
analyze p2_y201708;
analyze p2_y201709;
analyze p2_y201710;
analyze p2_y201711;
analyze p2_y201712;

select relname, relpages, reltuples::integer 
from   pg_class
where  relname like 'p1%' or
       relname like 'p2%' 
order by 1;
  relname   | relpages | reltuples
------------+----------+-----------
 p1         |        0 |         0
 p1_y201701 |     1975 |    310000
 p1_y201702 |     1784 |    280000
 p1_y201703 |     1975 |    310000
 p1_y201704 |     1911 |    300000
 p1_y201705 |     1975 |    310000
 p1_y201706 |     1911 |    300000
 p1_y201707 |     1975 |    310000
 p1_y201708 |     1975 |    310000
 p1_y201709 |     1911 |    300000
 p1_y201710 |     1975 |    310000
 p1_y201711 |     1911 |    300000
 p1_y201712 |     1975 |    310000
 p2         |        0 |         0
 p2_y201701 |     1975 |    310000
 p2_y201702 |     1784 |    280000
 p2_y201703 |     1975 |    310000
 p2_y201704 |     1911 |    300000
 p2_y201705 |     1975 |    310000
 p2_y201706 |     1911 |    300000
 p2_y201707 |     1975 |    310000
 p2_y201708 |     1975 |    310000
 p2_y201709 |     1911 |    300000
 p2_y201710 |     1975 |    310000
 p2_y201711 |     1911 |    300000
 p2_y201712 |     1975 |    310000

 

 

입력 조건이 1개의 테이블에만 있는 경우

 

아래와 같이 P1 테이블에 대해서만 날자 조건을 입력하면, P1 테이블에 대한 파티션 Pruning은 수행되지만 P2 테이블에 대한 파티션 Pruing은 수행되지 않습니다.

 

즉, 조인 Pruning을 제공하지 않으므로 P2 테이블에 딸린 모든 파티션을 Seq Scan하게 됩니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31';
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=129351.64..129351.65 rows=1 width=8) (actual time=3230.240..3230.240 rows=1 loops=1)
   Buffers: shared hit=25228, temp read=10163 written=10157
   ->  Hash Join  (cost=12486.01..129349.51 rows=849 width=0) (actual time=1027.855..3191.489 rows=310000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=25228, temp read=10163 written=10157
         ->  Append  (cost=0.00..59753.00 rows=3650001 width=8) (actual time=0.008..1308.494 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2 b  (cost=0.00..0.00 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
               ->  Seq Scan on p2_y201701 b_1  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..55.073 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_2  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.016..50.464 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_3  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.030..54.888 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_4  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..58.291 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_5  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..57.236 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_6  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..54.321 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.015..59.018 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_8  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..56.012 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_9  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.015..53.940 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_10  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..55.760 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_11  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.009..53.782 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_12  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..57.858 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=6625.00..6625.00 rows=310001 width=8) (actual time=244.772..244.772 rows=310000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 4055kB
               Buffers: shared hit=1975, temp written=793
               ->  Append  (cost=0.00..6625.00 rows=310001 width=8) (actual time=0.008..149.727 rows=310000 loops=1)
                     Buffers: shared hit=1975
                     ->  Seq Scan on p1 a  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                     ->  Seq Scan on p1_y201705 a_1  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.006..93.462 rows=310000 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                           Buffers: shared hit=1975
 Planning time: 0.440 ms
 Execution time: 3230.319 ms

 

입력 조건을 추가한 경우


위와 같은 문제점때문에, 쿼리 작성 시에는 아래와 같이 모든 테이블에 상수 조건을 입력해야 합니다.

수행 결과를 보면, P1 테이블에서 5월 파티션, P2 테이블에서 5월 파티션만 액세스한 것을 알 수 있습니다.

결과적으로 수행속도도 매우 빠르게 개선되었습니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31'
and    b.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31';
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25079.64..25079.65 rows=1 width=8) (actual time=721.734..721.734 rows=1 loops=1)
   Buffers: shared hit=3950, temp read=1598 written=1592
   ->  Hash Join  (cost=12486.01..25077.51 rows=849 width=0) (actual time=219.268..677.870 rows=310000 loops=1)
         Hash Cond: ((a.logdate = b.logdate) AND (a.c1 = b.c1))
         Buffers: shared hit=3950, temp read=1598 written=1592
         ->  Append  (cost=0.00..6625.00 rows=310001 width=8) (actual time=0.011..129.327 rows=310000 loops=1)
               Buffers: shared hit=1975
               ->  Seq Scan on p1 a  (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
               ->  Seq Scan on p1_y201705 a_1  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.006..75.549 rows=310000 loops=1)
                     Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                     Buffers: shared hit=1975
         ->  Hash  (cost=6625.00..6625.00 rows=310001 width=8) (actual time=219.036..219.036 rows=310000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 4055kB
               Buffers: shared hit=1975, temp written=793
               ->  Append  (cost=0.00..6625.00 rows=310001 width=8) (actual time=0.007..131.560 rows=310000 loops=1)
                     Buffers: shared hit=1975
                     ->  Seq Scan on p2 b  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                     ->  Seq Scan on p2_y201705 b_1  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.005..78.213 rows=310000 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                           Buffers: shared hit=1975
 Planning time: 0.522 ms
 Execution time: 721.791 ms


 

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

6월 PostgreSQL 교육을 위해서 MySQL 연구를 잠시 접고, PostgreSQL 파티션을 테스트하던 중이었는데요. PostgreSQL 10 베타 버전이 나왔네요.

 

소스 파일을 이용해서 PostgreSQL 10 베타 버전을 설치하는 법을 간단히 정리했습니다.

 

다운로드

 

아래의 사이트에서 postgresql-10beta1.tar.gz 파일을 다운로드합니다.

 

https://www.postgresql.org/ftp/source/v10beta1/

 

 

설치 절차

 

-- 압축을 풉니다.

 

gunzip postgresql-10beta1.tar.gz
tar xvf postgresql-10beta1.tar
cd postgresql-10beta1

 

-- Configure 명령어를 수행해서 Makefile을 생성합니다.
-- 기본 설치 디렉토리는 /usr/local/pgsql 인데요.

-- 만일, 해당 위치에 다른 버전이 설치돼있으면 prefix 옵션을 이용해서 디렉토리를 지정합니다.

 

./configure --prefix=/usr/local/pgsql10
make
make install

mkdir /usr/local/pgsql10/data
chown -R postgres:postgres /usr/local/pgsql10/data

 

-- postgresql 유저로 스위치합니다.

 

su - postgres

 

-- initdb를 수행합니다.

 

/usr/local/pgsql10/bin/initdb -D /usr/local/pgsql10/data

 

-- $PGDATA/postgresql.conf  파일에서 port를 변경합니다. (9.6 버전이 설치된 경우)

 

port = 5410

 

-- 데이터베이스를 시작합니다.

 

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data start

 

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

PostgreSQL 1-Day 튜닝 교육을 시작합니다.

PostgreSQL 2017.04.27 11:43 Posted by 시연아카데미

어제 처음으로 외부에서 PostgreSQL 1-Day 튜닝 교육을 진행했습니다.
진행 후에 수강하신 분들의 반응을 들어보고, 저 나름대로 자체적인 평가를 해본 결과 정식으로 교육을 런칭해도 괜찮을 것같다는 생각이 들었습니다.

 

교육과 관련된 내용은 아래 링크를 참고해주세요.

 

https://siyeonacademy.wordpress.com/postgresql-1-day-tuning-workshop/

 

감사합니다.

 

김시연 올림.

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

개발자를 위한 PostgreSQL 튜닝 워크샵 교육 자료

PostgreSQL 2017.04.24 02:55 Posted by 시연아카데미

우연한 기회에 개발자 대상으로 1일짜리 PostgreSQL 성능 교육을 진행하게 됐습니다.

이를 위해 제 책중에서 액기스만 뽑아서 아주 깔끔한(?) 교육 자료를 만들었습니다.

첫 교육 진행 후에 반응이 괜찮으면 정식 교육으로 론칭 해보려고 합니다.

온사이트 교육도 환영입니다. (제 e-mail로 연락주세요. ^^) 


파트-1: 아키텍처 (개요, Shared Buffer, MVCC, Vacuum, 파티션)







파트-2: 옵티마이저 & 쿼리 튜닝 (Explain, Access 방법, 조인, Query Rewrite, PG_HINT_PLAN, BRIN, Parallel)




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

PostgreSQL 서적 집필을 시작했습니다.

PostgreSQL 2016.09.11 18:42 Posted by 시연아카데미
알고 있다고 생각하는 것과 실제로 아는 것,
그리고 아는 것을 정리하는 것과 정리한 것을 쉽게 설명하는 것 사이에는 아주 커다란 간극이 존재합니다.

이러한 지식의 헛점을 메꾸고 지식을 집대성하기에 가장 좋은 방법은 책을 쓰는 것입니다.

PostgreSQL과 관련된 몇 가지 자료를 포스팅하면서 "PostgreSQL과 관련된 책을 써봐야겠다"는 생각이 점차로 강해졌습니다.

하지만 책을 쓰는 것은 상당 기간의 시간을 필요로합니다. 그리고 혼자서 오라클이 아닌 새로운 DBMS에 대한 서적을 집필하는 것은 쉽지 않은 일이기도 합니다. 하지만 다행히도 저와 뜻을 같이하는 동지를 만나게 되서 같이 책을 쓰기로 의기투합했습니다.

이를 위해 당분간은 PostgreSQL 서적 집필활동에만 집중할 예정입니다. 정확한 지식을 쉽게 풀어 씀과 동시에 인터널한 부분에만 치우치지 않고 실제 업무에 적용 가능한 실용성을 겸비한 책을 써보려고 합니다.

책 출간 후에는 포스팅 뿐 아니라 오프라인 강의로도 찾아뵙겠습니다.

블로그를 찾아주시는 모든 분들께 감사드립니다.


2016년 9월

김 시연 올림





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

Vacuum을 연구함에 있어서 FrozenXID는 풀리지 않던 마지막 숙제였습니다.


Anti-Wraparound Vacuum 수행 시에 XMIN 값을 FrozenXID로 변경할까?


만일 그렇다면 대량의 리두가 발생할 텐데, 과연 그렇게 비효율적인 처리를 수행할 것인가? 하는 의문인 것이죠.  그렇다고, 현재의 PostgreSQL의 구조상으로는 딱히 다른 방법이 있을 것 같지도 않았습니다. 9.6 버전에서 50억 가까운 트랜잭션을 발생시킨 후에도 XMIN 값이 변경되지 않는 것을 확인했습니다. 9.4, 9.5에서도 동일한 테스트를 해본 결과 역시 XMIN 값이 변경되지 않는 것을 확인했습니다. 여기까지 테스트한 후에 약간의 멘붕이 왔습니다. 대부분의 문서, 심지어 매뉴얼까지도 Anti-Wraparound Vacuum 수행 시에는 XMIN 값을 FrozenXID(2)로 replace 한다고 되어있기 때문입니다.


테스트를 접을까 하다, 마음을 가다듬고 9.1부터 다시 테스트를 시작해보기로 했습니다. 수행 결과, 9.1, 9.2, 9.3까지는 Anti-Wraparound Vacuum 수행 후에 실제로 XMIN 값을 FrozenXID인 2로 변경하는 것을 확인했습니다. 그리고 뒤 늦은 구글링 검색 결과, 9.4부터는 XMIN 값을 FrozenXID로 변경하지 않고, Flag로 처리한다는 사실을 발견했습니다 (좀 더 일찍 발견했다면 일주일 이상 삽질을 하지 않았을 텐데요. 하지만 좋은 경험이었습니다)


Note
PostgreSQL은 XID를 Permanent XID와 Normal XID로 구분합니다. 또한 Permanent XID는 Bootstrap XID (initdb시의 할당되는 XID임. 값은 1)와 FrozenXID (2)로 구분됩니다. 즉, 일반 트랜잭션의 시작 XID는 3부터입니다. 따라서 XMIN 값을 FrozenXID(2)로 변경하면, 해당 레코드는 항상 "Visible"한 상태가 됩니다.


하지만 Anti-Wraparound Vacuum 시에 XMIN 값을 실제로 변경하던, Flag를 변경하던 간에 레코드에 변경이 발생하게 되고 이로 인해 WAL 로그가 발생합니다. 즉, Vacuum 수행 시에 WAL 로그가 발생하는 것은 이와 같이 Anti-Wraparound Vacuum에 의해서 테이블 내의 변경이 발생하는 경우라고 보면 됩니다.
이상으로 베큠에 대한 정리를 마무리하도록 하겠습니다.

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

이전 포스팅: [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

PostgreSQL을 사용함에 있어서 Vacuum의 동작 방식을 정확히 이해하는 것은 너무나도 중요한 일입니다. 만일, 업무 시간에 autovacuum 수행으로 인한 IO saturation 현상이 발생한다면 성능 상의 문제가 발생하기 때문입니다. 따라서, Vacuum, 특히 자동으로 수행되는 autovacuum의 원리 및 위험성을 정확히 파악하고 있어야 함은 PostgreSQL 운영에 있어서 가장 중요한 부분이라고 할 수 있습니다.




1. PostgreSQL의 age에 대한 이해


Vacuum을 쉽게 이해하기 위해서는 나이(age) 개념을 이해해야 합니다. 현재 XID가 1,969라고 가정하겠습니다. 이때 테이블 A를 생성하면 해당 테이블의 relfrozenxid=1,970이되고 age=1, 즉 1살이 됩니다. 그리고 테이블 A에 4개의 트랜잭션이 발생하면, 5살로 증가합니다. 그런데 여기서 주의해야할 사항이 있습니다. 이 시점에 테이블 B에 5개의 트랜잭션이 발생한다면 테이블 A의 나이는 어떻게 될까요? 테이블 A의 나이는 5살이 증가한 10살이 됩니다. 즉, 나 (테이블 A)는 아무것도 하지 않았지만 세월이 흘러감에 따라 (트랜잭션이 발생함에 따라) 자연스럽게 나이가 증가하는 개념입니다. 다소 철학적이죠? 즉, PostgreSQL의 나이는 사람의 나이와 동일한 개념이라고 이해하시면 됩니다. (그림-1 참조)


그림-1. 트랜잭션 발생에 따른 테이블 A의 나이 증가




2. Autovacuum의 동작 원리


Autovacuum은 autovacuum_freeze_max_agevacuum_freeze_min_age 파라미터에 의해서 제어됩니다.

  • autovacuum_freeze_max_age : autovacuum 대상이 되는 테이블의 나이(age)를 의미합니다. 기본 설정 값이 2억이므로 2억살 이상 되는 테이블은 autovacuum 대상이 됩니다. 
  • vacuum_freeze_min_age : 매뉴얼의 설명은 이해하기가 다소 어렵습니다. 매뉴얼의 설명보다는 autovacuum 수행 직후에 설정되는 테이블의 나이(age)라고 이해하면 됩니다. 기본 설정 값이 5천만이므로, autovacuum 직후에 해당 테이블의 나이는 5천만살이 됩니다. (그림-2 참조) 만일, vacuum_freeze_min_age=0으로 설정하면 autovacuum 수행 직후에 해당 테이블의 나이는 0살이 됩니다. 
그림-2. autovacuum 수행 후의 나이 변화 (vacuum_freeze_min_age=5천만인 경우)




즉, autovacuum 직후에 설정되는 테이블의 나이는 아래의 수식을 이용합니다. 


 테이블 RelfrozenXID = CURRENT XID - vacuum_freeze_min_age
 테이블의 나이 (age) = CURRENT XID - 테이블 RelfrozenXID   




3. Autovacuum의 위험성


눈치가 빠른 분들은 이미 2장의 설명을 통해 Autovacuum의 위험성을 간파했을 것입니다. 즉, 테이블에 어떠한 변경사항도 없고, Autovacuum (또는 Vacuum)이 수행됐다고 하더라도 일정 기간이 경과되면 해당 테이블은 또 다시 Autovacuum 대상이 됩니다.


autovacuum_freeze_max_age과 vacuum_freeze_min_age 파라미터가 기본 설정 값이라면, autovacuum이 발생한 이후에 매 1.5억 트랜잭션이 발생할 때 마다 autovacuum을 재 수행하게 됩니다. 특히, 9.6 이전 버전까지는 visibility map 내에 all_frozen 비트가 없으므로 테이블을 거의 full scan 해야하는 문제가 존재합니다. 이는 테이블의 크기가 클 수록, 보관주기가 길수록 더 심각한 문제일 수 밖에 없습니다.




4. 테스트 시나리오 및 결과 요약


테스트를 위한 파라미터 설정과 테스트 시나리오는 다음과 같습니다.


4-1. 파라미터 설정 내역

  • autovacuum_freeze_max_age : 20만으로 설정 
  • vacuum_freeze_min_age : 4만으로 설정 
  • vacuum_freeze_table_age : 14만으로 설정 (이번 테스트에는 영향을 미치지 않음)

4-2. 테스트 시나리오

  • 파티션 테이블 생성 
  • 1월 파티션에 20만건 입력 
    • 20만건을 입력했으므로 모든 파티션 테이블 나이가 20만으로 증가함
    • 따라서 모든 파티션 테이블에 대해서 Autovacuum이 수행됨 
  • 2월 파티션에 16만건 입력 
    • 16만건을 입력했으므로 모든 파티션 테이블 나이가 20만으로 증가함 
    • 따라서 모든 파티션(1월 포함) 테이블에 대해서 Autovacuum이 수행됨
  • 3월 파티션에 16만건 입력 
    • 16만건을 입력했으므로 모든 파티션 테이블 나이가 20만으로 증가함 
    • 따라서 모든 파티션(1월, 2월 포함) 테이블에 대해서 Autovacuum이 수행됨

4-3. 결과 요약 및 해결 방안
테스트 결과를 보면 이전에 Autovacuum을 수행했더라도 재 수행되며, 기존과 거의 유사한 IO 리소스를 사용하는 것을 알 수 있습니다. (그림-3~그림 5 참조) 아쉽게도 9.5까지는 이 문제를 해결할 수 있는 방법은 없습니다. 다만, 시스템의 TPS와 보관주기에 따라 적절히 큰 값으로 autovacuum 관련된 파라미터 값을 설정하는 정도입니다. 그리고 IO 부하가 집중되는 현상을 완화시키기 위해 autovacuum_vacuum_cost_delay (단위: 밀리세컨) 파라미터를 큰 값을 설정함으로써 autovacuum 프로세스가 IO를 사용하는 빈도를 낮출 수는 있습니다. 하지만 반대급부로 autovacuum 처리 시간이 길어지게 됩니다. 근본적인 해결을 위해서는 9.6부터 (2016년 9월 현재 베타 버전) 제공되는 Visibility map의 향상된 기능을 이용해야 합니다. 해당 내용은 별도 포스팅 예정입니다.


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



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


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




5. 테스트 세부 내역


테스트 세부 내역은 다음과 같습니다.


5-1. 파라미터 설정

-- 파라미터 수정 (/opt/PostgreSQL/9.4/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)


5-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);


5-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;

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


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

-- 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  
    -- SIZE 증가를 위해 500만건 Bulk Insert 
	insert into p1 select generate_series(1,5000000), v_logdate, 'dummy';	

    -- TXID 증가를 위해 LOOP 커밋 	
    FOR i in 1..v_end LOOP
        PERFORM insert_p1(i, v_logdate, 'dummy');
    END LOOP;
END;
$$ LANGUAGE plpgsql;


5-5. 익스텐션 설치

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


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

-- 현재 TXID_CURRENT 확인  
$ get_time.sh
 txid_current
--------------
         1908

-- 현재 테이블 나이 확인 		 
$ get_info.sh
  relname   | age | relfrozenxid | reltuples |  size   | last_autoanalze | last_autovacuum
------------+-----+--------------+-----------+---------+-----------------+-----------------
 p1_y201601 |  18 |        1,891 |         0 | 0 bytes |                 |
 p1_y201602 |  17 |        1,892 |         0 | 0 bytes |                 |
 p1_y201603 |  16 |        1,893 |         0 | 0 bytes |                 |
 p1_y201604 |  15 |        1,894 |         0 | 0 bytes |                 |
 p1_y201605 |  14 |        1,895 |         0 | 0 bytes |                 |
 p1_y201606 |  13 |        1,896 |         0 | 0 bytes |                 |
 p1_y201607 |  12 |        1,897 |         0 | 0 bytes |                 |
 p1_y201608 |  11 |        1,898 |         0 | 0 bytes |                 |
 p1_y201609 |  10 |        1,899 |         0 | 0 bytes |                 |
 p1_y201610 |   9 |        1,900 |         0 | 0 bytes |                 |
 p1_y201611 |   8 |        1,901 |         0 | 0 bytes |                 |
 p1_y201612 |   7 |        1,902 |         0 | 0 bytes |                 |
 
-- 20만건 입력 
select dblink_connect('myconn','dbname=test port=5434 user=postgres password=oracle');
select loop_insert_p1('2016-01-02 00:00:00', 200000);

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

-- Autovacuum 수행 종료 후 테이블 나이 및 TXID_CURRENT() 확인 	  
$ get_info.sh
  relname   |  age   | relfrozenxid |  reltuples  |  size   |   last_autoanalze   |   last_autovacuum
------------+--------+--------------+-------------+---------+---------------------+---------------------
 p1_y201601 | 40,002 |      161,914 | 5.19996e+06 | 380 MB  | 2016-09-06 09:59:03 | 2016-09-06 09:58:41
 p1_y201602 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201603 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201604 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201605 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201606 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201607 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201608 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201609 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201610 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201611 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30
 p1_y201612 | 40,002 |      161,914 |           0 | 0 bytes |                     | 2016-09-06 09:56:30

$ get_time.sh
 txid_current
--------------
      201,916
	  
-- Autovacuum 수행 시간 확인 
2016-09-06 09:55:00 KST LOG:  automatic analyze of table "test.public.p1_y201601" system usage: CPU 0.06s/0.28u sec elapsed 21.81 sec
2016-09-06 09:58:41 KST LOG:  automatic vacuum of table "test.public.p1_y201601": index scans: 0
        pages: 0 removed, 48599 remain
        tuples: 0 removed, 5200000 remain, 0 are dead but not yet removable
        buffer usage: 63807 hits, 33426 misses, 48606 dirtied
        avg read rate: 1.719 MB/s, avg write rate: 2.499 MB/s
        system usage: CPU 0.92s/1.80u sec elapsed 151.93 sec
2016-09-06 09:59:03 KST LOG:  automatic analyze of table "test.public.p1_y201601" system usage: CPU 0.00s/0.25u sec elapsed 22.62 sec


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

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

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

-- Autovacuum 수행 종료 후 테이블 나이 및 TXID_CURRENT() 확인 
$ get_info.sh
  relname   |  age   | relfrozenxid |  reltuples  |  size   |   last_autoanalze   |   last_autovacuum
------------+--------+--------------+-------------+---------+---------------------+---------------------
 p1_y201601 | 40,001 |      321,921 |     5.2e+06 | 380 MB  | 2016-09-06 09:59:03 | 2016-09-06 10:19:27
 p1_y201602 | 40,001 |      321,921 | 5.16005e+06 | 377 MB  | 2016-09-06 10:21:38 | 2016-09-06 10:21:12
 p1_y201603 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201604 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201605 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201606 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201607 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201608 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201609 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201610 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201611 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14
 p1_y201612 | 40,001 |      321,921 |           0 | 0 bytes |                     | 2016-09-06 10:18:14

$ get_time.sh
 txid_current
--------------
      361,922

-- Autovacuum 수행 시간 확인 
2016-09-06 10:17:06 KST LOG:  automatic analyze of table "test.public.p1_y201602" system usage: CPU 0.06s/0.30u sec elapsed 21.79 sec
2016-09-06 10:19:27 KST LOG:  automatic vacuum of table "test.public.p1_y201601": index scans: 0
        pages: 0 removed, 48599 remain
        tuples: 0 removed, 5200000 remain, 0 are dead but not yet removable
        buffer usage: 48618 hits, 48615 misses, 376 dirtied
        avg read rate: 3.706 MB/s, avg write rate: 0.029 MB/s
        system usage: CPU 0.17s/0.43u sec elapsed 102.48 sec
2016-09-06 10:21:12 KST LOG:  automatic vacuum of table "test.public.p1_y201602": index scans: 0
        pages: 0 removed, 48225 remain
        tuples: 0 removed, 5160000 remain, 0 are dead but not yet removable
        buffer usage: 63844 hits, 32640 misses, 48232 dirtied
        avg read rate: 1.331 MB/s, avg write rate: 1.966 MB/s
        system usage: CPU 1.37s/1.42u sec elapsed 191.65 sec

2016-09-06 10:21:38 KST LOG:  automatic analyze of table "test.public.p1_y201602" system usage: CPU 0.00s/0.27u sec elapsed 25.84 sec


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

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

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

-- Autovacuum 수행 종료 후 테이블 나이 및 TXID_CURRENT() 확인 
$ get_info.sh
  relname   |  age   | relfrozenxid |  reltuples  |  size   |   last_autoanalze   |   last_autovacuum
------------+--------+--------------+-------------+---------+---------------------+---------------------
 p1_y201601 | 40,001 |      481,926 |     5.2e+06 | 380 MB  | 2016-09-06 09:59:03 | 2016-09-06 10:39:11
 p1_y201602 | 40,001 |      481,926 |    5.16e+06 | 377 MB  | 2016-09-06 10:21:38 | 2016-09-06 10:39:47
 p1_y201603 | 40,001 |      481,926 | 5.15997e+06 | 377 MB  | 2016-09-06 10:41:46 | 2016-09-06 10:41:23
 p1_y201604 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201605 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201606 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201607 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201608 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201609 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201610 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201611 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12
 p1_y201612 | 40,001 |      481,926 |           0 | 0 bytes |                     | 2016-09-06 10:39:12

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

-- Autovacuum 수행 시간 확인 
2016-09-06 10:39:11 KST LOG:  automatic vacuum of table "test.public.p1_y201601": index scans: 0
        pages: 0 removed, 48599 remain
        tuples: 0 removed, 5200000 remain, 0 are dead but not yet removable
        buffer usage: 48618 hits, 48615 misses, 0 dirtied
        avg read rate: 2.834 MB/s, avg write rate: 0.000 MB/s
        system usage: CPU 0.37s/0.25u sec elapsed 133.99 sec
2016-09-06 10:39:47 KST LOG:  automatic vacuum of table "test.public.p1_y201602": index scans: 0
        pages: 0 removed, 48225 remain
        tuples: 0 removed, 5160000 remain, 0 are dead but not yet removable
        buffer usage: 48244 hits, 48240 misses, 376 dirtied
        avg read rate: 2.459 MB/s, avg write rate: 0.019 MB/s
        system usage: CPU 0.40s/0.28u sec elapsed 153.25 sec
2016-09-06 10:41:23 KST LOG:  automatic vacuum of table "test.public.p1_y201603": index scans: 0
        pages: 0 removed, 48225 remain
        tuples: 0 removed, 5160000 remain, 0 are dead but not yet removable
        buffer usage: 63734 hits, 32751 misses, 48232 dirtied
        avg read rate: 1.085 MB/s, avg write rate: 1.597 MB/s
        system usage: CPU 1.00s/1.75u sec elapsed 235.91 sec

2016-09-06 10:41:46 KST LOG:  automatic analyze of table "test.public.p1_y201603" system usage: CPU 0.00s/0.28u sec elapsed 22.23 sec


다음 포스팅: [PostgreSQL 9.6] Visibility Map 내의 FROZEN 비트를 이용한 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

PostgreSQL은 인덱스 생성 시에 WHERE 절을 이용한 Partial Index 기능을 제공합니다. 오라클과 비교해봤을 때 아주 흥미로운 기능이라고 할 수 있습니다. 오라클에서 Skew 칼럼 데이터를 액세스할 때 사용할 수 있는 방법은 크게 2가지입니다.

  1. Skew 칼럼에 인덱스 생성 
  2. 12c의 Partition Partial Index 기능 사용 (Skew 칼럼으로 LIST 파티션 생성 후, 결과 건수가 적은 파티션은 Indexing=on, 결과 건수가 많은 칼럼은 Indexing=off 적용)

1번 방법은 불필요한 데이터까지도 인덱스에 저장한다는 비효율이 있습니다. 12c에서 소개된 Partition Partial Index는 좋은 방법이긴 하지만, 만일 다른 칼럼으로 파티션을 해야할 경우에는 적용하기 어렵다는 문제점이 있습니다. 이에 비해 PostgreSQL은 Partial Index를 이용해서 인덱스로 스캔할 대상 데이터만을 선별적으로 인덱스에 저장한다는 장점을 제공합니다. 테스트 내역은 다음과 같습니다.


1. Partial Index 생성


create table p1 (c1 integer, send_flag char(1));

-- send_flag 칼럼에 'Y'는 999,990 건, 'N'는 10건을 입력합니다. 
insert into p1 select generate_series(1,999990), 'Y';
insert into p1 select generate_series(999991,1000000), 'N';

-- WHERE 절을 이용해서 Partial Index를 생성합니다. 
create index p1_idx01 on p1 (send_flag) where send_flag='N';


2. 인덱스 크기 및 건수 확인


p_idx01 인덱스의 크기 및 건수를 확인해보면 10건만 저장되어 있다는 점과 그로 인해 인덱스 크기가 매우 작다는 것을 알 수 있습니다.

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM  pg_class
WHERE relname  in ('p1','p1_idx01')
ORDER BY relpages DESC;

 objectname | objecttype | #entries | size
------------+------------+----------+-------
 p1         | r          |    1e+06 | 35 MB
 p1_idx01   | i          |       10 | 16 kB


3. Explain 결과 확인


Explain으로 수행 결과를 확인해보면, send_flag='N' 조건인 경우에는 Index Scan을 수행하고 send_flag='Y'인 경우에는 Seq Scan을 수행하는 것을 알 수 있습니다.

test=# explain analyze verbose select count(*) from p1 where send_flag='N';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.15..4.16 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using p1_idx01 on public.p1  (cost=0.14..4.15 rows=1 width=0) (actual time=0.011..0.015 rows=10 loops=1)
         Output: send_flag
         Heap Fetches: 10
 Planning time: 0.079 ms
 Execution time: 0.041 ms

test=# explain analyze verbose select count(*) from p1 where send_flag='Y';
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11675.22..11675.23 rows=1 width=8) (actual time=1029.285..1029.285 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=11675.00..11675.21 rows=2 width=8) (actual time=985.944..1029.279 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=10675.00..10675.01 rows=1 width=8) (actual time=923.592..923.592 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=922.109..922.109 rows=1 loops=1
               Worker 1: actual time=865.724..865.724 rows=1 loops=1
               ->  Parallel Seq Scan on public.p1  (cost=0.00..9633.33 rows=416667 width=0) (actual time=0.020..628.419 rows=333330 loops=3)
                     Filter: (p1.send_flag = 'Y'::bpchar)
                     Rows Removed by Filter: 3
                     Worker 0: actual time=0.022..750.056 rows=267810 loops=1
                     Worker 1: actual time=0.025..256.533 rows=280240 loops=1
 Planning time: 0.125 ms
 Execution time: 1040.657 ms


Note
send_flag='Y' 조건인 경우에는 Parallel Seq Scan이 수행되는 것이 목격됩니다. 해당 기능은 PostgreSQL 9.6부터 제공되는 Parallel Seq Scan 기능이 작동된 것을 의미합니다. 해당 내용은 별도 포스팅 예정입니다.

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

간혹, PostgreSQL의 내부 동작원리를 파악하기 위해서 XID를 아주 큰 값 (10억 또는 20억)으로 증가시켜야 할 경우가 있습니다. 이러한 경우, 이전 포스팅에서 소개한 것과 같이 DBLINK를 이용한 LOOP COMMIT 방식을 사용할 수도 있지만, DML을 통해 10억건 이상을 생성(또는 생성 후 삭제)하는 것은 리소스 소모적이며 그에 따라 오랜 시간이 소요됩니다. 따라서 아주 빠르게 XID를 증가시킬 수 있는 방법이 필요합니다. 가장 효과적인 방법은 TXID_CURRENT() 함수를 이용하는 것입니다. SELECT 수행 시에는 XID가 증가하지 않지만, SELECT TXID_CURRENT();를 수행하면 XID가 1 증가합니다. 이러한 속성을 이용해서 다음과 같은 프로시저를 작성합니다. (제 환경에서 XID를 10억 증가시키는데 소요되는 시간은 대략 20시간 정도입니다. DB LINK & LOOP COMMIT 방식은 10일 이상 소요될 것으로 예상된 것에 비하면 10배 이상 빠른 방법입니다)



1. DBLINK를 이용해서 TXID_CURRENT()를 호출하는 get_xid() 프로시저 생성

CREATE OR REPLACE FUNCTION get_xid() RETURNS INTEGER AS $$
DECLARE
rval integer;
BEGIN
       SELECT txid into rval
       FROM dblink('myconn','select cast(cast(txid_current() AS text) AS integer) txid') as t1(txid integer);
     
       RETURN rval;
END;
$$ LANGUAGE plpgsql;


2. get_xid() 프로시저를 반복적으로 호출하는 loop_get_xid() 프로시저 생성

CREATE or replace FUNCTION loop_get_xid(v_begin integer, v_end integer) RETURNS VOID AS $$
DECLARE
rval integer;
BEGIN
     
    FOR i in v_begin..v_end LOOP
        SELECT get_xid() INTO rval;
    END LOOP;
END;
$$ LANGUAGE plpgsql;


3. loop_get_xid() 프로시저를 호출하는 Shell 생성

-- call_loop_get_xid.sh의 내용 
-- call_loop_get_xid.sh을 10개 세션에서 동시에 수행합니다.
-- 제 경우, 대략 초당 3만개의 XID가 증가했습니다.

PGPASSWORD=oracle psql test -p 5433 -Upostgres <<EOF1
select dblink_connect('myconn','dbname=test port=5433 user=postgres password=oracle');
select loop_get_xid(1,50000000);
EOF1

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

PostgreSQL은 pageinspect 익스텐션을 이용한 블록 덤프 기능을 제공합니다. 오라클과 같이 다양하고 세세한 기능을 제공하지는 않지만, 간단한 테스트 시에는 좋은 도구로 활용할 수 있습니다. 사용 방법 및 예제는 다음과 같습니다.


1. PAGEINSPECT 익스텐션 설치 

create extension pageinspect;


2. 테스트 시나리오 수행

-- 테이블 생성 
test=# create table t3 (c1 integer);

-- 3건 입력 
test=# insert into t3 values(1);
test=# insert into t3 values(2);
test=# insert into t3 values(3);

-- 각각의 XID가 74,75,76 인것을 확인 
test=# select xmin, xmax, * from t3;
   xmin    | xmax | c1
-----------+------+----
        74 |    0 |  1
        75 |    0 |  2
        76 |    0 |  3

-- 2번 레코드 삭제  
test=#  delete from t3 where c1=2;

test=# select xmin, xmax, * from t3;
   xmin    | xmax | c1
-----------+------+----
        74 |    0 |  1
        76 |    0 |  3

-- 3번 레코드 변경 	
test=# update t3 set c1=4 where c1=3;

test=# select xmin, xmax, * from t3;
   xmin    | xmax | c1
-----------+------+----
        74 |    0 |  1
        78 |    0 |  4

이때, 실제 블록 내부에는 어떠한 변경 사항이 발생했는지 확인해보겠습니다.


3. GET_RAW_PAGE 함수를 이용한 블록 덤프

-- get_raw_page의 (1번쨰 아규먼트: 오브젝트 명) (2번쨰 아규먼트: 블록 번호)
-- delete와 update로 인해 삭제된 레코드 2건은 아직 블록 내에 존재하는 것을 알 수 있습니다.
-- delete의 XID는 77
-- update의 XID는 78 

test=# 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('t3',0));		

 t_ctid |  case  |  t_xmin   |  t_xmax
--------+--------+-----------+-----------
 (0,1)  | Normal | 74        |         0   
 (0,2)  | Normal | 75        |        77   --- delete 명령어로 delete된 레코드  
 (0,4)  | Normal | 76        |        78   --- update 명령어로 delete된 레코드
 (0,4)  | Normal | 78        |         0


4. VACUUM 수행 후의 변경 사항 확인

test=# vacuum t3;

-- VACUUM 수행 후, 삭제된 2건은 재활용 가능한 상태로 변경된 것을 알 수 있습니다. 

test=# 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('t3',0));
	
 t_ctid |     case      |  t_xmin   | t_xmax
--------+---------------+-----------+--------
 (0,1)  | Normal        |        74 |      0
        | Unused        |           |         
        | Redirect to 4 |           |         
 (0,4)  | Normal        |        78 |      0


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

PostgreSQL은 현재까지(버전 9.6) plpgsql내에서 autonomous 트랜잭션을 지원하지 않습니다. 하지만 dblink 익스텐션을 이용하면 autonomous 트랜잭션을 구현하는 것이 가능합니다. 제 경우에는 Vacuum 동작원리를 파악하기 위해 XID를 빠르게 증가시킬 필요가 있었습니다. 하지만 PostgreSQL은 LOOP 내에서의 COMMIT을 허용하지 않으므로 dblink 익스텐션을 이용해서 해당 기능을 구현했습니다. 사용 방법은 다음과 같습니다.


1. DBLINK 익스텐션 설치

test=# create extension dblink;


2. DBLINK를 이용해서 매건 커밋하는 프로시저 생성

-- 성능을 위해서 dblink_connect 및 dblink_disconnect는 프로시저 외부에서 수행하도록 변경 

CREATE OR REPLACE FUNCTION insert_t1(v1 integer) RETURNS VOID AS $$
BEGIN
           -- PERFORM dblink_connect('myconn','dbname=test port=5433 user=postgres password=oracle');
           PERFORM dblink('myconn','INSERT INTO t1 SELECT ' || '''' || v1 || '''');
           PERFORM dblink('myconn','COMMIT;');
           -- PERFORM dblink_disconnect('myconn'); 
END;
$$ LANGUAGE plpgsql;


3. 매건 커밋하는 프로시저를 호출하는 프로시저 생성

CREATE or replace FUNCTION loop_insert_t1(v_loop integer) RETURNS VOID AS $$
BEGIN
    
    FOR i in 1..v_loop LOOP
        PERFORM insert_t1(i);
    END LOOP;
END;
$$ LANGUAGE plpgsql;


4. DBLINK 연결 -> LOOP 수행 -> DBLINK 연결 해제


다음과 같은 방법을 이용하면 아주 빠르고 편리하게 XID를 증가시킬 수 있습니다. 테스트 시에 도움이 되길 바랍니다.

-- DBLINK 연결
test=# select dblink_connect('myconn','dbname=test port=5433 user=postgres password=oracle');

--프로시저 수행
test=# select loop_insert_t1(100000000);

--DBLINK 연결해제 
test=# select dblink_disconnect('myconn'); 


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

PostgreSQL은 file_fdw 익스텐션을 이용해서 external 테이블을 생성할 수 있습니다.


1. file_fdw 익스텐션을 이용한 external 테이블 생성 방법


-- file_fdw 익스텐션을 생성합니다.
test=#  create extension file_fdw;

-- 서버를 생성합니다.
test=# create server srv_file_fdw foreign data wrapper file_fdw;

-- foreign 테이블을 생성합니다.
test=#  create foreign table op_perf_report 
(
  overhead      numeric,
  command       varchar(100),
  shared_obj    varchar(100),
  symbol        varchar(100)  
)
server srv_file_fdw 
options ( 
    format 'csv',
	header 'true',
    delimiter ',',
    filename '/var/tmp/perf_report.txt');


2. external 테이블을 이용한 fulltime.sh 포팅


이전 포스팅에서 fulltime.sh (perf 명령어를 이용해서 오라클 Kernel function을 분석하는 쉘) 스크립트를 소개한바 있습니다. 해당 방법의 골자는 perf 명령어 수행결과 파일을 external 테이블로 지정한 후, 해당 테이블을 조회하는 것입니다. 따라서 fulltime.sh를 PostgreSQL용으로 포팅하기 위해서는 external 테이블을 이용해야 합니다. 단, PostgreSQL은 세션 레벨의 CPU Time 정보 (오라클의 v$sess_time_model)을 제공하지 않으므로 OS 레벨의 CPU 사용 정보만을 제공한다는 제약사항이 있습니다. 아래의 소스 중에서 눈 여겨 볼 것은 2가지 정도입니다.


1) foreign 테이블 생성을 위한 약간의 조작
foreign 테이블용으로 생성되는 파일 (/var/tmp/perf_report.txt)에는 주석(#) 및 공백 라인이 있을 경우에는 에러가 발생합니다. 따라서 다음과 같이 grep과 sed 명령어를 이용해서 해당 라인들을 제거합니다.

perf report -t, > z ; cat z | grep -v '#' | sed '/^\s*$/d' > /var/tmp/perf_report.txt 2>/dev/null

2) 패스워드 입력 없이 psql에 로그인 하는 방법

PGPASSWORD=oracle psql test -p 5433 -Upostgres <<EOF1


PostgreSQL용으로 포팅된 fulltime.sh 소스는 다음과 같습니다.


Source: PostgreSQL용 fulltime.sh

#!/bin/sh
#
# Name    : fulltime.sh
version=3e
# Purpose : To see PostgreSQL kernel function CPU details TOGETHER.
# Orig    : 17-Oct-2013
# Latest  : 31-Oct-2013
# Modify  : 31-Aug-2016 by Siyeon.Kim 
# Author  : Craig Shallahamer 
#           Frits Hoogland 
# Warrenty: There is absolutely no warrenty.
#           Use at your own and your organization's risk.
#           It's all on you, not me!

# Usage   : ./fulltime.sh
#           ./fulltime.sh PID INTERVAL CYCLES
#
# This has been developed on Linux.
# You break out with a cntrl-c

# Here is the general idea:
#
# Help user Identify the PID to profile
# Initial setup
# Loop
#   start PostgreSQL kernel cpu details
#   sleep x
#   stop PostgreSQL kernel cpu gathering
#   do some cool math and other neat stuff
#   display results
# End Loop

# Set the key variables
#
# use this for virtualised hosts:
#PERF_SAMPLE_METHOD='-e cpu-clock'
# use this for physical hosts:
PERF_SAMPLE_METHOD='-e cycles'

refresh_time=3
samples_remaining=999 # this is useful for longer single samples

workdir=$PWD
perf_file=perf_report.txt

clear

# if problems with perf
echo "If unable to execute perf, do as root:"
echo "   echo 0 > /proc/sys/kernel/perf_event_paranoid"

# perf sample method
echo ""
echo "The perf sample method is set to: $PERF_SAMPLE_METHOD"
echo "Use cpu-clock for virtualised hosts, cycles for physical hosts"

###
# ctrl_c routine
###
ctrl_c() {
        PGPASSWORD=oracle psql test -p 5433 -Upostgres <& /dev/null
        drop foreign table op_perf_report;
        drop table op_timing;
EOF0

        echo ""
        echo ""
        echo "Thanks for using FULLTIME v${version}!"
        echo ""
        echo "To see the latest Call Graph, press ENTER or to exit press CNTRL-C."
        echo ""
        echo "The Call Graph file is callgraph.txt"
        read x
        perf report -g -i callgraph.pdata > callgraph.txt 2>/dev/null
        echo ""
        more callgraph.txt
        exit
}

###
# help_find_pid routine
#
# Let's help the user identifiy the Oracle session of interest
#
###
help_find_pid() {
PGPASSWORD=oracle psql test -p 5433 -Upostgres </dev/null; then ctrl_c; fi

PGPASSWORD=oracle psql test -p 5433 -Upostgres <& /dev/null

drop foreign table op_perf_report;

create foreign table op_perf_report (
  overhead      numeric,
  command       varchar(100),
  shared_obj    varchar(100),
  symbol        varchar(100)
)
server srv_file_fdw
options (
    format 'csv',
    header 'true',
    delimiter ',',
    filename '/var/tmp/perf_report.txt'
);

EOF2

while [ $samples_remaining  -gt 0 ]
do
        echo "Gathering next $refresh_time second sample..."

        if ! ps -p $ospid >/dev/null; then ctrl_c; fi

        perf record -f $PERF_SAMPLE_METHOD -p $ospid sleep $refresh_time >& /dev/null &
        perf record -f $PERF_SAMPLE_METHOD -g -o callgraph.pdata -p $ospid sleep $refresh_time >& /dev/null   &
        wait

        if ! ps -p $ospid >/dev/null; then ctrl_c; fi

        perf report -t, > z ; cat z | grep -v '#' | sed '/^\s*$/d' > /var/tmp/perf_report.txt 2>/dev/null

        if ! ps -p $ospid >/dev/null; then ctrl_c; fi

        clear

        echo "Fulltime.sh v$version"

        PGPASSWORD=oracle psql test -p 5433 -Upostgres < 2.0;

EOF5
        echo ""
        echo "To see the Call Graph, press ENTER or to exit press CNTRL-C."
        samples_remaining=`echo "$samples_remaining-1" | bc`

        echo "Samples remaining: $samples_remaining"

done

# If NO command line options, then prompt for call graph, else just exit.
#
if [ "$#" -eq 0 ]; then
        ctrl_c
fi


3. fulltime.sh 사용 예제


-- 아규먼트 없이 fulltime.sh를 수행하면 현재 수행하는 모든 프로세스 목록이 출력됩니다. 
[root@pgserver ~]# ./fulltime.sh

  pid  |  query           
-------+------------------------------------------
 32572 | select loop_insert_t1(10000000);
 32598 | INSERT INTO t1 SELECT '3199470'
   427 | INSERT INTO t1 SELECT '3216064'
 32751 | select loop_insert_t1(100000000);
   521 | select loop_insert_t1(100000000);
   522 | INSERT INTO t1 SELECT '3067927'
   669 | select loop_insert_t1(100000000);
   670 | INSERT INTO t1 SELECT '3064539'
   773 | select loop_insert_t1(100000000);
   774 | INSERT INTO t1 SELECT '3072957'
   868 | select loop_insert_t1(100000000);
   870 | INSERT INTO t1 SELECT '3092238'
  1138 | select loop_insert_t1(100000000);
  1139 | INSERT INTO t1 SELECT '3013273'
  1248 | select loop_insert_t1(100000000);
  1249 | INSERT INTO t1 SELECT '3015102'
  1341 | select loop_insert_t1(100000000);
  1342 | INSERT INTO t1 SELECT '3002411'
  1433 | select loop_insert_t1(100000000);
  1435 | INSERT INTO t1 SELECT '2992595'
  
-- PID를 입력하면, 해당 프로세스의 기본 정보 및 Kernel function 사용 내역을 확인할 수 있습니다. 
Enter PID to profile : 32598

Fulltime.sh v3e
                          BASIC_INFO                            |                    QUERY
----------------------------------------------------------------+---------------------------------------------
 PID: 32598 USERNAME: postgres QUERY TIME(Sec): 00:00:00.000373 | CURRENT SQL: INSERT INTO t1 SELECT '101134'

                   item                    | overhead
-------------------------------------------+----------
 cpu : [k] _raw_spin_unlock_irqrestore     |    20.07
 cpu : [k] __do_softirq                    |     9.67
 cpu : [.] SearchCatCache                  |     2.60
(3 rows)

-- CTRL+C 이후에 엔터를 입력하면 Kernel Function Call Tree를 확인할 수 있습니다.
To see the Call Graph, press ENTER or to exit press CNTRL-C.
Samples remaining: 990
Gathering next 3 second sample...

# ========
# captured on: Tue Aug 30 12:43:14 2016
# hostname : pgserver
# os release : 3.8.13-35.3.1.el7uek.x86_64
# perf version : 3.8.13-35.3.1.el7uek.x86_64
# arch : x86_64
# nrcpus online : 2
# nrcpus avail : 2
# cpudesc : Intel(R) Core(TM) i7-6500U CPU @ 2.50GHz
# cpuid : GenuineIntel,6,78,3
# total memory : 3785700 kB
# cmdline : /usr/libexec/perf.3.8.13-35.3.1.el7uek.x86_64 record -f -e cycles -g -o callgraph.pdata -p 32572 sleep 3
# event : name = cpu-clock, type = 1, config = 0x0, config1 = 0x0, config2 = 0x0, excl_usr = 0, excl_kern = 0, excl_h
ost = 0, excl_guest = 1, precise_ip = 0, id = { 456 }
# HEADER_CPU_TOPOLOGY info available, use -I to display
# HEADER_NUMA_TOPOLOGY info available, use -I to display
# pmu mappings: software = 1, tracepoint = 2, breakpoint = 5
# ========
#
# Samples: 80  of event 'cpu-clock'
# Event count (approx.): 80
#
# Overhead   Command      Shared Object                           Symbol
# ........  ........  .................  ...............................
#
    21.25%  postgres  [kernel.kallsyms]  [k] finish_task_switch
            |
            --- finish_task_switch
                __schedule
                schedule
                schedule_hrtimeout_range_clock
                schedule_hrtimeout_range
                poll_schedule_timeout
                do_sys_poll
                sys_poll
                system_call_fastpath
                __poll_nocancel

    18.75%  postgres  [kernel.kallsyms]  [k] _raw_spin_unlock_irqrestore
            |
            --- _raw_spin_unlock_irqrestore
               |
               |--86.67%-- __wake_up_sync_key
               |          |
               |          |--61.54%-- pipe_write
               |          |          do_aio_write
               |          |          do_sync_write
               |          |          vfs_write
               |          |          sys_write
               |          |          system_call_fastpath
               |          |          __GI___libc_write
               |          |          0x2065726568742020
               |          |
               |           --38.46%-- sock_def_readable
               |                     unix_stream_sendmsg
               |                     sock_sendmsg


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

PostgreSQL도 버전 9.6부터 pg_stat_activity 테이블의 wait_event_typewait_event 칼럼을 통해 대기이벤트를 모니터링할 수 있습니다. (세부 내용은 매뉴얼 참조) 현재는 4개의 wait_event_type과 100개 이내의 wait_event를 제공할 뿐이지만, 이전버전까지는 waiting 칼럼을 통해 락 대기 여부만을 판단할 수 있던 것에 비하면 획기적인 변화의 시작임은 분명합니다. 부하를 주고 모니터링을 해본 결과, 오라클과 같이 아주 세세한 부분까지 디버깅을 할 수는 없지만 심각한 문제에 대해서는 어느 정도 문제를 유추할 수 있는 수준의 데이터는 제공하는 것 같습니다. 아래는 동시에 10여개의 세션에서 동일 테이블에 INSERT를 수행하는 부하 테스트 시의 모니터링 결과입니다. 보시는 것과 같이 WALWriteLock 대기이벤트를 대기하는 세션이 다수이고, 이를 통해 해당 세션들은 WAL 버퍼가 디스크로 기록되기를 대기한다는 것을 알 수 있습니다.


Note
이전 버전까지 제공되던 waiting 칼럼은 9.6 버전에서는 제공되지 않습니다. 따라서 waiting='t' 조건을 이용해서 락 대기 세션을 모니터링했던 쿼리들은 wait_event_type='Lock'으로 조건을 변경해야 합니다. 


예시. wait_event_type 및 wait_event 칼럼을 이용한 대기이벤트 모니터링

test=# select pid, wait_event_type, wait_event, current_timestamp-xact_start as txn_time, query from pg_stat_activity;

  pid  | wait_event_type |  wait_event  |     txn_time     |     query
-------+-----------------+--------------+------------------+---------------------------------
 32572 |                 |              | 03:29:30.451393  | select loop_insert_t1(10000000);
 32598 | LWLockNamed     | WALWriteLock | 00:00:00.000653  | INSERT INTO t1 SELECT '3810212'
   427 | LWLockNamed     | WALWriteLock | 00:00:00.001069  | INSERT INTO t1 SELECT '3831263'
 32751 |                 |              | 03:29:17.380626  | select loop_insert_t1(100000000);
   521 |                 |              | 03:25:06.032286  | select loop_insert_t1(100000000);
   522 |                 |              |                  | INSERT INTO t1 SELECT '3683652'
   669 |                 |              | 03:25:04.224235  | select loop_insert_t1(100000000);
   670 | LWLockNamed     | WALWriteLock | 00:00:00.001333  | INSERT INTO t1 SELECT '3679654'
   773 |                 |              | 03:25:00.344482  | select loop_insert_t1(100000000);
   774 |                 |              | 00:00:00.001249  | INSERT INTO t1 SELECT '3689628'
   868 |                 |              | 03:25:45.836126  | select loop_insert_t1(100000000);
   870 | LWLockNamed     | WALWriteLock | 00:00:00.000672  | INSERT INTO t1 SELECT '3708035'
  1138 |                 |              | 03:22:13.689574  | select loop_insert_t1(100000000);
  1139 | LWLockNamed     | WALWriteLock | 00:00:00.000892  | INSERT INTO t1 SELECT '3628461'
  1248 |                 |              | 03:21:42.46344   | select loop_insert_t1(100000000);
  1249 | LWLockNamed     | WALWriteLock | -00:00:00.000289 | INSERT INTO t1 SELECT '3632619'
  1341 |                 |              | 03:21:12.328308  | select loop_insert_t1(100000000);
  1342 | LWLockNamed     | WALWriteLock | 00:00:00.002372  | INSERT INTO t1 SELECT '3619281'
  1433 |                 |              | 03:20:49.765249  | select loop_insert_t1(100000000);
  1435 | LWLockNamed     | WALWriteLock | 00:00:00.000924  | INSERT INTO t1 SELECT '3608915'


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


 

티스토리 툴바