Hi all!, Im pretty new on the zabbix world, since in my new job we use it to monitor linux machines and weblogic servers. The thing is that they didn´t do any maintenance to it from more than 2 years, and in the last week Zabbix started to crash every 6 hours, an lately every 40 minutes. We did some cleanup to the DB, reindexing history and trends tables, this allowed us to move from 803 Gb to 630 Gb, hoping this to be the issue, but the problem still continues. I need a little help about where to start to look to solve this problem, since is in Production environment. Here is my Zabbix config, Postgres config. Any help would be appreciated. Thanks!
Zabbix server is running Yes 152.138.69.138:10051
Number of hosts (enabled/disabled/templates) 1754 1348 / 219 / 187
Number of items (enabled/disabled/not supported) 596159 431978 / 143032 / 21149
Number of triggers (enabled/disabled [problem/ok]) 119941 68353 / 51588 [989 / 67364]
Number of users (online) 30 11
Required server performance, new values per second 2042.06
Zabbix config:
Postgres config:
Again, thanks a lot in advance.!!
Zabbix server is running Yes 152.138.69.138:10051
Number of hosts (enabled/disabled/templates) 1754 1348 / 219 / 187
Number of items (enabled/disabled/not supported) 596159 431978 / 143032 / 21149
Number of triggers (enabled/disabled [problem/ok]) 119941 68353 / 51588 [989 / 67364]
Number of users (online) 30 11
Required server performance, new values per second 2042.06
Zabbix config:
Code:
# This is a configuration file for Zabbix server daemon
# To get more information about Zabbix, visit http://www.zabbix.com
############ GENERAL PARAMETERS #################
### Option: ListenPort
# Listen port for trapper.
#
# Mandatory: no
# Range: 1024-32767
# Default:
#ListenPort=10051
### Option: SourceIP
# Source IP address for outgoing connections.
#
# Mandatory: no
# Default:
# SourceIP=
### Option: LogType
# Specifies where log messages are written to:
# system - syslog
# file - file specified with LogFile parameter
# console - standard output
#
# Mandatory: no
# Default:
# LogType=file
### Option: LogFile
# Log file name for LogType 'file' parameter.
#
# Mandatory: yes, if LogType is set to file, otherwise no
# Default:
# LogFile=
LogFile=/var/log/zabbix/zabbix_server.log
### Option: LogFileSize
# Maximum size of log file in MB.
# 0 - disable automatic log rotation.
#
# Mandatory: no
# Range: 0-1024
# Default:
# LogFileSize=1
LogFileSize=20
### Option: DebugLevel
# Specifies debug level:
# 0 - basic information about starting and stopping of Zabbix processes
# 1 - critical information
# 2 - error information
# 3 - warnings
# 4 - for debugging (produces lots of information)
# 5 - extended debugging (produces even more information)
#
# Mandatory: no
# Range: 0-5
# Default:
DebugLevel=3
### Option: PidFile
# Name of PID file.
#
# Mandatory: no
# Default:
# PidFile=/tmp/zabbix_server.pid
PidFile=/var/run/zabbix/zabbix_server.pid
### Option: SocketDir
# IPC socket directory.
# Directory to store IPC sockets used by internal Zabbix services.
#
# Mandatory: no
# Default:
# SocketDir=/tmp
SocketDir=/var/run/zabbix
### Option: DBHost
# Database host name.
# If set to localhost, socket is used for MySQL.
# If set to empty string, socket is used for PostgreSQL.
#
# Mandatory: no
# Default:
#DBHost=152.139.48.63
DBHost=152.139.49.26
### Option: DBName
# Database name.
#
# Mandatory: yes
# Default:
# DBName=
DBName=zabbix_bch
### Option: DBSchema
# Schema name. Used for IBM DB2 and PostgreSQL.
#
# Mandatory: no
# Default:
# DBSchema=
### Option: DBUser
# Database user.
#
# Mandatory: no
# Default:
# DBUser=
DBUser=zabbixuserbch
### Option: DBPassword
# Database password.
# Comment this line if no password is used.
#
# Mandatory: no
# Default:
DBPassword=Coe2020*
### Option: DBSocket
# Path to MySQL socket.
#
# Mandatory: no
# Default:
# DBSocket=
### Option: DBPort
# Database port when not using local socket.
#
# Mandatory: no
# Range: 1024-65535
# Default:
DBPort=5432
### Option: HistoryStorageURL
# History storage HTTP[S] URL.
#
# Mandatory: no
# Default:
# HistoryStorageURL=
### Option: HistoryStorageTypes
# Comma separated list of value types to be sent to the history storage.
#
# Mandatory: no
# Default:
# HistoryStorageTypes=uint,dbl,str,log,text
### Option: HistoryStorageDateIndex
# Enable preprocessing of history values in history storage to store values in different indices based on date.
# 0 - disable
# 1 - enable
#
# Mandatory: no
# Default:
# HistoryStorageDateIndex=0
### Option: ExportDir
# Directory for real time export of events, history and trends in newline delimited JSON format.
# If set, enables real time export.
#
# Mandatory: no
# Default:
ExportDir=/u/nifi
### Option: ExportFileSize
# Maximum size per export file in bytes.
# Only used for rotation if ExportDir is set.
#
# Mandatory: no
# Range: 1M-1G
# Default:
ExportFileSize=30M
############ ADVANCED PARAMETERS ################
### Option: StartPollers
# Number of pre-forked instances of pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartPollers=300
### Option: StartIPMIPollers
# Number of pre-forked instances of IPMI pollers.
# The IPMI manager process is automatically started when at least one IPMI poller is started.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartIPMIPollers=0
### Option: StartPreprocessors
# Number of pre-forked instances of preprocessing workers.
# The preprocessing manager process is automatically started when preprocessor worker is started.
#
# Mandatory: no
# Range: 1-1000
# Default:
StartPreprocessors=10
### Option: StartPollersUnreachable
# Number of pre-forked instances of pollers for unreachable hosts (including IPMI and Java).
# At least one poller for unreachable hosts must be running if regular, IPMI or Java pollers
# are started.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartPollersUnreachable=200
### Option: StartTrappers
# Number of pre-forked instances of trappers.
# Trappers accept incoming connections from Zabbix sender, active agents and active proxies.
# At least one trapper process must be running to display server availability and view queue
# in the frontend.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartTrappers=200
### Option: StartPingers
# Number of pre-forked instances of ICMP pingers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartPingers=10
### Option: StartDiscoverers
# Number of pre-forked instances of discoverers.
#
# Mandatory: no
# Range: 0-250
# Default:
StartDiscoverers=10
### Option: StartHTTPPollers
# Number of pre-forked instances of HTTP pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartHTTPPollers=50
### Option: StartTimers
# Number of pre-forked instances of timers.
# Timers process maintenance periods.
# Only the first timer process handles host maintenance updates. Problem suppression updates are shared
# between all timers.
#
# Mandatory: no
# Range: 1-1000
# Default:
StartTimers=10
### Option: StartEscalators
# Number of pre-forked instances of escalators.
#
# Mandatory: no
# Range: 0-100
# Default:
# StartEscalators=1
### Option: StartAlerters
# Number of pre-forked instances of alerters.
# Alerters send the notifications created by action operations.
#
# Mandatory: no
# Range: 0-100
# Default:
# StartAlerters=3
### Option: JavaGateway
# IP address (or hostname) of Zabbix Java gateway.
# Only required if Java pollers are started.
#
# Mandatory: no
# Default:
JavaGateway=127.0.0.1
### Option: JavaGatewayPort
# Port that Zabbix Java gateway listens on.
#
# Mandatory: no
# Range: 1024-32767
# Default:
JavaGatewayPort=10052
### Option: StartJavaPollers
# Number of pre-forked instances of Java pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartJavaPollers=300
### Option: StartVMwareCollectors
# Number of pre-forked vmware collector instances.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartVMwareCollectors=0
### Option: VMwareFrequency
# How often Zabbix will connect to VMware service to obtain a new data.
#
# Mandatory: no
# Range: 10-86400
# Default:
# VMwareFrequency=60
### Option: VMwarePerfFrequency
# How often Zabbix will connect to VMware service to obtain performance data.
#
# Mandatory: no
# Range: 10-86400
# Default:
# VMwarePerfFrequency=60
### Option: VMwareCacheSize
# Size of VMware cache, in bytes.
# Shared memory size for storing VMware data.
# Only used if VMware collectors are started.
#
# Mandatory: no
# Range: 256K-2G
# Default:
# VMwareCacheSize=8M
### Option: VMwareTimeout
# Specifies how many seconds vmware collector waits for response from VMware service.
#
# Mandatory: no
# Range: 1-300
# Default:
# VMwareTimeout=10
### Option: SNMPTrapperFile
# Temporary file used for passing data from SNMP trap daemon to the server.
# Must be the same as in zabbix_trap_receiver.pl or SNMPTT configuration file.
#
# Mandatory: no
# Default:
# SNMPTrapperFile=/tmp/zabbix_traps.tmp
SNMPTrapperFile=/var/log/snmptrap/snmptrap.log
### Option: StartSNMPTrapper
# If 1, SNMP trapper process is started.
#
# Mandatory: no
# Range: 0-1
# Default:
# StartSNMPTrapper=0
### Option: ListenIP
# List of comma delimited IP addresses that the trapper should listen on.
# Trapper will listen on all network interfaces if this parameter is missing.
#
# Mandatory: no
# Default:
# ListenIP=0.0.0.0
# ListenIP=127.0.0.1
### Option: HousekeepingFrequency
# How often Zabbix will perform housekeeping procedure (in hours).
# Housekeeping is removing outdated information from the database.
# To prevent Housekeeper from being overloaded, no more than 4 times HousekeepingFrequency
# hours of outdated information are deleted in one housekeeping cycle, for each item.
# To lower load on server startup housekeeping is postponed for 30 minutes after server start.
# With HousekeepingFrequency=0 the housekeeper can be only executed using the runtime control option.
# In this case the period of outdated information deleted in one housekeeping cycle is 4 times the
# period since the last housekeeping cycle, but not less than 4 hours and not greater than 4 days.
#
# Mandatory: no
# Range: 0-24
# Default:
# HousekeepingFrequency=1
### Option: MaxHousekeeperDelete
# The table "housekeeper" contains "tasks" for housekeeping procedure in the format:
# [housekeeperid], [tablename], [field], [value].
# No more than 'MaxHousekeeperDelete' rows (corresponding to [tablename], [field], [value])
# will be deleted per one task in one housekeeping cycle.
# If set to 0 then no limit is used at all. In this case you must know what you are doing!
#
# Mandatory: no
# Range: 0-1000000
# Default:
# MaxHousekeeperDelete=5000
### Option: CacheSize
# Size of configuration cache, in bytes.
# Shared memory size for storing host, item and trigger data.
#
# Mandatory: no
# Range: 128K-8G
# Default:
CacheSize=1G
### Option: CacheUpdateFrequency
# How often Zabbix will perform update of configuration cache, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
CacheUpdateFrequency=120
### Option: StartDBSyncers
# Number of pre-forked instances of DB Syncers.
#
# Mandatory: no
# Range: 1-100
# Default:
StartDBSyncers=100
### Option: HistoryCacheSize
# Size of history cache, in bytes.
# Shared memory size for storing history data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
HistoryCacheSize=1G
### Option: HistoryIndexCacheSize
# Size of history index cache, in bytes.
# Shared memory size for indexing history cache.
#
# Mandatory: no
# Range: 128K-2G
# Default:
HistoryIndexCacheSize=1G
### Option: TrendCacheSize
# Size of trend cache, in bytes.
# Shared memory size for storing trends data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
TrendCacheSize=128M
### Option: ValueCacheSize
# Size of history value cache, in bytes.
# Shared memory size for caching item history data requests.
# Setting to 0 disables value cache.
#
# Mandatory: no
# Range: 0,128K-64G
# Default:
ValueCacheSize=1G
### Option: Timeout
# Specifies how long we wait for agent, SNMP device or external check (in seconds).
#
# Mandatory: no
# Range: 1-30
# Default:
# Timeout=3
Timeout=30
### Option: TrapperTimeout
# Specifies how many seconds trapper may spend processing new data.
#
# Mandatory: no
# Range: 1-300
# Default:
# TrapperTimeout=300
### Option: UnreachablePeriod
# After how many seconds of unreachability treat a host as unavailable.
#
# Mandatory: no
# Range: 1-3600
# Default:
UnreachablePeriod=600
### Option: UnavailableDelay
# How often host is checked for availability during the unavailability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
UnavailableDelay=30
### Option: UnreachableDelay
# How often host is checked for availability during the unreachability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
UnreachableDelay=30
### Option: AlertScriptsPath
# Full path to location of custom alert scripts.
# Default depends on compilation options.
# To see the default path run command "zabbix_server --help".
#
# Mandatory: no
# Default:
# AlertScriptsPath=${datadir}/zabbix/alertscripts
AlertScriptsPath=/usr/lib/zabbix/alertscripts
### Option: ExternalScripts
# Full path to location of external scripts.
# Default depends on compilation options.
# To see the default path run command "zabbix_server --help".
#
# Mandatory: no
# Default:
# ExternalScripts=${datadir}/zabbix/externalscripts
ExternalScripts=/usr/lib/zabbix/externalscripts
### Option: FpingLocation
# Location of fping.
# Make sure that fping binary has root ownership and SUID flag set.
#
# Mandatory: no
# Default:
# FpingLocation=/usr/sbin/fping
### Option: Fping6Location
# Location of fping6.
# Make sure that fping6 binary has root ownership and SUID flag set.
# Make empty if your fping utility is capable to process IPv6 addresses.
#
# Mandatory: no
# Default:
# Fping6Location=/usr/sbin/fping6
### Option: SSHKeyLocation
# Location of public and private keys for SSH checks and actions.
#
# Mandatory: no
# Default:
# SSHKeyLocation=
### Option: LogSlowQueries
# How long a database query may take before being logged (in milliseconds).
# Only works if DebugLevel set to 3, 4 or 5.
# 0 - don't log slow queries.
#
# Mandatory: no
# Range: 1-3600000
# Default:
# LogSlowQueries=0
LogSlowQueries=5000
### Option: TmpDir
# Temporary directory.
#
# Mandatory: no
# Default:
# TmpDir=/tmp
### Option: StartProxyPollers
# Number of pre-forked instances of pollers for passive proxies.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartProxyPollers=1
### Option: ProxyConfigFrequency
# How often Zabbix Server sends configuration data to a Zabbix Proxy in seconds.
# This parameter is used only for proxies in the passive mode.
#
# Mandatory: no
# Range: 1-3600*24*7
# Default:
# ProxyConfigFrequency=3600
### Option: ProxyDataFrequency
# How often Zabbix Server requests history data from a Zabbix Proxy in seconds.
# This parameter is used only for proxies in the passive mode.
#
# Mandatory: no
# Range: 1-3600
# Default:
# ProxyDataFrequency=1
### Option: StartLLDProcessors
# Number of pre-forked instances of low level discovery processors.
#
# Mandatory: no
# Range: 1-100
# Default:
StartLLDProcessors=25
### Option: AllowRoot
# Allow the server to run as 'root'. If disabled and the server is started by 'root', the server
# will try to switch to the user specified by the User configuration option instead.
# Has no effect if started under a regular user.
# 0 - do not allow
# 1 - allow
#
# Mandatory: no
# Default:
# AllowRoot=0
### Option: User
# Drop privileges to a specific, existing user on the system.
# Only has effect if run as 'root' and AllowRoot is disabled.
#
# Mandatory: no
# Default:
# User=zabbix
### Option: Include
# You may include individual files or all files in a directory in the configuration file.
# Installing Zabbix will create include directory in /usr/local/etc, unless modified during the compile time.
#
# Mandatory: no
# Default:
# Include=
# Include=/usr/local/etc/zabbix_server.general.conf
# Include=/usr/local/etc/zabbix_server.conf.d/
# Include=/usr/local/etc/zabbix_server.conf.d/*.conf
### Option: SSLCertLocation
# Location of SSL client certificates.
# This parameter is used only in web monitoring.
# Default depends on compilation options.
# To see the default path run command "zabbix_server --help".
#
# Mandatory: no
# Default:
# SSLCertLocation=${datadir}/zabbix/ssl/certs
### Option: SSLKeyLocation
# Location of private keys for SSL client certificates.
# This parameter is used only in web monitoring.
# Default depends on compilation options.
# To see the default path run command "zabbix_server --help".
#
# Mandatory: no
# Default:
# SSLKeyLocation=${datadir}/zabbix/ssl/keys
### Option: SSLCALocation
# Override the location of certificate authority (CA) files for SSL server certificate verification.
# If not set, system-wide directory will be used.
# This parameter is used only in web monitoring and SMTP authentication.
#
# Mandatory: no
# Default:
# SSLCALocation=
### Option: StatsAllowedIP
# List of comma delimited IP addresses, optionally in CIDR notation, or DNS names of external Zabbix instances.
# Stats request will be accepted only from the addresses listed here. If this parameter is not set no stats requests
# will be accepted.
# If IPv6 support is enabled then '127.0.0.1', '::127.0.0.1', '::ffff:127.0.0.1' are treated equally
# and '::/0' will allow any IPv4 or IPv6 address.
# '0.0.0.0/0' can be used to allow any IPv4 address.
# Example: StatsAllowedIP=127.0.0.1,192.168.1.0/24,::1,2001:db8::/32,zabbix.example.com
#
# Mandatory: no
# Default:
# StatsAllowedIP=
StatsAllowedIP=127.0.0.1
####### LOADABLE MODULES #######
### Option: LoadModulePath
# Full path to location of server modules.
# Default depends on compilation options.
# To see the default path run command "zabbix_server --help".
#
# Mandatory: no
# Default:
# LoadModulePath=${libdir}/modules
### Option: LoadModule
# Module to load at server startup. Modules are used to extend functionality of the server.
# Formats:
# LoadModule=<module.so>
# LoadModule=<path/module.so>
# LoadModule=</abs_path/module.so>
# Either the module must be located in directory specified by LoadModulePath or the path must precede the module name.
# If the preceding path is absolute (starts with '/') then LoadModulePath is ignored.
# It is allowed to include multiple LoadModule parameters.
#
# Mandatory: no
# Default:
# LoadModule=
####### TLS-RELATED PARAMETERS #######
### Option: TLSCAFile
# Full pathname of a file containing the top-level CA(s) certificates for
# peer certificate verification.
#
# Mandatory: no
# Default:
# TLSCAFile=
### Option: TLSCRLFile
# Full pathname of a file containing revoked certificates.
#
# Mandatory: no
# Default:
# TLSCRLFile=
### Option: TLSCertFile
# Full pathname of a file containing the server certificate or certificate chain.
#
# Mandatory: no
# Default:
# TLSCertFile=
### Option: TLSKeyFile
# Full pathname of a file containing the server private key.
#
# Mandatory: no
# Default:
# TLSKeyFile=
Postgres config:
Code:
# ----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, run "pg_ctl reload", or execute # "SELECT pg_reload_conf()". Some parameters, which are marked below, # require a server shutdown and restart to take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # "postgres -c log_connections=on". Some parameters can be changed at run time # with the "SET" SQL command. # # Memory units: kB = kilobytes Time units: ms = milliseconds # MB = megabytes s = seconds # GB = gigabytes min = minutes # TB = terabytes h = hours # d = days #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '' # write an extra PID file # (change requires restart) #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) max_connections = 1600 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) #bonjour = off # advertise server via Bonjour # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) # - Security and Authentication - #authentication_timeout = 1min # 1s-600s #ssl = off #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' #ssl_dh_params_file = '' #ssl_cert_file = 'server.crt' #ssl_key_file = 'server.key' #ssl_ca_file = '' #ssl_crl_file = '' #password_encryption = md5 # md5 or scram-sha-256 #db_user_namespace = off #row_security = on # GSSAPI using Kerberos #krb_server_keyfile = '' #krb_caseins_users = off # - TCP Keepalives - # see "man 7 tcp" for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------------ # - Memory - #shared_buffers = 128MB # min 128kB shared_buffers = 4GB # (change requires restart) huge_pages = try # on, off, or try # (change requires restart) temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Caution: it is not advisable to set max_prepared_transactions nonzero unless # you actively intend to use prepared transactions. work_mem = 8MB # min 64kB maintenance_work_mem = 2280MB # min 1MB #replacement_sort_tuples = 150000 # limits use of replacement selection sort autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB dynamic_shared_memory_type = posix # the default is the first option # supported by the operating system: # posix # sysv # windows # mmap # use none to disable dynamic shared memory # (change requires restart) # - Disk - temp_file_limit = -1 # limits per-process temp file space # in kB, or -1 for no limit # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 # (change requires restart) #shared_preload_libraries = '' # (change requires restart) # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0 # 0-100 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 1-10000 credits # - Background Writer - bgwriter_delay = 200ms # 10-10000ms between rounds bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round #bgwriter_flush_after = 512kB # measured in pages, 0 disables # - Asynchronous Behavior - effective_io_concurrency = 3 # 1-1000; 0 disables prefetching #max_worker_processes = 8 # (change requires restart) #max_parallel_workers_per_gather = 2 # taken from max_parallel_workers #max_parallel_workers = 8 # maximum number of max_worker_processes that # can be used in parallel queries #old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate # (change requires restart) #backend_flush_after = 0 # measured in pages, 0 disables #------------------------------------------------------------------------------ # WRITE AHEAD LOG #------------------------------------------------------------------------------ # - Settings - #wal_level = replica # minimal, replica, or logical # (change requires restart) fsync = on # flush data to disk for crash safety # (turning this off can cause # unrecoverable data corruption) synchronous_commit = off # synchronization level; # off, local, remote_write, remote_apply, or on #wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync (default on Linux) # fsync # fsync_writethrough # open_sync #full_page_writes = on # recover from partial page writes #wal_compression = off # enable compression of full-page writes wal_log_hints = off # also do full page writes of non-critical updates # (change requires restart) wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers # (change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds #wal_writer_flush_after = 1MB # measured in pages, 0 disables commit_delay = 100000 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 # - Checkpoints - checkpoint_timeout = 180s # range 30s-1d #max_wal_size = 1GB #min_wal_size = 80MB checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_flush_after = 256kB # measured in pages, 0 disables checkpoint_warning = 0 # 0 disables # - Archiving - #archive_mode = off # enables archiving; off, on, or always # (change requires restart) #archive_command = '' # command to use to archive a logfile segment # placeholders: %p = path of file to archive # %f = file name only # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' #archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disables #------------------------------------------------------------------------------ # REPLICATION #------------------------------------------------------------------------------ # - Sending Server(s) - # Set these on the master and on any standby that will send replication data. #max_wal_senders = 10 # max number of walsender processes # (change requires restart) wal_keep_segments = 512 # in logfile segments, 16MB each; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables max_replication_slots = 6 # max number of replication slots # (change requires restart) #track_commit_timestamp = off # collect timestamp of transaction commit # (change requires restart) # - Master Server - # These settings are ignored on a standby server. #synchronous_standby_names = '' # standby servers that provide sync rep # method to choose sync standbys, number of sync standbys, # and comma-separated list of application_name # from standby(s); '*' = all #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed # - Standby Servers - # These settings are ignored on a master server. #hot_standby = on # "off" disallows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; # -1 allows indefinite delay #max_standby_streaming_delay = 30s # max delay before canceling queries # when reading streaming WAL; # -1 allows indefinite delay #wal_receiver_status_interval = 10s # send replies at least this often # 0 disables hot_standby_feedback = on # send info from standby to prevent # query conflicts #wal_receiver_timeout = 60s # time that receiver waits for # communication from master # in milliseconds; 0 disables #wal_retrieve_retry_interval = 5s # time to wait before retrying to # retrieve WAL after a failed attempt # - Subscribers - # These settings are ignored on a publisher. #max_logical_replication_workers = 4 # taken from max_worker_processes # (change requires restart) #max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers #------------------------------------------------------------------------------ # QUERY TUNING #------------------------------------------------------------------------------ # - Planner Method Configuration - enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_indexonlyscan = on enable_material = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on # - Planner Cost Constants - #seq_page_cost = 1.0 # measured on an arbitrary scale random_page_cost = 2.0 # same scale as above #cpu_tuple_cost = 0.01 # same scale as above #cpu_index_tuple_cost = 0.005 # same scale as above #cpu_operator_cost = 0.0025 # same scale as above #parallel_tuple_cost = 0.1 # same scale as above #parallel_setup_cost = 1000.0 # same scale as above #min_parallel_table_scan_size = 8MB #min_parallel_index_scan_size = 512kB effective_cache_size = 12GB # - Genetic Query Optimizer - geqo = on geqo_threshold = 12 geqo_effort = 5 # range 1-10 geqo_pool_size = 0 # selects default based on effort geqo_generations = 0 # selects default based on effort geqo_selection_bias = 2.0 # range 1.5-2.0 geqo_seed = 0.0 # range 0.0-1.0 # - Other Planner Options - default_statistics_target = 100 # range 1-10000 constraint_exclusion = partition # on, off, or partition #cursor_tuple_fraction = 0.1 # range 0.0-1.0 #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses #force_parallel_mode = off #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - log_destination = 'stderr' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: log_directory = 'log' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-%a.log' # log file name pattern, # can include strftime() escapes #log_file_mode = 0600 # creation mode for log files, # begin with 0 to use octal notation log_truncate_on_rotation = on # If on, an existing log file with the # same name as the new log file will be # truncated rather than appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. log_rotation_age = 1d # Automatic rotation of logfiles will # happen after that time. 0 disables. log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much log output. # 0 disables. # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' #syslog_sequence_numbers = on #syslog_split_messages = on # This is only relevant when logging to eventlog (win32): # (change requires restart) #event_source = 'PostgreSQL' # - When to Log - #log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #log_min_error_statement = error # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic (effectively off) #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements # and their durations, > 0 logs only # statements running at least this number # of milliseconds # - What to Log - #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on log_checkpoints = on #log_connections = off #log_disconnections = off #log_duration = off #log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off log_line_prefix = '%m [%p] ' # special values: # %a = application name # %u = user name # %d = database name # %r = remote host and port # %h = remote host # %p = process ID # %t = timestamp without milliseconds # %m = timestamp with milliseconds # %n = timestamp with milliseconds (as a Unix epoch) # %i = command tag # %e = SQL state # %c = session ID # %l = session line number # %s = session start timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) # %q = stop here in non-session # processes # %% = '%' # e.g. '<%u%%%d> ' #log_lock_waits = off # log lock waits >= deadlock_timeout #log_statement = 'none' # none, ddl, mod, all #log_replication_commands = off #log_temp_files = -1 # log temporary files equal or larger # than the specified size in kilobytes; # -1 disables, 0 logs all temp files log_timezone = 'America/Santiago' # - Process Title - #cluster_name = '' # added to process titles if nonempty # (change requires restart) #update_process_title = on #------------------------------------------------------------------------------ # RUNTIME STATISTICS #------------------------------------------------------------------------------ # - Query/Index Statistics Collector - #track_activities = on #track_counts = on #track_io_timing = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) #stats_temp_directory = 'pg_stat_tmp' # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off #------------------------------------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 1000 # min number of row updates before # vacuum autovacuum_analyze_threshold = 1000 # min number of row updates before # analyze autovacuum_vacuum_scale_factor = 0.0 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.0 # fraction of table size before analyze autovacuum_freeze_max_age = 100000000 # maximum XID age before forced vacuum # (change requires restart) autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit #------------------------------------------------------------------------------ # CLIENT CONNECTION DEFAULTS #------------------------------------------------------------------------------ # - Statement Behavior - #client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error #search_path = '"$user", public' # schema names #default_tablespace = '' # a tablespace name, '' uses the default #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #default_transaction_deferrable = off #session_replication_role = 'origin' #statement_timeout = 0 # in milliseconds, 0 is disabled #lock_timeout = 0 # in milliseconds, 0 is disabled #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #vacuum_multixact_freeze_min_age = 5000000 #vacuum_multixact_freeze_table_age = 150000000 #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' #gin_fuzzy_search_limit = 0 #gin_pending_list_limit = 4MB # - Locale and Formatting - datestyle = 'iso, mdy' #intervalstyle = 'postgres' timezone = 'America/Santiago' #timezone_abbreviations = 'Default' # Select the set of available time zone # abbreviations. Currently, there are # Default # Australia (historical usage) # India # You can create your own file in # share/timezonesets/. #extra_float_digits = 0 # min -15, max 3 #client_encoding = sql_ascii # actually, defaults to database # encoding # These settings are initialized by initdb, but they can be changed. lc_messages = 'en_US.UTF-8' # locale for system error message # strings 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 configuration for text search default_text_search_config = 'pg_catalog.english' # - Other Defaults - #dynamic_library_path = '$libdir' #local_preload_libraries = '' #session_preload_libraries = '' #------------------------------------------------------------------------------ # LOCK MANAGEMENT #------------------------------------------------------------------------------ #deadlock_timeout = 1s #max_locks_per_transaction = 64 # min 10 # (change requires restart) #max_pred_locks_per_transaction = 64 # min 10 # (change requires restart) #max_pred_locks_per_relation = -2 # negative values mean # (max_pred_locks_per_transaction # / -max_pred_locks_per_relation) - 1 #max_pred_locks_per_page = 2 # min 0 #------------------------------------------------------------------------------ # VERSION/PLATFORM COMPATIBILITY #------------------------------------------------------------------------------ # - Previous PostgreSQL Versions - #array_nulls = on #backslash_quote = safe_encoding # on, off, or safe_encoding #default_with_oids = off #escape_string_warning = on #lo_compat_privileges = off #operator_precedence_warning = off #quote_all_identifiers = off #standard_conforming_strings = on #synchronize_seqscans = on # - Other Platforms and Clients - #transform_null_equals = off #------------------------------------------------------------------------------ # ERROR HANDLING #------------------------------------------------------------------------------ #exit_on_error = off # terminate session on any error? #restart_after_crash = on # reinitialize after backend crash? #data_sync_retry = off # retry or panic on failure to fsync # data? # (change requires restart) #------------------------------------------------------------------------------ # CONFIG FILE INCLUDES #------------------------------------------------------------------------------ # These options allow settings to be loaded from files other than the # default postgresql.conf. Note that these are directives, not variable # assignments, so they can usefully be given more than once. #include_dir = '...' # include files ending in '.conf' from # a directory, e.g., 'conf.d' #include_if_exists = '...' # include file only if it exists #include = '...' # include file #------------------------------------------------------------------------------ # CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ # Add settings for extensions here
Comment