Я адаптирую инструмент веб-анализа, чтобы использовать Vertica как БД. У меня настоящие проблемы optimizing joins. Я попытался создать предварительные прогнозы для некоторых моих запросов, и, хотя это делало запросы быстрыми и быстрыми, это замедляло загрузку данных в таблицу фактов в обход.
Простой INSERT INTO ... SELECT * FROM, который мы используем для загрузки данных в таблицу фактов из промежуточной таблицы, составляет от 5 секунд до 20+ минут.
В связи с этим я отбросил все предварительные прогнозы и попытался использовать конструктор баз данных для проектирования запросов, специфичных для конкретного запроса, но этого недостаточно. Даже с этими проекциями простое соединение занимает ~ 14 секунд, что занимает около 1 секунды с предварительным проецированием.
Мой вопрос заключается в следующем: нормальна ли предварительная проекция для медленной вставки данных, а если нет, что может быть виновником? Если это нормально, то это пробная пробка для нас и есть ли другие методы, которые мы могли бы использовать для ускорения соединений?
Мы запускаем Vertica в кластере 5 node, каждый node имеет 2 x четырехъядерный процессор и 32 ГБ памяти. Таблицы в моем примере запроса имеют 188 843 085 и 25 712 878 строк соответственно.
Выход EXPLAIN выглядит следующим образом:
EXPLAIN SELECT referer_via_.url as referralPageUrl, COUNT(DISTINCT sessio
n.id) as visits FROM owa_session as session JOIN owa_referer AS referer_vi
a_ ON session.referer_id = referer_via_.id WHERE session.yyyymmdd BETWEEN
'20121123' AND '20121123' AND session.site_id = '49' GROUP BY referer_via_
.url ORDER BY visits DESC LIMIT 250;
Access Path:
+-SELECT LIMIT 250 [Cost: 1M, Rows: 250 (STALE STATISTICS)] (PATH ID: 0)
| Output Only: 250 tuples
| Execute on: Query Initiator
| +---> SORT [Cost: 1M, Rows: 1 (STALE STATISTICS)] (PATH ID: 1)
| | Order: count(DISTINCT "session".id) DESC
| | Output Only: 250 tuples
| | Execute on: All Nodes
| | +---> GROUPBY PIPELINED (RESEGMENT GROUPS) [Cost: 1M, Rows: 1 (STALE
STATISTICS)] (PATH ID: 2)
| | | Aggregates: count(DISTINCT "session".id)
| | | Group By: referer_via_.url
| | | Execute on: All Nodes
| | | +---> GROUPBY HASH (SORT OUTPUT) (RESEGMENT GROUPS) [Cost: 1M, Rows
: 1 (STALE STATISTICS)] (PATH ID: 3)
| | | | Group By: referer_via_.url, "session".id
| | | | Execute on: All Nodes
| | | | +---> JOIN HASH [Cost: 1M, Rows: 1 (STALE STATISTICS)] (PATH ID:
4) Outer (RESEGMENT)
| | | | | Join Cond: ("session".referer_id = referer_via_.id)
| | | | | Execute on: All Nodes
| | | | | +-- Outer -> STORAGE ACCESS for session [Cost: 463, Rows: 1 (ST
ALE STATISTICS)] (PUSHED GROUPING) (PATH ID: 5)
| | | | | | Projection: public.owa_session_projection
| | | | | | Materialize: "session".id, "session".referer_id
| | | | | | Filter: ("session".site_id = '49')
| | | | | | Filter: (("session".yyyymmdd >= 20121123) AND ("session"
.yyyymmdd <= 20121123))
| | | | | | Execute on: All Nodes
| | | | | +-- Inner -> STORAGE ACCESS for referer_via_ [Cost: 293K, Rows:
26M] (PATH ID: 6)
| | | | | | Projection: public.owa_referer_DBD_1_seg_Potency_2012112
2_Potency_20121122
| | | | | | Materialize: referer_via_.id, referer_via_.url
| | | | | | Execute on: All Nodes
Чтобы ускорить соединение:
Разделите таблицу сеансов как разделяемую на столбец "yyyymmdd". Это позволит обрезать разделы
Добавьте условие на столбец "yyyymmdd" на _referer_via_ и разделите его на нем, если это возможно (скорее всего, нет)
есть столбец site_id как возможно близко к началу порядка по списку в используемой (супер) проекции сессии
И наличие большего количества узлов в кластере поможет.
Мой вопрос заключается в следующем: нормальна ли предварительная проекция для медленной вставки данных, а если нет, что может быть виновником? Если это нормально, то это пробная пробка для нас и есть ли другие методы, которые мы могли бы использовать для ускорения соединений?
Я предполагаю, что затронутая сумма будет различаться в зависимости от наборов данных и структур, с которыми вы работаете. Но, поскольку это переменная, которую вы изменили, я считаю, что можно с уверенностью сказать, что предварительная проекция вызывает медленность. Вы получаете время запроса за счет времени вставки.
Кто-то, пожалуйста, исправьте меня, если что-то из следующего неверно. Я собираюсь по памяти и информации, полученной с разговорами с другими.
Вы можете ускорить свои объединения без предварительного объединения нескольких способов. В этом случае идентификатор реферера. Я считаю, что если вы сегментируете свои прогнозы для обеих таблиц с помощью предиката соединения, который поможет. Все, что вы можете сделать для фильтрации данных.
Глядя на ваш план объяснения, вы делаете хеш-соединение вместо объединения слияния, которое вы, вероятно, захотите посмотреть.
Наконец, я хотел бы узнать через план объяснения или через системные таблицы, если ваш запрос на самом деле использует прогнозы, которые рекомендовал конструктор баз данных. Если нет, явно укажите их в своем запросе и посмотрите, помогает ли это.
У вас, похоже, много СТАТИСТИЧЕСКОЙ СТАТИСТИКИ. Отвечать на статистику STALE важно. Потому что именно поэтому ваши запросы медленные. Без статистики о базовых данных оптимизатор запросов Vertica не может выбрать лучший план выполнения. А ответ на статистику STALE только улучшает производительность SELECT, не обновляя производительность.
Если вы регулярно обновляете свои таблицы, помните, что в VERTICA нужно учитывать дополнительные вещи. Пожалуйста, проверьте ответ, который я отправил на этот вопрос . Надеюсь, это поможет улучшить скорость обновления.
Изучите настройки AHM, как описано в этом ответе. Если вам не нужно будет выбирать удаленные строки в таблице позже, часто бывает неплохо не поддерживать их. Есть способы сохранить только последнюю версию данных. Или вручную удалите удаленные данные. Дайте мне знать, как это происходит.
Я думаю, что ваш запрос может использовать некоторые более явные. Также не используйте Devil BETWEEN Попробуйте следующее:
EXPLAIN SELECT
referer_via_.url as referralPageUrl,
COUNT(DISTINCT session.id) as visits
FROM owa_session as session
JOIN owa_referer AS referer_via_
ON session.referer_id = referer_via_.id
WHERE session.yyyymmdd <= '20121123'
AND session.yyyymmdd > '20121123'
AND session.site_id = '49'
GROUP BY referer_via_.url
-- this `visits` column needs a table name
ORDER BY visits DESC LIMIT 250;
Я скажу, что я очень озадачен тем, почему вы должны использовать тот же DATE с BETWEEN, возможно, захотите изучить это.
это мой взгляд, исходящий из академического опыта работы с базами данных столбцов, включая Vertica (недавний выпускник PhD в системах баз данных).
Blockquote Мой вопрос таков: нормальна ли предварительная проекция для медленной вставки данных, а если нет, что может быть виновником? Если это нормально, то это пробная пробка для нас и есть ли другие методы, которые мы могли бы использовать для ускорения соединений? Blockquote
Да, обновление прогнозов происходит очень медленно, и вы должны идеально делать это только в больших партиях, чтобы амортизировать стоимость обновления. Основная причина заключается в том, что каждая проекция представляет собой другую копию данных (каждого столбца таблицы, который является частью проекции).
В одной строке вставки требуется добавить одно значение (один атрибут) к каждому столбцу в проекции. Например, для одной строки в таблице с 20 атрибутами требуется не менее 20 обновлений столбцов. Чтобы все ухудшилось, каждый столбец сортируется и сжимается. Это означает, что для вставки нового значения в столбец требуется несколько операций над большими фрагментами данных: чтение данных/распаковка/обновление/сортировка/сжатие данных/запись данных назад. Vertica имеет несколько оптимизаций для обновлений, но не может полностью скрыть затраты.
Проецирование можно рассматривать как эквивалент многоколоночных индексов в традиционном хранилище строк (MySQL, PostgreSQL, Oracle и т.д.). Потенциал прогнозов по сравнению с традиционными индексами B-Tree заключается в том, что их чтение (использование их для ответа на запрос) намного быстрее, чем использование традиционных индексов. Причины кратки: нет необходимости обращаться к данным о головке, как к некластеризованным индексам, меньшему размеру из-за сжатия и т.д. В обратном направлении их сложнее обновить. Компромиссы...