대량의 더미 데이터 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
  • 질문자가 질문을 하게 된 데이터 적재량 - 속도의 연관은 아래 그래프와 같다.
graph of the data directly above
  • 내가 경험한대로 각 CSV 에 row 수가 증가할 수록 데이터 적재 속도가 비선형적으로 증가한다.
  • 더 눈여겨볼 것은 그가 index / fk contraint 를 제거하고 적재한 상황이다.
graph of the data above (execution time for table with removed constraints)
  • 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 값을 산정함)
방법 소요시간 (per 1,000,000 rows)
Procedure (loop) < 9 min
Procedure (bulk, 100) < 40 sec
LOAD DATA (1M) < 20 sec
LOAD DATA (10M) >> 5 min
LOAD DATA (500K) < 15 sec
  • LOAD DATA / Procedure 모두 index 를 해제하고 적재 후 다시 생성한다면 시간을 줄일 수 있다.

결론

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


Read more

Lombok 에 대한 짧은 생각

Lombok 에 대한 짧은 생각

최근 Java / Spring 으로 스택을 변경하면서 작은 프로젝트를 하고 있다. 팀원의 추천으로 lombok 이라는 라이브러리를 사용하게 되었다. Lombok 을 쓰면 정말 간단하다. Getter, Setter, Constructor, equals, hashCode 과 같이 작성해야하지만 작성에 피로를 느끼는 코드들을 annotation을 통해서 너무 쉽게 만들 수 있다. 그런데, 이 라이브러리 사용할수록 필요성에 대한 의문이 생긴다. 생각을

By Sungjun Park
인증 인가 정보는 어디에 담아야할까? (cookie? authentication header?)

인증 인가 정보는 어디에 담아야할까? (cookie? authentication header?)

인증 인가 정보 전달 방법의 결정 요구사항 * HTTP 표준에 부합해야한다. * 보안 이슈를 최소화해야한다. * 클라이언트에서도 처리가 간편해야한다. * 모든 HTTP request 에서 처리 가능해야한다. * JWT, Session 모두 고려해야한다. 고려대상 Request Body * Request Body 에 auth 정보를 싣어 보내는 방법의 경우 GET HEAD DELETE TRACE 와 같은 HTTP Method 에서 사용할 수 없다.

By Sungjun Park