Настройка клиентских буферов 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 сообщит об ошибке выполнения запроса.