Настройка клиентских буферов MySQL

В MySQL имеются буферы, распределяемые для каждого установленного клиентского подключения.

join_buffer_size

Буфер join используется для сканирования только индекса, диапазона индекса и при слияниях таблиц, использующих полное сканирование таблицы. Переменная настраивает минимальный размер буфера, который по умолчанию равен 256 килобайтам. Прежде чем увеличивать размер этого буфера, лучше подумать о добавлении индексов, которые могли бы ускорить слияние таблиц.

sort_buffer_size

Буфер sort используется для сортировок и группировок. Если данные не умещаются в этот буфер, то используется диск. Значения по умолчанию - 256 килобайт. Для Linux лучше всего подходят значения - 256 килобайт или 2 мегабайт, а большие значения могут привести к замедлению.

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

Для подбора оптимального значения можно ориентироваться на значение переменной Sort_merge_passes, которая считает количество проходов сортировки слиянием с момента запуска сервера MySQL. Получить значение переменной можно, например, с помощью следующего запроса:

SHOW GLOBAL STATUS WHERE Variable_name = 'Sort_merge_passes';

read_buffer_size

Буфер read используется для последовательного чтения строк из таблиц MyISAM. По умолчанию настроено значение 128 килобайт, изменять можно с шагом в 4 килобайта. Оптимально значение по умолчанию в 128 килобайт.

read_rnd_buffer_size

Буфер read_rnd используется для чтения строк в произвольном порядке из таблиц MyISAM и при сортировке строк. По умолчанию имеет значение 256 килобайт. Если какой-то клиент выполняет запрос с сортировкой большой выборки, то лучше менять значение этой опции внутри клиентского подключения, а не на уровне сервера.

Временные файлы

При необходимости сортировки больших выборок данных MySQL использует раздел для временных файлов. Из-за этого выполнение больших запросов может происходить заметно дольше. Чтобы ускорить обработку таких запросов, можно создать файловую систему для временных файлов в оперативной памяти.

Создаём точку монтирования, например /mysql-tmp:

# cd /
# mkdir mysql-tmp

Добавляем в файл /etc/fstab строчку для монитрования раздела размером, например, 512 мегабайт:

tmpfs /mysql-tmp tmpfs relatime,nodev,nosuid,noexec,uid=mysql,gid=mysql,mode=0760,size=512M 0 0

Смонтируем временный раздел:

# mount /mysql-tmp

Теперь нужно указать в файле конфигурации сервера MySQL внутри секции server соответствующую опцию:

tmpdir = /mysql-tmp

И перезапустить MySQL:

# systemctl restart mysql

Стоит учитывать, что если места на этом разделе окажется недостаточно, запрос не выполнится и MySQL сообщит об ошибке выполнения запроса.

Использованные материалы