RecoPick



안녕하세요. RecoPick팀 김태현입니다. 최근 팀에서 MySQL InnoDB 를 사용하던 중 insert ignore query 실행 시 Auto-increment 관련해서 다음과 같은 경험을 하였습니다.



위 예제를 설명드리면, insert ignore 를 통해 실제 insert가 일어나지 않았음에도 auto increment를 설정한 field값이 증가한 것을 확인할 수 있습니다. 기존 MySQL 버전에서는 실제 insert가 일어나지 않았다면, auto increment 값이 증가하지 않았기 때문에 조금 의아한 상황이었습니다.


관련해서 조사를 해 본 결과, MySQL 버전 5.1.22 전후로 innodb_autoinc_lock_mode 기본값이 변경되었기 때문에 발생한 현상이었습니다.

5.1.22 이전까지는 innodb_autoinc_lock_mode default 값이 0으로 traditional mode를 사용했고, 5.1.22 이후부터는 default 값이 1인 consecutive mode가 사용되고 있습니다.

각 mode를 살펴보면 다음과 같습니다.

1. innodb_autoinc_lock_mode=0
Auto-increment lock이 테이블 레벨로 동작하며, Insert into로 실제 삽입된 경우가 아닌 업데이트 경우에는 해당 테이블의 Auto-increment 값을 증가시키지 않는 옵션입니다.

2. innodb_autoinc_lock_mode=1 
단순한 insert에 대해서는 lock을 걸지 않고, INSERT 되는 레코드 건수를 정확히 예측할 수 있을 때 사용해야 하며, latch(mutex)를 이용하여 처리합니다.

3. innodb_autoinc_lock_mode=2
Auto-increment lock을 사용하지 않고, 항상 latch(mutex)를 사용합니다.
대량의 INSERT 실행 도중 다른 커넥션에서 INSERT를 수행할 수 있어. 동시 처리 성능이 높습니다. 다만, 복제를 사용하는 경우 마스터와 슬레이브의 자동 증가 값이 달라질 가능성이 있으니 사용 시 주의가 필요합니다. 

따라서 위의 문제를 해결하기 위해서 innodb_autoinc_lock_mode 값을 기본값이 아닌 0으로 설정하여 사용하면 됩니다.
이 경우, Concurrent insert가 매우 빈번히 발생할 경우 Auto-increment lock이 병목이 될 가능성이 있습니다.

참고
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode


Posted by recopick