My Books

My Slides

rss 아이콘 이미지

 

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

 

디버그 옵션을 끄고 다시 컴파일한 후에 테스트를 해보니, 아래의 테스트 케이스에서는 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


 

티스토리 툴바