Задача
Сравнить влияние на производительность СУБД разных наборов конфигурационных параметров СУБД.
Виртуальная машина
- CPU = 8
- RAM = 8GB
- Red OS Murom 7.3
- PostgreSQL 17
Сценарий тестирования
- Select only : 50% нагрузки
- Select + Update : 30% нагрузки
- 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%
Ожидания типа IO
Среднее увеличение ожиданий IO в эксперименте-2 составило 22.76%
Ожидания IPC
Среднее увеличение ожиданий IPC в эксперименте-2 составило 1703.70%
Ожидания типа Lock
Среднее увеличение ожиданий Lock в эксперименте-2 составило 16.76%
Ожидания типа LWLock
Среднее уменьшение ожиданий LWLock в эксперименте-2 составило 35.69%
Ожидания типа Timeout
Среднее уменьшение ожиданий Timeout в эксперименте-2 составило 19.76%
Итог
Для данной виртуальной машины , для тестового характера нагрузки - Набор №1 является более оптимальным для производительности, по сравнению с Набором №2 .