Postgresql и Нейронные сети: истории из жизни, советы, новости, юмор — Горячее

0 просмотренных постов скрыто

kznalp

Postgres DBA

PG_EXPECTO : Использование нейросети для анализа результатов нагрузочного тестирования СУБД PostgreSQL⁠⁠

Серия СУБД PostgreSQL

1 день назад

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

PG_EXPECTO: Когда нейросеть видит то, что скрыто в данных.

Нагрузочное тестирование — это не просто сбор метрик, а сложный процесс их интерпретации. Традиционный анализ требует значительного времени и опыта. Проект PG_EXPECTO демонстрирует, как современные нейросети могут стать мощным соавтором инженера, мгновенно выявляя узкие места, коррелируя данные из разных источников (СУБД, ОС, диски) и формулируя конкретные рекомендации. Эта статья — практический пример превращения сырых данных нагрузочного теста в готовый план действий с помощью искусственного интеллекта.

Задача

Протестировать результаты использования нейросети для анализа результирующих данных по производительности СУБД и инфраструктуры в ходе нагрузочного тестирования.

Шаг-1: Формирование файлов статистических данных и промптов для нейросети по окончании нагрузочного тестирования

cd /postgres/pg_expecto/sh/performance_reports

./load_test_report.sh

$ ./load_test_report.sh
TIMESTAMP : 03-01-2026 15:10:17 : OK : ОТЧЕТ ПО НАГРУЗОЧНОМУ ТЕСТИРОВАНИЮ - НАЧАТ
…
TIMESTAMP : 03-01-2026 15:10:22 : OK : 1.ВХОДНЫЕ ДАННЫЕ ДЛЯ НЕЙРОСЕТИ - СУБД И VMSTAT/IOSTAT
TIMESTAMP : 03-01-2026 15:10:22 : OK : 2.ВХОДНЫЕ ДАННЫЕ ДЛЯ НЕЙРОСЕТИ - IO PERFORMANCE
TIMESTAMP : 03-01-2026 15:10:22 : OK : DEVICE = vdd
TIMESTAMP : 03-01-2026 15:10:22 : OK : DEVICE = vdc
TIMESTAMP : 03-01-2026 15:10:22 : OK : ОТЧЕТ ПО НАГРУЗОЧНОМУ ТЕСТИРОВАНИЮ - ВЫПОЛНЕН

Результирующие файлы:

Статистические данные по производительности СУБД и инфраструктуры : 1.summary.txt
Промпт для нейросети для формирования отчета по производительности СУБД и инфраструктуры : _1.prompt.txt
Статистические данные по производительности подсистемы IO: _2.io_performance.txt
Промпт для нейросети для формирования отчета по производительности подсистемы IO: _2.io_performance_prompt.txt

Шаг-2: Формирование итогового отчета по производительности СУБД и инфраструктуры

Промпт

Проанализируй данные по метрикам производительности и ожиданий СУБД , метрикам инфраструктуры vmstat/iostat. Подготовь итоговый отчет по результатам анализа.

1. Общая характеристика системы

PostgreSQL версия: конфигурация из postgresql.auto.conf
Аппаратные ресурсы:
CPU: 8 ядер (Intel Xeon)
RAM: 7.5 GB
Диски: отдельные разделы для данных (100GB), WAL (50GB), логов (30GB)
Нагрузка: тестирование проводилось с 12:59 до 14:48, нагрузка возрастала с 5 до 22 единиц

2. Ключевые проблемы производительности

2.1. Дисковый ввод-вывод (I/O) — критическая проблема

Ожидания IO составляют 99.93% от всех ожиданий СУБД
Основное событие ожидания: DataFileRead (чтение данных с диска)
В системных метриках (vmstat):
wa (ожидание I/O) постоянно превышает 44-50%
100% наблюдений имеют wa > 10% (крайне тревожный показатель)
Высокая корреляция (0.9755) между ожиданиями IO и процессами в состоянии непрерываемого сна (b)
Количество процессов в состоянии b возрастает с 5 до 14

2.2. Падение производительности под нагрузкой

Производительность (SPEED) снизилась на ~5% (с 284,895 до 265,388)
Ожидания (WAITINGS) выросли в 2.7 раза (с 27,055 до 72,905)
Сильная отрицательная корреляция (-0.71) между SPEED и WAITINGS

2.3. Проблемы с памятью

Свободной оперативной памяти менее 5% в 100% наблюдений
При этом свопинг не используется — вся память активно задействована

2.4. Конкуренция за ресурсы

Заметные ожидания по блокировкам:
Lock: extend (67.61%), transactionid (23.17%)
LWLock: ProcArray (51.61%), BufferContent (31.85%)
Высокая корреляция (0.91) между LWLock и user time CPU

3. Анализ запросов

Три основных запроса вызывают 80% проблем:

scenario1 (queryid: 65003243407475111) — 80.18% ожиданий IO
scenario3 (queryid: 2179758041927844257) — проблемы с блокировками
scenario2 (queryid: -1379548677898522698) — также вносит вклад в ожидания

4. Конфигурационные проблемы PostgreSQL

4.1. Агрессивные настройки autovacuum

autovacuum_naptime = '1s' # Слишком часто
autovacuum_vacuum_cost_delay = '2ms' # Слишком мало
autovacuum_work_mem = '256MB' # Много для каждого воркера

Может создавать избыточную фоновую нагрузку на I/O.

4.2. Параметры памяти

work_mem = '32MB' — может быть недостаточно для сложных операций
shared_buffers = '2GB' — разумно, но стоит мониторить эффективность

5. Рекомендации

5.1. Оптимизация запросов (высший приоритет)

Сфокусироваться на оптимизации scenario1 (80% проблем с I/O)
Рассмотреть:
Добавление индексов для уменьшения полных сканирований таблиц
Оптимизацию JOIN-операций
Кэширование часто запрашиваемых данных

5.2. Настройка PostgreSQL

Увеличить work_mem до 64-128MB для уменьшения дисковых операций
Настроить autovacuum менее агрессивно:
Увеличить autovacuum_vacuum_cost_delay до 10-20ms
Увеличить autovacuum_naptime до 30s-1min
Проверить эффективность shared_buffers и effective_cache_size

5.3. Инфраструктурные улучшения

Рассмотреть переход на более быстрые диски (SSD/NVMe)
Настроить параметры виртуальной памяти ОС (как указано в отчете):
Проверить и оптимизировать vm.dirty_background_bytes, vm.dirty_bytes
Рассмотреть увеличение оперативной памяти

5.4. Мониторинг и дальнейший анализ

Установить мониторинг дисковых задержек (latency)
Анализировать план выполнения проблемных запросов
Рассмотреть разделение нагрузки на несколько дисковых массивов

6. Заключение

Система упирается в производительность дискового ввода-вывода. При росте нагрузки диски не справляются с объемом операций чтения, что приводит к резкому росту ожиданий и падению производительности. Критически важна оптимизация запросов (особенно scenario1) и улучшение дискового подсистемы. Без этих мер дальнейшее увеличение нагрузки приведет к еще более серьезным проблемам с производительностью.

Шаг-3: Формирование итогового отчета по производительности подсистемы IO

Промпт

Подготовь итоговый отчёт по результатам анализа производительности подсистемы IO

1. Общая информация

Период анализа: 2026-01-03 12:59 - 2026-01-03 14:48 (109 минут)
Анализируемые устройства: vdd и vdc
CPU: 8 ядер, Intel Xeon Processor (Skylake)

2. Ключевые выводы по устройству vdd (/data)

Критические проблемы:

Постоянная 100% загрузка устройства - во всех 110 замерах %util = 100%
Высокая глубина очереди - 100% наблюдений с aqu_sz > 1 (до 18)
Неэффективное использование памяти:
Высокая корреляция (buff - wMB/s): 0.6776
Высокая корреляция (cache - r/s): 0.5023
Высокая корреляция (cache - w/s): 0.5183

Производительность:

IOPS: 3949-4052 операций/сек
Пропускная способность: 36-40 МБ/сек
Latency чтения: 2-5 мс (в пределах нормы)
Latency записи: 2-5 мс (в пределах нормы)
Нагрузка на CPU (wa): 44-50% (высокая)

3. Ключевые выводы по устройству vdc (/wal)

Статус: Удовлетворительно

Умеренная загрузка: 42-53% utilization
Низкая глубина очереди: менее 25% наблюдений > 1
Latency в норме: w_await ≈ 1 мс
Корреляции в основном в норме

Производительность:

IOPS записи: 758-971 операций/сек
Пропускная способность: 6-8 МБ/сек
Нагрузка на CPU (wa): 44-50%

4. Рекомендации

Срочные меры для vdd:

Рассмотреть апгрейд диска - текущий диск работает на пределе
Оптимизировать использование кэша - выявлена неэффективность
Балансировка нагрузки - возможно перенести часть операций на другие диски
Увеличить память для улучшения кэширования

Оптимизационные меры:

Мониторинг - установить постоянный мониторинг загрузки vdd
Анализ паттернов доступа - понять причину высокой нагрузки
Настройка параметров IO - оптимизировать размеры блоков и очереди

5. Общий вердикт

Устройство vdd требует немедленного внимания - работает в режиме saturation
Устройство vdc функционирует в нормальном режиме
Система в целом имеет резерв по CPU (wa < 50%), но дисковый IO является узким местом

Приоритет: Высокий (необходимы срочные меры по оптимизации vdd)

Показать полностью

kznalp

Postgres DBA

Короткий и долгий checkpoint_timeout : мнение нейросети⁠⁠

Серия СУБД PostgreSQL

15 дней назад

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

Вчера: редкий удар. Сегодня: ровный ритм. Эволюция контрольных точек PostgreSQL.

Предисловие

В старых руководствах по настройке PostgreSQL можно встретить почти ритуальную рекомендацию: увеличь checkpoint_timeout до 30 минут для повышения производительности. Десятилетиями этот совет кочевал из блога в блог, став аксиомой для многих администраторов. Но времена изменились. Современные диски, новые версии СУБД и свежие экспериментальные данные заставляют пересмотреть догмы. Эта статья рассказывает, почему старая оптимизация была логичной для эпохи HDD, как парадигма сменилась с приходом SSD и почему сегодня, вопреки устаревшим сборникам рецептов, checkpoint_timeout = '5m' с правильной дополнительной настройкой оказывается лучшим выбором для стабильности и производительности.

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

Вопрос нейросети

на чем основана старая рекомендация, вошедшая в сборники рецептов, о целесообразности увеличения checkpoint_timeout для PostgreSQL ?

Ответ нейросети DeepSeek

Старая рекомендация увеличивать checkpoint_timeout (например, до 30 минут) была популярна в эпоху, когда дисковые подсистемы были значительно медленнее современных. Она была основана на стремлении минимизировать негативное влияние частых контрольных точек на производительность системы.

🛠 В чем была суть старой рекомендации?

Основная идея заключалась в следующем:

Снижение частых пиков нагрузки на ввод-вывод (I/O): Контрольная точка — это дорогостоящая операция, при которой все измененные данные ("грязные" страницы) из оперативной памяти записываются на диск. Если она происходит слишком часто (по умолчанию раз в 5 минут), это может создавать периодические пиковые нагрузки на диск, что особенно заметно на медленных HDD. Увеличение интервала между контрольными точками позволяет снизить эту частоту.
Экономия ресурсов WAL: При включенном параметре full_page_writes (по умолчанию — включен) меньший интервал между контрольными точками увеличивает объем записи в журнал предзаписи (WAL). Более редкие контрольные точки теоретически могли сокращать этот дополнительный объем.

🔄 Как изменился современный подход?

Со временем понимание настройки контрольных точек эволюционировало. Сегодня фокус сместился с простого увеличения checkpoint_timeout на комплексную настройку и балансировку нескольких параметров.

Ключевая роль max_wal_size: Сейчас именно этот параметр чаще всего является основным триггером для запуска контрольной точки. Контрольная точка сработает, когда будет сгенерирован объем WAL, приближающийся к значению max_wal_size, — даже если не прошло времени, заданного в checkpoint_timeout. Поэтому настройка max_wal_size стала более приоритетной.
Распределение нагрузки во времени: Для сглаживания дискового ввода-вывода во время контрольной точки используется параметр checkpoint_completion_target (по умолчанию 0.9). Это означает, что сервер старается растянуть запись данных почти на весь интервал между контрольными точками, избегая резких всплесков активности.
Компромисс с восстановлением: Главным недостатком увеличения checkpoint_timeout является рост времени восстановления после сбоя, так как системе потребуется обработать больше журналов WAL. Современные рекомендации всегда учитывают этот компромисс между производительностью при записи и скоростью восстановления (RTO).

Ключевые современные принципы:

Не трогать без необходимости: Не увеличивайте checkpoint_timeout вслепую, следуя устаревшим советам.
Настройка max_wal_size: Увеличивайте max_wal_size так, чтобы контрольные точки в вашей рабочей нагрузке в основном срабатывали по времени (checkpoint_timeout), а не по достижению лимита размера WAL.
Мониторинг предупреждений: Если в логах сервера часто появляется предупреждение от checkpoint_warning, это прямой сигнал к увеличению max_wal_size.
Учет аппаратуры: На системах с быстрыми SSD-дисками негативный эффект от частых контрольных точек гораздо меньше, что делает старые рекомендации еще менее актуальными.

📊 Старая практика vs. Современный подход

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

Старая практика (упрощенный подход)
Основная цель: Избежать пиковой нагрузки на I/O любой ценой.
Главный параметр: Значительное увеличение checkpoint_timeout (напр., до 30-60 мин).
Риски: Длительное восстановление после сбоя, потенциальный перерасход места под WAL.
Контекст: Эпоха доминирования медленных HDD.

Современный подход (сбалансированный)
Основная цель: Сбалансировать нагрузку на I/O и время восстановления (RTO).
Главный параметр: Настройка max_wal_size в соответствии с реальной нагрузкой.
Риски: Сведены к минимуму при корректной настройке группы параметров.
Контекст: Быстрые SSD, акцент на наблюдаемость и сбалансированность.

Таким образом, если вы видите в старых руководствах рекомендацию просто выставить checkpoint_timeout = '30min', стоит воспринимать ее как исторический артефакт.

Современная эффективная настройка требует понимания работы связки checkpoint_timeout / max_wal_size / checkpoint_completion_target и мониторинга статистики контрольных точек в вашем конкретном окружении.

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

Вопрос нейросети

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

Ответ нейросети DeepSeek

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

Давайте разберем, почему ваше утверждение верно в идеале, но почему в прошлом от него отходили.

✅ Ваша логика (современный идеал)

"Часто и понемногу" — это принцип, на котором построена современная настройка checkpoint_completion_target = 0.9.
Именно этот параметр заставляет PostgreSQL растягивать запись "грязных" страниц почти на весь интервал между контрольными точками (90%), что обеспечивает максимально равномерную нагрузку на диск. Это прямо противоположно "редко и много".

❓ Почему же тогда ставили огромный checkpoint_timeout?

Это был вынужденный компромисс, обусловленный тремя ключевыми факторами:

1. Катастрофическая стоимость случайных операций записи на HDD.
Представьте, что система в фоне постоянно понемногу записывает страницы. Эти страницы разбросаны по всему объему данных. Для HDD с его механической головкой это означало постоянные и крайне затратные "скачки" по всей поверхности диска для поиска нужных блоков. Это убивало производительность всех других операций (SELECT, INSERT, UPDATE), которые в этот момент пытались работать с диском. Лучше было отложить эту работу и выполнить ее одним большим, но последовательным сливом — так голова диска двигалась меньше.

2. Роль кэша операционной системы.
Когда PostgreSQL пишет данные, они сначала попадают в кэш ОС. При частой фоновой записи эти "грязные" страницы из кэша ОС постоянно сбрасывались бы на физический диск, мешая другим процессам. Большой интервал позволял накопить изменения в кэше и записать их более управляемым блоком.

3. Влияние на другие фоновые процессы.
На старых системах с ограниченными ресурсами параллельные фоновые процессы (например, autovacuum) активно конкурировали за дисковый I/O. Периодическая, но предсказуемая мощная нагрузка от контрольной точки была часто предпочтительнее постоянного "фонового шума", который мешал всем остальным операциям все время.

🆚 Прямое сравнение двух стратегий

Давайте представим это наглядно:

Стратегия: "Редко, но много" (старый подход с checkpoint_timeout=30min)

Нагрузка на I/O: Огромные пики каждые 30 минут. Диск "задыхается" на несколько минут.
Время отклика системы: Плохое только во время пика. В остальное время — стабильно хорошее.
Поведение диска (HDD): Запись большими последовательными блоками (эффективнее для механики).
Поведение диска (SSD): Не имеет значения, но пиковая нагрузка все равно создает задержки.
Время восстановления (RTO): Катастрофически долгое (необходимо прочитать 30 минут WAL).

Стратегия: "Часто и понемногу" (современный подход с checkpoint_completion_target=0.9)

Нагрузка на I/O: Постоянная, ровная, предсказуемая фоновая нагрузка.
Время отклика системы: Стабильно хорошее, без резких провалов.
Поведение диска (HDD): Постоянный "шум" из случайных операций (неэффективно).
Поведение диска (SSD): Идеально, SSD отлично справляется с параллельными операциями.
Время восстановления (RTO): Минимальное (необходимо прочитать всего 5 минут WAL).

🧠 Итог: Почему парадигма изменилась

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

Переход на стратегию "часто и понемногу" стал возможен и правилен благодаря:

Распространению SSD: которые не имеют механических задержек и одинаково хорошо обрабатывают как последовательные, так и случайные операции.
Появлению и правильной настройке checkpoint_completion_target: который реализует эту идею на уровне СУБД.
Росту объема памяти: что позволяет кэшировать больше данных без необходимости их немедленной записи.

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

Показать полностью 1

Контент нейросетей Нейронные сети Postgresql Тестирование Яндекс Дзен (ссылка) Длиннопост

Odin404

Половина американцев уже работает с ИИ. А что у нас? Сравниваем цифры⁠⁠

17 дней назад

Половина американцев уже работает с ИИ. А что у нас? Сравниваем цифры.

Gallup на днях выкатил свежую статистику: в США 45% сотрудников используют ИИ на работе хотя бы пару раз в год. Ещё год назад было 27%. Рост почти вдвое.

Я залез в российские исследования — и знаете что? Мы не сильно отстаём. А в чём-то даже впереди.

Что происходит в США

По данным Gallup за третий квартал 2025:

45% используют ИИ хотя бы несколько раз в год
23% — еженедельно
10% — каждый день

Топ задач: сбор и обобщение информации (42%), генерация идей (41%), обучение (36%). ChatGPT лидирует — его юзают 61% тех, кто работает с ИИ.

Интересно, что айтишники впереди планеты всей — 76% в IT используют ИИ регулярно. В финансах — 58%, в ритейле — 33%.

А что в России?

Тут данные разные, но картина похожая:

По исследованию hh.ru и Touch Instinct — 26% соискателей регулярно используют ИИ, ещё 35% — периодически. Итого 61% хотя бы иногда.

CNews со ссылкой на РШУ пишет, что каждый второй россиянин уже использует ИИ в работе, а 81% видят в нём карьерные возможности.

Среди IT-специалистов вообще бум: 62% айтишников используют ИИ — в два раза больше, чем год назад. К 2028 году прогнозируют 98%.

А Forbes пишет, что 70% российских компаний уже внедрили генеративный ИИ в работу.

Для чего используют (Россия)

Генерация текстов и креативов — лидер
Клиентская поддержка (чат-боты)
Анализ данных (34%)
Обучение сотрудников (25%)

Топ отраслей: дизайн (70%), маркетинг (66%), IT (60%).

Страхи и опасения

Тут Россия и США похожи:

В США 47% боятся, что ИИ заберёт их работу в ближайшие 5 лет.

В России — половина крупных компаний допускает сокращение штата из-за ИИ. При этом спрос на специалистов с навыками ИИ удвоился, а зарплаты у них на 24% выше.

Получается классическая вилка: ИИ и создаёт рабочие места, и убивает их. Кто успеет переучиться — тот в шоколаде.

Забавный факт

В США только 22% компаний довели до сотрудников чёткую стратегию по ИИ. Остальные работники юзают ChatGPT на свой страх и риск — без гайдлайнов и понимания, можно ли вообще.

В России похожая история: 29% компаний допускают хаотичное использование ИИ по инициативе сотрудников, а 14% — вообще запрещают.

Используете ИИ на работе? Если да — для чего? ChatGPT, GigaChat, YandexGPT — что в ходу?

И главное — начальство в курсе, или это ваш маленький секрет эффективности?

«Кстати, самое интересное — что сотрудники используют ИИ втрое чаще, чем думают их руководители. McKinsey это подтверждает. То есть пока топ-менеджеры обсуждают «стратегию внедрения», народ уже давно всё внедрил сам.»

Показать полностью

[моё] Искусственный интеллект ChatGPT Работа Статистика Россия США Нейронные сети Карьера IT Развитие Будущее Успех Совершенство Postgresql Мышление Саморазвитие Фриланс Опыт

kznalp

Postgres DBA

Оптимизация пагинации в PostgreSQL: Как настройка work_mem превратила ROW_NUMBER в лидера производительности⁠⁠

Серия СУБД PostgreSQL

24 дня назад

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

Память решает: как ROW_NUMBER обогнал Deferred Join

Предисловие

В мире высоконагруженных баз данных выбор метода пагинации может стать решающим фактором для производительности системы. Эксперимент, проведённый с двумя подходами — классическим ROW_NUMBER и отложенным соединением (Deferred Join) — показал, что даже архитектурно более совершенный метод не гарантирует победы без тонкой настройки СУБД. Исследование раскрывает, как правильная конфигурация памяти PostgreSQL перевесила преимущества Deferred Join и позволила ROW_NUMBER добиться превосходства на параллельной нагрузке до 22 сессий.

Начало экспериментов с методом пагинации ROW_NUMBER

Прогноз нейросети

Вопрос

Используя информацию о размерах таблиц, схеме баз данных и тестовых запросах, дай прогноз и обоснование - какой тестовый запрос покажет большую производительность для нагрузочного тестирования с параллельной нагрузкой от 5 до 22 сессий для СУБД PostgreSQL 17 c ресурсами CPU=8 RAM=8GB

Прогноз:
Наибольшую производительность при параллельной нагрузке (5–22 сессии) покажет запрос plan-1 (ROW_NUMBER()) после оптимизации индексами.

Экспериментальная проверка

Анализ ситуации нейросетью

Почему не оправдался прогноз нейросети о большей эффективности метода ROW_NUMBER для заданных условий ?

5. Условия, при которых ROW_NUMBER() действительно был бы эффективнее:

Сценарий 2: Адекватный размер work_mem

Если бы work_mem был ≥ 256MB для сортировки в памяти

какие значение work_mem необходимо установить для получения преимущества метода row_number при максимальной нагрузке 22 параллельные сессии при общем размере RAM=8GB ?

-- Для получения преимущества ROW_NUMBER():
SET work_mem = '180MB';
SET max_parallel_workers_per_gather = 1;
SET max_parallel_workers = 16;

-- В postgresql.conf:
work_mem = 180MB
max_parallel_workers_per_gather = 1
max_parallel_workers = 16
max_worker_processes = 16
shared_buffers = 2GB
effective_cache_size = 6GB

Задача

Экспериментальная проверка рекомендации нейросети по оптимизации метода пагинации ROW_NUMBER

Изменение конфигурационных параметров СУБД

ALTER SYSTEM SET work_mem = '180MB';
ALTER SYSTEM SET max_parallel_workers_per_gather = 1;
ALTER SYSTEM SET max_parallel_workers = 16;
ALTER SYSTEM SET max_worker_processes = 16 ;
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM effective_cache_size = '6GB';

Производительность и ожидания СУБД в ходе нагрузочного тестирования

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

График изменения операционной скорости в ходе нагрузочного тестирования

График изменения относительной разницы операционной скорости при использовании метода DIFFERED JOIN по сравнению с методом ROW_NUMBER в ходе нагрузочного тестирования

Результат

Среднее превышение операционной скорости , при использовании метода ROW_NUMBER составило 12.59%

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

График изменения ожиданий СУБД в ходе нагрузочного тестирования

График изменения относительной разницы ожиданий СУБД при использовании метода DIFFERED JOIN по сравнению с методом ROW_NUMBER в ходе нагрузочного тестирования

Результат

Среднее снижение ожиданий СУБД, при использовании метода ROW_NUMBER составило 18.06%

Характерные особенности тестовых запросов и планов выполнения

1. Общая цель запросов

Оба запроса решают задачу случайной пагинации (выбор случайной "страницы" из 100 строк) для отфильтрованных данных (билеты с бизнес-классом).

2. Основные различия в подходах

Первый запрос (ROW_NUMBER()):

Использует оконную функцию ROW_NUMBER() для нумерации всех строк
Фильтрует по диапазону номеров строк после нумерации
Ключевая проблема: Выполняет полную сортировку и нумерацию всех 2.15 млн строк

Второй запрос (Deferred Join):

Использует отложенное соединение (deferred join)
Сначала выбирает только ticket_no с помощью OFFSET/LIMIT
Затем соединяет остальные данные по отобранным ключам
Преимущество: Сортирует только ключи, а не все данные

3. Производительность

Первый запрос:

Время выполнения: 12.65 секунд
Основные затраты:
Merge Semi Join: 9.3 секунды
Сортировка 2.15 млн строк: 850-1000 мс (quicksort, 170 МБ памяти)
WindowAgg (оконная функция): 1723 мс

Второй запрос:

Время выполнения: ~16.1 секунды (неполные данные в выводе)
Основные затраты:
Merge Semi Join: 8.75 секунды
Сортировка для OFFSET: 5.5 секунды (top-N heapsort, 15 МБ памяти)
Hash Join: 5575 мс

4. Критические различия в планах

Сортировка:

Первый запрос: Сортирует все столбцы (ticket_no, book_ref, passenger_name) - 92 байта на строку
Второй запрос: Сортирует только ticket_no - 32 байта на строку

Использование памяти:

Первый: 170 МБ для сортировки всех данных
Второй: 15 МБ для сортировки только ключей

Фильтрация:

Первый: Сначала нумерует все строки, затем фильтрует 100
Второй: Сначала выбирает 100 ключей, затем соединяет данные

5. Проблемы обоих подходов

Общая проблема: floor(random() * COUNT(*)/100) требует полного сканирования для подсчета строк
Производительность OFFSET: Во втором запросе OFFSET должен "пропустить" много строк (случайная страница * 100)

6. Рекомендации

Для оптимизации:

Второй подход лучше архитектурно (deferred join), но в данном случае медленнее из-за:
Необходимости вычислять OFFSET
Hash Join вместо более эффективного соединения
Потенциальные улучшения:
Использовать keyset pagination вместо OFFSET
Кэшировать общее количество строк
Рассмотреть материализованные представления для filtered_tickets

В текущей реализации:

Первый запрос быстрее (12.65с vs ~16.1с), но использует больше памяти
Второй запрос масштабируется лучше при увеличении размера данных, так как сортирует меньше данных

7. Вывод

Оба запроса имеют проблемы с производительностью из-за необходимости обработки всех 2.15 млн строк для случайной выборки. Deferred Join (второй подход) - более правильная архитектурная паттерн, но требует оптимизации OFFSET и соединения для повышения эффективности в данном конкретном случае.

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

1. Производительность (SPEED)

ROW_NUMBER подход:

Начальная производительность: 60
Стабильная после 45 итерации: 96
Максимум: 96 (после адаптации)
Прирост: 60 → 96 (+60%)

Deferred Join подход:

Начальная производительность: 52
Стабильная после 55 итерации: 84
Максимум: 84
Прирост: 52 → 84 (+61.5%)

Вывод: ROW_NUMBER показывает на 14.3% выше абсолютную производительность (96 vs 84).

2. Динамика адаптации

ROW_NUMBER:

Быстрый рост до 96 за 55 итераций
Более резкие скачки производительности
Ранняя стабилизация (с 55 итерации)

Deferred Join:

Более плавный рост до 84
Дольше адаптируется (до 55 итерации)
Стабильнее на низких нагрузках

3. Ожидания и блокировки

ROW_NUMBER:

TIMEOUT появляются с итерации 46 (при нагрузке 10 соединений)
LWLOCK стабилизируются на уровне 3-6
WAITINGS: 4-7

Deferred Join:

IO ожидания появляются с итерации 73 (при нагрузке 15+ соединений)
LWLOCK: 4-8 (выше, чем у ROW_NUMBER)
WAITINGS: до 21 пикового значения
Более выраженные скачки в ожиданиях (74 итерация: 21 ожидание)

4. Корреляция с нагрузкой

Нагрузка растет от 5 до 22 соединений
Критические точки:
10 соединений (итерация 46): ROW_NUMBER начинает показывать TIMEOUT
15 соединений (итерация 73): Deferred Join показывает IO ожидания
18+ соединений: оба подхода стабилизируются на максимальных значениях ожиданий

5. Ключевые различия в поведении

ROW_NUMBER:

Выше пиковая производительность (96 vs 84)
Раннее появление TIMEOUT (с 10 соединений)
Меньше LWLOCK в среднем (3-6 vs 4-8)
Более предсказуемые паттерны ожиданий

Deferred Join:

Лучше масштабируется при низких нагрузках
Более стабильные WAITINGS (кроме пиков)
Появление IO ожиданий вместо TIMEOUT
Сильнее страдает от LWLOCK

6. Анализ проблемных точек

ROW_NUMBER:

Проблема: TIMEOUT при 10+ соединениях
Причина: WindowAgg + сортировка больших данных конкурируют за ресурсы
Симптом: Конкуренция за CPU/память

Deferred Join:

Проблема: IO ожидания при 15+ соединениях
Причина: Hash Join + сортировка для OFFSET требуют disk I/O
Симптом: Конкуренция за disk I/O и LWLOCK

7. Рекомендации по оптимизации

Для ROW_NUMBER:

Увеличить work_mem для уменьшения disk spills
Рассмотреть материализованные представления для filtered_tickets
Кэшировать COUNT(*) для random_page

Для Deferred Join:

Оптимизировать индексы для сортировки ticket_no
Увеличить shared_buffers для кэширования hash таблиц
Рассмотреть использование keyset pagination вместо OFFSET

8. Общий вывод

ROW_NUMBER лучше для:
Максимальной производительности (14.3% выше)
Систем с достаточными CPU/памятью
Сценариев с <10 конкурентных соединений
Deferred Join лучше для:
Систем с ограничениями по памяти
Сценариев с предсказуемыми IO паттернами
Приложений, чувствительных к TIMEOUT
Оба подхода требуют оптимизации при >10 конкурентных соединений
Критическая нагрузка: 10-15 соединений (точка деградации производительности)

Метрики производительности инфраструктуры

1. Использование CPU

ROW_NUMBER:

Начало: 61% user, 3% system, 36% idle
Критическая точка (итерация 50): резкий переход на 95-96% user, 4% system, 0% idle
Стабильная нагрузка: 96% user, 4% system, 0% idle
Вывод: Полная загрузка CPU с преобладанием пользовательского времени

Deferred Join:

Начало: 61% user, 2% system, 36% idle
Критическая точка (итерация 52): переход на 97% user, 3% system, 0% idle
Стабильная нагрузка: 97% user, 3% system, 0% idle
Вывод: Аналогичная полная загрузка, но меньше системного времени

Сравнение: Deferred Join показывает на 1% выше user CPU и на 1% ниже system CPU.

2. Использование памяти

ROW_NUMBER:

Свободная память: 1791 → 2753 KB (+53% рост)
Буферы: 47 → 6 KB (-87% снижение)
Кэш: 4955 → 2751 KB (-44% снижение)
Своп: 70 → 67 KB (минимальное изменение)
Вывод: Активное использование кэша и буферов

Deferred Join:

Свободная память: 4567 → 2207 KB (-51% снижение)
Буферы: 18 → 6 KB (-67% снижение)
Кэш: 2211 → 2728 KB (+23% рост)
Своп: 69 → 73 KB (незначительный рост)
Вывод: Увеличение использования кэша, снижение свободной памяти

Сравнение: ROW_NUMBER активнее использует кэш в начале, Deferred Join наращивает кэш в процессе.

3. Ввод-вывод (IO)

ROW_NUMBER:

io_bo: 52 → 58 (+11.5% рост)
Пиковые значения: до 59
Вывод: Умеренный рост IO при увеличении нагрузки

Deferred Join:

io_bo: 59 → 65 (+10% рост)
Пиковые значения: до 68
Вывод: Более высокий базовый уровень IO

Сравнение: Deferred Join имеет на 10-15% выше активность IO, что соответствует ожиданиям из предыдущего анализа (IO ожидания).

4. Системные метрики

ROW_NUMBER:

Прерывания (system_in): 5687 → 8279 (+45% рост)
Переключения контекста (system_cs): 657 → 1053 (+60% рост)
Процессы в run queue (procs_r): 6 → 15 (+150% рост)

Deferred Join:

Прерывания (system_in): 5652 → 8274 (+46% рост)
Переключения контекста (system_cs): 652 → 1063 (+63% рост)
Процессы в run queue (procs_r): 5 → 15 (+200% рост)

Сравнение: Оба подхода показывают схожий рост системных метрик, Deferred Join имеет немного больше переключений контекста.

5. Критические точки перехода

ROW_NUMBER:

Итерация 50 (нагрузка 10): переход на 0% idle CPU
Сопровождается: Ростом procs_r до 9
Симптом: Резкое изменение паттерна использования памяти

Deferred Join:

Итерация 52 (нагрузка 10): переход на 0% idle CPU
Сопровождается: Ростом procs_r до 9
Симптом: Снижение свободной памяти до 4000 KB

6. Корреляция с нагрузкой

5-8 соединений: Оба подхода работают с простоями CPU (36% idle)
10 соединений: Критическая точка (0% idle)
12-15 соединений: Стабилизация на максимальной нагрузке
18-22 соединений: Дальнейший рост системных метрик

7. Ключевые различия по метрикам

ROW_NUMBER преимущества:

Меньше IO операций (58 vs 68)
Лучшее использование памяти (растущая свободная память)
Более стабильный кэш (меньше колебаний)

Deferred Join преимущества:

Меньше системного времени CPU (3% vs 4%)
Более предсказуемое использование кэша (постепенный рост)
Меньше начальных буферов (более эффективное использование)

8. Проблемные паттерны

ROW_NUMBER:

Проблема: Резкое падение кэша с 4955 до 2751
Причина: Оконные функции требуют больших рабочих наборов
Риск: Потенциальные page faults при недостатке памяти

Deferred Join:

Проблема: Высокий базовый уровень IO (59 vs 52)
Причина: Hash Join и сортировка для OFFSET
Риск: Disk I/O bottlenecks при высокой нагрузке

9. Общие выводы по метрикам

Оба подхода CPU-bound: 0% idle при нагрузке ≥10 соединений
Deferred Join более IO-intensive: на 10-15% выше IO активность
ROW_NUMBER более memory-intensive: активнее использует кэш и буферы
Критическая нагрузка: 10 соединений для обоих подходов
Масштабирование: Deferred Join лучше сохраняет кэш, но требует больше IO

10. Рекомендации по оптимизации системы

Для ROW_NUMBER:

Увеличить work_mem для сортировки в памяти
Настроить shared_buffers для кэширования рабочих наборов
Мониторить page faults и swap активность

Для Deferred Join:

Оптимизировать индексы для уменьшения IO
Рассмотреть SSD для уменьшения latency IO операций
Настроить effective_io_concurrency для параллельного IO

Итог:

Выбор между подходами зависит от конфигурации системы -

ROW_NUMBER для CPU-обеспеченных архитектур
Deferred Join для систем с хорошим IO.

Показать полностью 6

[моё] Postgresql Тестирование Нейронные сети Инженер Статья Длиннопост

kznalp

Postgres DBA

PG_EXPECTO: Прицельный залп по проблемам производительности PostgreSQL⁠⁠

Серия СУБД PostgreSQL

27 дней назад

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

Определи цель, заряди параметры, устрани проблему

Предисловие:

Когда производительность СУБД падает под нагрузкой, важно не стрелять наугад. Методика PG_EXPECTO предлагает сначала выявить ключевые цели — проблемные запросы и типы ожиданий — и только затем наносить точный удар, используя «большие пушки» оптимизации.

1. Анализ ожиданий СУБД на основании данных о диаграмме Парето

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

1. Анализ ожиданий типа IPC (Inter-Process Communication)

BgWorkerShutdown (40,62%) — наиболее проблемное событие:

Указывает на ожидание завершения фоновых процессов
Возможные причины:
Медленное завершение параллельных worker-процессов
Проблемы с освобождением ресурсов
Возможные deadlock-ситуации при завершении

ParallelFinish (25,4%) и ExecuteGather (21,14%):

Связаны с параллельным выполнением запросов
Высокие значения могут указывать на:
Слишком агрессивные настройки параллелизма
Конкуренцию за ресурсы между параллельными worker-ами
Неоптимальные планы выполнения параллельных запросов

2. Анализ ожиданий типа LWLock (Lightweight Locks)

BufferMapping (49,46%) — основная проблема в блокировках:

Указывает на конкуренцию за доступ к буферному кешу
Возможные причины:
Недостаточный размер shared_buffers
Высокая конкурентная нагрузка на горячие данные
Отсутствие или неэффективность индексов

SerializableXactHash (31,72%):

Связано с транзакциями уровня изоляции SERIALIZABLE
Может указывать на:
Высокую конкуренцию за структуры данных serializable транзакций
Необходимость оптимизации транзакций

Общие рекомендации:

Сбор дополнительной информации:
Получить полный список ожиданий за период инцидента
Проверить мониторинг нагрузки на CPU, IO, память
Возможные immediate-действия:
Перераспределить нагрузку по времени
Временно снизить параллелизм
Добавить индексы на часто используемые поля в условиях WHERE/JOIN

Вывод:

Основные проблемы связаны с параллельным выполнением запросов и конкуренцией за буферный кеш. Рекомендуется поэтапная оптимизация настроек параллелизма и работы с памятью.

2. Анализ событий ожиданий по отдельным SQL-запросам на основании данных о диаграмме Парето

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

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

1. Запросы с наибольшим влиянием на IPC-ожидания:

Топ-1 проблемный запрос (17,6% IPC ожиданий):

queryid: -1701015661318396920 3018 вызовов, 85k строк
Ожидания: BgWorkerShutdown, ParallelFinish, BufferIO, ExecuteGather
Анализ: Часто выполняемый параллельный запрос с проблемами завершения воркеров

Топ-2 (14% IPC ожиданий):

queryid: -678327810318891437 5183 вызовов, 5183 строк
Особенность: Каждый вызов возвращает ровно 1 строку
Высокая частота выполнения усиливает проблемы параллелизма

2. Запросы с наибольшим влиянием на LWLock-ожидания:

Топ-1 проблемный запрос (24,73% LWLock ожиданий):

queryid: -8182552260313222074 42,7k вызовов, 8,5M строк
Ожидания: BufferMapping
Анализ: Крайне часто выполняемый запрос с высокой конкуренцией за буферный кеш

Критический запрос, присутствующий в обеих категориях:

queryid: 5459520954633506046:
IPC: 5,34% (параллельное выполнение)
LWLock: 11,83% (ParallelHashJoin)
Особенность: 287 вызовов, но 217k строк на вызов (~759 строк/вызов)
Вероятно: Сложный аналитический запрос с хеш-соединениями

Детальный анализ проблемных паттернов:

A. Проблемы параллельного выполнения:

Запросы с ParallelFinish и ExecuteGather:
Все топ-8 IPC запросов имеют эти ожидания
Рекомендация: Уменьшить max_parallel_workers_per_gather для этих queryid
Конкретные кандидаты на оптимизацию:
queryid: 1374759154717555017 2053 вызова, 0 строк
Возможно, DML-операция с неправильным планом
Имеет HashBuildHashInner - проблема с хеш-таблицами

B. Проблемы с блокировками буферов:

Высокочастотные запросы:
queryid: -8182552260313222074 42k вызовов
Решение: Кэширование на уровне приложения, batch-обработка
Serializable транзакции в db5:
4 из топ-8 LWLock запросов используют SerializableXactHash
Все из базы db5
Рекомендация: Пересмотреть уровень изоляции

Приоритетные действия:

1. Немедленные меры:

Для queryid: -8182552260313222074 (42k вызовов):
-- Добавить индексы на используемые предикаты
-- Внедрить кэширование на 5-10 минут
Для queryid: -1701015661318396920 (частый параллельный запрос):
-- Принудительно отключить параллельное выполнение
SET max_parallel_workers_per_gather = 0;

2. Среднесрочная оптимизация:

Группа 1: Частые параллельные запросы

queryid: -678327810318891437 (5k вызовов)
queryid: 3449463017331132112 (3k вызовов)
Действие: Оптимизация планов, уменьшение частоты выполнения

Группа 2: Запросы с хеш-соединениями

queryid: 5459520954633506046
queryid: 1374759154717555017
Действие: Увеличить work_mem, добавить индексы для соединений

3. Долгосрочные изменения:

Для базы db5 (Serializable транзакции):

Аудит необходимости SERIALIZABLE изоляции
Возможный переход на REPEATABLE READ

Настройка PostgreSQL:

# Увеличить для уменьшения BufferMapping

shared_buffers

# Оптимизация параллельных запросов

max_parallel_workers_per_gather = 2 (вместо 4-8)

parallel_setup_cost = 1000

parallel_tuple_cost = 0.1

# Увеличение для хеш-соединений

work_mem = 32MB (или больше)

Важно:

Все изменения должны тестироваться на staging-окружении с нагрузочным тестированием.

3.Сводный отчет по ожиданиям и проблемным SQL запросам

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

Контекст

В ходе инцидента производительности СУБД PostgreSQL выявлены проблемные SQL-запросы с высокой корреляцией (≥0.7) между типом ожидания и нагрузкой на СУБД. Отчет сформирован на основе анализа событий ожиданий и соответствующих запросов.

Ключевые наблюдения

Наиболее частые события ожидания:
IPC: BgWorkerShutdown (40,62%)
LWLock: BufferMapping (49,46%)
Наиболее проблемные запросы:
IPC: -1701015661318396920 (17,6% ожиданий)
LWLock: -8182552260313222074 (24,73% ожиданий)
Распределение по БД:
db1 и db5 имеют наибольшее количество проблемных запросов
role1 является общей ролью для всех проблемных запросов
Паттерны ожиданий:
Запросы с высоким числом CALLS часто имеют ожидания типа LWLock
Запросы с большим количеством возвращаемых ROWS склонны к ожиданиям IPC

PG_EXPECTO: Принцип «только большие пушки» для СУБД⁠⁠

Серия СУБД PostgreSQL

28 дней назад

В силу ограниченности платформы Пикабу - статья сокращена. Полностью - на основном канале Дзен.

Когда нужен не просто выстрел, а залп главным калибром.

PG_EXPECTO — это не просто инструмент, а целостный комплекс, построенный на двух новых принципах.

1. Комплексный статистический анализ (CSSA)

Система больше не разделяет метрики СУБД и инфраструктуры. Она создаёт единый корреляционный снимок всей системы:

Вертикальная корреляция: Вместо отдельных событий система видит цепочки: например, как медленный запрос вызывает рост очереди дисковых операций (iowait).
Проактивность: Комплекс отслеживает не сбои, а тренды. Он может предупредить: «Обнаружена устойчивая тенденция к деградации из-за X», позволяя устранить проблему до того, как она затронет пользователей.

2. Семантический анализ и нейросеть-ассистент

Это и есть та самая «турбина». Вместо сырых графиков PG_EXPECTO использует нейросеть, которая интерпретирует данные в контексте.

Практический пример

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

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

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

График изменения операционной скорости СУБД перед инцидентом

Исходные данные для семантического анализа нейросетью

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

2. Метрика vmstat

3.Метрика iostat для дискового устройства, файловой системы /data

3.Метрика iostat для дискового устройства, файловой системы /wal

1.Анализ производительности и ожиданий СУБД

Проведи анализ производительности и ожиданий СУБД PostgreSQL за заданный период

На основе предоставленных данных можно провести следующий анализ производительности и ожиданий СУБД PostgreSQL за период с 08:40 до 09:40:

📈 1. Регрессионный анализ

· Операционная скорость (SPEED):

o Коэффициент детерминации R2=0.88R2=0.88 → модель хорошо описывает данные.

o Угол наклона = -43.1 → скорость со временем снижается.

· Ожидания СУБД (WAITINGS):

o R2=0.75R2=0.75 → модель умеренно точна.

o Угол наклона = +40.87 → количество ожиданий растёт.

🔗 2. Корреляционный анализ

· SPEED vs WAITINGS: −0.763→ сильная отрицательная корреляция. Чем больше ожиданий, тем ниже скорость.

· WAITINGS vs IPC: 0.9039 → очень сильная положительная корреляция. Рост межпроцессного взаимодействия связан с ростом ожиданий.

· WAITINGS vs LWLOCK: 0.9587 → почти прямая зависимость. Ожидания сильно связаны с лёгкими блокировками (lightweight locks).

📉 3. Динамика изменения показателей

· SPEED:

o Начало: ~130 572

o Конец: ~90 948

o Снижение на ~30% за час.

· WAITINGS:

o Начало: 345

o Конец: 435

o Рост на ~26%.

· IPC и LWLOCK также растут, особенно заметно после 09:30.

⚠️ 4. Потенциальные проблемы

1. Рост LWLOCK → возможна конкуренция за лёгкие блокировки (например, в памяти).

2. Рост IPC → увеличивается нагрузка на межпроцессное взаимодействие, возможно из-за параллельных запросов.

3. Падение SPEED при росте WAITINGS → система тратит больше времени на ожидания (возможно, из-за блокировок или нехватки ресурсов).

2.Анализ vmstat

Проведи анализ метрик vmstat за заданный период

На основе данных vmstat за период с 08:40 до 09:40 проведён анализ производительности системы, на которой работает PostgreSQL. Вот ключевые выводы:

📊 1. Общие тенденции

· Время простоя CPU (idle) снизилось с 90% до 85%.

· Пользовательская нагрузка CPU (us) выросла с 9% до 13%.

· Системная нагрузка CPU (sy) стабильна (2%).

· Ожиданий ввода-вывода (wa) не наблюдается (0%).

🧠 2. Анализ памяти

Показатель Начало Конец Изменение Комментарий

swpd 710МБ 749МБ +39 МБ Незначительный рост свопа, система не испытывает нехватки RAM.

free 7185МБ 3221МБ -3964 МБ Сильное снижение свободной памяти, что говорит о её активном использовании.

cache 53521МБ 55937МБ +2416МБ Кэш памяти увеличивается, что нормально при активной работе с диском.

Вывод: Память активно используется, но свопинг минимален — система работает в рамках доступной памяти.

💽 3. Ввод-вывод (I/O)

Показатель Начало Конец Пиковое значение Тренд

bi (блоков ввода/с) 572 2409 2409 Резкий рост после 09:25.

bo (блоков вывода/с) 850 511 850 Снижение, затем стабилизация.

Вывод:

· После 09:25 резко выросла активность чтения с диска (bi).

· Это совпадает с падением SPEED в данных PostgreSQL.

· Возможно, это связано с увеличением количества запросов к данным, не помещающихся в кэш.

🔄 4. Системные показатели

Показатель Начало Конец Изменение

in (прерываний/с) 12833 14503 +13%

cs (переключений контекста/с) 6835 7033 +3%

Вывод: Умеренный рост прерываний и переключений контекста, что соответствует увеличению нагрузки.

📉 5. Связь с данными PostgreSQL

Сравнивая с предыдущим анализом cluster.txt, наблюдаем:

1. Рост ожиданий (WAITINGS) в PostgreSQL коррелирует с:

o Ростом bi (чтение с диска).

o Увеличением us (пользовательская нагрузка CPU).

2. Падение SPEED совпадает с:

o Увеличением bi (дисковые операции).

o Снижением idle CPU.

⚠️ 6. Потенциальные узкие места

1. Дисковый ввод-вывод: резкий рост bi после 09:25 может быть причиной замедления.

2. Использование памяти: свободная память уменьшается, но свопинг незначителен — пока критично.

3. Нагрузка на CPU: idle снижается, но не до критических значений.

✅ 7. Рекомендации

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

o Увеличить shared_buffers, чтобы больше данных кэшировалось в памяти.

o Проверить наличие медленных запросов, выполняющих полное сканирование таблиц.

2. Мониторинг диска:

o Проверить скорость дисковых операций (например, с помощью iostat).

o Рассмотреть использование SSD или увеличение скорости дискового массива.

3. Настройка PostgreSQL:

o Увеличить effective_cache_size.

o Настроить work_mem для уменьшения операций на диске.

4. Системные настройки:

o Убедиться, что достаточно оперативной памяти для кэширования.

o Рассмотреть увеличение количества CPU, если нагрузка продолжит расти.

📈 8. Визуализация (рекомендуется)

· График SPEED и bi на одной оси времени → проверить корреляцию.

· График WAITINGS и us (пользовательская нагрузка CPU).

· График free memory и cache → отследить использование памяти.

График SPEED и bi на одной оси времени → проверить корреляцию.

Для наглядности значения bi увеличены в 10 раз

Отрицательная корреляция: Рост значений bi сопровождается снижением операционной скорости.

График WAITINGS и us (пользовательская нагрузка CPU).

Для наглядности значения us увеличены в 10 раз

Положительная корреляция: Рост значений us сопровождается увеличением количества ожиданий.

График free memory и cache → отследить использование памяти.

Отрицательная корреляция: Рост значений free сопровождается снижением значений cache.

free -3964 МБ: Сильное снижение свободной памяти, что говорит о её активном использовании.

cache +2416 МБ: Кэш памяти увеличивается, что нормально при активной работе с диском.

3. Анализ iostat для файловой системы /data

На основе данных iostat для устройства vdb за период с 08:40 до 09:40 проведён анализ дисковых операций. Вот ключевые выводы:

📊 1. Общая картина

· Устройство vdb используется для операций чтения и записи.

· %util (загрузка диска) выросла с 5.59% до 6.7% — загрузка низкая.

· aqu_sz (средняя длина очереди) незначительно снизилась с 0.26 до 0.20 — очереди практически нет.

📈 2. Операции чтения (Read)

Показатель Начало Конец Изменение Комментарий

r/s (чтений/с) 40.00 27.92 -30% Снижение количества операций чтения.

rMB/s (МБ/с) 0.84 2.82 +235% Резкий рост объёма читаемых данных.

rareq_sz (средний размер запроса) 19.35 КБ 67.09 КБ +247% Запросы стали крупнее.

r_await (среднее время чтения, мс) 2.07 3.73 +80% Увеличение времени отклика на чтение.

Вывод:

После 09:20 резко вырос объём читаемых данных (rMB/s) и средний размер запроса (rareq_sz). Это говорит о переходе на более крупные блоки данных, возможно из-за изменения типа запросов (например, сканирование больших таблиц).

📉 3. Операции записи (Write)

Показатель Начало Конец Изменение

w/s (записей/с) 16.83 15.20 -10%

wMB/s (МБ/с) 0.62 0.26 -58%

wareq_sz (средний размер записи) 31.74КБ 4.22КБ -87%

w_await (среднее время записи, мс) 5.57 3.59 -36%

Вывод:

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

🔗 4. Корреляция с WAITINGS (PostgreSQL)

· После 09:20 наблюдается рост r_await (время чтения) и WAITINGS (ожидания в СУБД).

· Рост rMB/s совпадает с ростом WAITINGS и падением SPEED.

· Это подтверждает гипотезу о том, что увеличение времени доступа к диску влияет на производительность БД.

⚠️ 5. Потенциальные проблемы

1. Увеличение времени чтения (r_await): с 2 мс до 3.7 мс — рост на 80%.

2. Увеличение размера запросов (rareq_sz): с 19 КБ до 67 КБ — возможно, из-за отсутствия индексов или полного сканирования таблиц.

3. Рост объёма читаемых данных (rMB/s): в 3 раза — нагрузка на диск возросла.

✅ 6. Рекомендации

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

o Проверить выполнение планов запросов (EXPLAIN ANALYZE).

o Добавить индексы для часто сканируемых таблиц.

o Увеличить shared_buffers, чтобы больше данных кэшировалось в памяти.

2. Мониторинг диска:

o Проверить скорость диска (например, с помощью hdparm или fio).

o Убедиться, что диск не фрагментирован.

3. Настройка PostgreSQL:

o Увеличить effective_cache_size.

o Настроить random_page_cost и seq_page_cost в соответствии с типом диска.

4. Аппаратные улучшения:

o Рассмотреть переход на SSD, если используется HDD.

o Увеличить скорость дисковой подсистемы (RAID, кэширование).

📈 7. Визуализация (рекомендуется)

· График rMB/s и WAITINGS на одной оси времени.

· График r_await и rareq_sz.

· График %util и aqu_sz для оценки загрузки диска.

График rMB/s и WAITINGS.

Для наглядности значения rMB/s увеличены в 100 раз

Увеличение rMB/s сопровождается ростом количества ожиданий.

График r_await и rareq_sz.

График %util и aqu_sz для оценки загрузки диска.

Для наглядности значения aqu_sz увеличены в 10 раз

🎯 Заключение

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

Приоритет действий:

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

2. Настройка параметров PostgreSQL (память, I/O)

3. Аппаратное улучшение дисковых подсистем

4. Мониторинг и алертинг ключевых метрик

Система не испытывает критической нехватки CPU или памяти, но дисковый ввод-вывод стал узким местом из-за изменившегося характера нагрузки (крупные запросы на чтение).

Показать полностью 11

[моё] Postgresql Нейронные сети Статья Инженер DeepSeek Длиннопост

kznalp

Postgres DBA

Нейросеть против PostgreSQL: системные ошибки AI в прогнозировании производительности под нагрузкой⁠⁠

Серия СУБД PostgreSQL

1 месяц назад

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

Стоимостная модель против реальности: как нейросеть не смогла предсказать поведение PostgreSQL при 22 параллельных сессиях

Предисловие

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

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

kznalp/PG_EXPECTO

pg-expecto pg_expecto

Методология эксперимента

Для тестирования прогностической способности нейросети была развернута тестовая среда PostgreSQL 17 с конфигурацией CPU=8 ядер, RAM=8GB. Создана таблица pgbench_test с 1 млн записей, выполнялся запрос с соединением по внешнему ключу. Анализировались два метода доступа: последовательное сканирование (Seq Scan) и индексное сканирование (Index Only Scan) с покрывающим индексом idx_pgbench_test_bid_abalance.

Подробности эксперимента(прогноз и анализ нейросети)

Использование нейросети для прогноза производительности СУБД PostgreSQL

Прогноз нейросети и его несоответствие реальности

Нейросеть, проанализировав планы выполнения запросов, выдала категоричный прогноз:

Index Only Scan: оптимальная производительность (~2.5 ms) даже при 22 параллельных сессиях
Seq Scan: катастрофическая деградация производительности (+400%) при превышении 10 сессий

Реальные результаты нагрузочного тестирования показали принципиально иную картину:

Среднее преимущество Seq Scan составило 9%
В самой финальной фазе теста Index Only Scan демонстрировал лучшую производительность
Оба метода показали сопоставимую устойчивость к нагрузке

График изменения операционной скорости в ходе нагрузочного тестирования при использовании метода доступа Seq Scan и Index only Scan

Критический анализ ошибок нейросетевого прогноза

1. Неспособность моделировать динамическое кэширование

Нейросеть основывалась на статической стоимостной модели, игнорируя эффект прогрева БД. В реальности Seq Scan выигрывал от полного размещения данных в shared_buffers, что нивелировало его основной недостаток - физический I/O.

2. Игнорирование конкуренции за индексные структуры

Модель недооценила contention в B-деревьях при высокой параллельности. Index Only Scan, предсказанный как идеальное решение, столкнулся с блокировками страниц индекса при одновременном доступе 22 сессий.

3. Ошибочная оценка масштабируемости

Нейросеть переоценила линейность индексного доступа и недооценила эффективность параллельного Seq Scan. Распределенная нагрузка workers оказалась стабильнее концентрированной нагрузки на индекс.

Системные ограничения нейросетей в экспертизе СУБД

Проведенный эксперимент выявил фундаментальные проблемы применения AI для анализа производительности БД:

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

Игнорирование конкурентного доступа - модели не способны адекватно предсказать поведение системы при одновременном доступе множества процессов.

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

Заключение

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

Показать полностью 1

[моё] Postgresql Тестирование Исследования Статья Субд Искусственный интеллект Нейронные сети DeepSeek Длиннопост

kznalp

Postgres DBA

ИИ как опасный советчик: Почему нейросетям нельзя доверять настройку производительности PostgreSQL⁠⁠

Серия СУБД PostgreSQL

1 месяц назад

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

Нейросеть видит паттерны, но не чувствует боль базы данных.

Аннотация

В статье проводится сравнительный анализ эффективности использования оператора JOIN и коррелированного подзапроса в СУБД PostgreSQL в условиях высокой параллельной нагрузки. На основе экспериментальных данных опровергаются универсальные рекомендации систем искусственного интеллекта и выявляются ключевые факторы, влияющие на производительность.

kznalp/PG_EXPECTO

pg-expecto pg_expecto

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

Рассматривается вопрос выбора оптимального паттерна для выполнения запросов при высокой параллельной нагрузке на СУБД: использование JOIN или коррелированного подзапроса.

Были получены рекомендации от нейросетевых моделей:

«Ask Postgres»: Для нагрузочных тестов с растущей параллельностью всегда используйте версию с JOIN. Коррелированные подзапросы с агрегациями — плохая практика в сценариях с высокой конкуренцией.
«DeepSeek»: Для данного сценария производительность будет выше при использовании запроса с LEFT JOIN и GROUP BY.

2. Детали эксперимента

Полное описание эксперимента:

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

2.1. Тестовый запрос с использованием JOIN

SELECT
c.customer_id, COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

План выполнения

HashAggregate (cost=35.85..37.25 rows=140 width=12) (actual time=0.622..0.629 rows=25 loops=1)
Group Key: c.customer_id
Batches: 1 Memory Usage: 40kB
-> Hash Right Join (cost=13.15..30.85 rows=1000 width=8) (actual time=0.077..0.429 rows=1000 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (cost=0.00..15.00 rows=1000 width=8) (actual time=0.035..0.148 rows=1000 loops=1)
-> Hash (cost=11.40..11.40 rows=140 width=4) (actual time=0.028..0.028 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on customers c (cost=0.00..11.40 rows=140 width=4) (actual time=0.018..0.021 rows=25 loops=1)
Planning Time: 0.221 ms
Execution Time: 0.787 ms

2.2. Тестовый запрос с использованием коррелированного подзапроса

SELECT c.customer_id,
(SELECT COUNT(o.order_id)
FROM orders o
WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;

План выполнения

Seq Scan on customers c (cost=0.00..1015.20 rows=140 width=12) (actual time=0.093..0.614 rows=25 loops=1)
SubPlan 1
-> Aggregate (cost=7.16..7.17 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=25)
-> Bitmap Heap Scan on orders o (cost=1.56..7.06 rows=40 width=4) (actual time=0.007..0.017 rows=40 loops=25)
Recheck Cond: (customer_id = c.customer_id)
Heap Blocks: exact=125
-> Bitmap Index Scan on idx_orders_customer_id (cost=0.00..1.55 rows=40 width=0) (actual time=0.004..0.004 rows=40 loops=25)
Index Cond: (customer_id = c.customer_id)
Planning Time: 0.145 ms
Execution Time: 0.718 ms

2.3. Сравнение производительности СУБД в ходе нагрузочного тестирования

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

График изменения операционной скорости в ходе нагрузочного тестирования.

3. Анализ причин некорректности рекомендаций нейросетей

3.1. Применение статических эвристик вместо анализа плана выполнения

Нейросети опираются на общие рекомендации, такие как:

«JOIN обычно эффективнее подзапросов»;
«Избегайте N+1 запросов»;
«Коррелированные подзапросы плохо масштабируются».

Однако они не анализируют конкретные планы выполнения запросов в условиях высокой нагрузки и конкуренции за ресурсы.

3.2. Игнорирование паттернов доступа к данным

Анализ планов выполнения показал:

Запрос 1 (JOIN): Seq Scan on orders (полное сканирование таблицы).
Запрос 2 (Подзапрос): Bitmap Index Scan on idx_orders_customer_id (точечный доступ по индексу).

При параллельных соединениях:

Количество сессий × Seq Scan = количество полных сканирований таблицы orders.
Количество сессий × Index Scan = равномерно распределенная нагрузка на чтение.

3.3. Неучёт механизмов блокировки и конфликтов ресурсов

Проблема JOIN при высокой конкуренции: Все сессии одновременно читают одни и те же страницы таблицы orders, что вызывает конфликт ресурсов (contention) на буферный кэш и подсистему ввода-вывода.
Преимущество подзапроса: Каждая сессия работает с разными частями индекса, что снижает конкуренцию за блокировки и улучшает параллелизм.

3.4. Разный профиль использования памяти

JOIN: Memory Usage: 40 kB + хэш-таблица.
Подзапрос: Точечное использование памяти для каждого клиента.

При множественных сессиях JOIN создает значительную нагрузку на shared_buffers.

4. Критические факторы, упускаемые нейросетями

Влияние на shared_buffers: Множественные последовательные сканирования вытесняют полезные данные из кэша.
Lock contention: Конкуренция за одни и те же ресурсы (блокировки).
Распределение операций ввода-вывода: Индексные чтения лучше распределены.
Параметры PostgreSQL: Значения work_mem, shared_buffers, random_page_cost и других настроек существенно влияют на результат.

5. Причины ошибок в рекомендациях нейросетевых моделей

Нейросети обучаются на синтетических или упрощённых данных, для которых характерны:

Небольшой объём наборов данных (TPC-H, TPC-DS).
Низкая параллельность запросов (1–10 соединений).
Идеализированные индексы.
Отсутствие блокировок и конкуренции за ресурсы (ЦП, ввод-вывод).

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

6. Заключение

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

Показать полностью 1

[моё] Postgresql Тестирование Нейронные сети Длиннопост

Посты не найдены

1 2 3 4 5 6 7 8