一、 查看参数设置
- show 参数名
- select * from pg_settings where name='参数名';
- 查看 postgresql.conf文件
二、 参数是否可修改
pg中主要可优化参数都位于postgresql.conf文件中，其中参数是否可修改可通过pg_settings的context字段查询。
postgres=# select distinct context from pg_settings;
context
-------------------
postmaster
superuser-backend
user
internal
backend
sighup
superuser
(7 rows)
postgres=# select name,context from pg_settings limit 5;
name | context
-------------------------+------------
allow_system_table_mods | postmaster
application_name | user
archive_command | sighup
archive_mode | postmaster
archive_timeout | sighup
(5 rows)
context含义
- internal：编译期间确定的数据库内部设置，除非重新编译PG否则无法更改
- postmaster：服务器重启后才生效（所有共享内存设置都属此类）
- signup：向服务器发送HUP信号会使服务器重新加载postgresql.conf，加载后对参数的设置会立即生效
- backend：类似signup，但参数的更改只对新连接生效，不影响现有会话
- superuser：修改后即生效，但只能由拥有超级用户权限的用户修改
- user：修改仅影响当前会话，类似alter session
- superuser-backend：可以在postgresql.conf中直接更改，无需重启生效。此值不能在启动会话后进行更改，另外仅超级用户（如postgres用户）可更改这些设置
三、 重新加载配置文件
1. pg_reload_conf（超级用户）
select pg_reload_conf();2. kill命令发送HUP信号
ps -ef|grep "postgres -D" #记下进程号
kill -HUP 进程号3. pg_ctl命令触发服务器进程SIGHUP信号
pg_ctl reload
#或者
service postgresql reload4. 查看最后load的时间
select pg_postmaster_start_time();
四、 主要参数详解
1. 数据库连接
参数名 | 含义 | 建议值 | 备注 |
listen_addresses | 指定服务器在哪些 TCP/IP 地址上监听客户端连接。 | '0.0.0.0'或者 '*' | 即允许从哪些客户端连到该pg数据库 |
max_connections | 最大连接数 | 不要超过实际需求值太多 | 过大的值可能浪费内存、加大连接开销 |
superuser_reserved_connections | 保留给超级管理员的连接数 | 10 | |
ssl | 是否开启ssl连接 | off | |
authentication_timeout | 客户端认证最长时间 | 默认1分钟 | |
2. 内存参数
参数名 | 含义 | 默认值 | 建议值 | 备注 |
shared_buffers | 共享内存 | 128M | os内存的25% | 详细参考后文。调整该参数时，适当调整max_wal_size |
huge_pages | 启用大页 | try | try | 建议shared_buffer内存大于64G（总内存大于256G）时打开。 |
temp_buffers | 每个连接可用的最大临时缓冲区大小 | 8M | 8M | 会话的本地缓冲区，只用于访问临时表。 |
max_prepared_transactions | 可以同时处于“prepared”状态的事务的最大数目 | 0 | 0 | 0为禁用，若启用，该值的最小设置为max_connections的大小 |
work_mem | 用于排序和hash table的内存 | 4M | 4M | 排序操作：ORDER BY, DISTINCT,merge joins。 Hash table主要操作：hash joins, hash-based aggregation。 如果该值过小，会使用临时文件。 |
maintenance_work_mem | 用于维护操作的内存 | 64M | 64M | 主要操作为：vacuum、create index、alter table add foreign key 该值应该大于work_mem，在vacuuming和restoring database dump时，适当提高该参数有加速作用。同时需要注意和autovacuum_max_workers的配合使用。 |
replacement_sort_tuples | 当要被排序的元组数比这个数字小时，排序将会使用replacement selection而不是quicksort方法来产生其第一个输出 | 150000 | 150000 | 默认为150000元组，在内存受限的环境中可能会有用 |
autovacuum_work_mem | 指定每个自动清理worker进程能使用的最大内存量 | -1 | -1 | 默认值为 -1，表示转而使用maintenance_work_mem的值做为vacuuming使用的最大内存大小 |
3. 日志记录
参数名 | 含义 | 建议值 | 备注 |
where to log | | | |
logging_collector | 打开日志收集 | logging_collector = on | 打开该参数下面参数才有意义 |
log_destination | 记录日志的格式 | log_destination = 'stderr,csvlog' | 需要logging_collector=on
取值范围： stderr, csvlog, syslog, and eventlog |
log_directory | 日志写入的目录 | log_directory = '/data/PRD/postgres/10.7/pg5432/data/pg_log' | 日志存放路径，可以写绝对路径，或者相对路径(相对于PGDATA) |
log_filename | 日志文件名格式 | log_filename = 'postgresql-%Y-%m-%d.log' | |
log_truncate_on_rotation | 若为on，当日志名重复时会覆盖掉原文件，否则为追加 | log_truncate_on_rotation = on | |
log_file_mode | 日志文件权限 | log_file_mode = 0640 | |
log_rotation_age | 日志切分时间间隔 | log_rotation_age = 1d | 当未达log_rotation_size大小时，一天生成一个 |
log_rotation_size | 日志最大大小，大于该值会切分 | log_rotation_size = 100MB |
|
| | | |
when to log | | | |
log_min_error_statement | 日志记录级别 | log_min_error_statement = waring | 可选值：debug5,debug4,debug3,debug2,debug1, info,notice,warning,error,log,fatal,panic (effectively off) |
log_min_duration_statement | 记录慢查询 | log_min_duration_statement = 60000 | 单位为ms，-1不记录，0记录所有 |
| | | |
what to log | | | |
debug_print_parse | 打印sql解析树 | off | |
debug_print_rewritten | 打印重写的sql | off | |
debug_print_plan | 打印sql执行计划 | off | |
debug_pretty_print | 缩进以上三种日志，使其更易读 | off | |
log_checkpoints | 记录Checkpoint | on | |
log_connections | 记录客户端连接 | on | |
log_disconnections | 记录客户端断开连接 | off | |
log_duration | 记录所有语句的执行时间 | off |
|
log_hostname | 是否记录客户端主机名 | off | 默认只记录客户端IP，不记录hostname |
log_line_prefix | 日志记录格式，将指定信息打印到日志每一行 | log_line_prefix = '%t:%r:%u@%d:[%p]: ' | %a = application name |
log_lock_waits | session等待锁时间超过deadlock_timeout时，记录在日志中 | on |
|
log_statement | 日志记录的语句类型 | log_statement = 'ddl' | none：不记录任何语句信息 ddl：只记录ddl mod：记录ddl+dml和COPY FROM、PREPARE、EXECUTE、EXPLAIN ANALYZE语句 all：记录所有语句 |
log_replication_commands | 是否记录replication command | off |
|
log_temp_files | 记录tempflie名字及大小。 | (-1,0,num) | -1不记录，0记录temp文件删除，>0 表示记录tempfile size大于这个值(KB) |
log_timezone | 日志显示时区 | log_timezone = 'Asia/Shanghai' |
|
4. vacuum
参数名 | 含义 | 建议值 | 备注 |
autovacuum | 是否启用自动清理子进程 | on | 需要track_counts=on |
log_autovacuum_min_duration | | -1 | |
autovacuum_max_workers | | 3 | |
autovacuum_naptime | | 1min | |
autovacuum_vacuum_threshold | | 50 | |
autovacuum_analyze_threshold | | 50 | |
autovacuum_vacuum_scale_factor | | 0.2 | |
autovacuum_analyze_scale_factor | | 0.1 | |
autovacuum_freeze_max_age | | 200000000 | |
autovacuum_multixact_freeze_max_age | | 400000000 | |
autovacuum_vacuum_cost_delay | | 20ms | |
autovacuum_vacuum_cost_limit | | -1 | |
vacuum_cost_delay |
| 0 | vacuuming达到cost值时的休眠时间。如果vacuum压力比较大，可以设置成10ms（20ms），避免对系统造成太大影响。 |
vacuum_cost_page_hit | | 1 | |
vacuum_cost_page_miss | | 10 | |
vacuum_cost_page_dirty | | 20 | |
vacuum_cost_limit | | 200 | |
5. 检查点
| checkpoint触发条件 | | | | checkpoint所做操作 |
| 1，执行checkpoint命令。 | | | | 1，在shared buffer中确认所有的dirty blocks。 |
| 参数名称 | 含义 | 默认值 | 建议值 | 备注 |
关键参数 | checkpoint_timeout | 自上次检查点后，经过checkpoint_timeout指定时间时，自动执行检查点 | 5min | 15-30 min，1h也可 | 单位为秒，有效值为30秒到1天 |
| max_wal_size | WAL日志大小达到max_wal_size时，自动执行检查点 | 1GB | | 1，select pg_current_wal_insert_lsn(); |
| min_wal_size | 只要WAL磁盘使用率低于这个设置，旧的WAL文件总数被回收，以供将来检查点使用。而不是删除。 这可以用来确保预留足够的WAL空间处理WAL使用中的峰值，比如当运行大批量工作时。 | 80MB | | 最小checkpoint wal size大小。 |
| checkpoint_completion_target | 每次checkpoint的数据会在多长时间内写完 | 0.5 | 0.9 | 时间为 checkpoint_completion_target * checkpoint_timeout |
| checkpoint_warning | 系统默认值是30秒，如果checkpoints的实际发生间隔小于该参数，将会在server log中写入写入一条相关信息。如果出现该告警，可能需要提高max_wal_size。 | | |
|
6. wal
参数名 | 默认值 | 建议值 | 备注 |
wal_level | replica | | minial：只记录数据库crash，immediate shutdown恢复所需要的日志。 |
fsync | on | on | 确保更新实际写入磁盘，关闭fsync（同时关闭full_page_writes）可以得到性能的提升，但是不安全。 |
synchronous_commit | on | on | 提交事务是否需等待其把wal写入磁盘后再返回成功 |
wal_sync_method | Linux：fdatasync | | 指定向磁盘强制更新wal日志数据的方法，可选值为open_datasync/fdatasync/fsync/fsync_writethrough/open_sync |
full_page_writes | on | on | 全页写，解决页断裂问题。 |
wal_compression | off | on | wal日志压缩，节省磁盘空间 |
wal_buffers | 16M | | shared memory的一部分，用来缓存wal data未写入磁盘部分。 |
wal_writer_delay | 200ms | | WAL writer进程的睡眠时间，进程每次在完成写事务日志的任务后，会等待wal_writer_delay时间，然后将新产生的事务日志从缓冲区写到WAL文件中。 如果时间过长可能造成WAL buffer的内存不足，数据丢失的危险；过短则WAL会不断写入，对磁盘IO也是很大考验 |
wal_writer_flush_after | 1M | | wal write的字节数超过配置的阈值时，触发fsync，设为0表示关闭 |
commit_delay | 0（无延迟） | | 至少有commit_siblings个并发事务时，该事务提交后，wal日志将延迟commit_delay时间后再写入磁盘。 非0值的影响： |
commit_siblings | 5 | | 延迟提交wal日志的最小并发事务数，决定参数commit_delay是否生效。 假设值是5，表示数据库中正在执行的事务数大于或等于5，该事务提交后，wal日志将会存入wal buffer中，延迟commit_delay时间后再写入磁盘。 如果数据库中正在执行的事务数小于5，这个事务提交后将wal日志直接写入磁盘。 |
四、 实际参数文件内容
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#------------------------------------------------------------------------------
# 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 = '/data/PRD/postgres/10.7/pg5432/data' # use data in another directory
# (change requires restart)
hba_file = '/data/PRD/postgres/10.7/pg5432/data/pg_hba.conf' # host-based authentication file
# (change requires restart)
ident_file = '/data/PRD/postgres/10.7/pg5432/data/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 = '0.0.0.0' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 1000 # (change requires restart)
superuser_reserved_connections = 10 # (change requires restart)
unix_socket_directories = '/tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
unix_socket_permissions = 0770 # begin with 0 to use octal notation
# - Security and Authentication -
authentication_timeout = 1min # 1s-600s
ssl = off
tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB # min 128kB
# (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 = 4MB # min 64kB
#maintenance_work_mem = 64MB # 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 = 'pg_stat_statements,auto_explain' # (change requires restart)
#------------------------------------------------------------------------------
# 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 = local # 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
# (change requires restart)
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
# - Checkpoints -
#checkpoint_timeout = 5min # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB # measured in pages, 0 disables
checkpoint_warning = 10800s # 0 disables
# - Archiving -
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'test ! -f /data/PRD/postgres/10.7/pg5432/pg_archlog/%f && cp %p /data/PRD/postgres/10.7/pg5432/pg_archlog/%f' # 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 = 5 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables
#max_replication_slots = 10 # max number of replication slots
# (change requires restart)
#track_commit_timestamp = off # collect timestamp of transaction commit
# (change requires restart)
# - Standby Servers -
# These settings are ignored on a master server.
hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr,csvlog' # csvlog是pg_rman需要
# 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 = '/data/PRD/postgres/10.7/pg5432/data/pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%d.log' # log file name pattern,
# can include strftime() escapes
log_file_mode = 0640 # 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 = 100MB # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
log_min_duration_statement = 60000 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
log_checkpoints = on
log_connections = on
log_line_prefix = '%t:%r:%u@%d:[%p]: '
log_lock_waits = on # log lock waits >= deadlock_timeout
log_timezone = 'Asia/Shanghai'
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on
autovacuum_work_mem = 3GB
autovacuum_max_workers = 10
autovacuum_naptime = 1s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001
autovacuum_vacuum_cost_delay = 0
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'PRC'
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
# - pg_stat_statements auto_explain -
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = true
pg_stat_statements.save = true
auto_explain.log_min_duration = 30s
# - AUDIT DDL -
log_statement=ddl
