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

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

Возможности СУБД 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), отданное сообществу, как и ряд других расширений. В том числе расширений, реализующих хинтование запровов. А вот только в платной их вресии есть расширения для фиксации плана исполнения, определение текущих фаз выполнения запроса и планов ... Возвращаясь к теме. Конечно администратору очень важно графическое представление активности БД, чтобы одним взглядом охватывать картину состояния инфраструктуры. Поэтому, пользуясь наработанным при работе с Ораклом пониманием, я реализова аналогичные WEB формы TOP Activity для своих решений на PostgreSQL, причём в двух вариантах - опираясь на данные расширения Wait Sampling, и, после выявленных недостатков - по сравнению с Oracle ASH, на данных моего ежесекундного коллектора stats activity

Ещё одним неочевидным, но принципиальным моментом, является то, что 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, а отдельная СУБД со своим отдельным, другим, впечатляющим функционалом

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