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

Kate · 7 Апр 2024

Предыстория.

Аварийная ситуация — информационная система жутко тормозит.
Как обычно- все менеджеры в панике.
Как обычно — «что там с СУБД»?
Как обычно — «с СУБД, всё хорошо — критичных ошибок нет, отклик не увеличивается, аномалий по метрикам мониторинга СУБД — нет.»
Новая вводная- «У нас все запросы стали очень медленно выполняться».
К сожалению на период развития событий, способа получить объективную картину времени выполнения запросов — не было.
Метрика, позволяющее получать время отклика СУБД показывает «среднюю температуру по больнице». А учитывая, что система высоконагруженная, среднее время отклика не может являться надежной метрикой оценки производительности СУБД. Так, что от разнообразных вариаций на тему SUM(total_time) / SUM(calls) — пользы не так и много.
Убедить разработчиков и менеджеров, что на стороне СУБД проблем нет — не получается.
И поэтому и возникла идея — сделать простой механизм, позволяющий получить оценку производительности СУБД — гистограммы максимального и среднего времени выполнения запросов, на основе уже используемого расширения pg_stat_statements.

SQL — запрос

Для построения гистограммы используется стандартная функция width_bucket
width_bucket ( operand double precision, low double precision, high double precision, count integer ) → integer

Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 or count+1 for an input outside that range.

width_bucket(5.35, 0.024, 10.06, 5) → 3

Идея следующая:

Построить корзины интервалов времени выполнения запросов:

До одной минуты с шагом 1 секунда
Свыше 1 минуты до 1 часа с шагом 1 минута

В результате, получился запрос для построения картины распределения среднего времени выполнения запросов(для максимального времени аналогично, только используется столбец max_exec_time вместо mean_exec_time из представления pg_stat_statements):
WITH
total_count AS
(
select count(*) AS "count" from pg_stat_statements
),
under_1m AS
(
select
width_bucket(mean_exec_time, 0::double precision , 60000::double precision , 60) as b,
count(*) AS "count"
from
pg_stat_statements
group by
b
order by
b
),
over_1m AS
(
select
width_bucket(mean_exec_time, 60000::double precision , 3600000 , 60) as b,
count(*) AS "count"
from
pg_stat_statements
group by
b
order by
b
)
SELECT
CASE
WHEN u1m.b = 1 THEN 0
ELSE u1m.b-1
END AS "backet" ,
CASE
WHEN u1m.b = 1 THEN '< 1s'
ELSE '['||to_char(u1m.b-1 , '99')||'s -'||to_char(u1m.b , '99')||'s )'
END AS "range" ,
u1m.count AS "count" ,
ROUND( ( u1m.count::numeric / total_count.count::numeric) * 100 , 2 ) AS "pct" ,
repeat('*',( u1m.count::float / total_count.count * 100 )::int ) as bar
FROM
under_1m u1m , total_count
WHERE
u1m.b <= 60
UNION
SELECT
CASE
WHEN o1m.b = 1 THEN 61
ELSE (o1m.b-1) + 61
END AS "backet" ,
CASE
WHEN o1m.b < 61 THEN '['||to_char(o1m.b , '99')||'m -'||to_char(o1m.b+1 , '99')||'m )'
ELSE '> 1h'
END AS "range" ,
o1m.count AS "count" ,
ROUND( ( o1m.count::numeric / total_count.count::numeric) * 100 , 2 ) AS "pct" ,
repeat('*',( o1m.count::float / total_count.count * 100 )::int ) as bar
FROM
over_1m o1m , total_count
WHERE
o1m.b > 0
ORDER BY
1 ;

Результат выполнения запроса:
backet | range | count | pct | bar
--------+---------------+-------+-------+-----------------------------------------------------------------------------------------------------
0 | < 1s | 4964 | 99.36 | ***************************************************************************************************
1 | [ 1s - 2s ) | 9 | 0.18 |
2 | [ 2s - 3s ) | 4 | 0.08 |
3 | [ 3s - 4s ) | 2 | 0.04 |
4 | [ 4s - 5s ) | 1 | 0.02 |
5 | [ 5s - 6s ) | 5 | 0.10 |
8 | [ 8s - 9s ) | 1 | 0.02 |
9 | [ 9s - 10s ) | 2 | 0.04 |
61 | [ 1m - 2m ) | 1 | 0.02 |
62 | [ 2m - 3m ) | 1 | 0.02 |
(10 rows)

Развитие идеи

Остальное- дело техники. Можно сделать bash скрипт и по cron получать «снимки» в виде текстовых файлов, которые путем нехитрых манипуляций, преобразовать в таблицы Excel:

P.S.

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