My Books

My Slides

rss 아이콘 이미지

Search

'전체'에 해당되는 글 83건

  1. 2017.07.31 2017년 8월 PostgreSQL 1-Day 튜닝 교육 자료입니다.
  2. 2017.07.11 Modulo 연산을 이용한 PostgreSQL XID 비교 로직 설명
  3. 2017.06.23 MySQL 서적 집필을 시작했습니다.
  4. 2017.06.10 PostgreSQL 1-Day 교육 자료 업데이트되었습니다. (2)
  5. 2017.05.24 PostgreSQL 10 - Parallel Index Scan 소개 (1)
  6. 2017.05.24 PostgreSQL 10 - Partition Wise Join (PWJ) 및 조인 Pruning 테스트 (1)
  7. 2017.05.23 PostgreSQL 9.6 - 조인 Pruning이 수행되지 않는 문제점 해결 방법
  8. 2017.05.23 PostgreSQL 10 베타 버전을 소스 파일로 설치하는 방법
  9. 2017.05.18 MySQL - Covering Index 사용 시에 ICP가 동작하지 않는 문제점과 해결 방안
  10. 2017.05.17 MySQL - Index Condition Pushdown (ICP) 동작 원리의 정확한 이해
  11. 2017.05.16 MySQL - Multi Range Read (MRR)와 관련된 매뉴얼 Bug 및 소스 디버깅을 통한 검증
  12. 2017.05.16 MySQL - 메모리 IO 시에도 Multi Range Read (MRR) 방식이 NO MRR 방식에 비해서 빠를까?
  13. 2017.05.15 MySQL - Multi Buffer Pool의 문제점과 innodb_buffer_pool_instances 파라미터를 이용한 개선 방안
  14. 2017.05.10 MySQL - Derived Table을 이용해서 NL 조인 시에 발생하는 Block Nested-Loop (BNL)를 튜닝하는 방법
  15. 2017.05.10 MySQL – Block Nested-Loop (BNL)에 대한 이해
  16. 2017.05.10 MySQL – Loose Index Scan 동작 원리의 이해
  17. 2017.05.10 MySQL - IN 절과 EXISTS 절 간의 처리 방식 비교
  18. 2017.05.03 MySQL에서 Complex View 처리 시의 문제점 및 코딩 시 주의 사항
  19. 2017.05.02 MySQL에서 unnamed derived table 사용 시의 주의 사항
  20. 2017.04.28 한 서버에 MySQL 5.6 버전과 5.7 버전을 설치하는 방법

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

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

MySQL 2017.06.23 09:45 Posted by 시연아카데미

시연아카데미의 두 번째 서적인 "개발자를 위한 MySQL 쿼리 튜닝 이야기" 집필을 시작했습니다.

 

이번 책은 쿼리 튜닝을 위해서 개발자들이 꼭 알아야 할 내용들을 쉽게 써보려고 합니다.

 

MySQL 연구는 이미 몇 달 정도 진행된 상태이므로 빠르면 9월 말 이전에 서적 출간이 가능할 것 같습니다.

 

그때까지는 집필과 PostgreSQL 강의에만 집중하려고 합니다.

 

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


 

 

2017년 6월 23일


김 시연 올림

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
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

이번 시간에는 Covering Index 사용 시에 ICP가 동작하지 않는 문제점을 알아보고, 대안을 찾아보도록 하겠습니다.


Covering Index는 쿼리의 조건절과 SELECT 절에 해당하는 모든 칼럼을 포함하는 인덱스를 의미합니다. 따라서 Covering 인덱스를 이용하면 테이블을 액세스할 필요가 없습니다.


그리고 ICP는 이전 시간에 설명할 것과 같이 인덱스 필터링 기능입니다.


즉, ICP는 인덱스 필터링 후에 추출된 인덱스 키에 대해서는 테이블을 액세스합니다.


따라서 ORACLE과 PostgreSQL과 같은 DBMS는 Covering Index를 사용하는 것이 인덱스 필터링보다 빠릅니다. (테이블을 액세스하지 않으므로, 당연한 결과입니다)


그런데 MySQL은 특정한 상황에서는 Covering Index를 사용하는 것보다 ICP 방식이 더 빠릅니다.


이 같은 문제의 원인은 Covering Index를 사용하면 핸들러 레벨에서 필터링을 처리하지만, ICP 방식을 사용하면 스토리지 엔진 레벨에서 필터링하기 때문입니다.


더 근본적인 이유는 Covering Index에 대한 ICP 기능이 개발되지 않아서입니다.


이 문제는 이미 5.6 시절에 페르코나 엔지니어에 의해서 리포팅되고 (여기 참조), 버그로도 등록되었는데요.


5.7 버전에서도 아직 이 문제가 해결되지 않고 있습니다.


그럼 어떤 내용인지 테스트를 통해서 살펴보고, 이 문제를 해결할 방안을 찾아보도록 하겠습니다.



테스트 환경 구성


테스트 환경은 이전 시간에 사용한 환경을 이용합니다. (여기 참조)



ICP로 동작하는 경우


아래의 쿼리는 천만 건 중에서 인덱스 범위로 500만 건을 액세스한 후에 인덱스 필터링을 통해서 1건만 테이블을 액세스합니다. (극단적인 예제입니다)


이때 Extra 칼럼을 보면 Using index condition으로 표시되므로, 이 쿼리는 ICP 방식을 사용한 것을 알 수 있습니다. 이때, 수행속도는 0.6초입니다.

mysql> explain select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

mysql> select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+------+------+-------+
| id | c1   | c2   | dummy |
+----+------+------+-------+
|  1 |    1 |    1 | dummy |
+----+------+------+-------+
1 row in set (0.60 sec)


Covering Index를 사용하는 경우


SELECT 절을 c1으로 변경한 후에는 ICP 방식으로 동작하지 않습니다. T2_IDX01 인덱스는 C1+C2 칼럼으로 구성된 결합인덱스이고, 쿼리는 C1, C2 칼럼만 존재하므로 Covering Index라고 할 수 있습니다.


그런데 이때 Extra 칼럼을 보면, Using Where; Using index라고 표시됩니다.


Using Where; Using index가 의미하는 것은 Index를 이용해서 쿼리 조건절에 해당하는 범위를 액세스한 후에, 필터링은 핸들러 레벨에서 수행한다는 것입니다.


이렇게 처리하다 보니, Disk 에서 InnoDB 버퍼로 로딩하는 블록 수는 동일하지만, ICP에 비해서 InnoDB 버퍼를 액세스하는 횟수가 상당히 많습니다. (테이블-1 참조)


결과적으로 수행 속도도 70% 가까이 느려집니다.

mysql> explain select c1 from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
 
mysql> select c1 from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (1.03 sec)


테이블-1. ICP 방식과 Covering 인덱스 방식 간의 일량 비교


COUNT(*) 쿼리는 ICP로 동작하지 않는다.


문제는 또 있는데요. Covering Index를 이용한 COUNT(*) 쿼리도 ICP로 동작하지 않습니다.

mysql> explain select count(*) from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+

mysql> select count(*) from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (1.08 sec)


개선 방안


그렇다면 어떡헤 해야 할까요?  MySQL 아키텍처의 특성 상, ICP가 훨씬 유리해 보이는데 말이죠.


제가 생각한 방법은 Derived Table을 이용하는 것입니다.


5.6 버전까지는 Unnamed Derived Table은 Merge가 되지 않습니다. 이 속성을 이용하면, ICP 방식으로 유도할 수 있습니다.

아래의 Explain 결과를 보면, 쿼리를 수정한 후에는 ICP 방식으로 동작하는 것을 알 수 있습니다.


이로 인해 처리 속도도 빨라졌습니다.


-- Version 5.6

mysql> explain select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----+-------------+------------+-------+---------------+----------+---------+------+---------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows    | Extra                            |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+----------------------------------+
|  1 | PRIMARY     |  | ALL   | NULL          | NULL     | NULL    | NULL | 4830516 | NULL                             |
|  2 | DERIVED     | t2         | range | t2_idx01      | t2_idx01 | 10      | NULL | 4830516 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+----------------------------------+

mysql> select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.69 sec)


-- Version 5.7


그런데, 5.7 버전에서는 이 방법을 사용할 수가 없습니다. 왜냐면 5.7 버전부터는 Unnamed Derived Table은 외부 쿼리와 View Merging이 되기 때문입니다.


따라서 아래와 같이 쿼리를 수정해봐도 여전히 sing where; Using index 방식으로 동작합니다.


TEMPTABLE 알고리즘 속성을 가진 View (Named Derived Table)를 생성하면 되긴 합니다만, 이 방식은 사실 업무에 적용하기는 불가능해 보입니다. (단순 참고용입니다)

mysql> explain select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+

mysql> select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (1.05 sec)

create or replace ALGORITHM=TEMPTABLE view t2_v 
as select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100;

explain select count(*) from t2_v; 
+----+-------------+------------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
|  1 | PRIMARY     |  | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |  495660 |   100.00 | NULL                  |
|  2 | DERIVED     | t2         | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

mysql> select count(*) from t2_v;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.62 sec)


-- Version 8


8 버전부터는 Unnamed Derived Table에 대한 NO_MERGE 힌트를 제공합니다. 테스트를 해보진 못했지만, 아마도 이 힌트를 사용하면 ICP로 동작할 것 같습니다. (이 힌트는 5.7에도 제공해주면 좋을 것 같습니다.)

explain select /*+ NO_MERGE(a) */ count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100) a;


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

이번에 살펴볼 내용은 MySQL 5.6 버전부터 지원하는 Index Condition Pushdown (이하 ICP)의 동작 원리입니다.

 

MariaDB 매뉴얼을 보면, (여기 참조) ICP의 동작 원리를 그림으로 매우 쉽게 설명하고 있습니다. 즉, 이 설명대로라면 ICP는 다른 DBMS에서 아주 오래전부터 지원하고 있는 "인덱스 필터링 기능"입니다.

 

그렇다면 MySQL은 다른 DBMS에는 너무나 당연히 지원했던 인덱스 필터링 기능을 5.6 버전에 와서야 지원을 시작했다는 걸까요? 아니면 ICP는 인덱스 필터링과는 다른, 특별한 기능이 있는 걸까요?

 

MySQL 매뉴얼을 보면 조금 헷갈린 설명이 있습니다. (매뉴얼 전문은 여기 참조)

"With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. "

 

매뉴얼에서 표현한 "storage engine" 이란 용어 때문에 ICP는 인덱스 필터링보다는 더 특별한 무언가가 있지 않을까? 하는 생각을 해봤습니다.

 

하지만 스토리지 엔진이란것도 따지고 보면, MySQL 서버 내에 있는 "디스크 + 처리 로직"의 개념일 뿐입니다. 즉, IO 처리를 위해서는 반드시 InnoDB 버퍼 풀로 디스크 블록을 로딩해야만 하는 구조입니다.

 

다시 말해, 오라클의 EXADATA와 같이 DB 서버와 스토리지 서버가 분리된 구조는 아니란 거죠.

 

따라서 MySQL의 ICP가 EXADATA에서 제공하는 Offloading 기능 (스토리지 박스 내에서 IO를 처리하는 방식)을 의미하는 것도 아닐 것 같습니다.

 

그렇다면 ICP는 대체 뭘까요?

 

일련의 테스트를 통해서 내린 결론은 "ICP는 인덱스 필터링 기능이다" 입니다.

 

그리고 테스트를 하는 과정에 MySQL은 인덱스 필터 처리를 매우 비효율적인 방식으로 수행했었다는 것도 알 수 있었습니다.

 

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

 

테스트 환경 구성

 

테스트 환경은 MRR 테스트를 수행했던 환경을 이용합니다. (여기 참조)

 

테이블과 인덱스 정보는 다음과 같습니다.

+------------+------------+-------------+---------------+-------------+----------------+--------------+
| table_name | table_rows | data_length | DATA_SIZE(MB) | DATA_BLOCKS | INDEX_SIZE(MB) | INDEX_BLOCKS |
+------------+------------+-------------+---------------+-------------+----------------+--------------+
| t2         |    9913212 |  1427111936 |          1361 |       87104 |            204 |        13041 |
+------------+------------+-------------+---------------+-------------+----------------+--------------+

+---------------+------------+------------+----------------+--------------+
| database_name | table_name | index_name | INDEX_SIZE(MB) | INDEX_BLOCKS |
+---------------+------------+------------+----------------+--------------+
| mysql         | t2         | PRIMARY    |           1361 |        87104 |
| mysql         | t2         | t2_idx01   |            204 |        13041 |
+---------------+------------+------------+----------------+--------------+

 

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

 

다음과 같은 3가지 경우에 대해서 각각 Disk IO 발생 시와 Memory IO 수행 시의 성능 통계 정보와 수행 속도를 비교했습니다.

 

1. 인덱스가 없는 경우
2. 인덱스 생성 후에 ICP 기능 사용 안 함
3. 인덱스 생성 후에 ICP 기능 사용 함

 

수행 결과는 다음과 같습니다.

 

 

위의 결과를 보면, ICP 방식이 NO ICP 방식에 비해서 10배 가까이 빠릅니다.

 

ICP 방식이 빠른 이유는 Innodb_data_reads 횟수가 NO ICP에 비해서 현저히 적기 때문입니다.

 

Innodb_data_reads는 Disk Read 횟수를 의미하는 성능 지표입니다.

 

즉, ICP 방식을 사용하면 Disk Read (및 Logical Read) 횟수가 현저히 줄어듭니다. 수치를 조금 더 자세히 분석해보겠습니다.

 

ICP 방식의 Disk IO 블록 수는 1,149블록입니다. 이 수치는 T2_IDX01 인덱스 블록의 1/10 정도 되는 수치입니다.

 

즉, 쿼리 수행을 위해서 인덱스의 1/10되는 범위를 액세스하면서 필터링을 수행했고, 필터링에 만족하는 1건만 테이블 블록을 액세스했다고 볼 수 있습니다.

 

반면, ICP 방식의 Disk IO 블록 수는 9,892블록입니다. 대략적으로 계산해서 9,892블록 중에서 8,700블록 정도는 테이블 블록을 위한 IO라고 할 수 있습니다.

 

테이블 블록이 87,104블록이므로 테이블 전체 블록의 1/10을 액세스한 셈입니다.

 

즉, 쿼리 수행을 위해서 인덱스의 1/10되는 범위를 액세스하지만, 인덱스 필터링을 수행하지는 못함에 따라서 테이블 블록을 액세스한 후에 필터링을 수행했다는 것을 알 수 있습니다.

 

다시 말해, ICP 방식을 사용하지 않으면, 인덱스에 필터 칼럼 (C2 칼럼)이 있어도 인덱스 필터링이 불가능합니다.

 

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

 

INDEX가 없는 경우

 

테스트를 위해서 인덱스를 Drop 합니다.

alter table t2 drop index t2_idx01;

 

Explain 결과를 보면 type은 ALL이고 Extra는 Using where입니다. 즉 테이블을 Full Scan하면서 Where 조건을 이용해서 필터링한다는 의미입니다.

mysql> explain select * from t2 where c1 between 1 and 1000000 and c2=100;

+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9913212 |     1.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

-- 1회차 수행 (DB 재기동 및 OS 캐시 클리어 직후)

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free

mysql> select * from t2 where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (10.70 sec)

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_rnd_next            |   10000265 |
| Innodb_buffer_pool_pages_data    |      87016 |
| Innodb_buffer_pool_bytes_data    | 1425670144 |
| Innodb_buffer_pool_read_requests |    1284907 |
| Innodb_buffer_pool_reads         |        104 |
| Innodb_data_read                 | 1425670144 |
| Innodb_data_reads                |      87017 |
| Innodb_pages_read                |      87016 |
| Innodb_rows_read                 |   10000000 |
+----------------------------------+------------+

-- 2회차 수행 (Memory IO로 수행)

mysql> select * from t2 where c1 between 1 and 1000000 and c2=100;

+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (2.26 sec)

--@@ 성능 지표 Diff 결과 
+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_key                 |          1 |
| Handler_read_rnd_next            |   10000208 |
| Innodb_buffer_pool_read_requests |    1198076 |
| Innodb_rows_read                 |   10000000 |
+----------------------------------+------------+

 

NO_ICP 힌트를 이용해서 ICP 기능을 비활성화

 

테스트를 위해서 인덱스를 생성합니다.

create index t2_idx01 on t2 (c1,c2);

 

NO_ICP 힌트를 적용하면 ICP 방식으로 동작하지 않습니다. 이로 인해 Extra 칼럼에는 Using where라고 표시됩니다.

 

Extra에 표시되는 Using where의 의미는 인덱스를 이용해서 처리 범위를 한정하긴 하지만, 필터 처리를 위해서는 테이블을 액세스한다는 것입니다.  

mysql> explain select /*+ NO_ICP(t2) */ * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+

-- 1회차 수행 (DB 재기동 및 OS 캐시 클리어 직후)

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free

mysql> select /*+ NO_ICP(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (1.73 sec)

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_next                |     999998 |
| Handler_read_rnd_next            |        264 |
| Innodb_buffer_pool_pages_data    |       9891 |
| Innodb_buffer_pool_bytes_data    |  162054144 |
| Innodb_buffer_pool_read_requests |    3115486 |
| Innodb_buffer_pool_reads         |       1251 |
| Innodb_data_read                 |  162054144 | 
| Innodb_data_reads                |       9892 |
| Innodb_pages_read                |       9891 |
| Innodb_rows_read                 |     999999 |
+----------------------------------+------------+

-- 2회차 수행 (Memory IO로 수행)

mysql> select /*+ NO_ICP(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (0.93 sec)

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_next                |     999998 |
| Handler_read_rnd_next            |        207 |
| Handler_write                    |         71 |
| Innodb_buffer_pool_read_requests |    3022034 |
| Innodb_rows_read                 |     999999 |
+----------------------------------+------------+

 

ICP 기능을 사용한 경우

 

NO_ICP 힌트를 제거하면 ICP 방식으로 수행됩니다. ICP 힌트는 별도로 존재하지 않는데요. 이는 옵티마이저가 ICP 방식을 우선적으로 검토하기 때문입니다

.

ICP 방식을 사용하면, Extra는 Using index condition으로 표시됩니다. 즉, 인덱스를 이용해서 조건을 검사한다는 뜻입니다.

 

다시 말해, 인덱스 핕터링이죠.

mysql> explain select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 2185996 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

-- 1회차 수행 (DB 재기동 및 OS 캐시 클리어 직후)

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free

mysql> select * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (0.18 sec)

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_rnd_next            |        264 |
| Innodb_buffer_pool_pages_data    |       1148 |
| Innodb_buffer_pool_bytes_data    |   18808832 |
| Innodb_buffer_pool_read_requests |       2301 |
| Innodb_buffer_pool_reads         |       1148 |
| Innodb_data_read                 |   18808832 |
| Innodb_data_reads                |       1149 |
| Innodb_pages_read                |       1148 |
| Innodb_rows_read                 |          1 | 
+----------------------------------+------------+

-- 2회차 수행 (Memory IO로 수행)

mysql> select * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (0.11 sec

--@@ 성능 지표 Diff 결과 )

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_key                 |          1 |
| Handler_read_next                |          1 |
| Handler_read_rnd_next            |        207 |
| Handler_write                    |         69 |
| Innodb_buffer_pool_read_requests |       1161 |
| Innodb_rows_read                 |          1 |
+----------------------------------+------------+

 

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


Multi Range Read (MRR)를 연구하던 중에 MySQL 매뉴얼에서 다음과 같은 내용을 접했습니다. 


Without MRR, an index scan covers all index tuples for the key_part1 range from 1000 up to 2000, regardless of the key_part2 value in these tuples. The scan does extra work to the extent that tuples in the range contain key_part2 values other than 10000.

With MRR, the scan is broken up into multiple ranges, each for a single value of key_part1 (1000, 1001, ... , 1999). Each of these scans need look only for tuples with key_part2 = 10000. If the index contains many tuples for which key_part2 is not 10000, MRR results in many fewer index tuples being read.


위의 내용대로라면 MRR 방식을 이용하면 BETWEEN 조건을 매우 효율적으로 처리할 수가 있습니다.


예를 들어 C1, C2 칼럼이 각각 유니크한 칼럼이고, 인덱스를 C1+C2로 생성했다고 가정해보죠.


매뉴얼대로라면, 다음과 같은 조건이 입력될 때 1개의 인덱스 블록만 액세스하면 됩니다. (놀라운 일이죠?)


WHERE C1 BETWEEN 1 AND 1000000 
AND   C2 = 100; 


그런데 아무리 봐도 상식적으로 이해가 가질 않습니다. 인덱스 리프 블록을 액세스하지 않고, 어떻게 해당 조건에 만족하는 1건만 추출할 수가 있다는 걸까요?


할 수 없이, 소스를 분석해보기로 했습니다. MRR 동작과 관련된 소스는 sql/handler.cc 입니다. 며칠간 소스 분석 및 디버깅을 수행한 제 결론은 다음과 같습니다. 


"매뉴얼 내용은 오류가 있다. MRR 방식을 사용하더라도 액세스하는 리프 블록 범위는 줄지 않는다. 단, 조건에 만족하는 레코드만 랜덤 버퍼에 입력된다."


즉, 대부분 레코드가 인덱스 레벨에서 필터링 되는 쿼리는 MRR 방식을 사용하더라도 처리 속도가 빨라지지 않습니다.


그럼 분석 내용을 살펴보겠습니다.


테스트 환경 구성

drop table t2;
create table t2 (id integer primary key, c1 integer, c2 integer, dummy char(100)) engine=InnoDB;

delimiter $$
drop procedure if exists insert_t2_proc$$

-- C1, C2 칼럼에 유니크한 값을 입력합니다. 

create procedure insert_t2_proc()
begin
   declare i int default 1;
   
   while (i <= 10000000) do
      insert into t2 values (i, i, i, 'dummy');
	  set i=i+1;
   end while;
end $$

delimiter ;

start transaction;

call insert_t2_proc(); 

commit;

-- C1 + C2 칼럼으로 구성된 결합 인덱스를 생성합니다. 

create index t2_idx01 on t2(c1, c2);

analyze table t2;

-- 테이블과 인덱스 크기를 확인합니다. 

select table_name,
       table_rows,
	   data_length,
       round(data_length/(1024*1024),0)  as 'DATA_SIZE(MB)',
	   round(data_length/16384,0)        as 'DATA_BLOCKS',
       round(index_length/(1024*1024),0) as 'INDEX_SIZE(MB)',
	   round(index_length/16384,0)       as 'INDEX_BLOCKS'
FROM  information_schema.TABLES
where table_name='t2';
+------------+------------+-------------+---------------+-------------+----------------+--------------+
| table_name | table_rows | data_length | DATA_SIZE(MB) | DATA_BLOCKS | INDEX_SIZE(MB) | INDEX_BLOCKS |
+------------+------------+-------------+---------------+-------------+----------------+--------------+
| t2         |    9913212 |  1427111936 |          1361 |       87104 |            204 |        13041 |
+------------+------------+-------------+---------------+-------------+----------------+--------------+

select database_name, 
       table_name, 
	   index_name, 
	   round(stat_value*@@innodb_page_size/(1024*1024),0) as 'INDEX_SIZE(MB)',
	   stat_value as 'INDEX_BLOCKS'
from   mysql.innodb_index_stats 
where  stat_name='size'
and    table_name='t2';
+---------------+------------+------------+----------------+--------------+
| database_name | table_name | index_name | INDEX_SIZE(MB) | INDEX_BLOCKS |
+---------------+------------+------------+----------------+--------------+
| mysql         | t2         | PRIMARY    |           1361 |        87104 |
| mysql         | t2         | t2_idx01   |            204 |        13041 |
+---------------+------------+------------+----------------+--------------+


MRR 방식을 사용하지 않는 경우


MRR 방식을 사용하지 않을 때의 수행 시간은 0.13초입니다. 이때 Innodb_buffer_pool_read_requests는 1,160번입니다. t2_idx01 인덱스 블록 수가 13,041 블록이고 액세스 범위가 1/10이므로, 대략 리프 블록의 1/10을 읽었다고 볼 수 있습니다.


explain select /*+ NO_MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

mysql> select /*+ NO_MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (0.13 sec)

--@@ 성능 지표 diff value 확인 결과 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Innodb_buffer_pool_read_ahead    |          0 |
| Innodb_buffer_pool_read_requests |       1160 |
| Innodb_buffer_pool_reads         |          0 |
| Innodb_data_read                 |          0 |
| Innodb_data_reads                |          0 |
+----------------------------------+------------+


MRR 방식을 사용할 경우


아래와 같이 MRR 방식을 사용하더라도 수행 속도와 IO 블록 수가 개선되지 않습니다. 이전과 거의 비슷한 속도이고 IO 블록 수도 거의 같습니다. 즉, MRR 방식을 사용하더라도 매뉴얼의 설명대로 동작하지 않습니다.


mysql> explain select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 2185996 |    10.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+

mysql> select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (0.12 sec)

--@@ 성능 지표 diff value 확인 결과 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Innodb_buffer_pool_read_ahead    |          0 |
| Innodb_buffer_pool_read_requests |       1159 |
| Innodb_buffer_pool_reads         |          0 |
| Innodb_data_read                 |          0 |
| Innodb_data_reads                |          0 |
+----------------------------------+------------+


소스 분석을 통한 랜덤 버퍼 디버깅


랜덤 버퍼에 레코드를 저장하는 함수는 dsmrr_fill_buffer() 입니다. 이 부분에 집중적으로 디버깅을 해보면 됩니다.


수행 결과, 아래의 쿼리 수행 시에 1건 (rec_cnt=[1])을 랜덤 버퍼에 저장합니다. 즉, Single Point Interval인 [100,100] 1건만 랜덤 버퍼에 저장합니다. (혹시 매뉴얼에서 설명하고자 했던 것이 이것이었을까요?)

mysql> select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;

[DEBUG-MRR] multi_range_read_init [Tue May 16 18:22:18 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [293086112] rec_cnt=[1] [Tue May 16 18:22:20 2017]


비교를 위해서, C2 조건을 빼볼까요? 그러면 MRR 방식으로 처리해야 할 레코드가 100만 건이 됩니다. 현재 랜덤 버퍼의 크기는 256KB이고 해당 공간에는 65,536개의 정수(4바이트)를 저장할 수 있습니다. 따라서 100만 건을 처리하기 위해 dsmrr_fill_buffer() 함수를 16번 호출합니다.

mysql> explain select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 5       | NULL | 2186450 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+

[dsmrr_init]
[DEBUG-MRR] multi_range_read_init [Tue May 16 18:28:28 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1985640849] rec_cnt=[65536] [Tue May 16 18:28:29 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [217748122]  rec_cnt=[65536] [Tue May 16 18:28:30 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1078195231] rec_cnt=[65536] [Tue May 16 18:28:32 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1579427864] rec_cnt=[65536] [Tue May 16 18:28:34 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1580987960] rec_cnt=[65536] [Tue May 16 18:28:35 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [205242914]  rec_cnt=[65536] [Tue May 16 18:28:37 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [319501994]  rec_cnt=[65536] [Tue May 16 18:28:38 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1685075907] rec_cnt=[65536] [Tue May 16 18:28:40 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [19939101]   rec_cnt=[65536] [Tue May 16 18:28:41 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [542792330]  rec_cnt=[65536] [Tue May 16 18:28:43 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1949408410] rec_cnt=[65536] [Tue May 16 18:28:44 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1637581751] rec_cnt=[65536] [Tue May 16 18:28:46 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1142998578] rec_cnt=[65536] [Tue May 16 18:28:47 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [2038101548] rec_cnt=[65536] [Tue May 16 18:28:49 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1197209131] rec_cnt=[65536] [Tue May 16 18:28:50 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [368741656]  rec_cnt=[16960] [Tue May 16 18:28:51 2017]



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

Multi Range Read (이하 MRR)는 디스크 랜덤 IO를 효율적으로 처리하기 위해서 고안된 방식입니다.


MRR에 대한 자세한 설명은 이미 국내외 블로그에서 소개하고 있으므로, 아래의 내용을 먼저 보시는 것이 좋을 것 같습니다.

MRR 동작 방식은 사실 매우 간단합니다. 

  1. read_rnd_buffer_size 파라미터로 설정한 크기의 랜덤 버퍼에 레코드를 저장
  2. 저장된 레코드를 ROWID 순으로 정렬 
  3. ROWID로 정렬된 순서로 테이블 레코드를 액세스


만일, 처리할 레코드가 랜덤 버퍼에 모두 저장할 수 없을 정도로 많다면, 위의 처리를 n번 반복합니다.


레코드를 ROWID 순으로 정렬한 후에 디스크를 액세스하면, 디스크 랜덤 액세스보다는 시쿼셜 스캔을 할 가능성이 높기 때문에 디스크 IO가 발생하는 경우에는 MRR을 사용하는 것이 당연히 빠릅니다.


그런데 만일 모든 IO를 메모리 IO로 처리하는 경우에는 어떨까요?


MRR이 유리할까요? 아니면 MRR을 사용하지 않는 것이 유리할까요?


사실 이런 질문에 대한 답은 상황에 따라 다를 수 있습니다. 메모리 IO 시에 MRR을 사용할 때에 얻는 이득이 더 크다면, MRR을 사용하는 것이 빠르겠죠? 만일 반대의 경우에는 MRR을 사용하는 것이 더 느릴겁니다.


그럼 메모리 IO 시에 MRR 방식이 장점과 단점을 생각해보죠.


MRR 방식의 장점은 버퍼 액세스 횟수를 줄일 수 있다는 점입니다. 반면, MRR 방식의 단점은 랜덤 버퍼에 레코드를 저장하고, 레코드를 소팅한다는 점입니다.


MRR 방식을 사용해서 장점을 취하는 것이 유리할까요? 아니면 반대일까요? 


제가 수행한 테스트 케이스에서는 MRR 방식을 사용하면 버퍼 액세스 횟수가 1/3로 줄어듭니다. 이로인해 MRR 방식의 수행 속도가 3배 이상 빠릅니다. 

 

위의 결과 또한 이전 포스트에서 언급한 것과 같이 버퍼 디버그 모드로 수행한 결과입니다. 버퍼 디버그 모드를 해제하고 다시 컴파일을 한 결과, 버퍼 액세스 속도의 개선으로 인해 MRR 방식의 수행속도가 15%정도 빠르게 나왔습니다. 즉, 버퍼를 3배나 더 액세스했지만, 버퍼 IO 처리 속도가 빨라졌으므로 (사실 디버그 모드 때문에 엄청나게 느려진 것이 정확하겠네요) 성능 차이가 대폭 줄어들었습니다.

 

이 같은 결과는 테스트 상황에 따라서 가변적이라는 사실을 염두에 두고 아래의 내용을 읽어보시기 바랍니다.



테스트 환경 구성


show variables like "version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.7.18-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

테스트를 위해 버퍼 풀은 2G로 설정하고, Multi Buffer Pool 기능은 비활성화합니다. 그리고 버퍼 캐시 Pre-Load 기능도 비활성화합니다. 

innodb_buffer_pool_size = 2048M
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_instances=1

테스트 환경 구성을 위한 데이터 Set은  "MySQL - Multiple Buffer Pool의 문제점과 innodb_buffer_pool_instances 파라미터를 이용한 개선 방안" 포스트의 내용을 이용해서 생성합니다.


해당 프로시저를 이용하면 C2 칼럼에 대해서 클러스터링 팩터가 매우 나쁘게 데이터가 저장됩니다.



Disk IO 발생 시에 MRR 방식과 NO MRR 방식의 성능 비교


먼저 Disk IO 발생 시에 MRR 방식과 NO MRR 방식의 성능을 비교해보겠습니다. 테스트 마다 DB를 재기동하고 아래의 명령어를 이용해서 OS 캐시를 클리어합니다.

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free


1) MRR 방식을 사용한 경우


아래의 쿼리는 천 만건 중에서 인덱스를 이용해서 100만건을 액세스하는 쿼리입니다. 먼저 MRR 방식으로 수행해보겠습니다. Explain 결과를 보면 MRR 방식으로 수행되는 것을 알 수 있고, 수행 시간은 50초입니다. 이때 버퍼 풀을 모니터링해보면 쿼리 수행 후의 Pages read 블록수는 19,729입니다.

mysql>  explain select /*+ MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows    | filtered | Extra                                         |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_c2_idx     | t1_c2_idx | 5       | NULL | 2166732 |    10.00 | Using index condition; Using where; Using MRR |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------------------------+

mysql> select /*+ MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (13.02 sec)

mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 19729, created 35, written 37


2) MRR 방식을 사용하지 않는 경우


이번에는 MRR 방식을 비활성화 시킨 후에 쿼리를 수행해보겠습니다. 수행 속도가 74초이므로, MRR 방식에 비해서 처리 속도가 40% 이상 느려졌습니다.

mysql> explain select /*+ NO_MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows    | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_c2_idx     | t1_c2_idx | 5       | NULL | 2166732 |    10.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+------------------------------------+

mysql> select /*+ NO_MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (13.31 sec)

mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 19645, created 35, written 37


Memory IO시에 MRR 방식과 NO MRR 방식의 성능 비교


1) MRR 방식을 사용한 경우


쿼리 수행 전/후에 버퍼 풀을 모니터링한 결과, Pages read 수치가 증가하지 않았으므로 추가적인 Disk IO는 발생하지 않았음을 알 수 있습니다. 그리고 이때의 수행 속도는 16초입니다. Disk IO가 발생할 때에 비해서 월등히 빨라진 것을 알 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS\G ---------------------- BUFFER POOL AND MEMORY ---------------------- Pages read 19645, created 35, written 37 mysql> select /*+ MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy'; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (1.52 sec) mysql> SHOW ENGINE INNODB STATUS\G ---------------------- BUFFER POOL AND MEMORY ---------------------- Pages read 19645, created 35, written 37


2) MRR 방식을 사용하지 않는 경우


MRR 방식을 사용하지 않도록 힌트를 적용하면, 수행 속도가 62초로 증가합니다. MRR 방식 보다 3배 이상 느린 결과가 나오네요. 왜 이같은 현상이 발생했는지 조금 더 살펴보겠습니다.

mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 19645, created 35, written 37

mysql> select /*+ NO_MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.78 sec)

mysql> SHOW ENGINE INNODB STATUS\G
Pages read 19645, created 35, written 37


Memory IO시에 MRR 방식과 NO MRR 방식 간의 버퍼 액세스 횟수 비교


Innodb Pool과 관련된 성능 통계 정보를 이용해서 두 방식간의 차이점을 분석해보겠습니다.


1) MRR 방식을 사용한 경우


쿼리 수행 전/후에 Innodb 관련 성능 통계 정보를 보면, Innodb_buffer_pool_read_requests가 1,137,126번 발생한 것을 알 수 있습니다.

mysql> select /*+ MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (16.02 sec)

--@@ Diff 결과를 추출하는 쿼리 수행 결과 
+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Innodb_buffer_pool_read_ahead    |          0 |
| Innodb_buffer_pool_read_requests |    1137126 |
| Innodb_buffer_pool_reads         |          0 |
| Innodb_data_read                 |          0 |
| Innodb_data_reads                |          0 |
+----------------------------------+------------+


2) MRR 방식을 사용하지 않는 경우


쿼리 수행 전/후에 Innodb 관련 성능 통계 정보를 보면, Innodb_buffer_pool_read_requests가 3,019,851번 발생한 것을 알 수 있습니다. 즉, MRR 방식에 비해서 버퍼 액세스 횟수가 3배 정도 많습니다. 이로써, 테스트 환경과 같이 클러스터링 팩터가 매우 나쁜 인덱스를 사용하는 경우에는 MRR 방식이 모든 면에서 월등히 유리하다는 것을 확인했습니다.

mysql> select /*+ NO_MRR(t1) */ count(*) from t1 FORCE INDEX(t1_c2_idx) where c2 between 1 and 1000000 and dummy='dummy';
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1 min 1.75 sec)

--@@ Diff 결과를 추출하는 쿼리 수행 결과 
+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Innodb_buffer_pool_read_ahead    |          0 |
| Innodb_buffer_pool_read_requests |    3019851 |
| Innodb_buffer_pool_reads         |          0 |
| Innodb_data_read                 |          0 |
| Innodb_data_reads                |          0 |
+----------------------------------+------------+


Perfomance_schema의 global_status 테이블을 이용해서 Diff 결과를 추출하는 스크립트

-- 테이블 생성 

create table mon_stat_1 (
	logging_time   datetime, 
	variable_name  varchar(50), 
	variable_value integer) engine=memory;
	
	
create table mon_stat_2 (
	logging_time   datetime, 
	variable_name  varchar(50), 
	variable_value integer) engine=memory;	

-- 쿼리 수행 전에 수행 

delete from mon_stat_1;

insert into mon_stat_1 
select now(), variable_name, variable_value 
from   performance_schema.global_status 
where  variable_value  > 0;

-- 쿼리 수행 후에 수행 

delete from mon_stat_2;

insert into mon_stat_2 
select now(), variable_name, variable_value 
from   performance_schema.global_status 
where  variable_value  > 0;

-- 결과 추출 

select a.variable_name, 
       b.variable_value-a.variable_value diff_value
from   mon_stat_1 a LEFT JOIN mon_stat_2 b ON  a.variable_name = b.variable_name
WHERE  a.variable_name like 'Innodb_buffer_pool_read%'
OR     a.variable_name like 'Innodb_data_read%';


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

 

아래의 테스트 결과는 완전히 잘못됐습니다. 혼란을 드려서 죄송합니다. 디버그 옵션을 켜고 테스트를 한 탓에 속도가 너무 느리게 나오기도 했고, 테스트 케이스 간의 성능 비교도 부정확했습니다.

 

디버그 옵션을 끄고 다시 컴파일한 후에 테스트를 해보니, 아래의 테스트 케이스에서는 Mutl Buffer Pool인 경우가 가장 빠릅니다.

 

DISK IO  

  •  256 MB                  : (6.86 sec)
  •  2 GB + Multi Buffer  : (6.64 sec)
  •  2 GB + Single Buffer : (6.68 sec) 

Memory IO

  •  256 MB                  : (3.27 sec)
  •  2 GB + Multi Buffer  : (2.32 sec)
  •  2 GB + Single Buffer : (2.34 sec)

 

연구 시에, 잘못된 테스트 환경이 가져오는 위험성을 보여주기 위해서 아래의 내용은 그대로 두기로 했습니다. 테스트하면서 버퍼 IO 속도가 너무 느리다는 점을 간과한 저의 실수입니다.(부끄럽네요. ㅠㅠ)

 

-- 아래 --

 

1GB 테이블에 대한 Full Scan을 빠르게 처리하기 위해서 innodb 버퍼 풀을 128MB(기본 설정값)에서 2GB로 변경했습니다. 과연 빨라졌을까요?

 

이상하게도 2배 가까이 느려졌습니다. 왜 이런 현상이 발생했을까요?

 

Oracle이나 PostgreSQL 처럼 대량의 스캔으로부터 버퍼 캐시를 보호하는 전략이 있다면 처리 속도가 기존과 비슷해야 할 것이고, 만일 그런 전략이 없다면, 처리 속도가 더 빨라져야함에도 불구하고 매우 느려졌습니다.

 

그 이유는 Multi Buffer Pool (이하 멀티 버퍼 풀) 사용 시에 발생하는 성능 문제 때문입니다.

 

멀티 버퍼 풀이란 버퍼 풀을 여러 개의 서브 풀로 나눠서 관리하는 것을 말하며, 버퍼 풀 크기를 1GB 이상으로 설정하면 멀티 버퍼 풀로 동작합니다.

 

하나의 버퍼 풀을 논리적 또는 물리적으로 나눠서 관리하는 것은 트랜잭션의 동시성을 높이기 위해서 필요한 기법임에는 분명합니다. 공유 메모리를 액세스할 때의 경합을 감소하기 위한 기본 원칙은 병목 지점을 분산하거나 더 많은 래치 또는 LW 락을 제공하는 것이니까요.

 

그런데 멀티 버퍼 풀은 매우 큰 테이블을 스캔할 때는 매우 성능이 떨어집니다. 2016년 논문에서도 이 문제점을 지적하고 있습니다.

 

즉, 트랜잭션의 동시성을 높인다는 목적은 달성했지만 대용량 처리를 위한 최적화는 아직 덜 된 상태로 보입니다.

 

이 문제를 개선하기 위해서는 innodb_buffer_pool_instances 파라미터를 1로 설정하면 됩니다.

 

해당 파라미터는 서브 풀 개수를 지정하는 파라미터이며, 1로 설정하면 서브 풀을 사용하지 않게 됩니다. 즉, 멀티 버퍼 풀 기능을 비활성화합니다. 서브 풀을 사용하지 않으면 대용량 테이블 스캔 속도는 빨라집니다. 반면 동시성은 떨어집니다. 따라서 MySQL 5.7 버전까지는 DBA가 업무 환경에 맞게 파라미터를 적절히 설정해야 합니다. (사실 쉽지 않은 일이죠. 대부분 Hybrid용으로 사용할테니까요. 결국 당연한 말이지만 우선 순위에 따라 결정을 해야할 겁니다. MySQL 8 버전에서는 이 문제가 개선되었으면 합니다.)

 

이와 관련된 또 다른 문제점은 버퍼 풀을 크게 설정하는 것이 반드시 대용량 스캔에 유리한 것은 아니라는 점입니다. 버퍼 풀을 2GB로 설정하고, 서브 풀을 비활성화 했음에도 스캔 속도는 기존보다는 20% 가까이 느립니다. 이 같은 현상은 일반적으로 해시 테이블 버킷 수의 부족, 또는 최적화되지 않은 해시 함수로 인해 발생하는 해시 충돌의 문제 때문입니다. 이 같은 현상은 테스트 환경에 따라서 다를 수 있습니다만, "메모리 IO가 Disk IO보다 항상 빠르다"는 일반 상식과는 다른 결과가 나올 수도 있다는 점을 유의해야 합니다.

 

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

 

테스트 환경 구성

 

테스트 환경은 MySQL 5.7입니다.

 

show variables like "version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.7.18-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

drop table t1;
create table t1 (c1 integer primary key, c2 integer, dummy char(100)) engine=InnoDB;

delimiter $$
drop procedure if exists insert_t1_proc$$

-- 천만 건을 입력합니다. (참고로, 이 프로시저는 MRR 이상 현상을 분석하기 위해 사용한 것입니다. MRR 이상 현상을 분석하다가 이 문제를 발견하게 됐습니다)

create procedure insert_t1_proc()
begin
   declare i int default 1;
   declare j int default 1;
   
   while (i <= 10000) do
      while (j <= 1000) do
	     insert into t1 values (j+(1000*(i-1)), i+(10000*(j-1)), 'dummy');
	     set j=j+1;
	  end while;
	  set i=i+1;
	  set j=1;
   end while;
end $$

delimiter ;

start transaction;

call insert_t1_proc(); 

commit;

create index t1_c2_idx on t1(c2);

analyze table t1;

-- 테이블 크기는 1.3GB 정도이고, 블록 수는 8만4천 블록 정도입니다. (MySQL 블록 크기는 16KB)

select table_name,
       table_rows,
	   data_length,
       round(data_length/(1024*1024),0)  as 'DATA_SIZE(MB)',
	   round(data_length/16384,0)         as 'DATA_BLOCKS',
       round(index_length/(1024*1024),0) as 'INDEX_SIZE(MB)'
FROM  information_schema.TABLES
where table_name='t1';
+------------+------------+-------------+----------------+-------------+-----------------+
| table_name | table_rows | data_length | DATA_SIZE(MB) | DATA_BLOCKS | INDEX_SIZE(MB) |
+------------+------------+-------------+----------------+-------------+-----------------+
| t1         |    9916130 |  1378877440 |           1315 |       84160 |             160 |
+------------+------------+-------------+----------------+-------------+-----------------+

 

 

 

Pre-Load 관련 파라미터 OFF로 설정

 

MySQL은 Shutdown 전에 버퍼 풀의 25% (기본 설정값)에 해당하는 버퍼의 메타 정보를 ib_buffer_pool 파일에 기록합니다. 그런 후에 DB를 재기동하면 해당 내용을 이용해서 백그라운드로 로딩 작업을 수행합니다. 자세한 내용은 여기를 참고하세요. (MySQL 관련해서 다양한 정보를 제공하는 블로그입니다) 이 기능은 운영 시에는 좋은 기능이지만 정확한 테스트를 위해서 해당 파라미터를 OFF로 설정합니다.

[mysqld]
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF

 

Innodb 버퍼 풀이 128MB일 때의 스캔 속도

 

explain 결과를 보면, 아래의 쿼리가 테이블 Full Scan으로 동작하는 것을 알 수 있습니다.

mysql> explain select count(*) from t1 where dummy='dummy';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9916130 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

 

정확한 테스트를 위해서 매 테스트마다 DB 재기동 및 OS 버퍼를 클리어합니다.

-- db stop
-- disk cache clear
# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
-- db start 

-- 버퍼 풀 크기는 128MB이고 버퍼 풀 개수는 1개임을 확인합니다.

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

 

-- 1회차 수행 속도

 

1회차 수행 속도는 64초입니다.

mysql> select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 4.06 sec)

 

이때 버퍼 모니터링을 하면, Pages read (DISK IO 블록 수)는 84,418 블록임을 알 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS\G
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

-- 2회차 수행 속도

 

2회차 수행 속도는 57초입니다. Disk IO 블록 수는 79,090 (163508 - 84418)입니다. Disk IO가 조금 줄어서 약간 더 빨라진 것을 알 수 있습니다. (참고로 3회차 수행 속도도 이와 비슷합니다)

mysql> select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (56.77 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 163508, created 35, written 37

 

 

Innodb 버퍼 풀을 2GB로 변경한 후의 스캔 속도

 

버퍼 풀 크기를 2GB로 증가합니다.

[mysqld]
innodb_buffer_pool_size = 2048M

-- db stop
-- disk cache clear
# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
-- db start 

버퍼 풀이 2GB이고, 이때 서브 풀이 8개로 설정된 것을 알 수 있습니다.

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              8 |
+--------------------------------+

 

-- 1회차 수행 속도

 

1회차 수행 속도는 125초 입니다. 기존 대비 2배 이상 느립니다. 이상하죠?

mysql>  select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2 min 5.00 sec)

이때, 버퍼 풀을 모니터링해보면 Disk IO 블록 수는 84,418이고 이는 이전과 동일합니다. 단, 8개의 풀에 대략 만 블록씩 나눠서 로딩된 것을 알 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Pages read 10597, created 0, written 2
---BUFFER POOL 1
Pages read 10566, created 0, written 0
---BUFFER POOL 2
Pages read 10568, created 0, written 0
---BUFFER POOL 3
Pages read 10349, created 0, written 0
---BUFFER POOL 4
Pages read 10556, created 0, written 0
---BUFFER POOL 5
Pages read 10625, created 0, written 0
---BUFFER POOL 6
Pages read 10566, created 35, written 35
---BUFFER POOL 7
Pages read 10591, created 0, written 0

 

-- 2회차 수행 속도

 

아래의 버퍼 모니터링 결과를 보면, Pages read 수치가 이전과 동일한 84,418입니다. 즉, 2회차 수행은 모두 Memory IO로 처리됐습니다. 이를 통해 MySQL은 대량의 스캔으로부터 버퍼를 보호하는 전략은 없는 것을 알 수 있습니다. 그런데 수행 속도가 그리 빠르지 않습니다. Memory IO로 처리했음에도 처리 속도는 기존과 거의 비슷합니다.

select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2 min 4.93 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

innodb_buffer_pool_instances 파라미터를 1로 변경한 후의 테스트 결과

 

서브 풀을 사용하지 않도록 innodb_buffer_pool_instances 파라미터를 1로 설정합니다.

[mysqld]
innodb_buffer_pool_instances = 1

-- db stop
-- disk cache clear
# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
-- db start

 

파라미터 설정 효과 인해 버퍼 풀이 1개로 설정된 것을 알 수 있습니다. 

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

 

-- 1회차 수행 속도

 

수행 속도도 제법 빨라졌습니다. 하지만 여전히 버퍼 크기가 128MB일때보다는 느립니다.

mysql>  select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 19.56 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

-- 2회차 수행 속도

 

2회차 수행은 모두 Memory IO로 처리됐습니다. 덕분에 기존 대비 10초 정도 빨라졌지만, 이 속도 역시 버퍼 풀이 128 MB일때에 비해서는 조금 느립니다.

 

mysql> select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 9.68 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

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

이번 시간에 살펴볼 내용은 Derived Table을 이용해서 Nested Loop 조인 시에 발생하는 BNL에 대한 튜닝 방법입니다. 이 내용은 이전 포스트인 “MySQL – Block Nested-Loop (BNL)에 대한 이해“와 연결된 내용입니다.

 

이번 포스팅에서 다룰 내용이 길지 않음에도 별도의 포스트로 분리한 이유는, 나름 참신한 방법으로 Nested Loop 시에 발생하는 BNL을 튜닝할 수 있는 방법이기 때문입니다. 결론부터 말하면, Derived Table의 특징을 이용하면 인덱스를 생성하지 않고도 BNL에 대한 성능 튜닝이 가능합니다. 왜냐면 TEMPTABLE 알고리즘을 이용하는 Derived Table은 조인 칼럼에 대해서 내부적으로 임시 인덱스를 생성하기 때문입니다. 이 원리를 이용하면 매우 극적인 튜닝이 가능합니다. 예제를 통해 살펴보겠습니다.

 

앞선 테스트에서 이어서 테스트를 진행하므로, 일단 인덱스를 drop 합니다.

alter table t2 drop index t2_c1_idx;

그리고 다음과 같이 TEMPTABLE 알고리즘을 이용하는 뷰 (named Derived Table)를 생성합니다. 뷰를 생성하는 이유는 5.7부터는 aggregate가 없는 인라인 뷰 (unnamed Derived Table)를 Merge 방식을 사용하므로 TEMPTABLE 방식으로 유도되지 않기 때문입니다. (이 내용은 여기를 참고하세요)

create or replace ALGORITHM=TEMPTABLE view t2_v 
as select * from t2;

 

뷰를 이용한 Explain 결과를 보면, t2 테이블을 스캔한 후에 c1 칼럼에 대해서 내부적인 임시 인덱스 (auto_key0) 생성한 후, 해당 키를 이용해서 조인을 수행한 것을 알 수 있습니다. 그리고 수행 속도를 보면, BNL 방식을 사용할 때보다 월등히 빨라졌습니다. 거의 인덱스를 생성할 때의 수행 속도와 비슷할 정도입니다. 뷰를 생성하는 번거로움은 있지만, 성능 개선 효율을 보면 생각해 볼 만한 방식인 것은 분명합니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL       |  4956 |    11.11 | Using where |
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 5       | mysql.a.c1 |    10 |   100.00 | NULL        |
|  2 | DERIVED     | t2         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL       | 49720 |   100.00 | NULL        |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+------------+---------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                       |
+----------+------------+---------------------------------------------------------------------------------------------+
|        1 | 0.06712000 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 | 0.06936275 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 0.11768975 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+------------+---------------------------------------------------------------------------------------------+

 

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

MySQL – Block Nested-Loop (BNL)에 대한 이해

MySQL 2017.05.10 15:51 Posted by 시연아카데미

이번 시간에 살펴볼 내용은 Block Nested-Loop (이하 BNL)입니다.

 

MySQL이 BNL을 제공하는 이유는 Nested Loop 조인만 지원하는 한계점을 보완하기 위해서입니다. 예를 들어, 조인 칼럼 인덱스가 없다고 가정해보죠. 이 경우, ORACLE과 PostgreSQL과 같은 DBMS는 해시 조인 또는 머지 조인을 사용합니다. 그런데 Nested Loop 조인만 지원하는 MySQL은 이 문제를 어떻게 처리할까요?

 

만일 BNL 방식이 없다면, Driving 테이블의 건수만큼 Inner 테이블을 스캔 (또는 Index Full Scan)해야만 합니다. 이것은 어마어마한 성능 저하를 초래합니다. 이 문제를 어느 정도 완화하기 위해서 MySQL은 BNL 방식을 고안했습니다.

 

BNL 방식은 프로세스 내에 별도의 버퍼 (이를 조인 버퍼라고 합니다)에 Driving 테이블의 레코드를 저장한 후에 Inner 테이블을 스캔하면서 조인 버퍼를 탐색하는 방식입니다. 따라서 BNL 방식을 사용하면 BNL 방식을 사용하지 않는 것에 비해서는 빠릅니다. 하지만 BNL 방식은 Nested Loop 조인의 한계를 개선하기 위한 차선책일 뿐 근본적인 해결책은 아닙니다. Sort Merge 조인이나 Hash 조인에 비해서 턱없이 느린 방식이기 때문입니다. 이 때문에 MySQL에서도 가능한 빨리 Sort Merge나 Hash 조인을 지원하면 좋겠지만, MySQL 8에서도 아직 지원 예정은 없는 것 같습니다.

 

그럼 예제를 통해 살펴보겠습니다. 참고로, Sort Merge, Hash 조인의 성능을 간접적으로 비교하기 위해서 PostgreSQL과 ORACLE에서 테스트한 결과도 첨부합니다.

 

테스트 환경 구성


 

테스트 환경은 MySQL 5.7입니다.

show variables like "version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.7.18-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

drop table t1;
drop table t2;

create table t1 (c1 integer, c2 integer, dummy char(100)) engine=InnoDB;
create table t2 (c1 integer, c2 integer, dummy char(100)) engine=InnoDB;

call generate_series(1,50000);

insert into t1 select series, series, 'dummy' from  series_tmp order by series limit 5000;
insert into t2 select mod(series,5000), series, 'dummy' from  series_tmp order by series limit 50000;

 

BNL 성능 테스트


 

아래의 Explain 결과처럼 Extra 칼럼에 “Using Join buffer (Block Nested Loop)“가 표시되면 BNL 방식으로 수행된 것입니다. BNL 방식은 조인 칼럼에 적절한 인덱스가 없을 때 수행될 수 있는 방식이므로, BNL 방식으로 수행될 때의 type은 ALL (Table Full Scan) 또는 Index (Index Full Scan)입니다.

 

그리고 Driving 테이블의 레코드를 조인 버퍼에 저장하기 때문에 조인 버퍼의 크기가 매우 중요합니다. 만일 Driving 테이블의 레코드를 조인 버퍼에 모두 저장할 수 없다면, Inner 테이블을 여러 번 스캔해야 하는 부하가 발생합니다. 참고로 아래의 예제는 모두 조인 버퍼 내에서 수행됐으므로, T2 테이블을 1회만 스캔했습니다.

 

따라서 3개의 쿼리 간의 성능 차이는 조인 버퍼를 탐색하는 시간이라고 해도 무방하며, 조인 버퍼 내에 레코드가 많을수록 선형적으로 처리 성능이 저하되는 것을 알 수 있습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  4956 |    11.11 | Using where                                        |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49720 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+-------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                     |
+----------+-------------+-------------------------------------------------------------------------------------------+
|        1 |  0.17276250 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 |  1.57978475 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 15.68152675 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+-------------+-------------------------------------------------------------------------------------------+

 

BNL DISABLE 후의 성능 테스트


 

그렇다면 과면 BNL은 정말 효과가 있는 방식일까요?

다음과 같이, 5.7에서부터 제공하는 NO_BNL 힌트를 이용해서 수행 속도를 비교해보겠습니다. 아래의 Explain 결과를 보면 Extra 칼럼에 BNL이 나타나지 않습니다. 즉, NO_BNL 힌트의 영향때문에 BNL 방식으로 수행되지 않은 것인데요. 처리 결과를 보면 알 수 있듯이, BNL 방식에 비해서 5배 이상 느립니다. 이로써 BNL 방식은 어느 정도의 튜닝 효과가 있다고 할 수 있습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
explain
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  4956 |    11.11 | Using where |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49720 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
 
set profiling=1;
 
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
 
+----------+-------------+------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                            |
+----------+-------------+------------------------------------------------------------------------------------------------------------------+
|        1 |  0.83281350 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50         |
|        2 |  8.31119800 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500        |
|        3 | 85.36058425 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000       |
+----------+-------------+------------------------------------------------------------------------------------------------------------------+

 

인덱스 생성 후의 성능 테스트


 

그렇다면 인덱스를 생성하면 어떻게 될까요?

당연한 결과지만, 너무나도 빠르게 처리됩니다. 10초 이상 수행된 3번째 쿼리가 인덱스 생성 후에는 0.1초 만에 수행되므로 100배 이상 빨라진 것이죠. 하지만 운영 단계에서 인덱스를 생성하는 것은 매우 부담스러운 작업입니다. 입력 속도 저하, 스토리지 사용량 증가, 다른 쿼리에 대한 영향도 분석 필요 등등. 매우 많은 고려가 필요합니다. 물론 설계 상의 실수로 인해, 반드시 생성해야 할 칼럼에 인덱스를 생성하지 않았던 것이라면 이런 고민을 덜 하겠지만, NDV가 좋지 않은 칼럼에 대한 인덱스 생성은 많은 고려가 필요한 게 사실이니까요. 그렇다면 인덱스를 생성할 수 없다면 어떻게 튜닝을 해야 할까요? 이 부분은 여기를 참고하세요.

 

— 인덱스 생성

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create index t2_c1_idx on t2(c1);
 
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL      | NULL    | NULL       | 4956 |    11.11 | Using where |
|  1 | SIMPLE      | b     | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.a.c1 |    9 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                     |
+----------+------------+-------------------------------------------------------------------------------------------+
|        1 | 0.00297075 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 | 0.01187425 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 0.12604875 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+------------+-------------------------------------------------------------------------------------------+

 

PostgreSQL 수행 내역


 

PostgreSQL은 해시 조인과 머지 조인을 지원합니다. 따라서 해시 조인과 머지 조인의 수행 결과를 보면 모두 0.1초 이내에 수행됩니다. NL 조인의 수행 속도는 58초 정도인데요. 이 속도를 MySQL과 비교해보면 BNL 방식을 적용한 것보다는 느리고 NO BNL 방식보다는 조금 빠른 정도입니다.

 

— 해시 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
explain analyze select * From t1 a, t2 b where a.c1=b.c1;
 
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=122.23..5444.27 rows=121631 width=824) (actual time=1.662..26.778 rows=49990 loops=1)
   Hash Cond: (b.c1 = a.c1)
   ->  Seq Scan on t2 b  (cost=0.00..1018.34 rows=15534 width=412) (actual time=0.009..7.129 rows=50000 loops=1)
   ->  Hash  (cost=102.66..102.66 rows=1566 width=412) (actual time=1.643..1.643 rows=5000 loops=1)
         Buckets: 8192 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 753kB
         ->  Seq Scan on t1 a  (cost=0.00..102.66 rows=1566 width=412) (actual time=0.004..0.754 rows=5000 loops=1)
 Planning time: 0.073 ms
 Execution time: 30.958 ms

 

— 머지 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
set enable_hashjoin=off;
explain analyze select * From t1 a, t2 b where a.c1=b.c1;
                                                     QUERY PLAN
 
-----------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=5208.01..7079.14 rows=121631 width=824) (actual time=40.566..81.406 rows=49990 loops=1)
   Merge Cond: (a.c1 = b.c1)
   ->  Sort  (cost=185.76..189.67 rows=1566 width=412) (actual time=1.420..2.105 rows=5000 loops=1)
         Sort Key: a.c1
         Sort Method: quicksort  Memory: 896kB
         ->  Seq Scan on t1 a  (cost=0.00..102.66 rows=1566 width=412) (actual time=0.007..0.647 rows=5000 loops=1)
   ->  Materialize  (cost=5022.25..5099.92 rows=15534 width=412) (actual time=39.133..58.476 rows=50000 loops=1)
         ->  Sort  (cost=5022.25..5061.09 rows=15534 width=412) (actual time=39.130..48.962 rows=50000 loops=1)
               Sort Key: b.c1
               Sort Method: external merge  Disk: 5816kB
               ->  Seq Scan on t2 b  (cost=0.00..1018.34 rows=15534 width=412) (actual time=0.005..7.529 rows=50000 loops=1)
 Planning time: 0.095 ms
 Execution time: 88.168 ms

 

— NL 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set enable_mergejoin=off;
explain (analyze, buffers) select * From t1 a, t2 b where a.c1=b.c1;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..366018.58 rows=121631 width=824) (actual time=0.014..58100.334 rows=49990 loops=1)
   Join Filter: (a.c1 = b.c1)
   Rows Removed by Join Filter: 249950010
   Buffers: shared hit=950
   ->  Seq Scan on t2 b  (cost=0.00..1018.34 rows=15534 width=412) (actual time=0.007..40.626 rows=50000 loops=1)
         Buffers: shared hit=863
   ->  Materialize  (cost=0.00..110.49 rows=1566 width=412) (actual time=0.000..0.477 rows=5000 loops=50000)
         Buffers: shared hit=87
         ->  Seq Scan on t1 a  (cost=0.00..102.66 rows=1566 width=412) (actual time=0.003..0.739 rows=5000 loops=1)
               Buffers: shared hit=87
 Planning time: 0.043 ms
 Execution time: 58114.032 ms

 

ORACLE 수행 내역


 

ORACLE 수행 내역을 보면 해시 조인과 머지 조인 모두 0.1초 내로 수행됩니다. 뿐만 아니라 NL 조인도 12초 정도에 수행됩니다. 이 속도는 MySQL의 BNL 방식보다 더 빠르네요.

 

— 해시 조인

 

1
2
3
4
5
6
7
8
9
10
11
select /*+ leading(a b) use_hash(b) gather_plan_statistics */ * from t1 a, t2 b where a.c1=b.c1;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  49990 |00:00:00.09 |    4196 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |  48072 |  49990 |00:00:00.09 |    4196 |  1397K|  1118K| 1445K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   5309 |   5000 |00:00:00.01 |      84 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  48070 |  50000 |00:00:00.03 |    4112 |       |       |          |
----------------------------------------------------------------------------------------------------------------

 

— 머지 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
select /*+ leading(a b) use_merge(b) gather_plan_statistics */ * from t1 a, t2 b where a.c1=b.c1;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |  49990 |00:00:00.10 |     919 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |  48072 |  49990 |00:00:00.10 |     919 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |   5309 |   5000 |00:00:00.01 |      84 |   832K|   511K|  739K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |   5309 |   5000 |00:00:00.01 |      84 |       |       |          |
|*  4 |   SORT JOIN         |      |   5000 |  48070 |  49990 |00:00:00.04 |     835 |  7282K|  1075K| 6472K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |  48070 |  50000 |00:00:00.01 |     835 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

 

— NL 조인

 

1
2
3
4
5
6
7
8
9
10
11
select /*+ leading(a b) use_nl(b) gather_plan_statistics */ * from t1 a, t2 b where a.c1=b.c1;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  49990 |00:00:12.80 |    4181K|
|   1 |  NESTED LOOPS      |      |      1 |  48072 |  49990 |00:00:12.80 |    4181K|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   5309 |   5000 |00:00:00.01 |    3339 |
|*  3 |   TABLE ACCESS FULL| T2   |   5000 |      9 |  49990 |00:00:11.89 |    4178K|
-------------------------------------------------------------------------------------

 

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

MySQL – Loose Index Scan 동작 원리의 이해

MySQL 2017.05.10 15:46 Posted by 시연아카데미

MySQL 매뉴얼에서는 Loose Index Scan 방식을 Group by 최적화를 위한 기법이라고 설명하고 있습니다.
그런데 매뉴얼의 설명과 용어만으로는 Loose Index Scan의 동작 방식을 정확히 이해하기가 힘듭니다. 또한, Loose Index Scan 방식은 많은 제약 사항이 있습니다. 이 같은 이유를 저 나름대로 분석해보면, Loose Index Scan 방식은 Index를 이용해서 Group by를 최적화하기 위해 고안된 방식이라기보다는 “인덱스를 이용해서 Group 별 Min, Max 값을 효율적으로 처리하기 위해 고안된 방식“이기 때문입니다. 즉, 정의를 조금 더 축소해서 명확해서 해뒀으면 이해하기 수월했을 텐데, “Group by 최적화”와 같은 거창한(?) 정의를 해둔 탓에 이해하기도 힘들고 제약 사항도 많아진 것 같습니다.

 

 

그림으로 보는 Loose Index Scan의 원리


 

아래 그림을 보면, Loose Index Scan의 원리를 쉽게 파악할 수 있으리라 봅니다. 예를 들어, 인덱스를 c1+c2+c3 칼럼을 구성하고, c1으로 group by를 한 후에 min(c2), max(c2)를 구하거나, c1, c2 칼럼으로 group by를 한 후에 min(c3), max(c3)을 구한다고 가정해보죠. 이때는 인덱스의 특정 위치만 액세스해서 원하는 결과를 추출할 수 있습니다.(첫 번째 그림 참조)

그런데 group by 를 c1으로 한 후에 max(c3)을 구한다고 가정해보죠. 이때는 Loose Index Scan 방식으로 처리할 수 없습니다. 왜냐면 group by를 c1 칼럼으로 했으므로, max(c3) 값은 min/max 위치가 아닌 인덱스의 중간 부분에 위치할 수도 있기 때문입니다.(두 번째 그림 참조)

이 같은 이유로 인해, “min, max 칼럼은 group by 칼럼의 바로 뒤 칼럼이어야 한다”는 제약 조건이 있습니다. 원리를 이해하면 이것은 당연한 말이죠. 또한, 같은 이유로 sum(), count()와 같이 모든 인덱스 킷값을 액세스해야 하는 함수 또한 사용할 수 없습니다. 즉, Loose Index Scan은 오로지 min/max 값을 처리하기 위한 방식이라고 할 수 있습니다.

 

MySQL-Loose Index Scan 예제
MySQL-Tight Index Scan 예제

 

테스트 데이터 생성


 

실제 데이터를 가지고 테스트를 해보겠습니다.

drop table t1;

create table t1 (id integer primary key, c1 integer, c2 integer, c3 integer, dummy char(100));

CALL generate_series(1,1000000);

insert into t1 select series, mod(series,10), mod(series,100), mod(series,1000), 'dummy' from series_tmp;

create index t1_idx01 on t1(c1,c2,c3);

 

Loose Index Scan으로 수행되는 예제 #1


 

아래의 예제는 c1 칼럼으로 group by를 한 후에 min(c2), max(c2)를 구하는 예제입니다. Loose Index Scan 방식으로 수행될 때 Explain 결과를 보면 “Using index for group-by“라고 표시됩니다. 그리고 이때 Handler 사용 통계를 보면, Handler_read_key 값이 12로 나타납니다. 이 값을 통해 인덱스 키를 12번만 액세스했다는 것을 알 수 있습니다. c1 칼럼으로 group by를 하면 10건이므로 10건+(2번)만큼 인덱스 키를 액세스합니다. 그리고 max(c2)인 경우에는 이 값이 22입니다. 아마도 max 값 추출 시에는 min 값을 거쳐서 max 값으로 건너뛰는 방식을 사용하는 것으로 추정됩니다.

1
2
3
4
5
6
7
explain
select c1, min(c2) from t1 group by c1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_idx01      | t1_idx01 | 10      | NULL |   10 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

—  MIN() 추출 시

flush status;
select c1, min(c2) from t1 group by c1;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 12    |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

— MAX() 추출 시

flush status;
select c1, max(c2) from t1 group by c1;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 22    |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

 

Loose Index Scan으로 수행되는 예제 #2


 

아래의 예제는 c1, c2 칼럼으로 group by를 한 후에 min(c3), max(c3)를 구하는 예제입니다. min(c3)을 추출할 때는 Handler_read_key 값이 102입니다. c1,c2 칼럼으로 group by를 하면 100건이므로, 100건+(2번)만큼 인덱스 키를 액세스한 것을 나타냅니다. 그리고 max(c3)인 경우에는 이 값이 202입니다.

1
2
3
4
5
6
7
explain
select c1, c2, min(c3) from t1 group by c1,c2;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_idx01      | t1_idx01 | 15      | NULL |   96 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

—  MIN() 추출 시

flush status;
select c1, c2, min(c3) from t1 group by c1,c2;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 102   |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

— MAX() 추출 시

flush status;
select c1, c2, max(c3) from t1 group by c1,c2;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 202   |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

 

Loose Index Scan으로 수행되지 않는 예제


 

아래의 쿼리는 Index Loose Scan 방식으로 동작하지 않습니다. Explain 결과를 봐도 Using Index로 표시됩니다.
쿼리를 수행하면, Handler_read_key 값은 1이고 Handler_read_next 값은 1,000,000입니다. 즉, 인덱스를 1번 액세스한 후에 다음 킷값을 액세스하기 위한 next 콜을 100만 번 수행한 것입니다. 즉, Index Full Scan을 한 것을 의미합니다.

1
2
3
4
5
6
7
explain
select c1, max(c3) from t1 group by c1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | t1_idx01      | t1_idx01 | 15      | NULL | 991230 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+

수행 시간: 10 rows in set (0.26 sec)

flush status;
select c1, max(c3) from t1 group by c1;
show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1       |
| Handler_read_last     | 0       |
| Handler_read_next     | 1000000 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 0       |
+-----------------------+---------+

 

위의 쿼리를 Loose Index Scan 방식으로 유도하려면? 


 

위의 쿼리를 Loose Index Scan 방식으로 유도하기 위해서 c1+c3으로 구성된 결합 인덱스를 생성하는 것은 매우 비효율적이고 비싼 방식입니다. 이런 경우에는 다음과 같이 약간의 쿼리 변경을 통해서 Loose Index Scan 방식을 이용한 튜닝을 수행할 수 있습니다. 즉, c1, c2 칼럼으로 group by를 수행함으로써 Loose Index Scan 방식으로 유도한 후에, 다시 c1 칼럼으로 group by를 수행하면 됩니다. (때에 따라 효과적인 튜닝 방법이 될 수도 있을 것 같습니다)
수행 시간은 이전보다 월등히 빨라집니다. (이전 수행 시간: 0.26초, 튜닝 후: 0.01초 미만)

1
2
3
4
5
6
7
8
explain
select c1, max(c3) from (select c1, c2, max(c3) c3 from t1 group by c1, c2) a group by c1;
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |   96 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | t1         | NULL       | range | t1_idx01      | t1_idx01 | 10      | NULL |   96 |   100.00 | Using index for group-by        |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---------------------------------+

수행 시간: 10 rows in set (0.00 sec)

flush status;
select c1, max(c3) from (select c1, c2, max(c3) c3 from t1 group by c1, c2) a group by c1;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 302   |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 10    |
| Handler_read_rnd_next | 112   |
+-----------------------+-------+

 

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

MySQL - IN 절과 EXISTS 절 간의 처리 방식 비교

MySQL 2017.05.10 15:40 Posted by 시연아카데미

이번 시간에 다룰 주제는 MySQL의 IN 절과 EXISTS 절 처리 방식입니다.

 

IN 절과 EXISTS 절은 논리적으로 동일합니다. 즉, IN 절을 사용하던 EXISTS 절을 사용하든 간에 쿼리 수행 결과는 같습니다. 이 같은 속성 때문에 ORACLE과 PostgreSQL은 이 2개의 실행 계획이 같습니다. 즉, 개발자가 어떤 형태로 쿼리를 작성하더라도 구문의 차이 때문에 다른 실행 계획을 수립하지는 않습니다. MySQL을 연구하기 전까지는 이것을 너무나 당연하게 생각했었는데요. MySQL은 좀 다릅니다.

 

MySQL은 EXISTS 절을 처리하는 방식과 IN 절을 처리하는 방식 간의 차이가 존재합니다. 제가 테스트한 버전은 5.7인데요. 예전 문서를 보면 가급적 IN 절을 사용하지 말라는 글도 있습니다. 5.x 초기 버전에서는 IN 절에 대한 최적화가 미흡했던 것 같습니다. 하지만 정확한 버전은 모르겠지만 제가 테스트한 5.6, 5.7 버전에서는 IN 절에 대한 최적화 기법이 많이 적용된 것을 확인할 수 있었습니다.

자 그럼 테스트를 통해 살펴보겠습니다. 참고로, Handler 관련 통계 정보 확인을 위해서 매 테스트 전에 항상 flush status 명령어를 수행해서 해당 수치를 초기화했고, 아래의 결과는 5.7에서 수행한 결과입니다.

 

테스트 데이터 생성

drop table t1;
drop table t2;

create table t1 (c1 integer primary key, c2 integer, dummy char(100));
create table t2 (c1 integer, c2 integer primary key, dummy char(100));

set max_heap_table_size=16777216*10;

CALL generate_series(1,1000000);

-- t1 테이블에는 100 건을 입력하고, t2 테이블에는 100 만건을 입력합니다. 

insert into t1 select series, series, 'dummy' from series_tmp order by series limit 100;
insert into t2 select mod(series,100), series, 'dummy' from series_tmp;

 

 

 

메인 쿼리 집합이 매우 작고 서브 쿼리 칼럼에 인덱스가 없는 경우

 

EXISTS 절은 항상 DEPENDENT SUBQUERY 방식으로 수행됩니다. 따라서 이 같은 경우는 EXISTS 절이 유리합니다. (물론 서브 쿼리 칼럼에 적절한 인덱스가 없는 것은 설계 오류이므로, 설명을 위한 예제일 뿐이라고 이해해주시면 좋을 것 같습니다)

 

DEPENDENT SUBQUERY 방식은 Main 쿼리 집합 건수만큼 반복적으로 서브 쿼리 테이블을 액세스하는 방식입니다. 이때 T1 테이블에서 C2=10 조건을 만족하는 레코드가 1건 뿐이므로 T2 테이블을 1번만 액세스하면 됩니다. 즉, T2 테이블이 크긴 하지만 1번만 Full Scan을 하면 될 뿐더러, 세미 조인의 특성 상 매칭 되는 레코드를 1건만 찾으면 더 이상 스캔을 수행하지 않으므로, 만일 매칭 되는 레코드가 앞 부분에 위치한다면 스캔의 부하도 크지 않습니다.

Handler_read_rnd_next 수치는 Full Scan 시에 Scan Call을 요청한 횟수를 의미합니다. 현재 수치가 111이며 이 수치는 다음과 같이 해석할 수 있습니다.

  1. T1 테이블을 Full Scan 할때 101번 Call 수행 (100건 + 1회)
  2. T2 테이블에서 매칭되는 레코드를 찾기 위해서 10회 Call 수행. 즉, 앞에서 10 번째에 위치한 레코드를 찾음

-- EXISTS 절 사용

explain
select * from t1 where c2=10 and exists (select 1 from t2 where t2.c1=t1.c1);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 991672 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 111   |
+-----------------------+-------+

 

이때, IN 절을 사용하면 MATERIALIZED 방식을 사용합니다. 이때도 DEPENDENT SUBQUERY 방식을 사용하면 좋지만 옵티마이저는 MATERIALIZED 방식이 더 유리하다고 판단한 것 같습니다. (결과적으로는 더 느립니다) MATERIALIZED 방식을 사용하므로 100 만 건이나 되는 T2 테이블을 Scan 한 후에 내부적으로 인덱스를 생성하는 작업을 수행합니다. 이로 인해 수행 속도도 EXISTS 절에 비해서 매우 느립니다. Handler_read_rnd_next 수치를 보면 T2 테이블 레코드 건수인 100만 건+1회만큼 Scan Call을 수행했고, T1 테이블 레코드 건수인 100건+1회만큼 Scan Call을 수행한 것을 알 수 있습니다.

 


-- IN 절 사용

explain
select * from t1 where c2=10 and c1 in (select c1 from t2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref         | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL        |    100 |    10.00 | Using where |
|  1 | SIMPLE       |  | NULL       | eq_ref |     |  | 5       | mysql.t1.c1 |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL        | 991672 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+

수행 결과: 1 row in set (0.48 sec)

show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 2       |
| Handler_read_key      | 3       |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1000102 |
+-----------------------+---------+

 

 

메인 쿼리 집합이 매우 작고 서브 쿼리 칼럼에 인덱스가 있는 경우

 

서브 쿼리 칼럼에 인덱스를 생성한 후에 동일한 테스트를 수행하면 EXISTS와 IN 절의 성능은 동일합니다. 아래 2개의 결과를 보면 수행 시간은 모두 0.01초 미만이며, 동일한 IO 일량을 나타냅니다. 이때, IN 절의 Explain 결과를 보면 FirstMatch라고 표시됩니다. FirstMatch는 IN 절을 수행하는 옵티마이저의 전략 중의 하나인데요. 이름 그대로 첫 번째 매칭되는 레코드를 찾으면 더 이상 조인을 수행하지 않겠다는 것입니다. 어찌보면 세미 조인의 원리라고도 할 수 있겠네요. 그리고 이 방식은 DEPENDENT SUBQUERY와 동일합니다. 처리 방식이 동일하니 일량도 동일합니다.  

 

 

-- 인덱스 생성

create index t2_c1_idx on t2(c1);

 

-- EXISTS 절 사용

explain
select * from t1 where c2=10 and exists (select 1 from t2 where t2.c1=t1.c1);
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key       | key_len | ref         | rows  | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL      | NULL    | NULL        |   100 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 10438 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

-- IN 절 사용

explain
select * from t1 where c2=10 and c1 in (select c1 from t2);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL        |   100 |    10.00 | Using where                 |
|  1 | SIMPLE      | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 10438 |   100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

 

메인 쿼리 집합이 매우 큰 경우

 

테스트 결과 간의 비교를 쉽게 하기 위해서 약간 과장된 예제를 들어보겠습니다. 아래의 예제처럼 메인 쿼리 테이블은 매우 크고, 메인 쿼리에는 필터 조건이 없고, 서브 쿼리 내에는 매우 좋은 필터 조건이 있는 경우를 가정해보겠습니다.


이때 EXISTS 절을 사용하면 매우 비효율적으로 쿼리가 수행됩니다. EXISTS 절은 DEPENDENT SUBQUERY 방식으로만 수행되므로 100만 번 서브 쿼리를 수행합니다.


반면 아래의 IN 절의 Explain 결과를 보면 서브 쿼리가 아닌 조인으로 수행됐고 이때 조인 순서는 T1->T2 인 것을 알 수 있습니다. 이 또한 IN 서브 쿼리 최적화 기법의 하나입니다. 즉, T1 테이블에서 C2=10 조건에 만족하는 1건을 추출한 후에 T2 테이블과 조인을 수행한 것입니다. 이때 t2_c1_idx 인덱스를 이용해서 T2 테이블을 10,000번 액세스하게 됩니다. (Handler_read_next=10000)

 

-- EXISTS 절 사용

explain 
select * from t2 where exists (select 1 from t1 where t1.c1=t2.c1 and c2=10);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref         | rows   | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
|  1 | PRIMARY            | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        | 991672 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.t2.c1 |      1 |    10.00 | Using where |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+

수행 결과: 10000 rows in set (2.18 sec)

mysql> show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1000001 |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1000001 |
+-----------------------+---------+

 

 

-- IN 절 사용

explain 
select * from t2 where c1 in  (select c1 from t1 where c2=10);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL        |  100 |    10.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 9818 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+

수행 결과: 10000 rows in set (0.08 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 10000 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

 

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

MySQL은 Complex View (aggregate가 존재하는 derived table)에 대한 View Merging을 지원하지 않을 뿐만 아니라 JPPD (Join Predicate Push-Down)도 지원하지 않습니다. 게다가 Lateral Inline View도 제공하지 않습니다. (Lateral Inline View는 여기를 참조하세요)


이같은 한계로 인해 "과연 MySQL이 제대로 된 성능을 내고 있을까?" 하는 의문이 들기도 합니다. (아니면 제가 모르는 다른 묘안이 있을까요?)


ORACLE은 Complex View에 대한 View Merging 또는 JPPD의 지원 범위를 꾸준히 늘리고 있습니다. 이제 대부분의 Complex View (Union을 포함한)도 JPPD가 가능할 정도입니다. 이뿐만 아니라 12c 버전에서는 Lateral Inline View를 사용자에게 제공함으로써 사용자가 직접 JPPD를 구현할 수도 있습니다.


PostgreSQL은 아직까지는 제한적인 View Merging과 JPPD를 지원하지만, Lateral Inline View를 제공함으로써 이러한 약점을 극복하고 있습니다.


그런데 MySQL은 현재까지 제가 조사한 바로는 Complex View에 대한 어떠한 튜닝 방안도 제시하고 있지 않습니다. 그나마 여기를 보면 Function 리턴 값을 이용해서 Complex View 내로 특정 상숫값을 푸시하는 기법을 제시하고 있지만, 이 방법은 매우 제한적인 방법입니다. 왜냐면 이 방법은 특정 상수값을 View로 푸시할 수 있는 경우에만 유효한 방법이기 때문입니다. 만일 외부 상수 조건을 푸시하는 것이 아니라 조인 조건을 푸시하는 경우에는 사용할 수 없습니다.


전통적인 튜닝 원칙 중의 하나는 "조인 횟수를 줄이는 것이 좋다"는 것입니다. 이를 위해 group by를 먼저 수행한 후에 조인을 수행하는 방식을 사용하곤 했습니다. 그런데 이 원칙을 MySQL에 적용하면 성능상의 문제가 발생하게 됩니다. 예를 들어 살펴보겠습니다.


테스트 환경 구성


drop table dept;
drop table emp;
 
create table dept (deptno integer primary key, dname char(100));
create table emp  (empno integer primary key, ename char(100), deptno integer, salary double);   


generate_series 함수 생성 방법은 여기를 참고하세요. 

CALL generate_series(1,1000000);
 
insert into dept select series, concat("dname_", convert(series,char)) from series_tmp order by series limit 10000;
insert into emp  select series, concat("ename_", convert(series,char)), mod(series,10000), rand()*100000 from series_tmp order by series limit 1000000;
 
analyze table dept;
analyze table emp;


emp 테이블의 dept 칼럼에 인덱스를 생성하지 않고 테스트 수행



아래는 부서별 급여을 추출하는 쿼리입니다. case-1과 같이 쿼리를 작성하면 조인 횟수가 불필요하게 많아지므로 "가능한 조인 횟수를 줄이는 것이 좋다"라는 튜닝 원칙에는 위배됩니다. 따라서 Case-2와 같이 Group by 후에 조인을 수행하는 것이 일반적인 방식이었습니다. 물론 아래와 같이 필터 조건이 없는 경우에는 MySQL도 동일한 원칙이 적용됩니다. Case-1의 수행 속도는 4.2초이고 Case-2의 수행 속도는 1.17초이므로 Case-2가 2배 이상 빠릅니다.


Case-1) 조인 후에 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
group by d.dname;

+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | 991230 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.e.deptno |      1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
9999 rows in set (4.20 sec)


Case-2) Group by 후에 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno;
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref            | rows   | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL           |   9749 |   100.00 | NULL                            |
|  1 | PRIMARY     |  | NULL       | ref  |    |  | 5       | mysql.d.deptno |    101 |   100.00 | NULL                            |
|  2 | DERIVED     | emp        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL           | 991230 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
9999 rows in set (1.17 sec)


아래와 같이 필터 조건이 추가된 경우에도 이전의 예제와 동일하게 Group by 후에 조인하는 패턴이 더 빠른 수행 속도를 나타내고 있습니다. 여기까지는 다른 DBMS와 동일합니다. 그런데 인덱스를 생성한 후에는 전혀 다른 결과를 보입니다.


Case-3) 필터 조건 추가 -조인 후 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990'
group by d.dname;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | 991230 |   100.00 | Using where |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.e.deptno |      1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
1 row in set (3.70 sec)


Case-4) 필터 조건 추가 - Group by 후 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref            | rows   | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL           |   9749 |    10.00 | Using where                     |
|  1 | PRIMARY     |  | NULL       | ref  |    |  | 5       | mysql.d.deptno |    101 |   100.00 | NULL                            |
|  2 | DERIVED     | emp        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL           | 991230 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
1 row in set (0.75 sec)


emp 테이블의 dept 칼럼에 인덱스를 생성한 후에 테스트 수행


-- 인덱스 생성 

create index emp_deptno_idx on emp (deptno);


인덱스 생성 후에 Case-1 쿼리를 수행하면 기존보다 2배 이상 느려집니다. 그 이유는 조인 순서가 dept->emp로 변경되었고 이로 인해 랜덤 IO가 더 많아졌기 때문입니다. 속도를 향상시키기 위해 생성한 인덱스의 부작용이 발생한 셈이죠. 더군다나 Case-2 쿼리는 기존 대비 7배 이상 느려졌습니다. 기존 방식은 emp 테이블을 full scan하면서 temp table을 생성했지만, 인덱스를 생성함에 따라 emp_deptno_idx 인덱스를 이용해서 건건이 테이블을 액세스하는 부하가 가중되었기 때문입니다. 이 또한, 인덱스 생성에 의한 부작용입니다.


Case-1) 조인 후에 Group By

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
group by d.dname;
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref            | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY        | NULL           | NULL    | NULL           | 9749 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | e     | NULL       | ref  | emp_deptno_idx | emp_deptno_idx | 5       | mysql.d.deptno |  100 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
9999 rows in set (8.44 sec)


Case-2)  Group By 후에 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno;
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref            | rows   | filtered | Extra |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
|  1 | PRIMARY     | d          | NULL       | ALL   | PRIMARY        | NULL           | NULL    | NULL           |   9749 |   100.00 | NULL  |
|  1 | PRIMARY     |  | NULL       | ref   |     |     | 5       | mysql.d.deptno |    101 |   100.00 | NULL  |
|  2 | DERIVED     | emp        | NULL       | index | emp_deptno_idx | emp_deptno_idx | 5       | NULL           | 991230 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
9999 rows in set (8.17 sec)



인덱스 생성 후에 Case-3과 Case-4의 결과를 보면, 인덱스 생성 전과 비교해서 확연한 차이를 보입니다. Case-3과 같이 조인을 수행한 후에 group by를 수행하는 경우는 매우 빠른 수행 속도를 보입니다. 그런데 Case-4와 같이 Complex View가 있는 경우는 기존보다 훨씬 더 느립니다.


Case-3) 필터 조건 추가 -조인 후 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990'
group by d.dname;
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY        | NULL           | NULL    | NULL           | 9749 |    10.00 | Using where |
|  1 | SIMPLE      | e     | NULL       | ref  | emp_deptno_idx | emp_deptno_idx | 5       | mysql.d.deptno |  100 |   100.00 | NULL        |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
1 row in set (0.05 sec)


Case-4) 필터 조건 추가 - Group by 후 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref            | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
|  1 | PRIMARY     | d          | NULL       | ALL   | PRIMARY        | NULL           | NULL    | NULL           |   9749 |    10.00 | Using where |
|  1 | PRIMARY     |  | NULL       | ref   |     |     | 5       | mysql.d.deptno |    101 |   100.00 | NULL        |
|  2 | DERIVED     | emp        | NULL       | index | emp_deptno_idx | emp_deptno_idx | 5       | NULL           | 991230 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
1 row in set (8.39 sec)


정리해보면, 필터 조건이 없이 전체 범위를 처리하는 경우에는 인덱스 없이 (또는 IGNORE INDEX 힌트 이용) Complex View 형태로 처리하는 것이 유리합니다. 하지만 필터 조건이 있다면, 일반 조인 형태로 처리한 후에 Group by를 수행하는 것이 가장 빠릅니다. (물론 이때 조인 칼럼에 인덱스가 있어야 하겠지요)


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

MySQL에서 unnamed derived table 사용 시의 주의 사항

MySQL 2017.05.02 00:52 Posted by 시연아카데미

MySQL 서버팀에서 제공하는 블로그 내용을 보면 5.7부터 unnamed derived table을 처리하는 방식이 달라졌다는 내용이 있습니다. (여기 참조)
DBMS마다 용어가 달라서 조금 헷갈린대요. unnamed derived table은 ORACLE에서 말하는 in-line view, 즉, FROM 절에 나타나는 SELECT 문장을 의미합니다. 그리고 named derived table은 View입니다. 이 2가지를 통칭해서 derived table이라고 합니다. 그리고 논의의 편의를 위해서 aggregate가 없는 것을 simple한 derived table 이라고 하고, aggregate가 있는 것을 complex한 derived table이라고 하겠습니다.


MySQL의 derived table 처리방식과 타 DBMS의 비교



오라클과 PostgreSQL은 In-line view와 view의 처리 방식이 완벽히 동일합니다. 어찌 보면 사용자 입장에서는 이 방식이 직관적입니다. 그런데 MySQL은 이와 다릅니다. 5.6까지는 simple한 unnamed derived table도 View Merging이 불가능합니다. 즉, 실수로 잘못 코딩한 SQL 때문에 성능이 저하되는 문제가 존재합니다. 5.7부터는 이 문제가 해결되었습니다. 사실 5.6의 동작 방식을 문제라고 해야 할지, 아니면 개발자의 계획된 설계라고 해야 할지는 잘 모르겠습니다. 오히려 이러한 동작 방식을 이용해서 튜닝할 수도 있을 테니까요.


하지만 정확한 동작 원리를 모른 체 작성한 SQL 때문에 생각지도 못한 성능 저하가 발생했다면 이것은 개선해야 할 사항이기도 했을 것 같습니다. 여하튼 5.7에서는 simple 한 unnamed derived table에 대해서는 View Merging 기능을 제공합니다.


테스트 환경 구성


drop table dept;
drop table emp;

create table dept (deptno integer primary key, dname char(100));
create table emp  (empno integer primary key, ename char(100), deptno integer, salary double);   
generate_series 함수 생성 방법은 여기를 참고하세요.
CALL generate_series(1,1000000);

insert into dept select series, concat("dname_", convert(series,char)) from series_tmp order by series limit 10000;
insert into emp  select series, concat("ename_", convert(series,char)), mod(series,10000), rand()*100000 from series_tmp order by series limit 1000000;

create index emp_deptno_idx on emp (deptno); 

analyze table dept;
analyze table emp;



수행 방식 테스트 (5.6, 5.7 버전)



1) 일반 조인


일반 조인은 5.6, 5.7 모두 매우 빠르게 수행됩니다.

explain
select d.dname, d.deptno, e.empno, e.ename
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';

+----+-------------+-------+-----+----------------+----------------+------+-------------+
| id | select_type | table |type | key            | ref            | rows | Extra       |
+----+-------------+-------+-----+----------------+----------------+------+-------------+
|  1 | SIMPLE      | d     |ALL  | NULL           | NULL           | 9749 | Using where |
|  1 | SIMPLE      | e     |ref  | emp_deptno_idx | mysql.d.deptno |  100 | NULL        |
+----+-------------+-------+-----+----------------+----------------+------+-------------+
100 rows in set (0.01 sec)



2) unnamed derived table 사용 시


5.6 버전까지는 unnamed derived table은 항상 temp 테이블을 생성했습니다. 따라서 emp 테이블 전체를 temp 테이블로 생성한 후에 조인을 수행합니다. 이때, temp 테이블에는 index가 없는 셈이므로 내부적으로 auto_key0이라고 하는 temp 테이블용 인덱스를 생성합니다.


물론, 이 역시 부하가 발생하는 작업이지만 dept 테이블 매 건마다 temp 테이블을 full scan 하는 부하보다는 훨씬 더 적은 부하이므로, temp 테이블에 대한 인덱스를 생성하는 작업을 수행하는 것입니다. 이때 Explain의 type 칼럼은 "ref"라고 표시됩니다. 따라서 5.6까지는 unnamed derived table을 사용할 때 이러한 특성을 인지해야만 합니다.


하지만 5.7부터는 simple한 unnamed derived table에 대해서는 View Merging이 가능합니다. 아래의 Explain 결과를 보면 Explain 결과가 2줄로 변경됐고, DERIVED 오퍼레이션이 사라진 것을 알 수 있습니다. 다시말해, 일반 조인과 동일한 방식으로 수행된 것입니다.


explain
select d.dname, d.deptno, e.empno, e.ename
from   dept d, (select empno, ename, deptno from emp) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';

-- 5.6 버전 Explain 결과와 수행 시간 


+----+-------------+------------+------+-------------+----------------+--------+-------------+
| id | select_type | table      | type | key         | ref            | rows   | Extra       |
+----+-------------+------------+------+-------------+----------------+--------+-------------+
|  1 | PRIMARY     | d          | ALL  | NULL        | NULL           |   9749 | Using where |
|  1 | PRIMARY     |  | ref  |  | mysql.d.deptno |     99 | NULL        |
|  2 | DERIVED     | emp        | ALL  | NULL        | NULL           | 966226 | NULL        | 
+----+-------------+------------+------+-------------+----------------+--------+-------------+
100 rows in set (7.29 sec)

-- 5.7 버전 Explain 결과와 수행 시간 

+----+-------------+-------+-----+----------------+---------------+------+------------+
| id | select_type | table |type | key            |ref            | rows |Extra       |
+----+-------------+-------+-----+----------------+---------------+------+------------+
|  1 | SIMPLE      | d     |ALL  | NULL           |NULL           | 9749 |Using where |
|  1 | SIMPLE      | emp   |ref  | emp_deptno_idx |mysql.d.deptno |  100 |NULL        |
+----+-------------+-------+-----+----------------+---------------+------+------------+
100 rows in set (0.01 sec)


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

한 서버에 MySQL 5.6 버전과 5.7 버전을 설치하는 방법

MySQL 2017.04.28 22:12 Posted by 시연아카데미

5.7을 이용해서 MySQL 연구를 하던 중에 5.7의 옵티마이저 동작 원리가 5.6과 달라진 부분이 많아서 5.6을 설치할 필요가 생겼습니다. 그런데 한 서버에 MySQL 5.6과 5.7 버전을 설치하는 것이 약간 까다롭네요.


PostgreSQL은 RPM을 이용해서 설치하더라도 설치 디렉토리, 접속 포트 등을 지정할 수 있었는데, MySQL은 RPM을 이용해서는 지정된 디렉토리 (/var/lib/mysql)에만 설치가 가능한 것 같습니다.


일부 문서를 보면 --relocate 옵션을 이용해서 디렉토리 지정이 가능하다고 되어있는데, 5.6, 5.7 버전은 relocate가 불가능합니다.


그래서 이참에 소스 컴파일로 두 개의 버전을 설치해보기로 했습니다. 그런데 이 또한 사전 설치 작업, 라이브러리 설치, socket 지정 등의 작업으로 인한 시행착오가 일부 있었습니다. 따라서 관련 내용을 공유하고자 포스팅합니다.


사전 설치 작업



소스 컴파일을 위해선 cmake와 Bison을 설치해야 합니다. 그리고 5.7은  Boost 라이브러리를 추가로 설치해야 합니다.


1) cmake 설치


다운로드 사이트: https://cmake.org/
다운로드 파일: cmake-3.8.0-Linux-x86_64.tar.gz 


-- 설치 단계


배포용 파일이므로 컴파일은 필요없고 디렉토리에 압축만 풀면됩니다.


# gunzip cmake-3.8.0-Linux-x86_64.tar.gz
# tar xvf cmake-3.8.0-Linux-x86_64.tar
# cp -rp cmake-3.8.0-Linux-x86_64 /usr/local/cmake


-- .bash_profile PATH를 수정합니다.


PATH=$PATH:$HOME/bin:/usr/local/cmake/bin


# . .bash_profile


2) Bison 설치


다운로드 사이트: http://www.gnu.org/software/bison/
다운로드 파일: bison-3.0.tar.gz


-- 설치 단계 


# gunzip bison-3.0.tar.gz
# tar xvf bison-3.0.tar
# cd bison-3.0
# ./configure
# make
# make install


3) Boost 라이브러리 설치 (참고사이트: http://dslee1.blogspot.kr/2016/04/mysql-5711-cmake-error-at.html)


-- 설치 단계


# cd /usr/local/src/
# wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
# tar xvfz boost_1_59_0.tar.gz


4) 유저 및 그룹 생성


# groupadd mysql56
# useradd -r -g mysql56 -s /bin/false mysql56

# groupadd mysql57
# useradd -r -g mysql57 -s /bin/false mysql57


5) 소스 다운로드


https://github.com/mysql/mysql-server/tree/5.6
https://github.com/mysql/mysql-server/tree/5.7


MySQL 5.6 설치



설치 단계는 다음과 같습니다.


1) 설치 단계


# unzip mysql-server-5.6.zip
# cd mysql-server-5.6
# mkdir bld
# cd bld
# cmake ..


-- CmakeCache.txt 파일을 열어서 설치 디렉토리 위치를 변경합니다.


CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql56
MYSQL_DATADIR:PATH=/usr/local/mysql56/data
SYSCONFDIR:PATH=/usr/local/mysql56/etc


-- 내용을 수정한 후에 make 및 make install을 수행합니다.


# make
# make install


2) 후속 작업


# chown -R mysql56:mysql56 /usr/local/mysql56
# /usr/local/mysql56/scripts/mysql_install_db --user=mysql56


3) 비밀번호 변경 (참고사이트: http://egloos.zum.com/spongebody/v/3530932)


5.6을 소스로 설치할 때 root 비번을 잘 모르는 경우가 있습니다. 이때는 다음과 같이 조치합니다.


# /usr/local/mysql56/bin/mysqld_safe --user=mysql56 --skip-grant-tables &
# mysql -u root mysql
mysql> update user set password=password('rootpassword') where user = 'root';
mysql> flush privileges;


# killall mysqld
# /usr/local/mysql56/bin/mysqld_safe --user=mysql56 &


4) /usr/local/mysql56/my.cnf 파일 편집


저 같은 경우에는 5.6은 기본 포트인 3306으로, socket은 /tmp/mysql56.sock 으로 설정했습니다. 따라서 /usr/local/mysql56/my.cnf 파일을 다음과 같이 편집합니다.


[mysqld]
port = 3306
socket=/tmp/mysql56.sock
이하 생략


5) 접속 방법


소켓을 별도로 지정했으므로 -S 옵션을 이용해서 해당 소켓을 지정합니다.

# mysql -uroot -p -S /tmp/mysql56.sock


MySQL 5.7 설치



설치 단계는 다음과 같습니다.


1) 설치 단계


# unzip mysql-server-5.7.zip
# cd mysql-server-5.7
# mkdir bld
# cd bld
# cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/src/boost_1_59_0


-- CmakeCache.txt 파일을 열어서 설치 디렉토리 위치를 변경합니다.


CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql57
MYSQL_DATADIR:PATH=/usr/local/mysql57/data
MYSQL_KEYRINGDIR:PATH=/usr/local/mysql57/keyring
SYSCONFDIR:PATH=/usr/local/mysql57/etc


-- 내용을 수정한 후에 make 및 make install을 수행합니다.


# make
# make install


2) 후속 작업


아래 작업 시에 화면에 root 임시 비번이 출력됩니다. 해당 비번을 복사해둡니다.


chown -R mysql57:mysql57 /usr/local/mysql57
/usr/local/mysql57/bin/mysqld --initialize --user=mysql57
/usr/local/mysql57/bin/mysql_ssl_rsa_setup


3) /usr/local/mysql57/my.cnf 파일 편집 


[mysqld]
socket=/tmp/mysql57.sock
port=3307
이하 생략


4) MySQL 시작
 
# /usr/local/mysql57/bin/mysqld_safe --user=mysql57 &


5) 비밀번호 변경


# mysql -uroot -ptcDqMfDh:7h? -S /tmp/mysql57.sock
mysql> alter user 'root'@'localhost' identified by 'rootpassword';
mysql> flush privileges;


6) 접속


소켓을 별도로 지정했으므로 -S 옵션을 이용해서 해당 소켓을 지정합니다.


# mysql -uroot -p -S /tmp/mysql57.sock


이로써 한 서버에 2개의 MySQL 설치가 완료됐습니다.


저는 서버에서 테스트용으로 2개 버전을 설치한 것이라서 mysql 접속만 하면 되는데요.

만일 PHP를 이용하는 경우에는 기본적으로는 /tmp/mysql.sock을 이용하므로 socket 명을 변경한 경우에는 php.ini를 수정하는 등의 작업이 필요할 것 같습니다.


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


 

티스토리 툴바