diagnostic.sql

-- https://habrahabr.ru/post/13992/
-- Отношение hit / read
-- При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет — делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.


    SELECT
      datname,
      blks_hit,
      blks_read,
      CASE
        WHEN blks_read = 0 THEN 0
        ELSE blks_hit / blks_read
      END AS ratio
    FROM
      pg_stat_database;

    SELECT
      relname,
      n_tup_ins,
      n_tup_upd,
      n_tup_del
    FROM
      pg_stat_user_tables
    ORDER BY
      n_tup_upd DESC;


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

    SELECT
      relname,
      n_tup_ins,
      n_tup_upd,
      n_tup_del
    FROM
      pg_stat_user_tables
    ORDER BY
      n_tup_upd DESC;


-- Статистика seq scan / index scan


-- Список по таблицам: какое количество запросов к ним было выполнено посредством последовательного просмотра; какое количество запросов было выполнено с использованием индексов; а также отношение этих двух чисел. Позволяет оценить, все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса, поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться: чем больше индексов по колонкам таблицы, тем дороже становятся операции обновления данных.

-- Также не забывайте, что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.

    SELECT
      relname,
      seq_scan,
      idx_scan,
      CASE
        WHEN idx_scan = 0 THEN 100
        ELSE seq_scan / idx_scan
      END AS ratio
    FROM
      pg_stat_user_tables
    ORDER BY
      ratio DESC;




-- Статистика по индексам
-- Список по индексам: сколько записей из индекса были возвращены в запросах по этому индексу; сколько рядов при этом все же пришлось просматривать в родительской таблице; отношение этих двух чисел. Суть данной статистики проста: если у вас много fetch-ей, значит индекс устарел и при выполнении запроса PostgreSQL вынужден смотреть непосредственно в таблицу, как в источник самых актуальных данных, что замедляет его работу. В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.

    SELECT
      indexrelname,
      idx_tup_read,
      idx_tup_fetch,
      CASE
        WHEN idx_tup_fetch = 0 THEN 100
        ELSE idx_tup_read / idx_tup_fetch
      END AS ratio
    FROM
      pg_stat_user_indexes
    ORDER BY
      ratio DESC;



-- Выполняющиеся запросы с их продолжительностью
-- Простой список выполняющихся сервером в настоящий момент запросов. Бывает полезен, когда вы недостаточно хорошо знаете систему или просто не успели ее настроить — с его помощью можно найти и прервать «плохой» запрос, который мешает работе сервера (колонка procpid содержит PID процесса, которому можно сделать kill при необходимости). Помните, однако, что простой периодический просмотр выполняющхся запросов ни в коем случае не заменит замечательный анализатор логов pgFouine. Также не стоит забывать, что процесс, в котором вы выполняете данный запрос, тоже попадает в результирующий список.

    SELECT
      datname,
      NOW() - query_start AS duration,
      procpid,
      current_query
    FROM
      pg_stat_activity
    ORDER BY duration DESC;



-- Список текущих блокировок


-- Список текущих блокировок с указанием типа блокировки, таблицы и базы данных, на которой она выставлена и номера транзакции, которая выставила блокировку. Не стоит пугаться, если запрос выдает длинный список lock-ов — не все они являются критическими и блокирующими таблицу от всех возможных изменений и даже чтения. Для анализа списка блокировок обязательно стоит ознакомиться с документацией об их типах в PostgreSQL и о том, когда и какими запросами они выставляются. Один из распространенных случаев, когда список блокировок может пригодиться: выполняя команду ps aux | grep ^postgres вы видите, что в статусе одного из процессов postgres-а написано waiting — это как раз и означает, что данный процесс ждет, когда будет снята нужная ему блокировка. Какая именно — выясняйте, запустив данный запрос.


    SELECT
      l.mode,
      d.datname,
      c.relname,
      l.granted,
      l.transactionid
    FROM
      pg_locks AS l
    LEFT JOIN pg_database AS d ON l.database= d.oid
    LEFT JOIN pg_class AS c ON l.relation = c.oid;