Качественная настройка параметров MySQL сервера увеличивает его производительность и снижает потребление оперативной памяти. Одними из основных параметров являются размеры буферов, которые должны быть подобраны под размер данных. Речь пойдёт об key_buffer_size и myisam_sort_buffer_size.

Оба параметра key_buffer_size и myisam_sort_buffer_size относятся к типу таблиц MyISAM. Точнее к их индексам. Но не стоит задавать этим параметрам значение «ноль», даже если используете в своём проекте только таблицы типа InnoDB. Это приведёт к падению производительности из-за того, что сама MySQL использует системные таблицы, которые хранятся как MyISAM.

В интернете можно столкнуться с советами типа: «Установите значение этих буферов равным 80% всей оперативной памяти». Но это слишком большая роскошь. Лучше выставить размер основываясь на текущих нуждах. Для этого определим сколько же всего на сервере есть таблиц MyISAM. Для этого в командную строку MySQL и из-под суперпользователя базы введём команду:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = ‘MyISAM’;

На сервере только этот блог работает на WordPress и хранит таблицы типа MyISAM. Поэтому результат получился такой:

Все базы с префиксом «wp_» относятся к блогу. Остальные таблицы системные. Для определения размеров буферов логично будет отталкиваться от суммарного размера индексов всех MyISAM таблиц.

Чтобы определить размер индексов, оставим сайт включённым на несколько дней, чтобы они создались и накопились в достаточном количестве. А затем получим общий размер индексов в байтах с помощью команды:

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

В моём случае результат такой:

Это означает, что имеются 158 килобайт индексов. С этого момента начинаются тяжёлые думы о размерах буферов key_buffer_size и myisam_sort_buffer_size… Мне кажется, что делать буферы равными 80% ОЗУ — это неправильно. На личном опыте можно убедиться, что со временем размер индексов довольно быстро перестаёт расти. Нет нужды делать большой запас. Поэтому для 158 килобайт индексов будет вполне приемлем десятикратный запас в 2 мегабайта.

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

[mysqld]

myisam_sort_buffer_size = 2M

key_buffer_size = 2M

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

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

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

Посмотрим сколько занимают индексы сейчас с помощью команд:

SHOW VARIABLES LIKE ‘key_buffer_size’;

SHOW VARIABLES LIKE ‘myisam_sort_buffer_size’;

Используйте следующую команду, если понадобится определить размер индексов за исключением системных:

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

Результат будет показан в байтах. Если убрать NOT из команды, то получите размер только системных индексов.