Изменение innodb_buffer_pool_size на лету

На боевом сервере с БД в продакшене демон MySQL отожрал всю память + swap после правки конфига. Грешить можно было только на не оптимальное значение параметра innodb_buffer_pool_size*, значение которого для сервера с 23 Гб ОЗУ было выставлено в 20 Гб – что довольно-таки многовато.

*было известно, что это значение менялось. В любых других случаях может быть что угодно.

Решено было менять innodb_buffer_pool_size на лету без перезагрузки, т.к. остановка была нежелательна.

Применимо только к MySQL версии не ниже 5.7!

Если обратиться к официальной документации, значение innodb_buffer_pool_size должно быть равным или кратным innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances и составлять от 50-75% от ОЗУ. Смотрим текущие значения и подбираем оптимальное.

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

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

Оптимальным для 23 Гб ОЗУ будет значение в 16 Гб или 16*1024^3 = 17179869184 байт.

Проверяем, что данное значение подходит под текущие: innodb_buffer_pool_size кратно или равно (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances):

17179869184 / (134217728 * = 16

Всё отлично, значение кратно, а значит подходит. В целом, даже если будет допущена ошибка при расчётах, MySQL сам округлит значение до правильного кратного, но лучше сделать это самому, чтобы понимать, что и как нужно сделать.

Осталось лишь применить его на горячую в самой базе:

SET GLOBAL innodb_buffer_pool_size=17179869184;

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