[MySQL] Innodb Change Buffer를 통한 보조 인덱스 I/O 개선

개요

MySQL InnoDB에는 보조 인덱스 I/O 성능 향상을 위한 체인지 버퍼가 존재합니다. 해당 버퍼를 통해 어떻게 보조 인덱스 I/O 최적화가 진행되는지 알아보겠습니다.

 

체인지 버퍼

체인지 버퍼는 보조 인덱스 페이지가 버퍼 풀에 없을 때 변경 사항을 캐시하는 특수한 데이터 구조입니다. INSERT, UPDATE 등 DML 작업으로 인해 발생할 수 있는 버퍼링된 변경 사항은 추후 읽기 작업에 의해 페이지가 버퍼 풀에 로드될 때 병합됩니다.

 

클러스터형 인덱스의 경우 정렬된 순서로 삽입되는 반면 보조 인덱스의 경우 일반적으로 유니크하지 않고 정렬되지 않은 채 처리가 되기 때문에 최신 상태로 유지하기 위해서는 상당한 I/O 가 발생하게됩니다. 이러한 단점을 극복하고자 DML 작업이 발생할 경우 실시간으로 업데이트하지 않고 체인지 버퍼를 통해 변경사항을 캐시합니다.

 

1. 보조 인덱스 INSERT, UPDATE, DELETE 연산 요청 시, 해당 페이지 Buffer Pool 상 존재 여부 확인 있을 경우 버퍼에 변경 사항 적용
2. (디스크에 보조 인덱스 변경 사항을 적용)(생략)
3. Buffer Pool에 없는 페이지의 경우 Change Buffer 영역에 저장 후 추후 디스크로 플러시됨
4. 인덱스의 변경된 내용을 버퍼풀에서 읽어옴

이렇게 체인지 버퍼를 통해 디스크 I/O를 최소화 할 수 있습니다.

 

체인지 버퍼링 변수

innodb_change_buffering

SHOW variables LIKE 'innodb_change_buffering'

• all (default) : Insert, Delete, Purges
• none : 버퍼링 하지않음
• insert : Insert 버퍼링
• deletes : Delete-marking 버퍼링
• changes : insert, delete 버퍼링
• purges : 백그라운드에서 발생하는 물리적 삭제 작업을 버퍼링

 

innodb_change_buffer_max_size

SHOW variables LIKE 'innodb_change_buffer_max_size'

전체 버퍼 풀 크기에 대한 백분율 표시

기본값은 25이고 최대 50 까지 설정이 가능

 

체인지 버퍼 모니터링

mysql> SHOW ENGINE INNODB STATUS

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

 

InnoDB 체인지 버퍼 메트릭 조회

SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'

 

버퍼 풀 각 페이지에 대한 메타데이터 조회

(해당 쿼리 조회 시 대량의 데이터를 조회하기에 문제가 발생할 수 있으니 주의)

SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE

 

결론

체인지 버퍼의 경우 DML 후 보조 인덱스를 최신 상태로 유지하는데 사용되는 상당한 I/O를 줄일 수 있습니다. 다만 과거 디스크가 느린 시절에 큰 효과가 있었지만 SSD 등 디스크의 성능이 개선됨에 따라 극적인 성능 개선을 가져오지는 않습니다. 또한 변경 사항이 많을 경우 재부팅, 복구 작업이 지연될 수 있기에 주의해야합니다. 추가로 이러한 이유 때문인지 Aurora MySQL의 경우 체인지 버퍼를 사용하지 않습니다.(None으로 설정되어있으며 변경 불가)

 

 

참고

https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html