Построение гистограммы максимального и среднего времени выполнения запросов для PostgreSQL06.04.2024 12:15

gf_rgf5h5empre3nw0kpm35ydzu.jpeg

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

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

SQL — запрос

Для построения гистограммы используется стандартная функция width_bucket

width_bucket ( operand numeric, low numeric, high numeric, count integer ) → integer

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 <= 12
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: 1jv-xiql8u3m11dfczyvd7ldxxe.jpeg

P.S.

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

© Habrahabr.ru