一、操作系统 /etc/sysctl.conf 文件追加如下内容:
(sysctl -p 使配置生效)
kernel.shmmni = 4096 kernel.sem = 50100 64128000 50100 1280 fs.file-max = 7672460 net.core.rmem_default = 1048576 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 net.core.netdev_max_backlog = 10000 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65000 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.tcp_timestamps = 0 net.ipv4.tcp_keepalive_time=72 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_intvl = 7 vm.overcommit_memory = 0 vm.swappiness=0 vm.dirty_background_bytes=102400000 vm.dirty_bytes=102400000
/etc/security/limits.conf 文件追加如下内容:
soft nofile 131072 hard nofile 131072 soft nproc 131072 hard nproc 131072 soft core unlimited hard core unlimited soft memlock 50000000 hard memlock 50000000 二、数据库配置 listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 600 # (change requires restart) unix_socket_directories = '.' # comma-separated list of directories shared_buffers = 11581MB # min 128kB maintenance_work_mem = 2048MB # min 1MB dynamic_shared_memory_type = posix # the default is the first option shared_preload_libraries = 'pg_stat_statements,pg_pathman' # (change requires restart) vacuum_cost_limit = 10000 # 1-10000 credits max_worker_processes = 8 # (change requires restart) max_parallel_workers_per_gather = 5 # taken from max_worker_processes wal_level = logical # minimal, replica, or logical synchronous_commit = off # synchronization level; wal_writer_delay = 500ms # 1-10000 milliseconds max_wal_size = 4GB min_wal_size = 1GB checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 checkpoint_flush_after = 256kB # 0 disables, archive_mode = on # enables archiving; off, on, or always archive_command = '/bin/date' # command to use to archive a logfile segment max_wal_senders = 13 # max number of walsender processes wal_keep_segments = 256 # in logfile segments, 16MB each; 0 disables hot_standby = on # "on" allows queries during recovery random_page_cost = 4 # same scale as above 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 = 10MB # Automatic rotation of logfiles will log_min_duration_statement = 100ms # -1 is disabled, 0 logs all statements log_timezone = 'PRC' autovacuum_naptime = 30s # time between autovacuum runs autovacuum_vacuum_scale_factor = 0.02 # fraction of table size before vacuum temp_tablespaces = 'tbs_zabbix' # a list of tablespace names, '' uses idle_in_transaction_session_timeout = 7200000 # in milliseconds, 0 is disabled datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'en_US.utf8' # locale for system error message lc_monetary = 'en_US.utf8' # locale for monetary formatting lc_numeric = 'en_US.utf8' # locale for number formatting lc_time = 'en_US.utf8' # locale for time formatting default_text_search_config = 'pg_catalog.english' pg_stat_statements.max = 1000 pg_stat_statements.track = all auth_delay.milliseconds = 5000