Тестовый стенд

CentOS 7.2.1511, PG 9.6.6-1.1С, Intel E5-2670 v2 @ 2.50GHz (10 VCPU), 32 Gb RAM

 

postgresql.conf

------------------------------------------------------------------------------------------------

DateStyle = 'ISO, DMY'
TimeZone = 'W-SU'

default_text_search_config = 'pg_catalog.russian'

lc_messages = 'C'
lc_monetary = 'ru_RU.UTF8'
lc_numeric = 'ru_RU.UTF8'
lc_time = 'ru_RU.UTF8'

listen_addresses = '0.0.0.0'

log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_size = '0'
log_timezone = 'W-SU'
log_truncate_on_rotation = 'on'
logging_collector = 'on'

shared_preload_libraries = 'online_analyze, plantuner, auto_explain'

online_analyze.enable = off
online_analyze.verbose = off
online_analyze.table_type = 'temporary'
online_analyze.scale_factor = 0.3
online_analyze.min_interval = 10000
online_analyze.threshold = 500
online_analyze.local_tracking = on

cpu_operator_cost = 0.0005

plantuner.fix_empty_table = on

autovacuum_naptime = '20' # s
#autovacuum_analyze_threshold = 20

bgwriter_delay = '10' # ms
bgwriter_lru_maxpages = '800'
bgwriter_lru_multiplier = '8'

# storage specific
effective_io_concurrency = '2'
random_page_cost = '2'

checkpoint_completion_target = 0.9
#checkpoint_segments = '128'
max_connections = '1000'

synchronous_commit = 'off'
commit_delay = '1000'

# 1C specific
escape_string_warning = 'off'
standard_conforming_strings = 'off'
max_locks_per_transaction = '256'
work_mem = 65536

# pgbadger
log_directory = '/var/log/pgsql'
log_filename = 'postgresql.log'
log_min_duration_statement = 0s
log_autovacuum_min_duration = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

# backup
max_wal_senders = 4
wal_level = hot_standby
wal_keep_segments = 1024

------------------------------------------------------------------------------------------------

postgresql.auto.conf

------------------------------------------------------------------------------------------------

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
effective_cache_size = '12288MB'
work_mem = '32MB'
shared_buffers = '12288MB'
maintenance_work_mem = '512MB'
temp_buffers = '32MB'
temp_tablespaces = 'user_temp'
max_files_per_process = '24576'
autovacuum_max_workers = '8'
autovacuum_analyze_scale_factor = '0.01'
autovacuum_vacuum_scale_factor = '0.02'
random_page_cost = '1.5'
log_statement = 'none'
------------------------------------------------------------------------------------------------