Эти материалы являются объектом авторского права и защищены законами РФ и международными соглашениями о защите авторских прав. Перед использованием материалов вы обязаны принять условия лицензионного договора на использование этих материалов, или же вы не имеете права использовать настоящие материалы

Авторская площадка "Наши орбиты" состоит из ряда тематических подразделов, являющихся моими лабораторными дневниками, содержащими записи за разное, иногда продолжительно отличающееся, время. Эти материалы призваны рассказать о прошедшем опыте, они никого ни к чему не призывают и совершенно не обязательно могут быть применимы кем-то ещё. Это только лишь истории о прошлом

Возможности СУБД Oracle, с чем сравниваем

Продолжаем отстраивать понятийную навигацию о возможностях ванильной, т.е. общедоступной безоплатной СУБД Postgree SQL в сравнении с возможностями СУБД Oracle c горизонта инженера по эксплуатации, т.е администратора баз данных. Этот раздел получится совмещённый по Ораклу и POstgreeSQL. Статья зафиксировать мысли задумывалась давно, много лет назад, но руки похоже не дошли. А сейчас, в контексте изучения применимости PgSQL, полезно будет зафиксировать мысли и подходы. Только писаться она будет долго, ибо по совсем остаточному принципую Основные мотиваторы сбора и анализа статистики СУБД и БД - поиск узких мест и возможностей их устранения. У Оракла с этим всё неплохо, ибо каждый чих считается кумулятивными счётчиками по сотням статистик и разделённых на 12 классов событий ожидания, на основе которых можно проанализировать многое хоть в моменте, хоть в ретроспективе. Это касается работы СУБД в целом, и отдельных сессий, и при необходимости - отдельных запросов. Инструментов сбора и анализа статистик много - это и упомянутые кумулятивные счётчики, в т.ч. библиотечного кэша, и коллекторы в составе AWR - снапшоты, ASH, SQL monitoring, и средства их аналитики. И где то там старенький статспак, и ADDM и т.д. Однако всё же охват не такой уж и глобальный, о чём ниже поговорим, и посмотрим, а что из похожего и в каком объёме есть в PostgreSQL

Если мы не можем контролировать активности СУБД, мы не можем выявить узкие места и запланировать мероприятия по их устранению. С горизонта СУБД существует производительная [например - физический ввод вывод] и непроизводительная [например - очереди с блокировками] трата различного вида ресурсов. Именно распределение активности СУБД по классам событий ожидания, причём в динамике за разные периоды и с возможностью провалиться в статистику уже конкретных ожиданий и статистических счётчиков, является основой для ответа на вопрос утилизации ресурсов. С горизонта же именно базы данных БД важно выявнение требущющих оптимизации относительно редких долгоиграющих запросов и очень частых коротких, а также выявление и анализ активности отдельных сессий со всей совокупностью отрабатываемых такой сессией запросов. Оптимизация ради оптимизации - удел ИТ фанатиков и неадекватов по нашей шкале ценностей, избави боги от них, в т.ч. и среди читателей этой статьи, которая, как и всё, пишется только для своих, молящихся тем же "условным богам", скорее инженерным и демиургическим, что и автор. О вреде оптимизации ради оптимизации пишут и гуру, но имеющие обоснование потребности в оптимизации - тоже нередкая часть цикла жизни программ ... поэтому статистика важна, и отлаженные методы ее использования важны. Поэтому мы пойдём как раз по этим типовым классам задач, чтобы понять возможности PgSQL, а заодно зафиксировать и возможности СУБД от сбежавшего из страны "Оракела". Возможности у Pg не такие глобальные, как у Оракла, но всё же они впечатляют. И расти куда точно есть, только нужно понять - а не хватит ли существующих возможностей для большинства реальных, а не академических, задач

Основными первоисточниками статистической информации в Oracle являются (1) кумулятивные счётчики статистик и событий ожидания, (2) статистики библиотечного кеша, (3) статистики ASH, (4) коллектор статистик SQL Monitoring. Надстройками являются Statspack, AWR, метрики БД. Можно периодически сохранять значения кумулятивных счётчиков и статистики запросов библиотечного кэша, и на основе расчёта приращений этих срезов получать развёрнутую аналитику СУБД в целом, сессий и запросов. Сейчас, с применением оконных функций для расчёта дельт срезов, это не очень сложно. Если знаешь архитектуру системы и представляешь взаимодействие показателей. Однако данные из библиотечного кэша вымываются при большом количестве запросов, что ограничивает полноту охвата. Вымываются самые редкие запросы, так что полнота охвата всё же достаточно неплоха. Поэтому при расчёте отчёта AWR в выборку опять же не попадут малые и быстрые запросы. А ведь у AWR есть и друие функции, например на основании его данных можно закрепить старый план запроса через Oracle SPM. Если конечно план попал в AWR ...

Теперь далее, существует отдельный механизм ASH, собирающий ежесекундные срезы активных в БД сессий, вместе с событиями ожидания, запросами и фазой их выполнения, блокировками и блокируемыми объектами. Это довольно полная картина, но и она имеет ограничения, связанные с тем, что одна секунда это чудовищно большое время, ведь запросы в БД могут отрабатывать и за 1 миллисекунду, за которую отработают разные фазы этого запроса, от разбора и создания плана, до выборок, объединений данных и их сортировок, или за 10 миллисекунд, тогда их поместится в 1 секунду сто запросов. Или запросы отработают за 100 мсек, и все равно на момент среза за бортом останется 9/10 общесекундной активности. Тут нет никакой накопительной информации, за исключением длительности последней фазы активности, и за секунду между двумя срезами сессия может успеть отработать тысячу маленьких запросов. Глубина охвата и этого механизма ASH совершенно не полная. Но, если принять, что важно обращать внимание в первую очередь на относительно долие запросы, которые могут составить основную массу пожирателей ресурсов СУБД, то можно получить какую то протяжённую картину загрузки СУБД ... А что в этот механизм не попадёт огромное количество совсем уж мелких запросов - так на всё ресурсов не напасёшься, мелкие запросы и так отрабатываются, увидеть их можно попробовать в статистике библиотечного кэша. Если частые - не должны вымываться, покажутся. А СУБД должна ещё и работать, обслуживая запросы пользователей по возможности опративно. Далее, SQL Monitoring вообще собирает данные по запросам, работающим более 5 секунд, или параллельным

Фактически ни один механизм сбора статистики в Oracle не охватывает полную картину активности БД, хотя позволяет адекватно оценить различные значимые аспекты, а механизмы кумулятивных счётчиков, постоянно прирастающих с момента старта экземпляра в Оракле реализонаны уникально детальные. В дополнение каждую сессию, попавшую под подозрение, можно отправить на детальную трассировку, когда уже будет вычислена полная активность сессии до мельчайших запросов, и появится возможность полного анализа. На основании собираемых ASH данных реализован очень привычный администраторам БД механизм графиков TOP Activity, реализованный в Oracle Enterprise Manager (Oracle Cloud Control, OCC). Более 10 лет назад, анализируя запросы, отправляемые Oracle EM в базу и творчески их перосмысляя, мне удалось разработать свой вариант отображения TOP Activity, реализованное в моём продукте ОрСиМОН БеССТ. Так же, как и в OСС, он позволяет построить активность всей СУБД, а также показать ТОП сессий и запросов, и провалиться в них, чтобы посмотреть распределение по классам событий ожидания уже конкретную сессию или запрос. Этот продукт со мной с 2006 года, почти 20 лет, и он очень облегчает мне решение задачек по администрирования Оракла. Но помню, что лежащие в основе именно подсистемы TOP Activity данные табличек ASH - в БД и памяти - имеют разрядность всего 1 секунду, и потому пропускают многие мелкие запросы. Поэтому анализ, в зачисимости от задач, должен строиться не только на основе ASH, но и на основе аналитики дельт срезов кумулятивных счётчиков статистик и событий ожиданий в части запросов, сессий и работы архитектурных подсистем СУБД. Большая чась этого функционала удобно реализована в Оракле в виде AWR отчётов. Здесь ничего можно не выдумывать, пользуясь AWR. Есть только одна моральная и юридическая сложность - для использования AWR на продуктовых БД у вас должна быть куплена лицензия на DiagnosticPack. Или же вы можете использовать что то самописное, ведь данные именно к кумулятивным счётчикам не лицензируются, как не лицензируется и старенький stats pack

Ещё одной надстройкой над кумулятивными счётчиками является механизм метрик. Когда то в 2005 примерно году, когда никакого AWR не было у нас и впомине, мой ОрСиМОН вычислял ряд сигнальных показателей, таких, как коэффициенты попадания в различные буфера (hit ratio) на основании собираемых данных кумулятивных счётчиков. Сейчас этим заниматься не нужно, т.к. большинство значимых показателей базы можно получить в виде предвычисленных метрик, причём именно в разрезе динамики, т.е течении времени. Это позволяет строить профиль работы СУБД и оценивать эффективность работы её разных архитектурных компонентов. С этими вычислениями связана одна десятилетняя история, когда коллеги из отдела поддержки SAP в Росатоме пришли к нам с некими требованиями, но им было показано, что SAP считает отдельные оракловые показатели некорректно. Тогда ещё архитектор САПоводов Владимир Петриев ехидно спрашивал, не думаю ли я, что знаю архитектуру показателей лучвше, чем целая корпорация SAP, а я спокойно отвечал, что так и есть. Если не ошибаюсь, по его запросу и на основании моих комментариев САП тодга выпустил некий мелкий патчик ... Но это всё к чему - для работы ДБА полезно иметь целостную картину работы поддерживаемого продукта. Она всегда будет и у всех не полной, это психология, лишь бы была адекватной задачам

Таким образом администратор СУБД Oracle неплохо упакован различными инструментами сбора и анализа статистики "из коробки". Это ещё не упомянуты более высокоуровневые отчёты ADDM ... Вот теперь, на основе первой обзорной части, посмотрим по диагонали, а что же может предложить нам в части статистик и аналитики СУБД PostgreSQL. Важно понимать, что архитектура PostgreeSQL соответствует парадигме Unix way и принятым в мире Open Source. И часть функционала реализована т.н. расширениями, которые поставляются как сразу "из коробки", так и отдельными сторонними разработчиками. Это нормально, и если уж сообщество принимает сотни расширений и публикует их на официальном портале СУБД, стоит воспринимать их как часть СУБД. Спасибо, что механизм расширений реализован. Именно так строятся и крупные почтовые системы, собираясь из кубиков, и инфраструктурные решения, и привычные сервисы в UNIX. Не нравится - обратитесь к форточникам, продукты от Микрософт вам подойдут лучше. Мы же будем рассматривать СУБД PostgreSQL в широком смысле, т.е. вместе с типовыми расширениями, или доступными на портале postgresql.org, или поставляемые зарекомендовавшими себя вендорами, а именно компанией ПостгресПРО

Возможности СУБД PostgreSQL, что ищем

Статистика запросов Начнём с проведения аналогий по ключевым источникам статистик. В PgSQL реализована система статистики, существенно дополняемая расширениями, т.е. подключаемыми функциональными модулями. В PgSQL нет библиотечного кэша, соответственно и планы выполнения запросов не кэшируются, будучи разобранными при первом обращении, вытеснении и т.п. причинах. Нет и статистики библиотечного кэша. Однако доступно расширение pg_stat_statement (PSS), накапливающее статистику по отработанным запросам. Если организовать периодическое сохранение таких статистик, это позволяет анализировать в динамике как долгоиграющие, так и множественные короткие запросы. Также есть работающее с ним в паре расширение pg_store_plans (PSP), позволяющее сохранять планы выполнения запросов. Оно откровенно проще функционала Оракла, сохраняя план с модификаторанни и стоимостями в одном многострочном поле, что затрудняет работу с агрегацией планов. Здесь стоит отметить, что использование непараметризованных запросов в PostgreSQL может породить для одного запроса десятки, сотни и тысячи планов. Оно и понятно, если запрос в PgSQL принудительно нормализуется, но вот план для разных литеральных значений фильтров будет разный

Статистика активных сессий В PgSQL нет ASH, но есть механизм отображения текущих активных сессий pg_stats_activity (PSA), на основании которого можно огранизовать сбор данных, во многом аналогичных ASH, не только раз в каждую секунду, как это сделано в Oracle, но и, скажем, 10 раз в секунду. Как это реализовано в моём коллекторе данных stats activity, части продукта КрАгрАн - агрегатора и анализатора данных криптовалют, реализованного, как и коллекторы КоСиКУЛС четвертьвековой давности, на PostgreeSQL ... но в результате остоновился на ежесекундном срезе и не стал частить, хотя в 10-20 раз чаще точно ничего не мешает. Кроме статистики выполнения, показывающей, что количество запросов не сильно увеличивается при понижениии границы их длительности с 1 секунды до 100 миллисекунд, а вот при понижении до 10 миллисекунд охват становится явно больше, но и накладные рахходы на заполнение данных вырастают сильно, это ведь не отдельное легковесное расширение. При этом ASH Оракла живёт и вполне востребован и с периодом в одну секунду

Все методы сэмплирования, т.е. частичной выборки, упираются в предположение о справедливом распределении значений внутри частичной выборки. То есть вот в моменте они конечно случайны, но с течением времени всё больше отражают картину реального распределения. И поэтому считают, что взять некоторую часть выборки вполне допустимо, и при этом вполне дешевле по ресурсам. Однако т.к. у нас есть инструмент pg_state_statements, охватывающий все запросы, для этого у меня на БД лимиты счётчиков специально подняты, и мы можем проанализировать реальное состояние дел не в разрезе сессий, а в разрезе запросов. База проработала в этот раз не больше полутора суток, статистика есть, получается приведённое ниже распределение запросов по длительности запроса и количеству его вызовов в абсолютных цифрах и процентах от тотальных показателей. Здесь мной написан под задачку простенький запрос:

select round(sum(time_gt_10sec)::numeric/1000/3600,2) time_gt_10sec, round((sum(time_gt_10sec)*100/ds3.total_exec_time)::numeric,2) time_gt10s_prc,
       sum(calls_gt_10sec) calls_gt_10sec, round((sum(calls_gt_10sec)*100/ds3.calls)::numeric,2) callc_gt_10s_prc,
       round(sum(time_gt_1sec)::numeric/1000/3600,2) time_gt_1sec, round((sum(time_gt_1sec)*100/ds3.total_exec_time)::numeric,2) time_gt_1sec_prct,
       sum(calls_gt_1sec) calls_gt_1sec, round((sum(calls_gt_1sec)*100/ds3.calls)::numeric,2) calls_gt_1sec_prct,
       round(sum(time_gt_100msec)::numeric/1000/3600,2) time_gt_100msec, round((sum(time_gt_100msec)*100/ds3.total_exec_time)::numeric,2) time_gt_100msec_prc,
       sum(calls_gt_100msec) calls_gt_100msec, round((sum(calls_gt_100msec)*100/ds3.calls)::numeric,2)  calls_gt_100msec_prc,
       round(sum(time_gt_10msec)::numeric/1000/3600,2)  time_gt_10msec, round((sum(time_gt_10msec)*100/ds3.total_exec_time)::numeric,2) time_gt_10msec_prc,
       sum(calls_gt_10msec) calls_gt_10msec, round((sum(calls_gt_10msec)*100/ds3.calls)::numeric,2) calls_gt_10msec_prc,
       round(sum(time_gt_1msec)::numeric/1000/3600,2)  time_gt_1msec, round((sum(time_gt_1msec)*100/ds3.total_exec_time)::numeric,2) time_gt_1msec_prc,
       sum(calls_gt_1msec) calls_gt_1msec, round((sum(calls_gt_1msec)*100/ds3.calls)::numeric,2)  calls_gt_1msec_prc,
       round(sum(time_lt_1msec)::numeric/1000/3600,2)  time_lt_1msec, round((sum(time_lt_1msec)*100/ds3.total_exec_time)::numeric,2) time_lt_1msec_prc,
       sum(calls_lt_1msec) calls_lt_1msec, round((sum(calls_lt_1msec)*100/ds3.calls)::numeric,2) calls_lt_1msec_prc
       from (
select CASE WHEN time_per_call >= 10000 THEN total_exec_time ELSE 0 END time_gt_10sec,
       CASE WHEN time_per_call >= 10000 THEN calls ELSE 0 END calls_gt_10sec,
       CASE WHEN time_per_call >= 1000 AND time_per_call < 10000 THEN total_exec_time ELSE 0 END time_gt_1sec,
       CASE WHEN time_per_call >= 1000 AND time_per_call < 10000 THEN calls ELSE 0 END calls_gt_1sec,
       CASE WHEN time_per_call >= 100 AND time_per_call < 1000 THEN total_exec_time ELSE 0 END time_gt_100msec,
       CASE WHEN time_per_call >= 100 AND time_per_call < 1000 THEN calls ELSE 0 END calls_gt_100msec,
       CASE WHEN time_per_call >= 10 AND time_per_call < 100 THEN total_exec_time ELSE 0 END time_gt_10msec,
       CASE WHEN time_per_call >= 10 AND time_per_call < 100 THEN calls ELSE 0 END calls_gt_10msec,
       CASE WHEN time_per_call >= 1 AND time_per_call < 10 THEN total_exec_time ELSE 0 END time_gt_1msec,
       CASE WHEN time_per_call >= 1 AND time_per_call < 10 THEN calls ELSE 0 END calls_gt_1msec,
       CASE WHEN time_per_call < 1 THEN total_exec_time ELSE 0 END time_lt_1msec,
       CASE WHEN time_per_call < 1 THEN calls ELSE 0 END calls_lt_1msec
       from ( SELECT total_exec_time, calls,
              round((CASE WHEN calls = 0 OR calls IS NULL THEN total_exec_time ELSE (total_exec_time / calls)::numeric END)::numeric, 4) time_per_call
    from pg_stat_statements) ds1 ) ds2,
    (select sum(total_exec_time) total_exec_time, sum(calls) calls from pg_stat_statements ) ds3
    group by ds3.total_exec_time, ds3.calls ;

Для удобства сравнения я заполнил табличку из одной строки результатов в несколько, чтобы не писать ещё один уровень вложенности запроса. Распределение получилось такм :

Мы можем тут видеть, что более 91% времени работы БД выполнялись запросы длительностью более 1 секунды, и поэтому наша выборка с частотой среза раз в секунду вполне репрезентативна, не менее, чем на 91%. Однако что же с остальными запросами меньшей длительностью, которых гораздо больше и которые явно проскочили мимо нашей выборки ? А вот здесь, если я не ошибаюсь, нас поджидают боги математики, чтобы сказать, что, конечно, в отдельно взятом моменте распределение данных может оказаться вполне себе причудливым и случайным, но с увеличением временного диапазона и количества выборок равного периода распределение стремиться к нормальному, т.е. даже относительно небольшая выборка показывает адекватную картину не с полной, но с очень большой вероятностью. Так же работает и сэмплирование при сборе Ораклом статистики по таблицам с частичной выборкой, и масса иных применений этого подхода, когда гораздо проще потратить ресурс на частичную выборку, чтобы получить достаточно вероятный результат. Так же, как мы отметили выше, работает и выборка ASH, рассчитывая для построения графика TOP Activity не время ожиданий каждой записи, а банально суммируя их количество по разным классам событий ожиданий ... в своё время такая простота, показанная монитором SQL, показалась мне довольно примитивной. Но по прохождении времени и размышлений это представляется вполне приемлемым подходом. Pretty good, то бишь достаточно хорошо. К тому же огромное количество коротких запросов занимают здесь несущественный 1,1 процент общего времени отработки запросов, и для задачи наглядного выделения узких мест не применимы, т.к. выигрыш в полпроцента времени от их оптимизации, упрощённо говоря, погоды в общем случае не сделает никакой. Что однако не отменяет необходимости и такого анализа статистики запросов по временному распределению, и возможности появления сценариев, когда контретный поток автоматизации, не мешая остальным и БД в целом, тормозит на массе мелких запросов, требуя переработки архитектуры решения

Статистика событий ожидания Следующий момент. У PostgreSQL совершенно точно нет кумулятивных счётчиков по сотням событий ожидания и статистикам работы СУБД и отдельных сессий. Здесь Oracle впереди планеты всей, и даже у MS SQL есть какой-то коллектор, реализация которого непонятна. Однако для целей анализа распределения нагрузки СУБД по событиям ожидания и классам событий ожидания компанией ПостгресПРО реализовано расширение pg_wait_sampling (PWS), отданное сообществу, как и ряд других расширений. В том числе расширений, реализующих хинтование запровов. А вот только в платной их вресии есть расширения для фиксации плана исполнения, определение текущих фаз выполнения запроса и планов ... Возвращаясь к теме. Конечно администратору очень важно графическое представление активности БД, чтобы одним взглядом охватывать картину состояния инфраструктуры. Поэтому, пользуясь наработанным при работе с Ораклом пониманием и на основе всех своих продуктов, в т.ч. и ОРСиМОН БЕССТ, я реализова аналогичные оракловым механизмы TOP Activity для своих решений на PostgreSQL, в частности - криптоагрегатора КрАгрАн БЕССТ, причём в двух вариантах - опираясь на данные расширения Wait Sampling, и, после выявленных недостатков - по сравнению с Oracle ASH, на данных моего ежесекундного коллектора stats activity

источник - коллектор данных pg_stats_activity (кликабельно)		источник - коллектор данных pg_wait_sampling (кликабельно)

Ещё одним неочевидным, но принципиальным моментом, является то, что wait sampling, а отличие от pg_stats_activity. не собирает состояние активных ничего не ждущих сессий. А в оракловом ASH сведения об активных, ничего не ждущих, сессиях есть. И именно на этих данных строится во всеми любимом графике TOP Activity, частично похеренном вендором в 13 версии Cloud Control, компонента CPU Activity, которая "томно золёненькая". И в PostgreSQL в статистическом представлении pg_stats_activity эта статистика тоже есть. А ведь из практического опыта моего в Постгре и прилично большего в Oracle - даже на несильно загруженной БД с небольшим количеством запросов такая активность может составлять около 10%, существенно меняя общую каритину. Здесь важно показать плюсы и минусы расширения pg_wait_sampling (PWS). Конечно, оно не даёт такого полного охвата событий ожидания и статистик, как обязательные кумулятивные счётчики Оракла. Однако оно может собирать срезы статусов системы с частотой с сотню раз больше, чем срезы Oracle ASH, которые позиционируются и используются как достаточно качественный источник информации о состоянях системы. Сэмплирование расширением wait sampling по умолчанию настроено на 100 сэмплов в секунду. Это конечно не так много, но это не предел, и показать более менее адекватную кортину наиболее часто встречающихся событий ожидания этот механизм вполне способен

Он обладает меньшим охватом, чем кумулятивные счётчики Оракла, мимо которых не пройдут и самые малые активности, но очень неплохо для целей практической оценки профиля нагрузки СУБД. К тому же механизм PWS, реализованный как легковесное расширение на языке С, сохраняет при срезе не только событие ожидания и его класс, но и сессию (PID процесса), а также идентификатор запроса (query_id). Этого недостаточно для высокочастотной эмуляции Oracle ASH, т.к. не хватает как минимум поля, аналогичного #serial в Oracle, и идентификатора плана. Это если совсем по минимуму. PID сам по себе недостаточен, т.к. присвоение идентификаторов циклично. В ps_stats_activity это решается наличием понятия начала старта сессии (бэкэнда). Также и наличие идентификатора плана очень полезно для отследивания активности обработки запросов. Поэтому для целей эмуляции ASH мной выбран самописный коллектор. Но и wait sampling востребован именно как инструмент распределения активности СУБД по событиям ожидания

В целом оба два механизма востребованы при текущем и ретроспективном анализе состояния БД, отдельных сессий и запросов, что и реализовано в моём решении, а тройка расширений pg_stat_statement, pg_store_plans, pg_wait_sampling и сборщик данных из pg_stats_activity обеспечивают некое подмножество источников для реализации функционала, привычного инженеру поддержки СУБД Oracle в части сбора и аналитики статистик. Можно добавить сюда и pg_locks или вызов аналогичной системной процедуры. Кроме того, существует масса статистических таблиц по работе различных компонент системы, и масса расширений, реализующих некоторый дополнительный функционал. Всё это, вероятно, предстоит изучить глубже. Кроме того в коммерческой редакции СУБД от ПостгресПРО рассмотренные в статье аспекты получают инструментарий с более широким функционалом. Но основной вывод мой в том, что PostgreSQL это не маленький Oracle, а отдельная СУБД со своим отдельным, другим, впечатляющим функционалом

Статистики подсистем и операций В подсистеме статистических и статусных представлений СУБД PostgreeSQL есть ряд представлений, отражающих текущее функционирование подсистем СУБД и сервисных операций. Так же, как и для pg_stats_activity коллекторов и анализаторов этих статистик в самой БД нет, но базовая информация есть. В отличие от сессий здесь, вероятно, не нужен ежесекундный охват и хранение срезов, хотя в том же Oracle достаточно редкие срезы основных статистик обусловлены именно наличием кумулятивных счетчиков и достаточностью хранящих данные кешей памяти. В PgSQL ряд таких счётчиков тоже кумулятивен, что позволяет не нагружать систему постоянными частыми снапшотами. Кроме того, как раз в этой части уже существуют разработки, которые, как ранее упоминаемый pg_profile, претендуют на аналогичный AWR функционал, хотя реально его не обеспечивают. Но совместно с моей разработкой, в некоторой степени реализующий функционал Oracle ASH / TOP Activity, или ее аналогами, такой симбиоз уже позволяет анализировать функционирование кластера более адекватно

Свои продукты и сторонние решения

Даже при наличии своего инженерного продукта КАМАктСоСт БЕССТ вопросы выбора инструментария не теряют актуальности. Продукт сделан свободно доступным для некоммерческого использования https://github.com/bsszerot/camactsost, но лицензия запрещает ставить его для коммерческого использования. Казалось бы хозяин - барин. Продавай под разными лицензиями. Но нет, надеюсь и намереваю, что в сторону этого пройденного этапа возврата уже не будет. КАМАктСоСт предлагался вниманию компании Постгрес ПРО через Сергея Суетина, но был получен ответ, что это им не интересно. С этого момента неинтересно стало и мне. Продукт открыт для некоммерсантов и в моей картине мира выведен в статус учебного. А для задач поддержки СУБД его технологии являются донором знаний. Ровно так же, как донором знаний для коробочного продукта ТиКСер БЕССТ стала система КоСиСулс БЕССТ ((С), 2006, Sergey S. Belonin)

А вот инструментарий в целом полезно подобрать из стэка свободного ПО. Так даже интереснее. В качестве примера можно привести задачу получения графика активности кластера, аналогичного Oracle TOP Activity, в разрезе по классам ожидания. Можно ли это сделать полностью свободными инструментами ? В Оракле существует встроенный коллектор статистик, AWR (automatic workload repositary), а в Postgre SQL его нет. Поэтому КАМАктСоСт БЕССТ сам собирает, хранит и агрегирует статистические срезы. Если взять запросы КАМАктСОСт для сбора и агрегации данных таблички pg_stat_activity в рассматриваемом кластере, и использовать открытые средства построения графиков, можно получить отображение, похожее на отображение моего продукта

Ниже представлен пример одного и того же графика в КАМАктСоСт, и в продукте Графана

источник - коллектор данных КАМАктСоСт БЕССТ, график - КАМАктСоСт БЕССТ		источник - коллектор данных КАМАктСоСт БЕССТ, график - Графана

Сам источник данных, равно как и аналитический запрос, в рассматриваемых примерах в любом случае - КАМАктСоСт. Ну нету коллектора данных внутри СУБД. Каждый дрочит как он хочет пишет эти слои сбора, агрегации и аналитики сам

Запрос, вытаскивающий данные в виде временных серий для построения графика TOP Activity, был придуман мной в конце 2000х, и использован в аналогичном продукте для Оракла - ОРСиМОН БЕССТ. ПРи последующей работе с PostgreSQL он был адаптирован, а в последующем и существенно доработан. Теперь он показывает данные и для периодов простоя кластера, что оказывается важным в случае эпизодических рестарта и останова кластера. Но для целей настоящего изложения будет приведен старый, не доработанный запрос. Во многих случаях его вполне хватает

-- ORSiMON BESST TA-query, optimized for PostgreSQL
-- код депонирован при регистрации продукта в Роспатенте, прямое копирование для коммерческих целей запрещено,
-- но не запрещено написать свой отличающийся код, реализующий тот же алгоритм на основе изучения моего кода
select src2.sampling_time::timestamp sampling_time, sum(src2.wc_CPU_Active) wc_CPU_Active, sum(src2.wc_Activity) wc_Activity,
       sum(src2.wc_BufferPin) wc_BufferPin, sum(src2.wc_Client) wc_Client, sum(src2.wc_Extension) wc_Extension,
       sum(src2.wc_IO) wc_IO, sum(src2.wc_IPC) wc_IPC, sum(src2.wc_Lock) wc_Lock, sum(src2.wc_LWLock) wc_LWLock,
       sum(src2.wc_Timeout) wc_Timeout, sum(src2.wc_Other) wc_Other
       from (select src1.sampling_time,
                    CASE WHEN src1.wait_event_type = 'CPU Active' THEN src1.value ELSE 0 END wc_CPU_Active,
                    CASE WHEN src1.wait_event_type = 'Activity' THEN src1.value ELSE 0 END wc_Activity,
                    CASE WHEN src1.wait_event_type = 'BufferPin' THEN src1.value ELSE 0 END wc_BufferPin,
                    CASE WHEN src1.wait_event_type = 'Client' THEN src1.value ELSE 0 END wc_Client,
                    CASE WHEN src1.wait_event_type = 'Extension' THEN src1.value ELSE 0 END wc_Extension,
                    CASE WHEN src1.wait_event_type = 'IO' THEN src1.value ELSE 0 END wc_IO,
                    CASE WHEN src1.wait_event_type = 'IPC' THEN src1.value ELSE 0 END wc_IPC,
                    CASE WHEN src1.wait_event_type = 'Lock' THEN src1.value ELSE 0 END wc_Lock,
                    CASE WHEN src1.wait_event_type = 'LWLock' THEN src1.value ELSE 0 END wc_LWLock,
                    CASE WHEN src1.wait_event_type = 'Timeout' THEN src1.value ELSE 0 END wc_Timeout,
                    CASE WHEN src1.wait_event_type NOT IN ('CPU Active','Activity','BufferPin','Client','Extension','IO','IPC','Lock','LWLock','Timeout')
                              THEN src1.value ELSE 0 END wc_Other
                    from (select ash.sampling_time sampling_time,
                                 CASE WHEN ash.wait_event_type IS NULL THEN 'CPU Active' ELSE ash.wait_event_type END wait_event_type,
                                 CASE WHEN round(sum(ash.value)/60,4) IS NULL THEN 0 ELSE round(sum(ash.value)/60,4) END value
                                 from (select date_trunc('minute', sampling_time) sampling_time, wait_event_type, count(*) value
                                              from bestat_sa_history
                                              where sampling_time >= (now() - interval '20 hour') AND sampling_time <= now() 
                                              group by date_trunc('minute', sampling_time), wait_event_type) ash
                                              group by ash.sampling_time, ash.wait_event_type) src1 ) src2
         group by src2.sampling_time
         order by src2.sampling_time asc

Теперь - как собирать данные сырые ? В моём случае создана табличка, в которую ежесекундно складывается текущий срез состояния из pg_stat_activity. Делается это отдельной функцией внутри кластера БД postgre SQL, но запускается сбор статистики в виде юниксового процесса. Так гораздо проще отслежиать двойной и множественный старт, которых мы хотим избежать, а также ситуацию падения и автоматического рестарта запроса. Сама процедура сбора статистики такова (код депонирован при регистрации продукта в Роспатенте, прямое копирование запрещено, но не запрещено написать своё, реализующее тот же алгоритм):

-- KAMActSoSt (CAMActSoSt) BESST код депонирован при регистрации продукта в Роспатенте, прямое копирование для коммерческих целей запрещено,
-- но не запрещено написать свой отличающийся код, реализующий тот же алгоритм на основе изучения моего кода
CREATE USER BESTAT ...
GRANT pg_read_all_stats TO BESTAT ;
CREATE DATABASE BESTAT ;
CREATE TABLE IF NOT EXISTS bestat_sa_history_parameters ( sz_parameter character varying, sz_value character varying) ;
CREATE TABLE IF NOT EXISTS bestat_sa_history
       ( sampling_time timestamp with time zone, datid oid, datname name, pid integer, leader_pid integer, usesysid oid,
         usename name, application_name text, client_addr inet, client_hostname text, client_port integer,
         backend_start timestamp with time zone, xact_start timestamp with time zone, query_start timestamp with time zone,
         state_change timestamp with time zone, wait_event_type text, wait_event text, state text, backend_xid xid,
         backend_xmin xid, query_id bigint, backend_type text ) ;
ALTER TABLE IF EXISTS bestat_sa_history OWNER to crypta ;
CREATE INDEX IF NOT EXISTS bestat_sa_history_idx_on_st_01 ON bestat_sa_history USING btree (sampling_time ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS bestat_sa_history_idx_on_st_wet_01 ON bestat_sa_history USING btree
       (sampling_time ASC NULLS LAST, wait_event_type ASC NULLS LAST) ;

CREATE OR REPLACE PROCEDURE bestat_fill_sa_history(
    IN v_iteration integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare
v_Count INTEGER ;
sz_is_collect VARCHAR ;
v_insert_timestamp TIMESTAMP ;
begin
v_Count := 0 ;
while (v_Count < v_Iteration) LOOP
      if (v_Count = (v_Iteration - 1)) then
         commit ;
         select sz_value into sz_is_collect from bestat_sa_history_parameters where sz_parameter = 'is_collect' ;
         if sz_is_collect = 'yes' then v_Count := 0 ;
            else v_count := v_Iteration + 10 ;
            end if ;
         end if ;
      v_insert_timestamp := clock_timestamp() ;
      insert into bestat_sa_history
             (SELECT v_insert_timestamp, datid, datname, pid, leader_pid, usesysid, usename,
                     application_name, client_addr, client_hostname, client_port, backend_start, xact_start,
                     query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin,
                     query_id, backend_type
                     from pg_stat_activity) ;
      v_Count = v_Count + 1 ;
      perform pg_sleep(1) ;
      end LOOP ;
end ;
$BODY$;
ALTER PROCEDURE bestat_fill_sa_history(integer)
    OWNER TO crypta;

А скрипт запуска и отслеживания написан в полном соответствии с подходами, придуманными в начале 2000х ещё в моей КоСиКУЛС БЕССТ. В архитектуре заложена также возможность принудительного останова сбора статистики, путём изменения значения в таблице конфигурации сбора статистики, обработка истуации рестарта сервера и коммит сериями, чтобы немного разгрузить процесс сохранения данных

# KAMActSoSt (CAMActSoSt) BESST код депонирован при регистрации продукта в Роспатенте, прямое копирование для коммерческих целей запрещено,
# но не запрещено написать свой отличающийся код, реализующий тот же алгоритм на основе изучения моего кода
#!/usr/bin/bash

. /home/cragr/crypto_agregator/conf/bash_parameter.cragregator
CURR_DT=`date "+%Y%m%d%H%M%S"`

echo "cragran $CURR_DT start_PG_stats_collector - старт коллектора SAH ..." >> $LOG_MAIN_FILE

IS_STARTED=`ps -ef | grep -v grep | grep start_PG_stats_collector.sh | wc -l `
if [ $IS_STARTED -gt 1 ] && [ -f $LOCK_DIR/start_PG_stats_collector ]; then
   echo "cragran $CURR_DT start_PG_stats_collector -- уже стартовано, ничего не делаем ..." >> $LOG_MAIN_FILE
   exit 1 ;
   fi

echo > $LOCK_DIR/start_PG_stats_collector
ls -l $LOCK_DIR/start_PG_stats_collector
export PGPASSWORD="$PG_PASSWORD" ;

psql -U $PG_USER -h $PG_HOST -d $PG_DB <<EEOF
update bestat_sa_history_parameters set sz_value = 'yes' where sz_parameter = 'is_collect' ;
CALL bestat_fill_sa_history(10) ;
EEOF

echo "cragran $CURR_DT start_PG_stats_collector -- функция отработала, удаляю файл блокировки ..." >> $LOG_MAIN_FILE
rm -f $LOCK_DIR/start_PG_stats_collector

echo "cragran $CURR_DT start_PG_stats_collector - стоп коллектора SAH ..." >> $LOG_MAIN_FILE

Белонин С.С. (С), 10 июня 2024 года
(история последующих модификаций не фиксируются)