My Books

My Slides

rss 아이콘 이미지

이번에 살펴볼 내용은 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 ,


 

티스토리 툴바