Версия сайта для слабовидящих
Что такое SQL-оператор VACUUM, зачем эта команда нужна в Greenplum и как она работает. Разбираемся с таблицами системного каталога и тонкостями ускорения SQL-запросов в самой популярной MPP-СУБД.
Что такое сборка мусора в Greenplum и PostgreSQL
Напомним, в объектно-реляционной базе данных PostgreSQL, на которой основана MPP-СУБД Greenplum, о чем мы писали здесь, есть оператор VACUUM для сборки мусора. Подобно Garbage Collector в JVM, VACUUM высвобождает пространство, занимаемое удаленными или устаревшими кортежами, которые физически не удалены из таблицы. Рекомендуется периодически необходимо команду VACUUM для часто изменяемых таблиц. Этот оператор обрабатывает все таблицы и материализованные представления в базе данных, на очистку которых текущий пользователь имеет право. Однако, команда VACUUM не применяется внутри блока транзакции.
Необходимость применения очистки в Greenplum обусловлена MVCC-моделью блокировки/параллелизма (Multi Version Concurrency Control), где модули записи не блокируют считыватели во время операций обновления и удаления. Новые версии строк создаются во время операций по обработке данных, а не при физическом удалении или обновлении на месте исходной версии строки. Старые версии строк продолжают занимать физическое пространство, пока не будет вызвана команда очистки для освобождения физических слотов, используемых старыми версиями строк. Если очистка каталога выполняется в соответствующее время, то вновь вставленные строки будут повторно использовать пространство, освобожденное последней очисткой. Если свободных слотов для повторного использования нет, новые строки будут вставлены в конец таблицы каталога. Это увеличивает размер таблицы каталога, снижая производительность обработки данных.
В Greenplum, как и в PostgreSQL для хранения метаданных схемы, т.е. информации о таблицах и столбцах, а также других служебный сведений, используются системные каталоги. Хотя фактически они представляют собой обычные таблицы, которые можно удалить, пересоздать, добавить столбцы, изменить и добавить строки, модифицировать системные каталоги вручную не рекомендуется. Например, команда создания базы данных CREATE DATABASE вставляет строку в каталог pg_database.
В Greenplum размер каталога увеличивается, когда на страницах нет свободных слотов для повторного использования новыми строками данных, что происходит при выполнении DDL-команд, таких как создание и удаление объектов базы данных, особенно с использованием утилиты загрузки из внешних источников gpload. Таблицы каталога имеют многочисленные индексы B-дерева для оптимизации SQL-запросов. Если данных в каталоге становится слишком много, индексы тоже растут, снижая производительность критически важных запросов к каталогу, когда в плане запроса используется сканирование таблицы, а не поиск по индексу. Поэтому рекомендуется регулярно выполнять переиндексацию каталога. Хотя это и не устраняет потребность в полной очистке каталога, но может отсрочить эту ресурсоемкую процедуру до более подходящего момента. Подробнее про индексацию таблиц в Greenplum мы писали здесь.
При том что, рост каталога не останавливает выполнение SQL-запросов и не приводит к сбою экземпляра Greenplum, он может постепенно ухудшать работу базы данных в следующих аспектах:
- замедленное выполнение команд каталога во время сеанса psql, таких как \dt, \dv или \d+;
- увеличение времени подготовки или компиляции SQL-запроса;
- высокая загрузка ЦП на главном хосте Greenplum, поскольку планы доступа к каталогу заменяют сканирование таблицы быстрым поиском по индексу.
Рекомендуется отслеживать и разрешать ситуации, когда раздувание каталога превышает предопределенный порог, например, если фактическое количество страниц стало больше ожидаемого в 2 раза или размер индекса каталога превысил 25% размера его базовой таблицы. Обычно самыми большими таблицами в Greenplum являются pg_class и pg_attribute, где описаны все таблицы и информация об их столбцах соответственно. Наилучшей практикой управления средой Greenplum является исторический мониторинг системной информации о таблицах каталога, такой как схема, имя таблицы, количество строк, количество страниц и размер таблицы. Это поможет отслеживать, когда таблица каталога чрезмерно увеличилась, чтобы оптимизировать ETL-операции и свести к минимуму ее раздувание. Далее рассмотрим особенности применения команды очистки в Greenplum.
Как работает команда VACUUM
Хотя применение команды VACUUM к внешним (external и foreign) таблицам в Greenplum не допустимо, этот оператор имеет множество опций для настройки параметров очистки. В частности, без опции FULL этот оператор только высвобождает пространство и делает его доступным для повторного использования. Но такая форма команды может работать параллельно с обычными операциями чтения и записи таблицы, т.к. не требует исключительной блокировки. При этом освобождённое место не возвращается операционной системе, а просто становится доступным для размещения данных этой же таблицы. Команда VACUUM FULL работает медленнее и запрашивает блокировку в режиме эксклюзивного доступа (ACCESS EXCLUSIVE) для каждой обрабатываемой таблицы, перезаписывая всё содержимое таблицы в новый файл на диске, чтобы вернуть неиспользованное пространство операционной системе.
PostgreSQL имеет отдельный необязательный серверный процесс, называемый демоном автоочистки (autovacuum daemon), целью которого является автоматизация выполнения команд VACUUM и ANALYZE. MPP-СУБД Greenplum позволяет этой системной службе выполнять операции VACUUM только в базе данных шаблона базы данных template0, к которой запрещены подключения. Демон автоочистки выполняет операции VACUUM над template0 для управления идентификаторами транзакций (XID) и помогает избежать проблем с дублированием идентификаторов транзакций в template0. Таким образом, очистка в пользовательских базах данных позволяет управлять идентификаторами транзакций в них.
Применение команды VACUUM к таблицам Greenplum, оптимизированным для добавления, является поэтапным, включая сжатие:
- сперва очищаются индексы таблиц;
- затем по очереди сжимается каждый файл сегмента;
- очищаются вспомогательные отношения;
- обновляется статистика.
В каждом сегменте видимые строки копируются из текущего файла сегмента в новый файл сегмента, а затем планируется удаление текущего файла сегмента, чтобы сделать доступным новый. Обычная команда VACUUM для AO-таблицы в Greenplum позволяет сканировать, вставлять, удалять и обновлять таблицу во время сжатия файла сегмента. При этом на короткое время применяется блокировка монопольного доступа, чтобы удалить текущий файл сегмента и активировать новый файл сегмента.
Для AO-таблиц команда очистки требует достаточного свободного места на диске для размещения нового файла сегмента. Если отношение скрытых строк к общему количеству строк в файле сегмента меньше порогового значения (10 по умолчанию), файл сегмента не сжимается. Пороговое значение можно настроить с помощью параметра конфигурации сервера gp_appendonly_compaction_threshold. Впрочем, полная очистка (VACUUM FULL) игнорирует это пороговое значение и перезаписывает файл сегмента независимо от заданного коэффициента. VACUUM можно отключить для AO-таблиц с помощью параметра конфигурации сервера gp_appendonly_compaction.
Команда очистки узнает, какие строки нужно удалить, обращаясь к карте свободного пространства (FSM, Free Space Map), которая отслеживает расположение строк, пространство которых может быть освобождено обратно в ОС. Рекомендуется создать скрипт для очистки каталога Greenplum и регулярно планировать его выполнение с помощью cron, AirFlow или другого планировщика заданий, например, autosys. Обычно рекомендуется делать очищать каталог ежедневно не менее 2-х раз, например, до начала бизнес-обработки и после основных ETL-операций.
VACUUM вызывает существенное увеличение трафика ввода-вывода, что может снизить производительность других активных сеансов. А с опцией полной очистки это становится еще заметнее. Будучи очень ресурсоемкой, команда полной очистки (VACUUM FULL) обычно занимает много времени для больших распределенных таблиц Greenplum. Поэтому она не рекомендуется для повседневного использования, но может быть полезна в особых случаях. Например, после удаления большинства строк в таблице, нужно уменьшить ее физически, чтобы занимать меньше места на диске и ускорить сканирование. VACUUM с опцией FULL обычно сжимает таблицу лучше, чем без этого параметра. В качестве альтернативы VACUUM FULL можно удалить старую таблицу с оператором DROP и заново создать ее через CREATE TABLE AS.
В случае полной очистки следует перевести базу данных в ограниченный режим, используя утилиту gpstop, чтобы избежать любого взаимодействия с пользователем во время выполнения этого процесса, т.к. его прерывание может привести к повреждению каталога. В качестве наилучшей практики следует планировать период регулярного обслуживания раз в квартал, чтобы полностью очистить каталог. Очистка небольшого каталога ( до 200 ГБ) занимает от 15 до 30 минут, а полная очистка 300 и более ГБ может занять от 45 до 90 минут. Большую часть этого времени занимает работа с таблицей pg_attribute.
Рекомендуется почаще очищать активные базы данных, к примеру, каждую ночь, чтобы удалить ненужные строки. Также есть смысл применить команду VACUUM ANALYZE в Greenplum после добавления или удаления большого количества строк в таблице, чтобы обновить системные каталоги результатами всех последних изменений и помочь оптимизатору запросов лучше их планировать. Таким образом, сборка мусора, как и любая уборка – это рутинная операция, которую надо выполнять регулярно. Упреждающий мониторинг и обслуживание каталога позволит Greenplum работать с максимальной производительностью. Любое необходимое обслуживание каталога может быть запланировано в рамках обычных запланированных рабочих окон, а не как непредвиденные чрезвычайные ситуации.
Освойте администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
