Настраивая MySQL и оптимизируя настройки его производительности, я делал заметки, которые периодически обновлял и дополнял. В результате сформировался контрольный список настроек сервера MySQL, на которые стоит обратить внимание в первую очередь при его первоначальной настройке и при дальнейших подходах по оптимизации его производительности.
Замечу между прочим, что не стоит уделять слишком много внимания оптимизации производительности одной лишь конфигурации сервера MySQL. Самые лучшие оптимизации можно сделать на стороне приложения, оптимизировав SQL-запросы, изменив структуру таблиц и их индексы. Ну и конечно, не следует ожидать многого от слабого сервера.
Неплохим подспорьем при настройке производительности могут оказаться рекомендации утилиты mysqltuner. Однако не стоит безоглядно копировать в файл конфигурации всё, что он порекомендует. В частности, нужно учитывать размер доступной на сервере оперативной памяти.
Срок хранения журнала транзакций. Если данные часто и помногу обновляются, то для экономии места стоит задать значение поменьше, например 1 день:
expire_logs_days = 1
Однако, если вы используете репликацию данных на другой сервер, журналы стоит хранить за такой период времени, который может понадобиться на восстановление репликации при её поломке. В противном случае придётся повторно копировать данные с ведущего сервера на ведомый.
Уровень изоляции транзакций. Значение REPEATABLE-READ не покажет внутри транзакции новые данные, добавленные в другой транзакции. READ-COMMITED - наоборот, позволяет читать внутри транзакции данные, изменённые в других транзакциях.
Большинству приложений важна целостность и непротиворечивость данных, поэтому они используют базу данных как транзакционную (OLTP). В таком случае лучше использовать значение REPEATABLE-READ.
Если же приложение использует базу данных для аналитических запросов, то лучше подойдёт значение READ-COMMITED.
Бывают и приложения со смешанной логикой. Например, Zabbix хранит в базе данных как собственную конфигурацию, что больше соответствует OLTP, так и исторические данные со значениями определённых показателей в конкретные моменты времени, что больше соответствует OLAP. Но, т.к. нужно обеспечить непротиворечивость данных конфигурации, то запросы к таблицам истории тоже придётся выполнять на уровне изоляции транзакций REPEATABLE-READ.
transaction_isolation = REPEATABLE-READ
Для уверенности стоит поискать настройки, рекомендуемые разработчиками приложения. Если информации найти не удалось, более безопасным выбором будет REPEATABLE-READ.
Настройка, предписывающая хранить каждую таблицу базы данных в отдельном файле. Перед её выставлением необходимо сделать резервную копию всех баз данных. Чтобы настройка вступила в силу, нужно:
При восстановлении данных MySQL поместит каждую таблицу в отдельный файл.
Когда данные таблиц находятся в отдельных файлах, можно сравнительно легко вернуть в файловую систему место, освободившееся в таблице при удалении данных. Для этого достаточно запустить команду OPTIMIZE TABLE или ALTER TABLE ... FORCE над таблицей, которую нужно ужать. В случае, если используется общее хранилище для всех таблиц, неиспользуемое место никогда не возвращается на диск.
Есть у раздельного хранения данных таблиц и отрицательная сторона: при большом количестве таблиц (и секций таблиц, если они есть) увеличивается время запуска сервера MySQL.
Также операционная система обычно ограничивает количество одновременно открытых одним пользователем файлов, то серверу MySQL может потребоваться закрывать неиспользуемые файлы, чтобы открыть нужные и уложиться в этот лимит. Этот недостаток можно смягчить использованием настройки table_cache, описанной ниже, и изменением ограничений со стороны операционной системы.
Если у базы данных небольшой размер, а работать MySQL придётся на каком-нибудь микрокомпьютере, то возможно не стоит хранить данные таблиц в отдельных файлах. Ещё лучше в подобных случаях будет воспользоваться какой-нибудь встраиваемой базой данных, например, SQLite.
innodb_file_per_table = 1
Количество одновременно открытых файлов таблиц. Позволяет уменьшить количество открытий-закрытий файлов. Стоит оценить количество файлов в каталоге, где хранятся файлы с данными MySQL и выставить значение равного порядка.
table_cache = 512
Стоит учитывать, что операционная система ограничивает количество одновременно открытых одним пользователем файлов и значение, указанное в опции, не должно быть больше разрешённого операционной системой лимита.
Настройка, включающая встроенный в сервер MySQL планировщик задач. Позволяет по расписанию запускать запросы или хранимые процедуры:
event_scheduler = 1
Максимальное количество подключений к базе данных. Для обслуживания каждого подключения MySQL запускает по одному отдельному потоку. Если клиентов, подключающихся к базе данных, больше указанного числа, то подключения сверх лимита попадают в очередь, ожидая освобождения одного из занятых подключений.
В случае веб-приложений нет особого смысла выставлять эту настройку намного больше количества процессов сервера приложения. Если в php-fpm или uwsgi для работы приложения выделено 16 процессов, то как правило каждый процесс будет устанавливать не более одного подключения к базе данных.
max_connections = 140
Кэш результатов прошлых запросов. Если содержимое таблиц, фигурирующих в запросе, не менялось с момента кэширования результата предыдущего такого же запроса, то этот кэш позволит серверу сразу выдать клиенту результат запроса из кэша:
query_cache_size = 64M
Если содержимое таблиц постоянно меняется, а вероятность повторного выполнения запроса низка, то отключение этого кэша никак не скажется на производительности СУБД, но позволит сэкономить немного оперативной памяти. Для отключения кэша запросов можно указать такие опции:
query_cache_type = 0 query_cache_size = 0
Общий журнал. Содержит, например, сведения о запусках и остановках сервера.
general_log_file = /var/log/mysql/mysql.log
Журнал ошибок:
log_error = /var/log/mysql/mysql.err
Не выводить предупреждающие сообщения в журнале ошибок:
log_warnings = 0
Настройка кодировки сервера по умолчанию и настроек сортировки и сравнения символов:
character-set-server = utf8 collation-server = utf8_general_ci
Буфер, используемый для соединения таблиц друг с другом. При недостаточном объёме буфера соединение будет осуществляться с использованием диска:
join_buffer_size = 16M
Размер буферного пула. Пожалуй самая важная настройка сервера. Указывает количество оперативной памяти, которую сервер может использовать для обработки данных. Если размер базы данных на диске меньше этого значения, то содержимое всей базы данных может уместиться в оперативной памяти, благодаря чему может быть достигнута максимально возможная производительность сервера.
Если под MySQL выделен отдельный сервер, можно рассчитать размер этого буфера исходя из общего размера доступной на сервере памяти и объёма буферов под обработку запросов от каждого из максимально возможного количества клиентов (max_connections):
innodb_buffer_pool_size = 512M
В интернете можно встретить рекомендации делить буферные пулы размерами больше гигабайта на несколько экземпляров, чтобы на каждый из экземпляров приходилось, например, по одному гигабайту:
innodb_buffer_pool_size = 10G innodb_buffer_pool_instances = 10
Метод записи данных в файловую систему. Часто встречается рекомендация не использовать дисковый кэш операционной системы, т.к. у сервера MySQL есть собственные буферы, а двойная буферизация замедляет работу и повышает вероятность повреждения данных:
innodb_flush_method = O_DIRECT
Настройки журналов транзакций. Если в журнал данные пишутся помногу и часто, имеет смысл увеличить как размер самого журнала, чтобы серверу реже приходилось создавать новые файлы и переоткрывать их, так и увеличить размер буфера записи, чтобы снизить частоту блокирования сервера в ожидании записи в журнал транзакций:
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
Возможны следующие значения:
При необходимости сортировки больших выборок данных 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 сообщит об ошибке выполнения запроса.