Как да активирате дневника за бавни заявки в 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

След като влезете в системата, изпълнете следната команда:

ПОКАЖИ ПРОМЕНЛИВИТЕ, КОИТО СЕ ПОДОБЯВАТ НА '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.