Анализ сборки мусора

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

Read more »

Не оптимальная запись

Если необходимо локализовать проблемы связанные с не оптимальной записью, а это очень важные проблемы, т.к. предел возможностей БД в основном упирается в дисковые операции, и начать решать их с более тяжёлой, то этот запрос поможет вам. Он выводит статистику по таблицам в обратном порядке по сумме операций записи. Сверху будут таблицы с наиболее интенсивной записью. В условиях осуществляется фильтрация по схемам, чьи таблицы необходимо исключить из статистики.

Т.о. выявляются счётчики, лишние или временные вставки и т.п. Далее, интенсивность записи счётчиков можно снизить за счёт, например, накопления их в памяти (допустим в memcached) по 10 штук и одного сброса на диск. Где-то можно просто отказаться от некоторых операций, а где-то выявится необходимость рефакторинга приложения.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
seq_scan + idx_scan AS re, -- всего чтений
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
WHERE schemaname NOT IN ('pgq')
ORDER BY
n_tup_upd + n_tup_ins + n_tup_del DESC
LIMIT 40;

Не оптимальное чтение

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

Также удобно бывает использовать ограничение по размеру (в примере ниже оно закомментировано). Оно помогает отфильтровывать таблицы, по которым последовательные чтения потенциально выгодны. Тут необходимо смотреть на величину, и вообще на факт, необходимости использования по ситуации, проведя несколько экспериментов.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
seq_scan AS ss, -- последовательных чтений
idx_scan AS is, -- индексных чтений
seq_scan + idx_scan AS re, -- всего чтений
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
-- WHERE pg_relation_size(relid) > 1 * 1024 * 1024 -- ограничение по размеру
ORDER BY
length(seq_scan::text) DESC,
pg_relation_size(relid) DESC
LIMIT 40;

Важность очистки статистики

Это маленький но очень важный момент. Если ваш проект интенсивно развивается, то накопленная за долгий период времени внутренняя статистика PostgreSQL зачастую теряет смысл. Так, например, добавление нового индекса или изменение пары запросов меняет темпы накопления и новая ситуация начинает накладываться на старую, из-за чего можно сделать не правильные выводы. Для того чтобы избежать этого неообходимо очищать старые статистические данные перед запросом статистики, если с момента предыдущей очистки были какие-то изменения влияющие на её сбор.

В PostgreSQL для этого существует специальная функция:

SELECT pg_stat_reset();

Заметьте, после очистки возможно потребуется некоторое время для накопления новой статистики.