Real-time мониторинг тормозящих запросов PostgreSQL своими руками за полчаса

[ololx]

Спасибо за статью, как раз прикручиваю графики для postgres (не через Prometheus) в Graphana. Будет очень полезно.

Сразу вопрос: если уже настроена трассировка через тот же Jaeger, то чем этот способ лучше? В Jaeger же можно видеть запросы и их время выполнения.

[varanio]

Задачи можно решать разными способами. Jaeger вы прикрутили к приложению?

[varanio]

Кстати, советую посмотреть на https://github.com/cybertec-postgresql/pgwatch2

Там дофига дашбордов графаны с самыми разными метриками

[ololx]

Благодарю, гляну, пока подключил postgres как datasource и создавал свои графики на основании служебных таблиц.

[ololx]

Да, при помощи open tracing, настроил отправку данных трассировки на развернутый сервис Jaeger. А сам Jaeger подключил как datasource в Graphana; по мимо Jaeger просто собираем статы с prometheus и loki.

Пока разбирался, сделал небольшой пет проект на эту тему для себя еще; если интересно, то он доступен тут - https://github.com/innopolis-university-java-team/spring-boot-metrics-instances

[Aytuar]

Графики конечно красивые. Но сколько это стоит по ресурсам на самом сервере БД, overhead какой?

[varanio]

Оверхед около нуля. Само расширение посгреса хранит числа в сишной структуре, инкрементирует их, всё оч быстро.

Еще запрос к нему раз в несколько секунд - около нуля времени занимает тоже

Короче, в большинстве случаев можно пренебречь

[michailnikolaev]

Я использую вот такую кверю (она считает разницу прямо в запросе с 20-секундой паузой).

Кроме того, она схлопывает запросы с различным количеством параметров вместе посредством регулярки.

SELECT
   round(sum(total_time))                                          AS total,
   round(sum(calls))                                               AS calls,
   round(sum(total_time) / sum(calls))                             AS per_query,
   regexp_replace(query, '(\$[0-9]+\s*,?\s*)+', '$$ ', 'g') AS query_group
FROM (WITH
    p AS (
      SELECT 20.0
  ),
    t AS (
      SELECT sum(calls) as calls, queryid, max(query) as query, sum(total_time) as total_time FROM pg_stat_statements      
      group by queryid
  ),
  s AS (
      SELECT * FROM pg_sleep((SELECT * FROM p limit 1) + (SELECT count(*) FROM t) * 0.0001)
  ),
    r AS (
      SELECT sum(calls) as calls, queryid, max(query) as query, sum(total_time) as total_time
      FROM s,
           pg_stat_statements
      group by queryid
  )
SELECT coalesce(r.queryid, t.queryid)                             AS queryid,
       coalesce(r.query, t.query)                                 AS query,
       abs(coalesce(r.calls, 0) - coalesce(t.calls, 0))           AS calls,
       abs(coalesce(r.total_time, 0) - coalesce(t.total_time, 0)) AS total_time
FROM t inner
       JOIN r ON (t.queryid = r.queryid)
WHERE coalesce(r.calls, 0) - coalesce(t.calls, 0) != 0)  
         AS s
GROUP BY regexp_replace(query, '(\$[0-9]+\s*,?\s*)+', '$$ ', 'g')
ORDER BY sum(total_time) DESC