PG_EXPECTO: Анализ влияния размера shared_buffers на производительность СУБД PostgreSQL

Глоссарий терминов

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

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Предисловие

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

Задача

Используя классическую задачу о влиянии значения параметра shared_buffers на производительность СУБД, подготовить и протестировать общую методологию проведения экспериментов по анализу производительности СУБД PostgerSQL c использованием нейросети для анализа статистических данных, собранных комплексом pg_expecto в ходе нагрузочного тестирования.

Методология исследования

Тестовая среда, инструменты и конфигурация СУБД:

• СУБД: PostgreSQL 17

• Инструмент нагрузочного тестирования: pg_expecto

• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)

• Условия тестирования: параллельная нагрузка от 5 до 22 сессий по каждому тестовому сценарию.

--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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_airports_data_code_timezone on airports_data  (cost=143.04..145.26 rows=1 width=190) (actual time=2.557..2.559 rows=1 loops=1)
   Index Cond: (airport_code = (InitPlan 1).col1)
   Buffers: shared hit=23
   InitPlan 1
     ->  Limit  (cost=142.75..142.76 rows=1 width=12) (actual time=2.468..2.469 rows=1 loops=1)
           Buffers: shared hit=20
           ->  Sort  (cost=142.75..156.51 rows=5501 width=12) (actual time=2.467..2.467 rows=1 loops=1)
                 Sort Key: (random())
                 Sort Method: top-N heapsort  Memory: 25kB
                 Buffers: shared hit=20
                 ->  Index Only Scan using airports_data_pkey on airports_data airports_data_1  (cost=0.28..115.25 rows=5501 width=12) (actual time=0.127..1.319 rows=5501 loops=1)
                       Heap Fetches: 0
                       Buffers: shared hit=17
 Planning:
   Buffers: shared hit=199
 Planning Time: 1.783 ms
 Execution Time: 2.675 ms
(17 rows)

--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 IN (SELECT airplane_code FROM random_airplanes)
GROUP BY s.fare_conditions
ORDER BY seat_count DESC;

                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23.59..23.60 rows=3 width=24) (actual time=2.231..2.235 rows=3 loops=1)
   Sort Key: (count(*)) DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=23
   CTE random_airplanes
     ->  Limit  (cost=1.29..1.29 rows=1 width=12) (actual time=0.080..0.083 rows=6 loops=1)
           Buffers: shared hit=1
           ->  Sort  (cost=1.29..1.32 rows=10 width=12) (actual time=0.066..0.067 rows=6 loops=1)
                 Sort Key: (random())
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=1
                 ->  Seq Scan on airplanes_data  (cost=0.00..1.12 rows=10 width=12) (actual time=0.051..0.053 rows=10 loops=1)
                       Buffers: shared hit=1
   ->  GroupAggregate  (cost=20.07..22.28 rows=3 width=24) (actual time=1.844..2.187 rows=3 loops=1)
         Group Key: s.fare_conditions
         Buffers: shared hit=20
         ->  Sort  (cost=20.07..20.61 rows=218 width=12) (actual time=1.787..1.877 rows=1294 loops=1)
               Sort Key: s.fare_conditions, s.airplane_code
               Sort Method: quicksort  Memory: 89kB
               Buffers: shared hit=20
               ->  Nested Loop  (cost=0.30..7.41 rows=218 width=12) (actual time=0.191..0.632 rows=1294 loops=1)
                     Buffers: shared hit=14
                     ->  HashAggregate  (cost=0.02..0.03 rows=1 width=16) (actual time=0.114..0.118 rows=6 loops=1)
                           Group Key: random_airplanes.airplane_code
                           Batches: 1  Memory Usage: 24kB
                           Buffers: shared hit=1
                           ->  CTE Scan on random_airplanes  (cost=0.00..0.02 rows=1 width=16) (actual time=0.104..0.107 rows=6 loops=1)
                                 Buffers: shared hit=1
                     ->  Index Only Scan using idx_seats_airplane_code_fare_conditions on seats s  (cost=0.28..5.19 rows=218 width=12) (actual time=0.017..0.050 rows=216 loops=6)
                           Index Cond: (airplane_code = random_airplanes.airplane_code)
                           Heap Fetches: 0
                           Buffers: shared hit=13
 Planning:
   Buffers: shared hit=90
 Planning Time: 0.774 ms
 Execution Time: 2.544 ms
(36 rows)

-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
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..21335.15 rows=711319 width=53) (actual time=0.084..5.599 rows=142 loops=1)
   Buffers: shared hit=91 read=10
   I/O Timings: shared read=5.249
   ->  Index Scan using idx_bookings_book_date on bookings  (cost=0.43..213347.50 rows=7113187 width=53) (actual time=0.081..5.576 rows=142 loops=1)
         Filter: (total_amount > '0'::numeric)
         Buffers: shared hit=91 read=10
         I/O Timings: shared read=5.249
 Planning:
   Buffers: shared hit=72
 Planning Time: 0.646 ms
 Execution Time: 5.642 ms
(11 rows)

--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 random_date
    FROM bookings.routes 
    WHERE validity IS NOT NULL 
      AND upper(validity) > lower(validity) + interval '7 days'
    ORDER BY random() 
    LIMIT 1
)
SELECT r.route_no, 
       r.departure_airport,
       a_dep.city->>'en' as departure_city,
       r.arrival_airport,
       a_arr.city->>'en' as arrival_city,
       r.duration,
       r.days_of_week
FROM bookings.routes r
JOIN bookings.airports_data a_dep ON r.departure_airport = a_dep.airport_code
JOIN bookings.airports_data a_arr ON r.arrival_airport = a_arr.airport_code
CROSS JOIN random_validity_date rvd
WHERE r.departure_airport IN (SELECT airport_code FROM random_departures)
  AND r.validity @> rvd.random_date
  AND array_length(r.days_of_week, 1) > 0
ORDER BY r.departure_airport, r.route_no
LIMIT 300;

                                                                                                                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=707.57..707.58 rows=2 width=133) (actual time=14.544..14.551 rows=0 loops=1)
   Buffers: shared hit=1804
   CTE random_departures
     ->  Limit  (cost=457.01..458.39 rows=550 width=12) (actual time=3.396..3.398 rows=2 loops=1)
           Buffers: shared hit=17
           ->  Sort  (cost=457.01..470.77 rows=5501 width=12) (actual time=3.393..3.394 rows=2 loops=1)
                 Sort Key: (random())
                 Sort Method: top-N heapsort  Memory: 25kB
                 Buffers: shared hit=17
                 ->  Index Only Scan using airports_data_pkey on airports_data  (cost=0.28..115.25 rows=5501 width=12) (actual time=0.055..1.765 rows=5501 loops=1)
                       Heap Fetches: 0
                       Buffers: shared hit=17
   CTE random_validity_date
     ->  Limit  (cost=200.26..200.31 rows=1 width=16) (actual time=6.880..6.883 rows=1 loops=1)
           Buffers: shared hit=37
           ->  Result  (cost=200.26..282.33 rows=1931 width=16) (actual time=6.879..6.881 rows=1 loops=1)
                 Buffers: shared hit=37
                 ->  Sort  (cost=200.26..205.09 rows=1931 width=30) (actual time=6.862..6.862 rows=1 loops=1)
                       Sort Key: (random())
                       Sort Method: top-N heapsort  Memory: 25kB
                       Buffers: shared hit=37
                       ->  Index Only Scan using idx_routes_route_no_validity on routes  (cost=0.28..190.61 rows=1931 width=30) (actual time=0.114..5.148 rows=5792 loops=1)
                             Filter: (upper(validity) > (lower(validity) + '7 days'::interval))
                             Heap Fetches: 0
                             Buffers: shared hit=37
   ->  Sort  (cost=48.88..48.88 rows=2 width=133) (actual time=14.542..14.546 rows=0 loops=1)
         Sort Key: r.departure_airport, r.route_no
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1804
         ->  Hash Semi Join  (cost=19.91..48.86 rows=2 width=133) (actual time=14.512..14.515 rows=0 loops=1)
               Hash Cond: (r.departure_airport = random_departures.airport_code)
               Buffers: shared hit=1804
               ->  Nested Loop  (cost=2.03..30.95 rows=2 width=171) (actual time=7.224..10.962 rows=290 loops=1)
                     Buffers: shared hit=1787
                     ->  Nested Loop  (cost=1.75..30.15 rows=2 width=122) (actual time=7.190..9.174 rows=290 loops=1)
                           Buffers: shared hit=917
                           ->  Nested Loop  (cost=1.47..29.36 rows=2 width=69) (actual time=7.129..7.455 rows=290 loops=1)
                                 Buffers: shared hit=47
                                 ->  CTE Scan on random_validity_date rvd  (cost=0.00..0.02 rows=1 width=8) (actual time=6.883..6.885 rows=1 loops=1)
                                       Buffers: shared hit=37
                                 ->  Bitmap Heap Scan on routes r  (cost=1.47..29.24 rows=10 width=91) (actual time=0.240..0.494 rows=290 loops=1)
                                       Recheck Cond: (validity @> rvd.random_date)
                                       Filter: (array_length(days_of_week, 1) > 0)
                                       Heap Blocks: exact=6
                                       Buffers: shared hit=10
                                       ->  Bitmap Index Scan on idx_routes_validity  (cost=0.00..1.47 rows=29 width=0) (actual time=0.184..0.184 rows=290 loops=1)
                                             Index Cond: (validity @> rvd.random_date)
                                             Buffers: shared hit=4
                           ->  Index Scan using airports_data_pkey on airports_data a_dep  (cost=0.28..0.40 rows=1 width=53) (actual time=0.005..0.005 rows=1 loops=290)
                                 Index Cond: (airport_code = r.departure_airport)
                                 Buffers: shared hit=870
                     ->  Index Scan using airports_data_pkey on airports_data a_arr  (cost=0.28..0.40 rows=1 width=53) (actual time=0.005..0.005 rows=1 loops=290)
                           Index Cond: (airport_code = r.arrival_airport)
                           Buffers: shared hit=870
               ->  Hash  (cost=11.00..11.00 rows=550 width=16) (actual time=3.423..3.423 rows=2 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=17
                     ->  CTE Scan on random_departures  (cost=0.00..11.00 rows=550 width=16) (actual time=3.398..3.401 rows=2 loops=1)
                           Buffers: shared hit=17
 Planning:
   Buffers: shared hit=216
 Planning Time: 3.506 ms
 Execution Time: 15.095 ms
(63 rows)

QUERYID | SQL TEXT |
-1247703524680257765|select scenario2()
-2326249669894930556|select scenario3()
3162304388436908605|select scenario4()
-3890986969840966328|select scenario1()

     relname     | n_live_tup 
-----------------+------------
 airplanes_data  |         10
 seats           |       1741
 airports_data   |       5501
 airplanes_data  |         10
 airports_data   |       5501
 bookings        |    7113192
 routes          |       5792
 seats           |       1741
 tickets         |   15575748
 flights         |     108435
 segments        |   20598447
 boarding_passes |   19870557
(12 rows)

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Максимальная нагрузка
finish_load = 20
# Тестовая БД
testdb = demo
# Веса сценариев
scenario1 = 1.0
scenario2 = 1.0
scenario3 = 1.0
scenario4 = 1.0

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
track_io_timing = 'on'
listen_addresses = '0.0.0.0'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'postgresql-%u.log'
log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| '
log_truncate_on_rotation = 'on'
log_checkpoints = 'on'
archive_mode = 'on'
archive_command = 'true'
archive_timeout = '30min'
checkpoint_timeout = '15min'
checkpoint_warning = '60'
checkpoint_completion_target = '0.9'
min_wal_size = '2GB'
max_wal_size = '8GB'
synchronous_commit = 'on'
wal_compression = 'on'
random_page_cost = '1.1'
effective_io_concurrency = '300'
wal_sender_timeout = '0'
autovacuum_naptime = '1s'
autovacuum_vacuum_scale_factor = '0.01'
autovacuum_analyze_scale_factor = '0.005'
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_max_workers = '4'
autovacuum_work_mem = '256MB'
vacuum_cost_limit = '4000'
bgwriter_delay = '10ms'
bgwriter_lru_multiplier = '4'
bgwriter_lru_maxpages = '400'
max_locks_per_transaction = '256'
max_pred_locks_per_transaction = '256'
temp_buffers = '14MB'
maintenance_work_mem = '479MB'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
max_parallel_maintenance_workers = '4'
hash_mem_multiplier = '2'
autovacuum_vacuum_insert_scale_factor = '0.01'
shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling'
commit_delay = '1000'
log_autovacuum_min_duration = '0'
wipe_file_on_delete = 'on'
wipe_heaptuple_on_delete = 'on'
wipe_mem_on_free = 'on'
wipe_memctx_on_free = 'on'
wipe_xlog_on_free = 'on'
log_connections = 'on'
log_disconnections = 'on'
pg_stat_statements.track = 'all'
max_connections = '1000'
work_mem = '180MB'
max_parallel_workers_per_gather = '1'
max_parallel_workers = '16'
max_worker_processes = '16'
shared_buffers = '2GB'
effective_cache_size = '6GB'

ИЗМЕНЕНИЕ НАГРУЗКИ В ХОДЕ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ 
№	LOAD
1	5
2	5
3	5
4	5
5	5
6	5
7	5
8	5
9	5
10	5
11	5
12	8
13	8
14	8
15	8
16	8
17	8
18	8
19	8
20	8
21	8
22	8
23	8
24	8
25	8
26	8
27	8
28	8
29	8
30	8
31	8
32	8
33	8
34	9
35	9
36	9
37	9
38	9
39	9
40	9
41	9
42	9
43	9
44	9
45	10
46	10
47	10
48	10
49	10
50	10
51	10
52	10
53	10
54	10
55	10
56	12
57	12
58	12
59	12
60	12
61	12
62	12
63	12
64	12
65	12
66	12
67	13
68	13
69	13
70	13
71	13
72	13
73	13
74	13
75	13
76	13
77	13
78	15
79	15
80	15
81	15
82	15
83	15
84	15
85	15
86	15
87	15
88	15
89	18
90	18
91	18
92	18
93	18
94	18
95	18
96	18
97	18
98	18
99	18
100	22
101	22
102	22
103	22
104	22
105	22
106	22
107	22
108	22

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

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

Схема тестовой базы данных : demo_schema.sql

Размеры таблиц : tables.txt

Конфигурация СУБД : postgres.auto.conf

Таблица нагрузки: load.txt

Список queryid : sql_list.txt

Тестовые запросы и планы выполнения: plans.txt

Используя предоставленные входные данные, сформулируй гипотезу о влиянии увеличения параметра shared_buffers в ходе нагрузочного тестирования по сравнению базовым значением shared_buffers=2GB (Эксперимент-1) в экспериментах : shared_buffers=4GB (Эксперимент-2), shared_buffers=5GB (Эксперимент-3), shared_buffers=6GB (Эксперимент-4). Уточни ключевые признаки значений ожиданий СУБД wait_event_type и метрик производительности инфраструктуры vmstat, iostat, указывающие на слишком большой размер shared_buffers в ходе данных экспериментов.

Гипотеза о влиянии увеличения shared_buffers

Ожидаемый положительный эффект:
При увеличении shared_buffers с 2GB до 4-6GB в данных экспериментах ожидается:

1. Уменьшение физических чтений с диска, особенно для больших таблиц (bookings, tickets, segments, boarding_passes с 7-20 млн строк)

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

3. Снижение нагрузки на подсистему ввода-вывода

Однако существует риск избыточного размера shared_buffers, о котором свидетельствуют следующие признаки:

1. Признаки в wait_event_type СУБД:

• Увеличение событий BufferContentLock, LWLock - конкуренция за доступ к буферам

• Рост IO событий, связанных с WALWrite - из-за увеличения объема dirty buffers, требующих записи

• Появление CheckpointWrite и CheckpointSync чаще - контрольные точки становятся более продолжительными

• Увеличение BgWriter активности - фоновый писатель не справляется с очисткой буферов

2. Признаки в метриках инфраструктуры:

vmstat:

• Высокий si (swap in) и so (swap out) при недостатке оперативной памяти

• Увеличение cs (context switches) из-за борьбы за память

• Рост us (user CPU) без соответствующего улучшения throughput

iostat:

• Увеличение await времени для устройств хранения, хотя %util может снижаться

• Рост svctm (service time) из-за более долгой записи на контрольных точках

• Непропорциональное увеличение записи (wkB/s) относительно чтения (rkB/s)

3. Ключевые индикаторы проблем с oversized shared_buffers:

Для текущей конфигурации (effective_cache_size=6GB, данные ~20GB+):

• Эксперимент-4 (6GB shared_buffers) - риск наибольших проблем, так как:

  • shared_buffers приближается к effective_cache_size

  • Может вызывать вытеснение файлового кэша ОС

  • Увеличивает нагрузку на управление памятью

Специфические для нагрузки признаки:

1. Отсутствие улучшения после 4GB - если производительность стагнирует или ухудшается при переходе с 4GB на 5-6GB

2. Увеличение checkpoint_segments/wal в логах PostgreSQL

3. Рост buffers_backend_fsync в pg_stat_bgwriter

4. Увеличение разницы между buffers_alloc и buffers_backend

Оптимальная точка вероятно находится между 4-5GB для данной нагрузки, учитывая:

• Общий объем данных ~20GB+

• Наличие memory-intensive операций (сортировки, hash-агрегации)

• Одновременное выполнение 4 различных сценариев запросов

Результаты экспериментов по итогам нагрузочных тестирований

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

SPEED-4GB / SPEED-2GB

Среднее снижение операционной скорости в эксперименте "shared_buffers=4GB" по сравнению с базовым значением "shared_buffers=2GB" составило 0.54%

SPEED-5GB / SPEED-2GB

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

SPEED-6GB / SPEED-2GB

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

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

WAITINGS-4GB / WAITINGS-2GB

Среднее снижение количества ожиданий СУБД в эксперименте "shared_buffers=4GB" по сравнению с базовым значением "shared_buffers=2GB" составило 0.22%

WAITINGS-5GB / WAITINGS-2GB

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

WAITINGS-6GB / WAITINGS-2GB

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

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

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

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

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

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

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

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

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

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

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

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

Схема тестовой базы данных : demo_schema.sql

Размеры таблиц : tables.txt

Конфигурация СУБД : postgres.auto.conf

Таблица нагрузки: load.txt

Список queryid : sql_list.txt

Тестовые запросы и планы выполнения: plans.txt

Регрессионный и корреляционный анализ производительности и ожиданий СУБД для эксперимента N(1-4) : N.postgres.1.cluster_report_meta.txt

Статистические данные по производительности и ожиданиям СУБД (wait_event_type) для эксперимента N(1-4) : N.postgres.1.cluster_report_4graph.txt

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

Сравнительный анализ экспериментов 1-4

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. Сравнительный анализ производительности (SPEED)

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

• Все эксперименты демонстрируют стабильное снижение производительности с течением времени (отрицательные углы наклона)

• Наивысшая пиковая производительность в эксперименте 1 (2.967.383)

• Наименьшая минимальная производительность в эксперименте 2 (1.181.182)

• Коэффициент детерминации R² ≈ 0,95-0,96 указывает на сильную линейную зависимость

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

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

• Эксперимент 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 (базовый):

   • Наименьшие максимальные ожидания

   • Отсутствие IO/IPC ожиданий

   • Вероятно, выполнялись простые SELECT запросы

2. Эксперимент 2 (максимальная нагрузка):

   • Наибольшие общие ожидания (183)

   • Пиковые значения IO/IPC

   • Вероятно, выполнялись запросы с сортировкой больших объемов

3. Эксперимент 3 (сбалансированный):

   • Промежуточные значения ожиданий

   • Незначительные IO/IPC

   • Стабильная работа

4. Эксперимент 4 (IO-нагруженный):

   • Устойчивые IO ожидания (корреляция 0,93)

   • Вероятно, выполнялись JOIN запросы с большими хеш-таблицами

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

1. Производительность стабильно снижается с увеличением нагрузки во всех экспериментах

2. TIMEOUT и LWLOCK - доминирующие типы ожиданий во всех сценариях

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

4. Конфигурация СУБД адекватна нагрузке, но требует мониторинга при пиковых значениях

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

2. Анализ производительности подсистемы IO

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

Таблица нагрузки: load.txt

Статистические данные ioatst для эксперимента N(1-4) : N.linux.x.iostat_vdd_4graph.txt

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

1. Общие наблюдения

Во всех четырёх экспериментах зафиксированы идентичные значения метрик iostat для устройства vdd:

• w/s (количество операций записи в секунду): 0,0000

• wrqm/s (количество слиянных операций записи в секунду): 0,0000

• %wrqm (процент слияний операций записи): 49,0000

• w_await (среднее время ожидания записи в мс): 2,0000

• wareq_sz (средний размер запроса записи в секторах): 10,0000

• %util (процент использования диска): 0,0000

• f/s (количество операций flush в секунду): 0,0000

• f_await (среднее время ожидания flush в мс): 0,0000

...

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

1. Диск vdd не был нагружен в ходе всех четырёх экспериментов.

2. Метрики iostat неизменны во времени и между экспериментами.

3. Нагрузочное тестирование, судя по данным, не затрагивало устройство vdd. Возможно:

   • Нагрузка направлялась на другие диски или ресурсы (CPU, память, сеть).

   • Данные собраны не с того устройства или в момент простоя системы.

   • Конфигурация теста не подразумевала операций ввода-вывода на vdd.

2. Анализ производительности инфраструктуры (vmstat)

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

Таблица нагрузки: load.txt

Тестовые запросы и планы выполнения: plans.txt

Статистические данные vmstat для эксперимента N(1-4) : N.linux.x.vmstat_4graph.txt

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

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

Память (Memory):

• Memory_swpd (виртуальная память):

  • Эксперимент 1: стабильно 190 MB

  • Эксперимент 2: стабильно 59 MB

  • Эксперимент 3: стабильно 59 MB

  • Эксперимент 4: стабильно 58 MB

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

• Memory_free (свободная память):

  • Начальный уровень свободной памяти отличается:

    • Эксп 1: ~160-200 MB

    • Эксп 2: ~2000-2044 MB

    • Эксп 3: ~1600-1613 MB

    • Эксп 4: ~200-250 MB

  • Вывод: Эксперименты проводились в разных условиях загрузки системы, что влияет на сравнение.

Процессы (Procs_r):

• Количество запущенных процессов:

  • Эксп 1: увеличивается с 21 до 50 (рост в 2.38 раза)

  • Эксп 2: увеличивается с 21 до 49 (рост в 2.33 раза)

  • Эксп 3: увеличивается с 21 до 51 (рост в 2.43 раза)

  • Эксп 4: увеличивается с 21 до 51 (рост в 2.43 раза)

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

CPU (процессор):

• cpu_us (пользовательское время CPU):

  • Во всех экспериментах: стабильно 89-95%

  • Незначительный рост к концу тестов

• cpu_sy (системное время CPU):

  • Эксп 1: снижается с 10% до 5%

  • Эксп 2: снижается с 10% до 4%

  • Эксп 3: снижается с 10% до 4%

  • Эксп 4: снижается с 10% до 5%

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

Ввод-вывод (I/O):

• io_bo (блоки ввода-вывода):

  • Эксп 1: 53-56 (стабильно)

  • Эксп 2: 48-62 (более волатильно)

  • Эксп 3: 45-51 (наиболее стабильно)

  • Эксп 4: 51-58 (умеренная волатильность)

Системные метрики:

• system_in (прерывания):

  • Значительно снижается во всех экспериментах к концу тестирования

  • Наиболее резкое падение в эксперименте 4

• system_cs (переключения контекста):

  • Стабильное снижение во всех экспериментах

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

2. Связь с нагрузочным тестированием

Из файла load.txt видно, что нагрузка увеличивалась постепенно:

• С 5 до 22 единиц нагрузки

• 110 измерений соответствуют 110 записям в каждом эксперименте

Зависимости, наблюдаемые при увеличении нагрузки:

1. Количество процессов (procs_r) линейно возрастает с увеличением нагрузки

2. Свободная память (memory_free) уменьшается при увеличении нагрузки

3. CPU пользовательское время остается стабильно высоким

4. CPU системное время снижается при увеличении нагрузки

3. Анализ тестовых запросов (plans.txt)

Из планов выполнения видно 4 сценария:

1. SELECT BY PK - быстрый запрос (2.675 ms)

2. GROUP BY - умеренная сложность (2.544 ms)

3. ORDER BY - самый медленный (5.642 ms)

4. JOIN - сложный запрос с CTE (15.095 ms)

Связь с экспериментами:
Каждый эксперимент, соответствует одному из сценариев, что объясняет разницу в метриках:

• Эксперимент 4 (с JOIN) показывает наибольшее изменение метрик

• Эксперимент 1 (с SELECT BY PK) показывает наименьшую волатильность

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

Ключевые выводы:

1. Производительность системы:

   • Система демонстрирует стабильность даже при 22 единицах нагрузки

   • CPU остается основным узким местом (высокий % пользовательского времени)

2. Память:

   • Значительная разница в использовании свопа между экспериментами

   • Наиболее эффективное использование памяти в экспериментах 2 и 3

3. Масштабируемость:

   • Система адекватно реагирует на увеличение нагрузки

   • Наблюдается линейный рост процессов при увеличении нагрузки

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

   • Оптимизировать запросы типа JOIN (самые ресурсоемкие)

   • Рассмотреть увеличение оперативной памяти для снижения использования свопа

   • Мониторить системные прерывания и переключения контекста как индикаторы проблем

Сравнительная таблица итоговых показателей:

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

Общий итог по анализу результатов экспериментов

1. Основные выводы о влиянии параметра shared_buffers

• Незначительное влияние на производительность: Увеличение shared_buffers с 2GB до 4-6GB не привело к существенному изменению операционной скорости. Различия составляют всего 0,54-0,62%, что находится в пределах статистической погрешности.

• Парадоксальный эффект на ожидания: При увеличении shared_buffers до 5GB и 6GB наблюдается рост количества ожиданий СУБД на 5,75% и 6,50% соответственно, что указывает на возможные блокировки и конкуренцию за ресурсы.

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

• Стабильность методологии: Все четыре эксперимента проведены по единой методологии с одинаковой продолжительностью (109 минут) и схемой нагрузки (плавный рост от 5 до 22 сессий).

• Воспроизводимость результатов: Эксперименты демонстрируют схожую динамику снижения производительности с течением времени (углы наклона -44,27...-44,45) при высоких коэффициентах детерминации (R²=0,95-0,96).

3. Ключевые факторы, влияющие на производительность

3.1 Типы выполняемых запросов

• SELECT BY PK: Минимальные IO ожидания, стабильная производительность

• GROUP BY/ORDER BY: Умеренная нагрузка, возможны временные операции

• JOIN запросы: Наибольшая ресурсоемкость, вызывают устойчивые IO ожидания

3.2 Доминирующие типы ожиданий

• TIMEOUT: Преобладающий тип (80-90% от общего числа ожиданий)

• LWLOCK (Lightweight locks): Значительная доля в структуре ожиданий

• IO ожидания: Появляются только при выполнении запросов с большими объемами данных

4. Анализ подсистем

4.1 Дисковая подсистема (iostat)

• Отсутствие нагрузки на устройство vdd: Все эксперименты показали нулевую утилизацию диска, что может указывать на:

  • Направление нагрузки на другие дисковые устройства

  • Достаточность оперативной памяти для кэширования

  • Особенности конфигурации тестовой среды

4.2 Системные ресурсы (vmstat)

• CPU как узкое место: Стабильно высокое пользовательское время CPU (89-95%)

• Проблемы с управлением памятью: Значительные различия в использовании свопа между экспериментами (от 58MB до 190MB)

• Адекватное масштабирование: Линейный рост процессов при увеличении нагрузки

Общий вывод

Серия экспериментов подтвердила стабильность работы PostgreSQL под различными типами нагрузки. Увеличение параметра shared_buffers не оказало существенного влияния на производительность в данной тестовой конфигурации, что может указывать на:

• Достаточность базового значения (2GB) для данной нагрузки

• Преобладание других факторов, ограничивающих производительность (CPU, типы запросов, блокировки)

Наиболее значимыми факторами производительности оказались:

1. Типы выполняемых запросов (особенно JOIN-операции)

2. Управление блокировками (LWLOCK)

3. Эффективность использования оперативной памяти

4. Нагрузка на CPU

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

Анализ причин ошибки первоначального прогноза

Отчет по анализу причин ошибочности начальной гипотезы и итогам экспериментов

1. Анализ причин ошибочности начальной гипотезы

Начальная гипотеза предполагала, что увеличение shared_buffers с 2 ГБ до 4–6 ГБ приведёт к значимому улучшению производительности за счёт снижения физических чтений с диска и уменьшения нагрузки на подсистему ввода-вывода. Однако результаты экспериментов не подтвердили эти ожидания, что обусловлено следующими причинами:

• Дисковая подсистема не являлась узким местом
  Данные iostat показали нулевую утилизацию диска (vdd), что указывает на отсутствие значимой нагрузки на хранилище. В таких условиях увеличение кэширования в shared_buffers не могло дать заметного прироста производительности.

• Доминирующим ресурсом оказался CPU
  Показатели vmstat выявили стабильно высокое пользовательское время CPU (89–95%), что свидетельствует о процессорном ограничении. Увеличение shared_buffers не снижает нагрузку на CPU, а в некоторых случаях даже усиливает конкуренцию за ресурсы.

• Рост числа ожиданий и блокировок
  При увеличении shared_buffers до 5–6 ГБ наблюдался рост событий LWLOCK и общего количества ожиданий СУБД (до 6,5%). Это подтверждает гипотезу о рисках избыточного размера буфера, но в данном случае негативный эффект проявился раньше ожидаемого.

• Неучтённые факторы нагрузки
  Характер запросов (особенно JOIN-операции и агрегации) и структура данных (~20 ГБ) оказались более значимыми, чем размер shared_buffers. Оптимизация выполнения запросов и управление блокировками имели большее влияние на производительность.

• Эффективность базовой конфигурации
  Значение shared_buffers=2 ГБ оказалось достаточным для данной тестовой нагрузки, что указывает на сбалансированность базовой конфигурации.

2. Общие итоги серии экспериментов

• Минимальное влияние shared_buffers
  Увеличение параметра не привело к статистически значимому улучшению производительности. Различия между экспериментами составили менее 1%, что находится в пределах погрешности измерений.

• Стабильность и воспроизводимость результатов
  Все эксперименты выполнены по единой методологии, показали схожую динамику снижения производительности во времени и высокую согласованность данных (R² = 0,95–0,96).

• Ключевые факторы производительности

  1. Типы запросов — JOIN-операции вызывали наибольшую нагрузку и устойчивые IO-ожидания.

  2. Блокировки — события LWLOCK и TIMEOUT составляли основную долю ожиданий.

  3. Использование CPU — процессорное время было основным ограничивающим ресурсом.

  4. Управление памятью — значительные колебания в использовании свопа указывают на потенциальные проблемы с распределением памяти.

• Отсутствие нагрузки на диск
  Нулевая утилизация диска vdd во всех экспериментах позволяет сделать вывод о достаточности оперативной памяти для кэширования или особенностях тестовой среды.

3. Рекомендации по дальнейшим исследованиям

1. Сфокусироваться на оптимизации запросов, особенно с JOIN и агрегациями.

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

3. Проанализировать использование памяти ОС и взаимодействие shared_buffers с файловым кэшем.

4. Рассмотреть возможность увеличения вычислительных ресурсов (CPU) или оптимизации рабочих нагрузок.

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

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

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

Отчёт составлен на основе анализа предоставленных документов «Начальная гипотеза нейросети.txt» и «Общий итог.txt».