postgres9.6 系统优化配置参数

操作系统：centos7.8

内存：256GB

CPU：64核

cat /etc/sysctl.conf

# For more information, see sysctl.conf(5) and sysctl.d(5).
fs.aio-max-nr = 1048576
fs.file-max = 76724600
#kernel.sem = 4096 2147483647 2147483646 512000
kernel.sem = 4096 2147483647 2147483646 4096
kernel.shmall = 107374182
kernel.shmmax = 274877906944
#kernel.shmmni = 819200
kernel.shmmni = 8192
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
#net.nf_conntrack_max = 1200000
#net.netfilter.nf_conntrack_max = 1200000
vm.dirty_background_bytes = 409600000
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 95
vm.dirty_writeback_centisecs = 100
vm.swappiness = 0
vm.mmap_min_addr = 65536
vm.overcommit_memory = 0
vm.overcommit_ratio = 90
vm.swappiness = 0
vm.zone_reclaim_mode = 0
net.ipv4.ip_local_port_range = 40000 65535
fs.nr_open = 20480000

*******************postgresql.conf*****************************

listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 1500 # (change requires restart)
superuser_reserved_connections = 3 # (change requires restart)
shared_buffers = 64GB # min 128kB
work_mem = 8MB # min 64kB
maintenance_work_mem = 2GB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
effective_io_concurrency = 40 # 1-1000; 0 disables prefetching
max_worker_processes = 128 # (change requires restart)
synchronous_commit = off # synchronization level;
checkpoint_warning = 0 # 0 disables
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_min_duration_statement = 60000 # -1 is disabled, 0 logs all statements
log_error_verbosity = default # terse, default, or verbose messages
log_line_prefix = 'db=%d,client=%h < %m > ' # special values:
log_timezone = 'PRC'
autovacuum = on # Enable autovacuum subprocess? 'on'
autovacuum_max_workers = 4
autovacuum_naptime = 6s # time between autovacuum runs
autovacuum_vacuum_threshold = 500 # min number of row updates before
autovacuum_analyze_threshold = 500 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_io_timing = on
random_page_cost = 1
seq_page_cost = 1
# Add settings for extensions here
####primary and stand_by @bai
wal_level = hot_standby
max_wal_senders = 2
full_page_writes = on
wal_keep_segments = 10000
#wal_keep_segments = 30000
wal_compression = on