Дэйв Пэйдж. Эффективный мониторинг PostgreSQL: Использование представлений pg_stat_all_tables и pg_stat_all_indexes в PostgreSQL 16

Это перевод статьи Dave Page. Effective PostgreSQL Monitoring: Utilizing the views pg_stat_all_tables and pg_stat_all_indexes in PostgreSQL 16

Содержание

Введение

Мониторинг и уведомления о различных аспектах базы данных PostgreSQL имеет ключевое значение для обеспечения оптимальной производительности и поиска узких мест. В этом блоге мы рассмотрим представления pg_stat_all_tables и pg_stat_all_indexes, которые используются для отслеживания использования индексов и статистики таблиц. Путём оперативного отслеживания этих показателей и принятия соответствующих шагов можно найти области оптимизации и поднять общую производительность базы данных PostgreSQL.

Понимание представлений pg_stat_all_tables и pg_stat_all_indexes

Системные представления PostgreSQL pg_stat_all_tables и pg_stat_all_indexes позволяют находить способы повышения производительности и информацию об использовании таблиц и индексов.

• pg_stat_all_tables: В этом представлении можно найти статистику обо всех таблицах в текущей базе данных, включая количество последовательных сканирований и сканированием по индексу, обновлений, удалений, вставок и т.п. В нём также есть информация о количестве актуальных и устаревших кортежей, статистика об очистке таблиц от устаревших записей и анализе таблиц.
• pg_stat_all_indexes: В этом представлении можно найти подробную статистику обо всех индексах в текущей базе данных. Оно содержит количество сканирований индекса, отметку времени последнего сканирования по индексу и количество строк, полученных с помощью индекса.

Вклад EDB в PostgreSQL 16

Я реализовал запись статистики о последнем последовательном санировании таблиц и сканировании по индексу.

1. В представление pg_stat_all_indexes добавлена отметка времени последнего сканирования по индексу (last_idx_scan)
2. В представление pg_stat_all_tables добавлена отметка времени последнего последовательного сканирования (last_seq_scan)

https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-MONITORING

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

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

Мониторинг использования индексов

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

	Столбец	Тип	Описание
1	relid	oid	OID индексируемой таблицы
2	indexrelid	oid	OID индекса
3	schemaname	name	Название схемы, в которой находится индекс
4	relname	name	Название индексируемой таблицы
5	indexrelname	name	Название индекса
6	idx_scan	bigint	Количество сканирований этого индекса
7	last_idx_scan	timestamp with time zone	Время завершения последней транзакции, в которой использовался этот индекс
8	idx_tup_read	bigint	Количество элементов индекса, возвращённых при сканировании этого индекса
9	idx_tup_fetch	bigint	Количество актуальных строк таблицы, прочитанных при простом сканировании этого индекса

Важно проверить следующие столбцы:

• Количество сканирований индекса (idx_scan): Столбец idx_scan в представлении pg_stat_all_indexes содержит количество сканирований таблицы с использованием индекса. С его помощью можно определить, какие индексы используются часто, какие - редко, а какие не используются вовсе.
• Время последнего сканирования индекса (last_idx_scan): Столбец last_idx_scan в pg_stat_all_indexes - это нововведение PostgreSQL 16. В нём содержится время последнего сканирования индекса. Учтите, что отметка времени соответствует времени окончания транзакции, а не в точности времени использования индекса (которое может значительно отличаться в случае длительных транзакций).
• idx_tup_read - это количество элементов индекса, возвращённых при сканировании этого индекса. Его значение увеличивается при каждом чтении индекса.
• idx_tup_fetch - это количество актуальных строк таблицы/кучи, полученных при сканировании индекса.

Продемонстрируем на следующем примере:

CREATE TABLE sales (
  id integer NOT NULL,
  email VARCHAR,
  location VARCHAR,
  item VARCHAR,
  price VARCHAR
);

INSERT INTO sales (
  id, email, location, item, price
)
VALUES
(
  1, 'a@hotmail.com', 'London', 'item1', '$50'
),
(
  2, 'b@hotmail.com', 'Australia', 'item2', '$5'
),
(
  3, 'c@hotmail.com', 'Canada', 'item3', '$12'
),
(
  4, 'd@hotmail.com', 'London', 'item4', '$2'
),
(
  5, 'e@hotmail.com', 'Switzerland', 'item5', '$150'
),
(
  6, 'f@hotmail.com', 'Canada', 'item6', '$49'
);

CREATE INDEX idx_sale_id ON sales(id);

Давайте проверим количество строк в представлении pg_stat_all_indexes:

postgres=# SELECT * FROM pg_stat_all_indexes WHERE indexrelname='idx_sale_id';
-[ RECORD 1 ]-+------------------------------
relid         | 24576
indexrelid    | 24581
schemaname    | public
relname       | sales
indexrelname  | idx_sale_id
idx_scan      | 0
last_idx_scan | 
idx_tup_read  | 0
idx_tup_fetch | 0

Теперь мы перейдём к запросу для выборки из таблицы sales, но перед этим отключим параметр конфигурации seqscan, чтобы вынудить планировщик использовать сканирование по индексу.

postgres=# SET enable_seqscan=off;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM sales WHERE id = 5;
QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using idx_sale_id_2 on sales  (cost=0.13..8.15 rows=1 width=132) (actual time=0.057..0.059 rows=1 loops=1)
   Index Cond: (id = 5)
 Planning Time: 0.076 ms
 Execution Time: 0.077 ms
(4 rows)

Теперь можно увидеть, что представление pg_stat_all_indexes обновилось и можно увидеть отметку времени последнего сканирования индекса:

postgres=# SELECT * FROM pg_stat_all_indexes WHERE indexrelname='idx_sale_id';
-[ RECORD 1 ]-+------------------------------
relid         | 24576
indexrelid    | 24581
schemaname    | public
relname       | sales
indexrelname  | idx_sale_id
idx_scan      | 1
last_idx_scan | 2023-07-13 07:15:49.886831-04
idx_tup_read  | 1
idx_tup_fetch | 1

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

SELECT
        idxstat.schemaname as schema_name,
        idxstat.relname AS table_name,
        indexrelname AS index_name,
        idxstat.idx_scan AS index_scans_count,
        idxstat.last_idx_scan AS last_idx_scan_timestamp,
        pg_size_pretty(pg_relation_size(idxstat.indexrelid)) AS index_size
FROM
        pg_stat_all_indexes AS idxstat
JOIN
        pg_index i ON idxstat.indexrelid = i.indexrelid
WHERE
        idxstat.schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
        AND NOT i.indisunique -- is not a UNIQUE index
ORDER BY
        idxstat.idx_scan ASC,
        idxstat.last_idx_scan ASC;

Этот запрос отображает список всех индексов, отсортированный по частоте сканирования (столбец 4) и времени последнего сканирования (столбец 5). Если частота сканирования некоторых индексов (index_scans_count) равна нулю, меньше 10 или значение last_idx_scan_timestamp очень старое, возможно будет разумным удалить их.

Причины избавиться от неиспользуемых индексов:

• Зачастую индексы баз данных занимают такой же или даже больший объём, чем сами данные. Кроме этого хранилище необходимой скорости для базы данных может быть довольно дорогим. Также индексы увеличивают размер физической резервной копии и длительность такого резервного копирования.
• Индексы замедляют операции DML, изменяющие данные. При каждом запуске команды INSERT или DELETE в таблице все индексы нужно изменить в соответствии с таблицей ("куча"). Обновление индексированной таблицы обычно обходится дороже, чем обновление неиндексированной.
• Обновления HOT не применяются при наличии индекса. Каждая операция UPDATE приводит к записи новой версии строки (также известной как "кортеж"), что приводит к созданию нового элемента в каждом индексе таблицы и не позволяет обновить данные на месте.

Кандидата на удаление нужно проверить следующим образом:

• Возможно планировщик избегает использовать индекс из-за дублирования или некорректной настройки таких параметров PostgreSQL, как effective_cache_size и random_page_cost, или может считать более выгодным последовательное сканирование таблицы по другим причинам. Решение нужно принимать, учитывая другие причины в пользу наличие (или отсутствие) других причин для его удаления.
• Сколько места занимает индекс? Возможно сейчас не стоит обращать на него внимание?
• Сколько раз происходило последовательное сканирование таблицы? Для предотвращения последовательных сканирований может потребоваться добавить больше столбцов в индекс.
• Может быть не нужно удалять таблицу или индекс из-за их неактивности. Может быть это таблица журнала аудита, которая используется редко, но нужна для согласованности, а индекс может использоваться только при итоговой обработке данных в конце года.

Мониторинг последовательных сканирований:

Последовательное сканирование означает, что PostgreSQL читает данные из таблицы последовательно без использования индекса. Если дело касается больших таблиц, последовательное сканирование неэффективно и может занять много времени на чтение всей таблицы. Если PostgreSQL выполняет одновременно несколько последовательных сканирований, падение производительности может быть чрезвычайно большим.

Мониторинг последовательное сканирования может способствовать обнаружению проблем производительности и подсказать возможные изменения индексов. Представление pg_stat_all_tables содержит перечисленные ниже столбцы:

	Столбец	Тип	Описание
1	relid	oid	OID таблицы
2	schemaname	name	Название схемы, в которой находится таблица
3	relname	name	Название таблицы
4	seq_scan	bigint	Количество последовательных сканирований этой таблицы
5	last_seq_scan	timestamp with time zone	Время завершения последней транзакции, в ходе которой произошло последовательное сканирование этой таблицы
6	seq_tup_read	bigint	Количество актуальных строк, прочитанных последовательным сканированием
7	idx_scan	bigint	Количество сканирований индекса в этой таблице
8	last_idx_scan	timestamp with time zone	Время завершения последней транзакции, в ходе которой произошло сканирование индекса этой таблицы
9	idx_tup_fetch	bigint	Количество актуальных строк, полученных индексом
10	n_tup_ins	bigint	Общее количество вставленных строк
11	n_tup_upd	bigint	Общее количество обновлённых строк (включает в себя количество обновлений строк из n_tup_hot_upd и n_tup_newpage_upd, и остальных не-HOT обновлений)
12	n_tup_del	bigint	Общее количество удалённых строк
13	n_tup_hot_upd	bigint	Количество HOT-обновлений строк. Эти обновления не требуют обновления связанных индексов
14	n_tup_newpage_upd	bigint	Количество обновлённых строк, попавших в новую страницу кучи, когда у исходной версии строки поле t_ctid указывает на другую страницу кучи. Это всегда не-HOT обновления
15	n_live_tup	bigint	Оценочное количество актуальных строк
16	n_dead_tup	bigint	Оценочное количество неактуальных строк
17	n_mod_since_analyze	bigint	Оценочное количество строк, изменённых в таблице с момента последнего анализа таблицы
18	n_ins_since_vacuum	bigint	Оценочное количество строк, вставленных в таблицу с последней очистки таблицы от неактуальных строк
19	last_vacuum	timestamp with time zone	Время последней ручной очистки таблицы от неактуальных строк (не считая VACUUM FULL)
20	last_autovacuum	timestamp with time zone	Время последней автоматической очистки таблицы
21	last_analyze	timestamp with time zone	Время последнего ручного анализа таблицы
22	last_autoanalyze	timestamp with time zone	Время последнего автоматического анализа таблицы
23	vacuum_count	bigint	Сколько раз таблица была очищена от неактуальных строк вручную (не считая VACUUM FULL)
24	autovacuum_count	bigint	Сколько раз таблица была очищена от неактуальных строк автоматически
25	analyze_count	bigint	Сколько раз таблица была проанализирована вручную
26	autoanalyze_count	bigint	Сколько раз таблица была проанализирована автоматически

Важно проверить следующие столбцы:

• Количество последовательных сканирований (seq_scan): Столбец seq_scan в представлении pg_stat_all_tables содержит общее количество последовательных сканирований определённой таблицы. Большое количество последовательных сканирований указывает, что подходящий индекс отсутствует или используется не часто.
• Время последнего последовательного сканирования (last_seq_scan): Столбец last_seq_scan в представлении pg_stat_all_tables - нововведение PostgreSQL 16. В нём содержится время последнего последовательного сканирования таблицы. Этот столбец обновляется значением времени завершения транзакции (GetCurrentTransactionStopTimestamp()).
• Время последнего сканирования индекса (last_idx_scan): Столбец last_idx_scan в представлении pg_stat_all_tables совпадает по смыслу с одноимённым столбцом в представлении pg_stat_all_indexes, но не содержит информации о том, какой индекс использовался при последнем сканировании.
• Фрагментированные строки (n_dead_tup): Показывает примерное количество неактуальных строк, данные которых были удалены и которые можно использовать повторно при выполнении команд DELETE или UPDATE.
• Количество HOT-обновлений (n_tup_hot_upd): Можно посчитать коэффициент HOT-обновлений для наиболее обновляемых таблиц с помощью данных из этих столбцов. Столбцы n_tup_upd и n_tup_hot_upd в этом представлении содержат количество обычных и HOT-обновлений для определённой таблицы. Таблицы с высоким коэффициентом HOT-обновлений - "хорошие". Стоит обращать внимания на интенсивно обновляемые таблицы с низким или нулевым коэффициентом HOT-обновлений. Обычно у таких таблиц стоит настроить коэффициент заполнения fillfactor, чтобы зарезервировать больше места для добавления новых строк или увеличения таблицы. Наличие зарезервированного места позволяет повысить вероятность обновления строки внутри страницы с помощью HOT-обновления. Подробности о HOT-обновлениях можно найти по ссылке: https://www.postgresql.org/docs/16/storage-hot.html

Есть и другие полезные столбцы, такие как время последней ручной и автоматической очистки от неактуальных строк (last_vacuum и last_autovacuum), время последнего ручного анализа и автоматического анализа (last_analyze и last_autoanalyze) и их количество.

Чтобы найти в PostgreSQL старые таблицы, можно воспользоваться следующим запросом:

SELECT
        tabstat.schemaname AS schema_name,
        tabstat.relname AS table_name,
        tabstat.seq_scan AS tab_seq_scan_count,
        tabstat.idx_scan AS tab_index_scan_count,
        tabstat.last_seq_scan AS tab_last_seq_scan_timestamp,
        tabstat.last_idx_scan AS tab_last_idx_scan_timestamp,
        pg_size_pretty(pg_total_relation_size(tabstat.relid)) AS table_size
FROM
        pg_stat_all_tables AS tabstat
WHERE
        tabstat.schemaname not in ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY
        tabstat.last_seq_scan ASC,
        tabstat.last_idx_scan ASC;

Этот запрос показывает список всех таблиц, отсортированный по времени последнего последовательного сканирования (5 столбец) и времени последнего сканирования по индексу (столбец 6). Выражение ORDER также можно заменить на столбцы seq_scan и idx_scan, чтобы получить список редко используемых таблиц.

Эффективный мониторинг использования индексов и последовательных сканирований

Для эффективного мониторинга использования индексов и последовательных сканирований стоит придерживаться следующих рекомендаций:

a. Регулярно анализируйте статистику

Регулярно анализируйте представления pg_stat_all_tables и pg_stat_all_indexes. Наблюдайте за тенденциями, аномалиями и возможными улучшениями.

b. Настройте автоматический мониторинг

Используйте скрипты или инструменты мониторинга для автоматизации сбора и анализа статистики. Они должны непрерывно сканировать базу данных и отправлять уведомления при выходе за пределы или при обнаружении тенденций.

c. Сравнивайте исторические данные

Сохраняйте статистику о последовательных сканированиях и использованиях индексов для сравнения и поиска повторяющихся шаблонов. Так можно обнаружить тенденции и оценить влияние изменений на базу данных.

d. Оптимизируйте использование индекса

Используйте столбцы idx_scan и last_idx_scan в представлении pg_stat_all_indexes для обнаружения редко используемых индексов. Чтобы снизить стоимость обслуживания и увеличить производительность запросов вставки и обновления данных подумайте о том, чтобы удалить или изменить эти индексы.

e. Оптимизируйте последовательные сканирования

С помощью столбцов seq_scan и last_seq_scan в представлении pg_stat_all_tables найдите таблицы с большим количеством последовательных сканирований. Оцените необходимость в дополнительных индексах. Также можно удалить или заархивировать таблицы, которые не использовались по данным мониторинга столбцов last_seq_scan и last_idx_scan. Так можно снизить использование места на диске и, как следствие, ускорить резервное копирование.

f. Анализируйте производительность запросов

Анализируйте долгие запросы с помощью команд PostgreSQL EXPLAIN и EXPLAIN ANALYZE. Обратите внимание на планы выполнения, использование индексов и последовательных сканирований, указанные в выводе. Обнаружив запросы с низким использованием индексов или использующие дорогое последовательное сканирование, можно найти области, требующие оптимизации.

Заключение

Для достижения максимальной производительности базы данных PostgreSQL важен мониторинг использования индексов и последовательных сканирований. С помощью представлений pg_stat_all_tables и pg_stat_all_indexes можно много узнать о таблице и использовании индексов. Обнаружив редко используемые индексы, отслеживая соотношение сканирований по индексу и последовательных сканирований, можно оптимизировать производительность запросов. Так можно убедиться, что база данных PostgreSQL работает эффективно и предоставляет наилучшую производительность для приложения. Чтобы база данных продолжала эффективно работать, постоянно отслеживайте и настраивайте индексы в соответствии с результатами мониторинга. Удачных наблюдений!

Использованные материалы

• https://www.postgresql.org/message-id/CA%2BOCxozrVHNFVEPkweUHMZje%2Bt1tfY816d9MZYc6eZwOOusOaQ%40mail.gmail.com
• https://www.postgresql.org/docs/16/monitoring-stats.html