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

Published by Leave your thoughts

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:

CentOS 7:

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

CentOS 6:

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 меняет порядок сортировки по умолчанию, выводя длинные запросы в верхней части списка. Чтобы опустить ожидающие потоки, отредактируйте этот параметр:

idle=0

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

man 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 можно найти здесь.

Tags: , , , ,

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *


*

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>