Innotop

Введение

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

1. Установка CentOS 8.
2. Настройка CentOS 8.
3. Установка и настройка zabbix сервера.

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

1. Установка Debian 10.
2. Базовая настройка Debian.
3. Установка и настройка zabbix на debian.

Несколько слов о том, что именно мы будем мониторить. Посмотреть статус репликации можно с помощью простой команды в консоли mysql:

MariaDB > show slave status\G;

Нас будут интересовать три параметра:

• Seconds_Behind_Master — то, насколько слейв сервер отстает от мастера в репликации.
• Slave_IO_Running — индикатор работы демона по сбору бинарного лога с мастера и записи его в локальный relay лог.
• Slave_SQL_Running — индикатор выполнения команд из локального relay лога.

Первый параметр в идеале должен равняться нулю, то есть slave сервере следует за мастером непрерывно. Если значение начинает увеличиваться, срабатывает триггер и оповещает о том, что реплика начинает отставать. Два других параметра должны выдавать значение Yes. Если это не так, то тоже срабатывает триггер и шлет оповещение о том, что репликация не работает.

Реализовывать будем так же как и в случае с мониторингом nginx и php-fpm через скрипт и UserParameter. Если у вас репликация master-mastert, то настраиваете мониторинг на обоих серверах.

Я буду настраивать мониторинг на сервере CentOS 7, но в данном случае это не имеет принципиального значения. Настройки будут идентичны практически на любом linux дистрибутиве.

Введение

Напоминаю одну важную деталь. Если вы ставите Zabbix Server не с нуля, а обновляете старую версию, у вас не обновляются стандартные шаблоны. А они последнее время сильно изменились, плюс появились новые. Посмотреть их можно на github — https://github.com/zabbix/zabbix/tree/master/templates.

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

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

1. Установка CentOS 8.
2. Настройка CentOS 8.
3. Установка и настройка zabbix сервера.

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

1. Установка Debian 10.
2. Базовая настройка Debian.
3. Установка и настройка zabbix на debian.

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

Обзор функциональности

Синтаксис вызова утилиты:

sysbench --test=имя-теста  команда

Команды, которые можно использовать в тестах:

• prepare — подготовка теста (создание таблиц, вставка данных и т.п.) если актуально
• run — выполнение теста
• cleanup — очистка данных (после этапа подготовки)
• help — выводит дополнительные параметры теста

Параметр задает имя теста, который следует выполнять. Sysbench включает в себя несколько тестов:

memory

Этот тест служит для измерения производительности последовательных операций чтения/записи в оперативную память.

fileio

Этот тест используется для симуляции разнообразной нагрузки на файловую подсистему. При подготовке теста создается определенное количество файлов (указанного размера). Затем, при выполнении теста, над этими файлами происходят операции чтения/записи в несколько потоков.

sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw prepare
sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run
sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw cleanup

Что такое HAProxy?

HAProxy, или High Availability Proxy, является программным балансировщиком нагрузки TCP/HTTP. Он распределяет рабочую нагрузку по серверам для обеспечения максимальной производительности и оптимизации использования ресурсов. HAProxy поддерживает гибко настраиваемые методы проверки доступности, обработки отказов и восстановления после них.

Приложение, использующее базу данных, может легко перенасытить ее слишком большим количеством одновременно работающих соединений. HAProxy обеспечивает организацию очереди и регулирование соединений с одним или несколькими серверами MySQL и предотвращает перегрузку одного сервера слишком большим количеством запросов. Все клиенты подключаются к экземпляру HAProxy, а обратный прокси-сервер пересылает подключение к одному из доступных серверов MySQL на основе используемого алгоритма распределения нагрузки.

В одном из вариантов настройки HAProxy устанавливается на каждом сервере приложений, выполняющем запросы к базе данных. Такой вариант отлично подходит при наличии нескольких серверов, что позволяет контролировать нагрузку и скрыть организацию кластера СУБД. Приложение подключается к локальному HAProxy (например, устанавив mysql-соединение на 127.0.0.1:3306) и может получить доступ ко всем серверам базы данных. Веб-сервер и HAProxy вместе образуют рабочий блок, поэтому веб-сервер не будет работать, если HAProxy недоступен.

Использование HAProxy для балансировки нагрузки дает следующие преимущества:

• Все приложения получают доступ к кластеру через указанные IP-адреса. Внутренняя топология кластера базы данных скрывается за HAProxy.
• Соединения MySQL распределены между доступными узлами БД.
• Можно добавлять или удалять узлы базы данных без необходимости внесения каких-либо изменений в приложения.
• Как только достигается максимальное количество соединений с базой данных, новые соединения ставятся в очередь. Это удобный способ ограничения количества запросов на соединение с базой данных, обеспечивающий защиту от перегрузки.

ClusterControl поддерживает развертывание HAProxy из пользовательского интерфейса, поддерживая стандартные алгоритмы балансировки, предоставляемые HAProxy:

• Round Robin. Каждый сервер получает запросы пропорционально своему весу, при этом веса серверов могут меняться на лету.
• Least Connection. Выбирается сервер с наименьшим количеством активных соединений.
• Source Hash Scheduling. Сервер для соединения назначается на основе хэша IP-адреса отправителя запроса и весов серверов.

InnoDB Row Ops

Здесь вы увидите вывод операций строки InnoDB, операции строки misc, семафоров и таблиц массива ожидания по умолчанию.

________________ Операции со строками InnoDB _________________ Ins Upd Прочитано Del Ins / Sec Upd / Sec Read / Sec Del / Sec 0 0 0 0 0.00 0.00 0.00 0.00 ________________________ Операция строки Разное _________________________ Запросы в очереди Запросы в просмотрах Rd Состояние основного потока 0 0 1 ожидает активности сервера _____________________________ Семафоры InnoDB _____________________________ Ожидание Спинов Раунды RW Ожидания RW Спины Sh Ожидания Sh Спины Сигналы ResCnt 2 0 41 1 1 2 4 5 5 ____________________________ Массив ожидания InnoDB _____________________________ Время потока Файл Тип читателей Читатели Lck Var Ожидания Ожидание? Окончание?

Статистика веб-сервера

Для веб-серверов можно воспользоваться служебной страницей просмотра статуса. Она может показать статистику запросов к веб-серверу.

Apache

Для Apache необходим модуль mod_status, который идет в комплекте с данным веб-сервером. Проверить подключение модуля можно в конфигурационном файле httpd.conf (в разных Linux системах может находится в различных каталогах).

По умолчанию, server-status не активен. Создаем конфигурационный файл.

Для CentOS / Red Hat:

vi /etc/httpd/conf.d/server-status.conf

Для Ubuntu / Debian:

vi /etc/apache2/sites-enabled/server-status.conf

* где 2 — используемая версия apache.

В открытый конфигурационный файл добавим:

ExtendedStatus on
<VirtualHost *:80>
  servername 111.111.111.111
  <Location /server-status>
    Sethandler server-status
  </Location>
</VirtualHost>
<Location /server-status>
    SetHandler server-status
</Location>

* где 111.111.111.111 — IP-адрес нашего веб-сервера; 80 — порт, на котором слушает apache.* в данном примере мы прописали два варианта просмотра статистики: первый — обращение в браузере к серверу по IP-адресу + /server-status; второй — обращение к любому сайту + /server-status. Разные способы оправданы для разных настроек самих сайтов и используемых CMS.

Проверим корректность внесенных данных и перезапустим веб-сервер apache:

apachectl configtest

systemctl restart httpd || systemctl restart apache2

Теперь открываем браузер и вводим название сайта + /server-status, например, http://www.dmosk.ru/server-status. Или обращаемся к серверу по IP-адресу, например, http://111.111.111.111/server-status.

NGINX + PHP-FPM

Открываем конфигурационный файл nginx:

vi /etc/nginx/nginx.conf

В секцию http добавляем:

…
    server {
        listen 80;
        server_name 111.111.111.111;
        location /server-status {
              stub_status on;
        }
    }
…

* где 111.111.111.111 — IP-адрес нашего веб-сервера.

Проверяем корректность настройки и перезапускаем nginx:

nginx -t

systemctl restart nginx

Открываем браузер и заходим на страницу 111.111.111.111/server-status. Мы должны увидеть статистику использования сервера:

Теперь настроим статистику для php-fpm. В конфигурационном файле nginx в нашу директиву server добавим:

vi /etc/nginx/nginx.conf

…
    server {
        listen 80;
        server_name 78.110.63.31;
        location /server-status {
            stub_status on;
        }
        location /status {
            access_log off;
            include fastcgi_params;
            #fastcgi_pass unix:/var/run/php-fpm/php5-fpm.sock;
            fastcgi_pass 127.0.0.1:9000;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        }
    }
…

* обратите внимание на закомментированную строку и строку под ней. В зависимости от того, как настроен php-fpm (слушает на порту или через сокетный файл) необходимо настроить nginx

В данном примере подразумевается, что php-fpm слушает на 9000 порту.

Открываем конфигурационный файл php-fpm:

vi /etc/php-fpm.d/www.conf

Снимаем комментарий со следующей строки:

pm.status_path = /status

Проверяем настройку nginx, перезапускаем его и php-fpm:

nginx -t

systemctl restart nginx

systemctl restart php-fpm

Открываем браузер и заходим на страницу 111.111.111.111/server-status. Мы должны увидеть статистику использования сервера:

show full processlist

Показать, какие потоки работают, как показано ниже:

Наиболее важной частью этой команды является столбец состояния. Состояния, перечисленные mysql, в основном следующие:

• Проверка таблицы — проверка таблицы данных (это автоматически).
• Закрытие таблиц обновляет данные, измененные в таблице, на диск и закрывает использованные таблицы. Это быстрая операция, в противном случае вы должны проверить, заполнено ли дисковое пространство или диск находится под большой нагрузкой.
• Подключить Out подчиненный сервер репликации подключается к главному серверу.
• Копирование в таблицу tmp на диске Поскольку временный набор результатов больше, чем tmp_table_size, временная таблица преобразуется из памяти в дисковое хранилище для экономии памяти.
• Создание таблицы tmp создает временную таблицу для хранения результатов некоторых запросов.
• удаление из основной таблицы Сервер выполняет первую часть удаления нескольких таблиц, просто удалил первую таблицу.
• удаление из справочных таблиц Сервер выполняет вторую часть удаления нескольких таблиц, удаляя записи из других таблиц.
• Очистка таблиц выполняет FLUSH TABLES, ожидая, пока другие потоки закроют таблицу данных.
• Killed отправил запрос на уничтожение потоку, затем этот поток проверит флаг уничтожения и откажется от следующего запроса на уничтожение. MySQL будет проверять флаг уничтожения в каждом основном цикле, но в некоторых случаях поток может умереть через короткий промежуток времени. Если поток заблокирован другим потоком, запрос на уничтожение вступит в силу немедленно после снятия блокировки.
• Заблокировано было заблокировано другим запросом.
• Отправка данных обрабатывает записи запроса SELECT и отправляет результаты клиенту.
• Сортировка по группе — это сортировка по GROUP BY.
• Сортировка для заказа это сортировка для ORDER BY.
• Открытие столов Этот процесс должен быть быстрым, если на него не влияют другие факторы. Например, перед выполнением инструкции ALTER TABLE или LOCK TABLE таблица данных не может быть открыта другими потоками. Пытаюсь открыть стол.
• Удаление дубликатов — это выполнение запроса SELECT DISTINCT, но MySQL не может оптимизировать эти дубликаты на предыдущем этапе. Поэтому MySQL необходимо снова удалить дубликаты записей и затем отправить результаты клиенту.
• При повторном открытии таблицы была получена блокировка таблицы, но блокировка должна быть получена после изменения структуры таблицы. Блокировка снята, таблица данных закрыта, и была предпринята попытка открыть таблицу данных заново.
• Восстановление путем сортировки Инструкции по ремонту сортируются для создания индекса.
• Восстановление с помощью keycache Инструкция по восстановлению использует кэш индекса для создания новых индексов один за другим. Это будет медленнее, чем Ремонт путем сортировки.
• Поиск строк для обновления — это поиск подходящих записей для обновления. Это должно быть завершено до того, как UPDATE захочет изменить соответствующие записи.
• Спящий ждет, пока клиент отправит новый запрос.
• Системная блокировка ожидает получения внешней системной блокировки. Если в настоящее время вы не используете несколько серверов mysqld, запрашивающих одну и ту же таблицу одновременно, вы можете запретить блокировки внешней системы, добавив параметр —skip-external-lock.
• Обновление блокировки INSERT DELAYED пытается получить таблицу блокировки для вставки новой записи.
• Обновление — поиск подходящих записей и их изменение.
• Блокировка пользователя ожидает GET_LOCK ().
• Ожидание таблиц Поток уведомляется о том, что структура таблицы данных была изменена, и таблицу данных необходимо открыть заново, чтобы получить новую структуру. Затем, чтобы снова открыть таблицу данных, вы должны подождать, пока все остальные потоки не закроют таблицу. Это уведомление генерируется в следующих ситуациях: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE или OPTIMIZE TABLE.
• ожидание вставки обработчика. INSERT DELAYED завершил обработку всех ожидающих операций вставки и ожидает нового запроса.

Большинство состояний соответствуют быстрым операциям. Пока один поток остается в одном и том же состоянии в течение нескольких секунд, может возникнуть проблема, которую необходимо проверить.

Спасибо

Большое спасибо отOracle Дэйв Стоукс из VividCortex и Юэн Форчун из VividCortex, которые предоставили много ценных отзывов до публикации этой статьи.

Эта статьяOneAPMИнженер составляет и организует.OneAPM Cloud Insight Интегрируя мониторинг, управление, вычисления, сотрудничество и визуализацию, он помогает всем ИТ-компаниям сократить трудозатраты и временные затраты на мониторинг системы и сделать эксплуатацию и обслуживание более эффективными и простыми. Чтобы прочитать больше технических статей, посетитеОфициальный технический блог OneAPM。

Эта статья воспроизводится сОфициальный блог OneAPM

Исходный адрес:https://www.datadoghq.com/blog/monitoring-mysql-performance-metrics/

3，rpmРазвертываниеagentклиент

rpmСпособ установить:

Адрес источника блога:http://blog.csdn.net/mchdba/article/details/51288767Отказался от перепечатки

Запустите агент:

INNOTOP

Innotop can be really handy when you need a quick and easy tool that can provide a lot of details on what MySQL is doing – without using difficult queries to get those details manually. For our Percona Support customers, using Innotop will often be much easier then running complex SHOW statements and filtering their output in order to get some simple facts like slave replication lag, number of busy threads or InnoDB history list length. Also for us, Support engineers, it’s sometimes quicker to get a fast system overview with Innotop before we do deeper investigation using SHOW statements, Information_schema or more complex ways.

Настройка сервера Zabbix

На сервере Zabbix мы делаем два действия:

• Создаем шаблон для мониторинга репликации.
• Привязываем к хосту шаблон.

Создание шаблона

Скачиваем шаблон mysql_repl_template.xml. Заходим на веб-портал управления сервером мониторинга с правами администратора. Переходим в раздел Настройка — Шаблоны:

Сверху справа кликаем по Импорт:

В открывшемся окне выбираем скачанный шаблон и нажимаем по Импорт:

В шаблонах должен появиться новый с названием «Template MySQL Replication Slave».

Настройка хоста

Переходим в раздел Настройка — Узлы сети:

Находим среди узлов наш сервер MySQL, который будем мониторить и переходим в его настройки. 

На вкладке Шаблоны выбираем наш шаблон, который мы загрузили, добавляем его и обновляем настройки хоста:

Готово. При возникновении проблем репликации мы увидим предупреждение «MySQL: Replication Error».

show status

Просмотр информации о состоянии сервера MySQL.

Непосредственное использование этой команды приведет к вводу сотен строк данных, что очень уродливо.

Ниже мы рассмотрим несколько часто используемых команд с параметрами

Запрос текущего времени работы MySQL после этого запуска

show status like 'uptime';

Посмотрите количество операторов выбора, выполненных после этого запуска MySQL

show status like 'com_select';

Посмотрите, сколько раз выполнить инструкцию вставки после этого запуска MySQL

show  status like 'com_insert';

Посмотрите, сколько раз инструкция обновления выполняется после запуска MySQL

show  status like 'com_update';

Посмотрите, сколько раз оператор удаления выполнялся после этого запуска MySQL

show  status like 'com_delete';

Просмотр информации о потоке сервера MySQL

show status like 'Thread_%';

Просмотр количества соединений, пытающихся соединиться с MySQL (независимо от того, успешно ли соединение)

show status like 'connections';

Просмотр количества потоков в потоке кеша

show status like 'threads_cached';

Просмотр количества блокировок таблицы, которые были получены немедленно

show status like 'table_locks_immediate';

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

show status like 'table_locks_waited';

Просмотр количества запросов, время запроса которых превышает long_query_time секунд

show status like 'slow_queries';

Долгие запросы

С помощью длительных запросов к веб-серверу или СУБД можно сделать выводы о том, что является узким местом в работе сервиса.

MySQL / MariDB

Для начала, воспользуемся инструкцией, чтобы настроить ведение лога медленных запросов (для MySQL или MariaDB).

После, воспользовавшись статистикой, находим неоптимальные запросы. В одних случаях необходимо будет переписать сам запрос, в других — создать индексы базы данных.

PHP-FPM

Открываем конфигурационный файл:

vi /etc/php-fpm.d/www.conf

Редактируем следующие параметры:

request_slowlog_timeout = 10s
slowlog = /var/log/php-fpm/www-slow.log

* request_slowlog_timeout определяет время, в течение которого должен выполняться запрос, чтобы он считался медленным; slowlog — путь до лога, куда будет сохранена информация о медленных запросах.

Перезапускаем сервис:

systemctl restart php-fpm

Непрерывный просмотр лога можно запустить командой:

tail -f /var/log/php-fpm/www-slow.log

Mtop — мониторинг нагрузки на MySQL-сервер

При работе с MySQL довольно полезно следить за нагрузкой, которая ложится на MySQL-сервер. В портах есть полезная утилита — mtop. Она в режиме реального времени отслеживает и показывает текущие запросы к безе данных, время их выполнения, статус MySQL-сервера в удобной форме.

Итак, выполним установку утилиты из системы портов:

По завершению установки создадим MySQL-пользователя, которому дадим права на просмотр процессов MySQL:

На этом все «приготовления» можно считать законченными. Запустим mtop и посмотрим на результат. Параметри запуска следующие:

Если не указывать ключ -dbu, то mtop будет пытаться подключаться от имени пользователя mysqltop.

Теперь можно наблюдать за динамикой SQL-запросов. При этом быстрые запросы отображаются белым цветом и потом, по мере роста длительности выполнения цвет начинает меняться, если запрос выполняется более 10 секунд он окрашивается фиолетовым, более 20 секунд — желтым, более 40 секунд — красным. Это значения по умолчанию, но их можно переназначить.

Утилита очень хорошо визуально демонстрирует проблемные места в работе MySQL. Так же очень хорошо видны такие показатели как число процессов (threads) и сколько из них активны (running), сколько процессов закешированны(cached), сколько запросов поступило и сколько из них медленных(Queries/slow) и какова эффективность кеша запросов (Cache Hit).

Для того, чтобы узнать все возможности и ключи запуска утилиты — настоятельно рекомендую ознакомиться со страницей руководства.

А чтобы ознакомиться с доступными ключами во время работы утилиты, необходимо нажать «?». В результате получаем такую подсказку:

q - quit
? - help; show this text
f - flush status
F - fold/unfold column names in select statement display
k - kill processes; send a kill to a list of ids
s - change the number of seconds to delay between updates
m - toggle manual refresh mode on/off
d - filter display with regular expression (user/host/db/command/state/info)
h - display process for only one host
u - display process for only one user
i - toggle all/non-Sleeping process display
o - reverse the sort order
e - explain a process; show query optimizer info
t - show mysqld stats (show status/mysqladmin ext)
T - show mysqld important stats
v - show mysqld variables (show variables/mysqladmin vars)
z - zoom in on a process, show sql statement detail
r - show replication status for master/slaves

Думаю, что переводить нету смысла, и так все понятно. Ну и напоследок — скриншоты вывода утилиты mtop:

Заключение

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

Автоматическую оптимизацию с помощью скрипта следует обязательно дополнять настройкой производительности в ручном режиме с помощью регулировки основных параметров СУБД. Для повышения эффективности MySQL не менее важна оптимизация работы с выборкой из нескольких объединенных таблиц.

Оцените материал: