Размер key_buffer_size и myisam_sort_buffer_size

Систему управления базами данных MySQL не стоит использовать со стандартными настройками. Необходимо изменять конфигурацию под проект. А главными параметрами, на которые стоит обратить внимание являются размеры буфферов. В случае использования таблиц типа MyISAM это буферы, размеры которых задаются параметрами key_buffer_size и myisam_sort_buffer_size.

Настоятельно не рекомендуем использовать тип таблиц MyISAM. Используйте всегда InnoDB. Потому что хоть у MyISAM скорость в некоторых операциях чуть-чуть выше, чем у InnoDB, но преимущество мгновенно теряется, если таблица фрагментирована. Такая таблица выполняет запросы в 3-5 раз медленнее, чем целая. При этом фрагментируется MyISAM с такой скоростью, что на рабочих проектах приходится выполнять восстановление таблиц буквально раз в неделю. Поэтому всегда используйте InnoDB.

Два параметра key_buffer_size и myisam_sort_buffer_size отвечают за буферы, которые взаимодействуют с индексами в таблицах типа MyISAM. Эти параметры содержат в себе размеры буферов. Но если на проекте не используется MyISAM (а есть только InnoDB), то не стоит их обнулять. Потому что всегда могут оставаться системные таблицы (не в базе проекта), которые используют MyISAM.

Оценка размера буфера

Размер буферов key_buffer_size и myisam_sort_buffer_size необходимо подбирать из размера индексов таблиц типа MyISAM. Чтобы оценить это значение необходимо сначала дождаться, чтобы база данных сайта (проекта) работала несколько недель без перерыва. Это нужно, чтобы все индексы создались. Затем необходимо зайти в SQL консоль базы данных от лица суперпользователя и выполнить следующий запрос:

SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM';

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

Далее можно взять полученное значение и задать равной ему размеры буферов в пятикратном размере (на всякий случай). То есть если индексов будет 100 килобайт, то каждый буфер key_buffer_size и myisam_sort_buffer_size будет размером в 500 килобайт.

Обратите внимание, что эти буферы относятся у беферам подключений. Поэтому для оценки максимального количества занятого места в оперативной памяти, необходимо умножить их значения на значение "max_connections". К примеру, если оба буфера общим размером 1 мегабайт умножить на 1000 возможных подключений к базе, то получится 1 гигабайт. И всё это находится в ОЗУ, поэтому не стоит делать слишком большой запас на такие буферы.

Проверка и установка размера буферов

Для проверки текущих размеров этих буферов можно выполнить команды в SQL консоли сервера (или через PHP запрос):

SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'myisam_sort_buffer_size';

Чтобы изменить значения только для текущей сессии базы данных (до перезагрузки), к примеру, установить оба на 2 мегабайта, можно выполнить такие SQL запросы:

SET @@global.myisam_sort_buffer_size=2*1024*1024;
SET @@global.key_buffer_size=2*1024*1024;

Таким образом, меняя объёмы буферов на работающем сервере, можно узнать до каких реальных размеров могут вырастать индексы.

Далее чтобы сохранить размеры буферов после перезагрузки, нужно прописать в кофигурационном файле базы данных /etc/my.conf следующие строки в секции [mysqld] (пример для 2 мегабайт):

[mysqld]
myisam_sort_buffer_size = 2M
key_buffer_size = 2M

Системные буферы

Как было сказано ранее, некоторые системные таблицы имеют тип MyISAM. Поэтому обнуление размеров буферов key_buffer_size и myisam_sort_buffer_size только замедлит систему. Из-за чего стоит провести оценку размера системных индексов. Делается это командой:

SELECT SUM(index_length) FROM information_schema.tables 
WHERE engine='MyISAM' AND table_schema IN ('information_schema','mysql');

А если нужен только размер индексов за исключением системных, то надо поставить NOT IN в предыдущем запросе, чтобы получилось так:

SELECT SUM(index_length) FROM information_schema.tables 
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql');