Как не грабить память, не пытать диск, не мучать кластер. Или делать все это всего одним запросом на Impala к Hadoop.
Среди задач аналитиков данных, в рамках которых необходимо иметь дело с большими объемами однотипных данных, выделяются задачи построения витрин данных, автоматизации процессов сбора и обработки данных. Многие аналитики используют различные реляционные базы данных, в таблицах которых хранятся огромные объемы информации, агрегация и доступ к которым может занимать долгое время, поэтому правильное составление и оптимизация запросов к этим таблицам становится критически необходимым фактором для работы аналитиков, инженеров данных и data scientist.
Автоматизация бизнес-процессов приносит много пользы, она позволяет
Упорядочить регулярные задачи.
Минимизировать человеческий фактор.
Четко разделить зоны ответственности внутри процессов.
Держать под контролем детали процесса.
Создать единую ИТ-инфраструктуру с различными правами доступа.
Экономить время и средства на управлении процессом.
В совокупности задачи автоматизации затрагивают экономию — одну из ключевых целей, причем экономиться могут как ресурсы так и время на выполнение задач. Для решения этих проблем не всегда нужно ломать существующие процессы и строить их заново, достаточно их оптимизировать. Так и в работе аналитика, обращающегося к базам данных, есть те инструменты, оптимизация которых принесет пользу, среди них - оптимизация запросов.
Пытки памяти
Моя история с пытками памяти на диске началась задолго до эры ChatGPT, это был код формирования витрины данных из далекого прошлого, еще тех времен, когда все ходили в офис и даже не мечтали об удаленной работе. Своими глазами я видел манускрипт с требованиями на автоматизацию, датированный 2020 годом нашей эры. Так началось мое знакомство с неоптимизированным скриптом на тысячу строк sql-кода.
Ничто не предвещало масштабной битвы за ресурсы и память. Запрос отрабатывал всего за час с небольшим... с небольшим спилом на диск в объеме 6 Терабайт!
Доработки процесса шли своим чередом, шагая тернистой дорогой CI\CD. Чем ближе казалась цель, тем больше полей требовала витрина, тем больше новых источников использовалось в запросе: транзакции, балансы, заявки, справочники, договоры... Казалось им не будет конца.
Методы оптимизации запросов
Теперь пора поделиться тактикой борьбы с неоптимизированными запросами. Этих тактик несколько, приемы контрнаступления должны быть эффективными и решать две основные задачи:
Минимизация времени на выполнение запроса
Минимизация используемой памяти.
Реализовывать эти задачи приходится когда в результате запроса выдается ошибка "Out of memory" или сам запрос отрабатывает слишком долго. Иногда ошибка не является блокирующей при выполнении запроса - в таком случае Impala обращается к общему дисковому пространству, что может негативно сказываться на работе всего кластера. Сама необходимость оптимизации не всегда является очевидной. Поэтому при разработке скриптов рекомендуется использовать дополнительные инструменты для контроля и мониторинга выполнения запроса.
Cloudera Manager является компонентом платформы данных Cloudera CDP. Это комплексное приложение для управления кластерами, оно обеспечивает видимость и контроль каждой части кластера CDH, повышая производительность и качество сервисов. Именно в Cloudera Manager можно обнаружить необходимость в оптимизации, например, по времени выполнения запроса Duration или по используемой дисковой памяти - Memory Spilled.
Для достижения поставленных целей следует использовать следующие приемы:
-
Сбор статистики
После создания или изменения таблиц необходимо собирать статистику - compute stats table_name. Это поможет Impala строить план запроса эффективнее.
-
Минимизация подзапросов
Рекомендуется уменьшить количество подзапросов. Часто подзапрос можно заменить на join с условием или иными средствами.
-
Партицирование таблиц
Партицирование по некоторому полю (часто дате\периоду) позволяет обработывать данные несколькими независимыми и параллельно выполняющимися потоками, что в итоге ускоряет работу по чтению данных.
-
Ограничение выборки (where ...)
Фильтрация данных является примером ограничения выборки, которая позволяет считывать не все данные, а только их часть. Условия на ограничение наиболее эффективны для полей партиционирования - в таком случае целые блоки таблиц не будут участвовать в запросе.
-
Материализация промежуточных результатов
Common Table Expression (CTE) —Обобщенное табличное выражение - результаты запроса, которые можно использовать множество раз в других запросах также как и подзапросы могут сильно влиять на производительность.
-
Минимизация затратных операций (group by, distinct, order by...)
Большое количество полей в группировке или сортировке - очень ресурсозатратно. Уменьшив число атрибутов, можно достичь меньшего потребления ресурсов.
-
Минимизация кол-ва join'ов в одном запросе
Чем больше таблиц участвует в запросе, тем дольше он отрабатывает. Разделение запроса на несколько частей с материализацией их результата и сбором статистики - это оптимальное решение, особенно если соединение по полям партицирования.
Комментарии (11)
ALexKud
25.11.2023 21:05Насчёт СТЕ - эта штука работает только на ближайший запрос и не всегда быстрее того же подзапроса. Это из моего опыта, когда пришлось переделывать. СТЕ это скорее синтетических сахар чем реально оптимизирующий код. Оптимизация возможна когда используется каскадное соединение СТЕ, когда последующий использует выборку предыдущего.
Akina
25.11.2023 21:05СТЕ это скорее синтетических сахар чем реально оптимизирующий код. Оптимизация возможна когда используется каскадное соединение СТЕ, когда последующий использует выборку предыдущего.
Есть ещё вариант, и тоже достаточно популярный. Это случай, когда во внешнем запросе результат CTE используется более одного раза.
Можно, конечно, дважды написать текст подзапроса - но сервер ни в жисть не сообразит, что это один и тот же подзапрос, и выполнит его дважды. Причём не сообразит он совершенно правильно, даже абсолютное совпадение текстов не гарантирует совпадения результата (например, если подзапрос использует пользовательские переменные).
EvgenyVilkov
25.11.2023 21:05-1Все рекомендации данные в статье скорее набор клише, которые можно применить абсолютно к любому движку или СУБД.
Забавно, но складывается впечатление что ни автор, ни активные участники дискуссии понятия не имееют как работает Impala.
Придется провести краткий ликбез
Impala действительно используется память, но только на первичное чтение блоков из файловой системы. Дальнейшие операции прекрасно уходят в spill
Память - самый ценный ресурс. Чем ее больше на кластере тем выше пропускная способность системы (больше запросов в еденицу времени)
То есть чтобы пропускная способность была выше нужно чтобы каждый запрос жрал как можно меньше памяти. Именно на это и на надо сосредоточится и этого достигать.
Как это сделать:
Помимо клишированного сбора сстатистики и прочей банальщины вроде "упрощайте запрос", нужно обязательно работать с фильтрами. Impala читает только те блоки который удовлетворяют условиям join и where. Лишнее читать она не будет (это свойство кстати делает ее самым быстрым MPP двжиком в прицнипе)!
Как этого добиться? Нужно "попросить" оптимизатор не спешить читать данные с файловой системы, а сперва проанализировать where и join, построить фильтры под эти операции. Далее Impala фильтры накладывает на паркетные индексы и просто не будет читать файлы и блоки в которых нет данных, удовлетворяющих условиям запроса. По сути надо уметь работать всего с друмя параметрами: время построения фильтра, количество фильтров которые оптимизатор может применить. По-умолчанию первый паметр всего 1 сек. Теперь представьте что у вас на входе запрос в котором 55 join операций и 20 условий where. Оптимизатор всего 1 секунду ожидает построения фильтров, естесвенно не дожидается всех возможных и начинает сканировать и загонять в hash все ваши 55 таблиц. Второй параметр - количество таких фильтров. По умлочанию он равен 10, что явно мало для запросов с большим количеством фильтров и соедиенений.
Применение этой простой "магии" позволяет на порядки(!) сократить объемы чтений, а значит и уменьшить утилизацию памяти и повысить пропускную способность системы. А еще, применение этой магии, позволяем вам тупо забвить на то есть у вас секции или нет, потому что Impala фильтрует блоки, а не секции. Да, конечно, вы получите выигрыш в производительности если патерн секционирования, совпадает с условием where , но лишь на время которое необходимо чтобы прочитать заголовки всех паркет файлов и проверить storage index, но не более того. Памяти от этого меньше расзодоваться не будет.
Идем далее. В зависимости от версии Impala у нее может быть доступен внутриузловой паралелеризм. По умлочанию он конечно не включен и любая операция (разве что кроме сбора статистики) выполняется в один поток на одном узле. Включение дает серьезный буст производительности.
Теперь как борятся с спиллом, помимо очевидных архитектурных решений (вынос спилла на быстрые диски). Ваша задача - дать такой объем памяти, при котором и спилла не будет (или он будет минимальный) и память вы лишнюю (не востребованную запросом) не зарезервируете, по сути лишив ее другие ожидающие запросы.
Оптимизатор тупой. Он не может предсказать сколько точно надо памяти чтобы не спиллить. Надо ему помогать. Вот как мы с командой работаем:
-собираем все профили запросов Impala. Чем дольше период тем лучше. Если есть возможность, то лучше за несколько недель или месяцев.
-парсим профили запросов и получает метрики работ
-обучаем модель. Она предсказываем сколько памяти надо выделить запросу, чтобы он не упал по ООМ, не спиллил на диск и не пережирал память.
-все запросы прогоняются через модель и она выдает параметр mem_limit
-перидочески модель переобучается. По сути она подстриаивается под текущий wokrload конкретного кластера и нагрузки
Применение такого подхода на кластере 1Пт с 2млн регламентных запросов Impala повысило пропускную способность на 30%. Это вам не секции нарезать! Это настроящий AI на практике.
Akina
Не знаю, как оно на уровне библиотек и фреймворков, но стОит опуститься на уровень SQL, и многое сказанное вызывает огромные сомнения.
Бывает и наоборот - избавление от подзапроса резко ухудшает показатели. Это как минимум два шаблонных случая - коррелированный WHERE EXISTS в объёмную таблицу и агрегат от связанных записей по другой таблице в списке вывода.
Обычно все СУБД идут в направлении уничтожения однозначной зависимости между использованием CTE и материализацией его результата.
А использование результата CTE в другом запросе - это вообще сказки какие-то. Нигде и никогда. Для этого представления имеются. И да - кое-где даже и материализованные.
Это с чего бы? Ту надо в первую очередь вести речь не о количестве полей в выражении, а о суммарном размере данных в этом выражении. 10 INTEGER против одного VARCHAR(256) куда как выгоднее, даже если не вспоминать про collation.
Это даже если не считать того простого факта, что мне НАДО получить эти поля.
А это вообще глупость какая-то. Индексы? не, не слышали..
Количество JOIN определяется только логикой требуемого результата. И оптимизация может лишь увеличить количество требуемых связываний.
Да кабы мне кто сказал "Ты получишь не то, что тебе надо, зато оптимальным путём!".. ох, и далёко ж ему будет идти...
sshikov
То что статья ни о чем, и рекомендации ничем не подкреплены - я с вами скорее согласен.
Только вы не забывайте, что речь идет не о знакомой вам реляционной СУБД, а об Impala. А это таки большая разница. Не всегда и не везде - но часто.
И даже сама постановка задачи меня вот лично сильно смущает:
В реальности на кластере обычно достаточно много ресурсов, и очень часто можно как раз уменьшить время на выполнение запросов путем распараллеливания выполнения на большее число ядер, и выделения большего объема памяти. То есть, ресурсов в сумме мы пожрем сильно больше - но вот время выполнения запроса при этом может сократиться.
Поэтому когда мне говорят, а давайте мы эти две величины оптимизируем, у меня сразу вопрос - а ничего, что они друг другу могут противоречить?
Akina
Я это прекрасно помню. И именно поэтому и начал с соответствующей оговорки.
Однако я убеждён, что оптимизация на нижнем уровне в подавляющем большинстве случаев более эффективна. Да, Impala обращается к серверу Hadoop - а на нём уже считай чистый SQL. И ускорение (ну, например, вдвое) на этом нижнем уровне гораздо более эффективно, потому как при неоптимизированном исполнении для получения той же производительности надо вдвое увеличить количество параллельных потоков - расход ресурсов не сравнить... Всё-таки такое распараллеливание - это экстенсивный путь увеличения производительности. Если, конечно, при распараллеливании не происходит принципиального изменения выполнения на нижнем уровне - например, когда меньший объём обрабатываемых в одном потоке данных может выполняться без материализации промежуточных результатов.
А если работа каждого отдельного потока оптимизирована? так и ресурсов пожрём сильно меньше, и производительность не упадёт. Избыток ресурсов не должен расслаблять.
Если пункт 1 выполняется на Hadoop, а пункт 2 на Impala - то всё вроде как ровно. И противоречия нет. Причём выполнение пункта 1 само по себе является одной из предпосылок для выполнения пункта 2.
sshikov
В каком смысле на хадупе чистый SQL?
Akina
Да, неверно выразился, конечно. Правильно сказать, что Hadoop находится между Impala и финальным слоем, работающим с данными. То есть ближе к СУБД и SQL, чем Impala.
sshikov
Все равно не понял. Импала (насколько я знаю, но я могу ошибаться) является сама SQL движком. Исполнение запросов - за ней. Метаданные - берет из Hive. Хранение - HDFS.
Ниже от хадупа используется только HDFS (ну и видимо Yarn). То есть, ниже импалы в этом случае как раз никакого SQL вообще нет. Да и слоев там особо уже нет - потому что импала не использует ни MR, ни Tez, т.е. те движки (не SQL), которые используются скажем Hive в похожей ситуации.
Ну во всяком случае, это я так все представляю, исходя из их документов "для менеджеров", потому что мы у себя импалу выбросили по случаю перехода с Cloudera, где она была, на свою сборку хадупа, где во-первых, импалы нет, а во-вторых, Hive и так ускорился в версии 3 достаточно сильно.
Akina
Статья в своих рекомендациях оперирует практически исключительно элементами SQL-языка - CTE, JOIN, WHERE, GROUP BY, ORDER BY.. то есть рассматривает именно финальные запросы, которые выполняются движком СУБД. Уж не знаю, как это выглядит на самом верху, в тексте того задания, которое получает Impala от клиента. И если в конце этого пути получается неэффективный SQL-код, то от оптимизаций на самом верху цепи изменений большого эффекта ждать не стОит. Даже если там есть какие-то эффективные оптимизации - скорее всего они останутся сравнимо эффективными в том случае, если финальный SQL-код будет более эффективным. А как итог - бОльший профит. Вот я и указываю на то, что рекомендации, относящиеся к финальному этапу обработки запроса, в значительной степени спорны. Не более.
Я уж не говорю о том, что рекомендации получать не то, что нужно, а то, что можно получить более эффективно, вообще с моей точки зрения противоречат здравому смыслу.
EvgenyVilkov
Impala от Hadoop ничего кроме файловой системе и HMS не надо. Даже больше, файловая система может быть и не HDFS, а простой S3. Переходить на Hive пусть даже 3 с Impala это все равно что с Ferrary на ВАЗ ))