10.5.5 Bulk Data Loading for InnoDB Tables

10.5.5 Bulk Data Loading for InnoDB Tables

이러한 성능 팁은 Section 10.2.5.1, “Optimizing INSERT Statements”의 빠른 insert에 대한 일반적인 가이드를 보완합니다.

• InnoDB로 데이터를 import할 때는 autocommit 모드를 끄십시오. autocommit 모드는 각 insert마다 로그 플러시를 디스크에 수행하기 때문입니다. import 작업 동안 autocommit을 비활성화하려면, 작업을 SET autocommit 및 COMMIT 문으로 둘러싸십시오:

1SET autocommit=0;
2... SQL import statements ...
3COMMIT;

mysqldump 옵션 --opt은 dump 파일을 생성하는데, 이 파일은 SET autocommit 및 COMMIT 문으로 감싸지 않더라도 InnoDB 테이블로 빠르게 import할 수 있습니다.

• secondary 키에 UNIQUE 제약 조건이 있는 경우, import 세션 동안 유니크 검사(uniqueness check)를 임시로 비활성화하여 테이블 import를 가속할 수 있습니다:

1SET unique_checks=0;
2... SQL import statements ...
3SET unique_checks=1;

큰 테이블의 경우, 이 방법은 InnoDB가 체인지 버퍼를 사용하여 secondary 인덱스 레코드를 배치로 쓸 수 있게 해주므로 많은 디스크 I/O를 절약합니다. 데이터에 중복 키가 포함되어 있지 않은지 반드시 확인하십시오.

• 테이블에 FOREIGN KEY 제약 조건이 있는 경우, import 세션 동안 외래 키 검사(foreign key check)를 끄면 테이블 import를 가속할 수 있습니다:

1SET foreign_key_checks=0;
2... SQL import statements ...
3SET foreign_key_checks=1;

큰 테이블의 경우, 이는 많은 디스크 I/O를 절약할 수 있습니다.

• 많은 row를 insert해야 하는 경우, 클라이언트와 서버 간의 통신 오버헤드를 줄이기 위해 다중 row INSERT 구문을 사용하십시오:

1INSERT INTO yourtable VALUES (1,2), (5,5), ...;

이 팁은 InnoDB 테이블뿐 아니라 모든 테이블에 대한 insert에 유효합니다.

• auto-increment 컬럼이 있는 테이블에 bulk insert를 수행할 때는, innodb_autoinc_lock_mode를 1 (consecutive) 대신 2 (interleaved)로 설정하십시오. 자세한 내용은 Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”를 참조하십시오.

• bulk insert를 수행할 때는 row를 PRIMARY KEY 순서대로 insert하는 것이 더 빠릅니다. InnoDB 테이블은 clustered index를 사용하므로 PRIMARY KEY 순서의 데이터를 사용하는 것이 상대적으로 빠릅니다. bulk insert를 PRIMARY KEY 순서로 수행하는 것은 버퍼 풀 전체에 완전히 들어맞지 않는 테이블의 경우 특히 중요합니다.

• InnoDB``FULLTEXT 인덱스로 데이터를 load할 때 최적의 성능을 위해 다음 단계들을 따르십시오:

  1. 테이블 생성 시, 타입이 BIGINT UNSIGNED NOT NULL이고 FTS_DOC_ID_INDEX라는 이름의 유니크 인덱스를 가진 FTS_DOC_ID 컬럼을 정의합니다. 예를 들면 다음과 같습니다:

     1CREATE TABLE t1 (
     2    FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
     3    title varchar(255) NOT NULL DEFAULT '',
     4    text mediumtext NOT NULL,
     5PRIMARY KEY (`FTS_DOC_ID`)
     6) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     7
     8CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

  2. 데이터를 테이블에 load합니다.

  3. 데이터가 load된 후 FULLTEXT 인덱스를 생성합니다.

참고

테이블 생성 시 FTS_DOC_ID 컬럼을 추가하는 경우, FULLTEXT 인덱스가 설정된 컬럼이 업데이트될 때 FTS_DOC_ID 컬럼이 업데이트되도록 해야 합니다. FTS_DOC_ID는 각 INSERT 또는 UPDATE마다 단조 증가(monotonically)해야 합니다. 테이블 생성 시 FTS_DOC_ID를 추가하지 않고 InnoDB가 DOC ID를 관리하도록 선택하는 경우, InnoDB는 다음 CREATE FULLTEXT INDEX 호출 시 FTS_DOC_ID를 hidden 컬럼으로 추가합니다. 그러나 이 접근 방식은 테이블 리빌드를 필요로 하며, 이는 성능에 영향을 줄 수 있습니다.

• 새로운 MySQL 인스턴스에 데이터를 load하는 경우, ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG 구문을 사용하여 redo 로깅 비활성화를 고려하십시오. redo 로깅을 비활성화하면 redo 로그 write를 피함으로써 데이터 로딩을 가속하는 데 도움이 됩니다. 자세한 내용은 Disabling Redo Logging을 참조하십시오.

주의

이 기능은 새로운 MySQL 인스턴스에 데이터를 load하는 경우에만 사용하도록 의도된 것입니다. 프로덕션 시스템에서는 redo 로깅을 비활성화하지 마십시오. redo 로깅이 비활성화된 동안 서버를 shutdown 및 restart하는 것은 허용되지만, redo 로깅이 비활성화된 상태에서 예기치 않은 서버 중지는 데이터 손실과 인스턴스 손상을 유발할 수 있습니다.

• 데이터 import에 MySQL Shell을 사용하십시오. MySQL Shell의 병렬 테이블 import 유틸리티인 util.importTable()은 큰 데이터 파일에 대해 MySQL 관계형 테이블로의 빠른 데이터 import를 제공합니다. MySQL Shell의 dump 로딩 유틸리티 util.loadDump() 역시 병렬 로드 기능을 제공합니다. MySQL Shell Utilities를 참조하십시오.