SQL-Ex blog

Введение

Существует много типов запросов и, конечно, все они важны, но, когда дело касается мониторинга и отладки, мы обычно торопимся и не помним, какой запрос написать для проверки конкретной проблемы. Здесь мы вкратце обсудим некоторые запросы и их использование, особенно что касается мониторинга и отладки проблем, связанных с PostrgeSQL.

В случае мониторинга и отладки мы обычно имеем проблемы, относящиеся к размеру базы данных и к производительности. Иногда эти проблемы взаимосвязаны.

Проблемы, связанные с размером

Вот некоторые основные команды/запросы в PostgreSQL, которые будут весьма полезны для мониторинга и отладки проблем, связанных с размерами:

• Проверка размера базы данных: Нам часто приходится проверять, какая база данных является виновницей проблемы, т.е. какая база данных потребляет основное пространство. Следующий запрос проверяет размер базы данных в убывающем порядке потребляемого объема.
  
  

  SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database ORDER BY pg_database_size(pg_database.datname) desc;



• После этого нам может потребоваться определить таблицу/индекс, которая/который занимает наибольшее пространство; для этого у нас есть следующий запрос:
  
  

  SELECT
  
    nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  
  FROM pg_class C
  
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  
  ORDER BY pg_relation_size(C.oid) DESC
  
  LIMIT 20;

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

SELECT schemaname,relname,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC limit 10;

Пример вывода:

schemaname | relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum

public | campaign_session_denormalized_data | 1123219 | 114268349 | 2021–01–10 18:27:34.050087+05:30 | 2021–01–19 14:08:58.062574+05:30

Вывод выше позволяет нам узнать также, был или нет правильно выполнен auto vacuum. Когда последний раз запускался auto vacuum на любой конкретной таблице, где высокое число мертвых кортежей.

Время от времени, благодаря MVCC, ваша таблица может увеличиться в размерах (называемое раздуванием таблицы) - именно поэтому необходим регулярный VACUUM. Этот запрос покажет вам список таблиц и индексов с наибольшим раздуванием. Значение представляет число "потраченных впустую байтов", или разницу между тем, что используется на текущий момент таблицей или индексом, и что должно быть по расчетам.

Это работает так: оценивается оптимальный размер таблицы/индекса путем вычисления размеров каждой строки, умноженного на общее число строк, и сравнением с фактическим размером таблицы. Заметьте, что это оценка, а не точное значение.

with foo as (

  SELECT

    schemaname, tablename, hdr, ma, bs,

    SUM((1-null_frac)*avg_width) AS datawidth,

    MAX(null_frac) AS maxfracsum,

    hdr+(

      SELECT 1+COUNT(*)/8

      FROM pg_stats s2

      WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename

    ) AS nullhdr

  FROM pg_stats s, (

    SELECT

      (SELECT current_setting('block_size')::NUMERIC) AS bs,

      CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,

      CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma

    FROM (SELECT version() AS v) AS foo

  ) AS constants

  GROUP BY 1,2,3,4,5  

), rs as (

  SELECT

    ma,bs,schemaname,tablename,

    (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,

    (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2

  FROM foo  

), sml as (

  SELECT

    schemaname, tablename, cc.reltuples, cc.relpages, bs,

    CEIL((cc.reltuples*((datahdr+ma-

      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,

    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,

    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- очень грубая аппроксимация, предполагаются все столбцы

  FROM rs

  JOIN pg_class cc ON cc.relname = rs.tablename

  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'

  LEFT JOIN pg_index i ON indrelid = cc.oid

  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid

)

SELECT

  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/

  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,

  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,

  iname, /*ituples::bigint, ipages::bigint, iotta,*/

  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,

  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes

FROM sml

ORDER BY wastedbytes DESC

Пример вывода

current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes

dashboard | public | job_logs | 1.1 | 4139507712 | job_logs_pkey | 0.2 | 0

dashboard | public | job_logs | 1.1 | 4139507712 | index_job_logs_on_job_id_and_created_at | 0.4 | 0

dashboard | public | events | 1.1 | 3571736576 | events_pkey | 0.1 | 0

dashboard | public | events | 1.1 | 3571736576 | index_events_on_tenant_id | 0.1 | 0

dashboard | public | events | 1.1 | 3571736576 | index_events_on_event_type | 0.2 | 0

dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_status | 0.0 | 0

dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_tag | 0.3 | 0

dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_tenant_id | 0.2 | 0

dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_created_at | 0.2 | 0

dashboard | public | jobs | 1.1 | 2013282304 | index_jobs_on_created_at_queued_or_running | 0.0 | 21086208

Следует смотреть на:

• tbloat: раздувание таблицы, отношение между текущим и оптимизированным значениями.


• wastedbytes: число растраченных байтов.


• ibloat & wastedibytes: то же самое, что и выше, только для индексов.

Когда вы видите таблицу с высоким значением раздутия, то рассмотрите возможность выполнения VACUUM ANALYZE для неё.

Проблемы, связанные с производительностью

Для мониторинга производительности нам потребуется выполнить следующие запросы, хотя в повседневной практике этого может быть слишком много.

• Получить выполняющиеся запросы (и состояние блокировок) в PostgreSQL
  

  SELECT S.pid, age(clock_timestamp(), query_start),usename,query,L.mode,L.locktype,L.granted,s.datname FROM pg_stat_activity S inner join pg_locks L on S.pid = L.pid order by L.granted, L.pid DESC;

  
  Этот запрос может помочь, если некоторый запрос выполняется часами и накладывает блокировку, из-за чего другие запросы блокируются. Выводится также имя базы данных, на которой выполняется запрос. Мы также можем определить, когда некоторое подключение является открытым, но ни один запрос не выполняется, поэтому в вашей базе данных может быть утечка сеанса.


• Чтобы убить конкретный запрос, который вызывает проблему, используйте такой скрипт:
  
  

  SELECT pg_cancel_backend(pid);

  
  Это пошлет SIGINT текущему процессу.