关于postgresql:postgresql配置参数优化

在应用 postgresql 过程当中如果并发比拟高，并且数据量也比拟大的时候须要对配置项做一些优化

理论的 postgresql 默认配置比拟激进，所以如果机器硬件配置还能够的话能够大幅度改变

优化配置项

不同版本下的配置默认值是不一样的，具体以官网文档为准
以下配置是pg14 版本

max_connections

• 默认值 100
• 容许的最大客户端连接数，通常能够配置上百上千个连贯，并发不是太高的话 100 够用了

shared_buffers

• 增大数据库内存应用效率
• 设置数据库服务器将应用的共享内存缓冲区量，默认128MB
• 决定有多少内存能够被 PostgreSQL 用于缓存数据
• 举荐内存的 1 /4
• 在磁盘 IO 压力很大的状况下，进步该值能够缩小磁盘IO

work_mem

• 放慢查问排序
• 默认4M
• 设置在写入长期磁盘文件之前查问操作 (例如排序或哈希表) 可应用的根底最大内存容量

• ORDER BY、DISTINCT和归并连贯都要用到排序操作，哈希连贯、基于哈希的汇集以及基于哈希的 IN 子查询处理中都要用到哈希表，

  使外部排序和一些简单的查问都在这个 buffer 中实现，有助进步排序等操作的速度，并且升高IO

• 这个参数和 max_connections 无关，如果 100 个查问并发，最坏状况下就很快就达到 400M 的内存应用，

  work_mem * max_connections须要小于内存，举荐乘积能够放弃在内存的 1/2 和3/4之间

effective_cache_size

• 放慢查问
• 默认4G，该配置不理论占用内存，优化器假如一个查问能够用的最大内存，激进举荐配置为物理内存的1/2，更加举荐配置为内存的3/4
• 设置变大，优化器更偏向应用索引扫描而不是程序扫描

max_wal_size

• 防止频繁的进行检查点，缩小磁盘IO
• 默认1G
• wal全称是 write ahead log，是postgresql 中的online redo log，保证数据的一致性和事务的完整性
• 在主动 WAL 检查点之间容许 WAL 增长到的最大尺寸
• 中心思想是先写日志后写数据，即要保障对数据库文件的批改应放生在这些批改曾经写入到日志之后
• 应用 wal 能够显著缩小磁盘写操作的数量，因为只须要将日志文件刷新到磁盘以确保提交事务，而不是事务更改的每个数据文件，日志文件是按程序写入的，因而同步日志的老本要比刷新数据页的成本低得多
• 如果在数据库的日志当中看到告警如 consider increasing the configuration parameter "max_wal_size"，则的确须要增大该参数，在高负载状况下，日志很快就会达到1G, 举荐把该参数配置为32G 或者以上

wal_buffers

• 放慢数据更新操作
• 默认4M
• 配置 wal 应用的共享内存大小，能够改成1G

配置形式

批改数据库配置能够通过批改 postgresql.conf 文件或者批改启动命令等形式

批改文件形式

在 postgresql 的docker容器当中

postgresql.conf的文件地位是/var/lib/postgresql/data/postgresql.conf，如果找不到能够执行如下命令搜寻一下

$ find / -name "postgresql.conf" 2>/dev/null

在批改实现该配置文件之后间接挂载进去启动容器就行

批改启动命令形式

该形式简略间接

比方批改最大连贯数量，配置启动命令为 postgres -c max_connections=500 即可

查看配置是否失效

最终通过优化，批改 pg 的启动命令改为

$ postgres -c max_connections=500 -c shared_buffers=1GB -c work_mem=128MB -c max_wal_size=64GB -c wal_buffers=1GB

进入到 psql 环境上面执行 show 命令验证

# show max_connections ;
 max_connections 
-----------------
 500
(1 row)

或者应用 show all 查看所有配置

参考浏览

postgres中文文档

postgresql服务器配置

PostgreSQL配置优化参数详解

PostgreSQL参数优化