pgcompacttable. Реорганизации данных в таблицах и перестройка индексов#

Версия: 1.0.10.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: модуль pgcompacttable написан на языке Perl, поэтому для работы требует библиотеки Perl DBI с модулем поддержки PostgreSQL:

• perl-Time-HiRes;

• perl-DBI;

• perl-DBD-Pg;

• модуль pgstattuple.

Схема размещения: не используется.

Утилита pgcompacttable - это инструмент для уменьшения размера «раздутых» таблиц и индексов без тяжелых блокировок.

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

Модуль может быть запущен от имени любого пользователя ОС, в том числе на другом хосте (вариант с ключом --host).

Фрагментация: pg_repack и pgcompacttable#

В процессе работы с Pangolin возникает table bloat — ситуация, при которой данные таблиц будут храниться неэффективно. Они фрагментируются, что приводит к ухудшению производительности и нерациональному использованию места на диске.

Основные причины фрагментации:

• непредвиденный скачок запросов UPDATE или DELETE;

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

• незавершенные PREPARED транзакции;

• открытые слоты репликации из-за сильной задержки между Active и Standby или в случае недоступности Standby;

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

Расширение pg_repack#

Устройство расширения#

Функциональность:

• удаление пустот в таблицах и индексах;

• восстановление физического порядка кластеризованных индексов.

Особенности функциональности:

• в отличие от стандартных средств PostgreSQL, VACUUM FULL и CLUSTER, реорганизация таблиц происходит без блокировки операций над обрабатываемыми данными;

• производительность сравнима с CLUSTER;

• для работы pg\_repack требуются права superuser;

• требования к реорганизуемым таблицам: наличие primary key или хотя бы одного уникального поля;

• во время работы утилиты с обрабатываемой таблицей можно производить любые манипуляции, кроме DDL.

Алгоритм работы:

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

2. В оригинальной таблице создаются триггеры для операций Insert, Update и Delete. Триггеры записывают действия в созданную таблицу для логирования.

3. Создается новая таблица, копия оригинальной таблицы.

4. Копируются индексы из оригинальной таблицы в новую.

5. К новой таблице применяются все операции из таблицы логирования (см. пункт 1).

6. Выполняется swap оригинальной и новой таблицы.

7. Удаляется оригинальная таблица.

На шагах 1, 2, 6 и 7 для оригинальной таблицы применяется короткая Access Exclusive блокировка. На остальных - Access Share блокировка.

Недостатки pg_repack:

• отсутствие ограничений на работу с диском и возможности распределить нагрузку. Приводит к значительной нагрузке на диск и падению производительности при работе с большими таблицами;

• во время работы переполняется WAL. Это приводит к задержке между Active и Standby и может стать причиной отказа экземпляра PostgreSQL. При обработке больших таблиц рекомендуется отключить слот репликации на время работы утилиты;

• для реорганизации требуется дополнительное место на диске, равное сумме оригинальной и содержащей логи таблиц и размеру индексов.

Инструмент pgcompacttable#

Устройство расширения#

Скрипт реорганизации данных в «раздутых» таблицах (bloated tables), восстановления индексов и возврата дискового пространства.

Преимущества:

• не влияет на производительность базы данных;

• не использует Access Exclusive блокировки.

Отличия от pg_repack:

• не требует много места на диске:

  • таблицы обрабатываются «на месте»;

  • индексы перестраиваются друг за другом, от меньшего к большему;

  • максимальное требуемое место на диске равно размеру наибольшего индекса.

• таблицы обрабатываются с настраиваемыми задержками для предотвращения перегрузки IO и всплесков задержки репликации (см. ключ --delay-ratio);

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

Принцип работы pgcompacttable:

1. Выполняется фиктивное обновление всех записей таблицы, начиная с конца, командой SET field\_name = field\_name. Задержка перед обработкой каждого поля одинакова, что позволяет контролировать нагрузку на систему.

   Примечание:

   PostgreSQL гарантирует, что все новые данные добавляются в свободные места в начале таблицы, так что по прохождению всех строк таблица будет укомплектована.

   После обработки таблицы pgcompacttable перестраивает индексы за три шага:

   1. Создается новый индекс (команда CREATE INDEX с параметром CONCURRENTLY).

   2. Происходит замена (SWAP) имени старого индекса на новый (команда ALTER INDEX RENAME).

   3. Удаляется старый индекс (команда DROP INDEX с параметром CONCURRENTLY).

   На втором шаге может возникнуть длительная блокировка. Чтобы не блокировать индексы на длительное время, pgcompacttable выполняет множество коротких попыток замены индексов. Поведение управляется ключами:

   • --reindex-retry-count – максимальное количество попыток;

   • --reindex-retry-pause – задержка между попытками;

   • --reindex-lock-timeout – максимальное время выполнения переименования. При превышении количества попыток выводится WARNING сообщение: Reindex <имя индекса>, lock has not been acquired.

2. После обработки всех строк запускается VACUUM для удаления пустых блоков с конца таблицы.

Сравнение утилит Pangolin и встроенных в PostgreSQL#

Работу обеих утилит могут затормозить:

• долгие транзакции;

• постоянная вставка и изменение данных.

Рекомендации по использованию:

• pgcompacttable – для безопасной реорганизации таблиц;

• pg_repack – для переноса таблицы в другое табличное пространство и использования CLUSTER без блокировки.

Доработка#

Доработка не проводилась.

Ограничения#

Ограничения отсутствуют.

Установка#

Установка утилиты в документе «Руководство администратора», раздел «Сценарии администрирования», подраздел «Инструмент pgcompacttable».

Расширение установлено по умолчанию.

Настройка#

Настройка не требуется.

Использование модуля#

Подробное описание в документе «Руководство администратора», раздел «Сценарии администрирования», подраздел «Инструмент pgcompacttable».

Ссылки на документацию разработчика#

Утилита pgcompacttable: https://github.com/dataegret/pgcompacttable.