Postgresql + Нейронные сети

В продолжении Интересно - будет ли ответ на прямой вопрос на Хабре?

Ответ в комментариях

По результатам нескольких независимых исследований было установлено количественное влияние изменения fillfactor на производительность PostgreSQL при массовых UPDATE операциях.

Для снижения fillfactor на 50% (с 100% до 50%):
Основываясь на исследованиях, можно ожидать:

• Улучшение производительности UPDATE на 50-70%

• Увеличение потребления места на диске вдвое

• Снижение производительности SELECT на 20-30% из-за большего количества страниц

https://habr.com/ru/companies/otus/articles/923648/#comment_28526716

Ссылки на источники конечно можно

1. https://www.cybertec-postgresql.com/en/what-is-fillfactor-and-how-does-it-affect-postgresql-performance/

2. https://www.razsamuel.com/understanding-hot-updates-in-postgresql-a-benchmark-analysis/

3. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index?view=sql-server-ver17

4. https://habr.com/ru/companies/otus/articles/905882/

5. https://stackoverflow.com/questions/69601803/tuning-fillfactor-for-high-update-tables-in-postgres-13

6. https://www.geeksforgeeks.org/sql/sql-fill-factor-and-performance/

7. https://dba.stackexchange.com/questions/56735/postgresql-when-should-i-use-fillfactor-100-for-tables-on-ssd

8. https://www.sqlshack.com/sql-server-index-fill-factor-with-performance-benchmark/

Пруфы : https://habr.com/ru/companies/otus/articles/923648/#comment_28526804

Очень большое подозрение - ответ сгенерирован нейросетью.

Самый главный вопрос по пруфам - как меняется нагрузка на СУБД ? Надо внимательнее поизучать.

Итак теперь самое интересное , можно сравнить с собственными результатами:

Для UPDATE

Для данной СУБД , при использовании сценария "Update only", уменьшение значения параметра fillfactor с 100 до 50 дает прирост производительности СУБД:

• При нагрузке от 5 до 115 соединений : от -2 до 17%

• При нормальной нагрузке(5-22 соединения): от 7 до 16%

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

Для смешанной нагрузки

Для данной СУБД , при использовании сценария смешанной нагрузки "Mix", уменьшение значения параметра fillfactor с 100 до 50 дает прирост производительности СУБД:

• При нагрузке от 5 до 115 соединений : от -6 до 9%

• При нормальной нагрузке(5-22 соединения): от 0 до 9%

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

Разница в результатах - очень значительная.

Нужно внимательнее изучить постановку экспериментов в "независимых исследованиях".

1. What is fillfactor and how does it affect PostgreSQL performance?

07.2020

Pgbench: scales 1000/2500/10000, 12h runtime for each scale / FF combination, 2 concurrent sessions.

2 сессии - это не серьезно . Тут анализировать нечего.

2. Optimizing PostgreSQL Updates with HOT and Fillfactor

March 10, 2025

For our benchmark, we created a table with 7 columns, three indexes, and 1,000,000 rows. We then updated 30% of the rows and measured the update performance across different fillfactor settings.

Синтетический тест самописным бенчмарком не используя pgbench. Тут анализировать нечего.

3. Specify Fill Factor for an Index

Блин - мелкомягкий каким боком оказался ?

Тут анализировать нечего.

4. Зачем нужен fillfactor в PostgreSQL

Змея укусила себя за хвост . В комментарии на статью приводится исходная статья. Забавно.

Тут анализировать нечего.

5. HOT and FILLFACTOR Results

Синтетический тест самописным бенчмарком не используя pgbench. Тут анализировать нечего.

6. SQL Fill Factor and Performance

Блин - мелкомягкий каким боком оказался ?

Тут анализировать нечего.

7. PostgreSQL: When should I use fillfactor < 100 for tables on SSD?

Описание бенчмарка - отсутствует.

Тут анализировать нечего.

8. https://www.sqlshack.com/sql-server-index-fill-factor-with-performance-benchmark/

403 - Forbidden

Итог

Комментарий на Хабре :

https://habr.com/ru/companies/otus/articles/923648/#comment_28526716

Сгенерирован нейросетью и никакой практической пользы для инженеров DBA - не представляет.

Студентам втюхать курсовой , да вполне подойдет - картинки красивые и ссылок много.

Инженерам - для работы - только время тратить.

Чуда не произошло. Хабр и хабровчане не удивили.

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

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

Какие типы ожидания wait_event_type будут иметь наибольшую корреляцию со снижением производительности СУБД PostgreSQL при выполнении UPDATE для виртуальной машины CPU = 2 и RAM = 2GB при росте нагрузки с 5 до 115 одновременных соединений ?

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

При анализе корреляции между типами ожиданий (wait_event_type) в PostgreSQL и снижением производительности во время UPDATE на слабой ВМ (2 vCPU, 2 ГБ RAM) при росте нагрузки с 5 до 115 соединений, наибольшую корреляцию будут иметь следующие типы ожиданий, ранжированные по степени влияния:

1. LWLock (Lightweight Locks)

• Почему корреляция высокая:

  • На слабых ВМ (особенно с 2 vCPU) нехватка CPU усиливает конкуренцию за системные ресурсы.

  • LWLock защищает внутренние структуры данных (буферы, память, lock-менеджер). При высокой нагрузке (115 соединений) процессы блокируются в очередях на эти ресурсы.

  • Подтипы buffer_content, buffer_mapping, lock_manager будут доминировать из-за частого доступа к буферам и блокировкам.

• Связь с производительностью: Прямая корреляция с ростом времени ожидания и снижением TPS (transactions per second).

• 
  

  2. Lock (Транзакционные блокировки)

  • Почему корреляция высокая:

    • UPDATE вызывает эксклюзивные блокировки строк (ROW SHARE/EXCLUSIVE).

    • При 115 соединениях конкуренция за одни строки/таблицы резко возрастает → частые ожидания снятия блокировок.

    • Нехватка RAM (2 ГБ) уменьшает эффективность work_mem → транзакции дольше удерживают блокировки.

  • Ключевые подтипы: transactionid, tuple (блокировки кортежей).

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

  • Почему корреляция высокая:

    • Мало RAM → буферный кеш перегружен → частые чтения с диска (read I/O).

    • UPDATE генерирует WAL-записи → нагрузка на write I/O.

    • На ВМ без SSD дисков латентность I/O резко снижает производительность.

  • Подтипы: DataFileRead (чтение данных), WALWrite (запись журнала).

4. BufferPin

• Почему корреляция средняя:

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

  • Усиливается при нехватке RAM и высокой конкуренции за буферы.

  • Менее критичен, чем LWLock/Lock, но значим при массовых UPDATE.

Итог:
Наибольший вклад в снижение производительности внесут LWLock и Lock из-за нехватки CPU/RAM и высокой конкуренции. IO станет критичным при исчерпании буферного кеша.

Как обстоит ситуация в реальности :

Результаты

Для данного сценария "Update only"и данной конфигурации СУБД характерными значениями корреляции между ожиданиями СУБД и типами ожиданий , являются :

• IO : сильная отрицательная корреляция , влияние ожиданий типа IO на производительность СУБД отсутствует.

• IPC : ожидания практически отсутствуют, влияние на производительность СУБД отсутствует.

• Lock : очень сильная прямая корреляция, сильное влияние ожиданий типа Lock на снижение производительности СУБД .

• LWLock : слабая отрицательная корреляция , влияние ожиданий типа LWLock на производительность СУБД отсутствует.

Подробности :

PG_HAZEL : Характерные ожидания при выполнении сценария "UPDATE ONLY"

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

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

Как на производительность СУБД PostgreSQL влияет недостаточный размер shared_buffers ?

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

**Увеличение операций ввода-вывода (I/O)**
**Неэффективное использование кэшей**
**Рост времени отклика**
**Проблемы с контрольными точками (checkpoints)**
**Конкуренция за блокировки**

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

Как на производительность СУБД PostgreSQL влияет слишком большой размер shared_buffers ?

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

**Рост дискового I/O**:
**Неэффективное использование памяти (Double Buffering)**
**Снижение производительности контрольных точек (Checkpoint)
** **Снижение эффективности кэша**

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

Все нейросети имеют принципиальное и пока непреодолимое ограничения - они не обладают экспериментальными данными , только тексты , большая часть которых - мусор, в интернете . И уж конечно , я по крайней мере пока не встречал , нейросети не умеют экспериментировать и логически осмысливать результаты .
Хотя , в области ML по экспериментам по оптимизации запросов что, то было . Но там очень ограниченная область. Да и тишина, что то последние 5 лет. Все внимание себе забрал ии.

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

А нейросети отлично подходят когда надо воды налить в инструкции и статьи - тут им конкурентов нет.

Вопрос : Какие события ожидания возникают при излишнем размере shared_buffers ?

Ответ ChatPPG:

При установке слишком большого значения для параметра shared_buffers в PostgreSQL могут возникнуть следующие проблемы и события ожидания:

1. Двойное кэширование (Double Buffering):
- Когда страница данных должна быть загружена в shared_buffers, она может сначала вытеснить существующую страницу, а затем операционная система (OS) может также вытеснить страницу, чтобы прочитать данные с диска. Это приводит к тому, что одна и та же страница хранится как в кэше PostgreSQL, так и в кэше OS, что приводит к избыточному использованию памяти и потенциально к более медленной производительности, так как страницы могут чаще читаться напрямую с диска, а не из кэша.

2. Нестабильность системы:
- Если shared_buffers установлен слишком высоко, это может привести к ошибкам нехватки памяти (out-of-memory errors) или активации механизма OOM (Out-Of-Memory) управляющего процессом, что может привести к нестабильности всей системы.

3. Затраты на очистку кэша после операций:
- После операций, таких как DROP TABLE, TRUNCATE или определенных команд COPY, PostgreSQL должна отменить большое количество буферов. При больших размерах кэша этот процесс может стать значительно более времязатратным и ресурсоемким.

4. События ожидания (Wait Events):
- Из-за избыточного использования памяти могут возникать события ожидания, связанные с операциями ввода-вывода (I/O). Например, события ожидания могут возникать из-за того, что система ожидает завершения операций чтения или записи данных с диска, что может замедлить общую производительность.

Для оптимизации использования памяти рекомендуется тщательно настроить значение shared_buffers относительно доступной оперативной памяти системы. Обычно рекомендуется устанавливать shared_buffers на значение, которое составляет от 25% до 40% от общего объема оперативной памяти, но не более 40%, чтобы избежать вышеупомянутых проблем.

Дополнительная информация о настройке shared_buffers доступна в документации PostgreSQL: [Memory Configuration](https://postgrespro.com/docs/postgrespro/15/runtime-config-r...).

Вывод - нейросеть ChatPPG не знает ответа на вопрос.
Либо , в PostgreSQL ожидания возникающие при большом размере shared_buffers не логируются. Как например , не логируются ожидания по причине утилизации CPU.

A вот ответ DeepSeek, гораздо интереснее , потому , что содержит конкретные названия событий ожидания (выделено мной):

При излишне большом размере `shared_buffers` в PostgreSQL могут возникать следующие события ожидания и проблемы:

### 1. **Увеличение времени поиска в буферном кеше**
- **Проблема**: Большой размер `shared_buffers` увеличивает время поиска данных в кеше из-за роста хеш-таблицы, управляющей буферами.
- **Ожидание**: `BufferPin` (задержки из-за конкуренции за доступ к буферам).

### 2. **Рост накладных расходов на блокировки**
- **Проблема**:
- Увеличение конкуренции за `LWLock` (легковесные блокировки), особенно за `buffer_mapping` и `buffer_content`.
- Блокировки на уровне буферов (`BufferLock`) становятся "узким местом".
- **Ожидания**:
- `LWLock: buffer_content`
- `LWLock: buffer_mapping`

### 3. **Неэффективное использование памяти**
- **Проблема**:
- Память отнимается у кеша ОС, что приводит к **двойному кешированию** (данные хранятся и в `shared_buffers`, и в кеше ОС).
- Нехватка памяти для рабочих процессов (`work_mem`, `maintenance_work_mem`).
- **Симптомы**: Увеличение операций I/O из-за отсутствия данных в кеше ОС.

### 4. **Проблемы с управлением грязными страницами**
- **Проблема**:
- При checkpoint требуется записать больше "грязных" страниц, что вызывает всплеск I/O-нагрузки.
- Увеличение времени завершения контрольных точек.
- **Ожидания**:
- `CheckpointWriteDelay` (принудительные задержки для регулировки записи).
- `WALWrite` (ожидание записи WAL).

### 5. **Деградация производительности при высокой конкуренции**
- **Проблема**:
- Множество процессов пытаются одновременно получить доступ к буферам.
- Увеличивается время освобождения буферов (`clock sweep` алгоритм замещения работает медленнее).
- **Ожидания**:
- `BufferPin`
- `IO: DataFileRead` (если данные не найдены в кеше).

### 6. **Снижение отзывчивости системы**
- **Проблема**:
- Избыточная память под `shared_buffers` может вызвать свопинг (swapping), если общая память сервера исчерпана.
- Увеличивается нагрузка на подсистему I/O и ЦП.

---

### Рекомендации:
1. **Оптимальный размер**:
- **15-25% от ОЗУ сервера** (но не более 40%).
- Пример: Для сервера с 64 ГБ ОЗУ — 8–16 ГБ.
2. **Мониторинг**:
- Следите за соотношением `cache hit ratio` в `pg_stat_bgwriter`. Если близок к 99% — увеличение `shared_buffers` не нужно.
- Анализируйте ожидания в `pg_stat_activity` и `pg_locks`.
3. **Настройка смежных параметров**:
- Увеличьте `checkpoint_timeout` и `max_wal_size` для снижения частоты контрольных точек.
- Настройте `bgwriter_delay` и `bgwriter_lru_maxpages` для эффективной записи грязных страниц.

### Пример диагностики:
```sql
-- Проверка hit ratio:
SELECT (sum(blks_hit) / sum(blks_hit + blks_read)) * 100 AS cache_hit_ratio
FROM pg_stat_database;

-- Поиск событий ожидания:
SELECT query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;
```

**Итог**: Слишком большой `shared_buffers` нарушает баланс между памятью, блокировками и I/O. Всегда тестируйте настройки под реальную нагрузку!

Самое занятное , что похоже DeepSeek тоже не знает ответа. Потому, что события BufferPin, BufferMapping, BufferContent возникают именно при недостаточном размере shared_buffers, что подтверждено экспериментально .

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

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

https://habr.com/ru/companies/postgrespro/articles/466199/