Журналы (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 инициирует ротацию лога.

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.

powered by leo blog .

Всем привет! Заметка будет краткой, так как не хочу открывать лишние вкладки, для того чтобы вспомнить, где и как включать логи. Ниже будет описание о том, какие логи есть (кратко) и как их включить (емко).

Лог ошибок — Error Log

Если необходимо понять, по какой причине не запускается MySql сервер — error log вам в помощь. Там же еще можно прочесть сообщения о том,

По умолчанию все ошибки выводятся в консоль (stderr), в Debian ошибки пишутся в syslog, но по хорошему было бы неплохо вести этот лог в отдельном файле, а именно:

Как его перенести?

открыв файл /etc/mysql/my.conf я нашел следующую строчку:

Ок, полез в файл /etc/mysql/conf.d/mysqld_safe_syslog.cnf — там следующее содержимое:

Поняв, что все льется в syslog, я закомментировал syslog и добавил следующую строку:

Все, логи пишутся куда нужно, и я спокоен.

ps.: Для того, чтобы понять что означают те или иные ошибки, можно воспользоваться такой штукой, как perror.

Двоичный («bin’арный») лог.

В этот лог записываются все команды изменения БД, и нужен он для репликации и восстановления. Включать его не рекомендуется, если никакой репликации не планируется, так как он требователен к ресурсам.

Включается он в файле /etc/mysql/my.conf, там нужно разкомментрировать следующие строки:

• log_bin — расположение;
• expire_logs_days — срок жизни;
• max_binlog_size — максимальный размер файла.

Лог медленных запросов — mysql-slow.log.

Он будет содержать в себе запросы, которые очень нуждаются в оптимизации. По умолчанию он отключен, включается в том же /etc/mysql/my.cnf.

Если версия MySql у вас или = 5.7, то нужно исправить следующие настройки:

• log_slow_queries (slow_query_log) — путь к файлу настроек;
• long_query_time — минимальное время выполнения запроса в секундах, после которого он считается медленным.

Лог всех запросов.

Пригодиться он опять же для оптимизации и выявления ошибочных запросов, так как записывает все запросы. по умолчанию отключен. Включаем там же: /etc/mysql/my.cnf.

Настройки нужно исправить на подобные:

• general_log_file — месторасположение
• general_log — включение лога

Включить этот лог «на лету», без перезагрузки, мы можем и из консоли «mysql»:

Не забываем про logrotate.

Как я и обещал в какой-то другой статье — «возможно статья будет дополняться».

Во первых про LogRotate, приведу скрипт который используется у меня:

Как вы уже поняли, он стандартный, и он прекрасно справляется со своей работой.

Кроме того у меня возникал вопрос: «Скажите пожалуйста какими командами в SSH вычисляется нагрузка на БД ?«..

Собственно все это можно посмотреть хоть в phpmyadmin, но так же никто не запрещает воспользоваться консольным клиентом MySQL, который так и называется: mysql 🙂

Для того, чтобы в него попасть, необходимо ввести следующую команду, а после пароль

все, вы в него попали 🙂

Там мне были полезны две команды:

show status — команда предоставляет информацию по состоянию сервера.

• Aborted_clients — Количество соединений, отмененных по причине отключения клиента без надлежащего закрытия соединения. See Раздел A.2.9, «Коммуникационные ошибки / Оборванные соединения».
• Aborted_connects — Количество неудачных попыток подсоединения к серверу MySQL. See Раздел A.2.9, «Коммуникационные ошибки / Оборванные соединения».
• Bytes_received — Количество байтов, полученных от всех клиентов.
• Bytes_sent — Количество байтов, отправленных всем клиентам.
• Com_xxx — Количество запусков каждой команды xxx.
• Connections — Количество попыток подсоединения к серверу MySQL.
• Created_tmp_tables — Количество неявных временных таблиц на диске, созданных во время выполнения операторов.
• Created_tmp_tables — Количество неявных временных таблиц в памяти, созданных во время выполнения операторов.
• Created_tmp_files — Количество созданных временных файлов mysqld.
• Delayed_insert_threads — Количество используемых потоков вставки данных в режиме insert delayed.
• Delayed_writes — Количество строк, вставленных при помощи команды INSERT DELAYED.
• Delayed_errors — Количество записанных при помощи команды INSERT DELAYED строк, в которых произошли какие-либо ошибки (возможно, duplicate key).
• Flush_commands — Количество запущенных команд FLUSH.
• Handler_commit — Количество внутренних команд COMMIT.
• Handler_delete — Количество удалений строки из таблицы.
• Handler_read_first — Количество считываний из индекса первой записи. Если это значение высокое, то, по всей вероятности, сервер осуществляет много полных индексных сканирований, например, SELECT col1 FROM foo, предполагая, что col1 проиндексирован.
• Handler_read_key — Количество запросов на чтение строки, основанных на ключе. Высокое значение переменной говорит о том, что ваши запросы и таблицы проиндексированы надлежащим образом.
• Handler_read_next — Количество запросов на чтение следующей строки в порядке расположения ключей. Это значение будет увеличиваться, если производится запрос индексного столбца с ограничением по размеру. Значение также увеличивается во время проведения индексного сканирования.
• Handler_read_prev — Количество запросов на чтение предыдущей строки в порядке расположения ключей. В большинстве случаев используется для оптимизации ORDER BY . DESC.
• Handler_read_rnd — Количество запросов на чтение строки, основанных на фиксированной позиции. Значение будет высоким, если выполняется много запросов, требующих сортировки результатов.
• Handler_read_rnd_next — Количество запросов на чтение следующей строки из файла данных. Данное значение будет высоким, если производится много сканирований таблиц. Обычно это означает, что ваши таблицы не проиндексированы надлежащим образом или ваши запросы не используют преимущества индексов.
• Handler_rollback — Количество внутренних команд ROLLBACK.
• Handler_update — Количество запросов на обновление строки в таблице.
• Handler_write — Количество запросов на вставку строки в таблицу.
• Key_blocks_used — Количество используемых блоков в кэше ключей.
• Key_read_requests — Количество запросов на чтение блока ключей из кэша.
• Key_reads — Количество физических считываний блока ключей с диска.
• Key_write_requests — Количество запросов на запись блока ключей в кэш.
• Key_writes — Количество физических записей блоков ключей на диск.
• Max_used_connections — Максимальное количество одновременно используемых соединений.
• Not_flushed_key_blocks — Блоки ключей в кэше ключей, которые были изменены, но еще не записаны на диск.
• Not_flushed_delayed_rows — Количество строк, стоящих в очереди на запись в запросах INSERT DELAY.
• Open_tables — Количество открытых таблиц.
• Open_files — Количество открытых файлов.
• Open_streams — Количество открытых потоков (в основном используется для журналирования).
• Opened_tables — Количество открывавшихся таблиц.
• Rpl_status — Статус отказобезопасной репликации (еще не используется).
• Select_full_join — Количество соединений без ключей (если это значение равно 0, необходимо внимательно проверить индексы своих таблиц).
• Select_full_range_join — Количество соединений, где был использован поиск по диапазону в справочной таблице.
• Select_range — Количество соединений, в которых использовались диапазоны в первой таблице. (Обычно это значение не критично, даже если оно велико)
  Select_scan — Количество соединений, в которых проводилось первое сканирование первой таблицы.
• Select_range_check — Количество соединений без ключей, в которых проверка использования ключей производится после каждой строки (если это значение равно 0, необходимо внимательно проверить индексы своих таблиц).
• Questions — Количество запросов, направленных на сервер.
• Slave_open_temp_tables — Количество временных таблиц, открытых в настоящий момент потоком подчиненного компьютера.
• Slave_running — Содержит значение ON, если это подчиненный компьютер, подключенный к головному компьютеру.
• Slow_launch_threads — Количество потоков, создание которых заняло больше, чем указано в slow_launch_time.
• Slow_queries — Количество запросов, обработка которых заняла больше времени, чем long_query_time. See Раздел 4.9.5, «Журнал медленных запросов».
• Sort_merge_passes — Количество объединений, осуществленных алгоритмом сортировки. Если это значение велико, следует увеличить sort_buffer_size.
• Sort_range — Количество сортировок, которые осуществлялись в диапазонах.
• Sort_rows — Количество отсортированных строк.
• Sort_scan — Количество сортировок, осуществленных путем сканирования таблицы.
• ssl_xxx — Переменные, используемые SSL; еще не реализовано.
• Table_locks_immediate — Количество запросов на немедленную блокировку таблицы. Доступно начиная с версии 3.23.33.
• Table_locks_waited — Количество запросов, когда немедленная блокировка не могла быть осуществлена и требовалось время на ожидание. Если это значение велико, и у вас есть проблемы с производительностью, сначала необходимо оптимизировать свои запросы, а затем либо разделить таблицы, либо использовать репликацию. Доступно начиная с версии 3.23.33.
• Threads_cached — Количество потоков в кэше потоков.
• Threads_connected — Количество открытых в настоящий момент соединений.
• Threads_created — Количество потоков, созданных для управления соединениями.
• Threads_running — Количество не простаивающих потоков.
• Uptime — Время в секундах, в течение которого сервер находится в работе.

Некоторые примечания к приведенной выше информации:

• Если значение Opened_tables велико, возможно, что значение переменной table_cache слишком мало.
• Если значение Key_reads велико, возможно, что значение переменной key_buffer_size слишком мало. Частоту неуспешных обращений к кэшу можно вычислить так: Key_reads/Key_read_requests.
• Если значение Handler_read_rnd велико, возможно, поступает слишком много запросов, требующих от MySQL полного сканирования таблиц или у вас есть соединения, которые не используют ключи надлежащим образом.
• Если значение Threads_created велико, возможно, необходимо увеличить значение переменной thread_cache_size. Частоту успешных обращений к кэшу можно вычислить при помощи Threads_created/Connections.
• Если значение Created_tmp_disk_tables велико, возможно, необходимо увеличить значение переменной tmp_table_size, чтобы временные таблицы располагались в памяти, а не на жестком диске.

show processlist — показывает, какие потоки запущены в настоящий момент. Пример ее вывода:

Начиная с 4.0.12, MySQL сообщает имя хоста для TCP/IP соединений как имя_хоста:клиентский_порт с тем, чтобы было проще понять, какой клиент чем занят.

Эта команда очень полезна, если выдается сообщение об ошибке ‘too many connections’ (слишком много соединений) и необходимо выяснить, что происходит. MySQL резервирует одно дополнительное соединение для клиента с привилегией SUPER, чтобы у вас всегда была возможность войти в систему и произвести проверку (предполагается, что вы не станете раздавать эту привилегию всем своим пользователям).

Некоторые состояния обычно можно увидеть в mysqladmin processlist.

• Checking table — Поток осуществляет [автоматическую] проверку таблицы.
• Closing tables — Означает, что поток записывает измененные данные таблиц на диск и закрывает использующиеся таблицы. Выполнение этой операции должно произойти быстро. Если на нее уходит значительное время, убедитесь, что диск не переполнен или что диск не используется слишком интенсивно.
• Connect Out — Подчиненный компьютер, подсоединенный к головному компьютеру.
• Copying to tmp table on disk — Набор временных результатов превысил tmp_table_size, и теперь поток изменяет таблицу временных данных, расположенную в памяти, на дисковую таблицу, чтобы сохранить память.
• Creating tmp table — Поток создает временную таблицу, чтобы хранить часть результатов для запроса.
• deleting from main table — При запуске первой части удаления нескольких таблиц удаление производится только начиная с первой таблицы.
• deleting from reference tables — При запуске второй части удаления нескольких таблиц удаляются совпадающие строки из других таблиц.
• Flushing tables — Поток запускает команду FLUSH TABLES и ожидает, пока все потоки закроют свои таблицы.
• Killed — Кто-то направил команду на закрытие потока, и поток будет закрыт при следующей проверке флага закрытия. Флаг проверяется при каждом основном цикле в MySQL, но в некоторых случаях закрытие потока может занять некоторое время. Если поток заблокирован другим потоком, закрытие будет произведено сразу после того, как другой поток снимет блокировку.
• Sending data — Поток обрабатывает строки для оператора SELECT, а также направляет данные клиенту.
• Sorting for group — Поток осуществляет сортировку в соответствии с GROUP BY.
• Sorting for order — Поток осуществляет сортировку в соответствии с ORDER BY.
• Opening tables — Это просто означает, что поток пытается открыть таблицу. Такая процедура осуществляется довольно быстро, если что-либо не мешает открытию. Например, команды ALTER TABLE или LOCK TABLE могут помешать открытию таблицы, пока выполнение команды не будет завершено.
• Removing duplicates — Запрос использовал команду SELECT DISTINCT таким образом, что MySQL не смог произвести оптимизацию на начальном этапе. Поэтому MySQL перед отправкой результатов клиенту должен выполнить дополнительное удаление всех дублирующихся строк.
• Reopen table — Поток заблокировал таблицу, но обнаружил, что после блокировки структура таблицы изменилась. Он снял блокировку, закрыл таблицу и теперь пытается повторно ее открыть.
• Repair by sorting — Код восстановления использует сортировку для создания индексов.
• Repair with keycache — Код восстановления использует создание ключей один за другим, через кэш ключей. Это намного медленнее, чем Repair by sorting.
• Searching rows for update — Поток осуществляет первую фазу — производит поиск всех совпадающих строк, чтобы затем обновить их. Это действие
  необходимо выполнить, если команда UPDATE изменяет индекс, который используется для поиска указанных строк.
• Sleeping — Поток ожидает, когда клиент направит ему новую команду.
• System lock — Поток ожидает получения внешней системной блокировки таблицы. Если не используется несколько серверов mysqld, которые получают доступ к одним и тем же таблицам, системную блокировку можно отключить при помощи параметра —skip-external-locking.
• Upgrading lock — Обработчик INSERT DELAYED пытается заблокировать таблицу, чтобы вставить строки.
• Updating — Поток производит поиск строк, которые необходимо обновить, и обновляет их.
• User Lock — Поток ожидает GET_LOCK().
• Waiting for tables — Поток получил уведомление, что структура таблицы изменилась, и ему необходимо повторно открыть таблицу, чтобы получить новую структуру. Чтобы повторно открыть таблицу, он должен подождать, пока ее не закроют все остальные потоки. Это уведомление выдается, если другой поток воспользовался командой FLUSH TABLES или к таблице была применена одна из следующих команд: FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE или OPTIMIZE TABLE. Обработчик INSERT DELAYED завершил работу со всеми вставками и ожидает новые.

Большинство состояний — это очень быстрые операции. Если потоки остаются в любом из этих состояний на протяжении большого количества секунд, то, возможно, возникла какая-то проблема, которую следует устранить.

Существует еще несколько состояний, не упомянутых выше, но большинство из них полезны только для поиска ошибок в mysqld.

Описания я взял с руководства, возможно немного не актуального, но на первых парах описаны все основные моменты. Остальное — смотрим полные версии руководства.