My Books

My Slides

rss 아이콘 이미지


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 ,


 

티스토리 툴바