17.8.3.1 Configuring InnoDB Buffer Pool Size — MySQL 8.4

17.8.3.1 Настройка размера кэша InnoDB Buffer Pool

Вы можете настроить размер кэша InnoDB кэша InnoDB Buffer Pool в автономном режиме или во время работы сервера. Поведение, описанное в этом разделе, применимо к обоим методам. Дополнительную информацию о настройке размера кэша в режиме онлайн см. в разделе Настройка размера кэша InnoDB Buffer Pool в режиме онлайн.

При увеличении или уменьшении размера innodb_buffer_pool_size, операция выполняется частями. Размер части определяется параметром конфигурации innodb_buffer_pool_chunk_size, который по умолчанию равен 128M. Дополнительную информацию см. в разделе Настройка размера части кэша InnoDB Buffer Pool.

Размер кэша должен всегда быть равен или кратным значению innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Если вы настраиваете innodb_buffer_pool_size на значение, которое не равно или не кратно innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, размер кэша автоматически корректируется до значения, равного или кратного innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

В следующем примере innodb_buffer_pool_size установлен в значение 8G, а innodb_buffer_pool_instances установлен в значение 16. innodb_buffer_pool_chunk_size равен 128M, что является значением по умолчанию.

8G является допустимым значением для innodb_buffer_pool_size, поскольку 8G кратно innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, что равно 2G.

$> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           8.000000000000 |
+------------------------------------------+

В этом примере innodb_buffer_pool_size установлен в 9G, а innodb_buffer_pool_instances установлен в 16. innodb_buffer_pool_chunk_size равен 128M, что является значением по умолчанию. В этом случае 9G не кратно innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, поэтому innodb_buffer_pool_size корректируется до значения 10G, которое кратно innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

$> mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                          10.000000000000 |
+------------------------------------------+

Настройка размера части кэша InnoDB Buffer Pool

innodb_buffer_pool_chunk_size можно увеличить или уменьшить на 1 МБ (1048576 байт), но изменить его можно только при запуске, в строке командной строки или в файле конфигурации MySQL.

Командная строка:

$> mysqld --innodb-buffer-pool-chunk-size=134217728

Файл конфигурации:

[mysqld]
innodb_buffer_pool_chunk_size=134217728

При изменении innodb_buffer_pool_chunk_size действуют следующие условия:

Если новое значение innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances больше текущего размера кэша при инициализации кэша, значение innodb_buffer_pool_chunk_size усекается до innodb_buffer_pool_size / innodb_buffer_pool_instances.

Например, если кэш инициализируется размером 2GB (2147483648 байт), количество экземпляров кэша равно 4, а размер части равен 1GB (1073741824 байт), размер части усекается до значения, равного innodb_buffer_pool_size / innodb_buffer_pool_instances, как показано ниже:

$> mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4
--innodb-buffer-pool-chunk-size=1073741824;

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

Размер кэша должен всегда быть равен или кратным innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Если вы изменяете innodb_buffer_pool_chunk_size, innodb_buffer_pool_size автоматически корректируется до значения, равного или кратного innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Корректировка происходит при инициализации кэша. Это поведение демонстрируется в следующем примере:

# The buffer pool has a default size of 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

# The chunk size is also 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       134217728 |
+---------------------------------+

# There is a single buffer pool instance

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (134217728 - 1048576 = 133169152):

$> mysqld --innodb-buffer-pool-chunk-size=133169152

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       133169152 |
+---------------------------------+

# Buffer pool size increases from 134217728 to 266338304
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 266338304 |
+---------------------------+

Этот пример демонстрирует то же поведение, но с несколькими экземплярами кэша:

# The buffer pool has a default size of 2GB (2147483648 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

# The chunk size is .5 GB (536870912 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

# There are 4 buffer pool instances

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (536870912 - 1048576 = 535822336):

$> mysqld --innodb-buffer-pool-chunk-size=535822336

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       535822336 |
+---------------------------------+

# Buffer pool size increases from 2147483648 to 4286578688
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4286578688 |
+---------------------------+

Следует соблюдать осторожность при изменении innodb_buffer_pool_chunk_size, так как изменение этого значения может увеличить размер кэша, как показано в приведенных выше примерах. Прежде чем изменять innodb_buffer_pool_chunk_size, рассчитайте влияние на innodb_buffer_pool_size, чтобы убедиться, что полученный размер кэша приемлем.

Примечание

Для избежания потенциальных проблем с производительностью количество фрагментов (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) не должно превышать 1000.

Настройка размера кэша InnoDB Buffer Pool в режиме онлайн

Параметр конфигурации innodb_buffer_pool_size можно динамически установить с помощью оператора SET, что позволит вам изменить размер кэша, не перезапуская сервер. Например:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Примечание

Размер кэша должен быть равен или кратным innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Изменение этих переменных требует перезапуска сервера.

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

Примечание

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

Мониторинг процесса онлайн-изменения размера буфера пула

Переменная Innodb_buffer_pool_resize_status сообщает строковое значение, указывающее на прогресс изменения размера буфера пула; например:

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name                    | Value                            |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+

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

Переменная состояния Innodb_buffer_pool_resize_status_code сообщает код состояния, указывающий на этап операции изменения размера онлайн-буфера пула. Коды состояний включают:

0: Операция изменения размера не выполняется
1: Начало изменения размера
2: Отключение AHI (Adaptive Hash Index)
3: Вывод блоков
4: Получение глобальной блокировки
5: Изменение размера пула
6: Изменение размера хэша
7: Изменение размера завершилось ошибкой

Переменная состояния Innodb_buffer_pool_resize_status_progress сообщает процентное значение, указывающее на прогресс каждого этапа. Процентное значение обновляется после обработки каждого экземпляра буфера пула. При изменении состояния (сообщаемого Innodb_buffer_pool_resize_status_code) с одного на другое процентное значение сбрасывается до 0.

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

SELECT variable_name, variable_value
 FROM performance_schema.global_status
 WHERE LOWER(variable_name) LIKE "innodb_buffer_pool_resize%";

Прогресс изменения размера буфера пула также отображается в журнале ошибок сервера. Этот пример показывает записи, которые регистрируются при увеличении размера буфера пула:

[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.

Этот пример показывает записи, которые регистрируются при уменьшении размера буфера пула:

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate
0 pages. (253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.

Запуск сервера с --log-error-verbosity=3 записывает дополнительную информацию в журнал ошибок во время операции онлайн-изменения размера буфера пула. Дополнительная информация включает коды состояний, сообщаемые Innodb_buffer_pool_resize_status_code, и процентное значение прогресса, сообщаемое Innodb_buffer_pool_resize_status_progress.

[Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 1073741824 bytes)
[Note] [MY-013954] [InnoDB] Status code 1: Resizing buffer pool from 134217728 to 1073741824
(unit=134217728).
[Note] [MY-013953] [InnoDB] Status code 1: 100% complete
[Note] [MY-013952] [InnoDB] Status code 1: Completed
[Note] [MY-013954] [InnoDB] Status code 2: Disabling adaptive hash index.
[Note] [MY-011885] [InnoDB] disabled adaptive hash index.
[Note] [MY-013953] [InnoDB] Status code 2: 100% complete
[Note] [MY-013952] [InnoDB] Status code 2: Completed
[Note] [MY-013954] [InnoDB] Status code 3: Withdrawing blocks to be shrunken.
[Note] [MY-013953] [InnoDB] Status code 3: 100% complete
[Note] [MY-013952] [InnoDB] Status code 3: Completed
[Note] [MY-013954] [InnoDB] Status code 4: Latching whole of buffer pool.
[Note] [MY-013953] [InnoDB] Status code 4: 14% complete
[Note] [MY-013953] [InnoDB] Status code 4: 28% complete
[Note] [MY-013953] [InnoDB] Status code 4: 42% complete
[Note] [MY-013953] [InnoDB] Status code 4: 57% complete
[Note] [MY-013953] [InnoDB] Status code 4: 71% complete
[Note] [MY-013953] [InnoDB] Status code 4: 85% complete
[Note] [MY-013953] [InnoDB] Status code 4: 100% complete
[Note] [MY-013952] [InnoDB] Status code 4: Completed
[Note] [MY-013954] [InnoDB] Status code 5: Starting pool resize
[Note] [MY-013954] [InnoDB] Status code 5: buffer pool 0 : resizing with chunks 1 to 8.
[Note] [MY-011891] [InnoDB] buffer pool 0 : 7 chunks (57339 blocks) were added.
[Note] [MY-013953] [InnoDB] Status code 5: 100% complete
[Note] [MY-013952] [InnoDB] Status code 5: Completed
[Note] [MY-013954] [InnoDB] Status code 6: Resizing hash tables.
[Note] [MY-011892] [InnoDB] buffer pool 0 : hash tables were resized.
[Note] [MY-013953] [InnoDB] Status code 6: 100% complete
[Note] [MY-013954] [InnoDB] Status code 6: Resizing also other hash tables.
[Note] [MY-011893] [InnoDB] Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 1073741824.
[Note] [MY-011895] [InnoDB] Re-enabled adaptive hash index.
[Note] [MY-013952] [InnoDB] Status code 6: Completed
[Note] [MY-013954] [InnoDB] Status code 0: Completed resizing buffer pool at 220826  6:25:46.
[Note] [MY-013953] [InnoDB] Status code 0: 100% complete

Внутренние механизмы изменения размера буфера пула

Операция изменения размера выполняется фоновым потоком. При увеличении размера буфера пула операция изменения размера:

Добавляет страницы в chunks (размер блока определяется innodb_buffer_pool_chunk_size)
Преобразует таблицы хеширования, списки и указатели, чтобы использовать новые адреса в памяти
Добавляет новые страницы в список свободных страниц

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

При уменьшении размера буфера пула операция изменения размера:

Оптимизирует буферный пул и освобождает страницы
Удаляет страницы в chunks (размер блока определяется innodb_buffer_pool_chunk_size)
Преобразует таблицы хеширования, списки и указатели, чтобы использовать новые адреса в памяти

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