[Системное администрирование, PostgreSQL, SQL, Администрирование баз данных] SQL HowTo: красивые отчеты по «дырявым» данным — GROUPING SETS
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Для пользователя наш СБИС представляется единой системой управления бизнесом, но внутри состоит из множества взаимодействующих сервисов. И чем их становится больше — тем выше вероятность возникновения каких-то неприятностей, которые необходимо вовремя отлавливать, исследовать и пресекать.
Поэтому, когда на каком-то из тысяч подконтрольных серверов случается аномальное потребление ресурсов (CPU, памяти, диска, сети, ...), возникает потребность разобраться «кто виноват, и что делать».
Для оперативного мониторинга использования ресурсов Linux-сервера «в моменте» существует утилита pidstat. То есть если пики нагрузки периодичны — их можно «высидеть» прямо в консоли. Но мы-то хотим эти данные анализировать постфактум, пытаясь найти процесс, создавший максимальную нагрузку на ресурсы.
То есть хочется иметь возможность смотреть по ранее собранным данным разные красивые отчеты с группировкой и детализацией на интервале типа таких:
В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS.
Сначала посмотрим, что за данные мы можем извлечь, если брать «все по максимуму»:
pidstat -rudw -lh 1
Time
UID
PID
%usr
%system
%guest
%CPU
CPU
minflt/s
majflt/s
VSZ
RSS
%MEM
kB_rd/s
kB_wr/s
kB_ccwr/s
cswch/s
nvcswch/s
Command
1594893415
0
1
0.00
13.08
0.00
13.08
52
0.00
0.00
197312
8512
0.00
0.00
0.00
0.00
0.00
7.48
/usr/lib/systemd/systemd --switched-root --system --deserialize 21
1594893415
0
9
0.00
0.93
0.00
0.93
40
0.00
0.00
0
0
0.00
0.00
0.00
0.00
350.47
0.00
rcu_sched
1594893415
0
13
0.00
0.00
0.00
0.00
1
0.00
0.00
0
0
0.00
0.00
0.00
0.00
1.87
0.00
migration/11.87
Все эти значения делятся на несколько классов. Некоторые из них меняются постоянно (активность CPU и диска), другие — редко (выделение памяти), а Command — не только редко меняется в рамках одного процесса, но еще и регулярно повторяется на разных PID.
Структура базы
Для простоты давайте ограничимся одной метрикой каждого «класса», которые мы будем сохранять: %CPU, RSS и Command.
Раз мы заведомо знаем, что Command регулярно повторяется — просто вынесем его в отдельную таблицу-словарь, где UUID-ключом будет выступать MD5-хэш:
CREATE TABLE diccmd(
cmd
uuid
PRIMARY KEY
, data
varchar
);
А для самих данных нам подойдет таблица такого вида:
CREATE TABLE pidstat(
host
uuid
, tm
integer
, pid
integer
, cpu
smallint
, rss
bigint
, cmd
uuid
);
Обращу внимание, что раз %CPU приходит к нам всегда с точностью 2 знаков после запятой и заведомо не превышает 100.00, то мы спокойно можем домножить его на 100 и положить в smallint. С одной стороны, это избавит нас от проблем точности учета при операциях, с другой — все-таки лучше хранить только 2 байта по сравнению с 4 байтами real или 8 байтами double precision.
Подробнее о способах эффективной упаковки записей в PostgreSQL-хранилище можно прочитать в статье «Экономим копеечку на больших объемах», а про увеличение пропускной способности базы на запись — в «Пишем на субсветовой: 1 host, 1 day, 1TB».
«Бесплатное» хранение NULL'ов
Чтобы сэкономить производительность дисковой подсистемы нашей базы и занимаемый базой объем, постараемся как можно больше данных представить в виде NULL — их хранение практически «бесплатно», поскольку занимает лишь бит в заголовке записи.
Подробнее с внутренней механикой представления записей в PostgreSQL можно ознакомиться в докладе Николая Шаплова на PGConf.Russia 2016 «Что у него внутри: хранение данных на низком уровне». Конкретно хранению NULL посвящен слайд #16.
Снова внимательно посмотрим на виды наших данных:
- CPU/DSK
Меняется постоянно, но очень часто обращается в ноль — так что выгодно писать в базу NULL вместо 0.
- RSS/CMD
Меняется достаточно редко — поэтому будем писать NULL вместо повторов в рамках одного и того же PID.
Получается картинка вроде такой, если смотреть на нее в разрезе конкретного PID:
Понятно, что если у нас процесс начал выполнять другую команду, то значение используемой памяти тоже наверняка окажется не таким, как раньше — поэтому договоримся, что при смене CMD значение RSS тоже будем фиксировать, независимо от предыдущего значения.
То есть у записи с заполненным значением CMD заполнено и значение RSS. Запомним этот момент, он нам еще пригодится.
Собираем красивый отчет
Давайте теперь соберем запрос, который покажет нам потребителей ресурсов конкретного хоста на определенном временном интервале.
Но сделаем это сразу с минимальным использованием ресурсов — примерно как в статье про SELF JOIN и оконные функции.
Использование входящих параметров
Чтобы не указывать значения параметров отчета (или $1/$2) в нескольких местах по ходу SQL-запроса, выделим CTE из единственного json-поля, в котором по ключам находятся эти самые параметры:
-- сохраняем параметры отчета
WITH args AS (
SELECT
json_object(
ARRAY[
'dtb'
, extract('epoch' from '2020-07-16 10:00'::timestamp(0)) -- переводим timestamp в integer
, 'dte'
, extract('epoch' from '2020-07-16 10:01'::timestamp(0))
, 'host'
, 'e828a54d-7e8a-43dd-b213-30c3201a6d8e' -- это у нас uuid
]::text[]
)
)
Извлекаем «сырые» данные
Поскольку никаких сложных агрегатов мы не придумывали, единственный способ проанализировать данные — прочитать их. Для этого нам понадобится очевидный индекс:
CREATE INDEX ON pidstat(host, tm);
-- извлекаем "сырые" данные
, src AS (
SELECT
*
FROM
pidstat
WHERE
host = ((TABLE args) ->> 'host')::uuid AND
tm >= ((TABLE args) ->> 'dtb')::integer AND
tm < ((TABLE args) ->> 'dte')::integer
)
Группировка по ключу анализа
Для каждого найденного PID определим интервал его активности и возьмем CMD с первой записи на этом интервале.
Для этого воспользуемся уникализацией через DISTINCT ON и оконными функциями:
-- группировка по ключу анализа
, pidtm AS (
SELECT DISTINCT ON(pid)
host
, pid
, cmd
, min(tm) OVER(w) tmb -- начало активности процесса на интервале
, max(tm) OVER(w) tme -- завершение активности
FROM
src
WINDOW
w AS(PARTITION BY pid)
ORDER BY
pid
, tm
)
Границы активности процесса
Заметим, что относительно начала нашего интервала первой попавшейся записью может оказаться как та, которая уже имеет заполненное поле CMD (PID#1 на картинке выше), так и с NULL'ом, обозначающим продолжение заполненного «выше» по хронологии значения (PID#2).
Те из PID, которые остались без CMD в результате предыдущей операции, начались раньше начала нашего интервала — значит, эти «начала» надо найти:
Поскольку мы точно знаем, что очередной сегмент активности начинается с заполненного значения CMD (а там и заполненный RSS, значит), тут нам поможет условный индекс:
CREATE INDEX ON pidstat(host, pid, tm DESC) WHERE cmd IS NOT NULL;
-- определяем начало активности каждого "неопределившегося" процесса
, precmd AS (
SELECT
t.host
, t.pid
, c.tm
, c.rss
, c.cmd
FROM
pidtm t
, LATERAL(
SELECT
*
FROM
pidstat -- увы, SELF JOIN не избежать
WHERE
(host, pid) = (t.host, t.pid) AND
tm < t.tmb AND
cmd IS NOT NULL -- садимся на условный индекс
ORDER BY
tm DESC
LIMIT 1
) c
WHERE
t.cmd IS NULL -- только для "неопределившихся"
)
Если мы хотим (а мы — хотим) знать время окончания активности сегмента, то уже для каждого PID придется воспользоваться «двухходовкой» для определения нижней границы.
Аналогичную методику мы уже использовали в статье «PostgreSQL Antipatterns: навигация по реестру».
-- определяем момент окончания активности сегмента
, pstcmd AS (
SELECT
host
, pid
, c.tm
, NULL::bigint rss
, NULL::uuid cmd
FROM
pidtm t
, LATERAL(
SELECT
tm
FROM
pidstat
WHERE
(host, pid) = (t.host, t.pid) AND
tm > t.tme AND
tm < coalesce((
SELECT
tm
FROM
pidstat
WHERE
(host, pid) = (t.host, t.pid) AND
tm > t.tme AND
cmd IS NOT NULL
ORDER BY
tm
LIMIT 1
), x'7fffffff'::integer) -- MAX_INT4
ORDER BY
tm DESC
LIMIT 1
) c
)
JSON-конвертация форматов записей
Замечу, что мы отбирали в precmd/pstcmd только те поля, которые влияют на последующие строки, а всякие CPU/DSK, которые меняются постоянно — нет. Поэтому формат записей в исходной таблице и этих CTE у нас расходится. Не беда!
- row_to_json — превращаем каждую запись с полями в json-объект
- array_agg — собираем все записи в '{...}'::json[]
- array_to_json — преобразуем массив-из-JSON в JSON-массив '[...]'::json
- json_populate_recordset — генерируем из JSON-массива выборку заданной структуры
Тут мы используем именно однократный вызов json_populate_recordset вместо множественного json_populate_record, потому что это банально быстрее в разы.
Склеиваем найденные «начала» и «концы» в общую кучу и добавляем к исходному набору записей:
-- склеиваем все
, uni AS (
TABLE src
UNION ALL
SELECT
*
FROM
json_populate_recordset( -- развернули в полный
NULL::pidstat
, (
SELECT
array_to_json(array_agg(row_to_json(t))) -- свернули сокращенный формат
FROM
(
TABLE precmd
UNION ALL
TABLE pstcmd
) t
)
)
)
Заполняем NULL-пропуски повторов
Воспользуемся моделью, рассмотренной в статье «SQL HowTo: собираем «цепочки» с помощью window functions».
Сначала выделим группы «повторов»:
-- выделение групп
, grp AS (
SELECT
*
, count(*) FILTER(WHERE cmd IS NOT NULL) OVER(w) grp -- группы по CMD
, count(*) FILTER(WHERE rss IS NOT NULL) OVER(w) grpm -- группы по RSS
FROM
uni
WINDOW
w AS(PARTITION BY pid ORDER BY tm)
)
Причем по CMD и RSS группы будут независимы друг от друга, поэтому могут выглядеть примерно так:
Заполним пропуски по RSS и посчитаем продолжительность каждого отрезка, чтобы корректно учесть распределение нагрузки по времени:
-- заполняем пропуски
, rst AS (
SELECT
*
, CASE
WHEN least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) >= greatest(tm, ((TABLE args) ->> 'dtb')::integer) THEN
least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) - greatest(tm, ((TABLE args) ->> 'dtb')::integer) + 1
END gln -- продолжительность сегмента от предыдущей записи или начала интервала
, first_value(rss) OVER(PARTITION BY pid, grpm ORDER BY tm) _rss -- заполнение пропусков по RSS
FROM
grp
WINDOW
w AS(PARTITION BY pid, grp ORDER BY tm)
)
Мультигруппировка с помощью GROUPING SETS
Поскольку мы хотим увидеть в результате одновременно и сводную информацию по всему процессу, и его детализацию по разным сегментам активности, воспользуемся группировкой сразу по нескольким наборам ключей с помощью GROUPING SETS:
-- мультигруппировка
, gs AS (
SELECT
pid
, grp
, max(grp) qty -- количество сегментов активности по PID
, (array_agg(cmd ORDER BY tm) FILTER(WHERE cmd IS NOT NULL))[1] cmd -- "должен остаться только один"
, sum(cpu) cpu
, avg(_rss)::bigint rss
, min(tm) tmb
, max(tm) tme
, sum(gln) gln
FROM
rst
GROUP BY
GROUPING SETS((pid, grp), pid)
)
Вариант использования (array_agg(... ORDER BY ..) FILTER(WHERE ...))[1] позволяет нам прямо при группировке, без дополнительных телодвижений получить первое непустое (даже если оно не самое первое) значение из всего набора.
Вариант получения сразу нескольких разрезов целевой выборки очень удобен для формирования различных отчетов с детализацией, чтобы все детализирующие данные не надо было перестраивать, а чтобы в UI они попадали вместе с основной выборкой.
Словарь вместо JOIN
Создаем «словарь» CMD для всех найденных сегментов:
Подробнее про методику «ословаривания» можно прочесть в статье «PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN».
-- словарь CMD
, cmdhs AS (
SELECT
json_object(
array_agg(cmd)::text[]
, array_agg(data)
)
FROM
diccmd
WHERE
cmd = ANY(ARRAY(
SELECT DISTINCT
cmd
FROM
gs
WHERE
cmd IS NOT NULL
))
)
А теперь используем его вместо JOIN, получая финальные «красивые» данные:
SELECT
pid
, grp
, CASE
WHEN grp IS NOT NULL THEN -- это "сегмент" активности
cmd
END cmd
, (nullif(cpu::numeric / gln, 0))::numeric(32,2) cpu -- приводим CPU к "средней" нагрузке
, nullif(rss, 0) rss
, tmb -- верхняя граница активности
, tme -- нижняя граница активности
, gln -- продолжительность активности
, CASE
WHEN grp IS NULL THEN -- это весь процесс
qty
END cnt
, CASE
WHEN grp IS NOT NULL THEN
(TABLE cmdhs) ->> cmd::text -- извлекаем данные из словаря
END command
FROM
gs
WHERE
grp IS NOT NULL OR -- это запись "сегмента"
qty > 1 -- или в процессе больше одного сегмента
ORDER BY
pid DESC
, grp NULLS FIRST;
Напоследок убедимся, что весь наш запрос при выполнении оказался достаточно легковесным:
[посмотреть на explain.tensor.ru]
Всего 44ms и 33MB данных прочитано!
===========
Источник:
habr.com
===========
Похожие новости:
- [Google API, Python, Анализ и проектирование систем] Создание системы антифрода в такси с нуля
- [Open source, Конференции] Открыта регистрация на онлайн-конференцию Linux Vacation / Eastern Europe — LVEE 2020
- [Сетевое оборудование, Сетевые технологии, Системное администрирование] Паук для паутины или центральный узел распределенной сети
- [IT-инфраструктура, IT-стандарты, Анализ и проектирование систем, Системное администрирование, Финансы в IT] Классификация критичности информационных систем
- [Open source, OpenStreetMap, Визуализация данных, Научно-популярное, Программирование] Делаем маршрутизацию (роутинг) на OpenStreetMap. Добавляем поддержку односторонних дорог
- [Хостинг, Системное администрирование, Развитие стартапа, Интервью, IT-компании] Куда со временем деваются PHP-программисты и хорошо ли им там?
- [*nix, Open source] FOSS News №26 – обзор новостей свободного и открытого ПО за 20–26 июля 2020 года
- [Java, Разработка под Linux, Управление проектами] Как я автоматизировал разворачивание приложений на Linux на коленке с помощью Bash скриптов и Java
- [DevOps, Go, Open source, Системное администрирование, Тестирование веб-сервисов] Squzy — бесплатная open-source self-host система мониторинга с инцидентами и уведомлениями
- [IT-компании, Системное администрирование, Софт] Microsoft будет принудительно устанавливать обновление May 2020 Update на ПК с Windows 10 версий 1809 и 1903
Теги для поиска: #_sistemnoe_administrirovanie (Системное администрирование), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_dba, #_sql_tips_and_tricks, #_null, #_linux, #_pidstat, #_sbis (сбис), #_json_populate_recordset, #_array_to_json, #_nenormalnoe_programmirovanie (ненормальное программирование), #_blog_kompanii_tenzor (
Блог компании Тензор
), #_sistemnoe_administrirovanie (
Системное администрирование
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 00:43
Часовой пояс: UTC + 5
Автор | Сообщение |
---|---|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
Для пользователя наш СБИС представляется единой системой управления бизнесом, но внутри состоит из множества взаимодействующих сервисов. И чем их становится больше — тем выше вероятность возникновения каких-то неприятностей, которые необходимо вовремя отлавливать, исследовать и пресекать. Поэтому, когда на каком-то из тысяч подконтрольных серверов случается аномальное потребление ресурсов (CPU, памяти, диска, сети, ...), возникает потребность разобраться «кто виноват, и что делать». Для оперативного мониторинга использования ресурсов Linux-сервера «в моменте» существует утилита pidstat. То есть если пики нагрузки периодичны — их можно «высидеть» прямо в консоли. Но мы-то хотим эти данные анализировать постфактум, пытаясь найти процесс, создавший максимальную нагрузку на ресурсы. То есть хочется иметь возможность смотреть по ранее собранным данным разные красивые отчеты с группировкой и детализацией на интервале типа таких: В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS. Сначала посмотрим, что за данные мы можем извлечь, если брать «все по максимуму»: pidstat -rudw -lh 1
UID PID %usr %system %guest %CPU CPU minflt/s majflt/s VSZ RSS %MEM kB_rd/s kB_wr/s kB_ccwr/s cswch/s nvcswch/s Command 1594893415 0 1 0.00 13.08 0.00 13.08 52 0.00 0.00 197312 8512 0.00 0.00 0.00 0.00 0.00 7.48 /usr/lib/systemd/systemd --switched-root --system --deserialize 21 1594893415 0 9 0.00 0.93 0.00 0.93 40 0.00 0.00 0 0 0.00 0.00 0.00 0.00 350.47 0.00 rcu_sched 1594893415 0 13 0.00 0.00 0.00 0.00 1 0.00 0.00 0 0 0.00 0.00 0.00 0.00 1.87 0.00 migration/11.87 Все эти значения делятся на несколько классов. Некоторые из них меняются постоянно (активность CPU и диска), другие — редко (выделение памяти), а Command — не только редко меняется в рамках одного процесса, но еще и регулярно повторяется на разных PID. Структура базы Для простоты давайте ограничимся одной метрикой каждого «класса», которые мы будем сохранять: %CPU, RSS и Command. Раз мы заведомо знаем, что Command регулярно повторяется — просто вынесем его в отдельную таблицу-словарь, где UUID-ключом будет выступать MD5-хэш: CREATE TABLE diccmd(
cmd uuid PRIMARY KEY , data varchar ); А для самих данных нам подойдет таблица такого вида: CREATE TABLE pidstat(
host uuid , tm integer , pid integer , cpu smallint , rss bigint , cmd uuid ); Обращу внимание, что раз %CPU приходит к нам всегда с точностью 2 знаков после запятой и заведомо не превышает 100.00, то мы спокойно можем домножить его на 100 и положить в smallint. С одной стороны, это избавит нас от проблем точности учета при операциях, с другой — все-таки лучше хранить только 2 байта по сравнению с 4 байтами real или 8 байтами double precision. Подробнее о способах эффективной упаковки записей в PostgreSQL-хранилище можно прочитать в статье «Экономим копеечку на больших объемах», а про увеличение пропускной способности базы на запись — в «Пишем на субсветовой: 1 host, 1 day, 1TB».
«Бесплатное» хранение NULL'ов Чтобы сэкономить производительность дисковой подсистемы нашей базы и занимаемый базой объем, постараемся как можно больше данных представить в виде NULL — их хранение практически «бесплатно», поскольку занимает лишь бит в заголовке записи. Подробнее с внутренней механикой представления записей в PostgreSQL можно ознакомиться в докладе Николая Шаплова на PGConf.Russia 2016 «Что у него внутри: хранение данных на низком уровне». Конкретно хранению NULL посвящен слайд #16.
Получается картинка вроде такой, если смотреть на нее в разрезе конкретного PID: Понятно, что если у нас процесс начал выполнять другую команду, то значение используемой памяти тоже наверняка окажется не таким, как раньше — поэтому договоримся, что при смене CMD значение RSS тоже будем фиксировать, независимо от предыдущего значения. То есть у записи с заполненным значением CMD заполнено и значение RSS. Запомним этот момент, он нам еще пригодится. Собираем красивый отчет Давайте теперь соберем запрос, который покажет нам потребителей ресурсов конкретного хоста на определенном временном интервале. Но сделаем это сразу с минимальным использованием ресурсов — примерно как в статье про SELF JOIN и оконные функции. Использование входящих параметров Чтобы не указывать значения параметров отчета (или $1/$2) в нескольких местах по ходу SQL-запроса, выделим CTE из единственного json-поля, в котором по ключам находятся эти самые параметры: -- сохраняем параметры отчета
WITH args AS ( SELECT json_object( ARRAY[ 'dtb' , extract('epoch' from '2020-07-16 10:00'::timestamp(0)) -- переводим timestamp в integer , 'dte' , extract('epoch' from '2020-07-16 10:01'::timestamp(0)) , 'host' , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e' -- это у нас uuid ]::text[] ) ) Извлекаем «сырые» данные Поскольку никаких сложных агрегатов мы не придумывали, единственный способ проанализировать данные — прочитать их. Для этого нам понадобится очевидный индекс: CREATE INDEX ON pidstat(host, tm);
-- извлекаем "сырые" данные
, src AS ( SELECT * FROM pidstat WHERE host = ((TABLE args) ->> 'host')::uuid AND tm >= ((TABLE args) ->> 'dtb')::integer AND tm < ((TABLE args) ->> 'dte')::integer ) Группировка по ключу анализа Для каждого найденного PID определим интервал его активности и возьмем CMD с первой записи на этом интервале. Для этого воспользуемся уникализацией через DISTINCT ON и оконными функциями: -- группировка по ключу анализа
, pidtm AS ( SELECT DISTINCT ON(pid) host , pid , cmd , min(tm) OVER(w) tmb -- начало активности процесса на интервале , max(tm) OVER(w) tme -- завершение активности FROM src WINDOW w AS(PARTITION BY pid) ORDER BY pid , tm ) Границы активности процесса Заметим, что относительно начала нашего интервала первой попавшейся записью может оказаться как та, которая уже имеет заполненное поле CMD (PID#1 на картинке выше), так и с NULL'ом, обозначающим продолжение заполненного «выше» по хронологии значения (PID#2). Те из PID, которые остались без CMD в результате предыдущей операции, начались раньше начала нашего интервала — значит, эти «начала» надо найти: Поскольку мы точно знаем, что очередной сегмент активности начинается с заполненного значения CMD (а там и заполненный RSS, значит), тут нам поможет условный индекс: CREATE INDEX ON pidstat(host, pid, tm DESC) WHERE cmd IS NOT NULL;
-- определяем начало активности каждого "неопределившегося" процесса
, precmd AS ( SELECT t.host , t.pid , c.tm , c.rss , c.cmd FROM pidtm t , LATERAL( SELECT * FROM pidstat -- увы, SELF JOIN не избежать WHERE (host, pid) = (t.host, t.pid) AND tm < t.tmb AND cmd IS NOT NULL -- садимся на условный индекс ORDER BY tm DESC LIMIT 1 ) c WHERE t.cmd IS NULL -- только для "неопределившихся" ) Если мы хотим (а мы — хотим) знать время окончания активности сегмента, то уже для каждого PID придется воспользоваться «двухходовкой» для определения нижней границы. Аналогичную методику мы уже использовали в статье «PostgreSQL Antipatterns: навигация по реестру».
-- определяем момент окончания активности сегмента
, pstcmd AS ( SELECT host , pid , c.tm , NULL::bigint rss , NULL::uuid cmd FROM pidtm t , LATERAL( SELECT tm FROM pidstat WHERE (host, pid) = (t.host, t.pid) AND tm > t.tme AND tm < coalesce(( SELECT tm FROM pidstat WHERE (host, pid) = (t.host, t.pid) AND tm > t.tme AND cmd IS NOT NULL ORDER BY tm LIMIT 1 ), x'7fffffff'::integer) -- MAX_INT4 ORDER BY tm DESC LIMIT 1 ) c ) JSON-конвертация форматов записей Замечу, что мы отбирали в precmd/pstcmd только те поля, которые влияют на последующие строки, а всякие CPU/DSK, которые меняются постоянно — нет. Поэтому формат записей в исходной таблице и этих CTE у нас расходится. Не беда!
Тут мы используем именно однократный вызов json_populate_recordset вместо множественного json_populate_record, потому что это банально быстрее в разы.
-- склеиваем все
, uni AS ( TABLE src UNION ALL SELECT * FROM json_populate_recordset( -- развернули в полный NULL::pidstat , ( SELECT array_to_json(array_agg(row_to_json(t))) -- свернули сокращенный формат FROM ( TABLE precmd UNION ALL TABLE pstcmd ) t ) ) ) Заполняем NULL-пропуски повторов Воспользуемся моделью, рассмотренной в статье «SQL HowTo: собираем «цепочки» с помощью window functions».
-- выделение групп
, grp AS ( SELECT * , count(*) FILTER(WHERE cmd IS NOT NULL) OVER(w) grp -- группы по CMD , count(*) FILTER(WHERE rss IS NOT NULL) OVER(w) grpm -- группы по RSS FROM uni WINDOW w AS(PARTITION BY pid ORDER BY tm) ) Причем по CMD и RSS группы будут независимы друг от друга, поэтому могут выглядеть примерно так: Заполним пропуски по RSS и посчитаем продолжительность каждого отрезка, чтобы корректно учесть распределение нагрузки по времени: -- заполняем пропуски
, rst AS ( SELECT * , CASE WHEN least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) >= greatest(tm, ((TABLE args) ->> 'dtb')::integer) THEN least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) - greatest(tm, ((TABLE args) ->> 'dtb')::integer) + 1 END gln -- продолжительность сегмента от предыдущей записи или начала интервала , first_value(rss) OVER(PARTITION BY pid, grpm ORDER BY tm) _rss -- заполнение пропусков по RSS FROM grp WINDOW w AS(PARTITION BY pid, grp ORDER BY tm) ) Мультигруппировка с помощью GROUPING SETS Поскольку мы хотим увидеть в результате одновременно и сводную информацию по всему процессу, и его детализацию по разным сегментам активности, воспользуемся группировкой сразу по нескольким наборам ключей с помощью GROUPING SETS: -- мультигруппировка
, gs AS ( SELECT pid , grp , max(grp) qty -- количество сегментов активности по PID , (array_agg(cmd ORDER BY tm) FILTER(WHERE cmd IS NOT NULL))[1] cmd -- "должен остаться только один" , sum(cpu) cpu , avg(_rss)::bigint rss , min(tm) tmb , max(tm) tme , sum(gln) gln FROM rst GROUP BY GROUPING SETS((pid, grp), pid) ) Вариант использования (array_agg(... ORDER BY ..) FILTER(WHERE ...))[1] позволяет нам прямо при группировке, без дополнительных телодвижений получить первое непустое (даже если оно не самое первое) значение из всего набора.
Словарь вместо JOIN Создаем «словарь» CMD для всех найденных сегментов: Подробнее про методику «ословаривания» можно прочесть в статье «PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN».
-- словарь CMD
, cmdhs AS ( SELECT json_object( array_agg(cmd)::text[] , array_agg(data) ) FROM diccmd WHERE cmd = ANY(ARRAY( SELECT DISTINCT cmd FROM gs WHERE cmd IS NOT NULL )) ) А теперь используем его вместо JOIN, получая финальные «красивые» данные: SELECT
pid , grp , CASE WHEN grp IS NOT NULL THEN -- это "сегмент" активности cmd END cmd , (nullif(cpu::numeric / gln, 0))::numeric(32,2) cpu -- приводим CPU к "средней" нагрузке , nullif(rss, 0) rss , tmb -- верхняя граница активности , tme -- нижняя граница активности , gln -- продолжительность активности , CASE WHEN grp IS NULL THEN -- это весь процесс qty END cnt , CASE WHEN grp IS NOT NULL THEN (TABLE cmdhs) ->> cmd::text -- извлекаем данные из словаря END command FROM gs WHERE grp IS NOT NULL OR -- это запись "сегмента" qty > 1 -- или в процессе больше одного сегмента ORDER BY pid DESC , grp NULLS FIRST; Напоследок убедимся, что весь наш запрос при выполнении оказался достаточно легковесным: [посмотреть на explain.tensor.ru] Всего 44ms и 33MB данных прочитано! =========== Источник: habr.com =========== Похожие новости:
Блог компании Тензор ), #_sistemnoe_administrirovanie ( Системное администрирование ), #_postgresql, #_sql, #_administrirovanie_baz_dannyh ( Администрирование баз данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 00:43
Часовой пояс: UTC + 5