14.5.2 Change Buffer — MySQL 5.7

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

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

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

Content is described in the surrounding text.

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

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

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

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

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

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

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

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

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

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

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

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

Допустимые значения innodb_change_buffering включают:

all

Значение по умолчанию: буферизация вставок, операций помечания на удаление и операций очистки.
none

Не буферизовать никакие операции.
inserts

Буферизовать операции вставки.
deletes

Буферизовать операции помечания на удаление.
changes

Буферизовать как вставки, так и операции помечания на удаление.
purges

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

Вы можете установить переменную innodb_change_buffering в файле параметров MySQL (my.cnf или my.ini) или изменить её динамически с помощью оператора SET GLOBAL, который требует привилегий для установки глобальных системных переменных. См. Раздел 5.1.8.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
```
Дополнительную информацию см. в разделе 14.18.3, «InnoDB Standard Monitor и Lock Monitor Output».
Таблица INNODB_METRICS схемы информации предоставляет большинство точек данных, представленных в выводе InnoDB Standard Monitor, а также другие точки данных. Чтобы просмотреть метрики буфера изменений и описание каждой из них, выполните следующий запрос:
```
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
```
Для получения информации о использовании таблицы INNODB_METRICS, см. раздел 14.16.6, «InnoDB INFORMATION_SCHEMA Metrics Table».
Таблица схемы информации 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, см. в разделе 24.4.2, «Таблица INFORMATION_SCHEMA INNODB_BUFFER_PAGE». Для получения связанной информации об использовании, см. раздел 14.16.5, «InnoDB INFORMATION_SCHEMA Buffer Pool Tables».
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 см. в разделе 14.17.2, «Мониторинг ожиданий блокировок InnoDB с помощью Performance Schema».