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

Предыстория

Аварийная ситуация — информационная система жутко тормозит.
Как обычно- все менеджеры в панике.
Как обычно — «что там с СУБД»?
Как обычно — «с СУБД, всё хорошо — критичных ошибок нет, отклик не увеличивается, аномалий по метрикам мониторинга СУБД — нет.»

Новая вводная- «У нас все запросы стали очень медленно выполняться».
К сожалению на период развития событий, способа получить объективную картину времени выполнения запросов — не было.

Метрика, позволяющее получать время отклика СУБД показывает «среднюю температуру по больнице». А учитывая, что система высоконагруженная, среднее время отклика не может являться надежной метрикой оценки производительности СУБД. Так, что от разнообразных вариаций на тему 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 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:

P.S.

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