대량의 더미 데이터 DB에 주입하기

대량의 더미 데이터 DB에 주입하기

현재 만들고 있는 토이 프로젝트에서는 대량의 데이터가 존재하는 DB 를 대상으로 여러가지 테스트를 수행한다. 테스트를 수행하기 위해서 더미 데이터를 주입할 방법에 대해서 모색해야했다.

SQL Procedure

• SQL 문으로 직접 테이블에 데이터를 주입한다.
• 직접 Insert into () values (); 와 같은 구문을 작성할 수도 있지만, Procedure 혹은 function 을 직접 작성하여 해결하는 편이 낫다.
• Procedure 예시

  
DROP PROCEDURE IF EXISTS init_perfomances $$  
CREATE  PROCEDURE init_perfomances(  
    IN num_rows INT  
) BEGIN  
    DECLARE i INT DEFAULT 1;  
    DECLARE name VARCHAR(255);  
    DECLARE start_at TIMESTAMP;  
    DECLARE end_at TIMESTAMP;  
    DECLARE created_at TIMESTAMP;  
    DECLARE updated_at TIMESTAMP;  
--  loop num_rows times  
    WHILE i <= num_rows DO  
        SET name = CONCAT('name_', i);  
        SET created_at = FROM_UNIXTIME(UNIX_TIMESTAMP('2020-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP('2024-01-01') - UNIX_TIMESTAMP('2020-01-01'))));  
        SET updated_at = created_at;  
#         start at is a random time larger than created Time  
        SET start_at = FROM_UNIXTIME(UNIX_TIMESTAMP(created_at) +  
                                     FLOOR(RAND() * (UNIX_TIMESTAMP('2024-01-01') - UNIX_TIMESTAMP(created_at))));  
#         end at is a random time larger than start Time  
        SET end_at = FROM_UNIXTIME(UNIX_TIMESTAMP(start_at) +  
                                   FLOOR(RAND() * (UNIX_TIMESTAMP('2024-01-01') - UNIX_TIMESTAMP(start_at))));  
        INSERT INTO performances (performance_id, name, start_at, end_at, created_at, updated_at) VALUES (i, name, start_at, end_at, created_at, updated_at);  
        SET i = i + 1;  
    END WHILE;  
END $$

• Procedure 을 사용할 경우 8 min per 1,000,000 rows 정도 걸린다.
  • 이는 loop 로 100 만 건의 insert into 를 하기 때문에 더 느리다.
  • 단순 테스트에서는 bulk insert (100 rows split) 으로 할 경우 < 40s 정도 소요된다.
• 나름 간편하게 데이터를 생성하고 주입할 수 있다는 장점이 있다.
• 데이터 적재 속도로 따지면 후에 기술할 CSV 를 통한 LOAD DATA 에 비해서 많이 느린 편이다. 하지만, constraint 를 잘 지키면서 빠르게 작성하기에 용이하다는 장점이 있다.
• 다만 이 방법의 경우 `INSERT INTO `구문이 너무 많이 호출된다. BULK INSERT 를 이용하여 INSERT INTO 구문의 호출 횟수를 줄여 어느정도 속도 증가를 기대할 수 있다.
• Procedure 을 서버에서 실행하기만 하면 되므로, 별도로 데이터를 생성하고 옮기거나 하는 작업이 필요 없다는 것 또한 큰 장점이다.

Mock Generator

• 더미 데이터 생성기 와 같은 생성기를 활용하는 방법이다. 생성기의 경우 다양한 데이터 타입이나 종류에 대해서 의미있게 식별되는 다양한 데이터를 삽입한다. 실제와 같은 환경을 일부 모사하고 싶을 경우 해당 방법을 사용할 수 있다.
• csv 포멧으로 export 해두는게 좋다. 아래 기술될 LOAD DATA INFILE 을 활용하면 빠르게 데이터를 넣을 수 있다.
• 테스트 결과 웹을 통한 Dummy data 생성기는 대부분 1,000,000 건 이 초과할 경우 쓸 것이 못된다. 생성 시간 자체가 너무 오래걸리고 해당 서버에서 timeout 이 나는 것으로 보인다. 더 큰 데이터의 경우에는 유료 결제로 해결을 해야한다.
• 직접 Faker 라이브러리 등을 활용해서 CSV 파일을 만드는 것도 한 방법이다.
  • 주입할 schema 를 입력 / 필요한 format 을 정의하면 자동으로 주입하도록 한다면, 보다 쉽게 만들어낼 수 있다.
  • 다만, 이를 완전히 지원하는 라이브러리가 존재하는지는 아직 모르겠다. 웹 버전으로는 현재 존재하는데, docker 에 올려서 사용할 수 있도록 라이브러리를 만들어서 하나 배포하는 것도 괜찮아보인다.

LOAD DATA

• Mock Generator 와 같은 툴은 일반적으로 csv, sql 문 등으로 DB 에 삽입할 방법을 제공한다. 그 중에서, csv 를 활용해서 다음과 같이 load 할 경우 빠르게 데이터를 삽입할 수 있다.

mysql --local-infile=1 -u root -p
-- input password.
use test_db;
-- load data from csv
LOAD DATA LOCAL INFILE '/var/lib/mysql/output.csv' INTO TABLE USERS
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\n'
(@col1, @col2, @col3, @col4, @col5, @col6) set user_id=@col1,nickname=@col2,email=@col3,password=@col4,created_at=@col5,updated_at=@col6;

• procedure 의 경우 백 만 건에 총 8분 가량 소요된 반면 해당 쿼리로는 30초만에 적재가 완료되었다.
• 미리 데이터를 csv 로 생성할 수 있다면 해당 방법을 통하는 것이 가장 빠르다.
• MySQL - LOAD DATA

LOAD DATA 테스트

• 100 만건 주입 시에는 약 20초에 적재가 완료되었으나, 1,000 만건이 들어있는 csv 파일을 주입하는 경우에는 30분이 넘도록 주입에 실패했다.
• index 생성으로 인한 문제인지 혹은 다른 문제인지 확인하고자 테스트를 진행했다.
• 우선 아래 두 케이스를 비교해보자.

1. 10 M rows 를 한번에 주입
2. 500K 씩 나누어 20번, 총 10M row 주입

우선, 1번 10M 케이스는 > 30 min 이 소요되었으며 시간이 증가할수록 데이터가 주입되는 속도가 점점 느려져 테스트를 중단했다. 여기서 내 추측은 10 M 을 추가하는 과정에서 index 생성 오버헤드가 점점 증가하여, 데이터 주입이 느려지는 것으로 추측했다. (그러나, 현재 주입하는 데이터가 index 를 3개만 가지고 있는데 너무 큰 오버헤드가 아닌가? 하는 생각이 든다.)

2번 500K * 20 케이스에서는 놀랄만한 결과를 얻었다. 10 M 의 데이터를 주입하는데 총 160 sec 이 소요되었으며, 각 쿼리당 10 sec 미만이 소요되었다. 만약 인덱스 생성으로 인한 오버헤드가 존재했다면, record 의 증가와 비례하여 데이터 적재 시간이 증가해야한다. 그러나, 전혀 관련이 없는 모습이다. 10 M 까지는 인덱스의 영향과 관계없이 한번에 얼마만큼의 데이터를 주입하는가? 가 중요하다. 해당 구간에서는 record의 적재에 대해서 index의 영향은 크지 않다.

• 아래 stack overflow 의 질문에서는 나와 같은 고민을 이야기하고 있다.

Loading Lots of Data into MySQL too Slow

I’m running MySQL on my local computer. I’ve setup a database with multiple tables, 3 of which are potentially interesting here: CREATE TABLE rnames ( id INT AUTO_INCREMENT PRIMARY KEY, rname

Stack Overflowmimo31

• 질문자가 질문을 하게 된 데이터 적재량 - 속도의 연관은 아래 그래프와 같다.

• 내가 경험한대로 각 CSV 에 row 수가 증가할 수록 데이터 적재 속도가 비선형적으로 증가한다.
• 더 눈여겨볼 것은 그가 index / fk contraint 를 제거하고 적재한 상황이다.

• 3e6 케이스에서는 900s-> 60s 으로 95% 가량의 속도를 줄였다. index 가 실제로 데이터를 적재하는데 영향을 미치고 있다.
• 다만 이는 내가 2번에서 테스트한 상황과 다소 다르다. 작성자는 csv 파일 자체의 크기를 지속적으로 늘려간 반면, 나의 경우에는 record 의 개수와 연관이 있다.
• 결과적으로, 한번에 적재하는 양 은 인덱스 와 연관을 갖는것으로 보인다.
• 이는 Bulk insert 과정에서 두 가지의 문제를 짚어볼 수 있다.
  • transaction log
• LOAD DATA 의 경우 하나의 Transaction 으로 묶여 실행된다. 그런데, infile 의 data 가 증가할 수록 transaction 의 크기가 커지고 이에 따라 transaction log 또한 함께 증가한다.
  • load 과정에서 trasnaction log 관련한 warning 이 뜨기도 했다.
• index
  • index 를 갱신할 때, 한 번에 갱신하는 양이 많을 수록 B-Tree 의 rebalancing이 더 많이 필요하게 된다.
    • (해당 부분은 내부 동작에 대해서 아직 이해가 되지 않았다. B-Tree 의 rebalancing 이 비용이 높은 작업임은 이해하나, record 의 수에 비례하는 작업이기도 하다.)
• 따라서, 대량의 데이터를 migration 하거나 삽입해야하는 경우에는 다음과 같은 방법으로 속도를 최적화할 수 있다.
  • 정합성이 보장된 데이터라면 index 를 잠시 drop 하고 load 이후에 다시 index를 설정한다.
  • 데이터 적재시 한번에 너무 많은 양을 보내기보다, 나눠서 적재한다. (transaction 의 크기를 최적화)

방법에 따른 비교

• 환경
  • M1 Macbook Pro (Ram 16GB / M1 Pro)
  • Docker + MySQL 8.0
• LOAD DATA (10M 케이스의 경우 1 / 10 값을 산정함)

• LOAD DATA / Procedure 모두 index 를 해제하고 적재 후 다시 생성한다면 시간을 줄일 수 있다.

결론

• 상황에 따라 대량의 데이터를 삽입 시 두 가지의 방법을 고려하자.
  • Procedure + Bulk insert
    • 더미데이터를 생성하는 경우 유용하다. csv 파일로 저장할 경우, csv 파일의 크기 만으로도 상당히 크다. LOAD DATA 보다 느리기는 하나, 2배 정도 차이나므로 시도해볼만 하다.
  • LOAD DATA (CHUNKED)
    • DATA 를 migration 하는 경우에 가장 좋은 방법으로 판단된다. 모든 방법 중에서 가장 빠르게 적재할 수 있는 방법이며, 넣을 데이터를 나눌 Chunk 의 크기 조절과 MySQL 설정, Index 설정 등을 통해서 더욱 더 빠르게 최적화할 수 있다.