Оптимизация MySQL становится необходимой когда замечена нагрузка на систему, создаваемая сервисом и задержки в работе сайтов.
Основной конфигурационный файл сервиса располагается по пути /etc/mysql/my.cnf.
В нем могут подключаться другие файлы с помощью директивы include.
В этих файлах потребуется задать новые директивы или поменять значения существующих и затем перезапустить службу чтобы изменения вступили в силу.
За счет оптимизации MySQL можно добиться максимальной производительности и скорости работы для проекта.
За исходные условия принимаются:
- существующая нагрузка, определенное количество запросов к базе
- существующие запросов к базе данных, оптимально написанные или нет
- определенная аппаратная часть — сервер с тем процессором, который установлен, с определенным количеством оперативной памяти и диском
На все три условия можно повлиять и получить лучший результат для проекта, но сейчас примем их как данность и рассмотрим что можно сделать по части настроек.
Подбор параметров можно проводить вручную или с помощью скрипта MySQLTuner. Второй вариант проще и используется чаще. Будем разбирать его.
Чтобы воспользоваться скриптом нужно есть скачать
cd /tmp && wget http://mysqltuner.pl/ -O mysqltuner.pl
Потом скрипт можно запускать (под пользователем root)
perl mysqltuner.pl
Скрипт выдаст большой объем данных — укажет всё что он проверяет, в конце будут рекомендации, которые можно использовать для оптимизации MySQL, пример приведен на скриншоте
Чтобы результат был корректным сервис 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:
1)
skip-name-resolve=1
MySQL использует DNS и разрешает домены в ip адреса формируя кэш имен хостов.
Отключение этого механизма за счет установления значения 1 опции отключает данный механизм. Минус только в том, что при создании пользователя MySQL нельзя будет использовать домены, только ip адреса. Домены при создании пользователей практически никогда не используются так что в 99% случаев можно смело отключать DNS и эффект будет только позитивный.
2)
query_cache_size (=0)
Параметры отвечают за кэш запросов, первый определяет выделенное количество памяти. Второй — 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
Открываем файл на редактирование и добавляем параметры
Сохраняем файл, перезапускаем службу, убеждаемся, что она запустилась
Если служба не запустилась нужно проверять лог, обычно путь к нему /var/log/mysql/error.log
Там будут записи если, например, в именах параметров опечатки.
На этом всё, оптимизация MySQL с помощью скрипта mysqltuner выполнена. В реальных системах список параметров и значения могут быть другими, процесс аналогичен. По кадому параметру проверяется текущее значение, выставляется новое, если непонятно за что отвечает параметр по нему ищется информация в документации.
Перед внесением любых изменений нужно делать копии конфигурационных файлов.
Ручная оптимизация выполняется точно так же, но значения параметров выбираются на основе анализа запросов и текущих значений параметров. По каждому параметру есть информация в документации MySQL (пример).
Что ещё можно сделать
Для достижения максимально производительности можно выносить tmpfs в оперативную память, отключать логирование медленных запросов и общий лог сервиса, отсутствие логирования будет означать сокращение количества дисковых операций.
Кроме настроек сервера можно оптимизировать сами SQL запросы, для этого используется EXPLAIN, самым действенным способом оптимизации является добавление индексов.