Мониторинг PostgreSQL. Новые возможности анализа производительности 1С и других систем. Часть 1: счётчики

В данной статье хочу поднять тему, которая представляет собой одну большую боль для администраторов, разработчиков и тестировщиков высоконагруженных (и не очень) систем под управлением PostgreSQL. Даже не «боль», а «БОЛЬ»!

Удивительно, что за почти 30 лет существования PostgreSQL не появилось нормальных инструментов для получения вменяемых счетчиков и трассировок. Все, кто работают с MS SQL Server используют профайлер. Это обязательный и привычный инструмент, который позволяет вылавливать запросы, интересные нам в рамках исследования. Вылавливать как все запросы без разбора, так и какие-то единичные запросы, которые удовлетворяют правилам отбора. Кроме того, можно настроить не одну трассу, а столько сколько нужно, с разными фильтрами. Эти трассы содержат очень богатый набор измерений для анализа: – Reads физические и логические; Writes; SPID, Процессорное время; план запроса (хэш плана), количество строк и т.д.

Я уже писал ранее, что сейчас идет тренд перехода с MSSQL Server на PostgreSQL. И если пару лет назад это было всё больше на бумаге, то сейчас мы чаще и чаще сталкиваемся с реальными продуктивными и даже высоконагруженными системами на PostgreSQL.

Многие компании стали всерьез рассматривать СУБД PostgreSQL как замену MSSQL и сталкиваются с тем, что возможностей для ее мониторинга просто нет – она как черный ящик, в котором наощупь вылавливаешь какую-ту информацию и пытаешься систематизировать ее хоть как-то. Есть некие универсальные рекомендации по настройкам PostgreSQL, кочующие из форума в форум, и которые якобы должны подойти всем системам, отвечающим определенным признакам, например, «всем системам на 1С». Есть представление pg_stat_statements. Найдется, наверное, еще пара-тройка источников для получения полезных данных, типа лога PostgreSQL с очень ограниченным функционалом. И на этом всё. Совсем всё! Ничего даже рядом стоящего с SQL Profiler не существует. В PostgreSQL как таковое понятие «трасс» просто отсутствует.

И как жить? Zabbix плюс pgAdmin плюс консоль 1С. Вот и весь набор администратора. Утрирую, конечно, но всё равно печально.

Что не так с существующими инструментами типа pg_stat_statements

pg_stat_statements – это агрегированная статистика по запросам. Статистика не по каждому запросу, а по группе одинаковых с точки зрения PG запросов. Только одним этим уже многое сказано.

Активировав модуль в конфиг-файле, PostgreSQL сервер ведет накопительную статистику выполнения запросов и предоставляет уже посчитанные данные по запросам с момента запуска сервера или с момента последнего сброса через pg_stat_statements_reset. При проведении нагрузочных испытаний на тестовой машине эта статистика действительно может помочь определить узкие места и посмотреть на долю нагрузки, например, на память (по чтению блоков с диска, из кэша, из временных таблиц – что дополнительно усложняет интерпретацию данных) в разрезе разных групп запросов. Их там просто мало и разработчики знают что искать. В то же время, она очень ограниченна: не считается вклад группы в процессорную нагрузку; так как это агрегированные данные, то невозможно отследить каждый отдельный запрос и время его начала/окончания, конкретный вклад в нагрузку в зависимости от параметров. В рабочей системе эта статистика почти никак не помогает в расследовании инцидентов, для реальных рабочих нагрузок оно помогает лишь оценить на определенной дистанции вклад запросов в разрезе групп.

На реальной нагруженной БД pg_stat_statements быстро «забивается». Его нужно тюнить, регулярно сбрасывать, но даже это не всегда помогает.

Что еще хочется отметить. В pg_stat_statements довольно низкая вариативность запросов. Есть параметр pg_stat_statements.max, который по умолчанию равен 5000. Если говорить про системы 1С, то вариативность запросов (видов запросов) там измеряется сотнями тысяч с учетом работы с временными таблицами. Соответственно настройку pg_stat_statements.max нужно увеличить на несколько порядков. Это приведет к значительному расходу ресурсов.

Второй инструмент, который мы можем использовать еще более неудобный и менее применимый в реальных кейсах – механизм логирования запросов сервером PostgreSQL. Логи PostgreSQL – служебная функция сервиса, позволяет не только протоколировать важные вехи в работе сервера, ошибки, но и в рамках того же лога записывать запросы и планы выполнения, прошедшие условия отбора, заложенные в конфиге сервера. Типичная настройка для сбора тяжелых запросов с фильтрами по длительности более 5 секунд будет выглядеть примерно следующим образом:

log_autovacuum_min_duration=-1

log_checkpoints=off

log_connections=off

log_destination="stderr"

log_directory=pg_log

log_disconnections=off

log_duration=off

log_error_verbosity=default

log_executor_stats=off

log_file_mode=0600

log_filename="postgresql-%Y-%m-%d_%H%M%S.log"

logging_collector=on

log_hostname=on

log_line_prefix="< %m;%a;%u;%d;%p;%i;%e;%c;%l;%s;%v;%x >"

log_lock_waits=off

log_min_duration_statement=1000

log_min_error_statement=info

log_min_messages=info

log_parser_stats=off

log_planner_stats=off

log_replication_commands=off

log_rotation_age=3

log_rotation_size=10240

log_statement=none

log_statement_stats=off

log_temp_files=0

log_truncate_on_rotation=on

Полученный результат будет выглядеть примерно так:

Как видим, мы получили время начала выполнения запроса, окончания запроса и сам текст запроса, полный текст запроса, можем при желании узнать даже значения параметров вызова. Но что-то более существенное для аналитика узнать не получится.

PostgreSQL предоставляет в разы меньше значимых метрик в отличие от той же хранимой процедуры pg_stat_statements – характеристики запроса мы не узнаем. Поэтому область применения очень ограничена.

Кроме того, работа с логами несет в себе большие неудобства. Например, перенастройка конфигурации логирования, нет возможности задать гибкие условия отбора запросов, запросы логируются в текстовом виде, они неудобны и медлительны для парсинга, данные по собираемым запросам смешиваются со служебной информацией. От версии к версии PostgreSQL формат лога может меняться, соответственно парсинг нужно постоянно поддерживать. Но основной минус лога – в нём нет никаких характеристик запроса кроме текста и длительности.

Расширение SP_TRACE как выход из тупика

Мы (Softpoint) занимаемся производительностью высоконагруженных систем очень давно, а нашему флагманскому продукту «Мониторинг Perfexpert» почти 20 лет. Свой мониторинг мы дорабатываем по принципу «если каких-то данных, необходимых для анализа, ОС/СУБД/Приложение не предоставляет, значит мы их будем добывать сами». После первого же аудита производительности PG-системы на платформе 1С (в 2022 году) стало понятно, что для проведения серьезных исследований стандартных инструментов, предоставляемых PostgreSQL, недостаточно. Сторонние решения, как opensource, shareware, так и коммерческие, также не предлагают ничего радикально нового.

С учетом нашего опыта работы с MS SQL очень важное значение для средств мониторинга имеют трассировка запросов SQL для выявления тяжелых и длительных запросов, а также разнообразные счетчики, показывающие эффективность использования кэша, процессора, диска и многое другое.

Один из главных плюсов PostgreSQL – полная открытость исходных кодов, что позволяет расширять его возможности практически безгранично. Поэтому наши специалисты создали расширение SP_TRACE, который внедряется в службу СУБД и через API предоставляет нам множество сведений (расширенные счётчики и трассы) и позволяет проводить анализ уже совсем на другом уровне. Расширение может работать с любыми версиями PostgreSQL – платными и бесплатными.

SP_TRACE. Счетчики

Мы создали несколько новых счетчиков, каждый из которых собирает данные не только в разрезе сервера СУБД, но и в разрезе БД или приложений. Подобная детализация дает существенные преимущества эксперту даже по сравнению с возможностями анализа MS SQL. Это очень удобно при исследовании причин нагрузки, если на сервере работает нескольких нагруженных баз данных как на платформе 1С, так и на любой другой. Естественно, мониторинг Perfexpert включает в себя данный модуль по умолчанию.

Описание счетчиков представлено в следующей таблице:

№	Счетчик	Описание
1	Total queries	Общее количество запросов на сервере СУБД
2	Queries per second	Количество запросов в сек
3	Average query duration (ms)	Средняя длительность запроса SQL
4	Disk read speed (MB/s)	Скорость чтения Мб/c с диска
5	Disk write speed (MB/s)	Скорость записи Мб/c на диск
6	Cache read speed (MB/s)	Скорость чтения в Мб/c из памяти (кэша)
7	Cache write speed (MB/s)	Скорость записи в Мб/c в память (кеш)
8	Temp read speed (MB/s)	Скорость чтения в Мб/c данных из временных файлов
9	Temp write speed (MB/s)	Скорость записи в Мб/c данных во временные файлы
10	CPU load (ms/s)	Процессорная нагрузка
11	Cache hit ratio	Cache Hit Ratio – вероятность попадания в кеш
12	Memory/Temp ratio	Соотношение записи в память и временные файлы
13	CPU heavy queries (s)	Потреблённые ресурсы CPU (в сек) со стороны тяжелого запроса в момент его окончания, если его длительность больше чем 10 сек.

Как видно, можно узнать точное количество запросов в секунду, показатели чтения/записи на диск и кэш данных, долю нагрузки в разрезе баз данных и т.п.

Приведу несколько примеров, которые продемонстрируют как можно использовать тот или иной счетчик в анализе ситуаций падения производительности ИТ-систем. Это ни в коем случае не алгоритм действий при расследовании. Это примеры того, как при жалобах пользователей можно использовать дополнительную информацию от СУБД, сделать анализ более системным и не становится заложником ситуации отсутствия данных или неверных предположений.

1. CPU Load

С помощью мониторинга Perfexpert стало гораздо понятнее и очевиднее искать первопричины процессорной нагрузки. Достаточно спозиционировать вертикальную линейку на пике графика CPU Load и в окне справа отображаются пользовательские SQL-сессии. На рисунке ниже приведен SPID пользователя, загрузившего на 100% процессорное ядро (в табличке справа на розовом фоне первая строчка со SPID 3845375). Теперь, если построить трассу Reads, то в ней можно найти по этому SPID интересующий запрос и дальше уже понять причину подобного профиля нагрузки.

2. Queries per second

Рост нагрузки на CPU очень часто бывает вызван элементарным увеличением количества запросов в секунду. Причем без такого, казалось бы, очевидного счётчика увидеть это весьма не просто.

То есть, если возрастание нагрузки на CPU сопровождается значительным увеличением количества запросов в секунду, то, скорее всего, это увеличение запросов и явилось первопричиной. Надо искать причину в коде, каких-то фоновых заданиях, новых пользователях, активностях и прочее.

С другой стороны, если при увеличении нагрузки на CPU счетчик количества запросов в секунду уменьшился, то, вероятнее всего, снижение количества запросов явилось следствием нехватки ресурсов CPU на обработку и необходимо искать причину этой нагрузки, которая может быть даже не связана с запросами от ИТ-системы.

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

3. Disk read speed

Увеличение мгновенной скорости чтения к диску говорит о том, что дисковая подсистема испытывает в эти моменты большие нагрузки. Кто-то производит с диска более интенсивное чтение.

А далее нужно оценить эти всплески (пики). Если всплеск держится долго время и приближается к аппаратному максимуму, выше которого диск не способен работать, то имеет смысл задуматься о следующих вещах:

Увеличить оперативную память. Так данные будут считываться большей частью из кэша, а не с более медленной дисковой подсистемы.
Улучшить параметры диска, возможно он имеет уже устаревшие характеристики.
Найти причину(ы) этой нагрузки. Возможно всё дело в неоптимальном запросе, который выгрызает всю память и обращается к дисковой системе, считывая данные уже с нее. На скриншоте ниже приведена как раз такая ситуация, когда в окне мониторинга Perfexpert представлен график Disk read speed (красного цвета) с несколькими пиками и открыта трасса Reads, в которой представлены запросы, отсортированные по параметру «Количество физических чтений». Каждый из первых трех запросов как раз соответствует одному из пиков на графике. Эти запросы – первые кандидаты на оптимизацию. Часто достаточно будет проанализировать запрос на недостающие индексы и после индексного тюнинга он будет «летать».

4. Temp read speed

По аналогии с предыдущим счетчиком счетчик Temp read speed позволяет наблюдать на повышенной нагрузкой к временным файлам. Т.е. системе не хватает оперативной памяти, она сбрасывает данные во временные файлы и опять будет наблюдаться замедление, т.к. чтение файлов – это заведомо более медленная операция, чем чтение из оперативной памяти. Тут следует обратить внимание на настройку work_mem, используемую для сортировки и группировки данных в запросах. Неверная настройка может приводить к нехватки оперативной памяти.

5. Cache read speed

Еще один счётчик контроля за скоростью чтения данных, но теперь уже из памяти, буферного кэша.

Как только счетчик Cache read speed растет, значит происходит какое-то массовое чтение данных из буферного кэша. И если значение счетчика достигает больших значений (десятки гигабайт в секунду), держится на постоянном уровне значительный промежуток времени, то вполне возможно вы уперлись в производительность шины взаимодействия памяти и процессора и необходимо либо улучшать аппаратные ресурсы, либо искать причину такого интенсивного чтения из памяти.

6. Cache hit ratio

Счётчик, показывающий вероятностное попадание данных в кэш. Это фактически соотношение между количеством данных, считанных из памяти с количеством данных, считанных с физического носителя. В идеале график этого счетчика должен представлять условно горизонтальную линию со значением близким к 100% в течение рабочего дня. Это значит, что все данные в кэше и система почти не обращается к диску. Если же есть провалы, это значит, что кэша (памяти) не хватает, происходит выдавливание из него данных, что, в свою очередь, приводит к чтению данных с диска. Например, снижение показателя Cache hit ratio до 80% говорит о том, что 20% данных в запросах считывались с жесткого диска. И это не есть хорошо. Особенно, если подобная ситуация на протяжении дня повторяется неоднократно или вообще является трендом. Решение проблемы либо аппаратное (увеличение оперативной памяти), либо программное (оптимизация тяжелых запросов, совершающих большое количество логических чтений).

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

Практическое использование данных счетчиков позволит администратору системно подходить к анализу производительности и сэкономить время на поиск и устранение возникающих проблем.

Заключение

Как вы поняли, расширение SP_Trace – это не только счетчики, но и трассировка. То, чего так сильно не хватает в PostgreSQL после перехода с MS SQL.

В одной из следующих статей мы рассмотрим применение расширения SP_Trace уже с позиции профайлера (трассировщика). Покажем как мониторинг Perfexpert использует полученные трассы, приведем несколько практических кейсов.

Целью этой статьи была демонстрация новой возможности и нового инструментария, который теперь есть на российском рынке мониторинга производительности PostgreSQL-систем как на платформе 1С:Предприятие, так и на любой другой платформе.

Называется он SP_Trace и входит в состав известного и хорошо зарекомендовавшего себя мониторинга Perfexpert. Теперь анализ проблем производительности в системах на PostgreSQL проходит на порядки легче и быстрее, т.к. состав и полнота предоставляемых данных не хуже, а местами и лучше, чем в версии для привычного MS SQL Server.

Если интересно посмотреть возможности PerfExpert в вопросах поиска проблем производительности, то можно посмотреть информацию на нашем сайте, а также посмотреть несколько обзорных коротких видеороликов:

Вторая часть обзора расширения SP_TRACE: Мониторинг PostgreSQL. Новые возможности анализа производительности 1С и других систем. Часть 2: Трассировка