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 查看所有配置

postgresql检查点checkpoint

产生原因

• 在事务提交的时候，如果直接写入数据文件，必须要先在数据文件中定位到该数据块，然后才能进行数据的更新，写入是随机的，磁盘的随机读写速度比较慢，所以事务提交的时候以追加日志方式顺序写入wal日志文件，保证足够的速度
• wal会占用大量磁盘，并且故障恢复的时候redo的wal日志太多导致故障恢复速度变慢

checkpoint概述

• 用来保证数据一致性和完整性的一个数据库事务，执行的时候会把检查点之前的脏数据刷新到磁盘，实现数据缩短数据库崩溃恢复时间的目的
• 当数据更新的时候，包含更新数据的数据块在共享缓冲区更新，纪录被保存在wal缓冲区，提交后，更改被追加到磁盘上面的wal日志，永久生效
• 数据库崩溃的时候，共享缓冲区被清空，wal日志中的更改被应用于数据文件
• checkpoint执行的时候会识别共享缓冲区的脏页，把数据写入数据文件当中（脏页刷入），然后删除这个检查点位置之前的wal文件

触发条件

• 手动执行checkpoint命令
• wal文件的总大小超过了max_wal_size配置的大小
• 距离上一次检查点执行超过了checkpoint_timeout配置的超时时间
• 数据库停止或者恢复启动完成
• 执行pg_start_backup备份的时候