Мониторинг MySQL с помощью PRTG. MySQL On air

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

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

Скачать можно тут: dev.mysql.com/downloads/mysql-proxy
Первым делом нужно определиться с выбором дистрибутива. На данный момент новейшей версией является «MySQL Proxy 0.8.3 alpha», в качестве ОС у меня стоит Windows 7, поэтому все тесты будут на ней.

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

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

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

В данном контексте многое зависит от того, что у вас установлено. В качестве LAMP я использую Winginx, прочитать об этой связке можно тут: winginx.ru
По умолчанию MySQL работает на 3306 порту, его мы трогать не будем.
Нам нужно изменить порт для подключения php к базе. Для этого необходимо поправить php.ini
Найдем строку «mysql.default_port» и установим порт (по умолчанию 4040). Для более универсальной работы измените и «mysqli.default_port» на 4040

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

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

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

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then print(string.sub(packet, 2)) end end

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

C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script=C:\mysql-proxy\view.lua --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

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

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

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

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

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

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write(string.sub(packet, 2) .. "\n") file:close() print(string.sub(packet, 2)) end end
и bat файл - «view-write.bat»
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-write.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

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

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

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

По той-же схеме создаём скрипт «view-result.lua»:
function read_query(packet) if packet:byte() == proxy.COM_QUERY then print("Query: " .. string.sub(packet, 2)) local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Query: " .. string.sub(packet, 2) .. "\n") file:close() proxy.queries:append(2, string.char(proxy.COM_QUERY) .. string.sub(packet, 2), {resultset_is_needed = true}) proxy.queries:append(1, packet, {resultset_is_needed = true}) return proxy.PROXY_SEND_QUERY end end function read_query_result(inj) if inj.id == 1 then for row in inj.resultset.rows do local i = 1 local fields = {} while row[i] do if row[i] == row then break end local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i] .. "\n") file:close() print("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i]) i = i + 1 end end return proxy.PROXY_IGNORE_RESULT end end

И view-result.bat
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-result.lua" --log-file="C:\mysql-proxy\mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

В результате получаем полное логирование запросов и ответов в читаемом виде

Mytop является инструментом командной строки, который схож с top command, но предназначен для контроля MySQL. Для работы требуется версия MySQL 3.22 или более поздняя, поддерживается также текущая версия MySQL 5.x. Mytop работоспособен на большинстве систем Linux. В данной статье будем использовать за основу Ubuntu. Для работы потребуются установленные Perl, DBI и Term::ReadKey. С помощью Term::ANSIColor можно получить на выходе любой цвет, который вам захочется. Версия 0.7 работает и на windows.

Установка

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

Debian: apt-get install mytop Ubuntu: sudo apt-get install mytop

Или можно загрузить tar ball и установить его:

Tar -zxvf mytop-x.x.x.tar.gz (Номер вашей версии)
cd mytop-x.x.x. (Номер вашей версии)
perl Makefile.PL
make
make test
make install

Заполните команды сверху для извлечения данных и завершения установки. Для пользователей CentOS и RedHat потребуется загрузить репозиторий rpmforge для автоматической установки или же можно воспользоваться ручным методом установки.

Использование mytop

После установки перейдём к использованию mytop:

Mytop --prompt (зададим пароль prompt, далее нажимаем enter для подтверждения пароля)

Это стандартный способ, также можно задать пароль следующим образом:

Mytop -u root -p (здесь необходимо использовать пароль mysql, потому что задаётся пользователь "root")

Если возникает ошибка типа "root: NO PASS" или нечто подобное, введём следующее:

Mytop -u root -p xxxx (вместо "xxxx" вводим наш пароль)

Перед использованием данных команд необходимо проверить, работает ли сервер MySQL.

После проверки просто нажимаем enter.

MySQL on localhost (5.0.8) up 0+00:32:51 (11:20:10)
Queries: 19.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
Qps now: 0 Slow ups: 0.0 Threads: 1 (1/ 0) 00/00/00/00
Key Efficiency: 100% Bps in/out: 7.3/907.3 Now in/out: 8.4/1.3k
Id User Host/IP DB Time Cmd Query or State
2 root localhost test 0 Query show full process

Первая строка отображает имя сервера (localhost) и версию MySQL, запущенную на нашей системе. Строка, выделенная ядерным голубым, обозначает время, в течение которого сервер MySQ функционирует в рабочем режиме, формат обозначения дни+часы:минуты:секунды, кроме того указано текущее время системы.

Вторая строка обозначает количество запросов, которые обработала система с момента начала работы.

Третья строка отображает количество потоков.

Вторая часть изображения показывает количество потоков, которые обрабатывает MySQL. Потоки отсортированы по времени ожидания they are sorted according to their idle time least idle first.

Некоторые параметры можно пропустить с помощью mytop:

U or user username: Обычно используется для определения имени пользователя во время регистрации в mytop. По умолчанию установлен "root".

P or pass or password password: Пароли, использующиеся для авторизации пользователя.. Пароль по умолчанию не установлен, поэтому поле с паролем можно не заполнять.

H or host hostname[:port]: Для определения портов, если MySQL не использует стандартный порт. По умолчанию установлен 3306.

S or -delay seconds: Определяет временной интервал для обновления. По умолчанию установлен 5.

Для просмотра других параметров mytop введём следующее:

Man mytop

В man содержится достаточно информации о mytop.

Просмотрим файл конфигурации, созданный для получения доступа и контроля наших данных:

User=root #Задаётся имя пользователя.
pass= #Задаётся пароль.
host=localhost #имя хоста.
db=test #имя баз данных.
delay=5 #временной интервал для обновления выходных данных контроля.
port=3306 #порт для использования в mytop.
socket= #если вы определили любой сокет, mytop будет игнорировать номер порта и имя хоста, эту строку можно удалить, если она вам не нужна.
batchmode=0 #предоставляет точное число запросов, обработанных в MySQL.
header=1 #Задаётся, если вы хотите отображать заголовок. Отображается клавишей h при работе mytop.
color=1 #Задаётся, если вы хотите использовать различные цвета (только если у вас поддерживаются цвета).
idle=1 #для отображения бездействующих процессов в списке процессов.

Файл конфигурации создаётся следующим образом:

Mcedit ~/.mytop файл будет создан в базовой папке, можно использовать любой редактор

Для просмотра других опций mytop напечатаем: ?, это отобразит весь список быстрых клавиш, которые используются в mytop.

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

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

# apt-get install mytop

# yum install mytop

#pkg install mytop

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

# mysql -u root -p Enter password:

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

Mysql> create user "mytopuser"@"localhost" identified by "password"; mysql> grant all on *.* to ‘mytopuser’@’localhost’ identified by ‘password’;

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

# mcedit .mytop user=mytopuser pass=dgbple host=localhost db=asterisk port=3306 color=1 delay=3 idle=0

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

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

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

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

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

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

# chmod 700 .mytop

Запускаем утилиту:

# mytop

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

# mytop -u mytopuser -p password -d goip

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

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

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

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

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

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

Full query for which thread id:

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

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

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

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

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

Заключение

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

Вконтакте