MySQL 에서의 Bulk Inserting 성능 향상

MySQL은 조회(SELECT) 에 있어서 최고의 강점을 갖는 종류의 DB이지만, 경우에 따라 막대한 양의 Insert 를 수행해야할 때가 있다.

특히 API 에서 조회하는 DB에 배치 서버가 수시로 데이터를 갱신해주는 경우나, 막대한 양의 메시지 데이터를 처리하게 될 경우가 있는데, DB가 커질 수록, 단순 쿼리로만 작업하다가는 퍼포먼스에 있어 엄청난 디버프를 받게 된다.

대량의 데이터를 삽입하는 것을 Bulk Inserting 이라 하며, 이를 위한 다음과 같은 튜닝 기술들 정도는 숙지해두도록 하자.

(1) 여러 개의 Insert 구문 수행시 Values 리스트를 다중으로 사용하는 것이 성능을 향상시킬 수 있다. 

정말 효과 제대로 본 방법인데, 다수의 동일한 Insert query에 대해서 다음과 같이 최적화가 가능하다.

Insert into T values(a, b, c)

Insert into T values(d, e, f)

위와 같이 쿼리를 날린다고 할때, 이를 다음과 같이 바꾼다.

Insert into T values(a, b, c), (d, e, f) ...

이렇게 되면 성능이 기하급수적으로 향상되며, 이는 JDBC 혹은 클라이언트 등의 연결 설정에서 RewriteBatchedStatements=true 와 같은 속성을 주어 자동으로 최적화시킬 수 있다.

(물론 설정의 경우 실수의 우려도 있고, 개발자가 한눈에 파악하기 쉽지 않기 때문에 협업 시에는 코드 레벨에서 처리하는게 더 좋다.)

또한 이 쿼리 튜닝을 사용할 때에는 비어있지 않은 Table에 Insert 시 my.cnf 파일 내에 있는 bulk_insert_buffer_size를 변경하여 속도 개선이 가능하다. 

(수정 방법은 /etc/mysql/my.cnf 파일을 열어서 [mysqld] 항목 아래에 bulk_insert_buffer_size=256M 과 같이 설정해주면 된다.)

(2) 여러 클라이언트에서 Insert 시 Insert Delayed 를 통해 속도 개선이 가능하다. 이 구문을 이용하면 수행 응답이 큐에 적재되고 테이블이 사용되지 않을 때 삽입한다. 

실시간으로 서비스 중인 경우에 빛을 발하는 성능 향상 법이다.

(3) 파일 스트림으로부터 대량의 데이터를 삽입 시 Load Data Local Infile 구문을 이용해서 필드 구문자로 정리된 File을 MySQL DB로 Redirection 시킬 수 있다. 

각각에 대한 Insert 구문 수행보다 빠른 퍼포먼스를 보인다.

 (ex) Delimeter가 "|" 이고 라인 개행 단위로 레코드가 기록되어 있을 때,

LOAD DATA LOCAL INFILE '경로' [REPLACE | IGNORE] INTO TABLE 테이블명 FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

단, 이 경우 5.5 이상 버전에서는 LOCAL을 꼭 붙여야 에러가 나지 않으며, 이를 이용하기 위해 설정 옵션에 [mysql] 아래에 local-infile을, [mysqld] 아래에 local-infile을 추가해주어야 한다.

(4) Index가 많이 사용된 테이블에 대량으로 Insert 시 Index를 비활성화한 후 수행하는 것이 좋다. 

매 Row 마다 계산 후 삽입하는 것보다 전체 키를 비활성화하고 데이터 입력이 종료되고 다시 키를 활성화시키는 것이 빠른 수행을 할 수 있다.

ALTER TABLE 테이블 DISABLE KEYS;

Insert 구문 수행

ALTER TABLE 테이블 ENABLE KEYS;

여기서 Key 란 INDEX 뿐 아니라 FOREIGN KEY 참조도 포함한다. 실제로 Bulk Inserting 이 중요하다면, 체크를 꺼두는 것이 성능 향상에 큰 도움이 된다.

물론 이 경우 단점은, API 로직상 에러가 발생하여 문제가 될 수 있는 데이터가 삽입될 시, 실시간으로 정합성 체크가 불가능하다. 데이터를 신뢰할 경우에만 수행해야 한다.

(5) 트랜잭션을 지원하는 테이블의 경우, Start Transaction과 Commit을, 지원하지 않는 테이블의 경우 테이블 잠금을 실행하면 성능이 향상된다. 

(이유는 버퍼 플러시가 매번 수행되지 않고 작업이 끝난 후 수행되기 때문이다.)

(6) Buffer Size 의 조절을 통해 성능 향상이 가능하다.

테이블에 Insert 시 Index가 정렬되어 들어온다는 보장이 없기 때문에 이는 B트리 구조화시 추가적인 I/O를 수반한다. 

디스크에 데이터를 읽고 쓰고 하는 부가적인 동작이 레코드가 많아질수록, Buffer 크기를 넘어설수록 많이 수반되기 때문이다. 

이를 막기 위해서 InnoDB의 경우 Buffer Size를 크게 잡는다면 이 만큼의 메모리를 추가로 캐싱용 버퍼풀을 위해 사용하는 대신 디스크 I/O의 비용을 높은 비율로 줄일 수 있다. 

잔여 메모리의 50~80% 만큼 조절하는 게 정석이며 그 이상으로 조절 시 오히려 하드디스크를 가상 메모리로 쓰기 위한 스와핑 작업이 발생하기 때문에 성능이 저하된다고 알려져있다. 

(실제로 이부분은 범위 내라면, 조절하는 만큼 성능이 향상된다. 바꾸기 위해서는 my.cnf 설정 내에서 [mysql] 하위 항목으로 innodb_buffer_pool_size=1024M 과 같이 입력하면 된다.)

버퍼 풀 메모리가 충분히 큰 양으로 할당되어 있다면 innodb는 in-memory 데이터베이스처럼 동작한다. 

Access를 위한 select 데이터 뿐 아니라, Insert 및 Update 작업에도 도움이 되는 캐싱을 하기 때문에 적절하 조정하여 사용하는 것이 핵심이다.

버퍼 풀 메모리는 내부적으로 LRU 알고리즘을 사용하는 리스트의 형태로 자세한 내용은 http://dev.mysql.com 레퍼런스를 참조한다.