Скажу сразу, что я далеко на DBA и большую часть своего пути в ИТ работал с сервером баз данных Microsoft SQL Server. Безусловно, время от времени мне приходилось работать с серверами баз данных MySQL, где в большинстве случает было достаточно стандартных настроек. Но уже несколько раз мне приходилось немного “тюнить” параметры сервера MySQL и я пришел в тому, что пришла пора систематизировать и собрать в одном месте всю ту информацию, что я использовал, когда выполнялась оптимизация настроек сервера MySQL/MariaDB.
Важное примечание. Я рассматривают тип хранения InnoDB, а не MyISAM.
Общий анализ текущих настроек производительности MySQL/MariaDB
Когда я озадачился тем, как можно сделать срез наиболее важных метрик текущей производительности сервера MySQL, то наткнулся на интересный проект. По сути это скрипт, который пробегается по основным настройкам и метрикам и выводит на консоль подсказки по оптимизации производительности.
Пример использования:
curl -L https://raw.githubusercontent.com/BMDan/tuning-primer.sh/main/tuning-primer.sh | bash
Вывод по итогам работы скрипта будет длинный, но ниже я приведу пример подсказки:
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 6
The number of used connections is 3% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
т.е. скрипт говорит о том, что по умолчанию сервер MySQL настроен так, что позволяет установить 151 соединение, что может быть очень избыточно под ваш профиль нагрузки.
А вот пример успешного завершения одного из тестов:
MEMORY USAGE
Max Memory Ever Allocated : 290 M
Configured Max Per-thread Buffers : 439 M
Configured Max Global Buffers : 273 M
Configured Max Memory Limit : 712 M
Physical Memory : 3.78 G
Max memory limit seem to be within acceptable norms
Оптимизация настроек сервера MySQL/MariaDB
Настройки по большей части индивидуальны под каждый профиль нагрузки, но на просторах интернета я нашел публикацию на Хабре, где автор кратко изложил основные параметры. Хоть статья и вышла довольно давно, но она все еще не утратила свою актуальность.
Для наглядности я собрал в таблице ниже основные параметры сервера MySQL, которые могут потребовать оптимизации.
Настройки сервера MariaDB (для версии 10.6.12) находятся в файле /etc/mysql/mariadb.conf.d/50-server.cnf.
| Параметр | Описание |
| innodb_buffer_pool_size | Используется для типов хранения InnoDB. Если вы используете исключительно InnoDB, то важно указать значение примерно в 80% от оперативной памяти. В противном случае могут быть серьезные проблемы с производительность сервера MySQL. |
| innodb_log_file_size | Также является важным параметром при частых записях в БД. Указывает объем данных, который может быть использован для журналирования операций при изменению данных. Логирование в файл выполняет быстрее, чем логирование в БД. Размер по умолчанию 96 МБ (10.6.12-MariaDB). Универсальный вариант – innodb_log_file_size равен 25% от innodb_buffer_pool_size. |
| innodb_log_buffer_size | При активной работе с BLOB рекомендуется увеличить этот параметр до 32 МБ. |
| innodb_flush_log_at_trx_commit | Определяет параметры сброса кеша на диск. Стандартное значение (1) определяет, что при каждой транзакции обновления она должна сбрасывать значения на диск (что может замедлять сервер). Можно попробовать установить значение 2 – на диск буфер на сбрасывается, только в кэш ОС, что потенциально может ускорить работу сервера. Но повторюсь – все индивидуально. Нужно тестировать. |
| table_cache | Определяет сколько таблиц может находится в кэше. Попробуйте начать с 1000. При активном использовании сервера потребуется увеличить это значение. |
| thread_cache | Определяет количество потоков для кеширования. Посмотреть количество текущих потоков можно вот этой командой: mysqladmin extended-status | grep Threads Если Threads_created на тысячи больше чем Threads_cached, то нужно увеличивать thread_cache_size от 32 до 100, пока значения примерно не сравняются. |
| max_connections | Максимальное количество возможных соединений с сервером. |
Пожалуй, это наиболее распространенные параметры из числа тех, что рекомендуется изменять. Если у вас есть какие-то дополнения, то пишите, я обязательно это учту и дополню материал статьи. Надеюсь, что она поможет тем, кто хочет понять как выполняется оптимизация настроек сервера MySQL/MariaDB.