Оптимизация MySQL становится необходимой когда замечена нагрузка на систему, создаваемая сервисом и задержки в работе сайтов.
Основной конфигурационный файл сервиса располагается по пути /etc/mysql/my.cnf.
В нем могут подключаться другие файлы с помощью директивы include.
![Оптимизация MySQL](https://server-gu.ru/wp-content/uploads/2023/04/mysqloptim.png)
В этих файлах потребуется задать новые директивы или поменять значения существующих и затем перезапустить службу чтобы изменения вступили в силу.
За счет оптимизации MySQL можно добиться максимальной производительности и скорости работы для проекта.
За исходные условия принимаются:
- существующая нагрузка, определенное количество запросов к базе
- существующие запросов к базе данных, оптимально написанные или нет
- определенная аппаратная часть — сервер с тем процессором, который установлен, с определенным количеством оперативной памяти и диском
На все три условия можно повлиять и получить лучший результат для проекта, но сейчас примем их как данность и рассмотрим что можно сделать по части настроек.
Подбор параметров можно проводить вручную или с помощью скрипта MySQLTuner. Второй вариант проще и используется чаще. Будем разбирать его.
Чтобы воспользоваться скриптом нужно есть скачать
cd /tmp && wget http://mysqltuner.pl/ -O mysqltuner.pl
Потом скрипт можно запускать (под пользователем root)
perl mysqltuner.pl
Скрипт выдаст большой объем данных — укажет всё что он проверяет, в конце будут рекомендации, которые можно использовать для оптимизации MySQL, пример приведен на скриншоте
![mysqltuner для оптимизации mysql](https://server-gu.ru/wp-content/uploads/2023/04/mysqltuner23.png)
Чтобы результат был корректным сервис MySQL должен проработать после последнего перезапуска не менее 24 часов.
Если условие не выполняется mysqltuner выдаст предупреждение об этом.
В примере скрипт дал рекомендацию увеличить количество оперативной памяти, эту рекомендацию игнорируем поскольку ранее как входные условия были приняты фиксированные ресурсы сервера. В реальных условиях при выводе такого предупреждения стоит проверить память и добавить её количество.
Рассматриваем рекомендации утилиты mysqltuner по оптимизации MySQL
Рекомендации mysqltuner касающиеся настроек на машине, взятой для демонстрации:
skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
performance_schema=ON
key_buffer_size (~ 24M)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
Рассмотрим параметры и добавим их, но перед этим покажем как проходит процесс проверки текущих значений MySQL.
Как проверить текущие значения параметров MySQL
Чтобы увидеть текущие значения используемые службой, нужно зайти в консоль MySQL с реквизитами пользоватя root.
Подключившись по SSH выполняем
Пароль вводится сразу после -p, без пробела.
Оказавшись в консоли сервера баз данных значение любого параметра можно посмотреть выполнив
show variables like 'имя_параметра';
![mysql параметры](https://server-gu.ru/wp-content/uploads/2023/04/mysqlopt6.png)
Пример приведен на скриншоте. Все численные значения выводятся в байтах.
Определение параметров и их значений при оптимизации MySQL:
1)
skip-name-resolve=1
MySQL использует DNS и разрешает домены в ip адреса формируя кэш имен хостов.
Отключение этого механизма за счет установления значения 1 опции отключает данный механизм. Минус только в том, что при создании пользователя MySQL нельзя будет использовать домены, только ip адреса. Домены при создании пользователей практически никогда не используются так что в 99% случаев можно смело отключать DNS и эффект будет только позитивный.
2)
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
Параметры отвечают за кэш запросов, первый определяет выделенное количество памяти. Второй — query_cache_type сообщает службе какие данные кэшировать, со значением 0 не будет кэшироваться ничего, со значением 1 будут кэшироваться все запросы кроме SELECT SQL_NO_CACHE, со значением 2 будут кэшироваться все запросы.
query_cache_limit это ограничение в Мб на кэш для одного запроса.
В данном случае mysqltuner рекомендует отключить кэширование совсем. Для многих серверов так и стоит сделать, это обусловлено механизмом кэширования. MySQL накапливает кэш в памяти, отдает из него данные при поступении запросов на чтение, при поступлении запросов на запись обновляет кэш таблицы.
Использование механизма оправдано когда данные изменяются редко, при существенном количестве изменений в базе постоянно будут выполняться затратные операции сброса/обновления кэша.
3)
performance_schema=ON
performance_schema это встроенный механизм оптимизации MySQL, он позволяет собирать отладочную информацию о работе службы и запросах.
4)
key_buffer_size (~ 24M)
key_buffer_size буфер для индексов таблиц MyISAM. В данном случае скрипт рекомендуем значение 24Мб. Параметр очень важен для баз с преобладанием таблиц с движком MyISAM, о том как выяснить какой движок используется и сменить его рассказано в статье
5)
innodb_log_file_size
Рекомендуемое значение (=16M) (if possible, so InnoDB total log file size equals 25% of buffer_pool_size)
В данном случае скрипт ничего не вывел касательно самого параметра buffer_pool_size
. Он является самым важным для таблиц с движком InnoDB, он определяет объем выдееннной под них оперативной памяти. Значение innodb_log_file_size всегда должно быть равным 25% от buffer_pool_size
Редактирование настроек
Параметры определены, теперь нужно их добавить, перезапустить службу и оптимизация MySQL будет завершена. Находим в конфигурационных файлах сервиса файл с секцией [mysqld], на взятой для демонстрации машине это /etc/mysql/mariadb.conf.d/50-server.cnf
Открываем файл на редактирование и добавляем параметры
![параметры сервера баз данных](https://server-gu.ru/wp-content/uploads/2023/04/mysqldparam-1.png)
Сохраняем файл, перезапускаем службу, убеждаемся, что она запустилась
![mysql перезапуск](https://server-gu.ru/wp-content/uploads/2023/04/mysqlrest6.png)
Если служба не запустилась нужно проверять лог, обычно путь к нему /var/log/mysql/error.log
Там будут записи если, например, в именах параметров опечатки.
На этом всё, оптимизация MySQL с помощью скрипта mysqltuner выполнена. В реальных системах список параметров и значения могут быть другими, процесс аналогичен. По кадому параметру проверяется текущее значение, выставляется новое, если непонятно за что отвечает параметр по нему ищется информация в документации.
Перед внесением любых изменений нужно делать копии конфигурационных файлов.
Ручная оптимизация выполняется точно так же, но значения параметров выбираются на основе анализа запросов и текущих значений параметров. По каждому параметру есть информация в документации MySQL (пример).
Что ещё можно сделать
Для достижения максимально производительности можно выносить tmpfs в оперативную память, отключать логирование медленных запросов и общий лог сервиса, отсутствие логирования будет означать сокращение количества дисковых операций.
Кроме настроек сервера можно оптимизировать сами SQL запросы, для этого используется EXPLAIN, самым действенным способом оптимизации является добавление индексов.