Здравствуйте, rosencrantz, Вы писали:
R>У нас есть ежедневные перформанс тесты, которые проверяют какие-то сочетания каких-то параметров и от них есть выхлоп, но все возможные сочетания проверять конечно не получится — тупо очень много времени займёт, если каждую конфигурацию мучать хотя бы по 15 секунд.
Ну вот у нас похожая проблема, тольку у нас не Hibernate, а своя СУБД, но худшее, что происходит — это когда пользователи апгрейдятся после нового релиза и некий запрос, который ранее выполнялся быстро, теперь выполняется медленно. Самое смешное, что у пользователей запросов-то много и 100 запросов могут стать быстрее (или не измениться), но стоит одному стать существенно медленнее — как следует репорт в саппорт. По-человечески это совершенно понятно, но защищаться от такого очень сложно. Причем, бывает же и так, что старая версия движка выполняла какой-то конкретный запрос быстро не потому, что очень умная, а просто повезло (ну, например, статистика для неких предикатов работала плохо, но в каких-то случаях "угадывала" и получалось оптимальное дерево джойнов), а новой версии, с более умным оптимизатором, не повезло.
В общем случае защититься от такого на 100% нельзя (ну или я не знаю как). Что помогает:
* те самые перформанс тесты. У нас есть как синтетические бенчмарки, так и рандоминизированные датасеты реальных клиентов (ну т.е. структура данных их, а сами данные анонимизированы, как и константы в запросах). Не все такое позволяют, но кто-то позволяет.
* рандомизация и fuzzing. Я понимаю, что вы не можете тестировать все возможные сочетания *каждую ночь*, но можно самплировать запросы случайным (но детерминированным!) образом, тем самым повышая вероятность, что на длинном отрезке удастся отловить косяк в плане. Если что-то отловилось, этот запрос сохраняется и превращается в детерминированный тест на будущее.
* еще есть интересные подходы к созданию синтетических генераторов тестовых данных на основе статистики реальных данных, но это уже больше специфика тестирования именно оптимизатора запросов СУБД.
Здравствуйте, Sharov, Вы писали:
S>Зачем переписывать? Там же добавлять для connection'а добавлять sql код, который надо выполнить. Т.е. если S>идет манипуляция с какими-нибудь сущностями, то при открытия соединения или непосредственно запроса добавить S>какой-нибудь sql код. По идее любая приличная ORM это умеет.
Да, я нагуглил прикольное короткое решение через кастомный диалект Хайбернейта, но в долгосрочной перспективе мне больше подходит jOOQ для этих сценариев.
Я просто устал уже бороться с Hibernate. Одно поле где-то добавишь, внезапно быстрый селект превращается в необъяснимый N+1. Вешаешь fetch=lazy, всё равно подтягивает зачем-то. Через N+1. Мне эти данные вот в этом сценарии нафиг не нужны, блин. Начинаешь гуглить, там такие бодрые посты от инсайдеров хайбернейта: "вы могли удивиться, увидев что иногда fetch=lazy оказывается не lazy" — и дальше идёт прекрасное подробное объяснение почему так. Или там -to-many связь добавляешь, хайбернейт начинает паджинацию делать в памяти — вытягивает всю базу, все 500К записей и обрезает в памяти. Ну да, ворнинг в лог пишет, молодец. Для меня это просто не работает, хайбернейт — это какая-то нелепая уродливая технология, которая на ровном месте дарит кучу неприятных сюрпризов. Самое отвратительное — он не даёт абсолютно никаких инструментов для того, чтобы обнаруживать проблемы, пусть если не на этапе компиляции, то хоть с помощью инт тестов.
Вот у меня есть простая хотелка: я хочу, чтобы N+1 никогда не было. Вообще никогда, ни при каких обстоятельствах. Чтобы всё к херам падало вместо N+1 за 120 секунд в продакшне. Ключевые слова: "хрен", "внимательность", "трассировка" и "усидчивость". Я видел, что есть какие-то левые детекторы N+1, но меня реально вгоняет в депрессию: этот костыль для обнаружения N+1, этот костыль для детекта лишних джойнов. Давайте может вообще тогда снапшоты запросов снимать и хардкодить их в тесты? Ну потому что ведь проблемы лучше видеть явно, чем косвенно. Мне повезло — у меня есть перформанс тесты, я каждую глупость ловлю не позже, чем через 24 часа и это здорово работает с учётом того, что релизы мы делаем раз в неделю. Но меня задолбало уже. Я 10 лет использую этот грёбаный хайбернейт в десятке проектов, и чем дальше, тем больше нужно знать и помнить. Нет, это не то, как должна выглядить хорошая технология.
Ну у меня read-heavy сервис, я конечно оставлю Хайбернейт для PUT/DELETE сценариев. Но вот GET /things?skip=10&limit=20&filter=...&sortBy=qwerty&sortOrder=asc — ну нафиг, тут только jOOQ, только 100% предсказуемые запросы и возможность легко всё перевернуть с ног на голову, если потребуется. Hibernate здесь просто лишняя сущность.
Есть некий API доступа к данным, позволяющий указывать критерии фильтрации (по любому из N полей, по нескольким полям через and/or), сортировку (по любому из N полей) и паджинацию. По запросам к этом API строятся ORM-запросы (Java, Criteria builder), которые потом идут в Mysql. Запросы, конечно, получаются очень разными, а уж планы выполнения — так ещё разнее. Хочется чтобы любой запрос отрабатывал за 1 секунду при 10 параллельных юзерах. О тормозах мы часто узнаём от внешних потребителей. Показывают что они как хотят отфильтровать, мы воспроизводим, смотрим план, придумываем как сделать быстрее. Например индекс добавляем. Хочется придумать как отлавливать наибольшее число тормозных запросов как можно раньше — до того, как внешние потребители с этим столкнутся.
У нас есть ежедневные перформанс тесты, которые проверяют какие-то сочетания каких-то параметров и от них есть выхлоп, но все возможные сочетания проверять конечно не получится — тупо очень много времени займёт, если каждую конфигурацию мучать хотя бы по 15 секунд.
Приходит в голову написать "юнит тесты", которые бы проверяли как выглядит план выполнения запроса для каждой возможной конфигурации параметров, и убеждались бы, что там точно нигде никогда нет full scan например. Будут такие тесты иметь смысл? Как бы вы подошли к проблеме?
Здравствуйте, kl, Вы писали: kl>Но мне более интересно, можно ли динамически изменить порядок джойнов на этапе выполнения, если стало понятно, что статистика наврала. Ну типа есть отношения A, B, C, 3 условия джойна и выбрали порядок (A * B) * C. А потом выяснилось, что A*B выдает практически декартово произведение, и скорее всего надо было сначала джойнить с С. Я экспериментировал, но штука в том, что плохой план очень трудно "пофиксить" локальным образом: что-то переделал в одной части и это вызывает каскадные изменения в других местах. Внутри одного оператора конечно проще, но и импакт меньше.
Да, я думаю, что прямо порядок джойнов изменить не удастся. Кроме совсем уж вырожденных случаев.
Здравствуйте, rosencrantz, Вы писали: R>Приходит в голову написать "юнит тесты", которые бы проверяли как выглядит план выполнения запроса для каждой возможной конфигурации параметров, и убеждались бы, что там точно нигде никогда нет full scan например. Будут такие тесты иметь смысл? Как бы вы подошли к проблеме?
Прикольный вопрос. Постановка, имхо, правильная.
Понятно, что всего многообразия косяков вы не отловите, т.к.
а) реальный план будет отличаться от тестового из-за другой статистики
б) не знаю, как в MySQL, а в других СУБД зачастую "предварительный" план отличается от окончательного, т.к. предсказания статистики могут врать, и движок будет переключаться "на лету".
Проблему а) можно попытаться решить, подготовив как можно более "настоящую" тестовую БД. Т.к. реальные запросы к ней гонять необязательно, она может быть более-менее произвольно большой.\
На проблему б) я бы предварительно забил болт, просто подправив ожидания. Инструмент не будет вам 100% предсказывать все боттлнеки, но, по крайней мере, может отловить регрессию — скажем, кто-то поменял структуру таблиц или переделал индексы.
К проблеме я бы подошёл достаточно просто — прикрутил бы генерацию "explain select" для более-менее произвольного запроса. Возможны два подхода:
1. Методический: пишем по юнит-тесту на каждое интересующее нас сочетание параметров, выводим explain select, сравниваем с образцом, полученным разработчиком в момент сдачи фичи.
Если обнаружено различие — разбираемся, есть ли проблема с перформансом; если есть — то чиним. В любом случае принимаем результат за новый baseline.
2. Эвристически-механический: пишем один тест, который перебором порождает наборы параметров, запускает explain select, ищет в нём подозрительные места типа full scan.
Если будете делать — напишите результаты: очень интересно.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали:
R>Есть некий API доступа к данным, позволяющий указывать критерии фильтрации (по любому из N полей, по нескольким полям через and/or), сортировку (по любому из N полей) и паджинацию. По запросам к этом API строятся ORM-запросы (Java, Criteria builder), которые потом идут в Mysql. Запросы, конечно, получаются очень разными, а уж планы выполнения — так ещё разнее. Хочется чтобы любой запрос отрабатывал за 1 секунду при 10 параллельных юзерах. О тормозах мы часто узнаём от внешних потребителей. Показывают что они как хотят отфильтровать, мы воспроизводим, смотрим план, придумываем как сделать быстрее. Например индекс добавляем. Хочется придумать как отлавливать наибольшее число тормозных запросов как можно раньше — до того, как внешние потребители с этим столкнутся.
R>У нас есть ежедневные перформанс тесты, которые проверяют какие-то сочетания каких-то параметров и от них есть выхлоп, но все возможные сочетания проверять конечно не получится — тупо очень много времени займёт, если каждую конфигурацию мучать хотя бы по 15 секунд.
R>Приходит в голову написать "юнит тесты", которые бы проверяли как выглядит план выполнения запроса для каждой возможной конфигурации параметров, и убеждались бы, что там точно нигде никогда нет full scan например. Будут такие тесты иметь смысл? Как бы вы подошли к проблеме?
мы тестили перформанс но с другой стороны. а всё дело в том что на производительность сильно влияет какие паралельно выполняются запросы тоже. и нарваться на косяк с деградацией быстрее чем пропустить N+1 или table scan.
Запускали тесты с достаточно большим объемом данных и считали первофанс паралельных разных задач, заодно и количество дидлоков на N "операций"
причем собирали метрики по каждому конкурентному сценарию с SQL Server — кол-во запросов, колвичество IO, количество CPU на SQL Сервере и колво времени на SQL Сервере.
по метрикам сразу видно если есть косяк с неоптимальным выполнением в SQL — или IO завышено или CPU завышено или запросов слишком много (N+1 косяк)
В MySQL насколько я знаю таких метрик нельзя собрать для заданного контекста. можно только на весь экземпляр MySQL сервера.
Здравствуйте, Sinclair, Вы писали:
S>б) не знаю, как в MySQL, а в других СУБД зачастую "предварительный" план отличается от окончательного, т.к. предсказания статистики могут врать, и движок будет переключаться "на лету".
Именно "на лету", а не при следующем выполнении того же запроса? Если да, может у тебя есть ссылка на техническую статью, где можно почитать о подходах? Не важно, для какого языка запросов, главное чтоб cost-based оптимизатор был.
Я в свое время думал в этом направлении, но фундаментальная проблема в том, что к моменту, когда становится понятно, что оценки селективности плохие, движок уже выполнил слишком много работы, а самое главное — мог уже начать отдавать результаты клиенту (многие алгоритмы же лениво-потоковые, те же merge joins). После этого переключиться на другой план, не скомпрометировав корректность, очень сложно.
Гораздо проще закэшировать скорректированные оценки и использовать их при следующем выполнении того же (или структурно-схожего) запроса, получив более оптимальный план.
Или ты имеешь в виду, что СУБД могут эмпирически проверить оценки статистики, до начала выполнения запроса? Это грозит серьезным оверхедом когда статистика в порядке.
UPD: если что, с классической теорией типа Adaptive Query Processing я знаком, но обычно это работает на длинных, часто распределенных запросах, где можно себе позволить потерять секунды на переоптимизацию. Но все равно я бы почитал/послушал практический опыт.
Здравствуйте, Sharov, Вы писали:
kl>>Я в свое время думал в этом направлении, но фундаментальная проблема в том, что к моменту, когда становится понятно, что оценки селективности плохие, движок уже выполнил слишком много работы,
S>А что это такое? Это выбор (оценка выбора) индексов для запроса?
Ну вот представь у тебя есть джойн двух таблиц по условию X.a = Y.b. Оптимизатору надо примерно прикинуть, какой процент Декартова произведения этих таблиц удовлетворяет условию джойна. Это называется селективностью условия джойна. Если джойнов в запросе много, то фундаментальная задача оптимизатора — выбрать такой порядок джойнов, чтобы промежуточные результаты были минимальны (в реляционной алгебре обычный джойн, как и умножение в обычной алгебре, — операция ассоциативная, поэтому их можно выполнять в любом порядке).
Эти оценки всегда приблизательные, тк сбор статистики — штука недешевая (дажи несмотря на сэмплирование значений колонок). Если в БД активно писать данные, статистика зачастую будет отставать и соответственно врать оптимизатору. Если выполнять джойны в плохом порядке, то никакие индексы тебя не спасут, данные будут читаться быстрее и быстрее сожрут память.
Не найду сейчас кто это сказал, но есть известное изречение, что большинство ошибок современных cost-based оптимизаторов так или иначе сводятся к ошибкам в оценке селективности.
Здравствуйте, rosencrantz, Вы писали:
R>Приходит в голову написать "юнит тесты", которые бы проверяли как выглядит план выполнения запроса для каждой возможной конфигурации параметров, и убеждались бы, что там точно нигде никогда нет full scan например. Будут такие тесты иметь смысл? Как бы вы подошли к проблеме?
Звучит как овернормализованная база и развесистые SQL запросы. Я бы сделал архитектуру, в которой проблема обеспечить быструю фильтрацию и сортировку по любой колонке отсутствует или решается тривиально. Например, добавил Elasticsearch и выводил пользователям таблицы через него.
Если же необходимость жевать кактус неизбежна и заранее спроектировать структуру базы и индексы под всех хотелки пользователей невозможно, я бы перешел из проактивного к реактивному сценарию — обвешаться мониторингом и фиксить все медленные запросы по мере их появления и до жалоб бизнес-пользователей.
S> запретить создавать запросы по полям, на которые нет индекса; S> разрешить делать запрос по полям на которые есть индекс; S>
Ну это совсем работать не будет. Наличие индекса не означает, что он будет использоваться. Ну и в принципе отсутствие тоже не означает, что запрос будет тормозить
S>
S> включить slow_query_log в Mysql и мониторть запросы; S>
Это не очень улучшает ситуацию, т.к. чтобы увидеть тормоза, они должны сначала произойти. Увидим мы их сами или потребитель нам скажет — не такая большая разница.
S>
S> возможно стоит пересмотреть архитектуру бд, или перейти на документоориентированную СУБД или другую NoSQL. S>
На какое NoSQL стоит посмотреть если задача — быстро искать/сортировать по любым полям?
запретить создавать запросы по полям, на которые нет индекса;
разрешить делать запрос по полям на которые есть индекс;
включить slow_query_log в Mysql и мониторть запросы;
возможно стоит пересмотреть архитектуру бд, или перейти на документоориентированную СУБД или другую NoSQL.
Здравствуйте, Sinclair, Вы писали:
S>а) реальный план будет отличаться от тестового из-за другой статистики S>б) не знаю, как в MySQL, а в других СУБД зачастую "предварительный" план отличается от окончательного, т.к. предсказания статистики могут врать, и движок будет переключаться "на лету".
Здоровские замечания — столкнулся сейчас с проблемой: когда месяц назад писал код и смотрел план, некий индекс использовался, работало быстро. Неделю назад перформанс тесты начали падать. Запрос не менялся. Полез смотреть — план поменялся, индекс не используется, запрос работает в 2 раза медленнее. Если руками вписать в запрос "use index (...)", работает быстро как раньше. Сижу сейчас, переписываю с ORM (Hibernate) на "статически типизированную строилку SQL запросов" (jOOQ), чтоб впендюрить туда if (sortBy == "xxx") then use_index("xxxIndex"))
Выходит "юнит тесты" для планов выполнения не будут надёжным решением, т.к. результаты могут меняться непредсказуемым образом.
Здравствуйте, rosencrantz, Вы писали:
R>Здоровские замечания — столкнулся сейчас с проблемой: когда месяц назад писал код и смотрел план, некий индекс использовался, работало быстро. Неделю назад перформанс тесты начали падать. Запрос не менялся. Полез смотреть — план поменялся, индекс не используется, запрос работает в 2 раза медленнее. Если руками вписать в запрос "use index (...)", работает быстро как раньше. Сижу сейчас, переписываю с ORM (Hibernate) на "статически типизированную строилку SQL запросов" (jOOQ), чтоб впендюрить туда if (sortBy == "xxx") then use_index("xxxIndex"))
Зачем переписывать? Там же добавлять для connection'а добавлять sql код, который надо выполнить. Т.е. если
идет манипуляция с какими-нибудь сущностями, то при открытия соединения или непосредственно запроса добавить
какой-нибудь sql код. По идее любая приличная ORM это умеет.
Здравствуйте, kl, Вы писали:
kl>Я в свое время думал в этом направлении, но фундаментальная проблема в том, что к моменту, когда становится понятно, что оценки селективности плохие, движок уже выполнил слишком много работы,
А что это такое? Это выбор (оценка выбора) индексов для запроса?
kl>Именно "на лету", а не при следующем выполнении того же запроса? Если да, может у тебя есть ссылка на техническую статью, где можно почитать о подходах? Не важно, для какого языка запросов, главное чтоб cost-based оптимизатор был. https://www.sqlshack.com/hash-join-execution-internals/
См. разделы hash bailout и role reversal.
kl>UPD: если что, с классической теорией типа Adaptive Query Processing я знаком, но обычно это работает на длинных, часто распределенных запросах, где можно себе позволить потерять секунды на переоптимизацию. Но все равно я бы почитал/послушал практический опыт.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали:
R>Вот у меня есть простая хотелка: я хочу, чтобы N+1 никогда не было. Вообще никогда, ни при каких обстоятельствах. Чтобы всё к херам падало вместо N+1 за 120 секунд в продакшне.
Здравствуйте, Sinclair, Вы писали:
kl>>Именно "на лету", а не при следующем выполнении того же запроса? Если да, может у тебя есть ссылка на техническую статью, где можно почитать о подходах? Не важно, для какого языка запросов, главное чтоб cost-based оптимизатор был. S>https://www.sqlshack.com/hash-join-execution-internals/ S>См. разделы hash bailout и role reversal.
А, ну это очень локальное "переключение на лету", все динамические изменения внутри одного джойна. Особенно что касается hash bailout, если рассматриать хеш джойн как черный ящик, принимающий на вход кортежи и выдающий кортежи, то извне не меняется вообще ничего. С role reversal не так тривиально, поскольку это потенциально меняет порядок, в котором выдаются кортежи.
Но мне более интересно, можно ли динамически изменить порядок джойнов на этапе выполнения, если стало понятно, что статистика наврала. Ну типа есть отношения A, B, C, 3 условия джойна и выбрали порядок (A * B) * C. А потом выяснилось, что A*B выдает практически декартово произведение, и скорее всего надо было сначала джойнить с С. Я экспериментировал, но штука в том, что плохой план очень трудно "пофиксить" локальным образом: что-то переделал в одной части и это вызывает каскадные изменения в других местах. Внутри одного оператора конечно проще, но и импакт меньше.
Здравствуйте, vsb, Вы писали:
vsb>Здравствуйте, rosencrantz, Вы писали:
R>>Вот у меня есть простая хотелка: я хочу, чтобы N+1 никогда не было. Вообще никогда, ни при каких обстоятельствах. Чтобы всё к херам падало вместо N+1 за 120 секунд в продакшне.
vsb>Закрывай сессию сразу после выполнения запроса.
Не поможет — один хайбернейтовский запрос легко может транслироваться в N+1 SQL внутри.
