14.8.3.1 Configuring InnoDB Buffer Pool Size — MySQL 5.7

14.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. |
+----------------------------------+----------------------------------+

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

[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.

Внутреннее устройство онлайн-изменения размера кэша буфера

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

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

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

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

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

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