Как включить журнал медленных запросов в MySQL или MariaDB
По мере роста баз данных и превращения их в основной компонент веб-приложений настройка производительности становится критически важной. В MySQL® и MariaDB одним из наиболее эффективных способов диагностики узких мест в производительности является включение журнала медленных запросов. Этот журнал помогает выявить запросы, которые выполняются слишком долго, что позволяет оптимизировать их и повысить общую производительность базы данных.
В этом руководстве мы объясним, что такое журнал медленных запросов, почему он важен и как включить и настроить его в MySQL и MariaDB.
Что такое журнал медленных запросов?
Журнал медленных запросов – это файл журнала, в который записываются SQL-запросы, выполнение которых занимает больше определенного времени (порог). По умолчанию этот порог установлен на 10 секунд, но его можно настроить. Запросы, превышающие этот порог, записываются в журнал вместе с информацией о том, сколько времени занял запрос, а также о дате и времени его выполнения. Это важный инструмент для разработчиков и администраторов баз данных, позволяющий выявлять и оптимизировать медленно выполняющиеся запросы, что способствует повышению производительности базы данных.
Зачем включать журнал медленных запросов?
Включение журнала медленных запросов помогает:
- Выявить узкие места в производительности: Медленные запросы могут существенно повлиять на производительность вашего приложения. Журнал поможет вам выявить эти проблемные запросы.
- Оптимизация: Изучив медленные запросы, вы можете определить области, в которых индексы, реструктуризация запросов или кэширование могут повысить производительность.
- Мониторинг производительности запросов: Для постоянной настройки производительности журнал медленных запросов дает представление о том, как изменяется время выполнения запросов с течением времени.
Как включить журнал медленных запросов в MySQL или MariaDB
Включение журнала медленных запросов предполагает изменение конфигурационного файла и корректировку некоторых параметров непосредственно в экземпляре MySQL/MariaDB. Вот как это сделать шаг за шагом.
Шаг 1: Получите доступ к файлу конфигурации MySQL/MariaDB
Конфигурационные файлы MySQL и MariaDB обычно находятся по адресу:
- Для MySQL: /etc/my.cnf или /etc/mysql/my.cnf
- Для MariaDB: /etc/my.cnf.d/server.cnf или /etc/mysql/mariadb.cnf
Чтобы открыть файл конфигурации, воспользуйтесь удобным для вас текстовым редактором. Например, с помощью nano вы можете выполнить следующую команду:
sudo nano /etc/my.cnf
Шаг 2: Измените файл конфигурации
В файле конфигурации найдите строку [mysqld] раздел. Если его нет, вы можете создать его. Добавьте или измените следующие строки, чтобы включить журнал медленных запросов:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2- slow_query_log = 1: Включает журнал медленных запросов.
- slow_query_log_file: Указывает место, куда будет сохраняться файл журнала медленных запросов. Вы можете задать собственный путь и имя файла, но убедитесь, что каталог имеет соответствующие права на запись для MySQL/MariaDB.
- long_query_time = 2: Устанавливает порог для определения того, что считается “медленным” запросом. В этом случае в журнал будет записываться любой запрос, выполняемый дольше 2 секунд. Вы можете изменить это значение в зависимости от ваших потребностей в производительности.
Шаг 3: Перезапустите службу MySQL/MariaDB
После сохранения изменений в конфигурационном файле перезапустите службу MySQL или MariaDB, чтобы изменения вступили в силу.
- Для систем, использующих systemd:
sudo systemctl restart mysqld # For MySQL
sudo systemctl restart mariadb # For MariaDB
- Для систем, использующих init.d:
sudo service mysqld restart # For MySQL
sudo service mariadb restart # For MariaDB
Шаг 4: Проверка того, что журнал медленных запросов включен
Чтобы убедиться, что журнал медленных запросов включен, войдите в клиент MySQL/MariaDB, выполнив команду:
mysql -u root -p
Войдя в систему, выполните следующую команду:
SHOW VARIABLES LIKE 'slow_query_log';
Вы должны увидеть что-то вроде этого:
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| slow_query_log | ON |
+-----------------+-------+
Это подтверждает, что журнал медленных запросов включен.
Шаг 5: Анализ журнала медленных запросов
Журнал медленных запросов записывает запросы в файл, указанный в конфигурации. Чтобы просмотреть содержимое журнала медленных запросов, можно использовать такие программы просмотра текста, как less, cat или tail:
sudo less /var/log/mysql/slow-query.log
Вы увидите записи, похожие на следующие:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: root[root] @ localhost []
# Query_time: 4. 561529 Lock_time: 0.000115 Rows_sent: 1 Rows_examined: 50000
SET timestamp=1697030723;
SELECT * FROM large_table WHERE column = 'value';
Журнал содержит такие сведения, как время выполнения запроса, время блокировки, количество отправленных и просмотренных строк, а также сам SQL-запрос.
Дополнительная конфигурация для журнала медленных запросов
Существуют дополнительные параметры конфигурации, которые можно задать, чтобы настроить журнал медленных запросов под свои нужды.
1. Журнал запросов без индексов
Вы также можете включить ведение журнала для запросов, не использующих индексы, которые часто являются источником проблем с производительностью. Добавьте следующую строку в конфигурацию MySQL/MariaDB:
log_queries_not_using_indexes = 1
Это позволит регистрировать все запросы, которые не используют индексы, что поможет вам выявить и оптимизировать плохо работающие запросы.
2. Ограничение размера файла журнала
Чтобы журнал медленных запросов не разрастался слишком сильно, вы можете вращать журналы или ограничивать их размер. В системах Linux для управления размером и ротацией файлов журналов MySQL/MariaDB можно использовать logrotate.
Вот базовый пример для /etc/logrotate.d/mysql:
/var/log/mysql/slow-query.log {
daily
rotate 7
missingok
notifempty
compress
delaycompress
postrotate
/usr/bin/systemctl reload mysql >/dev/null 2>&1 || true
endscript
}Эта конфигурация ежедневно поворачивает файл журнала и сохраняет последние семь журналов, что позволяет медленному журналу запросов не занимать слишком много места на диске.
Шаг 6: Отключение журнала медленных запросов
После того как вы определили и оптимизировали медленные запросы, вы можете отключить журнал медленных запросов, чтобы сэкономить ресурсы. Для этого снова отредактируйте файл конфигурации и установите значение slow_query_log равным 0:
[mysqld]
slow_query_log = 0Перезапустите службу MySQL или MariaDB, чтобы применить изменения.
Заключение
Журнал медленных запросов – это бесценный инструмент для настройки производительности баз данных в MySQL и MariaDB. Записывая в журнал запросы, которые выполняются слишком долго, вы можете выявить узкие места и оптимизировать производительность запросов. Независимо от того, управляете ли вы небольшой базой данных или крупным корпоративным приложением, включение журнала медленных запросов – это активный способ обеспечить быстродействие и эффективность ваших баз данных.
Следуя шагам, описанным в этом руководстве, вы сможете включить, настроить и проанализировать журнал медленных запросов, чтобы эффективно оптимизировать работу базы данных MySQL или MariaDB.
