개발수양록

대량 데이터를 입출력하는 DBMS별 명령어와 도구(MySQL, Oracle, SQL Server, PostgreSQL)

MySQL, Oracle, MS SQL Server, PostgreSQL이 제공하는 대량 데이터 입출력 방식(LOAD DATA INFILE, SQL*Loader, BULK INSERT, COPY 등)과 성능 옵션을 정리합니다.

DBMS별로 최적화된 대량 데이터 로딩과 추출 기능을 전용 SQL 구문이나 도구로 제공합니다. 이 방식들은 구현 원리상 자바 애플리케이션으로는 도달하기 어려운 성능을 발휘합니다.

1. MySQL

MySQL에서는 SELECT …​ INTO OUTFILE 명령으로 데이터를 익스포트하고, LOAD DATA INFILE 명령으로 임포트합니다. SELECT …​ INTO OUTFILE 은 다음과 같은 형식으로 사용합니다.

SELECT [칼럼명]
INTO OUTFILE [파일명]
FROM [테이블명]

FIELDS 와 LINES 절을 추가하면 필드와 행 사이를 구분하는 문자를 지정할 수 있습니다. 디폴트로는 탭 문자(\t)로 칼럼 사이를, 새 줄 문자(\n)로 행 사이를 구분합니다. baseball.players 테이블의 내용을 players.csv 라는 파일명으로 출력하는 예시는 아래와 같습니다.

SELECT back_num, name, position
INTO OUTFILE 'players.csv'
FROM baseball.players
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

LOAD DATA INFILE 명령어는 아래와 같은 형식으로 사용합니다.

LOAD DATA INFILE [파일명] INTO TABLE [테이블명]

마찬가지로 필드와 라인 구분자를 지정할 수 있고, SELECT …​ INTO OUTFILE 과 같은 방식으로 옵션을 지정합니다. players.csv 의 내용을 baseball.players 테이블로 로딩하는 명령어는 아래와 같습니다.

LOAD DATA INFILE 'players.csv'
INTO TABLE baseball.players
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

대량 적재의 성능은 스토리지 엔진에 따라 손볼 곳이 다릅니다. MySQL 5.5부터 기본 스토리지 엔진이 InnoDB이고 요즘은 대부분 InnoDB를 쓰므로, InnoDB 기준으로 정리합니다. InnoDB 테이블에 대량으로 적재할 때는 innodb_buffer_pool_size 를 충분히 확보하고, 적재 구간에서 자동 커밋과 무결성 검사를 잠시 꺼 부가 작업을 줄이는 방식이 효과적입니다.

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;

LOAD DATA INFILE 'players.csv'
INTO TABLE baseball.players
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

COMMIT;

적재가 끝나면 꺼 두었던 unique_checks 와 foreign_key_checks 는 다시 켜야 합니다. 참고로 지금은 거의 쓰이지 않는 MyISAM 엔진에서는 bulk_insert_buffer_size, myisam_sort_buffer_size 같은 변수가 대량 삽입 성능에 영향을 줍니다. 다만 이 값들은 InnoDB 테이블에는 적용되지 않습니다.

LOAD DATA INFILE 명령은 mysqlimport 라는 도구로도 수행할 수 있습니다. 이 도구를 쓸 때 네트워크를 통해 파일이 전송된다면 데이터를 압축해 성능 향상을 꾀할 수 있습니다. 예전에는 --compress 옵션을 썼으나, MySQL 8.0.18부터는 이 옵션이 deprecated되어 --compression-algorithms 로 압축 알고리즘을 지정하는 방식으로 바뀌었습니다.

한 가지 주의할 점은 최근 MySQL이 보안상의 이유로 파일 입출력에 제약을 둔다는 것입니다. SELECT …​ INTO OUTFILE 과 (LOCAL 을 붙이지 않은) LOAD DATA INFILE 은 서버의 secure_file_priv 시스템 변수가 가리키는 디렉터리 안에서만 동작하며, 이 변수는 MySQL 5.7.6부터 기본값이 설정되어 있습니다. 또한 클라이언트 쪽 파일을 읽는 LOAD DATA LOCAL INFILE 은 MySQL 8.0부터 local_infile 변수가 기본적으로 꺼져 있어, 서버와 클라이언트 양쪽에서 이를 명시적으로 켜야 동작합니다.

자세한 내용은 아래 페이지를 참조합니다.

2. Oracle

Oracle에서는 SQL*Loader, Data Pump API 같은 도구나 External Table을 생성해서 데이터 로딩을 할 수 있습니다.

SQL*Loader에서 다루는 파일들은 역할이 상세하게 구분됩니다.

  • 컨트롤 파일 : 수행할 명령문을 담은 파일.

  • 입력 데이터 파일 : DB로 임포트할 데이터를 담은 파일. 컨트롤 파일에서 INFILE * 구문을 사용하면 컨트롤 파일에 데이터를 포함시킬 수도 있습니다.

  • 로그 파일 : 작업 과정과 결과를 기록하는 파일.

  • Bad 파일 : 입력이 거부된 행들이 저장되는 파일. 예를 들면 형식이 틀려서 SQL*Loader가 해석할 수 없거나 DB의 제약조건을 충족시키지 못한 행들이 이 파일로 기록됩니다.

  • Discard 파일 : 컨트롤 파일에서 정한 규칙에 의해 필터링되는 행들이 기록되는 파일.

컨트롤 파일은 아래와 같이 명령행에서 위치를 지정합니다.

sqlldr userid=scott/tiger control='./control.ctl'

컨트롤 파일 안에서는 아래와 같이 입력할 데이터 파일의 위치와 형식, 값이 들어갈 테이블을 지정합니다.

control.ctl
LOAD DATA
INFILE 'players.csv'
INTO players
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( back_num, name, position )

성능을 높이기 위해 아래 옵션값을 조정해볼 수 있습니다.

  • direct : Direct Path Load 를 사용할지를 true/false로 지정합니다. 이 방식을 쓰면 내부적으로 SQL 명령을 생성하지 않고 데이터베이스 블록에 직접 쓰기 작업을 하기 때문에 성능이 더 좋아집니다. direct 옵션의 디폴트는 false로, 특별히 지정하지 않으면 데이터 로딩을 INSERT 명령으로 수행하는 Conventional Path Load 가 활성화됩니다. Direct Path Load 를 쓸 때는 데이터 로딩 도중에 인덱스 업데이트와 제약조건 등이 바로 적용되지 않으며, 로딩 작업의 시작과 끝에 테이블과 인덱스에 락을 잡습니다. 따라서 로딩 작업 도중에 다른 프로세스에서도 대상 테이블에 접근해야 한다면 적합하지 않습니다. 클러스터링된 테이블에도 사용할 수 없습니다.

  • parallel : 병렬 수행 여부입니다. parallel 모드로 데이터를 올리는 중에는 인덱스가 업데이트되지 않으므로, 데이터 업로드가 끝난 후에 인덱스를 재생성해줘야 합니다. direct=true 와 함께 쓰려면 대상 테이블에 트리거나 제약조건이 비활성화되어 있어야 합니다.

  • rows : 한 번에 운반하는 행의 개수입니다. direct=false 일 때는 commit이 일어나는 단위로, 스프링 배치의 commit-interval 과 유사합니다. direct=true 로 Direct Path Load 를 쓸 때도 rows가 데이터를 저장하는 단위라는 점은 비슷하지만, 이 모드에서는 중간에 인덱스가 갱신되지 않습니다.

그 외에도 direct 옵션을 사용할 때는 columnarrayrows, streamsize, 아닐 때는 bindsize 등의 옵션으로 데이터의 운반 단위를 조정할 수 있습니다. 위의 옵션은 명령행에서 지정할 수 있습니다. 예를 들어 direct와 parallel 모드를 동시에 사용해서 데이터를 임포트하려면 아래와 같이 실행합니다.

sqlldr userid=scott/tiger control='./control1.ctl' DIRECT=TRUE PARALLEL=true
sqlldr userid=scott/tiger control='./control2.ctl' DIRECT=TRUE PARALLEL=true

Oracle 9i부터는 데이터를 테이블스페이스 바깥의 파일로 유지하는 External Table이 제공됩니다. CSV 형식의 외부 파일을 External Table로 연결하고 SELECT 구문으로 조회해서 테이블에 저장하는 작업도 가능합니다. External Table에 SELECT를 할 때 JOIN, SORT도 사용할 수 있고, 읽기 작업을 병렬로 수행할 수 있다는 장점도 있습니다. 그러나 읽기 전용 작업만 허용되기 때문에 DELETE나 UPDATE 같은 SQL을 External Table을 대상으로 수행할 수는 없습니다.

같은 Oracle DB 간의 테이블 단위 데이터 로딩에는 import, export 유틸리티가 더 성능에 유리합니다. Oracle Database 10.1(10g release 1) 이후부터는 이를 대체하는 Data Pump API가 제공됩니다. 자세한 내용은 아래를 참조합니다.

3. MS SQL Server

SQL Server에서는 OPENROWSET과 BULK INSERT 구문이 제공되고, bcp나 SSIS(Integration Services) 패키지 같은 별도의 도구로도 비슷한 작업을 수행할 수 있습니다.

BULK INSERT 구문은 파일을 지정하는 것 외에는 보통의 INSERT 문처럼 사용할 수 있습니다. players.csv 에서 players 테이블로 데이터를 입력하는 예제는 아래와 같습니다.

BULK INSERT players
FROM 'players.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)

BULK INSERT를 할 때는 아래와 같은 옵션을 조정해 성능을 높일 수 있습니다.

  • tablock : 대량 입력 작업 동안 테이블을 잠급니다.

  • rows_per_batch : 하나의 트랜잭션으로 처리할 행의 개수입니다. 스프링 배치의 commit-interval 속성과 유사합니다. 되도록 큰 값을 지정하는 것이 성능 향상에 유리하나, 인덱스가 있는 테이블에서 지나치게 큰 값을 지정하면 메모리 사용량이 많아지고 병렬 업로드 시 테이블 락을 유발할 수 있습니다.

앞선 예제에서 테이블 락을 걸고 3000개씩 commit을 하는 옵션을 추가하면 다음과 같습니다.

BULK INSERT players
FROM 'players.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  TABLOCK,
  ROWS_PER_BATCH = 3000
)

파일로부터 데이터를 읽어 들이는 또 하나의 방법인 OPENROWSET 구문은 아래와 같이 SELECT 문장과 함께 사용합니다.

SELECT *
FROM OPENROWSET('MSDASQL',
     'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\data;',
     'SELECT * FROM players.csv')

칼럼명은 텍스트 파일의 첫 줄에 포함시키거나 Schema.ini 파일로 정의할 수 있습니다. 다만 위 예제에서 쓰인 Microsoft Text Driver 는 32비트 전용인 구형 Jet 기반 드라이버로, 현재는 권장되지 않습니다. 최근 환경에서는 64비트를 지원하는 ACE(Access Connectivity Engine) 공급자(Microsoft.ACE.OLEDB.x)를 쓰거나, 아예 SQL Server가 직접 파일을 읽는 OPENROWSET(BULK …​) 구문을 사용하는 편이 낫습니다.

SQL Server는 bcp라는 별도의 데이터 임포트/익스포트 유틸리티도 제공합니다. bcp는 본래 Sybase에서 유래한 도구로, SAP ASE(과거 Sybase ASE)의 bcp와도 사용법이 비슷합니다. 데이터 익스포트와 임포트를 모두 SQL Server를 대상으로 할 때는 네이티브 형식을 이용하면 성능이 더 향상됩니다. BULK INSERT 구문에서는 DATAFILETYPE = 'native' 옵션으로, bcp에서는 -n 옵션을 지정해서 네이티브 파일을 사용할 수 있습니다.

그리고 SQL Server와 함께 제공되는 데이터 통합 도구인 SSIS(Integration Services) 패키지로도 다양한 자원에서 데이터를 입출력할 수 있습니다. GUI 도구에서 작업을 설계하고 dtexec.exe 라는 실행 파일로 커맨드라인에서 작업을 실행하는 방식도 가능합니다. ETL 도구와 유사한 접근법입니다. 앞에서 BULK INSERT 문의 옵션이었던 tablock, rows_per_batch 등의 값도 SSIS에서 설정할 수 있습니다.

각각의 방식에 대한 자세한 설명은 아래 페이지를 참고합니다.

4. PostgreSQL

PostgreSQL에서는 COPY 구문, Foreign Data Wrapper, pg_bulkload를 활용해 텍스트 파일을 바로 DB에 익스포트/임포트할 수 있습니다.

테이블에 있는 데이터를 COPY 구문으로 아래와 같이 익스포트합니다.

COPY (SELECT name, position, back_num FROM players)
TO 'players.csv'
WITH (FORMAT CSV);

임포트 명령은 아래와 같습니다.

COPY players (name, position, back_num)
FROM '/data/players.csv' DELIMITER ',' CSV;

마찬가지로 같은 PostgreSQL끼리 데이터를 주고받을 때는 포맷을 BINARY 로 지정하면 성능 향상을 유도할 수 있습니다.

PostgreSQL에서는 DB 밖에 저장된 데이터를 DB 안의 객체처럼 접근하는 Foreign Data Wrapper라는 개념도 지원합니다. Oracle의 External Table이나 SQL Server의 OPENROWSET 방식과 유사합니다. 아래와 같이 CREATE EXTENSION, CREATE SERVER 구문으로 file_fdw 모듈을 설치합니다.

CREATE EXTENSION file_fdw;
CREATE SERVER baseball FOREIGN DATA WRAPPER file_fdw;

그리고 파일에서 읽어올 데이터의 스키마를 아래와 같이 정의합니다.

CREATE FOREIGN TABLE players (
  name VARCHAR(30),
  position VARCHAR(30),
  back_num INTEGER
) SERVER baseball
OPTIONS ( filename '/home/benelog/players.csv', format 'csv' );

format, header, delimiter 등의 옵션은 COPY 구문과 똑같이 사용할 수 있습니다.

pg_bulkload라는 전용 도구도 지원합니다. Oracle의 SQL*Loader처럼 ctl 파일에 작업의 명세를 정의할 수 있습니다. CSV 파일을 로딩하는 ctl 파일은 아래와 같이 정의합니다.

players_csv.ctl
OUTPUT = players
INPUT = /home/benelog/players.csv
TYPE = CSV
QUOTE = "\""
ESCAPE = \
DELIMITER = ","

명령행에서 다음과 같이 실행하면 CSV 파일의 내용이 테이블로 입력됩니다.

pg_bulkload players_csv.ctl

각각의 방식에 대한 자세한 사용법은 다음을 참조합니다.

RAG 시스템의 구성요소와 성능을 좌우하는 요소