PG中的常见参数配置
Tags： PGshared_buffers内存参数参数参数配置
优化参数
参考：https://www.xmmup.com/pgzhongdeshared_bufferscanshupeizhi.html
wal_buffers
PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。
缓冲区的默认大小，由wal_buffers定义，但如果您有大量并发连接，则较高的值可以提供更好的性能。
effective_cache_size
该effective_cache_size提供了可以用于磁盘缓存存储器的估计。
它只是一个指导原则，而不是确切分配的内存或缓存大小。
它不分配实际内存，而是告诉优化器内核中可用的缓存量。
如果将此值设置得太低，查询计划程序可以决定不使用某些索引，即使它们有用。
因此，设置较大的值总是有益的。
work_mem
指定在写入磁盘上的临时文件之前，ORDER BY，DISTINCT，JOIN和哈希表的内部操作将使用的内存量。
此配置用于复杂排序，如果必须进行复杂排序，则增加work_mem的值以获得良好结果。
内存中的排序比溢出到磁盘的排序快得多。
设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是按用户排序操作。
如果您有许多用户尝试执行排序操作，系统将为所有用户分配 work_mem * 总排序操作 。
全局设置此参数可能会导致内存使用率过高，强烈建议在会话级别修改它。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | work_mem = 2MB testdb=# SET work_mem TO "2MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=508181.79..514431.86 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) (5 rows) testdb=# SET work_mem TO "2MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=508181.79..514431.86 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) (5 rows) |
初始查询的排序节点的估计成本为514431.86。成本是一个任意的计算单位。对于上面的查询，我们的work_mem只有2MB。出于测试目的，让我们将其增加到256MB并查看是否对成本有任何影响。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | work_mem = 256MB testdb=# SET work_mem TO "256MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=354367.29..360617.36 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) testdb=# SET work_mem TO "256MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN \----------------------------------------------------------------------------------- Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=354367.29..360617.36 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) |
查询成本从514431.86减少到360617.36 - 减少30％。
maintenance_work_mem
maintenance_work_mem是用于维护任务的内存设置。默认值为64MB。
设置较大的值有助于执行VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等任务。
由于会话中只能同时执行其中一个操作，并且通常没有多个同时运行，因此它可能比work_mem大。
较大的配置可以提高VACUUM和数据库还原的性能。
执行autovacuum时，可以为此内存分配autovacuum_max_workers参数的配置次数，因此我们必须考虑这一点，或者配置autovacuum_work_mem参数来单独管理它。本参数可以针对每个session设置。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | maintenance_work_mem = 10MB postgres=# CHECKPOINT; postgres=# SET maintenance_work_mem to '10MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 170091.371 ms (02:50.091) postgres=# CHECKPOINT; postgres=# SET maintenance_work_mem to '10MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 170091.371 ms (02:50.091) maintenance_work_mem = 256MB postgres=# CHECKPOINT; postgres=# set maintenance_work_mem to '256MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 111274.903 ms (01:51.275) postgres=# CHECKPOINT; postgres=# set maintenance_work_mem to '256MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 111274.903 ms (01:51.275) |
当maintenance_work_mem设置为仅10MB时，索引创建时间为170091.371ms，但当我们将maintenance_work_mem设置增加到256MB时，该时间减少到111274.903 ms。
FSYNC
如果启用了fsync，PostgreSQL将尝试确保将更新写入物理磁盘。
这可确保在操作系统或硬件崩溃后可以将数据库群集恢复到一致状态。
禁用fsync通常可以提高性能，但在发生电源故障或系统崩溃时可能会导致数据丢失。
因此，如果您可以轻松地从外部数据重新创建整个数据库，则建议停用fsync。
synchronous_commit
指定在命令向客户端返回“成功”指示之前，事务提交是否将等待WAL记录写入磁盘。
这是性能和可靠性之间的权衡。默认设置为“on”。
可能的值包括：“on”，“remote_apply”，“remote_write”，“local”和“off”。
与fsync不同，禁用此参数不会产生任何数据库不一致的风险：操作系统或数据库崩溃可能导致丢失一些最近发生的可能提交的事务，但数据库的状态将与这些事务完全相同，未提交的将被抛弃。
当性能比事务持久性更重要时，停用synchronous_commit可能是一个有用的替代方法。
这意味着成功状态与保证写入磁盘之间会存在时间差。
在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。
在这种情况下，事务提交非常快，因为它不会等待刷新WAL文件，但可靠性受到损害。
checkpoint_timeout，checkpoint_completion_target
PostgreSQL将更改写入WAL。
检查点进程将数据刷新到数据文件中。
发生CHECKPOINT时完成此活动。这是一项昂贵的操作，可能会导致大量的IO。
整个过程涉及昂贵的磁盘读/写操作。
checkpoint_timeout：检查点启动的时间间隔
将此设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但由于每个检查点都会占用宝贵的系统资源，因此也会损害性能。高频率的检查点可能会影响性能。
checkpoint_completion_target衡量检查点完成的时间长度。
注意：可以调整更多参数以获得更好的性能，但这些参数的影响小于此处突出显示的参数。最后，我们必须始终牢记并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能，有些则不会。必须针对应用程序及其运行的OS的特定需求调整数据库参数。
MAX_CONNECTIONS
确定与数据库同时连接的最大数量。
每个客户端都可以配置内存资源，因此，客户机的最大数量表明使用的内存的最大数量。
superuser_reserved_connections
在达到max_connection限制的情况下，这些连接保留给超级用户。
temp_buffers
设置每个会话使用的最大临时缓冲区数。
这些是仅用于访问临时表的本地会话缓冲区。
会话将根据需要分配临时缓冲区，直到temp_buffers给出的限制。
max_wal_size
允许WAL在检查点之间增长的最大大小。
在特殊情况下，WAL的大小可能超过max_wal_size。增加此参数可能会增加恢复故障所需的时间。
min_wal_size当WAL文件保持低于此值时，它将被回收以供将来在检查点使用，而不是被删除。这可以用于确保保留足够的WAL空间来处理WAL的使用中的峰值，例如在执行大批量作业时。
wal_sync_method
用于强制WAL更新到磁盘的方法。
如果禁用fsync，则此设置无效。该参数有如下取值：
² open_datasync（使用open()option 写入WAL文件O_DSYNC）
² fdatasync（fdatasync()在每次提交时调用），在linux平台上，该值是默认值
² fsync（fsync()在每次提交时调用）
² fsync_writethrough（fsync()在每次提交时调用，强制写入任何磁盘写入缓存）
² open_sync（使用open()option 写入WAL文件O_SYNC）
effective_cache_size
查询计划程序使用此值来考虑可能适合或不适合内存的计划。本参数本身不分配任何内存，该参数只是提供为规划器的一个建议值，表明有可能使用的大小。
在使用指数的成本估算中考虑到了这一点;
较高的值使得更有可能使用索引扫描，而较低的值使得更有可能使用顺序扫描。
合理的值将是RAM的50%。该参数可以在session级别设置，可以在需要较高设置的查询时再增加本参数值。
default_statistics_target
PostgreSQL从数据库中的每个表中收集统计信息，以决定如何在它们上执行查询。
默认情况下，它不会收集太多信息，如果您没有获得良好的执行计划，则应增加此值，然后再次在数据库中运行ANALYZE（或等待AUTOVACUUM）。增加此值会导致analyze运行的时长变长。
random_page_cost
本参数用于衡量SQL语句从磁盘进行随机存取的代价，默认值是4。这个默认值是针对机械硬盘的设置。若是$PGDATA位于固态盘(SSD)上，建议修改成1.1，因为固态盘(SSD)的随机存取和顺序存取的代价几乎是一样的。
- 顺序扫描一个数据块，cost值定为1，参数为seq_page_cost
- 随机扫描一个数据块，cost值定为4，参数为random_page_cost
- 处理一个数据行的CPU，cost为0.01，参数为cpu_tuple_cost
- 处理一个索引行的CPU，cost为0.005，参数为cpu_index_tuple_cost
- 每个操作符的 CPU 代价为 0.0025，参数为cpu_operator_cost
参考：https://www.xmmup.com/pgzhongdechaxunguihuacanshu.html
