PostgreSQL 数据库性能 参数优化

1 数据库优化参数

1.1 listen_addresses = 'localhost‘

监听地址，ipv4地址可以配置’0.0.0.0’，也可以配置为‘*’

1.2 port = 5432

监听端口，数据库服务器的防火墙可以考虑只开启这个端口。

1.3 max_connections = 100

最大连接数，参考应用使用情况，不建议超过shared_buffers*250，假设平均每个连接消耗4MB的内存（work_mem、syscache等），如果还需要更多的连接，可以考虑使用连接池。

1.4 superuser_reserved_connections = 3

为超级用户保留多少个连接，可设置为10，避免普通用户连接满了之后无法登录超级用户处理问题。

1.5 会话超时设置

#tcp_keepalives_idle = 0
60
#tcp_keepalives_interval = 0
10
#tcp_keepalives_count = 0
10

如果数据库空闲一段时间会断开，可能是网路中存在会话超时的设备，可以设置为60s检测连接的间隔。

1.6 shared_buffers = 128MB

建议¼物理内存。

1.7 huge_pages = try

try，启动时会尝试使用，如果没有足够大页，则不会使用。Pg并不推荐盲目使用大页，如果连接非常多，又不能使用连接池，并且shared_buffers特别大时，可以考虑使用大页

1.8 work_mem = 4MB

8MB，排序、hash连接、hash聚合等操作会用到work_mem，该内存参数在使用时会为每个进程分配内存空间，并且一条sql根据执行计划中的节点是可以使用多倍work_mem的，所以不建议设置的特别大，推荐为8MB，如果需要更大，建议在会话中设置。

1.9 maintenance_work_mem = 64MB

2GB，创建索引时使用的内存空间，能够提高创建索引速度

1.10 autovacuum_work_mem = -1

-1，等同于maintenance_work_mem，这部分内存用于记录垃圾tupleid，当扫描到的垃圾tuleid占满整个内存，那么会停止扫描表，开始index扫描，扫描index时，通过内存中记录的tupleid来进行匹配

1.11 temp_file_limit = -1

如果需要限制临时文件使用量，可以设置，可以避免一些异常的递归调用，无限使用临时文件等

1.12 max_files_per_process = 1000

一般默认即可，参数为每个进程打开文件的限制，针对于表、索引特别多（几十万）并且还存在同时访问的

1.13 死亡元组清理参数

#vacuum_cost_delay = 0
#vacuum_cost_page_hit = 1		
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200

1、vacuum delay与limit配合使用，当系统io比较好的情况下，delay可以保持0，这样可以避免垃圾回收任务周期长导致的膨胀。
2、delay为0时，limit的设置是无效的。
3、Limit设置的大一些，也能够保障垃圾快速回收。
4、但对于cpu、io较差的情况，delay为0，或者limit过大易造成性能抖动。

1.14 max_worker_processes = 8

256，parallel worker、wal sender，user动态创建的进程都属于worker processesor 所以要足够大

1.15 max_parallel_maintenance_workers = 2

如需使用并行创建索引时设置，但一般都是线下建索引，对速度要求并不太高

1.16 max_parallel_workers_per_gather = 2

0，并行查询时的并行度。这里并行查询的配置推荐的是默认关闭，原因为并行查询适用于从数据量庞大的表中查出较小的结果集的情况，如果结果集较大，开启并行可能反而会影响查询效率，并行查询也会有放弃索引的情况，导致数据库cpu较高

1.17 parallel_leader_participation = on

Leader是否与work process一起参与并行计算，on时并行度默认+1。

1.18 max_parallel_workers = 8

并行查询的最高限制，要小于max_work_process。

1.19 old_snapshot_threshold = -1

设置old_snapshot_threshold参数，强制删除为过老的事务快照保留的dead元组。这会导致长事务读取已被删除tuple时出错。
1、old_snapshot_threshold不会影响更新事务和隔离级别为RR只读事务。
2、old_snapshot_threshold参数也不能在线修改。
3、如果已经设置了old_snapshot_threshold但又需要运行更长的RR只读事务或单个大的只读SQL，可以临时在备机上设置max_standby_streaming_delay = -1，然后在备机执行长事务(会带来主备延迟)。

1.20 wal_level = replica

需要流复制时设置为replica，需要逻辑复制时设置为logical。

1.21 fsync = on

控制日志是否先写入磁盘，on为先写入，表示更新数据写入磁盘时系统必须等待wal日志的写入完成，如果设置为off会提高性能，但无法保证数据库崩溃后最近的事务能得到恢复，也可能会出现数据准确性的问题。

1.22 synchronous_commit = on

同步提交参数, 控制事务提交后返回客户端是否成功的策略，
1、为on时，会当wal日志真正刷新到磁盘永久存储后才会返回客户端事务已提交成功；
2、为off时，写到缓存中就会向客户端返回提交成功，延迟写入磁盘，延迟的时间为最大3倍的wal_writer_delay参数(默认200ms)的时间，即最大有可能丢失600ms的事务（在wal buffer中），但不会影响数据库的正常启动，所以若磁盘io较差，并且为高并发、小事务的系统可以考虑关闭，以提高性能。

1.23 full_page_writes = on

如果底层存储可以保证为原子写，可以设置为off。

1.24 wal_compression = off

如果当全页写成为io瓶颈时，可以设置为on。

1.25 wal_log_hints = off

如果使用pg_rewind，需要设置为on。

1.26 wal_buffers = -1

默认-1会根据shared_buffers自动调整。

1.27 wal_writer_delay = 200ms

wal writer进程的间歇时间，过大的话，可能会造成wal buffer不足，过小的话wal会不断写入，可能会有io瓶颈

1.28 commit_delay = 0

至少有commit_siblings个并发事务时，该事务提交后，wal日志将延迟commit_delay时间后再写入磁盘。可以合并其他事务进行组提交，所以当有大量事务的时候会延迟，而如果事务很稀少就不会再被延迟了。非0值的影响：减少IO，提高性能：事务执行commit后不会立即写入磁盘，而存放在WAL buffer中，崩溃数据面临着丢失的危险，可能引起WAL buffer内存不足，尤其是提交事务较多的高峰期

1.29 commit_siblings = 5

延迟提交wal日志的最小并发事务数，决定参数commit_delay是否生效。假设值是5，表示数据库中正在执行的事务数大于或等于5，该事务提交后，wal日志将会存入wal buffer中，延迟commit_delay时间后再写入磁盘。如果数据库中正在执行的事务数小于5，这个事务提交后将wal日志直接写入磁盘。

1.30 checkpoint_timeout = 5min

30min，不建议频繁做检查点，因为会造成很多的full page write，但检查点越长，崩溃后恢复的耗时就越长，需要同时综合考虑。

1.31 max_wal_size = 1GB

shared_buffers*2，且最好不要超过存储空间/10，即当shared_buffers较大时，max_wal_size不易过大。

1.32 min_wal_size = 80MB

shared_buffers/2

1.33 checkpoint_completion_target = 0.5

硬盘较好时，可以设置为0.2，可以让检查点快速结束，恢复时也可以快速达到一致，如果有hot standby节点可以设置为0.9，避免写高峰时check point的冲击。

1.34 checkpoint_warning = 30s

当检查点间隔小于这个值时，日志中会报警，此时应考虑调整max_wal_size的值。

1.35 archive_mode = off

on，建议默认打开，因为修改后需要重启实例，打开后，一个WAL文件写满后，会在pg_wal/archive_status目录中创建xxxxxx.ready的文件，归档命令archive_command正常结束后，会清除这个状态文件

1.36 archive_command = '‘

test ! -f /data/pgdata/arch/%f && cp %p /data/pgdata/arch/%f，设置为/bin/true可以认为是不开启归档

1.37 archive_timeout = 0

控制事务日志周期性的切换到下一个wal段，可以理解为就是周期性的归档

1.38 max_wal_senders = 10

同时允许几个流复制协议的连接，根据实际需求设定，可以设置一个默认值例如64

1.39 wal_keep_segments = 0

5000，保留wal段的数量，在主备环境下可以防止过早的清除wal而引起备库恢复中断，可以设置为一个较大值，比如5000

1.40 wal_sender_timeout = 60s

中断那些停止活动超过指定时间的复制连接，默认即可

1.41 max_replication_slots = 10

根据实际情况设置需要创建多少replication slot，使用slot，可以保证流复制下游没有接收的WAL会在当前节点永久保留。所以必须留意下游的接收情况，否则可能导致WAL膨胀，快速占满磁盘空间，建议大于等于max_wal_senders

1.42 track_commit_timestamp = off

记录事务提交的时间，一般不会有这个需求，保持默认即可

1.43 synchronous_standby_names = '‘

一般在有多个备库存在时，可以考虑使用同步多副本模式，比如一主两备的情况下，我们可以配置为ANY 1 (*)，这样即使挂掉一个备库，复制仍可以继续，如果挂掉两个备库，则主库hang住

1.44 vacuum_defer_cleanup_age = 0

设置主库垃圾回收的延迟，例如配置为1000，表示垃圾版本将延迟1000个事务再被回收，设置非0值很可能会导致cpu空转，一般默认0即可

1.45 max_standby_archive_delay = 30s

当standby的archive replay与standby的用户SQL请求发生冲突时，在打断SQL前，允许用户SQL最长的执行时间. 打断SQL后，需要等STANDBY APPLY所有archive restore command 的WAL文件，APPLY才允许被继续打断。

1.46 max_standby_streaming_delay = 30s

与查询在取消前可以运行的最大时间长度不同，而是一旦从主服务器接收到WAL数据就允许应用WAL数据的最大总时间

1.47 wal_receiver_status_interval = 10s

指定 WAL 接收的最小频率，处理备库上发送有关复制进程信息到主或上游 待机状态，在那里可以使用可见的 pg_stat_replication 视图。待机会报告它已写入 的最后一个事务日志的位置，最后一个位置已经刷新到磁盘中，并已申请最后位置。此参数值的最大时间间隔，以秒为单位

1.48 hot_standby_feedback = off

指定热备是否将发送反馈到主或有关查询当前正在备机上执行的上游备机。如果设置为ON，备库在执行QUERY时会通知主库，哪些版本需要被保留。

1.49 wal_receiver_timeout = 60s

中止处于非活动状态超过指定时间的复制链接

1.50 wal_retrieve_retry_interval = 5s

控制从库应用事务日志失败后的重试

1.51 max_logical_replication_workers = 4

指定逻辑复制工作的最大数量，必须小于max_work_processes，可以考虑等于max_wal_senfers

1.52 max_sync_workers_per_subscription = 2

每个订阅的最大同步工作者数量。 此参数控制订阅初始化期间或添加新表时初始数据副本的并行数量，可以考虑max_logical_replication_workers/2

2 执行计划参数

2.1 执行计划开关参数

#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_parallel_append = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

2.2 分区连接参数

2.2.1 enable_partitionwise_join = off

2.2.2 enable_partitionwise_aggregate = off

智能分区连接（partition-wise join），对于两个分区方式相同的分区表，如果使用分区键进行等值连接，允许使用匹配的分区直接进行连接操作。
智能分区聚合（partition-wise aggregation），对于分区表的聚合操作，如果 GROUP BY 中包含了分区键，允许针对各个分区进行并行的聚合操作，然后再合并结果。

2.2.3 enable_parallel_hash = on

分区级别的哈希连接可并行执行，且分区中的数据量比整个表少，性能会更好
olap系统可以考虑开启

2.2.4 enable_partition_pruning = on

通过enable_partition_pruning参数来控制，是否要对select,update,delete操作过滤到目标分区。

2.3 优化器参数因子

2.3.1 seq_page_cost = 1.0

2.3.2 random_page_cost = 4.0

2.3.3 cpu_tuple_cost = 0.01

2.3.4 cpu_index_tuple_cost = 0.005

2.3.5 cpu_operator_cost = 0.0025

执行计划cost计算的代价因子，在离散io较好的机器上，比如ssd，可以将random_page_cost设置为1.1，不需要考虑顺序扫描与随机扫描的成本差异，在部分sql情况下能更容易走索引，其他建议默认。

2.3.6 effective_cache_size = 4GB

主机内存*0.75，设置较大的值可以让优化器更倾向于走索引扫描。

3 日志参数

3.1 log_min_duration_statement = -1

记录慢sql，设置为1000时，则超过1s的慢sql都会打印，不受log_state_statement影响。

3.2 log_checkpoints = off

记录检查点详细统计信息，分析性能时可以开启。

3.3 log_connections = off

如果业务时短连接，建议关闭，否则可以考虑开启。

3.4 log_disconnections = off

如果业务时短连接，建议关闭，否则可以考虑开启。

3.5 log_duration = off

为on可以记录每条语句执行完成消耗时间，一般不太需要。

3.6 log_error_verbosity = default

可以记录错误代码的代码位置，方便排查问题

3.7 log_hostname = off

记录连接数据库服务器的hostname

3.8 log_line_prefix = '%m [%p] ‘

%m - %a - %u - %d - %p:
记录连接的信息，比如什么时间哪个应用用哪个用户连接哪个数据库执行的具体sql等

3.9 log_lock_waits = off

是否打印锁等待事件，分析性能时可用

3.10 log_statement = ‘none’

如果需要审计sql，可以配置为all，会打印全部sql

3.11 log_temp_files = -1

临时文件使用多大时，打印到日志，分析性能时可用

4 统计信息优化参数

4.1 track_activities = on

收集正在执行的sql

4.2 track_counts = on

收集表、索引上的统计信息

4.3 track_io_timing = off

收集io的时间信息，如果开启可能会对性能有一定影响

4.4 track_functions = none

收集函数被调用次数，需要分析数据时可开启

4.5 track_activity_query_size = 1024

单条被跟踪的query最多能存储多少字节，如果超长，日志中被截断

4.6 stats_temp_directory = 'pg_stat_tmp‘

相对路径（$PGDATA）或绝对路径。用于存储统计信息的临时目录。可以设置为io较好的目录，提高性

5 垃圾回收参数

5.1 autovacuum = on

自动垃圾回收

5.2 log_autovacuum_min_duration = -1

记录autovacuum执行时间，比如设置为500，则超过500ms的autovacuum记录到日志中

5.3 autovacuum_max_workers = 3

autovacuum并行，不建议超过cpu核数

5.4 autovacuum_naptime = 1min

两次自动清理操作之间的时间间隔

5.5 autovacuum_vacuum_threshold = 50

5.6 autovacuum_analyze_threshold = 50

5.7 autovacuum_vacuum_scale_factor = 0.2

5.8 autovacuum_analyze_scale_factor = 0.1

autovacuum操作的相关的比例因子和阈值
如上配置时，当dead tuple超过行数0.2+50，则触发vacuum，超过行数0.1+50，则触发analyze，对于大表来说，显然比例因子有些大，会影响扫描速率，一是可以适当调小比例因子，二是可以考虑放弃比例因子，直接设置稍大的阈值，对于一些。

5.9 vacuum_freeze_min_age = 50000000

50000000表示表中每个元组需要freeze的最小年龄

5.10 vacuum_freeze_table_age = 150000000

200000000表示当表的年龄大于该值时，会进行急切模式冻结

5.11 vacuum_multixact_freeze_min_age = 50000000

50000000

5.12 vacuum_multixact_freeze_table_age = 150000000

200000000

5.13 vacuum_cleanup_index_scale_factor = 0.1

可以降低vacuum cleanup阶段index scan的概率，在执行vacuum时，有一个cleanup阶段，以往，不管这个阶段是否需要清理PAGE，只要表上面有索引，就需要对这个表的索引全部扫描一遍，这个值类似用于记录索引未变化信息，因此，对于大量INSERT，没有UPDATE,DELETE操作的表的VACUUM，或者常规静态表的VACUUM会快很多，因为不需要SCAN INDEX了

6 事务ID 冻结参数

6.1 autovacuum_freeze_max_age = 200000000

1200000000一旦新老事务ID超过这个值，就会强制freeze

6.2 autovacuum_multixact_freeze_max_age = 400000000

1250000000

6.3 autovacuum_vacuum_cost_delay = 20ms

更新操作特别频繁的系统可以考虑设置为0，但需要有比较高的io保障，比如使用ssd

6.4 autovacuum_vacuum_cost_limit = -1

-1代表与vacuum_cost_limit保持一致

7 事务管理参数

7.1 statement_timeout = 0

可以防止雪崩，不建议全局设置，可以在窗口中执行sql前set

7.2 lock_timeout = 0

执行ddl前可以加上超时设置，防止雪崩，同样在窗口中设置

7.3 idle_in_transaction_session_timeout = 0

6h，空闲事务自动清理，0代表不清理，可以根据实际情况设置

8 一个生产的配置示例

listen_addresses = '*'
max_connections=2000  
superuser_reserved_connections = '10'
shared_buffers=16GB             
work_mem =8MB         
maintenance_work_mem = 1GB           
autovacuum_work_mem  = 1GB     
max_worker_processes = 64       
max_parallel_maintenance_workers = 8  
max_parallel_workers_per_gather = 0     
max_parallel_workers =12