Мониторинг производительности 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:
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: CentOS 6, CentOS 7, EPEL, MySQL, Mytop