2025-05-20 14:58:14
Вот примеры настройки и использования **журнала медленных запросов** (slow query log) в MariaDB на русском языке, чтобы выявить и оптимизировать медленно выполняющиеся SQL-запросы, аналогично функции `log_min_duration_statement` в PostgreSQL.
### 1. Включение журнала медленных запросов
Журнал можно включить динамически через SQL или через файл конфигурации (`my.cnf`).
#### Пример 1: Включение журнала динамически
Чтобы включить журнал без перезапуска MariaDB:
```sql
SET GLOBAL slow_query_log = 'ON'; -- Включить журнал
SET GLOBAL long_query_time = 1; -- Логировать запросы, выполняющиеся дольше 1 секунды
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log'; -- Путь к файлу журнала
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- Логировать запросы без индексов
```
- **Проверка настроек**:
```sql
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
```
Ожидаемый результат:
```
+------------------------+------------------------------+
| Variable_name | Value |
+------------------------+------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow-query.log |
+------------------------+------------------------------+
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+
```
#### Пример 2: Настройка через файл конфигурации
Редактируем файл конфигурации MariaDB (например, `/etc/my.cnf` или `/etc/mysql/mariadb.conf.d/50-server.cnf`):
```ini
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE -- Логировать в файл (или TABLE для таблицы mysql.slow_log)
```
- **Применение изменений**:
Перезапустите MariaDB:
```bash
sudo systemctl restart mariadb
```
- **Права доступа к файлу журнала**:
```bash
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
```
### 2. Пример записи в журнале медленных запросов
После включения журнала запросы, превышающие `long_query_time`, записываются в файл (например, `/var/log/mysql/slow-query.log`). Пример записи:
```
/usr/sbin/mysqld, Version: 10.5.15-MariaDB (MariaDB Server). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2025-05-20T14:30:45.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 1.234567 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 100000
SET timestamp=1742581845;
SELECT * FROM large_table WHERE name = 'example';
```
- **Объяснение**:
- **Query_time**: 1.234567 секунд (запрос выполняется ~1.23 сек).
- **Lock_time**: Время ожидания блокировок (0.000123 сек).
- **Rows_sent**: 1000 строк возвращено клиенту.
- **Rows_examined**: 100,000 строк просканировано (возможна неэффективность).
- **Запрос**: `SELECT * FROM large_table WHERE name = 'example'`.
### 3. Анализ с помощью `mysqldumpslow`
Инструмент `mysqldumpslow` помогает анализировать и суммировать данные журнала.
#### Пример 3: Использование `mysqldumpslow`
Запустите команду для анализа:
```bash
mysqldumpslow /var/log/mysql/slow-query.log
```
- **Пример вывода**:
```
Количество: 10 Время=1.25с (12.5с) Блокировка=0.00с (0.00с) Строк=1000.0 (10000), app_user[app_user]@localhost
SELECT * FROM large_table WHERE name = 'example'
```
- **Количество**: Запрос встретился 10 раз.
- **Время**: Среднее время выполнения (1.25с на запрос, 12.5с всего).
- **Строк**: Среднее количество возвращённых строк (1000 на запрос).
- **Фильтрация**:
Для вывода 5 самых медленных запросов:
```bash
mysqldumpslow -t 5 -s t /var/log/mysql/slow-query.log
```
- `-t 5`: Показать 5 запросов.
- `-s t`: Сортировать по общему времени.
### 4. Логирование в таблицу
Можно записывать медленные запросы в таблицу `mysql.slow_log` для анализа через SQL.
#### Пример 4: Включение логирования в таблицу
```sql
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
```
- **Анализ таблицы**:
```sql
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
WHERE query_time > 1
ORDER BY query_time DESC
LIMIT 5;
```
- **Пример вывода**:
```
+---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | sql_text |
+---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
| 2025-05-20 14:30:45 | app_user | 00:00:01.23 | 00:00:00.00 | 1000 | 100000 | SELECT * FROM large_table WHERE name = 'example' |
+---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
```
### 5. Оптимизация медленного запроса
Предположим, медленный запрос:
```sql
SELECT * FROM large_table WHERE name = 'example';
```
- **Шаг 1: Анализ с `EXPLAIN`**:
```sql
EXPLAIN SELECT * FROM large_table WHERE name = 'example';
```
- **Вывод**:
```
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | large_table| ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
```
- **Проблема**: `type: ALL` — полное сканирование таблицы, индекс не используется (`key: NULL`).
- **Шаг 2: Создание индекса**:
```sql
CREATE INDEX idx_name ON large_table(name);
```
- **Шаг 3: Повторный анализ**:
```sql
EXPLAIN SELECT * FROM large_table WHERE name = 'example';
```
- **Улучшенный вывод**:
```
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | large_table| ref | idx_name | idx_name | 256 | const | 1 | Using where |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
```
- **Улучшение**: `type: ref`, `rows: 1` — индекс используется, сканируется меньше строк.
- **Шаг 4: Проверка**:
Выполните запрос и проверьте журнал. Если время выполнения стало меньше `long_query_time`, запрос не попадёт в журнал.
### 6. Логирование плана выполнения
Чтобы включить планы выполнения в журнал (аналог `auto_explain` в PostgreSQL):
```sql
SET GLOBAL log_slow_verbosity = 'query_plan,explain';
```
- **Пример записи в журнале**:
```
# Time: 2025-05-20T14:32:00.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 1.500000 Lock_time: 0.000100 Rows_sent: 1000 Rows_examined: 100000
# EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra
# EXPLAIN: 1 SIMPLE large_table ALL NULL NULL NULL NULL 100000 Using where
SET timestamp=1742581920;
SELECT * FROM large_table WHERE name = 'example';
```
### 7. Автоматизация анализа
#### Пример 5: Скрипт для анализа логов
```bash
#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
mysqldumpslow -t 10 -s t "$LOG_FILE" > /tmp/slow_query_summary.txt
cat /tmp/slow_query_summary.txt
```
- Сохраните как `analyze_slow_queries.sh`, сделайте исполняемым (`chmod +x`) и запустите:
```bash
./analyze_slow_queries.sh
```
#### Пример 6: SQL-запрос для анализа таблицы
Если используется `log_output = TABLE`:
```sql
SELECT sql_text, COUNT(*) as количество, AVG(query_time) as среднее_время, SUM(rows_examined) as всего_строк
FROM mysql.slow_log
GROUP BY sql_text
ORDER BY среднее_время DESC
LIMIT 5;
```
### Примечания
- **Ротация логов**: Используйте `logrotate` для управления размером файла:
```bash
/var/log/mysql/slow-query.log {
weekly
rotate 4
compress
missingok
notifempty
}
```
- **Влияние на производительность**: Параметры вроде `log_queries_not_using_indexes` или `log_slow_verbosity = 'query_plan'` могут увеличить нагрузку. Тестируйте в нерабочей среде.
- **Инструменты**: Используйте `pt-query-digest` из Percona Toolkit для глубокого анализа:
```bash
pt-query-digest /var/log/mysql/slow-query.log
```
Подробности в [документации MariaDB](https://mariadb.com/kb/en/slow-query-log-overview/).
Back to list