17.5.2 Change Buffer — MySQL 9.2

17.5.2 Буфер изменений

Буфер изменений — это специальная структура данных, которая кеширует изменения в страницах, когда эти страницы отсутствуют в . Буферизованные изменения, которые могут возникнуть в результате операций INSERT, UPDATE или DELETE (DML), объединяются позже, когда страницы загружаются в буферный пул другими операциями чтения.

Рисунок 17.3 Буфер изменений

Content is described in the surrounding text.

В отличие от , вторичные индексы обычно не являются уникальными, а вставки в вторичные индексы происходят в относительно случайном порядке. Аналогично, удаления и обновления могут влиять на страницы вторичных индексов, которые не расположены рядом в дереве индекса. Объединение кэшированных изменений в более позднее время, когда соответствующие страницы считываются в буферный пул другими операциями, позволяет избежать значительного произвольного доступа к вводу-выводу, который потребовался бы для чтения страниц вторичного индекса в буферный пул с диска.

Периодически, операция очистки, выполняемая, когда система в основном бездействует или во время медленного завершения работы, записывает обновлённые страницы индекса на диск. Операция очистки может записывать блоки диска для ряда значений индекса более эффективно, чем если бы каждое значение записывалось на диск немедленно.

Объединение буфера изменений может занимать несколько часов, когда затронуто много строк и множество вторичных индексов требуют обновления. В течение этого времени увеличивается ввод-вывод на диск, что может вызвать значительное замедление для запросов, ограниченных диском. Объединение буфера изменений может также продолжаться после подтверждения транзакции и даже после завершения работы сервера и перезагрузки (см. Раздел 17.20.3, «Принудительное восстановление InnoDB» для получения дополнительной информации).

В оперативной памяти буфер изменений занимает часть буферного пула. На диске буфер изменений является частью системного табличного пространства, где изменения индекса буферизуются, когда сервер базы данных завершает свою работу.

Тип данных, кэшируемых в буфере изменений, регулируется переменной innodb_change_buffering. Дополнительную информацию можно найти в Настройке буферизации изменений. Также можно настроить максимальный размер буфера изменений. Дополнительную информацию см. в Настройке максимального размера буфера изменений.

Буферизация изменений не поддерживается для вторичного индекса, если индекс содержит столбец с убывающим индексом или если первичный ключ включает столбец с убывающим индексом.

Ответы на часто задаваемые вопросы о буфере изменений см. в Разделе A.16, «MySQL 9.2 FAQ: Буфер изменений InnoDB».

Настройка буферизации изменений

При выполнении операций INSERT, UPDATE и DELETE над таблицей значения индексированных столбцов (в особенности значения вторичных ключей) часто упорядочены произвольно, что требует значительного ввода-вывода для обновления вторичных индексов. Буфер кеширует изменения записей вторичных индексов, когда соответствующая страница не находится в , тем самым избегая дорогостоящих операций ввода-вывода, не загружая страницу с диска сразу. Буферизованные изменения объединяются, когда страница загружается в буферный пул, и обновленная страница позже сбрасывается на диск. InnoDB основной поток объединяет буферизованные изменения, когда сервер практически бездействует, и во время .

Поскольку это может привести к меньшему количеству чтений и записей на диск, буферизация изменений наиболее полезна для рабочих нагрузок, ограниченных вводом-выводом; например, приложения с большим объемом операций DML, такие как массовые вставки, извлекают выгоду из буферизации изменений.

Однако буфер изменений занимает часть буферного пула, уменьшая доступную память для кеширования страниц данных. Если рабочий набор почти полностью помещается в буферный пул или если ваши таблицы имеют относительно мало вторичных индексов, может быть полезно отключить буферизацию изменений. Если рабочий набор данных полностью помещается в буферный пул, буферизация изменений не накладывает дополнительной нагрузки, так как она применяется только к страницам, которые не находятся в буферном пуле.

Переменная innodb_change_buffering управляет степенью, в которой InnoDB выполняет буферизацию изменений. Можно включить или отключить буферизацию для вставок, операций удаления (когда записи индексов изначально отмечаются для удаления) и операций очистки (когда записи индексов физически удаляются). Операция обновления — это комбинация вставки и удаления. Значение по умолчанию для innodb_change_buffering — none, а допустимые значения описаны в документации к innodb_change_buffering.

Можно установить переменную innodb_change_buffering в файле опций MySQL (my.cnf или my.ini) или изменить ее динамически с помощью оператора SET GLOBAL, для чего требуются привилегии, достаточные для настройки глобальных системных переменных. См. Раздел 7.1.9.1, «Привилегии на системные переменные». Изменение настройки влияет на буферизацию новых операций; объединение существующих буферизованных записей не затрагивается.

Настройка максимального размера буфера изменений

Переменная innodb_change_buffer_max_size позволяет настроить максимальный размер буфера изменений в процентах от общего размера буферного пула. По умолчанию innodb_change_buffer_max_size устанавливается в 25. Максимальное значение — 50.

Увеличение innodb_change_buffer_max_size на сервере MySQL с интенсивными операциями вставки, обновления и удаления, где объединение буфера изменений не успевает за новыми записями в буфер изменений, что приводит к достижению максимального предела размера буфера изменений.

Уменьшение innodb_change_buffer_max_size на сервере MySQL со статическими данными, используемыми для отчетов, или если буфер изменений потребляет слишком много памяти, совместно используемой с буферным пулом, что приводит к более быстрому вытеснению страниц из буферного пула, чем хотелось бы.

Протестируйте различные настройки с репрезентативной рабочей нагрузкой, чтобы определить оптимальную конфигурацию. Переменная innodb_change_buffer_max_size динамическая, что позволяет изменять настройку без перезапуска сервера.

Мониторинг буфера изменений

Доступны следующие варианты для мониторинга буфера изменений:

Стандартный вывод монитора включает информацию о состоянии буфера изменений. Для просмотра данных монитора выполните команду SHOW ENGINE INNODB STATUS.
```
mysql> SHOW ENGINE INNODB STATUS\G
```
Информация о состоянии буфера изменений находится под заголовком INSERT BUFFER AND ADAPTIVE HASH INDEX и имеет сходный вид с приведённым ниже:
```
-------------------------------------
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
```
Дополнительную информацию см. в разделе 17.17.3, «InnoDB Стандартный монитор и монитор блокировок».
Таблица схемы информации INNODB_METRICS предоставляет большинство точек данных, найденных в выводе стандартного монитора InnoDB, а также другие точки данных. Для просмотра метрик буфера изменений и описания каждой из них выполните следующий запрос:
```
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
```
См. раздел 17.15.6, «Таблица метрик InnoDB INFORMATION_SCHEMA».
Таблица схемы информации INNODB_BUFFER_PAGE предоставляет метаданные о каждой странице в кэше страниц, включая индекс буфера изменений и страницы битовой карты буфера изменений. Страницы буфера изменений идентифицируются с помощью PAGE_TYPE. IBUF_INDEX — тип страницы для страниц индекса буфера изменений, а IBUF_BITMAP — тип страницы для страниц битовой карты буфера изменений.

Предупреждение

Обращение к таблице INNODB_BUFFER_PAGE может привести к значительной нагрузке на производительность. Чтобы избежать негативного влияния на производительность, воспроизведите проблему, которую вы хотите исследовать, на тестовом экземпляре и выполните запросы на тестовом экземпляре.

Например, вы можете запросить таблицу INNODB_BUFFER_PAGE, чтобы определить приблизительное количество страниц IBUF_INDEX и IBUF_BITMAP в процентах от общего числа страниц кэша страниц.
```
mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100))
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+
```
Дополнительную информацию о других данных, предоставляемых таблицей INNODB_BUFFER_PAGE, см. в разделе 28.4.2, «Таблица INFORMATION_SCHEMA INNODB_BUFFER_PAGE». Связанная информация по использованию представлена в разделе 17.15.5, «Таблицы буфера кэша InnoDB INFORMATION_SCHEMA».
Performance Schema предоставляет инструменты мониторинга ожиданий мьютексов буфера изменений для расширенного мониторинга производительности. Для просмотра инструментов мониторинга буфера изменений выполните следующий запрос:
```
mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
+-------------------------------------------------------+---------+-------+
```
Дополнительную информацию о мониторинге ожиданий мьютексов InnoDB см. в разделе 17.16.2, «Мониторинг ожиданий мьютексов InnoDB с помощью Performance Schema».