Як увімкнути журнал повільних запитів у 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]. Якщо його не існує, ви можете створити його. Додайте або змініть наступні рядки, щоб увімкнути журнал повільних запитів:

[mysqld]
 slow_query_log = 1
 slow_query_log_file = /var/log/mysql/slow-query.log
 long_query_time = 2

slow_query_log = 1: Вмикає ведення журналу повільних запитів.
файл_балки_повільних_запитів: Вказує місце, де буде збережено файл журналу повільних запитів. Ви можете вказати власний шлях та ім’я файлу, але переконайтеся, що каталог має належні права на запис для 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 VARARIES 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 ви можете використовувати logrotate для керування розміром і обертанням файлів журналів MySQL/MariaDB.

Ось базовий приклад для /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.