Динамическое изменение размера innodb_buffer_pool_size в MySQL | Блог о системном администрировании

Начиная с версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size (без перезапуска MySQL).

Как это правильно сделать — читаем ниже.

Исходные данные: Oracle MySQL 5.7.29
Задача: Изменить размер innodb_buffer_pool_size с 64M до 128M

Начиная с версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size (без перезапуска MySQL), однако необходимо помнить, что размер innodb_buffer_pool_size должен быть пропорционален innodb_buffer_pool_chunk_size

Подключимся к MySQL под root и запросим текущий размер innodb_buffer_pool_size:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024 AS 'InnoDB Buffer Pool Size in MByte';
+----------------------------------+
| InnoDB Buffer Pool Size in MByte |
+----------------------------------+
|                      64.00000000 |
+----------------------------------+
1 row in set (0.00 sec)

Размер innodb_buffer_pool_size у нас 64 МБайт.

Теперь запросим размер innodb_buffer_pool_chunk_size:

mysql> SELECT @@innodb_buffer_pool_chunk_size/1024/1024 AS 'InnoDB Buffer Pool Chunk Size in MByte';
+----------------------------------------+
| InnoDB Buffer Pool Chunk Size in MByte |
+----------------------------------------+
|                            64.00000000 |
+----------------------------------------+
1 row in set (0.00 sec)

Получили значение 64 МБайт.

Нам требуется динамически установить размер innodb_buffer_pool_size в 128M, соответственно 128M / 64M = 2, установим значение:

SET GLOBAL innodb_buffer_pool_size = (SELECT @@innodb_buffer_pool_chunk_size) * 2;

Если например нам потребуется установить размер innodb_buffer_pool_size в 1G, то расчет будет таким: 1024M / 64M = 16

Проверим для начала лог error.log, в нем должен отобразиться процесс увеличения innodb_buffer_pool_size, пример:

2020-03-08T16:34:08.748349Z 17665 [Note] InnoDB: Requested to resize buffer pool. (new size: 134217728 bytes)
2020-03-08T16:34:08.748346Z 0 [Note] InnoDB: Resizing buffer pool from 67108864 to 134217728 (unit=67108864).
2020-03-08T16:34:08.751825Z 0 [Note] InnoDB: Disabling adaptive hash index.
2020-03-08T16:34:08.758286Z 0 [Note] InnoDB: disabled adaptive hash index.
2020-03-08T16:34:08.759495Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2020-03-08T16:34:08.760689Z 0 [Note] InnoDB: Latching whole of buffer pool.
2020-03-08T16:34:08.761892Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 2.
2020-03-08T16:34:08.767128Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (4096 blocks) were added.
2020-03-08T16:34:08.768977Z 0 [Note] InnoDB: Completed to resize buffer pool from 67108864 to 134217728.
2020-03-08T16:34:08.771059Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2020-03-08T16:34:08.773007Z 0 [Note] InnoDB: Completed resizing buffer pool at 200308 21:34:08.

Еще раз запросим размер innodb_buffer_pool_size:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024 AS 'InnoDB Buffer Pool Size in MByte';
+----------------------------------+
| InnoDB Buffer Pool Size in MByte |
+----------------------------------+
|                     128.00000000 |
+----------------------------------+
1 row in set (0.00 sec)

Мы видим, что динамическое увеличение размера innodb_buffer_pool_size прошло успешно.

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

Михаил Григорьев

Профессионально занимаюсь системным администрированием Linux -серверов и баз данных (MySQL, PostgreSQL) на протяжении последних 24 лет.