Pull to refresh

Comments 15

Pinned Pinned comments
UFO just landed and posted this here

>> запрос находит индексы, которые созданы, но не использовались в SQL-запросах.

За какой период? :) А то вдруг этот индекс чтоб раз в месяц запрос для бухгалтерии для отчёта делать, а тут дропнули и создали проблем на голову в конце квартала.

Обычно такая статистика ведется с момента старта инстанса постгрес.

И как верно отметили ниже - в бд не должно быть индексов, использующихся раз в квартал. Если таблица небольшая, то просто пусть будет фул скан. Если таблица такая огромная, что фул скан уходит в себя навечно, и нет подходящего частичного индекса - у вас, вероятно, проблемы с дизайном бд. Может нужно думать о секционировании и/или отгрузке данных в специализированные бд для быстрого построения отчетности.

Статистика накапливается с момента её соседнего сброса. Смотрите pg_stat_reset и аналогичные ей функции.

собсно я это и хотел бы видеть в статье, а не в комменте)

UFO just landed and posted this here

Делать индекс, который обслуживается 24/7, а используется раз в месяц/квартал не самая лучшая идея.

Либо просто запустить pgwatch2 Postgres.ai Edition - https://gitlab.com/postgres-ai/pgwatch2 который уже содержит множество "полезных SQL-запросов" и получить графики состояния базы данных (в Grafana) за неделю, месяц, и год (при необходимости).

Demo: https://pgwatch.postgres.ai

demo/demo

а можно детальнее о причинах форка? посмотрел историю коммитов (очень бегло), похоже патчи из апстрима вы не подтягиваете

у проекта pgwatch2 свое видение дашбордов, у меня другое. Переделывал лично для себя, и уже несколько лет развивается параллельно, по части метрик и дашбордов, сам код сборщика не менял.

Посмотрел предложенный вами вариант. В целом достаточно интересно, но я бы вместо "либо" написал бы "можно ещё". Статистика приведенная в графане охватывает не только пересекающиеся темы, но и то, что не описано у меня в статье. Но все же, во-первых там нет половины рассматриваемых запросов из моей статьи, а во вторых там собрана общая статистика, а мои запросы приводят информацию по отдельным элементам базы данных.

UFO just landed and posted this here

Буквально на днях пользовался таким запросом для отладки блокировок. Он покажет чуть больше инфы, чем приведенный вами в статье, например имя пользователя и его ip, если есть разграничение доступа в вашей БД, время старта запроса и тело запроса, что поможет однозначно выявить неполадку.

select
  sa.usename as username,
  sa.client_addr,
  sa.backend_start,
  sa.query_start,
  sa.wait_event_type,
  sa.state,
  sa.query,
  lock.locktype,
  lock.relation::regclass as rel,
  lock.mode,
  lock.transactionid as tid,
  lock.virtualtransaction as vtid,
  lock.pid,
  lock.granted
from pg_catalog.pg_locks lock
  left join pg_catalog.pg_database db
    on db.oid = lock.database
  left join pg_catalog.pg_stat_activity sa
    on lock.pid = sa.pid
where not lock.pid = pg_backend_pid()
order by lock.pid;

Также пользуюсь вот таким дашбордом с postgres exporter.

Дополнение к размерам:

  1. Размер таблиц и индексов, единым плоским списком по всей СУБД (чтобы понять что кушает больше всего)

SELECT concat(schemaname, '.', tablename, ':', indexname),
       pg_relation_size(concat(schemaname, '.', indexname)::regclass) as size,
       pg_size_pretty(pg_relation_size(concat(schemaname, '.', indexname)::regclass)) AS pretty_size
FROM pg_indexes
UNION
SELECT concat(schemaname, '.', relname),
       pg_table_size(relid) as size,
       pg_size_pretty(pg_table_size(relid)) AS pretty_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY 2 DESC
  1. Размер одной таблицы суммарный и в пересчёте на одну строку (удобно для прикинуть сколько занимает одна запись на диске)

SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   partners t                     -- заменить здесь на имя таблицы, которую нужно проанализировать
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'tuples'                      , (SELECT reltuples::int FROM pg_class WHERE pg_class.oid = x.tbl))
   , (false, 'pages'                      , (SELECT relpages::int FROM pg_class WHERE pg_class.oid = x.tbl))
   ) l(is_size, metric, nr);

Дополнение к статистике:

  1. Получение статистики выполнявшихся запросов - по суммарно потраченному СУБД времени на все запросы / по потраченному на выполнение одного запроса времени.

Для Postgres 13 и выше нужно заменить total_time на total_exec_time.

SELECT round(total_time::numeric, 2) AS total_time,
       calls,
       ROWS,
       round(total_time::numeric / calls, 2) AS avg_time,
       round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu,
       query
FROM pg_stat_statements
ORDER BY total_time DESC -- для сортировки по суммарному времени выполнению всех копий запроса
-- ORDER BY avg_time DESC -- для сортировки по времени выполнения одного запроса
LIMIT 20;  
Спасибо, а по ораклу такого же нет, часом? :)

Есть ещё по mysql и oracle, со старого проекта записи остались. Оформлю в статью чуть позже)

Sign up to leave a comment.

Articles