17.5.2 Change Buffer

17.5.2 Change Buffer

change buffer는 secondary index 페이지가 buffer pool에 존재하지 않을 때, 해당 페이지에 대한 변경 내용을 캐시하는 특수한 데이터 구조입니다. 버퍼링된 변경 사항은 INSERT, UPDATE, DELETE 작업(DML)에서 비롯될 수 있으며, 이러한 변경 내용은 다른 읽기 작업에 의해 해당 페이지가 buffer pool로 로드될 때 나중에 머지됩니다.

Figure 17.3 Change Buffer

clustered indexes와 달리, secondary index는 보통 nonunique이며, secondary index에 대한 insert는 상대적으로 임의의 순서로 발생합니다. 마찬가지로, delete와 update는 인덱스 트리에서 인접하게 위치하지 않은 secondary index 페이지에 영향을 줄 수 있습니다. 영향을 받는 페이지가 다른 작업에 의해 buffer pool로 읽혀질 때 나중에 캐싱된 변경 내용을 머지하는 방식은, 디스크에서 secondary index 페이지를 buffer pool로 읽어들이기 위해 요구되는 상당한 랜덤 액세스 I/O를 피하게 해 줍니다.

정기적으로, 시스템이 대부분 유휴 상태일 때 실행되거나 slow shutdown 중에 실행되는 purge 작업이 업데이트된 인덱스 페이지를 디스크에 기록합니다. purge 작업은 각 값을 즉시 디스크에 기록하는 것보다 일련의 인덱스 값에 대해 디스크 블록을 더 효율적으로 기록할 수 있습니다.

변경 대상 row가 많고 업데이트해야 할 secondary index가 수많은 경우, change buffer 머지에는 몇 시간이 걸릴 수도 있습니다. 이 기간 동안 디스크 I/O는 증가하며, 디스크 바운드 쿼리에 상당한 성능 저하를 야기할 수 있습니다. 또한 change buffer 머지는 트랜잭션이 커밋된 후에도, 그리고 서버를 shutdown 및 재시작한 후에도 계속 발생할 수 있습니다(자세한 내용은 Section 17.20.3, “Forcing InnoDB Recovery”를 참조하십시오).

메모리 상에서 change buffer는 buffer pool의 일부를 차지합니다. 디스크 상에서 change buffer는 system tablespace의 일부로 존재하며, 데이터베이스 서버가 shutdown될 때 인덱스 변경 내용이 이곳에 버퍼링됩니다.

change buffer에 캐시되는 데이터 유형은 innodb_change_buffering 변수에 의해 제어됩니다. 자세한 내용은 Configuring Change Buffering을 참조하십시오. 또한 change buffer의 최대 크기를 설정할 수 있습니다. 자세한 내용은 Configuring the Change Buffer Maximum Size를 참조하십시오.

인덱스에 descending 인덱스 컬럼이 포함되어 있거나 primary key에 descending 인덱스 컬럼이 포함되어 있는 경우, 해당 secondary index에 대해서는 change buffering이 지원되지 않습니다.

change buffer에 대한 자주 묻는 질문의 답변은 Section A.16, “MySQL 9.5 FAQ: InnoDB Change Buffer”를 참조하십시오.

Configuring Change Buffering

테이블에 대해 INSERT, UPDATE, DELETE 작업을 수행할 때, 인덱스 컬럼 값(특히 secondary key 값)은 종종 정렬되지 않은 순서로 존재하여, secondary index를 최신 상태로 유지하기 위해 상당한 I/O가 필요합니다.

change buffer는 관련 page가 buffer pool에 존재하지 않을 때 secondary index 엔트리에 대한 변경 내용을 캐싱하여, 디스크에서 페이지를 즉시 읽어들이지 않음으로써 비용이 큰 I/O 작업을 피합니다. 버퍼링된 변경 내용은 페이지가 buffer pool로 로드될 때 머지되며, 업데이트된 페이지는 나중에 디스크로 플러시됩니다. InnoDB 메인 스레드는 서버가 거의 유휴 상태일 때와 slow shutdown 동안 버퍼링된 변경 내용을 머지합니다.

디스크 읽기와 쓰기를 줄일 수 있으므로, change buffering은 I/O 바운드 워크로드에서 가장 큰 가치를 발휘합니다. 예를 들어, 대량 insert와 같은 높은 수준의 DML 작업이 있는 애플리케이션은 change buffering의 이점을 누릴 수 있습니다.

그러나 change buffer는 buffer pool의 일부를 차지하므로, 데이터 페이지를 캐싱하는 데 사용할 수 있는 메모리가 줄어듭니다. 워킹 세트가 buffer pool에 거의 들어맞거나 테이블에 상대적으로 적은 수의 secondary index만 있는 경우, change buffering을 비활성화하는 것이 유용할 수 있습니다. 워킹 데이터 세트가 전적으로 buffer pool에 들어맞는 경우, change buffering은 buffer pool에 존재하지 않는 페이지에만 적용되므로 추가적인 오버헤드를 부과하지 않습니다.

innodb_change_buffering 변수는 InnoDB가 change buffering을 수행하는 정도를 제어합니다. insert, delete 작업(처음에 인덱스 레코드가 삭제 대상으로 표시될 때), purge 작업(인덱스 레코드가 물리적으로 삭제될 때)에 대한 버퍼링을 활성화하거나 비활성화할 수 있습니다. update 작업은 insert와 delete의 조합입니다. 기본 innodb_change_buffering 값은 none이며, 허용되는 값은 innodb_change_buffering 문서에 설명되어 있습니다.

innodb_change_buffering 변수는 MySQL 옵션 파일(my.cnf 또는 my.ini)에서 설정하거나, 글로벌 시스템 변수를 설정할 수 있을 만큼 충분한 권한이 필요한 SET GLOBAL 문을 사용해 동적으로 변경할 수 있습니다. Section 7.1.9.1, “System Variable Privileges”를 참조하십시오. 설정을 변경하면 새로운 작업에 대한 버퍼링에 영향을 주며, 이미 버퍼링된 엔트리의 머지에는 영향을 주지 않습니다.

Configuring the Change Buffer Maximum Size

innodb_change_buffer_max_size 변수는 change buffer의 최대 크기를 buffer pool 전체 크기에 대한 백분율로 설정할 수 있게 해 줍니다. 기본적으로 innodb_change_buffer_max_size는 5로 설정됩니다. 최대 설정 값은 50입니다.

insert, update, delete 활동이 많은 MySQL 서버에서, change buffer 머지가 새로운 change buffer 엔트리 생성 속도를 따라가지 못해 change buffer가 최대 크기 한도에 도달하는 경우, innodb_change_buffer_max_size를 증가시키는 것을 고려하십시오.

리포팅에 사용되는 정적인 데이터를 가진 MySQL 서버이거나, change buffer가 buffer pool과 공유하는 메모리 공간을 지나치게 많이 소비하여 페이지가 원하는 것보다 더 빨리 buffer pool에서 제거되는 경우, innodb_change_buffer_max_size를 줄이는 것을 고려하십시오.

대표적인 워크로드로 서로 다른 설정을 테스트하여 최적의 구성을 결정하십시오. innodb_change_buffer_max_size 변수는 다이내믹이므로, 서버를 재시작하지 않고 설정을 수정할 수 있습니다.

Monitoring the Change Buffer

다음 옵션들을 사용하여 change buffer를 모니터링할 수 있습니다:

• InnoDB Standard Monitor 출력에는 change buffer 상태 정보가 포함됩니다. 모니터 데이터를 보려면 SHOW ENGINE INNODB STATUS 문을 실행합니다.

1mysql> SHOW ENGINE INNODB STATUS\G

change buffer 상태 정보는 INSERT BUFFER AND ADAPTIVE HASH INDEX 제목 아래에 위치하며 다음과 유사하게 표시됩니다:

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

자세한 내용은 Section 17.17.3, “InnoDB Standard Monitor and Lock Monitor Output”를 참조하십시오.

• Information Schema INNODB_METRICS 테이블은 InnoDB Standard Monitor 출력에서 제공되는 대부분의 데이터 포인트와 그 밖의 데이터 포인트를 제공합니다. change buffer 메트릭과 각 항목에 대한 설명을 보려면 다음 쿼리를 실행하십시오:

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

Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”를 참조하십시오.

• Information Schema INNODB_BUFFER_PAGE 테이블은 buffer pool 내 각 페이지에 대한 메타데이터를 제공하며, 여기에는 change buffer 인덱스 페이지와 change buffer 비트맵 페이지도 포함됩니다. change buffer 페이지는 PAGE_TYPE으로 식별됩니다. IBUF_INDEX는 change buffer 인덱스 페이지의 페이지 타입이고, IBUF_BITMAP은 change buffer 비트맵 페이지의 페이지 타입입니다.

주의

INNODB_BUFFER_PAGE 테이블에 대한 쿼리는 상당한 성능 오버헤드를 유발할 수 있습니다. 성능에 영향을 주지 않기 위해, 조사하려는 문제를 테스트 인스턴스에서 재현하고, 해당 쿼리를 테스트 인스턴스에서 실행하십시오.

예를 들어, INNODB_BUFFER_PAGE 테이블에 대한 쿼리를 실행하여 전체 buffer pool 페이지 대비 IBUF_INDEX 및 IBUF_BITMAP 페이지의 대략적인 개수 비율을 확인할 수 있습니다.

1mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
2         WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
3         (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
4         (SELECT ((change_buffer_pages/total_pages)*100))
5         AS change_buffer_page_percentage;
6+---------------------+-------------+-------------------------------+
7| change_buffer_pages | total_pages | change_buffer_page_percentage |
8+---------------------+-------------+-------------------------------+
9|                  25 |        8192 |                        0.3052 |
10+---------------------+-------------+-------------------------------+

INNODB_BUFFER_PAGE 테이블에서 제공하는 다른 데이터에 대한 정보는 Section 28.4.2, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”를 참조하십시오. 관련 사용 정보는 Section 17.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”를 참조하십시오.

• Performance Schema는 고급 성능 모니터링을 위해 change buffer mutex wait 인스트루멘테이션을 제공합니다. change buffer 인스트루멘테이션을 보려면 다음 쿼리를 실행하십시오:

1mysql> SELECT * FROM performance_schema.setup_instruments
2         WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
3+-------------------------------------------------------+---------+-------+
4| NAME                                                  | ENABLED | TIMED |
5+-------------------------------------------------------+---------+-------+
6| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
7| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
8| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
9+-------------------------------------------------------+---------+-------+

InnoDB mutex wait 모니터링에 대한 정보는 Section 17.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”를 참조하십시오.