Изменение InnoDB buffer pool в MySQL

Приведу пример изменения размера InnoDB buffer pool в MySQL.
В InnoDB buffer pool — это пространство памяти в котором хранятся индексы, кеши, буферы и т.д.

Подключимся к MySQL и посмотрим текущее значение InnoDB buffer pool (по умолчанию 134217728, это 128 мегабайт):

mysql
SELECT @@innodb_buffer_pool_size;
SELECT @@innodb_buffer_pool_chunk_size;
SELECT @@innodb_buffer_pool_instances;
SHOW ENGINE INNODB STATUS;
show global status like '%innodb_buffer_pool_pages%';

Посчитаем примерное значение:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Допустим база данных 1 гигабайт, тогда увеличим InnoDB buffer pool до 2 гигабайт (значение нужно указывать примерно равное innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances и желательно больше размера базы данных, а также чтобы хватало оперативной памяти):

SET GLOBAL innodb_buffer_pool_size=2147483648;
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

Также изменим innodb_buffer_pool_instances, обычно равно количеству гигабайт InnoDB buffer pool, если innodb_buffer_pool_size равен 2 Гб, то innodb_buffer_pool_instances укажем не более 2, обычно каждая инстанция не должна быть менее 1Гб.
Так как переменная только для чтения, то следующей командой и без перезапуска mysql сервера не получится изменить значение:

SET GLOBAL innodb_buffer_pool_instances=2;

Чтобы изменения не сбросились после перезапуска MySQL сервера, откроем файл конфигурации в текстовом редакторе:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

И после блока mysqld, добавим:

innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2

Перезапустим MySQL сервер чтобы применить значение innodb_buffer_pool_instances:

sudo service mysql restart

Если Buffer pool hit rate 1000/1000, значит все ок:

SHOW ENGINE InnoDB STATUS\G

Смотрите также мои статьи:
Изменение InnoDB log file size
Установка и использование mysqltuner.pl

Оставьте комментарий

Добавить комментарий