Как выполнить очистку базы данных в PostgreSQL: команда VACUUM
В инструкции расскажем, что такое процедура очистки и как работать с VACUUM в PostgreSQL. Разберем синтаксис и примеры использования команды.
Зачем нужна очистка базы данных и при чем здесь многоверсионность
При одновременной работе нескольких сеансов возникает задача: что делать, если две транзакции обращаются к одной и той же строке. Если каждая из них — читающая, проблем не возникнет. Когда обе пишущие — тоже, так как они выполняются по очереди.
Самый интересный вариант — при одновременном доступе пишущей и читающей транзакции. В таких случаях, чтобы уменьшить аномалии и поддержать приемлемую производительность, PostgreSQL использует многоверсионность, то есть хранит несколько версий одной и той же строки. При этом каждая транзакция работает со своей версией.
Многоверсионность в PostgreSQL реализует изоляцию на основе снимков данных. В результате в табличных страницах накапливаются старые версии строк, а в страницах индексов – ссылки на эти версии.
Какое-то время исторические версии строк нужны транзакциям для работы со своими снимками данных. Но со временем необходимость в них исчезает — такие строки называют «мертвыми».
Процедура очистки удаляет из табличных страниц «мертвые» версии строк и ненужные индексные записи. Без нее таблицы и индексы будут разрастаться, а поиск по ним — замедляться.
Обновление карты видимости
Помимо основной функции, очистка выполняет и другие задачи для поддержки работоспособности системы. Во время этой процедуры обновляются карта видимости и карта свободного пространства — служебная информация, которая хранится вместе с основными данными.
Карта видимости отмечает страницы, содержащие только актуальные версии строк и видимые во всех снимках данных.
Карта видимости применяется в следующих случаях.
- Оптимизация очистки. Отмеченные страницы не требуют проверки во время очистки, так как не содержат мертвых строк.
- Ускорение доступа по индексу. Информация о версиях строк хранится только для таблиц, но не индексов. После получения из индекса ссылки на версию строки выполняется чтение табличной страницы — это нужно для проверки видимости ссылки. Если в индексе есть все нужные столбцы и при этом страница уже отмечена в карте видимости, к таблице можно не обращаться. Это ускоряет доступ по индексу.
Без регулярного обновления карты видимости доступ по индексу становится менее эффективным.
Обновление карты свободного пространства
Карта свободного пространства (Free Space Map, FSM) содержит информацию о наличии свободного места внутри страниц. Этот показатель постоянно меняется: уменьшается при добавлении новых версий строк и увеличивается при их очистке.
Для ускорения поиска карта имеет сложную древовидную структуру. Дерево страниц FSM используется для быстрого нахождения подходящей страницы при вставке новых версий строк.
Карта свободного пространства может существовать и для индексов. Однако, так как индексные записи вставляются в строго определенные места, она отмечает только пустые страницы, образовавшиеся после удаления всех записей. Они исключаются из индексной структуры и при необходимости могут быть снова включены в подходящее место индекса.
Переполнение счетчика транзакций и заморозка версий строк
PostgreSQL нумерует транзакции — для этого счетчика выделено 32 бита. В процессе работы сервера счетчик транзакций может переполниться и обнулиться. Если такое произойдет, порядок транзакций нарушится.
Чтобы этого избежать, пространство номеров транзакций организовано по кругу: для любой транзакции половина номеров в обратном направлении считается прошлым, а другая половина — будущим.
Старые версии строк должны помечаться как «замороженные». Этот признак указывает, что версия строки создана так давно, что номер ее транзакции больше не важен и может быть использован повторно.
Чтобы избежать ненужных проверок, в карту видимости добавлен бит, отмечающий страницы, на которых все версии строк уже заморожены.
Если заморозка не выполнится своевременно, сервер не сможет назначить новый номер транзакции. Это приведет к аварийной ситуации: машина остановится, и все незавершенные транзакции прервутся. После этого администратору потребуется вручную перезапустить сервер и выполнить заморозку.
Стадии выполнения VACUUM
Высвобождение пространства, занимаемого «мертвыми» кортежами, выполняется с помощью VACUUM и проходит через несколько стадий. Вот основные.
Инициализация. Начальная фаза. Процедура VACUUM определяет, какие таблицы и индексы нужно обработать. Здесь устанавливается точка входа для обработки данных и определяются параметры выполнения, например, режим работы — обычный или FULL (полный).
Сбор статистики. VACUUM собирает информацию о текущем состоянии таблиц и индексов, включая количество живых и мертвых строк, размер таблиц и уровень фрагментации.
Анализ и маркировка. VACUUM анализирует страницы данных и отмечает мертвые строки, которые следует удалить. На этом этапе могут обновляться карта видимости (VM) и карта свободного пространства (FSM).
Очистка таблицы. Удаляются мертвые строки, и освобождается пространство для новых данных. При использовании команды VACUUM FULL таблицы перестраиваются полностью. Это значительно сокращает их размер, но при этом требует временной блокировки.
Очистка индексов. Индексы, связанные с таблицами, также очищаются от ссылок на удаленные строки. Если используется команда VACUUM FULL, индексы перестраиваются заново.
Обновление статистики и завершение. Обновляются внутренние статистические данные, такие как карта видимости и карта свободного пространства. Записываются результаты выполнения процедуры, что помогает оптимизатору запросов более эффективно планировать будущие запросы. Завершается процедура, и освобождаются все временные ресурсы.
Каждая из этих стадий имеет свои особенности и влияет на производительность системы по-разному. Стандартный VACUUM работает в фоновом режиме и не блокирует таблицы, в то время как VACUUM FULL требует эксклюзивной блокировки и может значительно повлиять на производительность, но обеспечивает максимальную очистку и дефрагментацию данных.
Как использовать VACUUM
Рассмотрим на примерах, как выполнить очистку базы данных.
Стандартная очистка — VACUUM
Команда выполняет обычную очистку указанной таблицы. Если имя таблицы не указано, команда применяется ко всем таблицам в базе данных.
VACUUM [table_name];
Очистка с анализом — VACUUM ANALYZE
Этот вариант команды дополнительно обновляет статистику, используемую оптимизатором запросов. Бывает полезен для улучшения производительности запросов.
VACUUM ANALYZE [table_name];
Полная очистка — VACUUM FULL
Команда VACUUM FULL выполняет полную перестройку таблицы и всех ее индексов. Это освобождает максимальное количество неиспользуемого пространства в операционной системе, но требует эксклюзивной блокировки таблицы и может быть значительно медленнее обычного VACUUM.
VACUUM FULL [table_name];
Режим ACCESS EXCLUSIVE гарантирует, что кроме транзакции, получившей эту блокировку, никакая другая транзакция не может обращаться к таблице каким-либо способом.
Для полной перестройки таблицы можно использовать команду CLUSTER. Перестроение таблицы происходит на основе существующего индекса. В процессе выполнения кластеризации происходит сортировка полей на основе индекса. Как и для VACUUM FULL, требуется эксклюзивная блокировка таблицы.
CLUSTER [table_name] USING [index_name];
Мониторинг очистки — VACUUM VERBOSE
С указанием VERBOSE команда VACUUM выведет подробный отчет о действиях процесса очистки.
db1=# VACUUM VERBOSE test;
INFO: vacuuming "public.test"
INFO: table "test": index scan bypassed: 1 pages from table (0.18% of total) have 1 dead item identifiers
INFO: table "test": found 1 removable, 100000 nonremovable row versions in 541 out of 541 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1254221
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
Также за прогрессом процессов очистки можно следить с помощью представления pg_stat_progress_vacuum.
db1=# select * from pg_stat_progress_vacuum\gx
-[ RECORD 1 ]------+------------------
pid | 1462
datid | 16401
datname | db1
relid | 4112457
phase | vacuuming indexes
heap_blks_total | 9311
heap_blks_scanned | 9311
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 2709501
num_dead_tuples | 60149
Команда vacuumdb
Утилита vacuumdb позволяет запускать очистку и анализ вручную. Она обертка над командами VACUUM и ANALYZE с дополнительными удобными функциями:
- выполнить ANALYZE в несколько проходов — параметр —analyze-in-stages;
- выполнить VACUUM всех баз данных с помощью одной команды — параметр —all.
Выполнить очистку базы данных db1:
$ vacuumdb db1
$ vacuumdb db1
Выполнить очистку и анализ базы данных db1:
$ vacuumdb –-analyze db1
Выполнить анализ всех баз данных в несколько проходов:
$ vacuumdb --all --analyze-in-stages
Автоматическая очистка — autovacuum
Autovacuum автоматизирует описанные выше периодические задачи обслуживания. Процесс автоочистки реагирует на частоту обновления данных: чем активнее изменения, тем чаще таблица будет обрабатываться.
Эта операция не блокирует транзакции, но может создавать нагрузку на подсистему ввода-вывода. Автоматическая очистка перестанет работать, если отключить любой из двух параметров: autovacuum или track_counts. Может показаться, что такое отключение повысит производительность системы за счет исключения «лишних» операций ввода-вывода. Однако на практике отказ от очистки приводит к серьезным последствиям: неконтролируемому росту размеров файлов, замедлению выполнения запросов и риску аварийной остановки сервера.
Автоматическая очистка настраивается множеством взаимосвязанных параметров. В хорошо настроенной системе она предотвращает неадекватное разрастание таблиц, не создавая при этом избыточных накладных расходов.
Настройка autovacuum скорее итеративный процесс, но для начала необходимо установить разумные значения параметров, учитывая размер и характер использования таблиц, постоянно мониторить систему и вносить необходимые коррективы.