14.8.3.5 Configuring Buffer Pool Flushing — MySQL 5.7

14.8.3.5 Настройка сброса буфера пула

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

В MySQL 5.7 сброс буфера пула выполняется потоками очистки страниц. Количество потоков очистки страниц контролируется переменной innodb_page_cleaners, которая имеет значение по умолчанию 4. Однако, если количество потоков очистки страниц превышает количество экземпляров буфера пула, innodb_page_cleaners автоматически устанавливается в то же значение, что и innodb_buffer_pool_instances.

Сброс буфера пула инициируется, когда процент измененных страниц достигает значения нижней границы, определённой переменной innodb_max_dirty_pages_pct_lwm. Значение нижней границы по умолчанию равно 0, что отключает это поведение раннего сброса.

Цель порога innodb_max_dirty_pages_pct_lwm — контролировать процент изменённых страниц в буфере пуле и предотвращать превышение количества изменённых страниц порога, определённого переменной innodb_max_dirty_pages_pct, которая имеет значение по умолчанию 75. InnoDB активно сбрасывает страницы буфера пула, если процент изменённых страниц в буфере пула достигает порога innodb_max_dirty_pages_pct.

При настройке innodb_max_dirty_pages_pct_lwm значение должно быть всегда ниже значения innodb_max_dirty_pages_pct.

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

Переменная innodb_flush_neighbors определяет, будет ли сброс одной страницы из буфера пула также приводить к сбросу других изменённых страниц в том же объёме.
- Значение 0 отключает innodb_flush_neighbors. Изменённые страницы в том же объёме не сбрасываются.
- Значение по умолчанию 1 сбрасывает смежные изменённые страницы в том же объёме.
- Значение 2 сбрасывает изменённые страницы в том же объёме.
Когда данные таблицы хранятся на традиционном устройстве хранения, сброс соседних страниц в одной операции снижает издержки ввода-вывода (в первую очередь для операций поиска на диске) по сравнению со сбросом отдельных страниц в разное время. Для табличных данных, хранящихся на ..., время поиска не является существенным фактором, и вы можете отключить эту настройку, чтобы распределить операции записи.
Переменная innodb_lru_scan_depth задаёт, на сколько далеко вниз по списку LRU буфера пула поток очистки страниц ищет изменённые страницы для сброса. Это фоновая операция, выполняемая потоком очистки страниц один раз в секунду.

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

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

Переменные innodb_flush_neighbors и innodb_lru_scan_depth предназначены в первую очередь для рабочих нагрузок с интенсивными операциями записи. При интенсивной работе с DML сброс может отставать, если он недостаточно активный, или запись на диск может насытить ресурсы ввода-вывода, если сброс слишком активный. Идеальные значения зависят от вашей рабочей нагрузки, шаблонов доступа к данным и конфигурации хранилища (например, хранятся ли данные на HDD или SSD-устройствах).

Адаптивный сброс

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

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

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

Переменная innodb_adaptive_flushing_lwm определяет нижнюю границу ёмкости журнала обратной записи. Когда этот порог пересечён, адаптивный сброс включается, даже если переменная innodb_adaptive_flushing отключена.

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

innodb_flushing_avg_loops определяет количество итераций, в течение которых InnoDB сохраняет ранее рассчитанную «моментальную фотографию» состояния сброса, управляя тем, насколько быстро адаптивный сброс реагирует на изменения в фоновой рабочей нагрузке. Высокое значение innodb_flushing_avg_loops означает, что InnoDB дольше сохраняет ранее рассчитанную «моментальную фотографию», поэтому адаптивный сброс реагирует медленнее. При настройке высокого значения важно убедиться, что использование журнала обратной записи не достигает 75% (жестко заданный предел, при котором начинается асинхронный сброс), и что порог innodb_max_dirty_pages_pct поддерживает количество изменённых страниц на уровне, соответствующем рабочей нагрузке.

Системы с постоянной рабочей нагрузкой, большим размером файла журнала (innodb_log_file_size) и небольшими скачками, не достигающими 75% загрузки пространства журнала, должны использовать высокое значение innodb_flushing_avg_loops, чтобы сделать сброс максимально плавным. Для систем с экстремальными всплесками нагрузки или файлами журналов, не обеспечивающими большого пространства, меньшее значение позволяет сбросу более точно отслеживать изменения рабочей нагрузки и помогает избежать достижения 75% загрузки пространства журнала.

Имейте в виду, что если сброс отстаёт, скорость сброса буфера пула может превысить доступную ёмкость ввода-вывода для InnoDB, как определено настройкой innodb_io_capacity. Значение innodb_io_capacity_max определяет верхний предел ёмкости ввода-вывода в таких ситуациях, чтобы пик активности ввода-вывода не потреблял всю ёмкость ввода-вывода сервера.

Настройка innodb_io_capacity применима ко всем экземплярам буфера пула. При сбросе изменённых страниц ёмкость ввода-вывода делится поровну между экземплярами буфера пула.