MySQL 대량등록(Bulk Inserting) 성능 향상
페이지 정보
본문
mySQL은 조회(SELECT) 에 있어서 최고의 강점을 갖는 종류의 DB이지만, 경우에 따라 막대한 양의 Insert 를 수행해야할 때가 있습니다.
특히 API 에서 조회하는 DB에 배치 서버가 수시로 데이터를 갱신해주는 경우나,
막대한 양의 메시지 데이터를 처리하게 될 경우가 있는데,
DB가 커질 수록 단순 쿼리로만 작업하면 퍼포먼스에 있어 엄청난 디버프를 받게 됩니다.
대량 데이터 insert(삽입) 할때 시간이 걸리는 작업
데이터를 디스크에 동기화 (트랜젝션 종료의 일부로)
새 키 추가. 인덱스가 클수록 키를 업데이트 하는데 더 많은 시간이 걸림
외래 키를 확인 (존재하는 경우)
스토리지 엔진에 행 추가
서버로 데이터를 보내는 중
1. Using Big Transactions
한번에 많은 삽입을 수행할때 모든 행에 대해 전체 트랜젝션(디스크 동기화 포함)을 수행하지 않기 위해, 이름 BEGIN / END 로 매핑을 합니다
예를 들어, 1000개의 Insert 마다 BEGIN / END 를 하면 INSERT 속도가 거의 1000배 빨리집니다
BEGIN;
INSERT ...;
INSERT ...;
END;
BEGIN;
INSERT ...;
INSERT ...;
END;
2. Multi-Value Inserts
여러 개의 Insert 구문 수행시 Values 리스트를 다중으로 사용합니다.
다수의 동일한 Insert query에 대해서 다음과 같이 최적화가 가능합니다.
Insert into `테이블` values(a, b, c);
Insert into `테이블` values(d, e, f);
위와 같이 쿼리를 날린다고 할때, 이를 다음과 같이 바꿉니다.
Insert into `테이블` values(a, b, c), (d, e, f) ...;
이렇게 되면 성능이 기하급수적으로 향상되며, 이는 JDBC 혹은 클라이언트 등의 연결 설정에서 RewriteBatchedStatements=true 와 같은 속성을 주어 자동으로 최적화시킬 수 있습니다.
(물론 설정의 경우 실수의 우려도 있고, 개발자가 한눈에 파악하기 쉽지 않기 때문에 협업 시에는 코드 레벨에서 처리하는게 더 좋습니다.)
또한 이 쿼리 튜닝을 사용할 때에는 비어있지 않은 Table에 Insert 시 my.cnf 파일 내에 있는 bulk_insert_buffer_size를 변경하여 속도 개선이 가능합니다.
(수정 방법은 /etc/mysql/my.cnf 파일을 열어서 [mysqld] 항목 아래에 bulk_insert_buffer_size=256M 과 같이 설정합니다.)
3. Inserting Data Into Serveral Tables at Once
한번에 여러 테이블에 데이터를 삽입하는 경우 가장 좋은 방법은 다중 행 문을 활성화 하고 한번에 많은 삽입을 서버에 보내는 것입니다
insert into `테이블1` (a, b, c) values ('1', '2', '3');
insert into `테이블2` (a, b, c, d) values ('1', '2', '3', '4');
4. 여러 클라이언트에서 Insert 시 Insert Delayed 를 통해 속도 개선이 가능합니다.
여러 클라이언트에서 대량의 데이터를 삽입을 한다면 INSERT DELAYED 명령문을 사용하면 속도를 개선 할 수 있습니다.
서버로 부터 수행 응답을 받은 후 큐에 적재 후 테이블에 사용이 되지 않을때 테이블에 삽입이 됩니다.
INSERT DELAYED 는 MyISAM, MEMORY, ARCHIVE 테이블에서 사용 가능합니다.
그외 테이블에서는 성능 저하가 발생 할 수 있습니다.
affected row 값은 테이블이 바쁘지 않을때는 정확한 값을 출력을 하나 그렇지 않을때는 1의 값을 반환을 합니다.
DELAYED 이외에 LOW_PRIORITY, IGNORE, HIGI_PRIORITY 등을 이용하여 작업의 순위, 중복 오류등을 무시 할 수 있습니다.
LOW_PRIORITY : 최 하위 순위로 INSERT구문을 수행합니다. 다른 클라이언트의 SELECT가 종료된 후 수행합니다.
IGNORE : 키 중복 (PRIMARY KEY, UNIQUE KEY)의 오류를 무시하고 진행합니다.
HIGH_PRIORITY : 최 우선 작업으로 INSERT구문을 수행합니다.
INSERT DELAYED 구문을 수행할때에는 Slave replaction 와는 다른 데이터를 가질 수 있습니다.
INSERT DELAYED INTO `테이블` (필드1, 필드2) VALUES ('값1', '값2'),('값1', '값2')...('값1', '값2');
5. 파일 스트림으로부터 대량의 데이터를 삽입 시 Load Data Local Infile 구문을 이용해서 필드 구문자로 정리된 File을 MySQL DB로 Redirection 시킬 수 있습니다.
각각에 대한 Insert 구문 수행보다 빠른 퍼포먼스를 보입니다.
(ex) Delimeter가 "|" 이고 라인 개행 단위로 레코드가 기록되어 있을 때,
LOAD DATA LOCAL INFILE '파일경로' [REPLACE | IGNORE] INTO TABLE `테이블` FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
단, 이 경우 5.5 이상 버전에서는 LOCAL을 꼭 붙여야 에러가 나지 않으며, 이를 이용하기 위해 설정 옵션에 [mysql] 아래에 local-infile을, [mysqld] 아래에 local-infile을 추가해주어야 합니다.
6. Index가 많이 사용된 테이블에 대량으로 Insert 시 Index를 비활성화한 후 수행합니다.
매 Row 마다 계산 후 삽입하는 것보다 전체 키를 비활성화하고 데이터 입력이 종료되고 다시 키를 활성화시키는 것이 빠른 수행을 할 수 있습니다.
ALTER TABLE `테이블` DISABLE KEYS;
Insert 구문 수행
ALTER TABLE `테이블` ENABLE KEYS;
여기서 Key 란 INDEX 뿐 아니라 FOREIGN KEY 참조도 포함합니다.
실제로 Bulk Inserting 이 중요하다면, 체크를 꺼두는 것이 성능 향상에 큰 도움이 됩니다.
물론 이 경우 단점은, API 로직상 에러가 발생하여 문제가 될 수 있는 데이터가 삽입될 시, 실시간으로 정합성 체크가 불가능합니다.
데이터를 신뢰할 경우에만 수행해야 합니다.
7. 트랜젝션이 지원하지 않는 테이블에 PROCEDURE , FUNCTION , 다중 쿼리문 에서 수행되는 INSERT연산의 속도를 향상 시키기 위해서는 테이블 잠금을 실행하여 속도를 향상 시킬 수 있습니다.
인덱스 버퍼 플러시를 매번 수행하지 않고 모든 INSERT 구문의 모든 열이 삽입 되었을때 인덱스 버퍼 플러시가 이루어져 성능향상이 됩니다.
// 테이블의 WRITE 잠금을 수행합니다.
LOCK TABLES `테이블` WRITE;
// 대량 INSERT 구문을 수행합니다.
INSERT INTO `테이블` VALUES ('값1', '값2')....;
.....
INSERT INTO `테이블` VALUES ('값1', '값2')....;
// 테이블의 WRITE 잠금을 해제 합니다.
UNLOCK TABLES `테이블` WRITE;
트랜젝션이 지원하는 테이블에서는 START TRANSACTION 과 COMMIT을 활용하면 성능이 향상이 됩니다.
8. Buffer Size 의 조절을 통해 성능 향상
테이블에 Insert 시 Index가 정렬되어 들어온다는 보장이 없기 때문에 이는 B트리 구조화시 추가적인 I/O를 수반합니다.
디스크에 데이터를 읽고 쓰고 하는 부가적인 동작이 레코드가 많아질수록, Buffer 크기를 넘어설수록 많이 수반되기 때문입니다.
이를 막기 위해서 InnoDB의 경우 Buffer Size를 크게 잡는다면 이 만큼의 메모리를 추가로 캐싱용 버퍼풀을 위해 사용하는 대신 디스크 I/O의 비용을 높은 비율로 줄일 수 있습니다.
잔여 메모리의 50~80% 만큼 조절하는 게 정석이며 그 이상으로 조절 시 오히려 하드디스크를 가상 메모리로 쓰기 위한 스와핑 작업이 발생하기 때문에 성능이 저하된다고 알려져있습니다.
실제로 이부분은 범위 내라면, 조절하는 만큼 성능이 향상됩니다.
바꾸기 위해서는 my.cnf 설정 내에서 [mysql] 하위 항목으로 innodb_buffer_pool_size=1024M 과 같이 입력하면 됩니다.
버퍼 풀 메모리가 충분히 큰 양으로 할당되어 있다면 innodb는 in-memory 데이터베이스처럼 동작합니다.
Access를 위한 select 데이터 뿐 아니라, Insert 및 Update 작업에도 도움이 되는 캐싱을 하기 때문에 적절하 조정하여 사용하는 것이 핵심입니다.
참고자료
https://jins-dev.tistory.com/entry/MySQL-에서의-Bulk-Inserting-성능-향상
https://mariadb.com/kb/en/how-to-quickly-insert-data-into-mariadb/
https://blog.naver.com/islove8587/222326263425
https://openuiz.tistory.com/37
특히 API 에서 조회하는 DB에 배치 서버가 수시로 데이터를 갱신해주는 경우나,
막대한 양의 메시지 데이터를 처리하게 될 경우가 있는데,
DB가 커질 수록 단순 쿼리로만 작업하면 퍼포먼스에 있어 엄청난 디버프를 받게 됩니다.
대량 데이터 insert(삽입) 할때 시간이 걸리는 작업
데이터를 디스크에 동기화 (트랜젝션 종료의 일부로)
새 키 추가. 인덱스가 클수록 키를 업데이트 하는데 더 많은 시간이 걸림
외래 키를 확인 (존재하는 경우)
스토리지 엔진에 행 추가
서버로 데이터를 보내는 중
1. Using Big Transactions
한번에 많은 삽입을 수행할때 모든 행에 대해 전체 트랜젝션(디스크 동기화 포함)을 수행하지 않기 위해, 이름 BEGIN / END 로 매핑을 합니다
예를 들어, 1000개의 Insert 마다 BEGIN / END 를 하면 INSERT 속도가 거의 1000배 빨리집니다
BEGIN;
INSERT ...;
INSERT ...;
END;
BEGIN;
INSERT ...;
INSERT ...;
END;
2. Multi-Value Inserts
여러 개의 Insert 구문 수행시 Values 리스트를 다중으로 사용합니다.
다수의 동일한 Insert query에 대해서 다음과 같이 최적화가 가능합니다.
Insert into `테이블` values(a, b, c);
Insert into `테이블` values(d, e, f);
위와 같이 쿼리를 날린다고 할때, 이를 다음과 같이 바꿉니다.
Insert into `테이블` values(a, b, c), (d, e, f) ...;
이렇게 되면 성능이 기하급수적으로 향상되며, 이는 JDBC 혹은 클라이언트 등의 연결 설정에서 RewriteBatchedStatements=true 와 같은 속성을 주어 자동으로 최적화시킬 수 있습니다.
(물론 설정의 경우 실수의 우려도 있고, 개발자가 한눈에 파악하기 쉽지 않기 때문에 협업 시에는 코드 레벨에서 처리하는게 더 좋습니다.)
또한 이 쿼리 튜닝을 사용할 때에는 비어있지 않은 Table에 Insert 시 my.cnf 파일 내에 있는 bulk_insert_buffer_size를 변경하여 속도 개선이 가능합니다.
(수정 방법은 /etc/mysql/my.cnf 파일을 열어서 [mysqld] 항목 아래에 bulk_insert_buffer_size=256M 과 같이 설정합니다.)
3. Inserting Data Into Serveral Tables at Once
한번에 여러 테이블에 데이터를 삽입하는 경우 가장 좋은 방법은 다중 행 문을 활성화 하고 한번에 많은 삽입을 서버에 보내는 것입니다
insert into `테이블1` (a, b, c) values ('1', '2', '3');
insert into `테이블2` (a, b, c, d) values ('1', '2', '3', '4');
4. 여러 클라이언트에서 Insert 시 Insert Delayed 를 통해 속도 개선이 가능합니다.
여러 클라이언트에서 대량의 데이터를 삽입을 한다면 INSERT DELAYED 명령문을 사용하면 속도를 개선 할 수 있습니다.
서버로 부터 수행 응답을 받은 후 큐에 적재 후 테이블에 사용이 되지 않을때 테이블에 삽입이 됩니다.
INSERT DELAYED 는 MyISAM, MEMORY, ARCHIVE 테이블에서 사용 가능합니다.
그외 테이블에서는 성능 저하가 발생 할 수 있습니다.
affected row 값은 테이블이 바쁘지 않을때는 정확한 값을 출력을 하나 그렇지 않을때는 1의 값을 반환을 합니다.
DELAYED 이외에 LOW_PRIORITY, IGNORE, HIGI_PRIORITY 등을 이용하여 작업의 순위, 중복 오류등을 무시 할 수 있습니다.
LOW_PRIORITY : 최 하위 순위로 INSERT구문을 수행합니다. 다른 클라이언트의 SELECT가 종료된 후 수행합니다.
IGNORE : 키 중복 (PRIMARY KEY, UNIQUE KEY)의 오류를 무시하고 진행합니다.
HIGH_PRIORITY : 최 우선 작업으로 INSERT구문을 수행합니다.
INSERT DELAYED 구문을 수행할때에는 Slave replaction 와는 다른 데이터를 가질 수 있습니다.
INSERT DELAYED INTO `테이블` (필드1, 필드2) VALUES ('값1', '값2'),('값1', '값2')...('값1', '값2');
5. 파일 스트림으로부터 대량의 데이터를 삽입 시 Load Data Local Infile 구문을 이용해서 필드 구문자로 정리된 File을 MySQL DB로 Redirection 시킬 수 있습니다.
각각에 대한 Insert 구문 수행보다 빠른 퍼포먼스를 보입니다.
(ex) Delimeter가 "|" 이고 라인 개행 단위로 레코드가 기록되어 있을 때,
LOAD DATA LOCAL INFILE '파일경로' [REPLACE | IGNORE] INTO TABLE `테이블` FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
단, 이 경우 5.5 이상 버전에서는 LOCAL을 꼭 붙여야 에러가 나지 않으며, 이를 이용하기 위해 설정 옵션에 [mysql] 아래에 local-infile을, [mysqld] 아래에 local-infile을 추가해주어야 합니다.
6. Index가 많이 사용된 테이블에 대량으로 Insert 시 Index를 비활성화한 후 수행합니다.
매 Row 마다 계산 후 삽입하는 것보다 전체 키를 비활성화하고 데이터 입력이 종료되고 다시 키를 활성화시키는 것이 빠른 수행을 할 수 있습니다.
ALTER TABLE `테이블` DISABLE KEYS;
Insert 구문 수행
ALTER TABLE `테이블` ENABLE KEYS;
여기서 Key 란 INDEX 뿐 아니라 FOREIGN KEY 참조도 포함합니다.
실제로 Bulk Inserting 이 중요하다면, 체크를 꺼두는 것이 성능 향상에 큰 도움이 됩니다.
물론 이 경우 단점은, API 로직상 에러가 발생하여 문제가 될 수 있는 데이터가 삽입될 시, 실시간으로 정합성 체크가 불가능합니다.
데이터를 신뢰할 경우에만 수행해야 합니다.
7. 트랜젝션이 지원하지 않는 테이블에 PROCEDURE , FUNCTION , 다중 쿼리문 에서 수행되는 INSERT연산의 속도를 향상 시키기 위해서는 테이블 잠금을 실행하여 속도를 향상 시킬 수 있습니다.
인덱스 버퍼 플러시를 매번 수행하지 않고 모든 INSERT 구문의 모든 열이 삽입 되었을때 인덱스 버퍼 플러시가 이루어져 성능향상이 됩니다.
// 테이블의 WRITE 잠금을 수행합니다.
LOCK TABLES `테이블` WRITE;
// 대량 INSERT 구문을 수행합니다.
INSERT INTO `테이블` VALUES ('값1', '값2')....;
.....
INSERT INTO `테이블` VALUES ('값1', '값2')....;
// 테이블의 WRITE 잠금을 해제 합니다.
UNLOCK TABLES `테이블` WRITE;
트랜젝션이 지원하는 테이블에서는 START TRANSACTION 과 COMMIT을 활용하면 성능이 향상이 됩니다.
8. Buffer Size 의 조절을 통해 성능 향상
테이블에 Insert 시 Index가 정렬되어 들어온다는 보장이 없기 때문에 이는 B트리 구조화시 추가적인 I/O를 수반합니다.
디스크에 데이터를 읽고 쓰고 하는 부가적인 동작이 레코드가 많아질수록, Buffer 크기를 넘어설수록 많이 수반되기 때문입니다.
이를 막기 위해서 InnoDB의 경우 Buffer Size를 크게 잡는다면 이 만큼의 메모리를 추가로 캐싱용 버퍼풀을 위해 사용하는 대신 디스크 I/O의 비용을 높은 비율로 줄일 수 있습니다.
잔여 메모리의 50~80% 만큼 조절하는 게 정석이며 그 이상으로 조절 시 오히려 하드디스크를 가상 메모리로 쓰기 위한 스와핑 작업이 발생하기 때문에 성능이 저하된다고 알려져있습니다.
실제로 이부분은 범위 내라면, 조절하는 만큼 성능이 향상됩니다.
바꾸기 위해서는 my.cnf 설정 내에서 [mysql] 하위 항목으로 innodb_buffer_pool_size=1024M 과 같이 입력하면 됩니다.
버퍼 풀 메모리가 충분히 큰 양으로 할당되어 있다면 innodb는 in-memory 데이터베이스처럼 동작합니다.
Access를 위한 select 데이터 뿐 아니라, Insert 및 Update 작업에도 도움이 되는 캐싱을 하기 때문에 적절하 조정하여 사용하는 것이 핵심입니다.
참고자료
https://jins-dev.tistory.com/entry/MySQL-에서의-Bulk-Inserting-성능-향상
https://mariadb.com/kb/en/how-to-quickly-insert-data-into-mariadb/
https://blog.naver.com/islove8587/222326263425
https://openuiz.tistory.com/37
댓글목록
등록된 댓글이 없습니다.