Комментарии 40
Хотелось бы, конечно, чтобы таблица не менялась в процессе выполнения запросов для скриншотов. А то складывается ощущение что время выросло в 15 раз после создания индекса. Да и строка в резалтсете приросла.
Реляционная база данных — это набор данных с предопределенными связями между ними.
"Предопределенными" ????
Вы с сетевыми БД не перепутали ?
Какой тогда смысл в конструкциях Join , where?
Чтобы понять, почему выполнение замедлилось, нужно использовать функцию EXPLAIN.
Чтобы несколько спустить на землю. В реальной жизни , в процессе промышленной эксплуатации, explain не дает никакой информации о причинах деградации производительности .
Ну explain является вторым шагом в диагностике (первый это понять какие запросы тормозят и почему) - так что якобы бесполезность эксплейна это сильное преувеличение
первый это понять какие запросы тормозят и почему
Когда вы это поймете , explain не понадобится .
Буду рад за примеры из реальной жизни .
Не цитаты из учебников а кейсы возникавшие в процессе промышленной эксплуатации информационных систем.
Про свой скромный опыт - ни разу explain не использовался при анализе аварий и инцидентов.
Хотя я лично видел план запроса стоимостью триллион (10^12) и вопрос разраба - почему у нас система медленно работает, мы не можем пойти нагрузочное тестирование для закрытия этапа договора .
Спасибо за ваш комментарий! Дело в том, что здесь под предопределенными мы понимаем связи между таблицами по ключам (первичный — внешний). Возможно, это не совсем точное определение, но это намеренное написание — чтобы пользователям, которые только начинают работать с БД, было проще понять связи таблиц.
По поводу explain не совсем согласны: в реальной жизни бывает так, что ручка, связанная под капотом с БД и сложный запрос к оной, деградирует со временем из - за увеличения объема данных в таблицах. В этом случае ( если мы не меняли бизнес-логику в приложении) анализ запроса с помощью EXPLAIN будет очень полезен.
под предопределенными мы понимаем связи между таблицами по ключам
А если ограничение внешнего ключа , для таблицы не задано , то , связи нет ?
По поводу примера про explain - это цитата из любого учебника для начинающих DBA. Только в реальности это не работает. А как узнаете , что запрос деградировал ? По времени выполнения отчёта/экранной формы ? Вы имеете историю планов выполнения запросов ? А вы точно уверены, что проблема в запросе а, например не в СХД.? И т.д. и т.п. Повторюсь, в реальности все не так как в книжках .
Explain и анализ плана выполнения обязательно нужен на этапе разработки и нагрузочного тестирования, при условии конечно , что для разработчики понимают разницу между разными видами соединений и чтений. Но на этапе промышленной эксплуатации тратить время на анализ плана пользы нет. Сколько было аварий и инцидентов деградации производительности в моей личной практике - explain не помог ни разу. 99.9999% причина - кривая логика приложения и инфраструктура .
в моей личной практике - explain не помог ни разу
А в моей практике auto_explain, а затем explain analyze, выручал неоднократно.
Explain и анализ плана выполнения обязательно нужен на этапе разработки и нагрузочного тестирования
Это верно только в случае сферического коня в вакууме. А на практике, со временем в БД может не только расти объем данных, но так же и сильно, даже на порядки, изменяться селективность в запросах. И в результате, тот план запроса, который когда то строился при разработке и тестировании, может не иметь ничего общего с планом запроса, возникающем через год промышленной эксплуатации.
auto_explain + track_io_timing + сбор и анализ всех планов = можно построить heatmap распределения времени выполнения запросов и наглядно отследить деградацию.
По крайней мере, "проблема в СХД" определяется на раз по I/O Timings.
А можно чуть подробнее про
можно построить heatmap распределения времени выполнения запросов и наглядно отследить деградацию.
По вашему производительность запроса это время выполнения запроса ? Классический вопрос - если запрос выполняется дольше и возвращает больше строк - это деградация или нет ?
И далее - вы представляете размер лога при включённом auto_explain?
Есть реальные кейсы из промышленной эксплуатации ИС ? Учитывая , что в реальной работе высоконагруженной СУБД запросов не сотни, а тысячи.
По личному опыту совсем наоборот - auto_explain никак не помогает.
По поводу времени выполнения - да можно построить гистограммы среднего и максимального времени выполнения запросов . Но с использованием pg_stat_statements , не auto_explain. Этот вариант наверное сильно быстрее .
По вашему производительность запроса это время выполнения запроса?
Производительность - это именно про время, а вот эффективность может определяться и объемом buffers, и их распределением по hit/read, и скоростью обмена с СХД, и размером возвращаемого resultset, которые прямо или косвенно влияют на это самое время.
Можно вот типа таких картинок наблюдать по ходу дня.
Классический вопрос - если запрос выполняется дольше и возвращает больше строк - это деградация или нет?
Зачем сравнивать сладкое и теплое? Давайте лучше сравнивать в рамках одного критерия.
Если запрос хронически возвращает 1K записей, но внезапно возвращает 1M размером на несколько десятков MB - это может и не быть деградацией, но крайне подозрительно само по себе.
Если выполнялся по 10мс, а внезапно стало 1000мс - тоже подозрительно.
И далее - вы представляете размер лога при включённом auto_explain?
Есть реальные кейсы из промышленной эксплуатации ИС ?
У нас сейчас на мониторинге с включенным auto_explain ~3K инстансов PostgreSQL. Размер логов порядка 16TB за пару месяцев, дольше не храним.
Вот тут я рассказывал, как мы их собираем и храним.
Производительность - это именно про время, а вот эффективность может определяться и объемом buffers, и их распределением по hit/read, и скоростью обмена с СХД, и размером возвращаемого resultset, которые прямо или косвенно влияют на это самое время.
Стоп , стоп , стоп. Зачем вводить новое определение не утвердив старое. Т.е. ваше определение производительности СУБД это среднее время отклика СУБД ? Так ?
Если выполнялся по 10мс, а внезапно стало 1000мс - тоже подозрительно.
Ещё раз повторю вопрос - запрос выполнялся 10ms и выдавал 1K строк и стал выполнятся 1000ms и выдавать 1000K строк . Имеет место деградация производительности запроса/СУБД или нет ? Открываем инцидент ?
Размер логов порядка 16TB за пару месяцев, дольше не храним.
Можете , хотя бы примерно описать сценарий действий в случае возникновения инцидента производительности? Что смотрим, что ищем в 16тб логах ?
определение производительности СУБД это среднее время отклика СУБД
Да, только лучше использовать не среднее, а процентили. А вот эффективность достижения одной и той же производительности может быть разной относительно использованных ресурсов.
На этом принципе реализована параллелизация выполнения в PG. Очевидно, что распределение работы по воркерам требует больше ресурсов, но дает меньшее время выполнения.
Открываем инцидент ?
Безусловно. Но не факт, что на работу СУБД. Это может быть ошибкой условия, сформированного разработчиком. В любом случае требует исследования.
примерно описать сценарий действий
Определяем проблемный хост. На нем находим подходящий шаблон запроса в конкретном методе, дальше по времени/хитмапу находим конкретный проблемный план.
Тут есть в докладе примерная логика как и почему мы пришли к таким моделям.
Спасибо за обратную связь и предоставленную информацию.
Отмечу лишь, что на самом деле , в реальной жизни, среднее время отклика СУБД не является показателем производительности СУБД.
Если кратко:
1) Во время аварийной ситуации время отклика может не только не расти но даже уменьшаться .
2) В случае изменения характера нагрузки , время отклика изменится но никакой аварийной ситуации нет. Тратить время на анализ - непродуктивно.
Я придерживаюсь другой точки зрения на производительность СУБД и анализ причин . Работы идут, первые результаты есть. Если , все пойдёт по плану в этом году будет большая статья.
Спасибо за конструктивную переписку. Это в общем то редкость.
P.S. По поводу доклада - описанная методика никак не помогла бы в случаях реальных аварий и кризисов в информационной системах в решении которых я участвовал. В реальной жизни всё слишком по другому.
К сожалению (или к счастью), описываемые мной решения и методики - это наш собственный путь по реальным граблям в том числе и на продах - то есть никакого теоретизирования, сугубо практические выкладки, накопленные за последние 10-15 лет.
Например, вот тут я приводил примеры анализа ситуаций, которые требуют от DBA все-таки иметь кругозор и за пределами самой DB.
Поэтому взглянуть на принципиально другую методику будет весьма любопытно.
Принципиально другая методика
Корреляционный анализ для решения инцидентов производительности СУБД
https://habr.com/p/827504/
Подход в принципе другой - не эмпирический , в основе мат. статистика , и весь накопленный опыт статистического анализа. Т.е. по классике - гипотеза , эксперимент , анализ , гипотеза ...
Работы по теме только начались, но первые результаты уже есть.
Например - для анализа причин аварийной ситуации деградации производительности отчеты pgpro_pwr бесполезны .
Согласен, что снапшот-модель интервальных отчетов мало полезна. Но и анализ конкретных фактов с помощью матстата выглядит как-то не слишком оптимально.
Статью читал, но как применить к реальным условиям использования - не придумал. На мой взгляд, оно может быть полезно, если "везде все хорошо и вдруг где-то становится плохо". У нас же обычно можно взять любой хост и какую-нибудь неоптимальность да найти, а раз нашел - стоит устранить.
оно может быть полезно, если "везде все хорошо и вдруг где-то становится плохо".
Именно эта ситуация.
можно взять любой хост и какую-нибудь неоптимальность да найти, а раз нашел - стоит устранить.
Вопросы не относящийся к теме данного поста - зачем ? Или другими словами - за счет какого бюджета работы ? И самое главное - какие цифры являются показателем качества выполненных работ ?
Тут нет параметра "бюджета" - в нашем случае это баланс между затратами на ФОТ разработки против затрат на оборудование.
Банальный пример: разработчик написал запрос к БД, но забыл сделать под него подходящий индекс. Это приводит к увеличению нагрузки по cpu, памяти, диску хоста, кол-ву вычитываемых/отфильтровываемых записей, вычитываемых страниц.
Можно вложить 100 рублей в выделение больших аппаратных ресурсов, а можно за 1 рубль заставить разработчика добавить индекс, после чего оценить изменение описанных выше метрик.
Ответ будет сильно оффтопик и содержать сильно личное предвзятое мнение .
Проблема в следующем:
Можно вложить 100 рублей в выделение больших аппаратных ресурсов, а можно за 1 рубль заставить разработчика добавить индекс, после чего оценить изменение описанных выше метрик
Аппаратные ресурсы сейчас стоят сильно дешевле , особенно одноразовые, особенно совершенно ясно как провести расходы .
Как провести расходы по разработке - сильно другая тема . К тому же , очень острая проблема - удручающе низкий уровень разработки.
Что они творят, это тема отдельного набора статей.
Аппаратные ресурсы сейчас стоят сильно дешевле
У нас миллионы корпоративных аккаунтов. То есть потенциально неудачное решение может смасштабироваться на всех пользователей всех этих аккаунтов - тут за каждую миллисекунду имеет смысл биться, чтобы не завозить "железо" в ЦОД КАМАЗами.
удручающе низкий уровень разработки
Для его повышения я и пишу-пишу статьи - присоединяйтесь!
Для его повышения я и пишу-пишу статьи - присоединяйтесь!
По мере скромных сил стараюсь добавлять свои пять копеек по темам.
Но , к сожалению это практически не оказывает влияния на процесс разработки ИС. Современное поколение разработчиков воспринимает СУБД как ящик для хранения данных , не зная и не понимая как всё работает(они сами открытым текстом это говорят).
Классическая ситуация - в ходе НТ выясняется - система тормозит и не выдаёт заявленных в ТР показателей . Выяснилось - разрабы используют pg_adviser_lock. На вопрос - а какого болта ? Получен ответ - а у нас Фреймворк такой.
И все, ничего не сделать . Систему сдали в промышленную эксплуатацию, каждую неделю разбор инцидентов с одним и тем же результатом - проблемы приложения . После проведения рефакторинга - проблема прошла.
Или другой случай - огромное количество сессий в состоянии "idle in transaction". На вопрос - зачем вы так делаете ? Получен ответ - приложение открывает транзакцию , проводит изменения , ждет ответа от внешней системы и закрывает транзакцию. Случай "как сделать Highload на ровном месте". Описано в статьях, докладах и конференциях. Но разрабы этих статей не читают, доклады не слушают, конференции не посещают.Это не проблема это особенность логики приложения (с).
Вот такие пирожки с котятами.
К тому, же написание статью это много времени.
Спасибо, что у кого то есть время и возможность .
Cтранный выбор PostgreSQL для хранения логов. Мы выбрали для этой цели Kafka + ClickHouse, что автоматически решило целый ряд проблем, возникающих при сборе и хранении логов в RDBMS.
Хранение лога - второстепенная для нас задача, а основная - заранее известные выборки.
Мы пробовали использовать колоночные хранилища, например, citus. Получилось, что четко зная целевую выборку, можно написать запрос, работающий в 3-4 раза чем на citus.
Citus - реляционное колоночное хранилище, а ClickHouse - аналитическое (OLAP). Поэтому при подобном профиле загрузки, Citus очень сильно уступает ClickHouse в производительности.
А уж для заранее известных выборок разница в производительности будет на порядок, так как ClickHouse очень хорошо на это заточен (live view)
CH не особо заточен на обновляемые данных. У нас в общей инфраструктуре он тоже используется для некоторых задач, и у него есть и плюсы (я в курсе про *MergeTree-движки), и минусы, как и у любой СУБД.
Нам для анализа работы PG - пока не подошел. В том числе, и потому что наша собственная экспертиза в области PG, а не CH.
CH не особо заточен на обновляемые данных.
Откуда в логах обновляемые данные? Там по определению поступают только новые данные.
А ClickHouse на порядок эффективней того же Citus, и уж тем более PostgreSQL, умеет загружать массивы данных.
наша собственная экспертиза в области PG, а не CH
"Золотой молоток"? Это пугает.
В логах обновляемых данных нет, а в аналитике - есть.
"Золотой молоток"? Это пугает.
Мы не фанатики. Но сейчас у меня есть на руках инструмент для хорошего мониторинга/анализа работы PG, а для CH такого же по функционалу - нету. И усложнять клиентскую инфраструктуру для локального разворота нашего решения тоже не хочется.
В логах обновляемых данных нет, а в аналитике - есть.
"Золотой молоток"? Это пугает.
Мы не фанатики. Но сейчас у меня есть на руках инструмент для хорошего мониторинга/анализа работы PG, а для CH такого же по функционалу - нету. И усложнять клиентскую инфраструктуру для локального разворота нашего решения тоже не хочется.
По крайней мере, "проблема в СХД" определяется на раз по I/O Timings.
Прошу прощения , но не соглашусь. Увеличение значения метрики I/O Timing не является надежным индикатором проблемы с СХД , тем более для одного запроса .
Более того - чем выше нагрузка на СУБД , тем выше будет показатель I/O Timing.
Аналогичная ситуация с ожиданиями СУБД типа IO - высокое значение ожидания , еще не индикатор проблемы .
Если мы берем buffers.read и соотносим с io.read, то при достаточных величинах можем судить о скорости СХД "в моменте". И если эти значения каждый момент маловаты, то можем судить и о недостаточной пропускной способности дисковой подсистемы.
Понятно, что если на СХД max=10Kops положим нагрузку 15Kops, то увидим замедление каких-то из операций. Но это как раз и будет сигналом о недостаточной производительности.
если на СХД max=10Kops положим нагрузку 15Kops, то увидим замедление каких-то из операций. Но это как раз и будет сигналом о недостаточной производительности.
2 вопроса :
1) Как найти операции по которым есть замедление ?
2) Недостаточная производительность СУБД или СХД?
Ну и самое главный момент - заставлять DBA мониторить и анализировать СХД это в корне порочная практика . Каждый должен заниматься своим делом.
СУБД использует СХД, поэтому причиной проблем первой могут быть проблемы второй. А могут и не быть. А может быть и наоборот.
Если у меня машина не едет нормально, то виноват некачественный бензин, проблемы с двигателем или я передачу не переключил?..
Продолжая, предложенную ассоциацию - вы пробовали в нормальном автосервисе попросить электрика посмотреть, что там с ходовой стучит ?
Если "нормальный" == "клиентоориентированный", то не далее как сегодня, загнав машину на замену масла, попросил заодно проверить ручник. И таки да, поменяли колодки, суппорт и подтянули ручник без вопросов.
Ведь меня как пользователя машины интересует ее полная исправность, а не то, что Вася - электрик, Петя - механик, и как они делят обязанности на современных электромобилях.
Если вы выполняете Explain и видите Seq Scan, вам нужно либо подумать над тем, как ваша база данных спроектирована
Это не так. Использование последовательного чтения довольно часто вполне оправдано(это классический случай описанный в множестве статей и по моему даже в документации) , а с точки зрения общей производительности СУБД seq scan может быть даже выгоднее, чем index scan .
Потому, что рассматривать и анализировать нужно производительность СУБД и даже Информационной системы в целом , а не цифры cost отдельного запроса .
Оправданно при неправильном индексе, когда база пытается .быть умнее разработчика который создал индекс. Например проиндексировал boolean, или какой нибудь enum, где больщая часть строк приходится только на одно значение. Лечится частичным индексом.
Имеем таблицу, все кортежи которой умещаются в одной странице. В этом случае открытие файла индекса и чтение страницы индекса из него даст большие накладные расходы, чем последовательный поиск по единственной странице в таблице, если хотя бы одно поле из кортежа, необходимое в запросе, отсутствует в индексе.
А можно примеры использования hash ключей к примеру в связке таблица договоров таблица клиентов выгоднее на клиентов вешать hash или b tree индекс? А на ссылку на клиента в таблице договоров?
Разумно будет предпочесть hash, только если вы экономите место на диске - тут в лекции чуть подробнее. Во всех остальных случаях "умолчательный" btree будет не хуже.
Базы данных. Основы разработки и работа с индексами в PostgreSQL