Логирование запросов
Логирование запросов помогает найти узкие места в работе СУБД MySQL и повысить ее производительность. Рассмотрены варианты включения как с перезагрузкой сервера, так и БЕЗ перезагрузки.
Логирование медленных запросов
Включение лога длительных запросов без перезагрузки MySQL-сервера производится из терминала mysql :
mysql>SET global slow_launch_time = 1; SET global slow_query_log_file = '/tmp/mysql_time.log'; SET global slow_query_log = 1; FLUSH LOGS; Установленные параметры означают следующее:
- slow_query_log = 1 включает или выключает лог медленных запросов (1-включить; 0-выключить)
- slow_launch_time = 1 задает время (здесь, 1 секунда), при превышении которого запрос будет считаться медленным
- slow_query_log_file = . путь до файла, в который будут записываться все медленные запросы
- FLUSH LOGS — очищает log-файл
Путь до файла логов должен указывать на директорию, в которую пользователь mysql имеет права на запись. В противном случае, при выполнении команды SET global general_log_file=’. ‘ будет появляться ошибка: Variable ‘general_log_file’ can’t be set to the value of . . По умолчанию эти директории имеют имена: /var/log/mysql/ и /var/lib/mysql/ . Имя файла может быть любым.
Просмотр медленных запросов в реальном времени можно осуществлять командой:
tail -f /var/log/mysql/query_slow.logЧерез какое-то время, при накоплении запросов можно проанализировать файл при помощи утилиты mysqldumpslow . Она покажет какой запрос сколько раз встречается в log-файле:
mysqldumpslow /var/log/mysql/query_slow.logЛогирование можно отключить в любое время с помощью:
mysql>SET global slow_query_log = 0; После перезагрузки MySQL-сервера установленный параметры примут значение по умолчанию и логирование прекратиться. Что бы включить постоянное логирование запросов, то необходимо добавить вышеуказанные параметры в файл конфигурации MySQL-сервера, в секцию [mysqld] :
my.cnf[mysqld] slow_query_log = 1 long_query_time = 1 slow_query_log_file = /tmp/mysql_time.log Логирование всех запросов
В таблицу
Самым простым способом логирования всех запросов поступающих к БД MySQL это включения логирования в отдельную таблицу базы данных.
Проверим, существует ли таблица логов general_log :
mysql>USE mysql; SHOW TABLES LIKE 'general_log'; Если не существует то создадим ее:
mysql>CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; Включаем ведение журнала запросов в базе данных без перезагрузки MySQL-сервера:
mysql>SET global log_output = 'table'; SET global general_log = 1; Логирование можно отключить в любое время командой:
mysql>SET global general_log = 0; mysql>SELECT event_time, command_type, argument FROM mysql.general_log; После перезагрузки MySQL-сервера установленный параметры примут значение по умолчанию и логирование прекратиться. Что бы включить постоянное логирование запросов, то необходимо добавить вышеуказанные параметры в файл конфигурации MySQL-сервера, в секцию mysqld :
my.cnf[mysqld] general_log = on global log_output = 'TABLE' В файл
Для включения логирования запросов в файл без перезапуска базы данных, необходимо выполнить следующие команды в mysql-консоли:
mysql>SET global log_output = 'FILE'; SET global general_log_file='/tmp/mysql_access.log'; SET global general_log = 1; Файл логов должен указывать на директорию, в которую учетная запись (от которой работает MySQL-сервер) имеет права на запись. В противном случае, при выполнении команды SET global general_log_file=’. ‘ будет появляться ошибка: Variable ‘general_log_file’ can’t be set to the value of . . По умолчанию эти директории имеют имена: /var/log/mysql/ и ‘/var/lib/mysql/’. Имя файла может быть любым.
В принципе, для хранения логов запросов можно указать любую директорию, но при этом необходимо дать соответствующие разрешения командой chmod .
Просмотр всех запросов, поступающих к БД в реальном времени можно осуществлять командой:
tail -f /tmp/mysql_access.logЛогирование можно отключить в любое время с помощью:
mysql>SET global general_log = 0; Что бы включить логирование запросов на постоянной основе, необходимо добавить вышеуказанные параметры в файл конфигурации MySQL-сервера, в секцию [mysqld] :
my.cnf[mysqld] general_log = on log_output = 'FILE' general_log_file = /tmp/mysql_access.log Логирование Error Log
Если необходимо понять, по какой причине не запускается MySql сервер error log вам в помощь.
Посмотреть расположение файла по умолчанию куда пишется error log , можно выполнив команду:
mysql>SHOW GLOBAL VARIABLES; Бинарное логирование
В этот лог записываются все команды изменения БД, нужен он для репликации и восстановления. Включать его не рекомендуется, если никакой репликации не планируется, так как он требователен к ресурсам.
Посмотреть расположение файла по умолчанию куда пишется бинарный log , можно выполнив команду:
mysql>SHOW GLOBAL VARIABLES; Отладка и логирование MySQL запросов
Почему удобно включать логирование с помощью MySQL команд:
- Не нужно дергать файл конфига MySQL /etc/mysql/my.cnf;
- После рестарта сервера — лог выключается.
Посмотреть включено ли логирование:
SHOW GLOBAL VARIABLES LIKE '%log%';SHOW GLOBAL VARIABLES WHERE Variable_name in ('version', 'log', 'general_log', 'general_log_file', 'log_output');Включить/выключить логирование (1 — включить, 0 — выключить):
SET GLOBAL general_log = 1;Логировать запросы в таблицу mysql.general_log:
SET GLOBAL general_log = 1; SET GLOBAL log_output = 'TABLE'; Посмотреть логи запросов в таблице:
SELECT * FROM mysql.general_log;Логировать запросы в файл /var/log/mysql.log (этот тип логирования включён по умолчанию):
SET GLOBAL log_output = 'FILE'; SET GLOBAL general_log_file = '/var/log/mysql.log';Примечание
Файл не нужно создавать предварительно, но нужно дать права на запись в этот каталог!
По умолчанию лог-файл запросов: /var/lib/mysql/%USERNAME%log.
Включаем логирование SQL запросов в файл:
SET GLOBAL log_output = 'FILE'; SET GLOBAL general_log_file='dev-sql.log'; Внимание!
Глобальная системная переменная general_log_file содержит имя файла, а не полный путь к нему. Этот файл будет создан в каталоге /var/lib/mysql/ и по умолчанию именуется как hostname>.log.
Примечание
Для просмотра значения глобальной системной переменной general_log_file — выполните запрос:
SHOW VARIABLES LIKE 'general_log_file';Можно включить логирование и в таблицу, и в файл:
SET GLOBAL log_output = 'TABLE,FILE';SET GLOBAL log_output = 'NONE';Пример включения логирования из PHP-скрипта и просмотр логов:
mysql_query("SET GLOBAL log_output = 'FILE'"); mysql_query("SET GLOBAL general_log_file = '/home/stas/tmp/mysql.project.log'"); mysql_query("SET GLOBAL general_log = 1"); // здесь выполняем SQL запрос mysql_query("SET GLOBAL general_log = 0"); mysql_query("SET GLOBAL log_output = 'TABLE'");Включить постоянное логирование (требует рестарт сервера):
[mysqld] log = /var/log/mysql/mysql.logЖурналы (logs) в MySQL
В MySQL на данный момент существуют 4 вида журнала (лога) и при достаточно серьёзной работе с базами на MySQL необходимо за ними следить. Например, бинарный лог у нас за сутки набирает около гигабайта, а размер жёсткого диска на сервере ограничен и за ними надо следить. Однако следить следует не только за бинарным логом, так как логи (журналы) в MySQL могут принести немалую пользу.
Итак, какие логи ведёт MySQL? Это:
1. бинарный лог (binary log)
2. лог ошибок (error log)
3. лог медленный запросов (slow query log)
4. лог запросов (general query log)
5. лог репликаций (relay log)
Каждый из них по-своему полезен.
Бинарный лог
В первую очередь полезен с точки зрения репликаций. Можно его бэкапить, можно использовать для восстановления данных на более точное время при использовании бэкапов. Лог содержит все команды изменений базы данных, выборки (select, show) не сохраняет, для таблиц, поддерживающих транзакции (BDB, InnoDB) запись в лог выполняется только после выполнения команды COMMIT . Для лога можно указывать список баз данных, которые надо логировать и список баз данных, которые не надо логировать. В более ранних версиях вместо бинарного лога использовался лог обновлений. Использование бинарного лога снижает производительность базы данных, однако его польза настолько велика, что крайне не рекомендуется его отключать. Рекомендуется защищать бинарный лог паролем, так как он может данные также о паролях пользователей. При достижении максимально разрешённого размера (1 гиг по умолчанию) создаётся следующий файл. Каждый новый файл имеет порядковый номер после имени.
Содержание бинарного лога можно посмотреть с помощью утилиты mysqlbinlog.
Основные настройки в my.cnf
Местоположение лога:
log_bin = /var/log/mysql/mysql-bin.log
Максимальный размер, минимум 4096 байт, по умолчанию 1073741824 байт (1 гигабайт):
max_binlog_size= 500M
Сколько дней хранится:
expire_logs_days = 3
Наиболее часто использующиеся команды
Повторение действий после операции восстановления:
shell> mysqlbinlog log_file | mysql -h server_name
Удаление логов до определённого файла:
PURGE BINARY LOGS TO ‘mysql-bin.000’;
Удаление логов до определённой даты:
PURGE BINARY LOGS BEFORE ‘YYYY-MM-DD hh:mm:ss’;
Лог ошибок
Особенно полезен в случаях сбоев. Лог содержит информацию об остановках, запусках сервера, а также сообщения о критических ошибках. Может содержать сообщения с предупреждениями (warnings).
Основные настройки в my.cnf
Местоположение лога:
log_error = /var/log/mysql/mysql.err
Флаг, указывающий стоит ли записывать в лог в том числе предупреждения (записываются, если значение больше нуля):
log_warnings = 1
Наиболее часто использующиеся команды
Переход к новому файл лога:
shell> mysqladmin flush-logs
Копирование старой части лога (необходимо, так как в случае повторного выполнения fluch он будет удалён):
shell> mv host_name.err-old backup-directory
Лог медленных запросов
Если есть подозрение, что приложение работает медленно из-за неэффективных запросов к базе, то в первую очередь следует проверить лог медленных запросов. В случае оптимизации запросов этот лог поможет выяснить, что необходимо оптимизировать в первую очередь.
Основные настройки в my.cnf
Местоположение лога:
log_slow_queries = /var/log/mysql/mysql_slow.log
Со скольки секунд выполнения запрос считается медленным, минимальное значений — 1 секунда, по умолчанию 10 секунд:
long_query_time = 10
Если надо логировать запросы, которые не используют индексы, надо добавить строку:
log-queries-not-using-indexes
Если надо вести лог медленных команд, таких как OPTIMIZE TABLE , ANALYZE TABLE и ALTER TABLE :
log-slow-admin-statements
Лог запросов
Лог содержит информацию о подключениях и отключениях клиентов, а также все SQL запросы, которые были получены. Фактически, это временный лог. Обычно лог удаляется автоматически сразу после выполнения всех команд (т.е. как только он стал ненужным). Лог ведётся в соответствии с очередность поступления запросов. Этот лог содержит все запросы к базе данных (независимо от приложений и пользователей). Так что если есть желание (или необходимость) проанализировать, какие необходимы индексы, какие запросы могли бы оптимизированы, то этот лог как раз может помочь в таких целях. Лог полезен не только для случаев, когда необходимо знать, какие запросы выполняются с базой данных, но и в случаях, когда ясно, что возникла ошибка с базой данных, но неизвестно, какой запрос был отправлен к базе данных (например, в случае генерации динамического SQL-а). Рекомендуется защищать лог запросов паролем, так как он может данные также о паролях пользователей.
Основные настройки в my.cnf
Местоположение лога:
log = /var/log/mysql/mysql.log
Наиболее часто использующиеся команды
В отличии от других логов, перезагрузка сервера и команда fluch не инициирует создание нового лога. Но это можно сделать вручную:
shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory
Лог репликаций
Здесь логируются изменения, выполненные по инициации сервера репликаций. Как и бинарный лог, состоит из файлов, каждый из которых пронумерован.
Основные настройки в my.cnf
Местоположение лога:
relay-log = /var/log/mysql/mysql-relay-bin.log
Максимальный размер:
max_relay_log_size = 500М
Наиболее часто использующиеся команды
Начать новый файл лога можно только при остановленном дополнительном (slave) сервере:
shell> cat new_relay_log_name.index >> old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index
Команда fluch logs инициирует ротацию лога.