Иногда в докладах/статьях по оптимизации производительности СУБД, описание предлагаемой методики/средства начинается с события -"мы заметили резкое увеличение времени выполнения запроса/запросов и резкое увеличение количества прочитанных блоков разделяемой области". Далее следует описание процесса выявления ресурсоёмкого запроса, с целью его оптимизации.
Самый главный вопрос, по данному сценарию - а почему считается , что скачок количества прочитанных блоков это инцидент требующий анализа?
На этапе разработки данных сценарий поиска ресурсоемких запросов, возможно, вполне себя оправдывает . Нагрузка на СУБД - детерминирована, характер нагрузки определён и описан, картина распределения данных неизменна. При условии адекватности команды разработки, возможно даже удастся действительно оптимизировать запрос.
Но.
В процессе промышленной эксплуатации ситуация меняется принципиально и кардинально.
Нагрузка на СУБД меняется в самых широких диапазонах , и носит случайный характер.
Характер, объем и статистическая картина распределения данных очень изменчива .
Влияние инфраструктуры в общем случае - непредсказуемо.
Входные данные запросов меняются в самых широких пределах.
Изменить код запроса в общем случае нет никакой возможности, как минимум - очень затруднено .
Сопровождение системы со стороны разработчиков как правило уже отсутствует.
И эта ситуация порождает существенные вопросы :
1) Самый главный вопрос - что является метрикой производительности запроса ? Максимальное время , среднее время , минимальное , стандартная ошибка ?
2) А как определить , что производительность конкретного запроса деградировала ?
3) Как оценить производительность запроса на разных входных данных ? В одном случае запрос обрабатывает X строк и выполняется за время t1, в другом случае запрос обрабатывает Y строк и выполняется за время t2. Можно ли сказать , что есть деградация производительности выполнения , если Y существенно больше X и t2 больше t1?
4) Как оценить производительность запроса при разной нагрузке на инфраструктуру и информационную систему.
Таким образом , в общем виде, проблему можно сформулировать следующим образом:
Можно собрать сколь угодно объёмную историю различных показателей выполнения запроса(группы запросов) за сколь угодно необходимый период времени. Инструментов более чем достаточно.
Как проанализировать собранные данные ?
Какие ожидания от результата анализа?
Update.
Уже после публикации, возникла мысль - применить для мониторинга производительности SQL запроса метрику для оценки производительности СУБД:
https://habr.com/ru/posts/804899/
Конечно, с некоторыми изменениями, для расчета метрики.
1)Использовать вектор N для расчета производительности:
n1-количество прочитанных блоков распределенной области в секунду.
n2-количество записанных блоков распределённой области в секунду.
n3-количество изменённых блоков распределённой области в секунду.
2)Значением метрики будет являться отношение модуля вектора N к общему времени выполнения запроса за промежуток времени (total_exec_time).
В этом случае можно сравнить производительность запроса при разных входных данных и разных объёмах обрабатываемой информации. И затем применить статистические методы для корреляционного анализа с производительностью и метриками СУБД .
Т.е. результатом расчета данной метрики будет значение которое должно оставаться неизменным при разных входных данных и именно изменение этой метрики , а не максимального времени выполнения запроса, будет являться алертом для начала анализа инцидента производительности .
Предположу, что функция зависимости времени выполнения запроса от количества обработанных строк - нелинейна . Более того , вряд ли можно говорить о том, что существует функция времени выполнения запроса по количеству строк. Характер распрелеленич данных может быть сильно разный => план выполнения запроса будет разный . И вообще, одно и тоже количество результирующих строк, может потребовать разное время для получения результата, даже в идеальных тестах, не говоря уже о продуктиве.
Возможно , потребуется дополнить вектор N новыми измерениями.
Комментарии (26)
Kiel
07.07.2024 09:35Да никак ( Можно поиграться с индексами, нормализацией - на этом всё. Дальше нужно писать система кеширования на стороне сервера. Надо держать в голове, что бд достаточно умна, и на частые запросы сама закеширует какие то данные, но кеши наше всё
А сидеть оптимизировать запрос это рехнуться можно, бд не для этого создавались
Grigory_Otrepyev
07.07.2024 09:35+2Дальше нужно писать система кеширования на стороне сервера.
индексы нормально делать не пробовали, а не "поиграться" ?
А сидеть оптимизировать запрос это рехнуться можно, бд не для этого создавались
А для чего ????
Да никак ( Можно поиграться с индексами, нормализацией - на этом всё.
Или взять нормальную СУБД, у которой блокировки не ставят раком всю базу. Любую из .. трех или пяти.
Kiel
07.07.2024 09:35А для чего ????
СУБД это гетерогенная система. То есть она подстраивается под ваши желания. Вы не говорите ей как работать, вы запрашиваете у неё данные, а как их вам возвращать, какие алгоритмы использовать и тд - это проблема уже БД. Но нет! Людям же мало этого! Им нужно залезть внутрь! Разобрать, посмотреть, удивляться, что запросы формируются по разному в зависимости от нагрузки, статьи об этом писать.
И откуда столько эмоций аж на 4 вопросительных знака, всё что я выше написал, это абсолютно известные факты -_- Может вас оскорбило слово "поиграться", но я имел ввиду "play around" - я так часто говорю по работе, особенно когда что то сложное напишу...
Politura
07.07.2024 09:35+1Вообще-то нет. Разработчик определяет структуру хранения данных, индексы и сам запрос. СУБД, создавая план запроса, может подстраиваться в очень узких пределах, и, как правило, если этот план отличается от того, что представляет себе разработчик, то либо косяки с индексами, либо косяки с запросом, ну или косяки с данными и хранить их надо по-другому, в другой структуре.
Ну а если разработчик делая запрос не представляет, как этот запрос будет (или должен в его представлении) выполняться, то это очень грустно. Лучшеб ему подтянуть знания. Ибо там все очень просто и элементарно. Просто чуть-чуть знаний алгоритмической сложности структур, в которых хранятся данные в СУБД, чуть-чуть знаний алгоритмической сложности операций, которые СУБД применяет для выборки данных и для соединений, немножко здравого смысла и немножко практики.
Grigory_Otrepyev
07.07.2024 09:35Ну а если разработчик делая запрос не представляет, как этот запрос будет (или должен в его представлении) выполняться, то это очень грустно.
или сходить в метрики , или в профайлер. но нет, не барское дело, субд ему должна подстроиться.
Kiel
07.07.2024 09:35Лучшеб ему подтянуть знания
Ну так я и говорю, как можно не знать, что это СУБД это гетерогенная штука. У меня целый курс был в университете "гетерогенные системы", а здесь слышу "для чего создавались СУБД"
На самом деле я понял намёк, просто я не говорил, что не надо знать, я говорил, что не стоит умирать оптимизируя. Как с ChatGPT - стоит знать как оно работает, не стоит лезть оптимизировать направо налево
Kerman
07.07.2024 09:35+7бд достаточно умна
Неа. БД не в курсе про ваши сценарии использования данных. У неё свои представления о прекрасном. Она не знает, когда надо дропнуть кэш, она не угадает, какие данные надо предварительно закешировать, она не в курсе, что вот этот тяжёлый запрос нужен раз в полгода.
Grigory_Otrepyev
07.07.2024 09:35она не в курсе, что вот этот тяжёлый запрос нужен раз в полгода.
но ведь .. можно его брать из реплики ... ??? ведь база же могла сама угадать, что ей нужна реплика и перенаправить запрос туда?? ))))))))))))))))))))
Kiel
07.07.2024 09:35Допустим, ты по одному и тому же id делаешь 100к запросов каждый час к данным, которые не меняются. Это очень легко отследить со стороны БД
Но говорю еще раз, на БД надейся, но и сам не лажай - добавь кеш, если уверен, что всё медленно и данные не меняются, например Имя Фамилия у пользователя. Обновляй кеш каждый час. Советую LazyCache в C#, но это я так, на усмотрение
alexdora
07.07.2024 09:35Нормализация включает в себя индексы это раз.
Второе, я сталкивался с ситуацией где в инженеры СУБД уменьшали утилизацию CPU в 10-ки раз делая в SQL-запросе вложение которое «обманывала» сборщик кэша и прочее. Поэтому если я или к примеру вы знаем СУБД на уровне простейших селект/инсерт, то не стоит считать что больше вариаций никаких нет.
RmExevil
07.07.2024 09:35Не согласен. Есть и другие подходы. Перевод чтения на стенд-бай, добавление партиций, разделение данные на ноды, разделение на горячие и холодные данные. С точки зрения расширения пропускной способности базы, там можно обыграть по разному. В нашем случае, даже просто сохранение батчами начало помогать)
Kiel
07.07.2024 09:35+1Ну да, я через чур упростил, я имел ввиду направленность - всё что угодно вне, кроме прямых оптимизаций запросов
tonx92
07.07.2024 09:35+1На хабре любят минусить за здравый смысл.
Поможет только одно. Кэши и оптимизация запросов на стороне приложений.
В самих бд нет большого смысла что то менять, максимум репликацию при масштабировании добавить и следить за нагрузкой. Все ошибки ведущие к снижению скорости созданы на стороне приложений и решаются там же.
RmExevil
07.07.2024 09:35А разве не лучше комплексные подходы? Прибить план хороший способ решить деградацию в проде. Не всегда получится подобную проблему решить оперативно
Grigory_Otrepyev
07.07.2024 09:35Поможет только одно. Кэши и оптимизация запросов на стороне приложений.
дичь полная. у MS SQL , DB2 и Oracle RAC огромное число настроек и метрик. Крутить надо везде, и на всех маршруте от запроса до процесса исполнения запроса. для этого есть разный мониторинг. но это уже надо думать и даже звать админов.
BugM
07.07.2024 09:35+1Вы мониторинги делать не пробовали? Все нормальные БД умеют логировать медленные запросы. Критерий медленности можно настроить.
Потом вдумчиво читаете логи и смотрите какие запросы вчера были быстрыми по вашему критерию, а сегодня стали медленными. Ну или робота отправляете это делать. С этими запросами уже можно работать и смотреть что случилось.
Сверху стоит прикрутить мониторинг медианы времени выполнения и нагрузки на ЦПУ с дисками. Так вы увидите общее просаживание производительности. И будет вообще хорошо.
rinace Автор
07.07.2024 09:35мониторинг медианы времени выполнения и нагрузки на ЦПУ с дисками. Так вы увидите общее просаживание производительности.
Просаживание производительности чего ? Инфраструктуры или СУБД ?
BugM
07.07.2024 09:35Сервера на котором у вас БД крутится. Причины могут быть разными, этот мониторинг просто покажет что проблема есть.
Grigory_Otrepyev
07.07.2024 09:35+1Вы мониторинги делать не пробовали? Все нормальные БД умеют логировать медленные запросы.
s/ глупости говорите. вы так дойдете до того, что надо читать документацию по бест практик самой базы.
zubrbonasus
07.07.2024 09:35Существует понятие "эталонные тесты". Эталонные тесты это некоторый набор запросов составленный для тестирования бд.
Принцип использования такой: запускаем тесты, получаем результат выполнения запросов, анализируем проблемные моменты (запросы которые выполняются долго), оптимизируем структуру и запросы, запускаем эталонные тесты и сравниваем результаты, потом все с начала.
Ожидания от выполнения запросов можно определить так: "команда разработчиков считает что страница веб сервиса должна открыться за 300 ms.", - чтобы удовлетворить это ожидание данные должны появиться на фронте за ~75 ms. Под эти ожидания пишем запрос(ы) учитывая объем данных в таблице (их может быть много).
У СУБД также есть хорошие инструменты: лог долгих запросов, лог запросов, лист процессов, журнал репликации и др.
Эти инструменты также важны в работе с бд.
Ну как-то так.
rinace Автор
07.07.2024 09:35Скажу вам , как бывает в реальной жизни - абсолютно принципиально по другому .
Никто ни архитектор , ни менеджер, никто из разработчиков не знает цифр
"команда разработчиков считает что страница веб сервиса должна открыться за 300 ms.", - чтобы удовлетворить это ожидание данные должны появиться на фронте за ~75 ms.
Далее , они не пишут запросы
Под эти ожидания пишем запрос(ы) учитывая объем данных в таблице (их может быть много).
Они используют фреймворки и ORM.
И потом , уже через полгода-год совсем другие люди , не имевшие никакого отношения к разработке системы создают тикет - система стала медленно работать.
В реальной жизни всё происходит сильно не по книжкам.
Tzimie
07.07.2024 09:35Query store? Специальный отчет по деградировавшим запросам? Нет, не слышал.
ПыСы. Разумеется это не серебряная пуля
rinace Автор
07.07.2024 09:35отчет по деградировавшим запросам
Какая метрика является показателем деградации запроса ?
Например запрос выдавал 10 строк и выполнялся 10ms. Запрос выдает 10000 строк и выполняется секунду .
Можно говорить о деградации запроса ?
А если первая цифра получена при 50 активных сессиях, а вторая при 600?
Grigory_Otrepyev
И на этом все?
То есть рассказа о богоравном SHOWPLAN и стоимости операций не будет ?
Чего чего?? У вас программа меняется сама по себе и внутри ее меняется тот селект, который сделали разработчики ??