Анализ логов и данных MySQL

MySQL - популярная система управления базами данных. Зачастую случается, что неверная конфигурация MySQL сервера или же не оптимизированные SQL запросы ведут к нестабильной работе сайта. Сегодня будем разбираться как получить информацию о состоянии вашего MySQL сервера, баз данных, таблиц и запросов.

Список всех таблиц базы данных и их размеров в Мб:

SELECT table_name AS "Tables", engine AS "Engine",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "<DATABASE_NAME>"
ORDER BY (data_length + index_length) DESC;

Количество строк в таблицах отдельной базы данных:

SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables`
WHERE `table_schema` = '<DATABASE_NAME>'
ORDER BY TABLE_ROWS DESC;

Отобразить список активных процессов (threads). Зачастую позволяет понять какой запрос создает чрезмерную нагрузку на MySQL или определить список "подвисших" запросов:

SHOW PROCESSLIST;

Проверка на "deadlocks" (честно говоря, пригодилось всего 1 раз, но пусть будет):

SHOW ENGINE INNODB STATUS;

Команды для обзора MySQL Slow logs:

grep Query_time logs/mysqld-slow-query.log | sort -r --sort=numeric

grep -B 3 '# Query_time: ' logs/mysqld-slow-query.log

Пример данных из MySQL Slow logs:

# Time: 200616  8:34:58
# User@Host: user[host] @  [10.200.30.400]
# Thread_id: 694688  Schema: my_schema  QC_hit: No
# Query_time: 30.597096  Lock_time: 0.000060  Rows_sent: 0  Rows_examined: 474289

На что обращать внимание:

• Time - время, в которое запрос был запущен (можно связать с PHP логами, например).
• Query_time - время, за которое выполнен SQL запрос.
• Rows_sent - количество строк, которое вернул MySQL после выполнения запроса. Чем больше результатов - тем больше времени потратит PHP на их обработку, может вообще и вылететь по памяти.
• Rows_examined - количество строк, которое было задействовано в таблицах при поиске нужных данных во время выполнения SQL запроса. Не забывайте про индексы в таблицах и в случае чего EXPLAIN в помощь!

EXPLAIN

Это оператор "объяснит" всю внутреннюю кухню вашего SQL запроса. На самом деле про этот него можно много расписывать, но это все и без этой заметки есть в интернетах.

SQL_NO_CACHE

Этот оператор пригодится на случай если нужно выполнить SQL запрос, без использования кэшированных данных.