Как вы думаете, может ли хорошо работать веб-панель, которая напрямую взаимодействует с Amazon Redshift и показывает таблицы, диаграммы, числа - статистику? Мы считаем, что это возможно, если панелью управления пользуется несколько пользователей. Поскольку это был наш случай, мы решили попробовать.

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

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

В этом сообщении в блоге я расскажу вам, что я сделал для значительного повышения производительности системы. В общем, я сделал три шага:

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

Сначала давайте посмотрим, как все это выглядело раньше. Мы используем Kimball Start Schema с двумя таблицами фактов и несколькими таблицами измерений. Каждая из таблиц измерений имеет суррогатный ключ в качестве первичного ключа и естественный ключ в качестве свойства. Среди других преимуществ стоит упомянуть отслеживание исторических данных и различных событий, хранящихся в отдельных таблицах.

Основными проблемами медленных запросов были:

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

Предварительно агрегированная таблица

Предварительно агрегированная таблица - это очень примитивная форма витрины данных, но в моем случае она достаточно хороша. Основные характеристики стола:

  • данные взяты из обеих таблиц фактов со столбцом для различения данных - UNION не требуется,
  • данные агрегированы для обеспечения минимально необходимой детализации,
  • включены последние названия элементов размеров - никаких оконных функций не требуется. Этот шаг обеспечил повышение производительности примерно на 50%. Звучит неплохо, не правда ли? Есть ли недостатки у такого решения? Конечно - использование хранилища. Предварительно агрегированная таблица использует 10% всего хранилища таблиц Redshift. Это много? По-разному. Наша база данных по-прежнему намного ниже любых пороговых значений хранилища, поэтому в моем случае это вполне приемлемо.

Стиль и ключ распространения

Следуя инструкциям Amazon: Выберите столбец с высокой мощностью в отфильтрованном наборе результатов, было довольно очевидно, что выбрать дату события в качестве ключа распределения, поскольку существует только одна таблица и никаких объединений не требуется.

Ключ сортировки

Можно выбрать между составным ключом сортировки и ключом сортировки с чередованием и выбрать один или несколько столбцов для составления ключа. Лучше всего это описать в Амазонке.

Составной ключ состоит из всех столбцов, перечисленных в определении ключа сортировки, в порядке их перечисления. Составной ключ сортировки наиболее полезен, когда фильтр запроса применяет условия, такие как фильтры и объединения, которые используют префикс ключей сортировки. Преимущества в производительности составной сортировки уменьшаются, когда запросы зависят только от вторичных столбцов сортировки без ссылки на первичные столбцы. СОСТАВ - это тип сортировки по умолчанию.

Сортировка с чередованием дает равный вес каждому столбцу или подмножеству столбцов в ключе сортировки. Если в нескольких запросах используются разные столбцы для фильтров, то часто можно повысить производительность этих запросов, используя стиль сортировки с чередованием. Когда запрос использует ограничительные предикаты для столбцов вторичной сортировки, сортировка с чередованием значительно повышает производительность запроса по сравнению с составной сортировкой.

Хотя документация Amazon является хорошим источником знаний, если вы действительно хотите понять идею чередующихся и составных ключей сортировки, прочтите эту замечательную статью, состоящую из двух частей.

Помня об этом, я решил попробовать два разных подхода:

  • дата как ключ сортировки - это единственный столбец, который появляется в каждом запросе,
  • ключ сортировки с чередованием, состоящий из даты и двух столбцов, чаще всего встречающихся в предложении WHERE.

Я выполнил пакет запросов (90 наборов из 10 запросов, используемых панелью мониторинга с различными предложениями WHERE) к таблицам с ключами сортировки, определенными, как указано выше. Вот результаты:

Самое простое решение оказалось лучшим. Он дает примерно на 50% лучшую производительность, чем отсутствие ключа сортировки, и на 20% лучше, чем ключ сортировки с чередованием.

Прочие мелочи

Еще одна статья, которая мне очень помогла, взята из блога AWS по большим данным и описывает десять методов настройки производительности.

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

Проблемы, которые я нашел полезными в моем случае: Неправильная кодировка столбцов (проблема №1) - следуя инструкциям, я применил правильную кодировку столбцов. Таблицы с очень большими столбцами VARCHAR (проблема № 5) - все столбцы VARCHAR имели длину по умолчанию 256 Б, в то время как фактически самый длинный столбец требовал не более 50 Б, а примерно половина столбцов была короче 10 Б.

Резюме

Применение всех вышеупомянутых улучшений сделало систему более чем на 80% быстрее, чем раньше. Должен ли я гордиться собой за такое большое улучшение или, скорее, винить себя за то, что не применил все это при проектировании базы данных? Я лично считаю, что есть вещи, которые вы не можете предвидеть, и разработка - это процесс проектирования, внедрения и постоянного улучшения.

Агнешка Ольшевска

Инженер-программист @ Bright Inventions

"Электронное письмо"

Несколько шагов к быстрому красному смещению было опубликовано 6 октября 2017 г.

Первоначально опубликовано на brightinventions.pl.