My Books

My Slides

rss 아이콘 이미지

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


 

티스토리 툴바