Удаление устаревших строк с помощью VACUUM

Павел Семёнов

Contents

Обзор
- Освобождение дискового пространства
- Предотвращение ошибок из-за зацикливания счетчика транзакций
Проверка раздувания таблиц
Способы выполнения VACUUM
Когда выполнять VACUUM
Вакуумирование AO-таблиц
Устранение раздувания индексов
Устранение раздувания системного каталога

В этом разделе описывается, как удалять устаревшие строки таблиц в Greengage DB (на основе Greenplum) с помощью операции VACUUM.

Greengage использует модель Multi-Version Concurrency Control (MVCC) PostgreSQL для управления конкурентным доступом к heap-таблицам. Операции обновления и удаления в этой модели — логические: они не изменяют и не удаляют строки, а помечают их как устаревшие (или "мертвые"). Обновление создает новую версию строки с измененными значениями. Она добавляется в таблицу так же, как при вставке новой строки. По умолчанию устаревшие строки невидимы для пользователей: они не возвращаются в результатах SQL-запросов и не учитываются в статистике. Они используются СУБД, например, для разрешения конфликтов и откатов транзакций. Аналогично хранятся индексы: устаревшие записи в индексах сохраняются, но игнорируются при выполнении запросов. Более детальная информация об MVCC приведена в соответствующем разделе документации PostgreSQL: Concurrency control.

Поскольку устаревшие строки физически остаются в базе данных, размеры таблиц растут со временем, даже если их видимое число строк не меняется. Это приводит к деградации производительности SQL-запросов из-за увеличения объема операций дискового ввода-вывода.

Со временем таблица может занять значительно больше дискового пространства, чем требуется для хранения ее актуальных данных. Этот избыточный рост называется раздуванием (bloat). Раздуванию подвержены таблицы различных типов — пользовательские и системные, heap и оптимизированные для добавления (AO) — а также индексы.

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

Оптимизированные для добавления (AO) таблицы обрабатывают обновления и удаления иначе. Их специфика описана в разделе Вакуумирование AO-таблиц.

Системные таблицы также раздуваются со временем. Рекомендации по их вакуумированию приведены в разделе Устранение раздувания системного каталога.

Еще одно назначение операции VACUUM — предотвращение ошибок из-за зацикливания счетчика транзакций. В MVCC каждая транзакция имеет 32-битный идентификатор, который называется XID. Таким образом, доступно около четырех миллиардов значений XID. Они используются циклично: если счетчик переполняется, он обнуляется и запускается заново. После переполнения новые транзакции начинают получать XID с меньшими значениями, чем у более старых транзакций. Для корректной работы в этом случае Greengage DB, как и PostgreSQL, использует специальные правила сравнения XID, в которых меньшие значения XID могут соответствовать более новым транзакциям. Более детальная информация о зацикливании счетчика транзакций и сравнении XID приведена в соответствующем разделе документации PostgreSQL: Routine vacuuming.

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

Раздувание измеряется как соотношение между фактическим (actual) и ожидаемым (expected) количеством страниц, используемых таблицей, где:

actual — текущее количество страниц, занимаемых таблицей.
expected — минимальное количество страниц, необходимое для хранения данных без учета устаревших строк.

Это соотношение позволяет оценить, сколько места можно освободить с помощью операции VACUUM.

ВАЖНО

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

Чтобы узнать текущее количество страниц (actual), используемых таблицей, выполните запрос к системной таблице pg_class:

SELECT relname, relpages, reltuples FROM pg_class WHERE relname='orders';

Пример результата:

 relname | relpages | reltuples
---------+----------+-----------
 orders  |       25 |     1e+03
(1 row)

При обновлениях или удалениях количество страниц (relpages) со временем растет, даже если число строк (reltuples) не меняется или уменьшается.

Для проверки раздувания heap-таблиц в Greengage DB доступны два представления: gp_toolkit.gp_bloat_diag и gp_toolkit.gp_bloat_expected_pages.

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

SELECT * FROM gp_toolkit.gp_bloat_diag;

Пример вывода:

 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages |              bdidiag
----------+------------+------------+-------------+-------------+------------------------------------
    17018 | public     | orders     |          25 |           4 | moderate amount of bloat suspected
(1 row)

Информация о раздувании таблицы отображается в колонках:

bdirelpages — фактическое количество страниц (аналог relpages в pg_class).
bdiexppages — ожидаемое количество страниц.

Таблицы попадают в gp_toolkit.gp_bloat_diag, только если соотношение фактического числа страниц к ожидаемому больше либо равно 3.

В колонке bdidiag хранится диагностическое сообщение:

moderate amount of bloat suspected — количество страниц превышает ожидаемое более чем в 3 раза.
significant amount of bloat suspected — количество страниц превышает ожидаемое более чем в 10 раз.

Для поиска раздувшихся таблиц по имени используйте команду:

SELECT * FROM gp_toolkit.gp_bloat_diag WHERE bdirelname = 'orders';

Для отображения таблиц со значительным раздуванием:

SELECT * FROM gp_toolkit.gp_bloat_diag WHERE bdidiag LIKE '%significant%';

Чтобы просмотреть информацию об использовании страниц всеми heap-таблицами, включая те, которые отсутствуют в gp_bloat_diag, используйте представление gp_toolkit.gp_bloat_expected_pages.

Для проверки конкретной таблицы отфильтруйте по ее идентификатору объекта (oid):

SELECT * FROM gp_toolkit.gp_bloat_expected_pages WHERE btdrelid = 'orders'::regclass;

Пример вывода:

 btdrelid | btdrelpages | btdexppages
----------+-------------+-------------
    17018 |          25 |           4
(1 row)

Просмотр устаревших версий строки

Чтобы увидеть все хранимые версии строки, включая устаревшие, включите параметр конфигурации gp_select_invisible:

SET gp_select_invisible = TRUE;

Если после этого запросить одну запись таблицы, например, по уникальному столбцу, запрос может вернуть несколько строк:

SELECT * FROM orders WHERE order_id = 1;

Пример вывода:

 order_id | items | total | comment
----------+-------+-------+----------
        1 | 1     |    43 | comment
        1 | 1     |    47 | comment
        1 | 1     |    51 | comment
(3 rows)

Чтобы узнать, какая из версий актуальная, используйте скрытые системные столбцы xmin и xmax. Они содержат идентификаторы транзакций, в которых строка была создана или логически удалена. Чтобы отобразить эти столбцы, укажите их в запросе явно:

SELECT xmin, xmax, * FROM orders WHERE order_id = 1;

Пример вывода:

 xmin | xmax | order_id | items | total | comment
------+------+----------+-------+-------+----------
  897 |  898 |        1 | 1     |    43 | comment
  898 |  899 |        1 | 1     |    47 | comment
  899 |    0 |        1 | 1     |    51 | comment
(3 rows)

Строка с xmax = 0 является актуальной. Значение 0 означает, что строка не была логически удалена. Все остальные строки — устаревшие.

Для проверки раздувания таблиц, оптимизированных для добавления, воспользуйтесь функцией gp_toolkit.__gp_aovisimap_compaction_info(oid). Подробнее о ней рассказывается в разделе Вакуумирование AO-таблиц.

Greengage DB предоставляет следующие способы вакуумирования таблиц:

SQL-команда VACUUM и ее вариант VACUUM FULL.
Утилита vacuumdb.

Команда VACUUM освобождает место, занимаемое устаревшими строками, помечая его как доступное. Это пространство хранилища не возвращается операционной системе. Вместо этого будущие операции INSERT и UPDATE записывают новые строки в помеченное пространство на существующих страницах, перезаписывая устаревшие данные.

ПРИМЕЧАНИЕ

Исключением являются устаревшие строки в конце таблицы. Если операции VACUUM удается установить блокировку Access exclusive и последние страницы таблицы пусты, они отсекаются и освободившееся место возвращается операционной системе.

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

Также VACUUM замораживает старые актуальные строки на основе значения параметра vacuum_freeze_min_age. Это специальное состояние строки, при котором она остается доступной для всех будущих транзакций независимо от переполнений счетчика транзакций. VACUUM определяет, какие строки заморозить, на основе их возраста — разницы между значением xmin строки и последним использованным XID на текущий момент. Все строки старше vacuum_freeze_min_age замораживаются.

VACUUM — неблокирующая операция: ее можно выполнять параллельно с большинством других SQL-запросов, включая SELECT, INSERT и UPDATE. Однако она не может выполняться одновременно с некоторыми DDL-операциями, например, ALTER TABLE.

Вызов VACUUM без аргументов обрабатывает все таблицы текущей базы данных:

VACUUM;

Для вакуумирования конкретной таблицы укажите ее имя:

VACUUM orders;

Это часто быстрее и менее ресурсоемко, чем очистка всей базы, особенно если известно, какая таблица раздута.

Следующие ключевые слова определяют, как именно выполняется VACUUM:

FULL — переписывает таблицу без устаревших строк в новый файл и возвращает освободившееся место операционной системе. Требует эксклюзивного доступа к таблице и выполняется дольше. Подробности приведены в разделе Команда VACUUM FULL.
VERBOSE — выводит подробную информацию о процессе.
ANALYZE — запускает сбор статистики после очистки. Статистика собирается по таблицам, на которых выполняется VACUUM.

При вакуумировании одной таблицы можно указать одну или несколько колонок для анализа:
```
VACUUM ANALYZE orders (order_id);
```
Это может быть полезно, если в WHERE-условиях или соединениях участвуют только отдельные столбцы, требующие точной статистики. Если колонки не указаны, Greengage DB собирает статистику по всем колонкам таблицы.
FREEZE — выполняет VACUUM так, как если бы параметр vacuum_freeze_min_age имел значение 0. Это означает, что все актуальные строки, которые обрабатывает операция, замораживаются — их значения xmin логически находятся в прошлом относительно всех будущих транзакций, независимо от значений их XID.

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

VACUUM (VERBOSE, ANALYZE) orders;

Размер таблицы после VACUUM

Имейте в виду, что обычный VACUUM не уменьшает физический размер таблицы до минимально возможного. Он сохраняет все существующие страницы, очищая их от устаревших данных, и делает освободившееся место доступным для повторного использования. Если таблица сильно раздута, рассмотрите возможность использования VACUUM FULL или пересоздания таблицы с помощью CREATE TABLE AS.

VACUUM FULL — это специальная форма операции VACUUM, которая физически переписывает таблицу, удаляя устаревшие строки. В отличие от обычного VACUUM, который логически освобождает место на страницах таблицы, VACUUM FULL уменьшает физический размер таблицы и возвращает неиспользуемое пространство операционной системе. В результате фактическое количество страниц таблицы уменьшается до возможного минимума. Также VACUUM FULL упорядочивает данные в таблице, размещая строки вплотную без внутренней фрагментации. Это повышает эффективность последующего чтения данных.

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

VACUUM FULL;

Или для конкретной таблицы:

VACUUM FULL orders;

Поскольку VACUUM FULL переписывает всю таблицу, эта операция освобождает больше пространства, чем обычный VACUUM, не оставляя промежутков между строками. Ее выполнение устанавливает эксклюзивную блокировку таблицы (Access exclusive), поэтому другие запросы к таблице невозможны во время выполнения. Из-за переписывания таблицы операция может быть ресурсоемкой и требует дополнительного дискового пространства, примерно равного размеру таблицы.

ВНИМАНИЕ

Поскольку VACUUM FULL блокирует доступ к таблице и временно требует больше дискового пространства, его рекомендуется выполнять только во время планового обслуживания. На больших или активно используемых таблицах это может привести к перебоям в обслуживании.

Регулярное выполнение обычного VACUUM помогает контролировать раздувание таблиц и снижает необходимость в VACUUM FULL. Подробнее о планировании операций очистки рассказывается в разделе Когда выполнять VACUUM.

ПРИМЕЧАНИЕ

Альтернатива VACUUM FULL — использование оператора CREATE TABLE AS для копирования данных в новую таблицу с последующим удалением исходной. Такой ручной подход позволяет лучше контролировать блокировки и использование диска.

vacuumdb — это утилита командной строки, которая выполняет операцию VACUUM. Она автоматизирует вакуумирование, поэтому подходит для использования в скриптах обслуживания и запланированных заданиях. С помощью vacuumdb можно выполнять VACUUM без ручного подключения к базе данных через psql.

Для вакуумирования всей базы данных:

$ vacuumdb books_store

В полной форме вызова указывается название опции -d (--database):

$ vacuumdb -d books_store

Чтобы выполнить вакуумирование всех баз данных в кластере Greengage DB, используйте опцию -a (--all):

$ vacuumdb --all

Чтобы выполнить вакуумирование конкретной таблицы, укажите ее имя в опции -t (--table):

$ vacuumdb --table 'orders' books_store

Для вакуумирования таблицы с последующим сбором ее статистики:

$ vacuumdb --analyze --table 'orders' books_store

Ниже приведены параметры, управляющие выполнением VACUUM:

--full (или -f) — выполняет VACUUM FULL, переписывая таблицу и возвращая неиспользуемое пространство операционной системе. Рекомендуется запускать только в периоды обслуживания из-за блокировок и высокой ресурсоемкости.
--verbose (или -v)-- выводит подробную информацию о ходе выполнения.
--analyze (или -z)-- собирает статистику после вакуумирования, улучшая работу планировщика запросов.
--analyze-only (или -Z) — выполняет ANALYZE без вакуумирования. Равнозначно вызову утилиты analyzedb.

При анализе конкретной таблицы можно указать один или несколько столбцов для сбора статистики только по ним:
```
$ vacuumdb --analyze --verbose --table 'orders(order_id)' books_store
```
--freeze (или -F) — выполняет VACUUM FREEZE.

ВАЖНО

В отличие от PostgreSQL, в Greengage DB не поддерживается автоматическое вакуумирование (autovacuum) для пользовательских баз данных. Все операции VACUUM необходимо выполнять вручную или автоматизировать с помощью vacuumdb.

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

Стандартная рекомендация — выполнять вакуумирование раз в сутки. Для таблиц с частыми операциями UPDATE или DELETE имеет смысл выполнять VACUUM чаще. Например, несколько раз в день, в зависимости от нагрузки и объема таблицы.

ВАЖНО

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

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

VACUUM ANALYZE orders;

Это не только освобождает пространство, занятое устаревшими строками, но и обновляет статистику для планировщика запросов.

Для сильно раздувшихся таблиц полезно выполнять VACUUM перед сбором статистики (ANALYZE). Так статистика будет собрана по "очищенной" таблице, что даст более точную оценку числа строк и приведет к лучшим планам выполнения.

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

РЕКОМЕНДАЦИЯ

Если в базе данных регулярно удаляются все строки каких-либо таблиц, используйте TRUNCATE вместо DELETE. TRUNCATE сразу удаляет файлы данных и не использует MVCC. Это устраняет необходимость в VACUUM и снижает потребление дискового пространства.

Оптимизированные для добавления (AO) таблицы используют модель конкурентного доступа, отличную от MVCC. В отличие от heap-таблиц, AO-таблицы эффективны в первую очередь при добавлении новых данных, а не при их изменении или удалении. Тем не менее при выполнении операций UPDATE или DELETE AO-таблицы со временем также раздуваются.

ПРИМЕЧАНИЕ

Если строки AO-таблицы часто обновляются или удаляются, рассмотрите возможность изменения ее типа на heap. Хранилище heap оптимизировано для изменения строк и не требует полной перезаписи таблицы. Критерии выбора типа таблицы приведены в разделе Как выбрать тип хранения таблицы.

При выполнении VACUUM для AO-таблицы происходит физическая перезапись таблицы без устаревших строк с возвратом занятого ими пространства. Этот процесс называется AO-уплотнением (AO compaction) и требует достаточного свободного места на диске, чтобы временно хранить полную копию таблицы. Поскольку для AO-уплотнения требуется значительный объем дискового пространства, Greengage DB не сжимает AO-таблицы, в которых раздувание не превышает заданный порог. Этот порог задается параметром конфигурации gp_appendonly_compaction_threshold и отражает процент устаревших строк. По умолчанию он составляет 10.

При уплотнении AO-таблиц Greengage DB проверяет этот порог отдельно для каждого сегмента. Таким образом, команда VACUUM, вызванная на AO-таблице, может выполниться только на части сегментов и пропустить остальные.

Команда VACUUM FULL игнорирует порог и всегда выполняет уплотнение всей таблицы.

Чтобы проверить степень раздувания AO-таблицы, используйте функцию gp_toolkit.__gp_aovisimap_compaction_info(oid). Эта функция выводит информацию об устаревших строках в файлах данных сегментов AO-таблицы.

SELECT * FROM gp_toolkit.__gp_aovisimap_compaction_info('customers'::regclass);

Пример вывода:

NOTICE:  gp_appendonly_compaction_threshold = 10
 content | datafile | compaction_possible | hidden_tupcount | total_tupcount | percent_hidden
---------+----------+---------------------+-----------------+----------------+----------------
       1 |        1 | t                   |            2000 |           3000 |          66.67
       3 |        1 | t                   |            2000 |           3000 |          66.67
       0 |        1 | t                   |            2000 |           3000 |          66.67
       2 |        1 | t                   |            2000 |           3000 |          66.67
(4 rows)

Структура вывода функции __gp_aovisimap_compaction_info
Поле	Описание
content	Идентификатор сегмента
datafile	Идентификатор файла данных в сегменте
compaction_possible	Логическое значение (`t` или `f`), указывающее, будет ли файл уплотнен при выполнении `VACUUM`. Определяется с учетом значения параметра `gp_appendonly_compaction_threshold`
hidden_tupcount	Количество устаревших строк в файле данных
total_tupcount	Общее количество строк в файле, включая устаревшие
percent_hidden	Процент устаревших строк в файле

После выполнения VACUUM ANALYZE для таблицы функция возвращает следующие строки для того же сегмента:

NOTICE:  gp_appendonly_compaction_threshold = 10
 content | datafile | compaction_possible | hidden_tupcount | total_tupcount | percent_hidden
---------+----------+---------------------+-----------------+----------------+----------------
       0 |        1 | f                   |               0 |              0 |           0.00
       0 |        2 | f                   |               0 |           1000 |           0.00
       3 |        1 | f                   |               0 |              0 |           0.00
       3 |        2 | f                   |               0 |           1000 |           0.00
       2 |        1 | f                   |               0 |              0 |           0.00
       2 |        2 | f                   |               0 |           1000 |           0.00
       1 |        1 | f                   |               0 |              0 |           0.00
       1 |        2 | f                   |               0 |           1000 |           0.00
(8 rows)

Значение f в столбце compaction_possible показывает, что сейчас нет файлов, которые можно уплотнить.

Чтобы полностью отключить уплотнение AO-таблиц, установите параметр gp_appendonly_compaction в значение off.

$ gpconfig -c gp_appendonly_compaction -v off
$ gpstop -u

ВАЖНО

Операции VACUUM и VACUUM FULL не удаляют раздувание в индексах. Они обрабатывают только таблицы.

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

Единственный способ удалить раздувание в индексе — пересоздать его с помощью команды REINDEX:

Пересоздать все индексы таблицы:
```
REINDEX TABLE orders;
```
Пересоздать конкретный индекс:
```
REINDEX INDEX total_idx;
```
Если известно, что раздулись один-два конкретных индекса, лучше выбрать этот вариант: он потребляет меньше ресурсов.

После выполнения REINDEX метрики статистики индекса, такие как reltuples и relpages в pg_class, обнуляются. Чтобы актуализировать статистику, выполните ANALYZE для таблицы после перестроения индекса:

ANALYZE orders;

Системный каталог хранит метаданные обо всех объектах баз данных: таблицах, индексах, столбцах, функциях и других. Как и пользовательские таблицы, таблицы системного каталога и их индексы со временем раздуваются. Изменения в схемах и операции DDL обновляют и удаляют строки в системных таблицах, оставляя их устаревшие версии.

В активно используемых базах данных с частыми изменениями схем и объектов базы данных раздувание системного каталога может привести к следующим проблемам:

Замедление выполнения системных запросов (планирование, интроспекция, соединения).
Повышенное потребление памяти планировщиком запросов.
Увеличение использования дискового пространства системным каталогом.

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

Если объекты баз данных изменяются редко, можно выполнять очистку системного каталога реже. Минимально рекомендуемая частота в этом случае — раз в неделю. Кроме того, для удаления раздувания в индексах каталога необходимо выполнять перестроение индексов.

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

Перестройте индексы системного каталога с помощью команды REINDEX SYSTEM или утилиты reindexdb с опцией -s (--system).
Выполните вакуумирование таблиц системного каталога с помощью VACUUM (периодическое обслуживание) или VACUUM FULL (при сильном раздувании каталога).

ВАЖНО

Для выполнения VACUUM FULL запланируйте окно обслуживания с остановкой работы. Регулярную очистку каталога можно проводить в периоды низкой активности.
Обновите статистику для таблиц и индексов каталога с помощью команды ANALYZE или утилиты analyzedb.

Таблица pg_attribute

Таблица pg_attribute часто является самой большой и раздутой таблицей системного каталога. Она хранит метаданные столбцов для всех таблиц.

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

SELECT * FROM gp_toolkit.gp_bloat_diag WHERE bdirelname = 'pg_attribute';

Если результат не пустой, необходимо выполнить VACUUM или VACUUM FULL в зависимости от уровня раздувания.

Сбор статистики с помощью ANALYZE

Управление spill-файлами

Удаление устаревших строк с помощью VACUUM

Обзор

Освобождение дискового пространства

Предотвращение ошибок из-за зацикливания счетчика транзакций

Проверка раздувания таблиц

Способы выполнения VACUUM

Команда VACUUM

Команда VACUUM FULL

Утилита vacuumdb

Когда выполнять VACUUM

Вакуумирование AO-таблиц

Устранение раздувания индексов

Устранение раздувания системного каталога