Горячее
Лучшее
Свежее
Подписки
Сообщества
Блоги
Эксперты
Сообщество - Postgres DBA
Добавить пост

Postgres DBA

157 постов • 27 подписчиков

Популярные теги в сообществе:

Все посты  Горячее  Лучшее  Поиск
0 просмотренных постов скрыто
3

PG_HAZEL : Комплексный анализ инцидента производительности СУБД PostgreSQL⁠⁠

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

СУБД это не только software но и не менее важное hardware.

СУБД это не только software но и не менее важное hardware.

Задача

Провести комплексный(СУБД + ОС) анализ причин инцидента производительности СУБД .

Инцидент производительности СУБД

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

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

Отчет по инцидентам производительности

incidents_to_timepoint.sh - отчет по инцидентам производительности за период

cd /postgres/scripts/tester/reports/incidents

./incidents_to_timepoint.sh '2025-08-19 10:45' '2025-08-19 12:00'

Время начала инцидента

Фрагмент отчета

Фрагмент отчета

Начало инцидента : 11:50

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

cluster_performance.sh - метрики оценки производительности СУБД

cd /postgres/scripts/tester/reports/detailed

./cluster_performance.sh '2025-08-19 10:50' '2025-08-19 11:50'

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

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

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

Ожидания СУБД

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

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

Абсолютные значения по скорости и ожиданиям СУБД за период в течении часа до начала инцидента

Абсолютные значения по скорости и ожиданиям СУБД за период в течении часа до начала инцидента

Корреляция по типам ожиданий

Корреляция по типам ожиданий

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

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

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

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

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

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

Результат корреляционного анализ ожиданий СУБД

Наибольшая корреляция по событиям ожидания и снижением скорости СУБД:

  • IPC : Серверный процесс ожидает взаимодействия с другим процессом.

  • Lock : Серверный процесс ожидает тяжёлую блокировку.

Корреляционный анализ метрик оценки производительности инфраструктуры

vmstat.sh - отчет по метрикам vmstat

iostat_cpu.sh - отчет по метрикам iostat для CPU

iostat_device.sh - отчет по метрикам iostat для I/O

./vmstat.sh '2025-08-19 10:50' '2025-08-19 11:50'

./iostat_cpu.sh '2025-08-19 10:50' '2025-08-19 11:50'

lsblk

./iostat_device.sh '2025-08-19 10:50' '2025-08-19 11:50' 'vdb vdc'

VMSTAT

Корреляция между ожиданиями СУБД и метриками vmstat

Корреляция между ожиданиями СУБД и метриками vmstat

Абсолютные значения метрик vmstat

Абсолютные значения метрик vmstat

Корреляция между ожиданиями СУБД и метриками vmstat:

  • r : процессы в run queue (готовы к выполнению)

  • free : свободная RAM

  • buff : буферы

Исторические графики метрик vmstat

r : процессы в run queue (готовы к выполнению)

free : свободная RAM

buff : буферы

iostat_cpu

Корреляция между ожиданиями СУБД и метриками iostat_cpu

Корреляция между ожиданиями СУБД и метриками iostat_cpu

Абсолютные значения метрик iostat_cpu

Абсолютные значения метрик iostat_cpu

Корреляция между ожиданиями СУБД и метриками iostat_cpu: отсутствует

iostat_device (файловая система /data)

Корреляция между ожиданиями СУБД и метриками iostat_device(файловая система /data)

Корреляция между ожиданиями СУБД и метриками iostat_device(файловая система /data)

Абсолютные значения метрик iostat_device (файловая система /data)

Абсолютные значения метрик iostat_device (файловая система /data)

Корреляция между ожиданиями СУБД и метриками iostat_device(файловая система /data) : отсутствует

iostat_device (файловая система /wal)

Результаты аналогичны.

Корреляция между ожиданиями СУБД и метриками iostat_device(файловая система /wal) : отсутствует

Результат корреляционного анализа метрик оценки производительности инфраструктуры

Аномальная корреляция и влияние инфраструктуры на рост ожидания СУБД - не установлено.

SQL-запросы для оптимизации по результатам отчета incidents_to_timepoint.sh

Ожидания(wait_event_type / wait_event) в ходе инцидента

Наибольшее количество запросов в ходе инцидента имеет ожидание IPC / BgWorkerShutdown

Наибольшее количество запросов в ходе инцидента имеет ожидание IPC / BgWorkerShutdown

SQL-запросы для оптимизации

Наибольшая доля ожиданий IPC у SQL запроса 187233199925020157

Наибольшая доля ожиданий IPC у SQL запроса 187233199925020157

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

Фрагмент полного списка SQL-запросов

Фрагмент полного списка SQL-запросов

Корреляционный анализ отдельного SQL-запроса

queryid = 187233199925020157

queryid_stat.sh - события ожидания по заданному SQL-запросу за период

cd /postgres/scripts/tester/reports/detailed

./queryid_stat.sh 187233199925020157 '2025-08-19 10:45' '2025-08-19 12:00'

События ожидания по SQL-запросу

Наибольшая корреляция и наибольшее количество ожиданий:

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

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

Мероприятия для оптимизации SQL-запроса

Добавить индекс в таблицу используемую для представления VIEW_1

PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов.

Показать полностью 22
[моё] Субд Postgresql Исследования Статья Производительность Длиннопост
1
2

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

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

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

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

Особенности расчета коэффициента корреляции в PostgreSQL и 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

PostgreSQL и 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 символов после запятой

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

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

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

  • PostgreSQL : 0.1460196805623383

  • Excel : 0,000000000000000

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

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

Показать полностью 3
[моё] Postgresql Microsoft Excel Корреляция
1
4

PG_HAZEL - комплексное решение для мониторинга и анализа производительности СУБД PostgreSQL⁠⁠

Три слона на черепахе смогут удержать СУБД в океане бурь и штормов.

Три слона на черепахе смогут удержать СУБД в океане бурь и штормов.

Основа и фундамент PG_HAZEL

Методы и инструменты статистического анализа данных о СУБД и инфраструктуре .

PG_HAZEL : Первая часть

Методы оценки и анализа производительности СУБД.

PG_HAZEL : Вторая часть

Методология корреляционного анализа ожиданий СУБД.

PG_HAZEL : Третья часть

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

Вперед - к новым открытиям

Вперед - к новым открытиям

PG_HAZEL : Мониторинг и анализ производительности СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД PostgreSQL - презентация по докладу, не попавшему на конференцию PGConf.СПб 2025.

PG_HAZEL : Методология нагрузочного тестирования.

PG_HAZEL : Анализ инцидентов производительности СУБД

PG_HAZEL : Сравнительный анализ инфраструктуры по результатам нагрузочного тестирования СУБД

Показать полностью 2
[моё] Субд Postgresql Длиннопост
0
3

PostgreSQL — СУБД №1 по данным Stack Overflow⁠⁠

Открытая система управления базами данных занимает первое место по популярности, уровню удовлетворенности, а еще — лидирует среди систем, с которыми хотят работать респонденты.

➡️ Главное про открытую СУБД по итогам исследования Stack Overflow рассказали в карточках.

Полностью : https://t.me/postgres_pro/998

Показать полностью 5
Субд Postgresql Telegram (ссылка) Длиннопост IT
11
4

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

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

Общие принципы работы одинаковы, но размер имеет значение.

Общие принципы работы одинаковы, но размер имеет значение.

Задача

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

Малая СУБД

CPU = 2

RAM = 2GB

Размер тестовой БД = 10GB

Тестовая таблица ~60 000 000 строк

Влияние настройки autovacuum на производительность СУБД

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

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

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

Операционная скорость за весь период теста

  • Средняя относительная разница операционной скорости в экспериментах 1 и 2 составила : -3%

Операционная скорость при малой нагрузке ( до 10 соединений)

  • Средняя относительная разница операционной скорости в экспериментах 1 и 2 составила : 4%

Операционная скорость при высокой нагрузке ( свыше 10 соединений)

  • Средняя относительная разница операционной скорости в экспериментах 1 и 2 составила : -6%

Большая СУБД

CPU = 200

RAM = 1TB

Размер тестовой БД = 10TB

Тестовая таблица ~70 000 000 000 строк

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

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

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

  • Средний прирост производительности в эксперименте-7 по сравнению с экспериментом-1 составил 13.30%

  • Максимальный прирост производительности в эксперименте-7 по сравнению с экспериментом-1 составил 35.83%

Вывод

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

Показать полностью 2
[моё] Инженер Субд Postgresql Настройка Тестирование Длиннопост
2
2

Влияние уменьшения fillfactor на работу autovacuum⁠⁠

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

Регулярная уборка это правильно .

Регулярная уборка это правильно .

Задача

Оценить влияние кардинального снижения параметра хранения таблиц fillfactor c базового значения 100 до 50, при агрессивной настройке autovacuum.

Начало работ

PG_HAZEL : Влияние уменьшения параметра fillfactor на производительность СУБД PostgreSQL

Конфигурация виртуальной машины

CPU = 2

RAM = 2GB

Версия СУБД:

Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

Размер тестовой БД = 10GB

Тестовая таблица ~60 000 000 строк

Параметры настройки autovacuum на уровне СУБД

autovacuum_analyze_scale_factor 0.1

autovacuum_analyze_threshold 50

autovacuum_vacuum_cost_delay 2 ms

autovacuum_vacuum_cost_limit -1

autovacuum_vacuum_insert_scale_factor 0.2

autovacuum_vacuum_insert_threshold 1000

autovacuum_vacuum_scale_factor 0.2

autovacuum_vacuum_threshold 50

vacuum_cost_limit 2000

Параметры настройки autovacuum для тестовой таблицы

autovacuum_vacuum_scale_factor 0

autovacuum_vacuum_threshold 1000

autovacuum_analyze_scale_factor 0

autovacuum_analyze_threshold 1000

autovacuum_vacuum_insert_scale_factor 0

autovacuum_vacuum_insert_threshold 1000

autovacuum_vacuum_cost_delay 1

autovacuum_vacuum_cost_limit 2000

Нагрузка при тестировании : 5-48

Ось X - итерация теста. Ось Y - количество одновременных сессий pgbench

Ось X - итерация теста. Ось Y - количество одновременных сессий pgbench

Эксперимент-2 : fillfactor = 100

Интенсивность и показатели выполнения autovacuum

Ось X - точка завершения autovacuum. Ось Y - время выполнения autovacuum.

Ось X - точка завершения autovacuum. Ось Y - время выполнения autovacuum.

Нагрузка начала роста времени работы autovacuum = 8

Эксперимент-3 : fillfactor = 50

Ось X - точка завершения autovacuum. Ось Y - время выполнения autovacuum.

Ось X - точка завершения autovacuum. Ось Y - время выполнения autovacuum.

Нагрузка начала роста времени работы autovacuum = 12

Итоги

Для данной СУБД и данного сценария нагрузки , уменьшение параметра хранения таблиц на 50% c 100 до 50 , повысило эффективность работы autovacuum:

  • Количество запусков autovacuum увеличилось на 261% c 193 до 697

  • Максимальное время работы autovacuum уменьшилось на 29% с 936 сек. до 663 сек.

  • Среднее время работы autovacuum уменьшилось на 73% с 46 сек. до 12 сек.

  • Предельная нагрузка на autovacuum увеличивалась на 50% с 8 до 12 соединений.

Показать полностью 5
[моё] Субд Postgresql Настройка Тестирование Оптимизация Длиннопост
1
4

Причины увеличения количества мертвых строк при оптимизации параметров autovacuum⁠⁠

Зри в корень (с)

Зри в корень (с)

Гипотеза об ограничении процесса autovacuum для высокопроизводительных СУБД:

  1. Изменение параметров приводит к росту производительности СУБД

  2. Рост производительности СУБД означает большее количество операций UPDATE в ходе нагрузочного тестирования

  3. Принципиальное ограничение - производительность процесса autovacuum ограничена пропускной способности дисковой подсистемы.

Следствие :

Мертвые строки накапливаются быстрее, чем autovacuum успевает очищать

Итог:

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

  • В случае режима работы 24/7 - только архитектурное решение - не допускать роста таблиц до огромных размеров , для которых используется очень большое количество операций INSERT/UPDATE.

Показать полностью 1
[моё] Субд Postgresql Тестирование
0
Отличная работа, все прочитано!