PG 数据库常用参数调整

1.shard_buffers

Postgresql使用自己的缓冲区,也使用操作系统缓冲区。这意味着数据存储在内存中两次,首先是 Postgresql缓冲区,然后是操作系统缓冲区。

与其他数据库不同, Postgresql不提供直接IO。这称为双缓冲（就是磁盘中的时候读的时候先放在数据库的缓冲区，再放在操作系统缓冲区）。

Postgresql缓冲区称为 shared buffers,它是大多数操作系统最有效的可调参数。

Postgresql将用 shared buffers参数缓存如下数据:

表数据，索引，执行计划

初始化参考值:物理内存1/4

2.wal_buffer

PostgresαL将其WAL(预写日志)记录写入缓冲区,然后将这些缓冲区刷新到磁盘。

缓冲区的默认大小,由wal_ buffers定义,但如果您有大量并发连接,则较高的值可以提供更好的性能。

该缓冲区的作用是临时存放 redo log,所以分配太大不会对性能有好处,一般10MB左右。

3.effective_cache_size--默认4G

磁盘缓存存储器的估计。它只是一个指导原则,而不是确切分配的内存或缓存大小。它不分配实际内存,而是告诉优化器内核中可用的缓存量。如果将此值设置得太低,查询计划程序可以决定不使用某些索引,即使它们有用。因此,设置较大的值总是有益的建议使用默认值。

4.work_mem--类似Oracle的PGA

指定在写入磁盘上的临时文件之前, ORDER BY, DISTINCT,JON和哈希表的内部操作将使用的内存量。
此配置用于复杂排序,如果必须进行复杂排序,则增加work_mem的值以获得良好结果。内存中的排序比溢出到磁盘的排序快得多。
设置非常高的值可能会导致部署环境出现内存瓶颈,因为此参数是按用户排序操作。
如果您有许多用户尝试执行排序操作,系统将为所有用户分配 work mem*总排序操作
全局设置此参数可能会导致内存使用率过高,强烈建议在会话级别修改它。

默认4M

5.maintenance_work_mem

maintenance_work_mem是用于维护在务的内存设置。默认为64MB。本参数可以针对每个 session设置。
设置较大的值有助于执行 VACUUM, RESTORE, CREATE|NDEX, ADD FOREIGN KEY和ALTER TABLE等任务。
由于会话中只能同时执行其中一个操作,并且通常没有多个同时运行,因此它可能比 work_mem大。
较大的配置可以提高 VACUUM和数据库还原的性能。
执行 autovacuum时,或者配置 autovacuum_work_mem参数来单独管理它。

6.FSYNC

如果启用了 fsync, Postgresql将尝试确保将更新写入物理磁盘,会延长响应时间对性能有一定影响。
这可确保在操作系统或硬件崩溃后可以将数据库群集恢复到一致状态。
禁用sync通常可以提高性能,但在发生电源故障或系统崩溃时可能会导致数据丢失。
从外部数据重新创建整个数据库,则建议停用 fsync。

7.synchronous_commit

指定在命令向客户端返回“成功”指示之前,事务提交是否将等待WAL记录写入磁盘。这是性能和可靠性之间的权衡。默认设置为“on'’。

可能的值包括:"on","remote_apply", "remote_write","local"和“of"

与fsync不同,用此参数不会产生任何数据库不一致的风险:操作系统或数据库崩溃可能导致丢失一些最近发生的可能提交的事务,但数据库的状态将与这些事务完全相同,未提交的将被抛弃
当性能比事务持久性更重要时,停用 synchronous_commit可能是一个有用的替代方法
这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下,即使客户端在提交时收到成功消息,数据也可能丢失。在这种情况下,事务提交非常快,因为它不会等待刷新WAL文件,但可靠性受到损害。

8.checkpoint_timeout

检查点启动的时间间隔将此设置得太低会减少崩溃恢复时间,因为更多数据会写入磁盘,但由于每个检查点都会占用宝贵的系统资源,因此也会损害性能。高频率的检査点可能会影响性能。实例崩溃的机率与长时间运行的性能相比,实例崩溃所占的比重要小的多,该值设置为实例崩溃后客户允许恢复的时间。

检查点进程将数据刷新到数据文件中。
发生 CHECKPOINT时完成此活动。这是一项昂贵的操作,可能会导致大量的|O。整个过程涉及昂贵的磁盘读/写操作。

checkpoint_completion_target衡量检查点完成的时间长度。

9.checkpoint_completion_target

数据库中一个至关重要的参数,主要与参数 checkpoint_timeout(checkpoint_timeout配合使用,值越小意味着检查点要越快完成,要求写得要快。

控制每次检査点发生时i/o的吞吐量,值越高,则/o占用的资源越少,数据库性能越好;值越低,则/o占用的资源越多,影响数据库性能,但是提高检査点完成速度。

可以理解为如果这个参数设置的太大，可能会发生数据库震荡。

10.full_page_writes

全页写,把数据块写入到WAL日志中,目的是为了解决块不一致问题,保护数据的完整性,全页写会导致WAL日志膨胀,增加额外/O。

如果性能优先考虑,同时有日常数据库备份,则可以关闭。

其它参数：