Добавить в корзинуПозвонить
Найти в Дзене
Postgres DBA
68 подписчиков

PG_EXPECTO - shared_buffers: Постановка эксперимента по анализу производительности СУБД PostgreSQL.

1
28 мин
Статья в процессе подготовки . Производительность СУБД — ключевой фактор для отклика приложения и бизнеса, однако спонтанные проверки часто искажают реальную картину. PG_EXPECTO — это не просто набор скриптов, а чёткая методология, превращающая анализ PostgreSQL из хаотичного поиска проблем в структурированный, воспроизводимый эксперимент. На примере классической темы - "Анализ влияния увеличения shared_buffers на производительность СУБД " сформировать общий план постановки и анализа результатов эксперимента с использованием pg_expecto. Поедвдуший Входные данные: Используя схему базы данных , размеры таблиц, тестовые запросы и планы выполнения тестовых запросов, сформулируй гипотезу о влиянии на производительность СУБД изменения параметра shared_buffers в ходе нагрузочного тестирования с графиком нагрузки(количество сессий) на каждый тестовый запрос согласно load.txt, для СУБД PostgreSQL 17 с ресурсами: CPU=8 RAM=8GB. Значения shared_buffers в ходе экспериментов : 2GB , 4GB , 5GB ,
Оглавление
habr.com
PG_EXPECTO: Анализ влияния размера shared_buffers на производительность СУБД PostgreSQL

Статья в процессе подготовки .

PG_EXPECTO: Эксперимент, а не догадки
PG_EXPECTO: Эксперимент, а не догадки

Предисловие

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

Предыдущие эксперименты

PG_HAZEL : Влияние на производительность и характерные ожидания СУБД увеличения shared_buffers c 25 до 50% от размера RAM.
Postgres DBA23 июня 2025
PG_HAZEL : Влияние изменения параметра shared_buffer на скорость и ожидания СУБД.
Postgres DBA20 апреля 2025

Задача

На примере классической темы - "Анализ влияния увеличения shared_buffers на производительность СУБД " сформировать общий план постановки и анализа результатов эксперимента с использованием pg_expecto.

Поедвдуший

Тестовый сценарий-1(SELECT by PK)

--scenario1 SELECT BY PK QUERY PLAN --scenario1 --SELECT BY PK EXPLAIN (ANALYZE, BUFFERS , COSTS , SUMMARY ) SELECT * FROM bookings.airports_data WHERE airport_code = (   SELECT airport_code   FROM bookings.airports_data   ORDER BY random()   LIMIT 1 );    QUERY PLAN…
Postgres DBA13 декабря 2025

Тестовый сценарий-2(GROUP BY)

--scenario2 GROUP BY --scenario2 --GROUP BY EXPLAIN (ANALYZE, BUFFERS , COSTS , SUMMARY ) WITH random_airplanes AS (   SELECT airplane_code   FROM bookings.airplanes_data   ORDER BY random()   LIMIT (random() * 9 + 1)::int -- От 1 до 10 значений ) SELECT s.fare_conditions,    COUNT(*) as seat_count,    COUNT(DISTINCT s.airplane_code) as airplane_types FROM bookings.seats s WHERE s.airplane_code…
Postgres DBA13 декабря 2025

Тестовый сценарий-3(ORDER BY)

-scenario3 ORDER BY -scenario3 --ORDER BY EXPLAIN (ANALYZE, BUFFERS , COSTS , SUMMARY ) SELECT book_ref,    book_date,    total_amount,    EXTRACT(DAY FROM book_date) as booking_day FROM bookings.bookings WHERE total_amount > 0 ORDER BY book_date LIMIT (random() * 499 + 1)::int; -- От 1 до 500 записей    QUERY PLAN…
Postgres DBA13 декабря 2025

Тестовый сценарий-4(JOIN)

--scenario4 JOIN --scenario4 --JOIN EXPLAIN (ANALYZE, BUFFERS , COSTS , SUMMARY ) WITH random_departures AS (   SELECT airport_code   FROM bookings.airports_data   ORDER BY random()   LIMIT (random() * 9 + 1)::int ), random_validity_date AS (   SELECT    date_trunc('day',    lower(validity) +    (random() * EXTRACT(EPOCH FROM (upper(validity) - lower(validity)))) * interval '1 second'    ) as…
Postgres DBA13 декабря 2025

1. Начальная гипотеза нейросети

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Тестовый запрос и план выполнения запроса #1 (plan-1.sql)
  6. Тестовый запрос и план выполнения запроса #2 (plan-2.sql)
  7. Тестовый запрос и план выполнения запроса #3 (plan-3.sql)
  8. Тестовый запрос и план выполнения запроса #4 (plan-4.sql)

Промпт

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Тестовый запрос и план выполнения запроса #1 (plan-1.sql)
  6. Тестовый запрос и план выполнения запроса #2 (plan-2.sql)
  7. Тестовый запрос и план выполнения запроса #3 (plan-3.sql)
  8. Тестовый запрос и план выполнения запроса #4 (plan-4.sql)

Используя схему базы данных , размеры таблиц, тестовые запросы и планы выполнения тестовых запросов, сформулируй гипотезу о влиянии на производительность СУБД изменения параметра shared_buffers в ходе нагрузочного тестирования с графиком нагрузки(количество сессий) на каждый тестовый запрос согласно load.txt, для СУБД PostgreSQL 17 с ресурсами: CPU=8 RAM=8GB. Значения shared_buffers в ходе экспериментов : 2GB , 4GB , 5GB , 6GB. Уточни ключевые значения ожиданий СУБД wait_event_type и метрик vmstat и iostat , свидетельствующие о слишком большом размере shared_buffers.

Гипотеза нейросети

Промпт для DeepSeek
Промпт для DeepSeek

На основе анализа схемы, размеров данных, конфигурации и планов выполнения запросов, формулирую уточненную гипотезу:

Гипотеза о влиянии shared_buffers при нагрузочном тестировании

Оптимальное значение shared_buffers для данной конфигурации (8GB RAM) будет находиться в диапазоне 2-4GB. При увеличении до 5-6GB произойдет деградация производительности из-за конкуренции за память между shared_buffers, work_mem сессий и операционной системой, особенно при пиковой нагрузке 22 сессии.

Обоснование:

  1. Распределение памяти при пиковой нагрузке (22 сессии):
  2. shared_buffers = 6GB
  3. work_mem = 180MB × 22 сессии ≈ 4GB
  4. Системные нужды, кэш ОС, WAL buffers ≈ 1-2GB
  5. Итого требуется: 11-12GB > 8GB доступной RAM
  6. Характер нагрузки по сценариям:
  7. Scenario1: Random I/O на таблице airports_data (5501 строк) - чувствителен к кэшированию
  8. Scenario2: Index-only scan на seats (1741 строк) - легкий запрос, но требует памяти для сортировки
  9. Scenario3: Index scan на bookings (7.1 млн строк) - последовательное чтение большого объема, критичен к эффективности кэша ОС
  10. Scenario4: Сложные соединения с CTE, Bitmap Index Scan на routes (5792 строк) - требует промежуточной памяти
  11. Анализ текущих планов выполнения (при shared_buffers=2GB):
  12. Scenario3: уже показывает физические чтения (shared read=10) и высокий I/O Timing
  13. Scenario4: эффективно использует индексные сканы, но требует множества обращений к airports_data
  14. Scenario1 и 2: полностью выполняются из кэша (shared hit)

Ключевые признаки слишком большого shared_buffers:

В PostgreSQL wait_event_type:

  1. BufferIO/DataFileRead с высоким wait_time - несмотря на большой shared_buffers, данные читаются с диска
  2. WALWrite и WALSync с ростом времени ожидания - нехватка памяти для WAL буферов
  3. LWLock:buffer_content и LWLock:buffer_mapping конкуренция - конфликты при доступе к shared buffers
  4. IO:DataFileRead на таблицах которые должны быть в кэше - признаки вытеснения страниц

В vmstat (критические значения):

1. swpd > 0 (любое использование swap) - верный признак нехватки памяти

2. si (swap in) > 100 страниц/сек - активное чтение из swap

3. so (swap out) > 50 страниц/сек - активная запись в swap

4. free < 200 MB - критически мало свободной памяти

5. buff/cache резко уменьшается - ОС вытесняет кэш

6. us (user CPU) > 70% при низкой производительности - CPU тратится на управление памятью

7. sy (system CPU) > 30% - высокие накладные расходы ядра

8. cs (context switches) > 10000/сек - частые переключения контекста

В iostat (критические значения для диска):

1. %util > 80% продолжительное время - диск перегружен

2. await > 50ms - высокое время ожидания операций ввода-вывода

3. svctm > 20ms - высокое время обслуживания запросов

4. avgqu-sz > 10 - длинная очередь к диску

5. r/s + w/s > 500 - чрезмерная дисковая активность

6. rkB/s + wkB/s > 100MB/сек - высокая пропускная способность диска (признак свопинга)

Ожидаемое поведение по значениям shared_buffers:

2GB :

  • Преимущества: Оставляет достаточно памяти для OS cache (важно для Scenario3)
  • Недостатки: Возможны cache misses для больших таблиц
  • Ожидаемые wait_event: DataFileRead на таблицах bookings, segments
  • Лимитирующий фактор: Физические чтения при большом количестве сессий

4GB (вероятный оптимум):

  • Преимущества: Улучшенное кэширование рабочих наборов данных
  • Недостатки: Меньше памяти для work_mem при 22 сессиях
  • Ожидаемые улучшения: Scenario1, 2, 4 покажут лучшие результаты
  • Риски: При 22 сессиях возможны temp file операции из-за нехватки work_mem

5GB (зона риска):

  • Проблемы: Начинается конкуренция с памятью ОС
  • Признаки: Рост si/so в vmstat, увеличение await в iostat
  • Wait_event: Появление WALWrite, LWLock ожиданий
  • Деградация: Scenario3 покажет худшие результаты из-за неэффективного кэша ОС

6GB (критическое значение):

  • Проблемы: Системный swap, деградация всех запросов
  • Признаки: swpd > 1GB, free < 100MB, %util диска ~100%
  • Wait_event: Преобладание IO и Client ожиданий
  • Результат: Резкое увеличение времени выполнения всех сценариев

Специфические ожидания по тестовым запросам:

  1. Scenario3 (индексное сканирование bookings) будет наиболее чувствителен к увеличению shared_buffers свыше 4GB из-за потери эффективного кэша ОС для последовательных операций.
  2. Scenario4 (сложные соединения) покажет наибольшее улучшение при увеличении до 4GB за счет кэширования индексов, но начнет деградировать при 5-6GB из-за нехватки памяти для hash-соединений.
  3. Scenario1 и 2 будут показывать стабильные результаты до 4GB, затем возможен рост времени выполнения из-за накладных расходов управления памятью.

Вывод:

Оптимальным для данной конфигурации и нагрузки будет значение shared_buffers=4GB. При 5-6GB система столкнется с дефицитом памяти, особенно при пиковой нагрузке 22 сессии, что приведет к активному использованию swap и деградации производительности всех типов запросов, особенно тяжелых OLAP-подобных операций (Scenario3 и 4).

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

Нагрузка на СУБД

-3

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

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

PG_EXPECTO : Показатели производительности и ожиданий СУБД в ходе нагрузочного тестирования
Postgres DBA15 октября 2025

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

PG_EXPECTO : Построение графиков производительности и ожиданий по результатам нагрузочного тестирования СУБД
Postgres DBA15 октября 2025

Статистические данные iostat

PG_EXPECTO : Данные для построения графиков показателей iostat дискового устройства
Postgres DBA16 октября 2025

Статистические vmstat

PG_EXPECTO : Данные для построения графиков показателей vmstat
Postgres DBA16 октября 2025

1. Сравнительный график операционной скорости

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

shared_buffers=4GB / shared_buffers=2GB

График изменения относительной разницы операционной скорости СУБД для эксперимента "shared_buffers=4GB" по сравнению операционной скорости СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования
График изменения относительной разницы операционной скорости СУБД для эксперимента "shared_buffers=4GB" по сравнению операционной скорости СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования

Среднее уменьшение операционной скорости для эксперимента "shared_buffers=4GB" по сравнению с экспериментом "shared_buffers=2GB" составило 0.54%

shared_buffers=5GB / shared_buffers=2GB

График изменения относительной разницы операционной скорости СУБД для эксперимента "shared_buffers=5GB" по сравнению операционной скорости СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования
График изменения относительной разницы операционной скорости СУБД для эксперимента "shared_buffers=5GB" по сравнению операционной скорости СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования

Среднее уменьшение операционной скорости для эксперимента "shared_buffers=5GB" по сравнению с экспериментом "shared_buffers=2GB" составило 0.62%

shared_buffers=6GB / shared_buffers=2GB

График изменения относительной разницы операционной скорости СУБД для эксперимента "shared_buffers=6GB" по сравнению операционной скорости СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования
График изменения относительной разницы операционной скорости СУБД для эксперимента "shared_buffers=6GB" по сравнению операционной скорости СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования

Среднее увеличние операционной скорости для эксперимента "shared_buffers=6GB" по сравнению с экспериментом "shared_buffers=2GB" составило 0.56%

2. Сравнительный график ожиданий СУБД

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

shared_buffers=4GB / shared_buffers=2GB

График изменения относительной разницы ожиданий СУБД для эксперимента "shared_buffers=4GB" по сравнению ожиданиями СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования
График изменения относительной разницы ожиданий СУБД для эксперимента "shared_buffers=4GB" по сравнению ожиданиями СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования

Среднее уменьшение ожиданий для эксперимента "shared_buffers=4GB" по сравнению с экспериментом "shared_buffers=2GB" составило 0.22%

shared_buffers=5GB / shared_buffers=2GB

График изменения относительной разницы ожиданий СУБД для эксперимента "shared_buffers=5GB" по сравнению ожиданиями СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования
График изменения относительной разницы ожиданий СУБД для эксперимента "shared_buffers=5GB" по сравнению ожиданиями СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования

Среднее увеличение ожиданий для эксперимента "shared_buffers=5GB" по сравнению с экспериментом "shared_buffers=2GB" составило 5.75%

shared_buffers=6GB / shared_buffers=2GB

График изменения относительной разницы ожиданий СУБД для эксперимента "shared_buffers=6GB" по сравнению ожиданиями СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования
График изменения относительной разницы ожиданий СУБД для эксперимента "shared_buffers=6GB" по сравнению ожиданиями СУБД для эксперимента "shared_buffers=2GB" в ходе нагрузочного тестирования

Среднее увеличение ожиданий для эксперимента "shared_buffers=6GB" по сравнению с экспериментом "shared_buffers=2GB" составило 6.50%

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

Входные данные:

  1. Схема тестовой базы данных : demo_schema.sql
  2. Размеры таблиц : tables.txt
  3. Конфигурация СУБД : postgres.auto.conf (остальные параметры - по умолчанию)
  4. Таблица нагрузки: load.txt
  5. Список queryid : sql_list.txt
  6. Тестовые запросы и планы выполнения: plans.txt
  7. Регрессионный и корреляционный анализ производительности и ожиданий СУБД для эксперимента N(1-4) : N.postgres.1.cluster_report_meta.txt
  8. Статистические данные по производительности и ожиданиям СУБД (wait_event_type) для эксперимента N(1-4) : N.postgres.1.cluster_report_4graph.txt

Используя входные данные, сформируй сравнительный анализ .

Выдели ключевые отличия в производительности(speed), типах ожиданий СУБД(wait_event_type) , событий ожидания СУБД(wait_event). Сформируй итог по серии экспериментов.

Промпт для DeepSeek
Промпт для DeepSeek

1. Общая характеристика экспериментов

Временные периоды:

  • Эксперимент 1: 2025-12-12 10:21 - 12:10 (109 минут)
  • Эксперимент 2: 2025-12-12 13:13 - 15:02 (109 минут)
  • Эксперимент 3: 2025-12-12 17:04 - 18:53 (109 минут)
  • Эксперимент 4: 2025-12-13 08:49 - 10:38 (109 минут)

Нагрузка: Во всех экспериментах нагрузка плавно возрастала от 5 до 22 единиц.

2. Сравнительный анализ производительности

-13

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

  • Все эксперименты демонстрируют стабильное снижение производительности с течением времени (отрицательные углы наклона)
  • Наивысшая пиковая производительность в эксперименте 1 (2.967.383)
  • Наименьшая минимальная производительность в эксперименте 2 (1.181.182)
  • Коэффициент детерминации R² ≈ 0,95-0,96 указывает на сильную линейную зависимость

3. Сравнительный анализ ожиданий СУБД

-14

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

  • Эксперимент 2 имеет максимальные ожидания (183) и наибольший диапазон
  • Эксперимент 1 имеет минимальные максимальные ожидания (119)
  • Во всех экспериментах наблюдается снижение ожиданий со временем
  • Корреляция SPEED-WAITINGS: 0,87-0,91 (сильная обратная зависимость)

4. Анализ типов ожиданий (wait_event_type)

Эксперимент 1:

  • Основные типы: LWLOCK (0,95), TIMEOUT (1,00)
  • Отсутствуют: IO, IPC, LOCK, BUFFERPIN, EXTENSION
  • Корреляция: WAITINGS-LWLOCK=0,95, WAITINGS-TIMEOUT=1,00

Эксперимент 2:

  • Основные типы: LWLOCK (0,96), TIMEOUT (0,98)
  • Появляются: IO (незначительно), IPC (незначительно)
  • Корреляция: WAITINGS-LWLOCK=0,96, WAITINGS-TIMEOUT=0,98

Эксперимент 3:

  • Основные типы: LWLOCK (0,96), TIMEOUT (1,00)
  • Незначительные: IO, IPC
  • Корреляция: WAITINGS-LWLOCK=0,96, WAITINGS-TIMEOUT=1,00

Эксперимент 4:

  • Основные типы: LWLOCK (0,96), TIMEOUT (0,99), IO (0,93)
  • Корреляция: Сильная корреляция WAITINGS-IO=0,93

5. Анализ событий ожидания (wait_event)

Общие для всех экспериментов:

  • LWLOCK: Преобладают lightweight locks (средний уровень 5-8)
  • TIMEOUT: Доминирующий тип ожиданий (80-90% от WAITINGS)

Уникальные особенности:

  • Эксперимент 2: Пиковые значения IO=34 и IPC=20 (в начале эксперимента)
  • Эксперимент 4: Устойчивое присутствие IO ожиданий (до 12 единиц)
  • Эксперименты 2,3,4: Появление IPC ожиданий (до 21 в эксп. 4)

6. Связь с выполняемыми запросами

Из sql_list.txt и plans.txt:

  • scenario1(): SELECT BY PK (индексный доступ)
  • scenario2(): GROUP BY (агрегация с сортировкой)
  • scenario3(): ORDER BY (сортировка больших объемов)
  • scenario4(): JOIN (сложные соединения)

Гипотеза распределения:

  • Эксперимент 1: Преобладание scenario1 (минимальные IO ожидания)
  • Эксперимент 2: Преобладание scenario3/scenario4 (максимальные IO/IPC)
  • Эксперимент 4: Преобладание scenario4 (устойчивые IO ожидания)

7. Влияние конфигурации СУБД

postgresql.auto.conf:

  • effective_io_concurrency = 300 (высокий)
  • random_page_cost = 1.1 (низкий)
  • shared_buffers = 2GB
  • work_mem = 180MB

Выявленные эффекты:

  1. Высокий effective_io_concurrency позволяет параллельные IO операции
  2. Низкий random_page_cost способствует использованию индексов
  3. Достаточный work_mem уменьшает временные операции на диск

8. Итоговый вывод по серии экспериментов

Ключевые отличия между экспериментами:

  1. Эксперимент 1 (базовый):
  2. Наименьшие максимальные ожидания
  3. Отсутствие IO/IPC ожиданий
  5. Эксперимент 2 (максимальная нагрузка):
  6. Наибольшие общие ожидания (183)
  7. Пиковые значения IO/IPC
  9. Эксперимент 3 (сбалансированный):
  10. Промежуточные значения ожиданий
  11. Незначительные IO/IPC
  12. Стабильная работа
  13. Эксперимент 4 (IO-нагруженный):
  14. Устойчивые IO ожидания (корреляция 0,93)

Общие тенденции:

  1. Производительность стабильно снижается с увеличением нагрузки во всех экспериментах
  2. TIMEOUT и LWLOCK - доминирующие типы ожиданий во всех сценариях
  3. IO ожидания появляются только при выполнении запросов с большими объемами данных
  4. Конфигурация СУБД адекватна нагрузке

Общий итог:

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

4.1 Анализ событий ожиданий BufferPin для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных : demo_schema.sql
  2. Размеры таблиц : tables.txt
  3. Конфигурация СУБД : postgres.auto.conf (остальные параметры - по умолчанию)
  4. Таблица нагрузки: load.txt
  5. Список queryid : sql_list.txt
  6. Тестовые запросы и планы выполнения: plans.txt
  7. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = BufferPin , для эксперимента N(1-4) для сценария1: N.scenario1.BufferPin.txt

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

Промпт для DeepSeek
Промпт для DeepSeek

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

Анализируемый запрос: SELECT scenario1() (QUERYID: -3890986969840966328)

Тип ожидания: BufferPin (блокировка буфера в shared_buffers)

Период анализа: 4 эксперимента по ~110 минут каждый

Нагрузка: варьируется от 5 до 22 одновременных подключений

Ключевые отличия между экспериментами

1. Плотность событий BufferPin

-16

Наблюдение: Наиболее высокая плотность событий в эксперименте 3 (0.72 события/мин), наименьшая - в эксперименте 2 (0.50 события/мин).

2. Динамика накопления вызовов (calls)

-17

Наблюдение:

  • Наибольший прирост вызовов в эксперименте 3 (17,216)
  • Наименьший прирост в эксперименте 2 (15,442)

3. Паттерны возникновения событий

Эксперимент 1 (12 декабря, утренняя сессия):

  • События начинаются относительно редко (раз в 1-4 минуты)
  • Учащаются к середине сессии (каждые 1-2 минуты)
  • Пиковая плотность: 11:19-11:26 (7 событий за 7 минут)

Эксперимент 2 (12 декабря, послеобеденная сессия):

  • Наиболее стабильный паттерн
  • Наименьшая общая частота событий
  • Длительные периоды без событий (до 5 минут)

Эксперимент 3 (12 декабря, вечерняя сессия):

  • Наиболее высокая плотность событий
  • Частые события в периоды 17:44-17:47 и 18:39-18:41
  • Явные "всплески" активности

Эксперимент 4 (13 декабря, утренняя сессия):

  • Схож с экспериментом 1 по общей плотности
  • Более равномерное распределение событий
  • Умеренная частота на протяжении всей сессии

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

Сопоставление с файлом нагрузки (load.txt):

-18

Наблюдение: Нет прямой линейной зависимости между нагрузкой и частотой BufferPin. Пиковые значения (0.82 события/мин) наблюдаются при нагрузках 9, 13 и 15.

Анализ причин BufferPin для scenario1()

Исходя из плана выполнения scenario1():

  1. Запрос выполняет сложную операцию с вложенными подзапросами
  2. Используется ORDER BY random() что требует полного сканирования и сортировки
  3. Множественные операции с буферами при работе с таблицами tickets и segments

Конфигурационные факторы, влияющие на BufferPin:

  • shared_buffers = 2GB - относительно небольшой размер для нагрузки до 22 подключений
  • work_mem = 180MB - может приводить к частой записи промежуточных данных на диск
  • max_connections = 1000 - высокий лимит, но фактическая нагрузка значительно ниже
  • effective_io_concurrency = 300 - оптимизировано для SSD

Итоги по серии экспериментов

1. Стабильность возникновения BufferPin

События BufferPin возникают во всех экспериментах с частотой 0.5-0.72 события в минуту для запроса scenario1(). Это указывает на систематическую, а не случайную природу проблемы.

2. Влияние нагрузки

Прямой корреляции между уровнем нагрузки (5-22 подключения) и частотой BufferPin не обнаружено. Это свидетельствует о том, что:

  • Проблема связана с архитектурой запросов
  • Конфигурация СУБД адекватно справляется с варьирующей нагрузкой
  • Лимитирующим фактором является не количество подключений, а особенности выполнения конкретного запроса

3. Временные паттерны

  • Эксперимент 3 выделяется наибольшей частотой событий, что может быть связано с:
  • Накопленным состоянием буферного кэша
  • Конкуренцией с другими процессами
  • Эксперимент 2 показывает наилучшие результаты, несмотря на одинаковую нагрузку

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

Для запроса scenario1():

  1. Пересмотреть использование ORDER BY random() с LIMIT
  2. Рассмотреть материализацию промежуточных результатов
  3. Добавить индексы, покрывающие условия WHERE EXISTS

Для конфигурации СУБД:

  1. Увеличить shared_buffers до 4-6GB (при наличии доступной памяти)
  2. Настроить work_mem в зависимости от типа запросов
  3. Рассмотреть использование pg_prewarm для критических таблиц

Заключение

События BufferPin для запроса scenario1() носят систематический характер и возникают с частотой 0.5-0.7 события в минуту при нагрузке 5-22 подключения. Проблема обусловлена в большей степени архитектурой запроса, чем конфигурацией СУБД или уровнем нагрузки. Наиболее эффективным направлением оптимизации является переработка самого запроса, в частности - устранение ORDER BY random() в подзапросе и оптимизация работы с большими таблицами tickets и segments.

4.2 Анализ событий ожиданий BufferPin для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных : demo_schema.sql
  2. Размеры таблиц : tables.txt
  3. Конфигурация СУБД : postgres.auto.conf (остальные параметры - по умолчанию)
  4. Таблица нагрузки: load.txt
  5. Список queryid : sql_list.txt
  6. Тестовые запросы и планы выполнения: plans.txt
  7. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = BufferPin , для эксперимента N(1-4) для сценария2: N.scenario2.BufferPin.txt

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

-19
-20

4.3 Анализ событий ожиданий BufferPin для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid для эксперимента N(1-4) : N.postgres.x.sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = BufferPin , для эксперимента N(1-4) для сценария3: N.scenario3.BufferPin.txt

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

4.4 Анализ событий ожиданий BufferPin для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid для эксперимента N(1-4) : N.postgres.x.sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = BufferPin , для эксперимента N(1-4) для сценария4: N.scenario3.BufferPin.txt

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

5.1 Анализ событий ожиданий Extension для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid для эксперимента N(1-4) : N.postgres.x.sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Extension , для эксперимента N(1-4) для сценария1: N.scenario1.Extension.txt

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

5.2 Анализ событий ожиданий Extension для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid для эксперимента N(1-4) : N.postgres.x.sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Extension , для эксперимента N(1-4) для сценария2: N.scenario1.Extension.txt

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

5.3 Анализ событий ожиданий Extension для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid для эксперимента N(1-4) : N.postgres.x.sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Extension , для эксперимента N(1-4) для сценария3: N.scenario1.Extension.txt

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

5.4 Анализ событий ожиданий Extension для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid для эксперимента N(1-4) : N.postgres.x.sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Extension , для эксперимента N(1-4) для сценария4: N.scenario1.Extension.txt

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

6.1 Анализ событий ожиданий IO для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IO, для эксперимента N(1-4) для сценария1: N.scenario1.IO.txt

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

6.2 Анализ событий ожиданий IO для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IO, для эксперимента N(1-4) для сценария2: N.scenario2.IO.txt

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

6.3 Анализ событий ожиданий IO для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IO, для эксперимента N(1-4) для сценария3: N.scenario3.IO.txt

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

6.4 Анализ событий ожиданий IO для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IO, для эксперимента N(1-4) для сценария4: N.scenario4.IO.txt

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

7.1 Анализ событий ожиданий IPC для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IPC , для эксперимента N(1-4) для сценария1: N.scenario1.IPC.txt

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

7.2 Анализ событий ожиданий IPC для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IPC , для эксперимента N(1-4) для сценария2: N.scenario2.IPC.txt

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

7.3 Анализ событий ожиданий IPC для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IPC , для эксперимента N(1-4) для сценария3: N.scenario3.IPC.txt

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

7.4 Анализ событий ожиданий IPC для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = IPC , для эксперимента N(1-4) для сценария4: N.scenario4.IPC.txt

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

8.1 Анализ событий ожиданий Lock для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Lock , для эксперимента N(1-4) для сценария1: N.scenario1.Lock.txt

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

8.2 Анализ событий ожиданий Lock для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Lock , для эксперимента N(1-4) для сценария2: N.scenario2.Lock.txt

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

8.2 Анализ событий ожиданий Lock для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Lock , для эксперимента N(1-4) для сценария2: N.scenario2.Lock.txt

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

8.3 Анализ событий ожиданий Lock для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Lock , для эксперимента N(1-4) для сценария3: N.scenario3.Lock.txt

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

8.4 Анализ событий ожиданий Lock для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Lock , для эксперимента N(1-4) для сценария4: N.scenario4.Lock.txt

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

9.1 Анализ событий ожиданий LWLock для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = LWLock , для эксперимента N(1-4) для сценария1: N.scenario1.LWLock.txt

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

9.2 Анализ событий ожиданий LWLock для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = LWLock , для эксперимента N(1-4) для сценария2: N.scenario2.LWLock.txt

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

9.3 Анализ событий ожиданий LWLock для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = LWLock , для эксперимента N(1-4) для сценария3: N.scenario3.LWLock.txt

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

9.4 Анализ событий ожиданий LWLock для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = LWLock , для эксперимента N(1-4) для сценария4: N.scenario4.LWLock.txt

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

10.1 Анализ событий ожиданий Timeout для тестового запроса-1

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Timeout , для эксперимента N(1-4) для сценария1: N.scenario1.Timeout.txt

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

10.2 Анализ событий ожиданий Timeout для тестового запроса-2

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Timeout , для эксперимента N(1-4) для сценария2: N.scenario2.Timeout.txt

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

10.3 Анализ событий ожиданий Timeout для тестового запроса-3

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Timeout , для эксперимента N(1-4) для сценария3: N.scenario3.Timeout.txt

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

10.4 Анализ событий ожиданий Timeout для тестового запроса-4

Входные данные:

  1. Схема тестовой базы данных (demo_schema.sql)
  2. Размеры таблиц (tables.txt)
  3. postgres.auto.conf
  4. Таблица нагрузки (load.txt)
  5. Список queryid : sql_list.txt
  6. История событий ожидания СУБД (wait_event) по типу ожидания wait_event_type = Timeout , для эксперимента N(1-4) для сценария4: N.scenario4.Timeout.txt

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

11. Анализ статистических данных по iostat

Входные данные:

  1. Таблица нагрузки (load.txt)
  2. Статистические данные iostat по устройству vdd(/data) для эксперимента N(1-4) : N.linux.x.iostat_vdd_4graph.txt

Используя входные данные, сформируй сравнительный анализ по метрикам iostat.

Выдели ключевые отличия и сформируй итог по серии экспериментов.

12. Анализ статистических данных vmstat

Входные данные:

  1. Таблица нагрузки (load.txt)
  2. Статистические данные vmstat для эксперимента N(1-4) : N.linux.x.vmstat_4graph.txt

Используя входные данные, сформируй сравнительный анализ по метрикам vmstat.

Выдели ключевые отличия и сформируй итог по серии экспериментов.

Ринат Сунгатуллин (с), г. Казань, декабрь 2025, kznalp@yandex.ru