PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL

Содержание скрыть

1 Список литературы

2 Регулярное обслуживание

2.1 Устранение проблем с раздуванием

2.2 Обслуживание индексов

2.3 Индексы

2.4 Осторожное удаление индексов

3 Другие связанные статьи

В моем блоге уже был цикл статей с конспектом по MySQL. Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто мои заметки – конспектировал что-то новое и то, что я посчитал интересным и важным. Эта публикация посвящена тому, как выполняется регулярное обслуживание PostgreSQL.

Список литературы

При подготовке этого конспекта я использовал следующие материалы:

1. Книга PostgreSQL 14 Administration Cookbook.

2. Курс DBA1: Администрирование PostgreSQL 13. Базовый курс.

Регулярное обслуживание

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

Убедитесь, что функции самоочитски включены. Нужно проверить – включен ли autovacuum в конфигурационном файле postgresql.conf:

sudo nano /etc/postgresql/12/main/postgresql.conf

autovacuum = on
track_counts = on

По умолчанию автоочистка включена.

Максимально количество процессов самоочистки работающих параллельно задается параметром autovacuum_max_workers в конфигурационном файле postgresql.conf. Каждый из этих процессов генерирует определенную нагрузку. Вся нагрузка всех процессов самоочитски суммируется и не может быть больше значения параметра autovacuum_vacuum_cost_limit в конфигурационном файле postgresql.conf. Это необходимо для того, чтобы процессы самоочитски не использовали абсолютно все ресурсы сервера. Увеличение значение параметра autovacuum_vacuum_cost_delay замедлит процесс самоочитски и немного высвободит ресурсы сервера. Однако, этого не рекомендуется делать.

Пример конфигурации более агрессивной автоматической очистки. Параметры в конфигурационном файле postgresql.conf:

autovacuum = on
autovacuum_max_workers = 3
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_scale_factor = 0.1

Пример конфигурации менее агрессивной автоматической очистки. Параметры в конфигурационном файле postgresql.conf:

autovacuum = on
autovacuum_max_workers = 3
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 5
autovacuum_vacuum_scale_factor = 0.2

Устранение проблем с раздуванием

Увеличение размеров таблицы, индекса или базы данных в большинстве случае вызвано долгим выполнением какого-либо запроса или долгой транзакцией.

Самый первый шаг в диагностике – посмотреть самый старый снимок данных:

SELECT now() -
CASE
WHEN backend_xid IS NOT NULL
THEN xact_start
ELSE query_start END
AS age
, pid
, backend_xid AS xid
, backend_xmin AS xmin
, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY 1 DESC;

Если какие-то сессии находятся в состоянии “idle_in_transaction”, то есть смысл установить значение параметра “idle_in_transaction_session_timeout”, например, в минуту или пару минут (зависит от профиля нагрузки на сервер и приложение, которое использует БД). Штатное значение этого параметра равно нулю, т.е. сессии или транзакции никогда не завершаются и потенциально могут выполняться день, два или более.

Если использование параметра “idle_in_transaction_session_timeout”не представляется возможным, то можно рассмотреть параметр “old_snapshot_threshold”. Он определяет время, после которого неиспользуемые строки могут быть удалены. Если какой-то запрос попытается прочитать такие строки, то он будет отменен. Для всех других запросов, время выполнения которых меньше значения параметра “old_snapshot_threshold ” никаких негативных эффектов не будет.

Обслуживание индексов

Индексы также могут потребовать периодического обслуживания.

Например, можно переиндексировать одну конкретную базу данных:

reindexdb

Переиндексировать все базы данных:

reindexdb -a

Лучше всего индексировать отдельные таблицы, т.к. устанавливается AccessExclusiveLock в процессе перестроения индекса..

Можно запустить более “мягкое” перестроение индекса, но оно может завершиться с ошибкой. Пример запроса:

REINDEX INDEX CONCURRENTLY test_category_idx;

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

sudo -u postgres reindexdb --dbname app --concurrently -t postgres.t --verbose

Индексы

Запрос для определения частоты использования индексов:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan;

Осторожное удаление индексов

Не всегда безопасно удалять индекс сразу. Можно на время его “отключить”.

Запрос для отображения отключенных индексов:

SELECT ir.relname AS indexname
, it.relname AS tablename
, n.nspname AS schemaname
FROM pg_index i
JOIN pg_class ir ON ir.oid = i.indexrelid
JOIN pg_class it ON it.oid = i.indrelid
JOIN pg_namespace n ON n.oid = it.relnamespace
WHERE NOT i.indisvalid;

Процедура:

1. Создать функцию ля отключения индекса:

CREATE OR REPLACE FUNCTION trial_drop_index(iname
TEXT) RETURNS VOID
LANGUAGE SQL AS $$ UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = $1::regclass;
$$;

2. Применить её к потенциально неиспользуемому индексу.

select trial_drop_index('idx_t_s');

3. Еще раз проверит список отключенных индексов:

SELECT ir.relname AS indexname
, it.relname AS tablename
, n.nspname AS schemaname
FROM pg_index i
JOIN pg_class ir ON ir.oid = i.indexrelid
JOIN pg_class it ON it.oid = i.indrelid
JOIN pg_namespace n ON n.oid = it.relnamespace
WHERE NOT i.indisvalid;

4. Функция для отмены удаления:

CREATE OR REPLACE FUNCTION trial_undrop_index(iname
TEXT) RETURNS VOID
LANGUAGE SQL AS
$$ UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = $1::regclass;
$$;