17.8.3.1 Configuring InnoDB Buffer Pool Size — MySQL 9.2

17.8.3.1 Настройка размера кэша буфера InnoDB

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

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

Размер кэша буфера всегда должен быть равен или кратен 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

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 онлайн

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

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