[Admin] MySQL/MariaDB 대용량 데이터 마이그레이션 성능 비교(CTAS, mysqldump, INTO OUTFILE~LOAD DATA LOCAL INFILE)

MySQL/MariaDB에서 대용량 데이터를 이관하기 위해서 다양한 방법이 존재합니다. 저는 그중에서 전통적인 방식 CTAS와 mysqldump 유틸리티 그리고 INTO OUTFILE LOAD DATA LOCAL INFILE입니다. 대용량 데이터를 빠르게 이관할 때 유용하게 사용 가능합니다. 다만, 서비스를 운영하는 시점에 적용하는 게 아니라 점검 시간이나 다운타임이 발생할 때 가능합니다.

 

1. 테스트 환경

OS : CentOS 7.5(CPU : Intel(R) Xeon(R) CPU E3-1220 v3 @ 3.10GHz(4 core), Memory : 32GB)

DB : MariaDB 10.3.8

Parameter : 

• innodb_buffer_pool_size =15G
• innodb_flush_log_at_trx_commit=0
• sync_binlog=0
• tx_isolation=READ-COMMITTED

innodb_flush_log_at_trx_commit는 장애 시 commit이 완료된 트랜잭션이 유실되는 것을 방지하기 위해 필요한 파라미터인데 성능을 높이기 위해서는 0으로 설정하여 매 트랜잭션 commit마다 InnoDB log를 디스크로 sync하지 않도록 해줍니다. 
sync_binlog를 0으로 세팅하면 binary log flush를 OS가 알아서 하게 하므로 성능을 높일 수 있습니다
innodb_support_xa는 InnoDB log와 binary log의 일관성을 보장하기 위해서 필요하다고 하는데 여기서는 필요 없기 때문에 0으로 설정하였습니다. 
MySQL의 Transaction Isolation Level 기본 값은 REPEATABLE READ입니다. 하지만 이를 보장하기 위해서 Gap Lock을 사용하는데 이를 피하려면 Transaction Isolation Level을 READ COMMITTED나 READ-UNCOMMITTED로 변경하면 됩니다. 

 

 

2. CTAS(Create Table As Select)

OLDDB 데이터베이스에서 ESTDB 데이터베이스로 동일한 테이블을 옮겨보도록 하겠습니다. CTAS 데이터를 옮기게 되면 테이블 기본적인 스키마(PK, FK, UK, INDEX 제외)를 생성하고, 데이터를 이관하게 됩니다.

MariaDB [(none)]> CREATE DATABASE ESTDB;
Query OK, 1 row affected (0.021 sec)

MariaDB [ESTDB]> CREATE TABLE LARGE_DB_INSPT AS
    -> SELECT * FROM OLDDB.LARGE_DB_INSPT;
Query OK, 1858594 rows affected (20.309 sec)
Records: 1858594  Duplicates: 0  Warnings: 0     ->> 데이터 이관 완료

 

OLDDB 데이터베이스 LARGE_DB_INSPT 테이블에서 있었던 Primary Key를 생성합니다. 대용량 테이블일 경우 ALTER 문법을 진행할 때는 반드시 여러 번 고민해보고 적용하시기 바랍니다. 

MariaDB [ESTDB]> ALTER TABLE LARGE_DB_INSPT ADD PRIMARY KEY (SOLT_INSPT_ID, INSPT_EVENT_ID, EQMT_IP);
Connection id:    1338530
Current database: ESTDB

Query OK, 0 rows affected (2 min 30.731 sec)
Records: 0  Duplicates: 0  Warnings: 0            ->> 정상적으로 PK 생성 완료

 

3. mysqldump(mysqldump  Ver 10.16 Distrib 10.3.8-MariaDB)

mysqldump 유틸리티에서 데이터 백업할 때 필요한 옵션에 대해서 정리해보겠습니다.

• --no-autocommit=1 : autocommit을 끄고 개의 테이블 입력 완료 후 commit 수행, 오류 발생 시 다시 처음부터 시작합니다.
• --single-transaction=1 : 작업 후에 변경된 데이터의 내역을 다시 적용하지 않습니다.
• --extended-insert=1 :  INSERT 구문이 늘어나는 것을 차단합니다.

time 명령어를 활용해서 수행 시간을 확인합니다. 작업이 완료되면 습관적으로 tail -1으로 파일을 확인해보면 "Dump completed on 2020-07-08 15:57:00"으로 나오게 됩니다. head 명령어로 앞쪽 스크립트를 확인하는 것도 권장합니다.

[estdbusr@estsvr01 sspark]$ 
time mysqldump -hlocalhost -uroot -proot --databases OLDDB \
--tables LARGE_DB_INSPT --no-autocommit=1 --single-transaction=1 --extended-insert=1 \
> LARGE_DB_INSPT.sql

real    0m3.324s
user    0m2.344s
sys     0m0.169s

 

이제 데이터를 복원해보겠습니다. 여기에서 작업을 시작하기 전에 MySQL/MariaDB 글로벌 파라미터를 변경해서 성능을 높여 줍니다. 

• SET global net_buffer_length=1000000;
• SET global max_allowed_packet=1000000000;
• SET foreign_key_checks = 0;
• SET UNIQUE_CHECKS = 0;
• SET AUTOCOMMIT = 0;

[estdbusr@estsvr01 sspark]$ 
time mysql -hlocalhost -uroot -proot ESTDB < LARGE_DB_INSPT.sql

real    1m4.211s
user    0m2.491s
sys     0m0.114s

데이터 복원을 정상적으로 마쳤을 경우에는 아래와 같이 파라미터 설정을 다시 변경합니다.

• SET foreign_key_checks = 1;
• SET UNIQUE_CHECKS = 1;
• SET AUTOCOMMIT = 1;

4. INTO OUTFILE LOAD DATA LOCAL INFILE

배치 프로세스 개발할 때 많이 사용하는 방법으로 개발할 때 유연성을 보장해주는 장점이 있습니다. 그리고 무엇보다 빠르게 데이터 마이그레이션을 할 수 있습니다. 하지만 단점은 파일 안에 있는 데이터에 특수문자를 제어해야 합니다. 만약에 "FIELDS TERMINATED BY '|'" 옵션을 사용했는데 파일안에 '|' 문자가 존재한다면 에러가 나서 동작하지 않습니다.

 

데이터를 백업해보겠습니다.

MariaDB [ESTDB]> 
SELECT    SOLT_INSPT_ID       
         ,INSPT_EVENT_ID      
         ,EQMT_IP             
         ,EQMT_HOST_NM        
         ,DB_DUPX_TP_CODE     
         ,SOLT_INSPT_RST_CODE 
         ,SOLT_INSPT_RST_CNTS 
         ,REG_USER_NO         
         ,REG_DTTM            
INTO OUTFILE '/home/estdbusr/sspark/LARGE_DB_INSPT.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'
FROM OLDDB.LARGE_DB_INSPT;
Query OK, 1861555 rows affected (4.903 sec)

 

데이터를 복원해보겠습니다.

MariaDB [ESTDB]> 
CREATE TABLE "LARGE_DB_INSPT" (
  "SOLT_INSPT_ID" varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
  "INSPT_EVENT_ID" varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
  "EQMT_IP" varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
  "EQMT_HOST_NM" varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
  "DB_DUPX_TP_CODE" varchar(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ,
  "SOLT_INSPT_RST_CODE" varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
  "SOLT_INSPT_RST_CNTS" text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ,
  "REG_USER_NO" decimal(9,0) NOT NULL DEFAULT 1 ,
  "REG_DTTM" datetime NOT NULL DEFAULT current_timestamp() ,
  PRIMARY KEY ("SOLT_INSPT_ID","INSPT_EVENT_ID","EQMT_IP")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  


MariaDB [ESTDB]> 
LOAD DATA LOCAL INFILE '/home/estdbusr/sspark/LARGE_DB_INSPT.csv' 
INTO TABLE ESTDB.LARGE_DB_INSPT CHARACTER SET euckr FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
Query OK, 1862836 rows affected (23.350 sec)
Records: 1862836  Deleted: 0  Skipped: 0  Warnings: 0

 

"LINES TERMINATED BY '\r\n'" \r 옵션을 사용하면 아래와 같이 warnings이 발생 합니다. 

MariaDB [ESTDB]> 
LOAD DATA LOCAL INFILE '/home/estdbusr/sspark/LARGE_DB_INSPT.csv' 
INTO TABLE ESTDB.LARGE_DB_INSPT CHARACTER SET euckr FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
Query OK, 1 row affected, 1 warning (1.422 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [ESTDB]> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'REG_DTTM' at row 1 |
+---------+------+-----------------------------------------------+

 

5. 결과

데이터 이관 방식	수행 시간(ms)	CPU(4 Core)	건수	용량(size)
CTAS	2분 40초	40%	1,858,594	308 MB
mysqldump	1분 7초	90%
INTO OUTFILE LOAD DATA LOCAL INFILE	28초	170%

결과적으로는 속도는 INTO OUTFILE LOAD DATA를 활용하는 게 좋습니다. 하지만, 큰 용량에 따라서 성능 저하를 가져올 수 있습니다. 속도와 성능을 고려한다면 mysqldump를 활용하는 것을 권장합니다.

 

6. 참고

• treeple3.tistory.com/entry/ERROR-Warning-Code-1265
• blackbull.tistory.com/8
• lalwr.blogspot.com/2017/12/mysqldump.html
• www.mobiinside.co.kr/2018/02/09/buzzvil-migration-mysql/