Предыстория
Аварийная ситуация — информационная система жутко тормозит.
Как обычно- все менеджеры в панике.
Как обычно — «что там с СУБД»?
Как обычно — «с СУБД, всё хорошо — критичных ошибок нет, отклик не увеличивается, аномалий по метрикам мониторинга СУБД — нет.»
Новая вводная- «У нас все запросы стали очень медленно выполняться».
К сожалению на период развития событий, способа получить объективную картину времени выполнения запросов — не было.
Метрика, позволяющее получать время отклика СУБД показывает «среднюю температуру по больнице». А учитывая, что система высоконагруженная, среднее время отклика не может являться надежной метрикой оценки производительности СУБД. Так, что от разнообразных вариаций на тему SUM(total_time) / SUM(calls) — пользы не так и много.
Убедить разработчиков и менеджеров, что на стороне СУБД проблем нет — не получается.
И поэтому и возникла идея — сделать простой механизм, позволяющий получить оценку производительности СУБД — гистограммы максимального и среднего времени выполнения запросов, на основе уже используемого расширения pg_stat_statements.
SQL — запрос
Для построения гистограммы используется стандартная функция width_bucketwidth_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 SUM(calls) 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 , 59) as b,
count(*) AS "count"
from
pg_stat_statements
group by
b
order by
b
)
SELECT
u1m.b-1 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 "width" ,
ROUND( ( (SELECT SUM(calls) FROM pg_stat_statements where ( mean_exec_time >= (u1m.b-1)*1000 AND mean_exec_time < u1m.b * 1000 ))::numeric / total_count.count::numeric) * 100 , 0 ) AS "calls_pct"
,(SELECT SUM(calls) FROM pg_stat_statements where ( mean_exec_time >= (u1m.b-1)*1000 AND mean_exec_time < u1m.b * 1000 ))::numeric AS "calls"
FROM
under_1m u1m , total_count
WHERE
u1m.b <= 60
UNION
SELECT
o1m.b + 60 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 "width" ,
ROUND( ( (SELECT SUM(calls) FROM pg_stat_statements where ( mean_exec_time >= (o1m.b)*60000 AND mean_exec_time < (o1m.b+1) * 60000 ))::numeric / total_count.count::numeric) * 100 , 0 ) AS "calls_pct"
,(SELECT SUM(calls) FROM pg_stat_statements where ( mean_exec_time >= (o1m.b)*60000 AND mean_exec_time < (o1m.b+1)*60000 ))::numeric AS "calls"
FROM
over_1m o1m , total_count
WHERE
o1m.b > 0
ORDER BY
1 ;
Результат выполнения запроса:
backet | range | width | calls_pct | calls
--------+---------------+-------+-----------+-------------
0 | < 1s | 4788 | 100 | 37950361166
1 | [ 1s - 2s ) | 3 | 0 | 252839
2 | [ 2s - 3s ) | 2 | 0 | 141078
3 | [ 3s - 4s ) | 1 | 0 | 12167
5 | [ 5s - 6s ) | 1 | 0 | 10731
33 | [ 33s - 34s ) | 1 | 0 | 1151
61 | [ 1m - 2m ) | 1 | 0 | 198227
(7 rows)
Развитие идеи
Остальное-дело техники. Можно сделать bash скрипт и по cron получать «снимки» в виде текстовых файлов, которые путем нехитрых манипуляций, преобразовать в таблицы Excel: