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

Оглавление
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
bulk_insert_buffer_size
Временные файлы
max_connections
max_user_connections
wait_timeout
interactive_timeout
Ограничение количества подключений
Использованные материалы

В 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 килобайт. Если какой-то клиент выполняет запрос с сортировкой большой выборки, то лучше менять значение этой опции внутри клиентского подключения, а не на уровне сервера.

bulk_insert_buffer_size

Максимальный размер буфера, используемого для вставки данных в непустые таблицы MyISAM, для выполнения запросов INSERT ... SELECT, INSERT ... VALUES и LOAD DATA. По умолчанию равен 8 мегабайтам. Для ускорения вставки данных в таблицы MyISAM размер этого буфера можно увеличить.

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

При необходимости сортировки больших выборок данных 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 сообщит об ошибке выполнения запроса.

max_connections

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

В случае веб-приложений нет особого смысла выставлять эту настройку намного больше количества процессов сервера приложения. Если в php-fpm или uwsgi для работы приложения выделено 16 процессов, то как правило каждый процесс будет устанавливать не более одного подключения к базе данных.

max_connections = 140

Для настройки можно воспользоваться значением переменных Max_used_connections и Threads_connected, первая из которых фиксирует максимальное когда-либо использованное количество подключений, а вторая содержит текущее количество подключений. Если Max_used_connections равно значению max_connections, то бывали случаи, когда использовались все доступные подключения к серверу. Для более обоснованного решения об увеличении максимального количества подключений стоит понаблюдать за графиком Threads_connected.

Для получения значений переменных Max_used_connections и Threads_connected можно воспользоваться, например, следующими запросами:

SHOW GLOBAL STATUS WHERE Variable_name = 'Max_used_connections';
SHOW GLOBAL STATUS WHERE Variable_name = 'Threads_connected';

max_user_connections

Максимальное количество подключений, которое может быть установлено одним и тем же пользователем.

wait_timeout

Максимальная длительность неинтерактивного простаивающего подключения в секундах. Если за указанное время подключение не проявляло активности, то по истечение этого времени оно будет принудительно закрыто. По умолчанию - через 8 часов.

interactive_timeout

Максимальная длительность интерактивного простаивающего подключения в секундах. Если за указанное время подключение не проявляло активности, то по истечение этого времени оно будет принудительно закрыто. По умолчанию - через 8 часов. Интерактивным считается подключение, которая при вызове функции mysql_real_connect (или ей подобной) передала флаг CLIENT_INTERACTIVE.

Ограничение количества подключений

Указанные выше опции можно использовать для управления количеством подключений, устанавливаемых к серверу MySQL. Опция max_connections позволяет ограничить общее количество подключений, опция max_user_connetions - ограничить количество подключений от одного пользователя, а опции wait_timeout и interactive_timeout своевременно избавляться от простаивающих подключений.

Поскольку каждому установленному подключению выделяется по одному буферу размерами join_buffer_size, sort_buffer_size, read_buffer_size и read_rnd_buffer_size, то умножив сумму объёмов этих буферов на значение опции max_connections, можно узнать максимальный объём памяти, который будет использоваться клиентскими подключениями. Своевременное закрытие простаивающих подключений позволяет повлиять на количество установленных подключений, что может позволить уменьшить значение опции max_connections и распределить больше свободной памяти для общих буферов MySQL. Дополнительную экономию количества подключений может дать настройка ProxySQL.