MySQL InnoDB에서 Loop Insert 속도를 10배 이상 빠르게 하는 아주 간단한 방법

제목이 너무 자극적이죠?

MySQL 성능 테스트를 위해 Loop Insert 하는 Procedure를 생성했는데 처리 속도가 너무 느렸습니다. 100만 건 입력하는데 10분 이상 소요됐으니까요. (참고로 (MySQL은 anonymous procedure를 지원하지 않으므로 프로시저를 생성해야 합니다)

같은 유형의 프로시저를 PostgreSQL에서 수행하면 10초 이내에 끝나는 것에 비하면 극심한 성능 차이가 발생하는 것인데요. 이 문제를 처음 접했을 때는 MySQL의 InnoDB 엔진의 특성상 모든 테이블을 클러스터 인덱스로 생성하는 이유 때문인 것으로 추정했었습니다.

즉, Index Split 현상 떄문에 입력 시의 지연 현상이 발생한다고 생각했으나 결과적으로 이 문제는 아니었습니다. (물론, 이로 인한 약간의 성능 손실은 있습니다. 이 부분은 다음 포스팅에서 다룰 예정입니다)

문제의 원인은 MySQL이 Loop를 처리하는 방식 때문입니다. PostgreSQL은 프로시저 내의 Loop를 1-Transaction으로 처리합니다. 그런데 MySQL은 매건 COMMIT을 하는 방식으로 수행합니다.

따라서 이 문제를 해결하는 법은 간단합니다. Loop 수행 전에 SET TRANSACTION을 지정하거나 SET AUTOCOMMIT=OFF로 설정하면 됩니다. 간단하죠?

아래는 테스트를 위한 스크립트입니다.

— 테이블 및 프로시저 생성

drop table t1;
create table t1 (c1 integer, c2 integer, dummy char(100));

delimiter $$
drop procedure if exists insert_t1_proc$$
create procedure insert_t1_proc()
begin
   declare i int default 1;
   declare j int default 0;
   
   while (i <= 10000) do
       while (j <= 99) do
         insert into t1 values(i+(j*10000), i+(j*10000), 'dummy');
         set j=j+1;
       end while;
       set i=i+1;
       set j=0;
   end while;
end $$

delimiter ;

— 프로시저 수행 결과 (747초나 소요됨)

call insert_t1_proc();

Query OK, 1 row affected (12 min 27.08 sec) (747초)

— START TRANSACTION 적용 후의 수행 결과 (19초로 단축됨)

start transaction;

call insert_t1_proc(); 
Query OK, 1 row affected (19.20 sec)

commit;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s