Что такое монитор mysql

Обновлено: 25.01.2025

Mytop – это открытый инструмент командной строки для мониторинга производительности MySQL, разработанный по примеру инструмента мониторинга системы Linux под названием top. Mytop подключается к MySQL и периодически запускает команды show processlist и show global status, а затем резюмирует информацию в удобном формате. При помощи Mytop можно в режиме реального времени отслеживать потоки, запросы и аптайм MySQL, узнать, какой пользователь отправляет запросы к той или иной базе данных, определить медленные запросы и многое другое. Вся эта информация может быть использована для оптимизации производительности сервера MySQL.

Данное руководство поможет установить и настроить mytop.

Требования

Сервер CentOS 7 с 64-битной архитектурой (CentOS 6 также подойдёт).
Не-root пользователь с доступом к sudo (нужен для запуска команд руководства); чтобы создать такого пользователя, обратитесь к этой статье.
Предварительно установленный сервер MySQL. Инструкции по установке можно найти здесь.

1: Установка mytop

Для начала нужно добавить репозиторий EPEL (Extra Packages for Enterprise Linux), который управляет высококачественными пакетами дополнительных программ с открытым исходным кодом. Выполните следующую команду, чтобы добавить и включить репозиторий EPEL:

Прежде чем продолжить, убедитесь, что репозиторий EPEL включен:

sudo yum repolist

Если EPEL включен, он появится в выводе:

epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64

Теперь нужно защитить базовые пакеты системы от воздействия репозитория EPEL при помощи плагина protectbase.

sudo yum install yum-plugin-protectbase.noarch -y

Плагин protectbase защищает репозитории yum от обновления. Таким образом, пакеты в защищённых репозиториях не будут обновляться или переопределяться пакетами из других репозиториев (даже боле новыми).

Теперь можно установить пакет mytop:

sudo yum install mytop -y

Эта команда установит mytop и его зависимости, в основном это модули perl.

2: Настройка mytop

Прежде чем запустить mytop, создайте пользовательский конфигурационный файл по имени .mytop.

sudo nano /root/.mytop

Добавьте следующие настройки, а затем сохраните и закройте файл.

host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
color=1
idle=1

Этот конфигурационный файл будет использоваться при запуске mytop.

Отредактируйте этот файл согласно требованиям вашего сервера. К примеру, опция delay задаёт промежуток времени между обновлениями дисплея (в секундах). Чтобы дисплей mytop обновлялся каждые 3 секунды, отредактируйте файл /root/.mytop:

sudo nano /root/.mytop
delay=3

Параметр idle определяет, нужно ли выводить в mytop ожидающие потоки. По умолчанию такие потоки учитываются. Опуская ожидающие потоки, mytop меняет порядок сортировки по умолчанию, выводя длинные запросы в верхней части списка. Чтобы опустить ожидающие потоки, отредактируйте этот параметр:

При необходимости можно обратиться к справочнику по mytop, он содержит описание каждого параметра в конфигурационном файле. Чтобы получить доступ к справочнику, используйте команду:

Чтобы закрыть справочник, нажмите q.

3: Подключение к mytop

Теперь можно подключить mytop и MySQL.

Чтобы подключиться к mytop, введите:

sudo mytop --prompt

Предоставьте root-пароль MySQL. Также можно использовать аргументы (полный список аргументов можно найти в справочнике). К примеру, чтобы подключить к mytop другого пользователя mysql (в данном примере его зовут 8host), нужно добавить в команду его имя:

sudo mytop -u 8host --prompt

Чтобы подключить определённую БД, используйте команду:

sudo mytop -d databasename --prompt

Чтобы закрыть mytop и вернуться в сессию оболочки, введите q.

4: Просмотр и интерпретация вывода mytop

После подключения к mytop на экране появится просмотр потоков, который имеет примерно такой вид:

MySQL on localhost (5.5.41-MariaDB) up 0+00:05:52 [01:33:15] Queries: 148 qps: 0 Slow: 0.0 Se/In/Up/De(%): 09/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 6 ( 5/ 0) 67/00/00/00
Key Efficiency: 2.0% Bps in/out: 14.7/320.7k Now in/out: 192.5/731.8k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
2 root localhost mysql 0 Query show full processlist
16 root localhost 0 Sleep
17 root localhost testdb 0 Query SELECT * FROM dept_emp
18 root localhost testdb 0 Query SELECT * FROM dept_emp
19 root localhost testdb 0 Query SELECT * FROM dept_emp
20 root localhost testdb 0 Query SELECT * FROM dept_emp

Примечание: Чтобы вернуться в этот список, нажмите t.

Как видите, этот экран разделён на две части. Первые четыре линии – это заголовки, которые можно включить или выключить с помощью комбинации клавиш Shift-H. Заголовок содержит сводную информацию о сервере MySQL.

Первая строка определяет имя сервера и версию MySQL. Справа показан аптайм процесса MySQL в формате дни + часы: минуты: секунды.
Вторая строка отображает общее количество обработанных запросов (в данном случае), среднее количество запросов в секунду, количество медленных запросов, и процентное соотношение запросов Select, Insert, Update и Delete.
Третья строка показывает значения в режиме реального времени с момента последнего обновления mytop. Нормальным периодом обновления mytop считается 5 секунд, так что если за последние 5 секунд были проведены100 запросов с момента обновления, то qps now будет показывать 20. Первое поле показывает количество запросов в секунду (qps now: 2). Второе значение показывает количество медленных запросов в секунду. В Тем: 6 (5/0) сегмента указывает есть всего 6 связанные темы, 5 являются активными (один спит), и есть 0 нити в кэше потоков. Последнее поле в третьей строке показан процент запросов, как в предыдущей строке, но с прошлого MyTOP обновления. Сегмент Threads: 6 ( 5/ 0) показывает, что на данный момент всего есть 6 подключенных потоков, из которых 5 активны, 1 в ожидании и 0 в кэше потоков. Последнее поле в третьей строке показывает процентное соотношение запросов, как в предыдущей строке, но с момента последнего обновления mytop.
Четвертая строка отображает производительность буфера ключей (т.е. как часто ключи считываются из буфера, а не с диска) и количество отправленных и полученных байт (общее и с момента последнего обновления mytop). К примеру, Key Efficiency: 2.0% говорит, что из буфера получено 2% ключей, а Bps in/out: 14.7/320.7k показывает, что с момента запуска сервер MySQL в среднем получил 14.7kbps входящего трафика и 320.7kbps исходящего трафика. Now in/out тоже показывает трафик, но с момента последнего обновления mytop.

Вторая часть вывода показывает текущие потоки MySQL, отсортированные по времени простоя (сначала идут потоки с наименьшим показателем простоя). Чтобы обратить порядок сортировки, нажмите клавишу О. Также здесь показаны идентификатор потока, имя пользователя, хост, с которого подключается пользователь, БД, к которой он подключен, количество секунд простоя, команда или состояние потока, и первая часть информации запроса. Если поток в состоянии Query (т.е. Cmd показывает Query), то столбец Query or State будет показывать первую часть запущенного запроса. Если состояние потока Sleep или Idle, то столбец Query or State будет пуст. В данном примере поток с id 2 это собственно mytop, запускающий команду show processlis. Поток с id 16 находится в режиме ожидания, а поток с id 17 выполняет запрос SELECT в БД testdb.

Теперь вы знакомы с основами интерпретации вывода mytop. Инструмент mytop можно использовать, чтобы собрать больше информации о потоках и запросах MySQL. Рассмотрим следующий вывод mytop:

MySQL on localhost (5.5.41-MariaDB) up 0+00:13:10 [23:54:45] Queries: 2.8k qps: 4 Slow: 51.0 Se/In/Up/De(%): 45/00/00/00
qps now: 17 Slow qps: 0.0 Threads: 52 ( 51/ 0) 96/00/00/00
Key Efficiency: 100.0% Bps in/out: 215.4/ 7.6M Now in/out: 2.0k/16.2M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
34 root localhost testdb 0 Query show full processlist
1241 root localhost 1 Sleep
1242 root localhost testdb 1 Query SELECT * FROM dept_emp
1243 root localhost testdb 1 Query SELECT * FROM dept_emp
1244 root localhost testdb 1 Query SELECT * FROM dept_emp
1245 root localhost testdb 1 Query SELECT * FROM dept_emp
1246 root localhost testdb 1 Query SELECT * FROM dept_emp
1247 root localhost testdb 1 Query SELECT * FROM dept_emp

В стандартном выводе mytop запросы сокращены. Чтобы просмотреть запись полностью, нажмите F. Программа спросит:

Full query for which thread id:

Введите id потока, чтобы просмотреть необходимый запрос. Для примера введём 1244, и на экране появится:

Thread 1244 was executing following query:
SELECT * FROM dept_emp WHERE .
-- paused. press any key to resume or (e) to explain --

Чтобы получить объяснение запроса, нажмите Е, и тогда программа предоставит данные о запущенном запросе, которые помогут выяснить, оптимизируется ли запрос. EXPLAIN – один из самых производительных инструментов для понимания и оптимизации запросов MySQL. Например:

EXPLAIN SELECT * FROM dept_emp:
*** row 1 ***
table: dept_emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 332289
Extra: NULL
-- paused. press any key to resume --

Чтобы закрыть этот режим, нажмите любую клавишу; чтобы вернуть стандартный вывод, нажмите t.

Еще один полезный вид mytop доступен при нажатии клавиши С. Он показывает запущенные команды.

Command Total Pct | Last Pct
------- ----- --- | ---- ---
select 1782 55% | 100 8%
show status 723 22% | 533 45%
show processlist 708 22% | 532 45%
change db 2 0% | 0 0%
show variables 1 0% | 0 0%
Compression 0 0% | 0 0%

Столбец Command показывает тип запущенной команды или запроса. Столбец Total указывает общее количество команд такого типа с момента запуска сервера, а Pct показывает это число в процентном соотношении. Столбец Last показывает количество команд такого типа с момента последнего обновления mytop. В целом эти данные дают представление о работе сервера MySQL в краткосрочной и долгосрочной перспективе.

Конечно, данное руководство не может охватить все функции mytop, но теперь вы знакомы с самыми основными функциями данного инструмента. Чтобы просмотреть полный список опций, нажмите клавишу со знаком вопроса, когда mytop запущен.

Заключение

Итак, теперь вы знакомы с основами работы с mytop. Этот инструмент позволяет вовремя обнаружить и оптимизировать сложные запросы SQL, тем самым увеличивая общую производительность сервера. Более подробную информацию об оптимизации запросов и таблиц MySQL и MariaDB можно найти здесь.

Разбираясь как работает та или иная CMS приходится использовать различные инструменты, облегчающие работу.
Наиболее интересная тема — это работа с баз(ой|ами) данных. Естественно для изучения запросов и результатов запросов нужно использовать что-то универсальное. Что-то, что будет работать стабильно как с известным движком, так и с самописной системой.
Предположим у вас оказалась система управления контентом и вам необходимо посмотреть как реализовано добавление новых пользователей или смена паролей.

Большинство инструментов позволяющих мониторить работу с БД являются платными [раз, два]. Я хотел что-то более легкое и удобное, поэтому выбрал mysql-proxy. Хотя возможности утилиты гораздо шире чем мне требуется, я опишу лишь основное. Работает как под Windows, так и под Unix системами.

После нажатия на кнопку «Download» вас попросят авторизироваться либо зарегистрировать, но снизу есть ссылка для скачивания без лишних действий. (В репозиториях Ubuntu и Debian есть готовые пакеты, так что: sudo apt-get install mysql-proxy)

Хочу заметить, что при размере в 7.9Мб в дистрибутив входит Lua с поддержкой основых модулей.

После скачивания архива его нужно распаковать в удобную для вас директорию, возьмем для примера C:\mysql-proxy
Для запуска приложения нужно определиться с параметрами.

Подготовительная часть пройдена, перейдем к делу.

Вариант 1. Мониторинг запросов.

Для простого мониторинга необходимо использовать Lua скрипт. Как я уже говорил Lua идет в комплекте, так что ничего нового устанавливать не надо.
Создадим простой скрипт view.lua в директории C:\mysql-proxy\ с содержимым:

Теперь можно проверить результат.
Для удобства создадим в директории C:\mysql-proxy файл view.bat c содержимым:

--proxy-backend-addresses — адрес MySQL сервера на который будем проксировать запрос.

Запустив вэб-сервер и выполнив какие либо запросы к базе можете увидеть такое:

Запросы отображаются, хорошо.

Вариант 2. Мониторинг запросов и запись в файл.

Для записи запросов в файл будем использовать штатные возможности Lua.
Создадим файл view-write.lua в директории C:\mysql-proxy\ с содержимым:

и bat файл — «view-write.bat»

Результат после выполнения запросов (по адресу «C:\mysql-proxy\sql-log.txt»)

Помимо отображения самих запросов, нам может понадобиться вывод результатов этих запросов.

Вариант 3. Запросы и результат

По той-же схеме создаём скрипт «view-result.lua»:

Область применения: База данных Azure для MySQL — отдельный сервер

Мониторинг данных о серверах помогает устранять неполадки и оптимизировать рабочую нагрузку. База данных Azure для MySQL предоставляет различные метрики, позволяющие понять действия сервера.

Метрики

Все метрики Azure записываются ежеминутно, и каждая из них предоставляет данные за последние 30 дней. Вы можете настроить оповещения на основе метрик. Пошаговые инструкции см. в статье Использование портала Azure для настройки оповещений на основе метрик для базы данных Azure для MySQL. Другие задачи включают настройку автоматических действий, выполнение расширенной аналитики и архивирование журнала. Дополнительные сведения см. в статье Обзор метрик в Microsoft Azure.

Список метрик

Для базы данных Azure для MySQL доступны следующие метрики:

Журналы сервера

На сервере можно включить ведение журналов медленных запросов и журналов аудита. Эти журналы также доступны с помощью журналов диагностики Azure в Azure Monitor, Центрах событий и учетной записи хранения. Дополнительные сведения о ведении журналов см. в статьях о журналах аудита и журналах медленных запросов.

Хранилище запросов

Хранилище запросов — это функция, позволяющая отслеживать производительность запросов с течением времени, включая статистику выполнения запросов и события ожидания. Эта функция сохраняет сведения о производительности среды выполнения запросов в схеме mysql. Вы можете управлять процессами сбора и хранения данных с помощью различных приспособлений конфигурации.

Анализ производительности запросов

Средство Анализ производительности запросов работает совместно с хранилищем запросов для предоставления визуализаций, доступных на портале Azure. Эти диаграммы позволяют определить основные запросы, влияющие на производительность. Средство анализа производительности запросов находится в разделе Интеллектуальное управление производительностью на странице портала службы Базы данных Azure для MySQL.

Уведомления о плановом обслуживании

Уведомления о плановом обслуживании позволяют получать оповещения о запланированном мероприятии в Базе данных Azure для MySQL. Эти уведомления интегрированы в плановое обслуживание Работоспособность служб и позволяют централизованно просматривать все запланированные профилактические работы для подписок. Они также помогают масштабировать уведомление до нужных аудиторий для разных групп ресурсов, так как у вас могут быть разные контакты, ответственные за различные ресурсы. Уведомление о предстоящем обслуживании приходит за 72 часа.

Дополнительные сведения о настройке уведомлений см. в документации Уведомление о плановом обслуживании.

С появлением стандартных готовых шаблонов для различных приложений жизнь с заббиксом стала значительно проще. Сегодня я покажу это на примере мониторинга Mysql сервера в Zabbix 5 с использованием стандартного шаблона. Все стало не просто, а очень просто. Практически ничего делать не надо, разработчики все сделали за нас.

Если у вас есть желание научиться строить и поддерживать высокодоступные и надежные системы, рекомендую познакомиться с онлайн-курсом «DevOps практики и инструменты» в OTUS. Курс не для новичков, для поступления нужно пройти .

Введение

В данном случае я буду использовать шаблон из директории /db/mysql_agent/. Он написан для старого агента. Напомню, что начиная с версии 4.4 доступна новая версия агента, написанная на Go - zabbix_agent2. Для него появился новый функционал и новые шаблоны. Я пока буду использовать старого агента, так как с новым еще не разбирался.

Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:

То же самое на Debian 10, если предпочитаете его:

Ставьте себе сервер и погнали настраивать.

Подготовка mysql к мониторингу

Для примера настроим мониторинг Mysql на самом сервере мониторинга Zabbix. Так как это часто узкое место производительности системы, мониторинг базы zabbix лишним не будет. Первым делом добавим новые параметры в агенте. Для этого создаем конфигурационный файл /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf следующего содержания.

После этого сразу перезапустим zabbix-agent.

Дальше идем в консоль mysql и создаем пользователя, от которого будет работать мониторинг. Ему достаточно ограниченных прав на чтение.

Теперь смотрим, где у нас домашняя директория пользователя zabbix.

У меня ее не было, так что создаем.

Кладем в эту директорию конфиг .my.cnf с реквизитами доступа к серверу mysql.

Назначаем пользователя zabbix владельцем своей домашней директории и файла в ней. Файлу ограничиваем доступ.

Подготовка к мониторингу mysql сервера завершена. Идем теперь в web интерфейс системы мониторинга Zabbix.

Настройка мониторинга Mysql сервера

В веб интерфейсе идем в раздел Настройка -> Шаблоны и импортируем шаблон template_db_mysql_agent.xml.

После этого прикрепляем добавленный шаблон к хосту, где мы только что настроили zabbix-agent и добавили пользователя mysql. Для того, чтобы сразу увидеть все метрики, принудительно выполним сбор данных. Для начала вручную запустим правила автообнаружения, так как у них интервал проверок 1 час. Не хочется столько времени ждать данных. Идем в хост, далее во вкладку Правила обнаружения. Выбираем 2 правила от шаблона mysql и запускаем их.

Ждем несколько секунд и переходим на вкладку Элементы данных. Фильтруем элементы по названию группы MySQL и Zabbix raw items.

Теперь переходим к списку элементов данных. Выделяем все элементы, которые относятся к Mysql и имеют тип Zabbix Agent и запускаем их принудительную проверку. Основной элемент тут - MySQL: Get status variables. Почти все итемы получаются в результате предобработки данных с него.

После этого идем в раздел Мониторинг -> Последние данные и наблюдаем собираемые метрики.

На этом по базовой настройке мониторинга сервера mysql все. Дальше раскрою некоторые нюансы.

Мониторинг репликации MySQL

Вообще, шаблон достаточно навороченный. Там и автообнаружение, и зависимые элементы с предобработкой xml, и предобработка с помощью JavaScript. Рассмотрю отдельно некоторые моменты представленного шаблона zabbix по мониторингу mysql. Во-первых, некоторые параметры задаются с помощью макросов. Вот их список.

Из настраиваемых параметров ясно, что мониторить можно не только локальный mysql сервер, но и удаленный, задав параметры подключения к нему.

Так же в шаблоне реализован мониторинг репликации базы данных. Для этого есть отдельное правила автообнаружения с триггерами. Теперь моя старая статья по мониторингу репликации mysql стала не актуальна. Этот же функционал реализован в базовом шаблоне. Если у вас не настроена репликация, то автообнаружение просто не найдет ничего. Можно это правило выключить.

Для мониторинга репликации автоматически создаются 4 триггера.

Replication lag is too high (over for 5m) - отставание реплики больше заданного в макросе времени. По умолчанию 30 минут.
The slave I/O thread is not connected to a replication master - Демон по сбору бинарного лога запущен, но не подключен к мастеру. Его параметр slave_io_running имеет значение не Yes.
The slave I/O thread is not running - демон по сбору бинарного лога не запущен. Его параметр slave_io_running равен No.
The SQL thread is not running - демон выполнения команд локального relay лога не запущен. Его парметр slave_sql_running равен No.

В целом, этих четырех метрик достаточно для мониторинга репликации. Я так же настраивал мониторинг именно их.

Триггеры шаблона

Для полноты картины, поясню остальные триггеры шаблона, чтобы у вас было понимание, за чем они следят и как правильно реагировать на них. Ниже список триггеров шаблона для мониторинга mysql сервера.

Buffer pool utilization is too low (less % for 5m) - под innodb пул выделено слишком много памяти и она не используется вся. Триггер чисто информационный, делать ничего не надо, если у вас нет дефицита памяти на сервере. Если нехватка оперативной памяти есть, то имеет смысл забрать немного памяти у mysql и передать другому приложению. Настраивается потребление памяти пулом параметром innodb_buffer_pool_size.
Failed to get items (no data for 30m) - от mysql сервера не поступают новые данные мониторинга в течении 30 минут. Имеет смысл уменьшить этот интервал до 5-10 минут.
Refused connections (max_connections limit reached) - срабатывает ограничение на максимальное количество подключений к mysql. Увеличить его можно параметром mysql сервера - max_connections. Его необходимо увеличить, если позволяют возможности сервера. Напомню, что увеличенное количество подключений требует увеличения потребления оперативной памяти. Если у вас ее уже не хватает, нет смысла увеличивать число подключений. Нужно решать вопрос с потреблением памяти.
Server has aborted connections (over for 5m) - сервер отклонил подключений выше заданного порога в макросе. Надо идти в лог mysql сервера и разбираться в причинах этого события. Скорее всего там будут подсказки.
Server has slow queries (over for 5m) - количество медленных запросов выше установленного макросом предела. Надо идти и разбираться с медленными запросами. Тема не самая простая. Надо заниматься профилированием запросов и решать проблемы по факту - добавлением индексов, редактированием запросов, увеличения ресурсов mysql сервера и т.д.
Service has been restarted (uptime < 10m) - информационный триггер, срабатывающий на перезапуск mysql сервера (не ребут самого сервера).
Service is down - служба mysql не запущена.
Version has changed (new version value received: ) - версия mysql сервера изменилась. Тоже информационный триггер, сработает, к примеру, после обновления mysql сервера.

На этом по мониторингу MySQL сервера с помощью стандартного шаблона Zabbix все. Надеюсь, я доступно и понятно раскрыл данную тему. Если у вас есть замечания, жду вас в комментариях.

Заключение

Не понравилась статья и хочешь научить меня администрировать? Пожалуйста, я люблю учиться. Комментарии в твоем распоряжении. Расскажи, как сделать правильно!

Здорово, что разработчики сами занялись написанием готовых шаблонов для мониторинга сетевых устройств, операционных систем и приложений. Поняли, что это будет способствовать развитию продукта. Многие шаблоны, которые разрабатывали сами пользователи, становятся неактуальными, так как разработчики их делают лучше. Собственно, это и логично. Кто лучше всех знает продукт, как не они. За последнее время появилось много обновления по этой теме. Надеюсь, будет еще больше.

Читайте также: