Горячее
Лучшее
Свежее
Подписки
Сообщества
Блоги
Эксперты
#Круги добра
Войти
Забыли пароль?
или продолжите с
Создать аккаунт
Я хочу получать рассылки с лучшими постами за неделю
или
Восстановление пароля
Восстановление пароля
Получить код в Telegram
Войти с Яндекс ID Войти через VK ID
Создавая аккаунт, я соглашаюсь с правилами Пикабу и даю согласие на обработку персональных данных.
ПромокодыРаботаКурсыРекламаИгрыПополнение Steam
Пикабу Игры +1000 бесплатных онлайн игр Герои Войны - микс стратегии и РПГ. Собери лучшую армию и победи всех врагов. В игре 7 различных режимов - как для любителей PvE, так и PvP.

Герои Войны

Стратегии, Мидкорные, Экшены

Играть

Топ прошлой недели

  • SpongeGod SpongeGod 1 пост
  • Uncleyogurt007 Uncleyogurt007 9 постов
  • ZaTaS ZaTaS 3 поста
Посмотреть весь топ

Лучшие посты недели

Рассылка Пикабу: отправляем самые рейтинговые материалы за 7 дней 🔥

Нажимая кнопку «Подписаться на рассылку», я соглашаюсь с Правилами Пикабу и даю согласие на обработку персональных данных.

Спасибо, что подписались!
Пожалуйста, проверьте почту 😊

Помощь Кодекс Пикабу Команда Пикабу Моб. приложение
Правила соцсети О рекомендациях О компании
Промокоды Биг Гик Промокоды Lamoda Промокоды МВидео Промокоды Яндекс Директ Промокоды Отелло Промокоды Aroma Butik Промокоды Яндекс Путешествия Постила Футбол сегодня
0 просмотренных постов скрыто
22
sterblich
sterblich
2 дня назад
Наука | Научпоп
Серия Критическое мышление

Может содержать ложь (3)⁠⁠

Продолжаем знакомиться с книгой Алекса Эдманса.
Все части выложены в серии.

Даже если когда у нас в руках есть факты, они не всегда дают полную картину. Выборка должна быть представительной, то есть соответствовать генеральной совокупности. Иначе можно надёргать удобных данных для подтверждения своего утверждения. Чем многие и занимаются. В частности, этим грешат некоторые блоггеры, расписывающие прелести инвестирования на примере удачных вложений. Следует иметь в виду, что на каждого успешного спекулянта может найтись гораздо больше неудачников. Но про них часто предпочитают умолчать.

Кто ищет дорогу к надёжному знанию, тот следует научному методу. При нём мы начинаем с постановки гипотезы о влиянии А на Б (входа на выход). Затем эта гипотеза проверяется. В идеале стоило бы перебрать все данные. Но это чаще всего невозможно, поэтому приходится делать выборку. И выборка эта должна быть не какая попало. Она должна охватывать широкий спектр данных. Слоёный пирог режут сверху вниз, чтобы получить полное впечатление о его вкусе. Следующий шаг – найти контрольную выборку, которая не включает в себя вход. После этого рассчитываем выход для всех входных данных и сравниваем. Если для входа он сильнее – вуаля! Но после этого необходимо оценить статистическую значимость связи, то есть исключить роль случая. Чем больше выборка, и чем сильнее корреляция – тем лучше. Если вероятность «случайного» отклонения нашей гипотезы окажется ниже 5%, то мы можем сделать вывод о влиянии входа на выход. Но всё ещё но не сможем доказать нашу гипотезу! Мы просто будем знать, что установленная связь не случайна.

Простой обыватель, да и некоторые авторы, часто действуют совсем по-другому. Биограф Стива Джобса Исаксон не ставил гипотез, а идентифицировал возможные факторы, которые лежали в основе успеха бизнесмена. Например, тот факт, что Джобс был приёмным ребёнком. По науке надо было исследовать истории множества приёмных детей, а также множества успешных бизнесменов, а не одного лишь Стива. То есть набрать репрезентативную выборку. Иначе у нас получится вывод по индукции – из частного случая выводится общая последовательность. А это не есть логично.

Эта простая и очевидная вещь часто забывается, когда мы слышим увлекательный рассказ. Истории успеха эксплуатируют нарративное заблуждение: наше стремление видеть два события и верить в то, что одно вызывает второе. Даже если у них обоих может быть общая причина или вообще никакой связи между ними нет. Видя последовательность фактов, мы склонны выстраивать поясняющую историю с логическими стрелками между ними. У Джобса отец любил мастерить – ага, вот почему Стив стал таким успешным! Звучит красиво, во всяком случае.

Даже обилие фактов может оказаться не репрезентативным. Их всегда при желании можно надёргать. Главное, чтобы было откуда. Поэтому всякий раз, взяв в руки очередную историю успеха, задавайтесь вопросом о том, исследовали ли авторы случаи, которые не ложились в красивую картинку: когда есть вход, но нет выхода, или есть выход без входа.

Карабкаемся дальше по авторской лестнице. Итак, пусть у докладчика есть не только отдельные факты, но данные, то есть представительная выборка. Мы всё ещё не можем верить ему, потому что он мог нарыть её нехитрым методом под называнием data mining. Грубо говоря – он провёл сотни тестов, отбросил все, что не сработали, и оставил то немногое, что подтвердило его гипотезу. Если долго мучиться, что-нибудь получится. Вышеупомянутая вероятность в 5% статистической значимости – довольно большой процент.

Одним из средств при этом является перебор метрик для искомого фактора. Успешность компании можно измерять разными способами. Есть бесспорные индикаторы, такие как акционерный доход. Но никто не мешает взять что-то другое – скажем, норму прибыли. Пусть она не учитывает десятки других факторов, влияющих на цену акции и её дивиденды, зато с ней можно обосновать своё утверждение о влиянии разнообразия в менеджменте на успех компании. Что некоторые и делают.

Поэтому при виде всякой экзотики в обоснование результатов следует включить режим скептика и задаться вопросом: измеряет ли докладчик вход и выход самым естественным образом? Также следует включить здравый смысл и выяснить, способен ли вообще вход влиять на выход. Если нет логики в обоснование, то результат, скорее всего, намайнили в грудах данных.

Некоторые поступают совершенно иезуитским образом: запускают кучу тестов, после чего находят значимые связи, чтобы на этом основании нарыть уже саму гипотезу. Как поймать таких деятелей за руку?  Чтобы ответить на этот вопрос, Алекс рассказывает нам о своей академической юности, когда он писал диссертацию о влиянии спортивных событий на фондовый рынок. На эту идею его натолкнула практика на Morgan Stanley во время чемпионата Европы по футболу 2004 года. Он лицезрел эскапады трейдеров после проигрыша своей команды и подумал, что в суммарном масштабе это может влиять на курсы акций всей страны. Логично? Логично. Результат важной игры влияет на настроение миллионов, а настроение влияет на инвестиционные решения. Автор обнаружил, что на следующий после проигрыша национальной команды день фондовый рынок проседал чуть ли не на полпроцента. Англия не смогла нормально пробить послематчевые пенальти – получайте просадку в 10 миллиардов. Что интересно: на курс акций ощутимо влияют в основном проигрыши любимой команды. Побед не замечают.

Алексу удалось защитить свою диссертацию, потому что у него была сильная логичная гипотеза. Но при этом ему не удалось избежать обвинений в добыче данных. В погоне за результатом можно ведь попытаться сделать удачную выборку, выбросив всё неудобное. Чтобы ответить на упрёки, необходимо продемонстрировать, что результаты исследования работают и на других выборках: в другое время, в другом месте. Так и Алексу пришлось отвечать на обвинения, используя результаты других чемпионатов, а также другие виды спорта. Оно работало и в 2004, и в 2014, и в 2024 году. И в крикете, и в регби, и в баскетболе. Лишь хоккей выпал из тренда.

Может содержать ложь (3) Книги, Обзор книг, Критическое мышление, Наука, Корреляция, Пруф, Научпоп, Нон-фикшн, Длиннопост

Влияние проигрышей на курс акций на примере чемпионата Европы по футболу 2024 года

Можно прийти к разным выводам даже при использовании одних и тех же данных. Это достигается произвольным их группированием: данные разделяются на разные группы, после чего сравнивается влияние отдельных групп на  выход. Самое «вкусное» при этом то, что делить на группы можно, как хочется, так что фрагмент с неудобными данными можно не брать в сравнение. Если мы видим подобное сравнение в обоснование тезиса докладчика, нужно обращать внимание на пропуски. Например, когда сравнивают рентабельность компаний с тремя и более женщинами в руководстве с показателями компаний вообще без женщин на руководящих должностях, стоит спросить о том, куда подевались показатели компаний с одной или двумя женщинами в топ-менеджменте.

Предположим, что с данными у нас всё чисто, и мы имеем надёжную корреляцию. Значит ли это, что у нас есть свидетельство истинности утверждения? Всё ещё нет: корреляция не означает причинной связи. Автор поясняет это исследованием связи между кормлением грудным молоком и интеллектуальным развитием ребёнка. Несмотря на явную корреляцию между этими вещами, говорить о причинности здесь рано. Поскольку они могут зависеть не друг от друга, а от какой-то третьей, общей причины. Например, от интеллектуального развития матери, которая и позже начнёт прикорм, и курить будет реже, и будет развивать своего ребёнка. Не говоря уже о том, что она уже дала ему свои гены.

Данные – не свидетельство, потому что могут быть альтернативные объяснения для сложившегося положения вещей. Выход может включиться по какой-то другой причине, нежели вход. И даже если у нас есть контрольная группа, её может оказаться недостаточно, поскольку она может оказаться связанной с входом. Одним словом, разные входы могут оказаться не случайными, а быть следствием общей причины, будь то волевое решение, корреляция с другими факторами или другой процесс.

С волевым решением мы имеем дело тогда, когда люди принимают его. Мать решается на грудное вскармливание не случайным образом, но по каким-то причинам. Если же изучать не решения, а какие-нибудь признаки, то и они могут коррелировать с другими признаками. Изучаем эмоциональность – не забудем, что она может быть связана с IQ. Влияние других процессов автор иллюстрирует на примере исследования связи между грязным воздухом и летальностью пандемии ковида. Но грязный воздух – это эндогенный фактор. Он является следствием других факторов, которые могут влиять и на летальность от ковида. Первое, что приходит в голову при этом – это плотность населения.

Может содержать ложь (3) Книги, Обзор книг, Критическое мышление, Наука, Корреляция, Пруф, Научпоп, Нон-фикшн, Длиннопост

Летальность ковид-19 растёт при увеличении загрязнения воздуха

Спутаешь корреляцию с причинностью – будешь неправильно предсказывать и неверно принимать решения. Смелые предсказания вызывают широкий отклик, как случилось с публикацией McKinsey о связи долгосрочного планирования с производительностью компании. Они предсказывали, что если все американские компании раздвинут горизонт планирования, то экономика в целом вырастет на ещё три триллиона за десятилетие. Harvard Business Review посчитали это «доказательством», но после того, как читатели стали жаловаться, исправили «доказательство» в заголовке своей публикации на «свидетельство». Так что стараемся всегда смотреть с недоверием на подобные бомбические заголовки и помним: самая убедительная статистика никогда не бывает слишком хорошей или слишком плохой.

Как же прояснить возможную причинность корреляции? Лучшим инструментом является математическая регрессия. С её помощью можно установить корреляцию не только одного лишь входа с выходом, но протестировать связь и с другими возможными входами. Эти дополнительные входы называют контрольными величинами. После этого можно анализировать изменение выхода при изменении одного лишь из входов, при прочих равных. Тогда корреляция может легко испариться. Но если останется – то в этом действительно что-то есть.

При всей простоте метода многие умудряются сесть в галошу, что случилось с авторами бестселлера The Spirit Level, писавших о пагубном влиянии неравенства на общество. Книга имеет интригующий подзаголовок: «почему равенство лучше для всех». Алекс удивился: неужели и для богатых тоже? Её авторы собрали статистику о физическом и психическом здоровье, а также о других параметрах общества и утверждали, что неравенство ухудшает их всех. Однако они не учли, что в нашем случае может быть целый ворох общих причин, первая из которых, которая приходит на ум – это бедность. Бедные общества, как правило, отличаются высоким неравенством. И здоровье в них тоже не очень. Авторы попытались, однако, продемонстрировать, что бедность тут ни при чём, продемонстрировав слабую корреляцию бедности и здоровья. Но ведь они написали книгу не про бедность. А про неравенство. Правильным методом было бы продемонстрировать корреляцию неравенства и здоровья с бедностью в качестве контрольного параметра. И на самом деле, когда кто-то задался такой задачей, корреляция между неравенством и здоровьем в обществе стала намного слабее.

К сожалению, мы не всегда можем включить в контрольные переменные всё, что хотелось бы. Но нам и не надо брать туда всё подряд. Если переменная коррелирует лишь с выходом, но не со входом, то извлечение её из регрессии не повлияет на результат. Но а если она всё-таки связана со входом, но корреляция отрицательная, то она тем более не может считаться альтернативным объяснением.

Часто на неправильный вывод из корреляции может натолкнуть временная последовательность. Так, некоторые исследователи обнаружили, что прекращение курения приводит к более высокой вероятности смерти. Как известно, бросить курить можно до смерти, но не после неё, так что можно прийти к выводу, что, бросив курить, приближаешь свою смерть. Проблема здесь в неясности логических связей и общих причин, большинство из которых имеет разнонаправленное действие на смертность и прекращение курения. В данном случае на вход действует выход! Очень многие бросают курить лишь при возникновении большого риска для собственного здоровья. Но, как говорит поговорка, поздно пить Боржоми, когда почки отвалились. Такие «бросатели» проживают меньше, чем те, кому врач не советовал бросить. Вот поэтому те исследователи и не увидели ожидаемой положительной корреляции между курением и смертностью. Она есть, но на неё наложилась гораздо более сильная корреляция между угрозой смерти и прекращением курения.

Здесь мы имели дело с известным заблуждением post hoc ergo propter hoc: после этого, значит по причине этого. Как видим, это не всегда так. Добавлю от себя: в случае с курением у нас в распоряжении есть гораздо более наглядная статистика, чем простая корреляция между прекращением курения и смертностью.

Может содержать ложь (3) Книги, Обзор книг, Критическое мышление, Наука, Корреляция, Пруф, Научпоп, Нон-фикшн, Длиннопост

Продажи сигарет и смертность от рака лёгких в США


Путают корреляцию с причинностью все, кому не лень. Особенно нерадивые журналюги. Автор советует нам пользоваться множественной регрессией для того, чтобы исключить действие общих причин. Но я хотел бы напомнить, что регрессия – это инструмент для установления корреляции, но не причинной связи. С её помощью мы можем добиться определённой ясности, но до установления причины и следствия останется ещё долгий путь. Порою – бесконечный.

Показать полностью 3
[моё] Книги Обзор книг Критическое мышление Наука Корреляция Пруф Научпоп Нон-фикшн Длиннопост
2
2
kznalp
kznalp
22 дня назад
Postgres DBA
Серия СУБД PostgreSQL

Особенности расчета коэффициента корреляции в PostgreSQL и Excel при постоянных значениях одной переменной⁠⁠

Особенности расчета коэффициента корреляции в PostgreSQL и Excel при постоянных значениях одной переменной Исследования, Статья, Научпоп, Postgresql, Microsoft Excel, Корреляция, Длиннопост

При обработке статистических данных очень важной задачей становится оценка взаимосвязей между различными переменными. Один из самых популярных способов измерения такой связи — использование коэффициента корреляции Пирсона (Pearson correlation coefficient). Обозначаемый символом r, он представляет собой меру линейной зависимости между двумя величинами. Несмотря на широкое распространение данного метода, важно учитывать специфику реализации этого показателя в разных инструментах обработки данных, таких как системы управления базами данных (СУБД) вроде PostgreSQL и программы электронных таблиц, такие как Microsoft Excel.

Формула коэффициента корреляции Пирсона

Особенности расчета коэффициента корреляции в PostgreSQL и Excel при постоянных значениях одной переменной Исследования, Статья, Научпоп, Postgresql, Microsoft Excel, Корреляция, Длиннопост

Значение коэффициента варьируется от -1 до +1, где положительные значения указывают на прямую связь, отрицательные — на обратную, а близость к нулю свидетельствует об отсутствии значительной линейной связи.

Особенности расчета коэффициента корреляции в PostgreSQL и Excel

Теперь рассмотрим некоторые важные аспекты применения данной формулы в реальных условиях, когда значения одной переменной остаются неизменными по сравнению с другой переменной.

1. Особенности расчета в PostgreSQL

PostgreSQL предоставляет удобную встроенную функцию corr(), позволяющую легко вычислить коэффициент корреляции Пирсона прямо внутри базы данных. Тем не менее, при её применении важно учитывать ряд нюансов:

Отсутствие вариации: Если все значения одной переменной полностью идентичны (то есть стандартное отклонение равно нулю), знаменатель формулы становится нулевым, вызывая деление на ноль. В результате функция вернёт неопределённое значение (NULL).

Численная нестабильность: Когда изменение одной переменной намного меньше, чем другой, возможно возникновение проблемы числовой стабильности. Компьютерная система работает с ограниченным числом знаков после запятой, поэтому малые разности могут быть усечены до нуля, искажая результаты расчётов.

Чтобы избежать подобных проблем, целесообразно заранее провести проверку на наличие дисперсии в данных, используя стандартные функции PostgreSQL, такие как stddev(). Эта мера позволит убедиться, что обе переменные имеют достаточно выраженный разброс значений.

2. Особенности расчета в Excel

Microsoft Excel предлагает аналогичную возможность для вычисления коэффициента корреляции посредством функции CORREL() / КОРРЕЛ(). Здесь также существуют свои тонкости:

Ошибка деления на ноль: Подобно PostgreSQL, если значения одной переменной абсолютно одинаковые, Excel столкнется с ошибкой деления на ноль и выведет сообщение "#DIV/0!".

Различия алгоритмов округления: Хотя основная математическая формула та же самая, внутренние механизмы работы Excel могут немного отличаться от других инструментов. Это связано с особенностями машинного представления вещественных чисел и процедурами округления промежуточных результатов. Эти отличия способны приводить к незначительным, но всё-таки заметным колебаниям в конечном ответе.

Ограничения по представлению чисел с плавающей запятой: Особенно это проявляется, когда масштабы сравниваемых переменных сильно различаются (например, большие числа цен товаров против небольших процентов скидок). Округление промежуточных шагов способно исказить итоговый результат.

Заключение

Итак, правильный подход к оценке коэффициента корреляции требует внимательного рассмотрения характеристик самих данных. Важно понимать структуру информации, подлежащей анализу, проводить предварительные проверки на наличие достаточной вариативности в обоих параметрах и избегать серьёзных перекосов в масштабах значений. В противном случае результат может оказаться неверным или интерпретироваться неправильно. Таким образом, аккуратность и тщательность подготовки данных являются залогом надёжности любых последующих выводов.

Дополнительные материалы по теме:

Особенности расчета коэффициента корреляции в PostgreSQL

Показать полностью 2
[моё] Исследования Статья Научпоп Postgresql Microsoft Excel Корреляция Длиннопост
0
4
kznalp
kznalp
23 дня назад
Postgres DBA
Серия СУБД PostgreSQL

PostgreSQL и Excel по разному рассчитывают корреляцию⁠⁠

PostgreSQL и Excel по разному рассчитывают корреляцию Postgresql, Microsoft Excel, Корреляция

PostgreSQL (Версия 17)

CREATE TABLE test

(

id integer ,

n1 double precision ,

n2 double precision

);

INSERT INTO test

(

id ,

n1 ,

n2

)

VALUES

(

generate_series(1, 100),

trunc(random()*100),

0.01

);

select * from test order by 1;

id | n1 | n2

-----+----+------

1 | 15 | 0.01

2 | 95 | 0.01

3 | 17 | 0.01

...

97 | 44 | 0.01

98 | 63 | 0.01

99 | 59 | 0.01

100 | 24 | 0.01

corr ( Y double precision, X double precision ) → double precision

Вычисляет коэффициент корреляции.

SELECT corr( n1 , n2 ) FROM test ;

corr

--------------------

0.1460196805623383

(1 row)

Excel (Microsoft Office профессиональный 2016)

Импортировать таблицу . Числовой тип , 15 символов после запятой

PostgreSQL и Excel по разному рассчитывают корреляцию Postgresql, Microsoft Excel, Корреляция

Рассчитать корреляцию между столбцами n1 и n2

=КОРРЕЛ(B:B;C:C)

PostgreSQL и Excel по разному рассчитывают корреляцию Postgresql, Microsoft Excel, Корреляция

Результаты расчета корреляции

  • PostgreSQL : 0.1460196805623383

  • Excel : 0,000000000000000

Подробности и другие сценарии:

Особенности расчета коэффициента корреляции в PostgreSQL

Показать полностью 3
[моё] Postgresql Microsoft Excel Корреляция
1
0
kznalp
kznalp
5 месяцев назад
Postgres DBA
Серия СУБД PostgreSQL

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример)⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Если правильно и вовремя обслуживать - СУБД будет летать !

Постановка задачи

  1. Проанализировать инциденты снижения скорости СУБД.

  2. Установить причины снижения скорости СУБД.

  3. Сформировать список мероприятий для устранения причин снижения скорости СУБД.

  4. Итог.

Шаблон решения задачи

https://dzen.ru/a/Z-4mhu9oFCnB-jp9

Отчетность для анализа

https://dzen.ru/a/Z-6YLjZRni5hLvtD

1.Проанализировать инциденты снижения скорости СУБД

Ожидания и корреляция по инцидентам снижения скорости СУБД

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Фрагмент таблицы инцидентов снижения скорости СУБД

Столбцы таблицы:

  • ID : идентификатор инцидента снижения скорости СУБД

  • START TIME : время начала инцидента

  • FINISH TIME : время окончания инцидента

  • № : порядковый номер

  • IO : количество ожидания типа IO на время начала инцидента

  • IO CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями IO за отрезок [ START TIME - 1 ЧАС ; START TIME ]

  • LWLock : количество ожидания типа LWLock на время начала инцидента

  • LWLock CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями LWLock за отрезок [ START TIME - 1 ЧАС ; START TIME ]

Количество ожидания типа IO , LWLock по инцидентам

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - ID инцидента. Ось Y - количество ожидания типа IO на начало инцидента

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - ID инцидента. Ось Y - количество ожидания типа LWLock на начало инцидента

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - ID инцидента. Ось Y - коэффициент корреляции между всеми ожиданиями и ожиданиями типа IO , LWLock на начало инцидента

Особенности инцидентов 34 , 36 :

  1. Коэффициент корреляции между ожиданиями СУБД в целом и ожиданиями типа LWLock больше , чем между ожиданиями СУБД в целом и ожиданиями типа IO.

  2. Количество ожидания типа LWLock меньше чем количество ожидания типа IO.

Графики операционной скорости и ожиданий по инцидентам снижения скорости

Инцидент 34

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение операционной скорости.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock.

Инцидент 36

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение операционной скорости.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock.

Для справки: ожидания типа IO , LWLock по данным отчета "Top wait events" pgpro_pwr

Инцидент 34

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Инцидент 36

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

2. Установить причины снижения скорости СУБД

SQL запросы, имеющие наибольшую долю ожидания заданного типа

Инцидент 34

Ожидания типа IO

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа IO

Ожидания типа LWLock

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа LWLock

Столбцы таблицы:

  • QUERYID : queryid SQL выражения , из представления pgpro_stats.

  • PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.

  • CALLS : количество выполнений SQL выражения

  • WAITINGS : количество ожиданий

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение

  • WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.

Результат:

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Инцидент 36

Ожидания типа IO

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа IO

Ожидания типа LWLock

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа LWLock

Столбцы таблицы:

  • QUERYID : queryid SQL выражения , из представления pgpro_stats.

  • PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.

  • CALLS : количество выполнений SQL выражения

  • WAITINGS : количество ожиданий

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение

  • WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.

Результат:

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Главная причина снижения скорости СУБД

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Текст запроса

Доступен в pgpro_pwr

План выполнения запроса

Доступен в pgpro_pwr

События ожидания при выполнении запроса 2092406791392746781

Инцидент 34

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика выполнения и событий ожидания по запросу 2092406791392746781

Столбцы таблицы:

  • timestamp : точка времени сбора статистических данных уровня SQL.

  • datname : База данных, в которой выполнялся SQL запрос.

  • rolname : Роль, под которой выполнялся SQL запрос.

  • CALLS : Количество выполнений запроса .

  • WAITINGS : Количество ожиданий типа IO , LWLock .

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение.

  • WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

  • SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

  • IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

  • LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Инцидент 36

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика выполнения и событий ожидания по запросу 2092406791392746781

Cтолбцы таблицы:

  • timestamp : точка времени сбора статистических данных уровня SQL.

  • datname : База данных, в которой выполнялся SQL запрос.

  • rolname : Роль, под которой выполнялся SQL запрос.

  • CALLS : Количество выполнений запроса .

  • WAITINGS : Количество ожиданий типа IO , LWLock .

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение.

  • WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

  • SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

  • IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

  • LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

  • LWLock / BufferMapping : Ожидание при связывании блока данных с буфером в пуле буферов.

  • LWLock / ProcArray : Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

3. Cписок мероприятий для устранения причин снижения скорости СУБД .

Мероприятия для снижения ожиданий DSMFillZeroWrite

События ожидания DSMFillZeroWrite в PostgreSQL связаны с операциями записи в разделяемую память (shared memory), где необходимо заполнить область нулями перед использованием.

Это может происходить при работе с большими объектами или при высокой нагрузке на систему.

Чтобы снизить количество таких событий, можно рассмотреть следующие шаги:

1. Оптимизация запросов и транзакций:

- Убедитесь, что ваши запросы оптимизированы и не выполняют избыточных операций.

- Сократите длительность транзакций, чтобы уменьшить нагрузку на разделяемую память.

2. Настройка параметров конфигурации:

- Увеличьте размер разделяемой памяти (shared_buffers), чтобы уменьшить частоту операций записи в разделяемую память.

- Настройте параметры, связанные с кэшированием и буферизацией, чтобы уменьшить количество операций записи.

3. Оптимизация использования разделяемой памяти:

- Убедитесь, что ваши приложения и расширения эффективно используют разделяемую память.

- Избегайте создания большого количества временных объектов, которые могут приводить к увеличению операций записи в разделяемую память.

Мероприятия для снижения ожиданий ParallelHashJoin

Ожидания ParallelHashJoin могут возникать из-за того, что PostgreSQL использует параллельные запросы для выполнения операций, таких как Hash Join. Это может привести к увеличению количества ожиданий, особенно если у вас много одновременных запросов или ограниченные ресурсы.

1. Отключить параллельные запросы:

- Вы можете отключить параллельные запросы, установив параметр max_parallel_workers_per_gather в 0. Это отключит использование параллельных рабочих процессов для операций, таких как Hash Join.

2. Оптимизировать индексы:

- Убедитесь, что у вас есть правильные индексы на таблицах, участвующих в запросе. Индексы могут помочь ускорить выполнение запросов и уменьшить необходимость в параллельных операциях.

3. Анализ и вакуумизация таблиц:

- Периодически выполняйте команды ANALYZE и VACUUM для обновления статистики и очистки мертвых строк. Это поможет оптимизатору запросов выбрать более эффективные планы выполнения.

4. Настройка параметров планировщика:

- Настройте параметры, такие как random_page_cost и cpu_tuple_cost, чтобы повлиять на выбор плана выполнения запроса. Например, уменьшение random_page_cost может сделать индексные сканирования более привлекательными.

5. Использование правильных операторов JOIN:

- Попробуйте использовать другие типы соединений, такие как Nested Loop или Merge Join, если они подходят для вашего запроса. Вы можете временно отключить Hash Join, установив параметр enable_hashjoin в off.

6. Оптимизация запросов:

- Проверьте, можно ли оптимизировать сами запросы, например, добавив дополнительные условия в WHERE-clause или используя более эффективные подзапросы.

Примеры команд для настройки параметров:

-- Отключить параллельные запросы

SET max_parallel_workers_per_gather = 0;

-- Отключить Hash Join

SET enable_hashjoin = off;

-- Установить параметры планировщика

SET random_page_cost = 1.1;

SET cpu_tuple_cost = 0.01;

Мероприятия для снижения ожиданий BufferMapping

Ожидания на BufferMapping в PostgreSQL могут возникать из-за интенсивных операций чтения, когда база данных часто обращается к данным на диске вместо кэша. Это может происходить, когда рабочий набор данных превышает доступную память, что приводит к частым операциям ввода-вывода (I/O).

1. Увеличение shared_buffers:

- Увеличение параметра shared_buffers может помочь уменьшить количество операций ввода-вывода, так как больше данных будет храниться в памяти.

2. Оптимизация запросов:

- Проверьте и оптимизируйте ваши запросы, чтобы уменьшить количество операций ввода-вывода. Используйте индексы и другие методы оптимизации для уменьшения количества данных, которые нужно считывать из диска.

3. Увеличение effective_cache_size:

- Параметр effective_cache_size помогает PostgreSQL лучше оценивать доступную память для кэширования данных. Увеличение этого параметра может улучшить планирование запросов.

4. Увеличение work_mem и maintenance_work_mem:

- Увеличение параметров work_mem и maintenance_work_mem может помочь уменьшить количество операций ввода-вывода, особенно при выполнении операций сортировки и хранения данных.

5. Анализ и оптимизация индексов:

- Убедитесь, что у вас есть правильные индексы для ваших запросов. Индексы могут значительно уменьшить количество операций ввода-вывода.

6. Обновление аппаратного обеспечения:

- Если возможно, обновите аппаратное обеспечение, особенно увеличьте объем оперативной памяти и используйте более быстрые диски (например, SSD).

7. Распределение нагрузки:

- Анализируйте и оптимизируйте распределение нагрузки между сессиями, чтобы уменьшить конкуренцию за ресурсы.

Мероприятия для снижения ожиданий ProcArrayLock

Задержки, связанные с блокировкой ProcArrayLock, могут возникать из-за интенсивной активности рабочих процессов, которые создают конкуренцию за доступ к ProcArray. Это особенно актуально при выполнении параллельных запросов и операций, таких как walsender.

Для уменьшения задержек ProcArray можно рассмотреть следующие шаги:

1. Оптимизация рабочих процессов:

- Уменьшите количество одновременно выполняемых рабочих процессов, чтобы снизить нагрузку на ProcArrayLock.

- Оптимизируйте параллельные запросы, чтобы уменьшить их длительность и уменьшить время блокировки.

2. Настройка параметров конфигурации:

- Уменьшите значение параметра max_standby_streaming_delay, чтобы уменьшить задержку репликации.

- Настройте параметры, связанные с параллелизмом, такие как max_parallel_workers_per_gather и max_worker_processes, чтобы управлять количеством рабочих процессов.

3. Оптимизация хранения данных:

- Убедитесь, что у вас используется оптимальное хранилище данных, например, AWS EBS GP3, для уменьшения задержек ввода-вывода.

4. Итог

Использование корреляционного анализа ожиданий с помощью оперативно-тактического комплекса pg_hazel позволяет резко сократить время на поиск корневой причины снижения скорости СУБД и оперативно предоставить мероприятия для устранения причин.

Показать полностью 20
[моё] Субд Postgresql Мониторинг Производительность Корреляция Длиннопост
2
kznalp
kznalp
5 месяцев назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Хирург и DBA это холодная голова и горячее сердце.

Начало :

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск потенциально проблемных SQL запросов при продуктивной нагрузке .

Продолжение тестирования методологии использования корреляционного анализа для поиска проблемных SQL запросов при продуктивной нагрузке на СУБД .

Постановка задачи:

Проанализировать причины снижения скорости СУБД и найти проблемные запросы:

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Дашбоард мониторинга производительности СУБД

Операционная скорость СУБД - снижается.

Ожидания СУБД - растут.

Метрика относительной доли ожиданий - исключена из анализа.

Словарь терминов , используемых при корреляционном анализе.

Операционная скорость на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - значение операционной скорости

Ожидания на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - общее количество ожиданий СУБД

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - количество ожиданий типа IO

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - количество ожиданий типа LWLock

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - количество ожиданий типа IPC

Корреляционный анализ ожиданий и определение потенциально проблемных SQL запросов

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Таблица коэффициентов корреляции

  1. Сильная отрицательная корреляция между скоростью и ожиданиями .

  2. Наиболее сильная положительная корреляция между всеми ожиданиями и ожиданиями типа IPC.

  3. Сильная положительная корреляция между всеми ожиданиями и ожиданиями типа LWLock , IO .

Корреляционный анализ на уровне запросов SQL по ожиданию типа IPC

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

TOP-10 таблицы коэффициентов корреляции для SQL запросов с ожиданиями типа IPC

Столбцы таблицы

  • QUERYID : id SQL запроса

  • PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

  • CORRELATION : коэффициент корреляции между ожиданиями типа IPC по всем SQL запросам и ожиданиям типа IPC по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа IPC по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

  • WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа IPC для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 3985919093425059746

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

История выполнений и ожиданий запроса 3985919093425059746

События ожидания:

  • BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

  • ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

  • ExecuteGather Ожидание активности дочернего процесса при выполнении узла плана Gather.

Корреляционный анализ на уровне запросов SQL по ожиданию типа LWLock

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

TOP-10 таблицы коэффициентов корреляции для SQL запросов с ожиданиями типа LWLock.

Столбцы таблицы

  • QUERYID : id SQL запроса

  • PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

  • CORRELATION : коэффициент корреляции между ожиданиями типа LWLock по всем SQL запросам и ожиданиям типа LWLock по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа LWLock по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

  • WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа LWLock для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 2092406791392746781

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

История выполнений и ожиданий запроса 2092406791392746781

События ожидания:

  • ParallelHashJoin Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Корреляционный анализ на уровне запросов SQL по ожиданию типа IO

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

TOP-10 таблицы коэффициентов корреляции для SQL запросов с ожиданиями типа IO

Столбцы таблицы

  • QUERYID : id SQL запроса

  • PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

  • CORRELATION : коэффициент корреляции между ожиданиями типа IO по всем SQL запросам и ожиданиям типа IO по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа IO по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

  • WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа IO для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 5680299967307342186

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

История выполнений и ожиданий запроса 5680299967307342186

События ожидания:

  • DataFileExtend Ожидание расширения файла данных отношения.

  • DataFileRead Ожидание чтения из файла данных отношения.

Итог

Корреляционный анализ ожиданий СУБД может быть использован для поиска проблемных SQL запросов и первоначального анализа проблемы и путей оптимизации SQL запросов.

Показать полностью 14
[моё] Субд Postgresql Производительность Корреляция Длиннопост
0
2
kznalp
kznalp
5 месяцев назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Работа DBA в чем то , очень отдаленно, напоминает работу хирурга.

Постановка задачи

Проанализировать причины снижения скорости СУБД и найти проблемные запросы

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Дашбоард мониторинга производительности СУБД

Операционная скорость СУБД - снижается.

Ожидания СУБД - растут.

Порядок проведения корреляционного анализа

Операционная скорость на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - значение операционной скорости

Ожидания на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - количество ожиданий СУБД

Корреляционный анализ на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Таблица коэффициентов корреляции

  1. Средняя отрицательная корреляция между операционной скоростью и ожиданиями.

  2. Тип ожидания имеющий наибольшую корреляция с общим количеством ожиданий - IO.

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - количество ожиданий типа IO

Статистика выполнений и ожиданий по отдельным SQL запросам по типу ожидания IO

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Таблица статистических данных выполнения и ожиданий по отдельным запросам.

Столбцы таблицы

  • QUERYID : id SQL запроса

  • CORRELATION : коэффициент корреляции между ожиданиями типа IO по всем SQL запросам и ожиданиям типа IO по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа IO по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количество выполнений. Среднее количество ожидания за одно выполнение.

Таблица отсортирована по столбцам QUERYID / WAITINGS TO CALL .

Статистика выполнений и ожиданий по выбранным SQL-запросам

-7843470278038126227

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Статистика выполнений и ожиданий для queryid =-7843470278038126227

События ожидания:

  • DataFilePrefetch: Ожидание асинхронной предвыборки из файла данных отношения.

  • DataFileRead : Ожидание чтения из файла данных отношения.

  • DataFileWrite : Ожидание записи в файл данных отношения.

  • WALSync : Ожидание помещения файла WAL в надёжное хранилище.

  • WALWrite : Ожидание записи в файл WAL.

Результат анализа по запросу -7843470278038126227:

Выполнение процедуры ANALYZE во время продуктивной нагрузки на СУБД приводит к ожиданиям. Необходимо пересмотреть порядок проведения регламентных работ на СУБД.

-8198400089192679786

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Статистика выполнений и ожиданий для queryid =-8198400089192679786

События ожидания:

  • DataFileRead : Ожидание чтения из файла данных отношения.

Результат анализа по запросу -8198400089192679786

Необходимо провести анализ плана выполнения с целью снижения операций дискового чтения.

Итог

Использование корреляционного анализа позволяет определить степень влияния потенциально проблемных SQL запросов на общую скорость и ожидания СУБД.

Показать полностью 9
[моё] Субд Postgresql Производительность Корреляция Длиннопост
0
kznalp
kznalp
5 месяцев назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности⁠⁠

Взято с основного технического канала Postgres DBA

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Для быстрой работы системы - каждая отдельная часть должна быть настроена идеально.

Словарь терминов

Постановка проблемы эмерджентности СУБД

Решение проблемы

Для решения проблемы эмерджентности рассмотрим практический пример анализа производительности СУБД под продуктивной нагрузкой.

Операционная скорость и скользящая корреляция на уровне СУБД

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения . Ось Y - операционная скорость и скользящая медиана скорости и ожиданий

Анализ графика

  1. Имеется снижение производительности

  2. Скользящая корреляция постоянно в положительной зоне

Т.е. малым значениям операционной скорости соответствуют малые значения ожиданий.

Снижения производительности нет ?

Корреляционный анализ на уровне баз данных

Для корректного анализа производительности , необходимо анализировать корреляцию между операционной скоростью и ожиданиями не на уровне СУБД, а на уровне отдельных баз данных.

В основе остается та же идея

Основная гипотеза корреляционного анализа ожиданий СУБД

Новые метрики производительности СУБД

1. Относительная доля(%) баз данных, имеющих отрицательную корреляцию между операционной скоростью и ожиданиями.

2. Относительная доля(%) баз данных, имеющих среднюю и сильную отрицательную корреляцию между операционной скоростью и ожиданиями.

Если теперь посмотреть значение данных метрик, за тот же период наблюдения, появляются совершенно новые детали для начала анализа.

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение Метрики 1 и 2.

Результат анализа метрик относительной доли отрицательной корреляции.

  1. Рост количества баз данных имеющих отрицательную корреляцию между скоростью и ожиданиями

  2. Рост количества баз данных имеющих среднюю и сильную отрицательную корреляцию между скоростью и ожиданиями

Главный итог использования новых метрик

Имеются базы данных по которым возможно снижение скорости работы , хотя на уровне СУБД снижения не установлено.

Далее , используя полученные данные, уже можно примерить использованную ранее методику, хорошо зарекомендовавшую себя при нагрузочном тестировании.

Корреляционный анализ ожиданий для сценариев нагрузочного тестирования СУБД PostgreSQL.

Корреляционный анализ ожиданий баз данных и выявление SQL запросов вызывающих наибольшее влияние на снижение скорости баз данных.

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Таблица коэфициентов корреляции по базам данных.

  • База данных DB-1 - имеет наиболее сильную отрицательную корреляцию.

  • База DB-2 - исключается из дальнейшего анализа. Причина - отсутствие отрицательной корреляции по событию типу ожидания.

Корреляционный анализ по базе данных "DB-1"

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение операционной скорости

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO по базе данных DB-1

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

SQL запрос, имеющий наибольшую корреляцию с типом ожидания IO

Корреляционный анализ по базе данных "DB-3"

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение операционной скорости

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO по базе данных DB-3

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

SQL запрос, имеющий наибольшую корреляцию с типом ожидания IO

Корреляционный анализ по базе данных "DB-4"

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение операционной скорости

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа Extension по базе данных DB-4

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock по базе данных DB-4

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

SQL запросs, имеющий наибольшую корреляцию с типами ожидания Extension , LWLock.

Итог

  1. Использование корреляционного анализа позволяет выявить SQL выражения потенциально, имеющие влияние на скорость работы базы данных.

  2. Стартовым событием для начала анализа является отрицательная корреляция между операционной скоростью и ожиданиями для отдельной базы данных.

  3. Мониторинг операционной скорости, ожиданий и корреляции между операционной скоростью и ожиданиями на уровне СУБД в целом, при условии разнородного характера нагрузки на отдельный базы данных , входящих в кластер СУБД - не позволяет получить корректную картину для начала анализа.

  4. Для решения проблемы эмерджентности СУБД , необходимо мониторить метрики производительности на уровне отдельных баз данных , а не СУБД в целом.

Показать полностью 13
[моё] Исследования Инженер Субд Postgresql Корреляция Длиннопост
0
kznalp
kznalp
5 месяцев назад
Postgres DBA
Серия СУБД PostgreSQL

Является ли СУБД эмерджентной системой ?⁠⁠

Взято с основного технического канала Postgres DBA

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

В некоторых системах - целое больше, чем сумма его частей.

Эмердже́нтность или эмерге́нтность (англиц. от emergent «возникающий, неожиданно появляющийся»)[1] в теории систем — наличие у системы свойств, не присущих её компонентам по отдельности; несводимость свойств системы к сумме свойств её компонентов.

В связи с началом тестирования комплекса pg_hazel под продуктивной нагрузкой возникает очень важный вопрос:

Можно ли рассчитывать метрики производительности СУБД суммируя значения метрик отдельных баз данных, составляющих кластер PostgreSQL ?

Словарь терминов оперативно-тактического комплекса "PG_HAZEL".

Экспериментальные данные

В качестве источника данных для расчета метрик производительности кластера , используется представление pgpro_stats_totals

G.4.4.2. Представление pgpro_stats_totals

Агрегированная статистика, собранная модулем, выдаётся через представление pgpro_stats_totals.

Операционная скорость СУБД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - операционная скорость СУБД

Количество ожиданий СУБД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - Количество ожиданий по СУБД

Коэффициент корреляции между операционной скоростью и ожиданиями составляет -0,8736.

Вопрос : является ли ситуация существенного снижения операционной скорости и роста ожиданий СУБД - инцидентом деградации производительности СУБД?

Анализ операционной скорости по отдельным базам данных

Для расчета операционной скорости по отдельному SQL выражению используется представления pgpro_stats_statements

G.4.4.1. Представление pgpro_stats_statements

Статистика, собираемая модулем, выдаётся через представление с именем pgpro_stats_statements. Это представление содержит отдельные строки для каждой комбинации идентификатора базы данных, идентификатора пользователя и идентификатора запроса

Для расчета операционной скорости по базе данных применяется агрегирование .

Для сокращения анализа, рассмотрим только список баз данных с наиболее коррелированными(близкими) значениями операционной скорости со значениями операционной скорости СУБД .

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - операционная скорость СУБД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Таблица значение операционной скорости СУБД и отдельных баз данных.

Для простоты достаточно рассмотреть графики операционной скорости для баз данных по которым количество значений наиболее близко к количеству значений по СУБД в целом - test_db-7 , test_db-6 и test_db-4.

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - операционная скорость БД test_db-7

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - операционная скорость БД test_db-6

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - операционная скорость БД test_db-4

Вывод по итогам анализа операционной скорости при работе СУБД под продуктивной нагрузкой

График значений операционной скорости для СУБД в целом, очень сильно отличается от графиков значений операционной скорости баз данных входящих в кластер.

Корреляционный анализ на уровне баз данных

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Список БД имеющих обратную корреляцию между операционной скоростью и ожиданиями

Интересные детали

База данных DB-1 имеет значение коэффициента корреляции между значениями операционной скорости и ожиданиями = -0.3985 , но если рассчитать коэффициент корреляции между операционной скоростью и количеством ожиданий конкретного типа, то все значения положительны.

Если посмотреть график истории ожиданий, становится понятно, почему так происходит.

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение операционной скорости для БД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - Общее количество ожиданий для БД

Получается слабая отрицательная корреляция

Однако , если посмотреть графики истории ожиданий по типам ожиданий IO , IPC , LWLock корреляция будет совсем другой :

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа IO для БД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа IPC для БД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock для БД

Вывод

При расчете метрик производительности СУБД как суммы значений метрик производительности по базам данных, входящим в кластер СУБД, возможна ошибка второго рода - анализ инцидента деградации производительности СУБД при отсутствии деградации производительности баз данных.

Необходимо подготовить методику применения корреляционного анализа в условиях продуктивной нагрузки на СУБД.

Таким образом, можно сформулировать гипотезу

Если в состав кластера СУБД входят разнородные базы данных , то СУБД обладает свойством эмерджентности. Нельзя анализировать поведение СУБД в целом. Необходимо анализировать поведение отдельных баз данных.

Показать полностью 14
[моё] Исследования Субд Postgresql Корреляция Длиннопост
3
Посты не найдены
О нас
О Пикабу Контакты Реклама Сообщить об ошибке Сообщить о нарушении законодательства Отзывы и предложения Новости Пикабу Мобильное приложение RSS
Информация
Помощь Кодекс Пикабу Команда Пикабу Конфиденциальность Правила соцсети О рекомендациях О компании
Наши проекты
Блоги Работа Промокоды Игры Курсы
Партнёры
Промокоды Биг Гик Промокоды Lamoda Промокоды Мвидео Промокоды Яндекс Директ Промокоды Отелло Промокоды Aroma Butik Промокоды Яндекс Путешествия Постила Футбол сегодня
На информационном ресурсе Pikabu.ru применяются рекомендательные технологии