PostgreSQL11生产环境常用参数配置参考

系统管理员 2022-02-27 12:54 1342阅读 1赞

## 背景 ##

搭建日期：20190327  
版本：Postgresql-11  
服务器配置：8核cpu 16GB 内存。※比较小，下面配置都按照这个标准作为示例。  
Postgresql服务状态：单机yum安装，数据TB级别，但是并发不高，主要用于数据分析。没有做主从。

### 修改配置文件方法 ###

1.  默认配置文件postgresql.conf。每一行代表一个参数，\#代表注释。为PG提供默认值，除非被下面方法覆盖。
2.  postgresql.auto.conf。不能手工编辑，文件保存了Alter SYSTEM命令提供的设置。优先级 > postgresql.conf。
3.  SQL命令修改参数。
    
    1.  ALTER SYSTEM命令改变全局默认值，等同修改配置文件。
    2.  ALTER DATABASE命令允许针对一个数据库覆盖其全局设置。
    3.  ALTER ROLE命令允许用用户指定的值来覆盖全局设置和数据库设置  
        4，修改会话级别的参数。***SHOW/SET***
    4.  SHOW===current\_setting(setting\_name text)
    5.  SET ===set\_config(setting\_name, new\_value, is\_local)

SET configuration_parameter TO DEFAULT;
     等效于：
     UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

### 重新加载配置文件 ###

1.  不重启修改配置文件  
    sudo -u postgres /usr/pgsql-11/bin/pg\_ctl reload -D $PGDATA
2.  SQL函数pg\_reload\_conf()
3.  重启服务是SINGUP信号。

## 重点：生产参数配置参考 ##

> 各个参数的默认值可以参考我的另一片文章，默认参数。

##### max\_connections = 300 #####

决定数据库的最大并发连接数。

*  参考值：不超过物理内存(GB)\*50。

##### shared\_buffers =4GB #####

设置数据库服务器将使用的共享内存缓冲区量。默认通常是 128 兆字节（128MB）

*  参考值： 1/4 主机内存

##### work\_mem = 16MB #####

*  写到临时磁盘文件之前被内部排序操作和哈希表使用的内存量。  
    排序、去重、归并需要排序，再生成临时变落入磁盘之前，会在内存中占用一部分空间。同一个时刻，可能有很多排序的工作都会按此参数分配内存，所以这个值不能太大。  
    如果要使用语句中有较大的排序操作，可以在会话级别设置该参数，set work\_men = ‘2GB’,提高执行速度。
 *  参考值：\# 1/4 主机内存 / 256 (假设256个并发同时使用work\_mem)

##### maintenance\_work\_mem =1GB #####

指定在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGNKEY）中使用的最大的内存量。这样的操作并不频繁，所以可以比work\_men大很多。

*  参考值  
    内存的1/4除以autovacuum\_max\_workers数量。  
    系统内存超过32G时，建议设置为1GB。超过64GB时，建议设置为2GB。超过128GB时，建议设置为4GB。主要作用是改进清理和恢复数据库转储的性能，设定一个autovacuum\_max\_workers匹配值就好。

#### 下面三个参数主要控制并发工作者数量，对于性能有不小影响。 ####

##### max\_worker\_processes = 32 #####

系统能够支持的后台进程的最大数量,很多processes都需要获取的根源，所以尽可能保证充足的线程数量。

##### max\_parallel\_workers = 4 #####

并行工作者数量，从max\_worker\_processes获取，所以不能大于后者。

*  参考值：cpu-4

##### max\_parallel\_workers\_per\_gather=4 #####

每个查询并行工作者数量，不能大于上面两个

*  参考值：cpu-4

##### effective\_cache\_size #####

设置规划器对一个单一查询可用的有效磁盘缓冲区尺寸的假设  
更高的数值会使得索引扫描更可能被使用，更低的数值会使  
得顺序扫描更可能被使用。它只用于估计的目的

*  参考值：内存一半

#### 日志相关 ####

##### logging\_collector = on #####

启动日志收集器

##### log\_destination = ‘csvlog’ #####

stderr, csvlog, syslog, and eventlog（windows）  
日志记录格式，默认是stderr。csvlog可以以csv格式输出，便于分析。如果没有分析需求，个人推荐默认就好。

##### log\_directory = ‘log’ #####

log存放相遇于PGDATA的位置

##### log\_filename = ‘postgresql-%a.log’ #####

log 日志文件名，控制日志输出频率。上面是保存一周，每天一个log日志文件。

#### SQL审计相关日志记录 ####

##### log\_min\_duration\_statement = 5s; （重要） #####

慢查询日志记录，运行超过5s的语句被认为是慢查询。根据业务情况设定。

##### log\_duration = off #####

每一个语句的时间都被记录。

##### log\_statement = ‘mod’ #####

*  ddl =数据定义语句，例如CREATE、ALTER和 DROP语句。
 *  mod = ddl+数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM。
 *  all = 所有语句，但不包含语法错误的语句

##### log\_lock\_waits = on #####

当一个会话为获得一个锁等到超过deadlock\_timeout时，是否记录日志。

##### deadlock\_timeout = 1s #####

死锁检测之前在一个锁上等待的总时间。

#### 检查点相关参数 ####

###### checkpoint\_warning =30s 检查点频率检查 ######

如果检查点触发的频率小于30s，则log日志中会提醒你增加max\_wal\_size。

*  参考设置：如果硬盘的性能很好可以设置小一些，如果硬盘性能差就设置大一些。这个参数不影响性能。只是用来检查实际checkpoint发生的频率。

##### checkpoint\_timeout = 30 mins 检查点触发时间间隔 #####

自动检查点出发的时间，增加这个参数的值会增加崩溃恢复所需的时间。磁盘性能好的话可以适当减小。这个值减小会增加检查点频率，降低性能。

##### max\_wal\_size = 8GB #####

*  参考值：主机内存1/2  
    在自动WAL检查点使得WAL增长到最大尺寸，到了这个尺寸，检查点就开始工作。

##### min\_wal\_size = 1GB #####

WAL磁盘使用率低于这个设置，旧的WAL文件总数被回收,确保预留足够的WAL空间处理WAL使用中的峰值。

*  参考值：主机内存1/8

## 附录 ##

log\_duration=on 和 log\_min\_duration\_statement=0的区别  
前者所有查询时间都被记录，后者查询时间>0都被记录。听起来一个意思。

区别：log\_min\_duration\_statement强制记录SQL文本，而log\_duration不记录SQL文本。

也就是说，如果log\_duration=on 同时log\_min\_duration\_statement=5s时。

所有的语句都被记录时间，但是只有查询超过5s的语句才被记录SQL文。

个人认为log\_duration只有在特殊场景才有意义。log\_min\_duration\_statement已经够用了。

> 本文参数设定主要作为参考，未出现的参数并不意味着不需要设定。当你设定一个参数不知道参数意义和如何设定时可以参考这个文章。

PostgreSQL11生产环境常用参数配置参考

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Nginx生产环境配置、elasticsearch生产环境配置、rocketmq生产环境配置（史上最全）

相关 Hadoop：Yarn生产环境核心参数配置案例

相关 P11：生产环境打包

相关 Hadoop Yarn生产环境核心配置参数

相关 Yarn 生产环境核心参数配置、多队列/优先级配置

相关 Tomcat与Jre绿色环境配置（生产环境）【参考自用】

相关 springboot 常用配置之多环境配置(开发环境、测试环境、生产环境等)

相关 redis配置文件常用参考

相关 PostgreSQL11生产环境常用参数配置参考

相关 tornado 11、部署生产环境