[MySQL] innoDB INSERT 속도 향상 방법

출처: http://blog.naver.com/websearch/70137947078

MySQL innoDB 에서 INSERT 하는 속도를 MyISAM 과 유사하게 변경하는 방법은 다음과 같다.

* innodb_flush_log_at_trx_commit 설정값을 확인한다.

mysql> show variables like 'innodb_flush_log_at_trx_commit';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     | 

+--------------------------------+-------+

1 row in set (0.00 sec)

* innodb_flush_log_at_trx_commit 설정값이 1 이면 아래와 같이 실행하여서 0 으로 수정한다.

  - 아래와 같이 설정하면 INSERT 할 때에 로그 파일에 기록하지 않기 때문에 INSERT 속도가 향상된다. 단, 서버 비정상 종료 또는 정전과 같은 상황에서 INSERT 한 데이터를 잃어버릴 수 있다.

mysql> set global innodb_flush_log_at_trx_commit=0;

innodb_flush_log_at_trx_commit

이 옵션은 commit 을 하였을 경우 그 즉시 commit된 데이타를 log file 에 기록할 것인지 아닌지를 설정하는 옵션입니다. 즉시 로그 파일에 기록할 경우 급작스런 정전 같은 경우 데이타 손실을 막을 수 있지만 매번 로그를 기록해야 하므로 속도가 상당히 저하됩니다.

0 으로 설정할 경우 매 트랜잭션 마다 데이타를 로그에 기록하지 않으므로 I/O부하를 줄일 수 있으며 여러 트랜잭션을 모아서 한번의 디스크 I/O로 기록하므로 I/O횟수 자체도 줄어듭니다.

[참고자료] http://www.jakartaproject.com/board-read.do?boardId=dbtip&boardNo=111223586558100&command=READ&page=1&categoryId=-1

[출처] [MySQL] innoDB INSERT 속도 향상 방법|작성자 까미유

MySQL INSERT 성능 향상

(2009/08/15 08:07 작성)

출처: http://www.lovelgw.com/Blog/225

MySQL 테이블에 INSERT 구문을 수행 시킬때 성능을 향상시킬 수 있는 방법입니다. 

여러 데이터를 INSERT 구문으로 수행시 VALUES 리스트를 다중으로 사용하는것이 개별적으로 INSERT것보다 빠릅니다. 비어 있지 않은 테이블에 INSERT를 수행한다면 my.ini 파일 안에 bulk_insert_buffer_size 를 변경하여 속도를 개선 시킬 수 있습니다. 

INSERT INTO `테이블` (필드1, 필드2) VALUES ('값1', '값2'),('값1', '값2')...('값1', '값2');

# My.ini 파일 내에 bulk insert buffer size를 변경하고 MySQL을 재시작 합니다.
bulk_insert_buffer_size = 64M 


여러 클라이언트에서 대량의 데이터를 삽입을 한다면 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');

대량의 데이터를 삽입을 할때는 LOAD DATA INFILE 을 사용합니다. 

대량의 데이터를 INSERT 구문을 이용해서 삽입을 하는것 보다, 필드 구분자로 분리가된 LOAD DATA INFILE 을 이용하면 INSERT 구문보다 매우 빠른 동작을 합니다. 

Slave Replaction 에 동일한 구문이 전달이 가능하고 INSERT가 가능합니다. 




-- 필드 구분이 , (쉼표) 로 되어 있고 라인 구분이 개행으로 되어 있을때 아래와 같이 수행하면 됩니다.

LOAD DATA INFILE '파일경로' INTO TABLE `테이블명` FIELDS TERMINATED BY ','LINES TERMINATED By '\r'

INDEX가 많이 사용된 테이블에 대량으로 INSERT 구문 시 INDEX를 비활성화 한 후 수행을 하는것이 좋습니다. 한개의 열을 삽입하고 인덱스를 계산 후 다시 다른 행을 삽입하는 것 보다는 전체 키를 비 활성화 한 후에 데이터 입력이 종료되고 다시 키를 활성화 시키는것이 전체적으로 빠른 수행을 할 수 있습니다.




-- 테이블의 INDEX를 비활성화 시킵니다.

ALTER TABLE `테이블` DISABLE KEYS;

-- 대량 INSERT 구문을 수행합니다.

INSERT INTO `테이블` VALUES ('값1', '값2')....;

.....

INSERT INTO `테이블` VALUES ('값1', '값2')....;

-- 수행이 종료된 후 다시 INDEX를 활성화 시킵니다. 

ALTER TABLE `테이블` ENABLE KEYS;

트랜젝션이 지원하지 않는 테이블에 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을 활용하면 성능이 향상이 됩니다. 

위와 같이 MySQL INSERT 수행시 성능을 향상시킬 수 있습니다. 참고 : MySQL 메뉴얼 (Speed of INSERT Statements)