Мониторинг загрузки MySQL

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

Установка для разных систем:

Подключаемся к нашему MySQL:

Желательно для каждой базы или для разных программ создавать разные учетные записи. Поэтому создадим и опишем права на нужные базы пользователю mytopuser:

Создадим файл настроек в домашнем каталоге

/.mytop и в него вносим конфигурацию подключения:

где user и pass – логин и пароль, что мы создавали.

host, port – адрес и порт MySQL сервера

db – база, к которой подлючаемся

delay – чтобы дисплей mytop обновлялся каждые 3 секунды

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

Сменим права на доступ к этому файлу:

Можно вывести данные по определенной базе, например:

В первой строке: имя хоста и версия запущенного сервера MySQL. Верхний правый угол – uptime сервера (время непрерывной работы) дней+часов+минут+секунд.

Во второй строке: общее количество обработанных запросов к базе (Queries:), среднее количество запросов в секунду (qps:), и кол-во медленных запросов (Slow:).

В третьей строке показывает информацию о процессах: всего, активных, кешированных.

В четвертой строке: сведения об эффективности буфера ключей – как часто MySQL находит ключи в буфере, не обращаясь к диску, среднее число байтов, посланных и полученных сервером, и число байтов, пересылаемых в данный момент.

Вторая часть экрана показывает все активные потоки (в том числе тот, который использует MyTOP). Здесь выводятся в табличной форме имя пользователя, базы данных и узла, а также текущий запрос и состояние.

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

Введите id потока, чтобы просмотреть необходимый запрос.

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

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

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

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

Этот инструмент позволяет вовремя обнаружить и оптимизировать сложные запросы SQL, тем самым увеличивая общую производительность сервера.

Мониторинг производительности MySQL при помощи Mytop

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:

sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm

sudo rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Прежде чем продолжить, убедитесь, что репозиторий 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 необходимы учетные данные для доступа к БД, их можно предоставить через командную строку или указать в конфигурационном файле. Для большей безопасности рекомендуется использовать опцию –prompt, которая будет каждый раз запрашивать пароль.

Чтобы подключиться к 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 можно найти здесь.