LOAD DATA로 대용량 데이터 빠르게 삽입하기

작년 말에 K-DEVCON 스터디에서 MySQL을 공부하면서 LOAD DATA 명령어에 대해서 알게 되었다.

• INSERT, UPDATE, DELETE 쿼리 작성 및 최적화 - Real MySQL 스터디 7회차

하지만 그 이후로 특별히 사용할 일이 없었는데, 이번에 400만 개의 row 가 쌓인 환경에서 쿼리 성능을 측정해야 할 일이 있어서 LOAD DATA 를 실제로 사용해볼 수 있게 되었다.

LOAD DATA 란?

LOAD DATA 명령어는 텍스트 파일로부터 데이터를 읽어와 테이블에 매우 빠르게 삽입할 수 있다. Real MySQL에서는 그냥 insert 하는 것과 비교하면 약 20배의 성능차를 보여준다고 설명이 나와있다.

일반적인 INSERT나 BULK INSERT는 사용해 보았지만, 이번에는 테스트 해야하는 데이터가 워낙 많다보니 LOAD DATA 를 사용해보기로 하였다.

LOAD DATA 사용해보기

MySQL 세팅

Docker Desktop 을 사용하지 못하는 환경이라, Podman Desktop 을 사용하였다. Podman 은 이번에 처음 사용해 보았는데 Docker 와 Compatible 한 인터페이스를 제공하여, Docker 경험이 있다면 큰 어려움 없이 사용가능했다.

MySQL 은 실제 환경과 동일하게 진행하고자 8.0.32 버전으로 동일하게 설정하여 테스트를 진행하였다.

podman run -dit -e MYSQL_ROOT_PASSWORD=testtesttesttest -e MYSQL_DATABASE=test -p 3306:3306  --name local-mysql mysql:8.0.32

데이터 세팅

간단한 자바코드를 작성하여 파일로 csv 파일을 생성하도록 하였다. 컬럼은 csv에 담지 않았다. faker 를 이용하여 어느 정도 랜덤한 있는 데이터가 나올 수 있도록 하였다. 테스트 데이터도 최대한 실제와 유사하기 위해 암호화도 적용하여 데이터를 생성하게 하였다.

USER0000001,7426C09FB3...,Rob,Gerlach,47a7e9bd9...,251FE112...,10,\N,10,\N,\N,N,0,40,...
...

CSV 에서는 null 처리가 어려운데, LOAD DATA 는 csv 파일을 읽을 때 \N 을 null로 처리한다. 그냥 빈 공백으로 처리할 경우 삽입 처리중에 아래와 같은 에러가 발생될 수 있으니 주의하자.

[22001][1292] Data truncation: Incorrect ... value: '' for column 'column_name' at row xxx

Data 파일을 container 내부로 복사하기

다음과 같이 cp 명령어를 사용하여 데이터 파일을 container 내부로 복사할 수 있다.

podman cp /Users/jonghoonpark/project/slow-query-select-member-list/output.csv local-mysql:/var/lib/mysql-files/file.csv

LOAD DATA 를 이용하여 데이터 삽입

파일을 컨테이너 내부로 옮겼다면, 아래 명령어를 통해 데이터를 삽입할 수 있다. USER_TABLE 이라는 이름의 테이블에 데이터를 삽입한다.

LOAD DATA INFILE '/var/lib/mysql-files/file.csv'
INTO TABLE USER_TABLE
FIELDS TERMINATED BY ',' -- CSV 파일의 구분자 (쉼표인 경우)
ENCLOSED BY '"' -- 필드가 따옴표로 묶여 있는 경우
LINES TERMINATED BY '\n' -- 줄 바꿈 문자 (Unix/Linux 기준)
-- IGNORE 1 LINES; -- 헤더 있는 경우

중간 결과

우선 데이터 20만개를 생성하여 LOAD DATA를 실행하여보았다. 27초 만에 모든 데이터가 정상적으로 삽입이 되었다. 매우 만족스럽게 생각하고, 이번에는 400만개의 데이터를 생성하여 다시 LOAD DATA 를 실행해보았다.

20 배의 데이터를 추가하였으니, 20배 정도의 시간이 걸릴 것으로 생각하고 10분을 기다렸다. 하지만 10분이 지나도 완료되지 못했다. 심지어 1시간, 2시간이 지나도 완료가 되지 않았다.

트러블 슈팅 (Podman 과 Docker의 차이)

원인은 무엇이었을까?

Podman과 Docker는 macOS/Windows 환경에서 모두 리눅스 가상머신(VM)을 사용하지만 관리 방식에 약간의 차이가 있었다.

Podman은 사용자가 직접 VM의 리소스를 할당 해야하는 반면, Docker는 기본적으로 VM의 자동으로 관리한다.

Podman 을 처음 사용해보는 것이였기 때문에, 별 생각 없이 기본 세팅 그대로 하여 실행하였다. (기본 세팅은 vCPU 6, RAM 2G 였다.) 그리고 이 부분에서 문제가 된 것이다.

이 기본 세팅으로도 20만개의 데이터를 삽입하는데는 큰 문제가 되지 않았다. 하지만 400만개는 훨씬 큰 데이터였다.

400만개를 담은 csv 파일의 용량을 확인해보니, 2기가를 아슬하게 넘기고 있었다. 가상머신에 할당된 메모리가 2기가였으니, 감당하기 어려웠을 것이다.

가상머신의 스팩을 넉넉하게(vCPU 12, RAM 16G) 변경한 후 다시 LOAD DATA 를 시도하였다. 이번에는 400만개의 데이터도 정상적으로 삽입되었고, 약 5분 3초가 소요되었다.

Podman 이 아니더라도 대용량 데이터를 삽입해야 한다면 메모리가 충분한지 확인해 보는 것이 좋을 것 같고, 그렇지 않다면 파일을 분할해서 삽입을 진행하는 것이 좋을 것 같다.

실행 결과 (최종)

참고용으로 다시 한 번 소요된 시간을 정리해보자면 다음과 같았다.

마무리

스터디를 하며 배웠던 LOAD DATA 를 실제로 사용해보고 겪었던 일을 정리해보았다. MySQL에서 대용량 데이터를 빠르게 삽입할 때 매우 효과적인 방법임을 확인할 수 있었다. 대용량 데이터를 다룰 때에는 메모리에 대한 고려도 필요하다는 것도 배울 수 있었다.

이 글이 LOAD DATA 활용과 컨테이너 환경에서 발생할 수 있는 문제 해결에 도움이 되길 바란다.