Ускоряем чтение в слое DDS DWH
В статье предлагаются некоторые модельно-технические варианты ускорения чтения данных больших таблиц в слое DDS DWH (SCD2).
Тезисы
Оптимизация соединения больших таблиц дает значимый эффект, когда можно:
• исключить redistribute motion,
• исключить full scan хотя бы одной таблицы (выйти на узкую выборку),
• исключить вычисления, предшествующие фильтрации.
Если данные хранятся в грубо организованной структуре (толстые партиции) и не упорядочены (Greenplum), то узкая выборка к такой таблице может легко превратиться в full scan, даже если вы используете индексные таблицы.
SCD4
Выбор актуальных версий данных – это весьма популярный фильтр в аналитических запросах. Выделение актуальных данных позволяет ускорить и упростить запросы пользователей.
Решение – сделайте у таблицы верхний уровень из двух партиций:
• current – актуальные версии сущностей,
• history – исторические версии сущностей.
Особенность подхода – в общем случае нельзя просто заменить данные отдельного дня в истории (можно, но при определенных условиях, т.е. не всегда). Если возникнет потребность перезагрузить данные, то нужно ...
1. Восстановить состояние актуальной партиции (из данных исторической партиции), существовавшее до перегружаемого дня.
2. Скорректировать историческую партицию.
3. Загрузить данные от целевого дня до текущего.
Простая, но медленная альтернатива SCD4 – делать UPDATE над предметной таблицей + INDEX.
» При наличии B-Tree индекса вставка может катастрофически деградировать.
» Оптимизатор может не всегда подключать индекс.
» UPDATE разрушает эффект zone maps на уровне блоков, получаемый от вставки с сортировкой.
Техническая реализация
Таблицы
Предметная таблица с двумя партициями (current/history) и атрибутами:
- идентификатор (ключ) сущности (key),
- партиционный признак актуальной записи,
» Рекомендуется добавить в технику дату окончания актуальности и использовать её в качестве партиционного признака. - timestamp загрузки (created_at).
Вспомогательная таблица – реестр удалённых версий (key/created_at) с таким же ключом дистрибуции, как и у предметной таблицы. Строчная таблица с индексом.
Принципиальные моменты
Используется INSERT-only логика для загрузки данных.
Потребителю нужно давать доступ к предметной таблице через view, в котором будут исключены (LEFT JOIN) старые версии.
Удаление старых версий из актуальной партиции и отметок из реестра удалённых версий рекомендуется делать в фоновом режиме, в спокойное время, ограниченными порциями, одним оператором (используйте RETURNING).
Для актуальной партиции рекомендуется регулярный VACUUM, а вот исторической партиции он не нужен совсем.
Если в реестре удалённых версий не осталось записей, то перед загрузкой рекомендуется TRUNCATE (для полноценного высвобождения дискового пространства).
Загрузка
- Создайте временную (TEMPORARY) таблицу с новыми и старыми данными (LEFT JOIN).
• Старт транзакции - Из временной таблицы вставьте в историческую партицию старые версии измененных сущностей. В этом же операторе (используйте RETURNING) вставьте ключи (key/created_at) в реестр удалённых версий.
- Из временной таблицы вставьте в актуальную партицию новые версии сущностей.
• Завершение транзакции
Окончание актуальности
Добавление в запись даты окончания актуальности версии даст возможность потребителю упростить запросы и значительно их ускорить. Во всех запросах, где есть присоединяемая таблица, пользователю необходимо вычислять эту дату, поэтому разумно предоставить потребителю такую услугу заранее.
Эту задачу очень удобно совмещать с техникой выделения актуальных данных, но есть и другие варианты ...
- UPDATE над предметной таблицей.
- Построение оптимизирующей таблицы (key, from, to) рядом с основной.
Карта актуальности
В запросе вы выбираете небольшой процент данных из большой таблицы А и соединяете её с большой таблицей Б по ключу и полю с опорной датой. Оптимизатор не знает, где в таблице Б искать нужные данные, что приводит к full scan.
Как оптимизировать запрос?
Вы соединяете 2 таблицы целиком, но отсылка к таблице Б используется редко (необязательный атрибут).
Как оптимизировать запрос?
В этом случае надо сделать промежуточную таблицу Х (или представление):
- Поля: опорная дата (отгрузки или продажи) и внешний ключ из таблицы А, дата начала актуальности (actual_from) строки из таблицы Б.
- Партиционирование по опорной дате.
В таблице Х будет такое же количество строк (условно), как и в таблице А, а если внешний ключ необязательный или кортеж (дата + ключ) повторяется, то строк будет меньше. Эффект в том, что используется прямое (=) соединение между таблицами, поэтому в таблицах X и Б будут читаться только целевые партиции.
Чем тоньше партиции таблиц X и Б, тем меньше будет чтение нецелевых данных. Актуально для БД, которые не поддерживает кластеризацию, zone maps, bloom filter.
Решение очень узкое, поскольку настроено на конкретный кортеж (поле с опорной датой + внешний ключ), к тому же дает одностороннюю связь между таблицами.
Редистрибуция
В запросе вы выбираете данные из большой таблицы А и соединяете её с большой таблицей Б. Из таблицы Б выбирается всего несколько атрибутов. Фактически, в обеих таблицах выполняется full scan. Как оптимизировать запрос?
Нужно сделать так, чтобы нужные атрибуты из соответствующих записей таблицы Б оказались на той же ноде, что и записи таблицы А (исключаем Redistribute Motion). Для этого нужно создать вторичную таблицу Х с соответствующим ключом дистрибуции (и нужными атрибутами). Заполнить таблицу можно так ...
Здесь важно обмануть оптимизатор (random), чтобы получить корректный локальный ключ дистрибуции для каждой ноды.
В решение можно добавить дату окончания актуальности или сделать его заточенным под конкретное поле с опорной датой, что будет полезно для узких выборок. Таблица Х может быть очень короткой, если она создается с учетом фильтра, применяемого к таблице Б.
Нормализация данных
Грамотное вертикальное разделение таблицы может ускорить запросы и загрузку, так как позволит хранить данные с меньшим количеством повторов (нормализация SCD2).
Сбой загрузки фрагмента может привести к тому, что вы покажете потребителю актуальную версию сущности в неконсистентном состоянии. Если это критично, то для решения проблемы используйте PIT-таблицу (обновляется после успешной загрузки всех потоков всех фрагментов таблицы). С помощью PIT-таблицы можно также восстановить целевую таблицу в view (что удобно для потребителя).
Для сильно фрагментированных таблиц целесообразно реализовать каскад PIT-таблиц (т.е. нормализовать таблицу).
Рефлексия
Большие данные учетных систем это история не про красивые модели (Data Vault, Anchor Modeling), это история про оптимизацию, а оптимизация определяется профилем данных и логикой потребления.
В жизненном цикле сущности мало состояний?
Особо тяжелый атрибут стоит выделить (если он не является причиной новых состояний, если он обязательный или возникает на ранних стадиях ЖЦ сущности).
» Лучше это сделать ещё в источнике.
В жизненном цикле сущности часто меняется один атрибут или группа?
Выделите их в отдельную таблицу.
» И это лучше сделать ещё в источнике.
Внешний ключ ведет к большой таблице?
Возможно стоит задействовать карту актуальности или редистрибуцию.
Хотите стандартизацию, универсальность, модель?
Цена: повышенный расход дискового ресурса, снижение производительности и кратный рост процессорного ресурса, отсутствие удобства использования.
Банальности
Не нужно выносить внешний ключ в отдельную таблицу (link/tie), оставьте атрибут в основной таблице.
» Внешние ключи редко меняются.
Старайтесь не использовать строковые ключи.
Подчиненные сущности распределяйте по ключу родительской сущности.