rinace Apr 6 at 12:04

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

Easy

3 min

3.7K

PostgreSQL*

Tutorial

Предыстория

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

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

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

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

Tags:

Hubs:

PostgreSQL