Продолжаю свой цикл статей с конспектами материалов, посвященных настройкам и оптимизации производительности сервера MySQL. На очереди базовая конфигурация и оптимизация настроек MySQL.
90% настроек по части оптимизации конфигурационного файла сервера MySQL будут приведены в разделе ниже. Лишь изредка в отдельных случаях может потребоваться какая-то дополнительная оптимизация. Если даже после изменения конфигурационного файла ощущаются просадки в производительности сервера MySQL, то основной упер необходимо делать на оптимизацию запросов, актуализацию и правильную настройку индексов и схемы БД. Вложение в эти области будет гораздо выгоднее с точки зрения потраченного времени.
Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.
Список первоисточников
- O’Reilly High Performance MySQL 3rd Edition.
- O’Reilly High Performance MySQL 4rd Edition.
- Protecting MySQL from the OOM Killer.
- What is a big innodb_log_file_size?
Общий параметры системы
Корректировка swappiness
Параметр swappiness определяет насколько интенсивно сервер будет использовать файл подкачки (swap). Архитектура сервера MySQL такова, что чем меньше используется swap, тем лучше. Идеальный сценарий – вообще не использовать swap. Я находил разные рекомендации по части swappiness. Но все они так или иначе сводятся к тому, чтобы либо отключить swap:
echo 0 > /proc/sys/vm/swappinessЛибо использовать его по минимуму:
echo 1 > /proc/sys/vm/swappinessОсобенно swappiness = 1 нужно RHEL 6.4, т.к. в противном случае OOM killer может “убить” процесс сервера MySQL при высокой нагрузке.
Корректировка метрик для OOM killer
Неплохо было бы заранее скорректировать параметры работы OOM killer для процесса сервера MySQL. Как это сделать – лучше выполнить поиск с привязкой к вашему дистрибутиву. Например, для Ubuntu Server 22.04 можно отредактировать юнит файл:
sudo systemctl edit mysql.serviceИ добавить следующую строчку:
[Service]
OOMScoreAdjust=-1000
После корректировки файла перезапустить сервис mysql:
sudo systemctl restart mysqlПроверяем – применилась ли корректировка метрики для OOM killer:
cat /proc/$(pgrep mysql)/oom_score_adj
Либо можно посмотреть результирующий юнит файл для mysql:
systemctl show mysql --all | grep OOMScore
Базовый конфигурационный файл
Структура базового конфигурационный файл:
[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
user = mysql
port = 3306
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
# INNODB
innodb_buffer_pool_size = <value>
innodb_redo_log_capacity = <value>innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
# LOGGING
log_error = /var/log/mysql/mysql-error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# OTHER
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = <value>
thread_cache_size = <value>
table_open_cache = <value>
open_files_limit = 65535
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306Начиная с версии 8.0.30 innodb_log_file_size и innodb_log_files_in_group являются устаревшими. Они заменены параметром innodb_redo_log_capacity.
Этой конфигурации должно быть достаточно в большинстве случаев, но могут быть необходимо корректировки с учетом каких-то дополнительных потребностей. Например, изменить директорию для хранения данных (параметр datadir).
Про те параметры, где указано значение <value> я напишу чуть ниже. Эти значения необходимо адаптировать под ваше окружение.
Отдельно отмечу параметр innodb_dedicated_server , который вы можете дополнительно использовать. Он появился с версии 8.0. Если этот параметр включен (innodb_dedicated_server = 1), то параметры innodb_buffer_pool_size, innodb_log_file_size, innodb_log_files_in_group и innodb_flush_method явно указывать не нужно. Сервер mysql подберет их автоматически исходя из текущих ресурсов ЦП и памяти. Очень удобно в случае, если сервер выделен исключительно под MySQL. Или например, для облачного сценария, когда сервер ресайзится на более мощный план. В таком случае вам не нужно будет корректировать эти четыре параметра вручную.
Краткое описание основных параметров я приведу в подразделах ниже.
innodb_buffer_pool_size
Применимо только для InnoDB таблиц. Определяет объем оперативной памяти, который будет использоваться для нужд InnoDB. Если у вас всего 32 ГБ памяти на сервере, то не стоит отдавать, например, 28-30 ГБ под InnoDB. Оптимальный вариант – отдавать столько памяти, сколько нужно вашим данным, индексам + некоторый запас. В целом, если у вас выделенный сервер БД и используется исключительно InnoDB таблицы, то можно отдавать 50-80% ОЗУ под InnoDB.
Вот это запрос выводит рекомендуемый размер (в гигабайтах) для параметра innodb_buffer_pool_size:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
thread_cache_size
Этот параметр определяет какое-количество подключений будет находится в оперативном состоянии и будет ожидать подключения со стороны клиентов. Стоит учитывать, что все треды будут находиться в памяти и потреблять оперативную память. Когда клиент отключается, то ассоциированный ранее с ним тред возвращается в пул и будет доступен следующему клиенту.
Как правило, значение thread_cache_size = -1 будет достаточно. Но если у вас очень много соединений, то есть смысл ограничить этот параметр каким-то конкретным значением. Посмотреть текущее количество подключений к базе можно следующим запросом:
mysql -e 'show global status like "%Threads_connected%";'
Посмотреть максимальное количество подключений к базе можно вот таким запросом:
mysql -e 'show global status like "%max_used%";'
Например, для 100-200 подключений thread_cache_size = 20 будет вполне достаточно. Для 500-700 подключений thread_cache_size = 200 более оптимальный выбор.
table_open_cache
Определает сколько таблиц таблиц разрешено одному подключению хранить в оперативном кеше. Стандартное значение – 2000. Стоит начать с этого значения и поднимать порог по мере необходимости. Макисмальное значение – 524288.
innodb_redo_log_capacity (innodb_log_file_size) и innodb_log_buffer_size
Как показывает изыскание, размер в 15 ГБ (при наличии 64 ГБ ОЗУ на сервере) для innodb_redo_log_capacity(innodb_log_file_size) гораздо лучше, чем размер в 2ГБ. Опять же все зависит от объемов данных, но стоит попробовать начать с 25% от размера ОЗУ.
Начиная с версии 8.0.30 innodb_log_file_size и innodb_log_files_in_group являются устаревшими. Они заменены параметром innodb_redo_log_capacity.
Для параметра innodb_log_buffer_size значение в 1-8 МБ достаточно, если у вас нет больших BLOB записей.
innodb_flush_log_at_trx_commit
Параметр innodb_flush_log_at_trx_commit, грубо говоря, позволяет определить вам уровень производительности к надежности. Если вам нужна максимальная производительность даже в ущерб возможной частичной потери несохраненных транзакций, то вам сюда.
В таблице ниже приведены возможные варианты значений для параметра innodb_flush_log_at_trx_commit
| Параметр | Значение |
| 0 | Запись буфера в файл журнала каждую секунду с последующей очисткой журнала. Подтверждение транзакции никак не учитывается |
| 1 | Запись буфера в файл журнала каждый раз при подтверждении транзакции. Это наиболее надежный вариант из всех и он используется по умолчанию |
| 2 | Запись буфера в файл журнала каждый раз при подтверждении транзакции. и очистка журнала каждую секунду. Отличие параметра 0 от параметра 2 заключается в том, что параметр 2 не потеряет транзакции, если процесс mysql завершится аварийно. Но потеря данных может быть в случае сбоя всего сервера, например, по питанию или в случае отказа аппаратной части |
max_connections
Глобальный триггер количества соединений, после превышения указанного количества соединений в параметре max_connections новые соединения не будут приниматься сервером. Значение по умолчанию – 151.
Этот параметр необходим, чтобы перегрузить сервер. Если расчетное количество соединений к вашему серверу, скажем, около 300 соединений, то установите этот параметр в значение 400-500.
Как я уже говорил ранее посмотреть текущие количество соединений можно вот такой командой:
mysql -e 'show global status like "%Threads_connected%";'Команда для просмотра максимального количества подключений к серверу mysql:
mysql -e 'show global status like "%max_used%";'skip_name_resolve
Этот параметр отключает обратную проверку DNS. При подключении клиента сервер mysql пытается разрешить его ip-адрес в имя. Тут нет ничего критичного. Ровно до того момента, пока нет проблем с DNS. Рекомендуется отключать обратную проверку DNS. Но в таком случае во всех выданных разрешениях (через GRANT) необходимо заменить имена на IP-адреса.
innodb_io_capacity
Через параметр innodb_io_capacity можно подсказать InnoDB насколько производительна ваша дисковая подсистема. Изначально архитектура InnoDB проектировалась исходя из работы на типе дисков HDD.
Значение по умолчанию 200. Если ваше дисковая подсистема способно гораздо на большее, то укажите это значение в параметре innodb_io_capacity. Укажите примерно 50-75% от возможности вашей дисковой подсистемы в части IOPS.
Ссылки на другие статьи этого цикла
Часть 1. Тестирование производительности MySQL.
Часть 2. Профилирование нагрузки сервера MySQL.
Часть 4. Признаки проблем с производительностью MySQL
Часть 5. Полезные запросы для MySQL.