Настройка сервера MySQL

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

Замечу между прочим, что не стоит уделять слишком много внимания оптимизации производительности одной лишь конфигурации сервера MySQL. Самые лучшие оптимизации можно сделать на стороне приложения, оптимизировав SQL-запросы, изменив структуру таблиц и их индексы. Ну и конечно, не следует ожидать многого от слабого сервера.

Неплохим подспорьем при настройке производительности могут оказаться рекомендации утилиты mysqltuner. Однако не стоит безоглядно копировать в файл конфигурации всё, что он порекомендует. В частности, нужно учитывать размер доступной на сервере оперативной памяти.

expire_logs_days

Срок хранения журнала транзакций. Если данные часто и помногу обновляются, то для экономии места стоит задать значение поменьше, например 1 день:

expire_logs_days = 1

Однако, если вы используете репликацию данных на другой сервер, журналы стоит хранить за такой период времени, который может понадобиться на восстановление репликации при её поломке. В противном случае придётся повторно копировать данные с ведущего сервера на ведомый.

transaction_isolation

Уровень изоляции транзакций. Значение REPEATABLE-READ не покажет внутри транзакции новые данные, добавленные в другой транзакции. READ-COMMITED - наоборот, позволяет читать внутри транзакции данные, изменённые в других транзакциях.

Большинству приложений важна целостность и непротиворечивость данных, поэтому они используют базу данных как транзакционную (OLTP). В таком случае лучше использовать значение REPEATABLE-READ.

Если же приложение использует базу данных для аналитических запросов, то лучше подойдёт значение READ-COMMITED.

Бывают и приложения со смешанной логикой. Например, Zabbix хранит в базе данных как собственную конфигурацию, что больше соответствует OLTP, так и исторические данные со значениями определённых показателей в конкретные моменты времени, что больше соответствует OLAP. Но, т.к. нужно обеспечить непротиворечивость данных конфигурации, то запросы к таблицам истории тоже придётся выполнять на уровне изоляции транзакций REPEATABLE-READ.

transaction_isolation = REPEATABLE-READ

Для уверенности стоит поискать настройки, рекомендуемые разработчиками приложения. Если информации найти не удалось, более безопасным выбором будет REPEATABLE-READ.

innodb_file_per_table

Настройка, предписывающая хранить каждую таблицу базы данных в отдельном файле. Перед её выставлением необходимо сделать резервную копию всех баз данных. Чтобы настройка вступила в силу, нужно:

1. остановить MySQL,
2. удалить файлы ibdata1, ib_logfile0 и ib_logfile1,
3. запустить MySQL снова,
4. восстановить базы данных из резервных копий.

При восстановлении данных MySQL поместит каждую таблицу в отдельный файл.

Когда данные таблиц находятся в отдельных файлах, можно сравнительно легко вернуть в файловую систему место, освободившееся в таблице при удалении данных. Для этого достаточно запустить команду OPTIMIZE TABLE или ALTER TABLE ... FORCE над таблицей, которую нужно ужать. В случае, если используется общее хранилище для всех таблиц, неиспользуемое место никогда не возвращается на диск.

Есть у раздельного хранения данных таблиц и отрицательная сторона: при большом количестве таблиц (и секций таблиц, если они есть) увеличивается время запуска сервера MySQL.

Также операционная система обычно ограничивает количество одновременно открытых одним пользователем файлов, то серверу MySQL может потребоваться закрывать неиспользуемые файлы, чтобы открыть нужные и уложиться в этот лимит. Этот недостаток можно смягчить использованием настройки table_cache, описанной ниже, и изменением ограничений со стороны операционной системы.

Если у базы данных небольшой размер, а работать MySQL придётся на каком-нибудь микрокомпьютере, то возможно не стоит хранить данные таблиц в отдельных файлах. Ещё лучше в подобных случаях будет воспользоваться какой-нибудь встраиваемой базой данных, например, SQLite.

innodb_file_per_table = 1

table_cache

Количество одновременно открытых файлов таблиц. Позволяет уменьшить количество открытий-закрытий файлов. Стоит оценить количество файлов в каталоге, где хранятся файлы с данными MySQL и выставить значение равного порядка.

table_cache = 512

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

event_scheduler

Настройка, включающая встроенный в сервер MySQL планировщик задач. Позволяет по расписанию запускать запросы или хранимые процедуры:

event_scheduler = 1

max_connections

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

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

max_connections = 140

query_cache_size

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

query_cache_size = 64M

Если содержимое таблиц постоянно меняется, а вероятность повторного выполнения запроса низка, то отключение этого кэша никак не скажется на производительности СУБД, но позволит сэкономить немного оперативной памяти. Для отключения кэша запросов можно указать такие опции:

query_cache_type = 0
query_cache_size = 0

general_log_file

Общий журнал. Содержит, например, сведения о запусках и остановках сервера.

general_log_file = /var/log/mysql/mysql.log

log_error

Журнал ошибок:

log_error = /var/log/mysql/mysql.err

log_warnings

Не выводить предупреждающие сообщения в журнале ошибок:

log_warnings = 0

character-set-server и collation-server

Настройка кодировки сервера по умолчанию и настроек сортировки и сравнения символов:

character-set-server = utf8
collation-server = utf8_general_ci

join_buffer_size

Буфер, используемый для соединения таблиц друг с другом. При недостаточном объёме буфера соединение будет осуществляться с использованием диска:

join_buffer_size = 16M

innodb_buffer_pool_size и innodb_buffer_pool_instances

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

Если под MySQL выделен отдельный сервер, можно рассчитать размер этого буфера исходя из общего размера доступной на сервере памяти и объёма буферов под обработку запросов от каждого из максимально возможного количества клиентов (max_connections):

innodb_buffer_pool_size = 512M

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

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10

innodb_flush_method

Метод записи данных в файловую систему. Часто встречается рекомендация не использовать дисковый кэш операционной системы, т.к. у сервера MySQL есть собственные буферы, а двойная буферизация замедляет работу и повышает вероятность повреждения данных:

innodb_flush_method = O_DIRECT

innodb_log_file_size, innodb_log_buffer_size и innodb_flush_log_at_trx_commit

Настройки журналов транзакций. Если в журнал данные пишутся помногу и часто, имеет смысл увеличить как размер самого журнала, чтобы серверу реже приходилось создавать новые файлы и переоткрывать их, так и увеличить размер буфера записи, чтобы снизить частоту блокирования сервера в ожидании записи в журнал транзакций:

innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

Таких файлов у MySQL два. Рекомендуется, чтобы размер каждого из них составлял 1/4 от размера innodb_buffer_pool_size. Однако размер файла журнала должен быть меньше 2 гигабайт - это внутреннее ограничение MySQL.

У журнала транзакций имеется настройка, аналогичная настройке буферного пула innodb_flush_method:

innodb_flush_log_at_trx_commit = 2

Возможны следующие значения:

• 1 - каждая транзакция после фиксации записывается на диск (наибольшая надёжность),
• 2 - транзакция после фиксации записывается в кэш операционной системы (компромисс между надёжностью и производительностью),
• 0 - нет требования специально сохранять данные транзакции после её фиксации, данные сохраняются по мере заполнения буфера транзакций (наибольшая производительность, но высокий риск потери данных).

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

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