17.5.2 Change Buffer — MySQL 8.4

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 8.4 FAQ: Буфер изменений InnoDB».

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

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

Поскольку это может привести к меньшему количеству операций чтения и записи на диск, буферизация изменений наиболее полезна для рабочих нагрузок, ограниченных операциями ввода-вывода; например, приложения с большим объёмом операций 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».