ETL-потоки «VACUUM FULL», или Как учесть особенности жизненного цикла данных в условиях высоконагруженных хранилищ13.03.2025 12:01

1d1ee5949b374235a1b5408f65b258e2.jpg

Привет, Хабр! Наша команда работает в Сбере и отвечает за развитие аналитического хранилища данных (АХД), а также обеспечение финансово‑аналитической информацией ключевых подразделений. В нашем технологическом стеке используются Greenplum на основе PostgreSQL и Hadoop, что позволяет эффективно обрабатывать большие объёмы данных, гарантируя их надёжное хранение и своевременную доступность для потребителей.

Основными потребителями данных являются бизнес‑подразделения банка, которые используют информацию для принятия стратегических решений и управления операционной деятельностью. Главная цель нашей работы — поддержка процессов построения различных видов отчётности:

  • управленческой (для внутреннего анализа и планирования);

  • обязательной (в соответствии с внутренними требованиями банка);

  • регуляторной (для предоставления данных контролирующим органам).

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

Перейдём к технологической стороне работы с данными. Стоит уделить внимание особенностям системы управления базами данных, а именно PostgreSQL. Эта СУБД использует механизм MVCC (Multi‑Version Concurrency Control) для управления параллельными транзакциями. Когда строки обновляются или удаляются, их старые версии, называемые «мёртвыми кортежами», не удаляются немедленно из физического хранилища. Вместо этого они помечаются как невидимые для новых транзакций, а занимаемое ими место остаётся незадействованным.

Это критично в контексте работы с большими данными. Любой ETL‑процесс, включающий операции удаления (delete) и обновления (update), приводит к образованию «мёртвых кортежей». Эти кортежи занимают пространство и могут серьёзно повлиять на производительность системы.

В PostgreSQL и аналогичных базах данных для освобождения места, занимаемого «мёртвыми кортежами», используются команды VACUUM (FULL) и её автоматизированный вариант — AUTOVACUUM.

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

Особенности VACUUM

Освобождение места. VACUUM сканирует таблицу, выявляя «мёртвые кортежи», которые можно освободить. Однако он не уменьшает физический размер файла таблицы, вместо этого помечая место как доступное для повторного использования.

Предотвращение переполнения ID транзакций. В PostgreSQL каждой строке присваивается идентификатор транзакции (XID). Если этот счётчик достигнет предела, то могут возникнуть проблемы с целостностью данных, если старые строки не будут «заморожены». VACUUM помогает предотвратить это, замораживая старые строки и превращая их в постоянные.

Обновление статистики. VACUUM ANALYZE объединяет функциональность VACUUM и ANALYZE. Помимо освобождения места, он обновляет статистику для планировщика запросов, что способствует улучшению производительности запросов.

Неблокирующая операция. В отличие от VACUUM FULL, стандартная команда VACUUM не блокирует таблицу. Она позволяет читать и записывать во время её выполнения.

Что такое VACUUM FULL?

VACUUM FULL — это более ресурсоемкая версия VACUUM, которая перезаписывает всю таблицу в новый файл, исключая «мёртвые кортежи», и компактирует её на диске. Этот процесс физически уменьшает размер таблицы, постоянно освобождая место на диске. Основные особенности VACUUM FULL:

  • Перезапись таблицы. VACUUM FULL создаёт новую копию таблицы без «мёртвых кортежей», эффективно уменьшая её размер.

  • Блокировка таблицы. Во время выполнения VACUUM FULL таблица блокируется для записи (и иногда для чтения), что означает, что другие транзакции не смогут модифицировать таблицу до завершения операции.

  • Ресурсоёмкость. VACUUM FULL требует значительных ресурсов ввода/вывода (I/O) и CPU, так как включает в себя копирование всей таблицы и её индексов.

  • Временное место на диске. Процесс требует дополнительного места на диске, так как старая и новая версии таблицы существуют одновременно в процессе выполнения операции.

Зачем комбинировать VACUUM и VACUUM FULL с ANALYZE?

После выполнения команд VACUUM или VACUUM FULL полезно обновить статистику таблицы с помощью ANALYZE. Это гарантирует, что планировщик запросов имеет актуальную информацию о структуре таблицы и распределении данных, что улучшает производительность запросов. Комбинированное использование VACUUM и ANALYZE особенно важно в следующих случаях:

  • Частые обновления и удаления. Таблицы, которые подвергаются частым изменениям (обновлениям и удалениям), могут иметь устаревшие статистические данные, что может привести к неоптимальным планам запросов.

  • Большие таблицы. Для крупных таблиц актуальные статистические данные критичны для эффективного планирования запросов

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

Что такое AUTOVACUUM и на что обратить внимание?

AUTOVACUUM — это автоматическая система обслуживания в PostgreSQL, которая работает в фоновом режиме и выполняет задачи по очистке и оптимизации таблиц базы данных. Она была введена в PostgreSQL 8.1, чтобы минимизировать необходимость ручного вмешательства в обслуживание базы данных. Основная цель AUTOVACUUM — автоматическое выполнение команд VACUUM и ANALYZE.

Однако есть свои особенности использования AUTOVACUUM:

  • Избыточное потребление ресурсов. В системах с ограниченными ресурсами или высокой конкуренцией выполнение AUTOVACUUM может вызвать чрезмерную нагрузку на CPU, I/O и память, что снижает производительность.

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

  • Проблемы с очень большими таблицами. Для больших таблиц AUTOVACUUM может не запускаться достаточно часто или работать слишком долго, что приводит к накоплению «мёртвых кортежей» и ухудшению производительности, о которой писали ранее.

  • Конфликты с задачами. AUTOVACUUM может конфликтовать с другими задачами, такими как резервное копирование или массовая загрузка данных, если они требуют блокировки таблиц или интенсивного использования I/O, что приводит к задержкам и конфликтам.

AUTOVACUUM имеет множество параметров конфигурации, такие как, autovacuum_vacuum_scale_factor, autovacuum_analyze_threshold, которые требуют тщательной настройки. Неправильные настройки могут привести либо к избыточному использованию ресурсов,  либо к недостаточному обслуживанию.

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

Кастомный ETL‑поток «VACUUM FULL»

В нашем аналитическом хранилище, где гарантированная и точная доставка данных потребителю имеет первостепенное значение, мы пришли к необходимости создать и внедрить кастомизированный подход к выполнению процессов VACUUM. Это позволило не только оптимизировать производительность, но и минимизировать риски, связанные с накоплением объёма «мёртвых кортежей», что могло бы повлиять на стабильность обеспечения бизнес‑подразделений данными.

Мы выделили ряд важных факторов, которые требуют особого внимания для поддержания эффективной работы хранилища данных:

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

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

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

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

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

Реализация кастомизированного процесса «VACUUM FULL

Мы разработали и успешно внедрили ETL‑потоки «VACUUM FULL», которые автоматически выполняют операции VACUUM над таблицами, когда соотношение между количеством «мёртвых кортежей» (n_dead_tup) и «живых» строк (n_live_tup) достигает заранее установленного порогового значения. Данные о состоянии таблиц берутся из метаданных pg_stat_all_tables, что позволяет точно определять таблицы, требующие очистки.

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

Используемый ETL‑фреймворк

Чтобы детальнее описать реализацию, стоит сделать небольшое отступление для знакомства с ETL‑фреймворком SberETL, используемым в нашей системе для создания и управления ETL‑процессами.

SberETL поддерживает взаимодействие с различными СУБД и технологиями, обеспечивая работу с базами данных Greenplum, PostgreSQL, SDP Hadoop, ClickHouse, а также интеграцию с внутренними сервисами. Он поддерживает мультистрочные операции в одном сценарии с помощью оператора multi и условные операторы IF, что позволяет реализовывать сложную логику обработки данных. SberETL:

  • работает с промежуточными переменными через плейсхолдеры и оператор var;

  • поддерживает итеративную обработку данных с помощью циклов;

  • использует структурные переходы для управления потоком выполнения сценариев;

  • позволяет проверку условий и прерывание сценариев при выполнении заданных условий;

  • взаимодействует с файловой системой для чтения и записи данных.

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

… Вернёмся к реализации кастомизированного процесса «VACUUM FULL»

Применение SberETL в рамках этой задачи продемонстрировало свою полную обоснованность и высокую эффективность. Процессы «VACUUM FULL» были успешно реализованы в виде сценариев фреймворка SberETL, размещённых в сервисной схеме ***_db_dmetlfw. Структура сценария для потока «VACUUM FULL» включает в себя три последовательных шага), каждый из которых выполняет строго определённую функцию.

6e950cd8a79e2b4addb9a1a679775d48.png

Шаг 1. Получение переменной, содержащей перечень схем для последующей обработки.

Данная операция реализована через SQL‑запрос к таблице метаданных ETL_TASK_PARAM, в которой лежат параметры для ETL‑процессов: 

/*variable column=2*/
select 'schemas' as key, param_val as schemas
  from ***_db_dmetlfw.etl_task_param
 where param_name = 'VACUUM_SCHEMAS200';

Шаг 2. Формирование команд логирования и выполнения операции vacuum.

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

  1. команда выполнения операции VACUUM FULL ANALYZE;

  2. команда записи логов о начале и завершении обработки в таблицу debug_log.

Для повышения производительности процесс реализован с использованием параллельного выполнения в пяти одновременно работающих потоках. Таблицы обрабатываемых схем делятся на пять групп с использованием формулы mod(relid::bigint, 5). Все группы обрабатываются параллельно.

Важный момент: набор команд формируется динамически при запуске потока на основе данных из словаря базы данных (pg_tables). Это позволяет автоматически включать новые таблицы в процесс обработки без дополнительных настроек.

/*variable column=2*/
with a as (
  select case when mod(relid::bigint, 5) = 0
        then string_agg('/*sql*/ select count(*) from prc_debug(''Start ' || schemaname || '.' || relname ||''', ''[WORKFLOW_RUN_ID]'', ''sp_vacuum_full'');'||chr(10)||
                '/*sql*/ vacuum full analyze ' || schemaname || '.'|| relname || ';'||chr(10)||
                '/*sql*/ select count(*) from prc_debug(''Finish ' || schemaname || '.' || relname ||''', ''[WORKFLOW_RUN_ID]'', ''sp_vacuum_full'');',
                chr(10)) end as vacuum
    from pg_catalog.pg_stat_all_tables
   where ${schemas}
       and n_dead_tup > 10000
       and n_dead_tup / (case when n_live_tup = 0 then 1 else n_live_tup end)::numeric > 0.05
   group by mod(relid::bigint, 5)
)
select 'vacuum' as key,
     coalesce(max(vacuum), 'select 1'::text) as vacuum
  from a

Шаг 3. Выполнение сформированных команд.

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

/*multi*/
{vacuum}

Таким образом, каждый поток «VACUUM FULL» обрабатывает свой набор схем данных с заданной степенью распараллеливания. Потоки запускаются в соответствии с установленным расписанием, которое учитывает время выполнения для каждой конкретной схемы. Такая архитектура обеспечивает высокую производительность и гибкость системы, позволяя эффективно управлять ресурсами и минимизировать время обработки данных.

Заключение

Разработанное решение для ETL‑потоков «VACUUM FULL» демонстрирует, что гибкость и контроль могут быть достигнуты через кастомизированную реализацию. Это решение позволило:

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

  • точно управлять ресурсами и исключать из обработки таблицы, не требующие очистки;

  • автоматизировать принятие решений на основе актуальной статистики (pg_stat_all_tables), обеспечивая реактивность системы;

  • минимизировать зависимость от ручного вмешательства, передав управление второй линии сопровождения.

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

Авторы:

Николай Абрамов, участник профессионального сообщества Сбера DWH/BigData. Профессиональное сообщество отвечает за развитие компетенций в таких направлениях как экосистема Hadoop, PostgreSQL, GreenPlum, а также BI‑инструментах Qlik, Apache SuperSet и др.

Евгений Видман, участник профессионального сообщества Сбера DWH/BigData. Профессиональное сообщество отвечает за развитие компетенций в таких направлениях как экосистема Hadoop, PostgreSQL, GreenPlum, а также BI‑инструментах Qlik, Apache SuperSet и др.

© Habrahabr.ru