↩️ Назад

Категории

Диагностика MySQL/MariaDB: как найти тяжелые запросы и источники нагрузки

02.02.2026 | Статья из категории: mysql

Способы диагностики mysql - найти источник нагрузки на cpu

Когда база данных начинает тормозить, первое что нужно — быстро определить, кто и что нагружает сервер. В этой заметке собрал основные команды и инструменты для диагностики MySQL/MariaDB на боевом сервере.

Важно: Большинство примеров работают без прав суперпользователя. Если у вас ограниченные права — начинайте с раздела "Быстрая диагностика".

1. Быстрая диагностика в реальном времени

SHOW PROCESSLIST — кто сейчас работает

Самая первая команда при подозрении на нагрузку:

-- Показать все активные соединения
SHOW FULL PROCESSLIST;

-- Только "живые" запросы (без спящих)
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' 
ORDER BY TIME DESC;

Что ищем:

  • TIME — сколько секунд выполняется запрос (если > 5 сек — тревога)
  • STATE — состояние запроса (Sending data, Copying to tmp table, Locked)
  • INFO — сам запрос (обрезанный до 100 символов)

Мониторинг через командную строку

Запустите в терминале и смотрите обновления каждые 2 секунды:

# Простой мониторинг активных запросов
watch -n 2 "mysql -u user -p'password' -e 'SHOW FULL PROCESSLIST;' | grep -v Sleep"

# Расширенный вариант с фильтром по времени
watch -n 1 "mysql -u user -p'password' -sN -e \
  \"SELECT ID, USER, TIME, LEFT(INFO, 60) FROM information_schema.PROCESSLIST \
   WHERE TIME > 1 OR COMMAND != 'Sleep' ORDER BY TIME DESC;\""

mysqladmin — встроенная утилита

# Статистика в реальном времени
mysqladmin -u user -p status

# Ключевые метрики
mysqladmin -u user -p extended-status | grep -E "Queries|Threads|Slow_queries|Innodb"

# Пример вывода:
# | Queries        | 10552784  |  ← всего запросов
# | Slow_queries   | 787       |  ← медленных запросов
# | Threads_running| 2         |  ← сейчас в работе

2. Включение логирования медленных запросов

Чтобы найти проблемные запросы, которые выполняются не постоянно, а периодически — включаем лог.

Способ 1: Через конфигурационный файл (рекомендуется)

Откройте /etc/mysql/mariadb.conf.d/50-server.cnf (или my.cnf) и добавьте в секцию [mysqld]:

[mysqld]
# Включить лог медленных запросов
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log

# Запросы дольше 1 секунды считаем медленными
long_query_time = 1.0

# Также логировать запросы без индексов
log_queries_not_using_indexes = 1

# Логировать запросы, которые не используют индексы, но быстрые
# log_throttle_queries_not_using_indexes = 10

Перезапустите сервер:

sudo systemctl restart mariadb

Способ 2: Временное включение без перезагрузки

-- Требует прав SUPER
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/tmp/mariadb-slow.log';

Анализ лога медленных запросов

# Посмотреть последние записи
sudo tail -f /var/log/mysql/mariadb-slow.log

# Статистика по медленным запросам (встроенная утилита)
mysqldumpslow /var/log/mysql/mariadb-slow.log

# Детальный анализ с сортировкой по времени
mysqldumpslow -s t /var/log/mysql/mariadb-slow.log

# Топ-10 самых медленных запросов
mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log

3. Продвинутые инструменты анализа

Percona Toolkit — pt-query-digest

Самый мощный инструмент для анализа логов:

# Установка (Debian/Ubuntu)
sudo apt install percona-toolkit

# Анализ лога
pt-query-digest /var/log/mysql/mariadb-slow.log

# Анализ за последние 24 часа
pt-query-digest --since '24h' /var/log/mysql/mariadb-slow.log

# Вывод в браузер (HTML-отчет)
pt-query-digest --report-format=html /var/log/mysql/mariadb-slow.log > report.html

innotop — мониторинг в реальном времени

Современная замена устаревшему mytop:

# Установка
sudo apt install innotop

# Запуск
innotop -u user -p

# Горячие клавиши:
#   Q — Query List (активные запросы)
#   B — InnoDB Buffers
#   D — InnoDB Deadlocks
#   F — Command Summary

Performance Schema (требует прав)

-- Посмотреть самые тяжелые запросы по времени выполнения
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT / 1000000000000 AS total_time_sec,
    AVG_TIMER_WAIT / 1000000000 AS avg_time_ms,
    SUM_ROWS_SENT AS rows_sent,
    SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

4. Анализ нагрузки по пользователям и базам

Кто больше всех нагружает сервер?

-- Статистика по пользователям
SELECT 
    USER, 
    HOST, 
    COUNT(*) AS connections,
    SUM(TIME) AS total_time_seconds
FROM information_schema.PROCESSLIST 
GROUP BY USER, HOST 
ORDER BY total_time_seconds DESC;

Размер таблиц и индексов

-- Самые большие таблицы в базе
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY total_mb DESC
LIMIT 10;

Статистика использования таблиц

-- Какие таблицы чаще всего читаются
SELECT 
    TABLE_NAME,
    ROWS_READ,
    ROWS_CHANGED
FROM information_schema.TABLE_STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY ROWS_READ DESC
LIMIT 10;

5. Профилирование конкретного запроса

-- Включить профилирование
SET profiling = 1;

-- Выполнить ваш запрос
SELECT * FROM large_table WHERE condition = 'value';

-- Посмотреть профили всех запросов в сессии
SHOW PROFILES;

-- Детальный профиль конкретного запроса (например, #1)
SHOW PROFILE FOR QUERY 1;

-- Профиль с разбивкой по CPU и блокировкам
SHOW PROFILE CPU, BLOCK IO, CONTEXT SWITCHES FOR QUERY 1;

6. Практический пример: диагностика на Raspberry Pi

Допустим, у вас слабый сервер (как у меня — Raspberry Pi) и база начала тормозить. Вот пошаговый алгоритм:

Шаг 1: Быстрая проверка

# Смотрим активные запросы
mysql -u user -p -e "SHOW FULL PROCESSLIST;" | grep -v Sleep

# Статистика
mysqladmin -u user -p extended-status | grep -E "Slow_queries|Threads_running"

Шаг 2: Включаем лог (если еще не включен)

# Добавляем в конфиг и рестартуем
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
sudo systemctl restart mariadb

Шаг 3: Ждем 5-10 минут и анализируем

# Смотрим, что попало в лог
sudo tail -50 /var/log/mysql/mariadb-slow.log

# Или через pt-query-digest
pt-query-digest /var/log/mysql/mariadb-slow.log | head -100

Шаг 4: Оптимизируем найденные запросы

-- Проверяем план выполнения проблемного запроса
EXPLAIN SELECT * FROM sensor_data 
WHERE topic = 'temperature' 
ORDER BY timestamp DESC 
LIMIT 1;

-- Добавляем индекс, если его нет
CREATE INDEX idx_topic_ts ON sensor_data(topic, timestamp DESC);

7. Полезные SQL-запросы для диагностики

Топ-10 самых долгих текущих запросов

SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME AS duration_sec,
    STATE,
    LEFT(INFO, 80) AS query_preview
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 10;

Запросы, которые блокируют другие

SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Статистика использования индексов

SELECT 
    OBJECT_NAME AS table_name,
    INDEX_NAME AS index_name,
    COUNT_STAR AS accesses,
    COUNT_FETCH AS rows_fetched
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name'
ORDER BY COUNT_STAR DESC
LIMIT 20;

8. Автоматизация: скрипт для мониторинга

Сохраните как mysql-monitor.sh:

#!/bin/bash
# MySQL Monitoring Script

DB_USER="your_user"
DB_PASS="your_password"
DB_NAME="your_database"

echo "=== $(date) ==="

echo -e "\n[АКТИВНЫЕ ЗАПРОСЫ > 2 сек]"
mysql -u $DB_USER -p$DB_PASS -sN -e "
SELECT CONCAT(ID, ' | ', USER, ' | ', TIME, 's | ', LEFT(INFO, 50))
FROM information_schema.PROCESSLIST 
WHERE TIME > 2 AND COMMAND != 'Sleep' 
ORDER BY TIME DESC;
"

echo -e "\n[СТАТИСТИКА]"
mysqladmin -u $DB_USER -p$DB_PASS extended-status | \
    grep -E "Slow_queries|Threads_running|Queries|Uptime"

echo -e "\n[РАЗМЕР БАЗЫ]"
mysql -u $DB_USER -p$DB_PASS -sN -e "
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '$DB_NAME' 
ORDER BY size_mb DESC 
LIMIT 5;
"

Запускайте каждые 5 минут через watch или добавьте в crontab.

Заключение

Диагностика базы данных — это не магия, а системный подход:

  1. SHOW PROCESSLIST — смотрим, что происходит прямо сейчас
  2. Slow Query Log — ловим периодические проблемы
  3. pt-query-digest — анализируем и находим узкие места
  4. EXPLAIN + индексы — оптимизируем найденные запросы
  5. Мониторинг — не даем проблеме вернуться

На слабых серверах (типа Raspberry Pi) особенно важно следить за запросами без индексов и большими таблицами. Иногда простое добавление правильного индекса ускоряет запрос в 100 раз!

Pro tip: Если у вас нет прав суперпользователя — начинайте с SHOW PROCESSLIST и mysqladmin. Этого часто достаточно, чтобы найти проблему.
Теги: #mysql #диагностика #mariadb #sql_запросы #sql_команды


Категории:

Категории

Комментарии

Пока нет комментариев. Будьте первым!

Оставить комментарий

← Назад к списку статей

Обратная связь

Посетителей сегодня: 0
о блоге | карта блога

© Digital Specialist | Не являемся сотрудниками Google, Яндекса и NASA