Bulk Inserting - MySQL 다량의 데이터 넣기

* 공부하면서 정리하는 포스팅

튜플이 100,000개 미만이라면 엥간한 악조건이여도 쿼리가 금방금방 이루어진다.
(여기서 악조건 : DB가 올라가있는 하드웨어가 좋지 않거나, 쿼리가 거지같거나)

하지만 튜플이 1,000,000개 가량 되거나 그 이상이되면, 사람이 체감할 수 있을 정도로 쿼리에 시간이 걸린다.

Bulk Inserting

DB에 INSERT 해야하는 튜플이 많은 경우 사용되는 기법이다.
한 번에 다량에 데이터을 DB에 집어넣으려고 할 때 유용하다.

예를 들어, 기존의 쿼리가

1 2 3

INSERT INTO table VALUES (1, "hello"); INSERT INTO table VALUES (2, "world"); INSERT INTO table VALUES (3, "!");

cs

였다면,

1

INSERT INTO table VALUES (1, "hello"), (2, "world"), (3, "!");

cs

처럼 한 쿼리에 다량의 튜플을 묶어서 쿼리를 넣어주면 된다.

실제로 사용할 때에는, 한번에 수천개씩 묶어서 쿼리를 넣어주면 된다.

백만개 정도의 데이터를 한번 MySQL에 넣어보자.
정말 기하급수적으로 성능이 향상되는 모습을 볼 수 있다.

[성능이 향상되는 이유]

DB의 다양한 요소들에 의해, 쿼리 한번이 이루어지면 그 전후에 이루어지는 작업이 꽤나 있다. (Transaction, Index 등)

DB에 1,000,000개의 데이터를 INSERT 하는 상황을 생각해보자.
DB에 1개의 데이터를 넣을때 N, 쿼리 전후에 M이라는 값이 소모된다고 가정해보자.

만약 쿼리 하나에 1개의 튜플을 INSERT 하게 되면,
소모값은 (1,000,000 * N + 1,000,000 * M)이다.

하지만 쿼리 하나에 1,000개의 튜플을 INSERT 하게 되면,
소모값은 (1,000,000 * N + 1,000 * M)이다.

별거 아닌거 같아도 실제로 엄청난 속도차이를 나에게 보여줬다.

[MySQL 튜닝]

Bulk Inserting 중에는 최대한 DB가 다른 작업을 덜 하게 하는 것이 키 포인트이다.
MySQL의 B+ Tree 구조를 잘 생각하고, (뭐가 Clustered Index인지 잘 생각하고)
InnoDB인지 MyISAM인지를 잘 생각해야 한다.

- Auto Commit
mysql> SET autocommit=0;
Bulk Inserting......
mysql> commit;

INSERT가 끝나고 Transaction을 걸어주자~


- INDEX
테이벌에 걸려있는 INDEX가 많다면, Bulk Insert하면서 INDEX를 조정하는 것 보다 Bulk Insert가 끝나고 INDEX를 조정하는 것이 훨씬 빠르다.


- KEY / UNIQUE
Bulk Insert 되는 데이터가 믿을수 있는 데이터라면, 잠시 UNIQUE 검사 / FK 검사 등은 꺼두는 것이 좋다.

mysql> SET unique_checks=0;
Bulk Inserting......
mysql> SET unique_checks=1;

mysql> SET foreign_key_checks=0;
Bulk Inserting......
mysql> SET foreign_key_checks=1;


- 버퍼/로깅 등
MySQL의 잡다한 설정을 바꿔주는 것도 큰 도움이 된다고 한다.

/etc/my.cnf 에서

1 2 3 4 5 6 7 8

innodb_change_buffering=all innodb_change_buffer_max_size=25 # innodb_buffer_pool_instances=1 Change at your own discretion innodb_buffer_pool_size=3072M // RAM의 80% 가량으로 설정 innodb_log_file_size=384M //buffer_pool_size에 비례해서 조정 innodb_log_buffer_size=128M innodb_flush_log_at_trx_commit=1 skip-innodb_doublewrite Colored by Color Scripter

cs

(출처 : https://nbsoftsolutions.com/blog/optimizing-innodb-bulk-insert)

[샘플 테스트]

2 vCPU, 4GB RAM, SSD 기준 (AWS Lightsail에서 테스트)
CentOS 7 / MySQL 5.7 / Python3
* 데이터셋은 2개의 int와 1개의 varchar(250) column을 가지고 있음

실험1 - Bulk Inserting의 유무차이 (데이터셋 : 1,000,000개)

같은 1,000,000개의 튜플. 하지만 들어간 과정과 시간은 전혀 다르다 .

1. 기본 세팅 그대로, Bulk Inserting 없이 테스트 (t1 테이블)

2. 기본 세팅 그대로, Bulk Inserting (t2 테이블) - 1,000개씩 묶어서

Bulk Inserting을 했을 때 -> 7초 32
Bulk Inserting을 하지 않았을 때 -> 22분 4초 04
정말 큰 차이가 발생하는 것을 볼 수 있다.

튜플의 개수가 더 많아진다면, 그 차이는 말할 것도 없을 것이다.

실험2 - Bulk Inserting을 묶는 차이 (데이터셋 : 10,000,000개)

1. 1,000개로 묶었을 때

2. 10,000개로 묶었을 때

1,000개로 묶었을 때 -> 2분 5초 55
10,000개로 묶었을 때 -> 1분 50초 53

오늘의 의문

1,000개씩 bulk inserting한 t1테이블, 10,000개씩 bulk inserting한 t2테이블의 차이?

Schema와 들어있는 데이터는 똑같은데, SELECT 조회 시간 차이가 심하다.
explain을 봐도 똑같은거 같은데, 왜 저렇게 조회 시간이 차이가 있는지 모르겠다.

SELECT 조회를 여러번 날려봐도 똑같다. 왜그런건지 모르겠다.

짐작으로는, B+ Tree의 fan-out이랑 관련 된거 같다. 확실한지는 모르겠다.