[MySQL internal] Change buffer 살펴보기

안녕하세요, 대담이입니다!

 

오랜만에 MySQL 인터널에 대해 정리해보겠습니다. MySQL의 메모리 구조 중, Change buffer입니다. 먼저, 이에 대한 더 자세한 내용은 아래 문서를 통해 자세히 확인하실 수 있습니다.

 

체인지 버퍼 개요

 

체인지 버퍼는 세컨더리 인덱스 페이지들의 변화를 캐시하는 메모리 영역인데요 이는 해당 페이지들이 버퍼풀에 있지 않을 때 사용되고, 이는 만약 쿼리에서 DML을 수행한 경우, 세컨더리 인덱스의 변경을 저장하여 이를 향후 정기적으로 머지하고, 페이지가 버퍼풀로 읽기 작업에 의해 로드될 때 머지됩니다.

즉, 세컨더리 인덱스와 관련된 데이터페이지가 버퍼풀에 없을 경우에는 변화는 체인지 버퍼에만 저장되고, 만약 해당 인덱스 페이지가 체인지 버퍼에 있을 때, 페이지에 있는 데이터가 체인지버퍼로부터 버퍼 풀로 적용(머지) 됩니다. 

그리고 퍼지 작업을 통하여 세컨더리인덱스의 변화가 디스크로 적혀집니다. 이는 보통 idle, slow shultdown 등의 작업에 일어납니다. 이로 인하여 디스크 IO를 효율적으로 가져갈 수 있는 것이죠.

 

이 체인지 버퍼는 유니크 키에서는 사용되지 않으며, 몇가지 변수를 통하여 설정 가능합니다.

 

 

관련 변수값 설정

 

innodb_change_buffer 변수의 값은 아래와 같습니다.

• all : 기본값으로, 버퍼 insert, delete-marking, purge 작업을 모두 수행합니다.
• none : 체인지 버퍼작업을 진행하지 않습니다.
• inserts : 인서트 작업을 버퍼합니다.
• deletes : delete-marking 작업을 버퍼합니다.
• changes : insert, delete-marking 작업을 버퍼합니다.
• purges : 백그라운드에서 진행되는 물리 삭제 작업을 버퍼합니다.

이에 대한 버퍼값을 innodb_change_buffer_max_size 변수를 통하여 최댓값을 정할 수 있으며, 기본값을 25퍼센트이고 최대 50%까지 설정하실 수 있습니다. 이는 높은 DML 오퍼레이션 작업에서 해당 값을 높이기를 고려하실 수 있습니다. 이와 반대로 정적으로 데이터를 사용하거나, 체인지 버퍼가 메모리 영역을 너무많이 사용하는 경우 줄일 수있습니다.

 

체인지 버퍼 모니터링

체인지 버퍼는 SHOW ENGINE INNODB STATUS 명령의 출력밧을 통하여 모니터링할 수 있으며, INSERT BUFFER AND ADAPTIVE HASH INDEX 섹션을 통해 확인하실 수 있습니다. 또한 아래 명령을 통해서도 관련 지표를 모니터링하실 수 있습니다.

 

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

 

 

 

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

MySQL :: MySQL 5.7 Reference Manual :: 14.5.2 Change Buffer

 

 

이상 MySQL의 Change buffer의 아키텍쳐를 안내드렸으며, 퍼포먼스 이슈시 만약 워크로드 및 지표에서 해당 메모리 지표의 변경이 필요한 경우, 이 내용을 통하여 변경 및 fine tuning을 고려하시길 추천드려요

 

이상, 대담이였습니다 :)