더미 데이터 적재 속도 비교

인트로

Offset 페이징과 No-Offset 페이징의 성능을 비교하기 위해 더미 데이터를 추가하던 중, 다양한 방식으로 적재 속도를 비교해 보았습니다.

1. 개별 Insert
2. 프로시저 호출
3. CSV Import
4. Bulk Insert
5. Bulk Insert + 자동 커밋 비활성
6. Bulk Insert + 자동 커밋 비활성 + 외래키 검사 비활성

실행 환경

• PC : Apple M2 Pro 10코어 (저전력모드)

• MySQL 8.0.31 (Docker)

  

• Tool : IntelliJ 내장 Database

더미 데이터

• 1,000만 건의 Article 레코드
• 외래키 제약조건 1개, Check 제약조건 2개가 존재

---

1. 개별 Insert

방법

• INSERT문 1,000만개로 구성된 sql 파일을 실행

  from datetime import datetime
  
  #SQL 파일 생성
  sql_file_path = 'individual_insert.sql'
  with open(sql_file_path, 'w') as sql_file:
      # 1부터 10000000까지의 데이터 삽입
      for i in range(1, 10000001):
          dt = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
          insert_statement = f"INSERT INTO article (article_id, contents, created_term, created_year, published, user_id, created_date, modified_date) VALUES ({i}, 'contents', 1, 2024, 1, 537307139847203855, '{dt}', '{dt}');\n"
  
          sql_file.write(insert_statement)
  
  print(f"SQL 파일이 생성되었습니다: {sql_file_path}")

결과

• 3시간 23분 경과

---

2. 프로시저 호출

방법

• 반복문으로 INSERT를 1000번 실행하는 프로시저를 호출

  create
      definer = root@`%` procedure loopInsert()
  BEGIN
      DECLARE i INT DEFAULT 1;
      DECLARE dt datetime DEFAULT NOW();
      WHILE i <= 10000000 DO
              INSERT INTO article
              VALUES(i, 'contents', 1, 2024, 1, 537307139847203855, dt, dt);
              SET i = i + 1;
          END WHILE;
  END;

결과

• 2분 12초 경과

---

3. CSV Import

방법

• 더미 데이터가 저장된 csv 파일을 생성 후 Import

  

결과

• 12분 59초 경과

---

4. Bulk Insert

방법

• Bulk Insert문이 담긴 sql 파일을 실행

  # SQL 파일 생성
  sql_file_path = 'bulk_insert.sql'
  with open(sql_file_path, 'w') as sql_file:
      # INSERT INTO ... VALUES 문 작성
      sql_file.write("INSERT INTO article (article_id, contents, created_term, created_year, published, user_id, created_date, modified_date) VALUES\n")
  
      # 1부터 10000000까지의 데이터 삽입
      for i in range(1, 10000001):
          dt = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
          values = f"({i}, 'contents', 1, 2024, 1, 537307139847203855, '{dt}', '{dt}')"
  
          # 마지막 행이 아니라면 쉼표를 추가
          if i < 10000000:
              values += ","
  
          sql_file.write(values)
  
      sql_file.write(";\n")
  
  print(f"SQL 파일이 생성되었습니다: {sql_file_path}")

결과

• 9분 24초 경과

---

5. Bulk Insert + 자동 커밋 비활성

방법

• 자동 커밋을 비활성화 한 후, Bulk Insert를 수행

  SET autocommit=0;
  [BULK INSERT문]
  commit;

결과

• 9분 15초 경과 (9초 개선)

---

6. Bulk Insert + 자동 커밋 비활성 + 외래키 검사 비활성

방법

• 자동 커밋과 외래키 검사를 비활성화 후, Bulk Insert를 수행

  SET autocommit=0;
  SET foreign_key_checks=0;
  [BULK INSERT문]
  SET foreign_key_checks=1;
  commit;

결과

• 9분 3초 경과 (21초 개선)

---

결과

정리

• 성능 : 프로시저 > Bulk Insert > CSV Import >>>>> 개별 Insert
• Bulk Insert는 개별 Insert 방식보다 21배, CSV 방식보다 1.38배 빠르다
• 프로시저는 Bulk Insert 방식보다 4.27배 빠르다

결론

1. 데이터 구성이 단순한 경우 프로시저를 활용하자
2. 프로시저 활용이 불가능하다면 Bulk Insert를 활용하자
3. Bulk Insert시, 자동 커밋에 의한 영향은 거의 없다
4. 외래키 검사를 비활성화하면 좀 더 빨라진다 - 외래키 1개 기준, 2% 개선

---

참고

• [SQL] 쿼리 튜닝을 위한 준비 : 더미 데이터 적재
• BULK INSERTING - MYSQL 다량의 데이터 넣기