Начало

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

Задача

Провести экспериментальную проверку рекомендаций по настройке конфигурационных параметров СУБД PostgreSQL.

PGTune - поможет рассчитать конфигурацию для PostgreSQL на основе максимальной производительности для данной конфигурации оборудования

Рекомендация PgTune

Набор конфигурационных параметров №3

ALTER SYSTEM SET max_connections = '200';

ALTER SYSTEM SET shared_buffers = '2GB';

ALTER SYSTEM SET effective_cache_size = '6GB';

ALTER SYSTEM SET maintenance_work_mem = '512MB';

ALTER SYSTEM SET checkpoint_completion_target = '0.9';

ALTER SYSTEM SET wal_buffers = '16MB';

ALTER SYSTEM SET default_statistics_target = '100';

ALTER SYSTEM SET random_page_cost = '1.1';

ALTER SYSTEM SET effective_io_concurrency = '200';

ALTER SYSTEM SET work_mem = '2621kB';

ALTER SYSTEM SET huge_pages = 'off';

ALTER SYSTEM SET min_wal_size = '2GB';

ALTER SYSTEM SET max_wal_size = '8GB';

ALTER SYSTEM SET max_worker_processes = '8';

ALTER SYSTEM SET max_parallel_workers_per_gather = '4';

ALTER SYSTEM SET max_parallel_workers = '8';

ALTER SYSTEM SET max_parallel_maintenance_workers = '4';

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

Продолжение и подробности : PG_HAZEL : Сравнение рекомендаций по конфигурации PostgreSQL

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

stress_report.sh 'device-list'

'device-list' : список устройств . Например 'vdb vdc'.

Результат:

Текстовые файлы для последующего импорта в Excel .

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

Продолжение и подробности : PG_HAZEL : Сводный отчет по результатам нагрузочного тестирования СУБД PostgreSQL

Задача

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

Примеры свечных графиков для разных результатов нагрузочного тестирования

Вечерняя звезда

Утренняя звезда

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

Применение технического анализа для PostgreSQL

Задача

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

Виртуальная машина

• CPU = 8

• RAM = 8GB

• Red OS Murom 7.3

• PostgreSQL 17

Сценарий тестирования

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

Нагрузка

Эксперимент-1 : набор конфигурационных параметров №1

wipe_file_on_delete = 'off'
wipe_xlog_on_free = 'off'
wipe_heaptuple_on_delete = 'off'
wipe_memctx_on_free = 'off'
wipe_mem_on_free = 'off'
track_io_timing = 'on'
listen_addresses = '0.0.0.0'
max_connections = '100'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'name-%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'
synchronous_commit = 'on'
wal_compression = 'on'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
shared_preload_libraries = 'pg_wait_sampling, pgpro_stats'
max_wal_size = '4GB'
min_wal_size = '1GB'
effective_cache_size = '6GB'
work_mem = '24MB'
temp_buffers = '48MB'
random_page_cost = '1.1'
effective_io_concurrency = '200'
commit_delay = '0'
shared_buffers = '4GB'
log_autovacuum_min_duration = '0'
log_connections = 'on'
log_disconnections = 'on'

Эксперимент-2 : набор конфигурационных параметров №2

wipe_file_on_delete = 'off'
wipe_xlog_on_free = 'off'
wipe_heaptuple_on_delete = 'off'
wipe_memctx_on_free = 'off'
wipe_mem_on_free = 'off'
track_io_timing = 'on'
listen_addresses = '0.0.0.0'
#max_connections = '200'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'name-%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'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
shared_preload_libraries = 'pg_wait_sampling, pgpro_stats'
commit_delay = '0'
log_autovacuum_min_duration = '0'
log_connections = 'on'
log_disconnections = 'on'

##########################################################
wal_compression = lz4
jit = on
client_connection_check_interval = 3s
default_toast_compression = pglz
enable_async_append = on
autovacuum_vacuum_insert_threshold = 1596
autovacuum_vacuum_insert_scale_factor = 0.01
logical_decoding_work_mem = 64MB
maintenance_io_concurrency = 128
wal_keep_size = 1506MB
hash_mem_multiplier = 1.2
max_parallel_maintenance_workers = 4
max_parallel_workers = 4
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2
autovacuum = on
autovacuum_max_workers = 4
autovacuum_work_mem = 189MB
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 1596
autovacuum_analyze_threshold = 798
autovacuum_vacuum_scale_factor = 0.001
autovacuum_analyze_scale_factor = 0.0007
autovacuum_vacuum_cost_limit = 2188
vacuum_cost_limit = 8000
autovacuum_vacuum_cost_delay = 10ms
vacuum_cost_delay = 10ms
autovacuum_freeze_max_age = 500000000
autovacuum_multixact_freeze_max_age = 800000000
shared_buffers = 1779MB
max_connections = 200
max_files_per_process = 1391
superuser_reserved_connections = 4
work_mem = 35MB
temp_buffers = 3990kB
maintenance_work_mem = 196MB
huge_pages = try
fsync = on
wal_level = replica
synchronous_commit = on
full_page_writes = on
wal_buffers = 23MB
wal_writer_delay = 100ms
wal_writer_flush_after = 3050kB
min_wal_size = 1010MB
max_wal_size = 2021MB
max_replication_slots = 0
max_wal_senders = 0
wal_sender_timeout = 0
wal_log_hints = off
hot_standby = off
wal_receiver_timeout = 0
max_standby_streaming_delay = -1
wal_receiver_status_interval = 0
hot_standby_feedback = off
checkpoint_timeout = 15min
checkpoint_warning = 30s
checkpoint_completion_target = 0.8
commit_delay = 0
commit_siblings = 0
bgwriter_delay = 54ms
bgwriter_lru_maxpages = 515
bgwriter_lru_multiplier = 7.0
effective_cache_size = 5081MB
cpu_operator_cost = 0.0025
default_statistics_target = 500
random_page_cost = 1.1
seq_page_cost = 1
join_collapse_limit = 8
from_collapse_limit = 8
geqo = on
geqo_threshold = 12
effective_io_concurrency = 128
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_locks_per_transaction = 190
max_pred_locks_per_transaction = 190
statement_timeout = 86400000
idle_in_transaction_session_timeout = 86400000
##########################################################

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

Среднее снижение производительности СУБД в эксперименте-2 составило 52.55%

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

Среднее увеличение ожиданий СУБД в эксперименте-2 составило 22.56%

Продолжение и подробности : PG_HAZEL : Сравнительный анализ результатов нагрузочного тестирования СУБД

Задача

Оценить влияние повышенной утилизации CPU и нагрузки на RAM на производительность СУБД и метрики ОС.

Виртуальная машина 12

• CPU = 8

• RAM = 8GB

• Red OS Murom 7.3

• PostgreSQL 17

Сценарий тестирования-1

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

Сценарий тестирования-3

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

4. CPU + RAM Load : 5% нагрузки

Нагрузка

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

Среднее снижение производительности СУБД в эксперименте-3 составило 27.8%

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

Среднее увеличение ожиданий СУБД в эксперименте-3 составило 17.9%

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

Среднее увеличение ожиданий IO в эксперименте-3 составило 16.82%

Ожидания IPC

Среднее увеличение ожиданий IPC в эксперименте-3 составило 47 523%

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

Среднее уменьшение ожиданий Lock в эксперименте-3 составило 49.10%

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

Среднее увеличение ожиданий LWLock в эксперименте-3 составило 89.10%

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

Среднее увеличение ожиданий Timeout в эксперименте-3 составило 108.60%

Подробности

PG_HAZEL : Сценарий нагрузочного тестирования "HighLoad". Часть 1 - СУБД.

Чек-лист IO - без изменений

Чек-лист CPU - ALARM

Чек-лист RAM - без изменений

Подробности

PG_HAZEL : Сценарий нагрузочного тестирования "HighLoad". Часть 2 - ОС.

Продолжение

PG_HAZEL : Сценарий нагрузочного тестирования "HighLoad" - для слабой СУБД и ВМ.

Задача

Установить причину неудачного нагрузочного тестирования СУБД по сценарию повышенной нагрузки на CPU.

Виртуальная машина 06

• CPU = 2

• RAM = 2GB

• Astra Linux 1.7

• PostgreSQL 15

Сценарий тестирования-1

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

Сценарий тестирования-2

1. Select only : 50% нагрузки

2. Select + Update : 30% нагрузки

3. Insert only : 15% нагрузки

4. CPU Load : 5% нагрузки

Нагрузка

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

Отсутствуют данные по нагрузке 39 - 115 .

Чек-лист IO (сценарий-1 и сценарий-2)

Чек-лист CPU (сценарий-1 и сценарий-2)

Чек-лист RAM (сценарий-1 и сценарий-2)

Причина ошибки сбора данных при нагрузочном тестировании по сценарию-2

Свопинг в ходе нагрузочного тестирования по сценарию-2.

📊 1. Механизм возникновения свопинга при нехватке памяти

• При увеличении числа подключений к СУБД (например, с 5 до 115) каждый новый процесс требует выделения памяти для своих операций. Если физической памяти (RAM) недостаточно, система начинает использовать swap-пространство на диске для перемещения неактивных страниц памяти.

• При конфигурации 2 GB RAM и интенсивной нагрузке на CPU процессы СУБД могут быстро исчерпать доступную оперативную память. Это активирует механизм подкачки, при котором демон kswapd начинает активно перемещать данные между RAM и диском, что дополнительно нагружает CPU.

⚙️ 2. Влияние свопинга на CPU и общую производительность

• Процесс свопинга требует значительных вычислительных ресурсов. При активном использовании swap-пространства нагрузка на CPU может достигать 90-100%, причем большая часть этого времени тратится на системные (kernel) операции.

• Это связано с тем, что ядро Linux должно управлять страницами памяти, определять, какие данные перемещать в swap, и обрабатывать дисковые операции ввода-вывода. В результате нагрузка на CPU возрастает даже при том, что основная задача свопинга — разгрузить оперативную память.

3. Экспоненциальный рост нагрузки и его последствия

• Экспоненциальное увеличение числа соединений (с 5 до 115) приводит к нелинейному росту потребления ресурсов. Каждое новое соединение требует памяти для выполнения запросов, хранения временных данных и управления транзакциями.

• При 2 GB RAM такой рост может быстро исчерпать доступную память. Например, если каждое соединение потребляет даже небольшой объем памяти (например, 20-50 MB), то при 115 соединениях общее потребление может превысить 2 GB, что активирует свопинг.

💎 Заключение

При конфигурации CPU=2 ядра и RAM=2 GB экспоненциальный рост нагрузки до 115 соединений с высокой вероятностью приведет к свопингу, что вызовет дополнительную нагрузку на CPU и может значительно снизить производительность СУБД.

Задача

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

Методика сбора и анализа статистических данных показателей ОС

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

Инцидент

2.1 cluster - производительность СУБД

Результаты отчета:

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

2. Линия регрессии производительности и ожиданий СУБД.

3. Абсолютные значения события ожидания

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

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

События ожидания - IPC

События ожидания - LWLock

2.2 correlation - корреляция ОС + vmstat

Результаты отчета:

1. Корреляция ожиданий типа IO и метрик vmstat

2.3.1 stats - корреляция vmstat + iostat (файловая система /data)

Результаты отчета:

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

2. Корреляция метрик vmstat и iostat

2.3.2 stats - корреляция vmstat + iostat (файловая система /wal)

Результаты отчета:

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

2. Корреляция метрик vmstat и iostat

2.4 check_io - чек-лист IO

Результаты отчета:

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

2. Состояние IO

2.5 check_cpu - чек-лист CPU(32)

Результаты отчета:

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

2. Состояние CPU

2.6 check_ram - чек-лист RAM(64GB)

Результаты отчета:

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

2. Состояние RAM