合 PG中的常见参数配置

优化参数

shared_buffers

参考：https://www.dbaup.com/pgzhongdeshared_bufferscanshupeizhi.html

wal_buffers

PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。

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

PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。由wal_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。

查看当前wal_buffers的值：

effective_cache_size

该effective_cache_size提供了可以用于磁盘缓存存储器的估计。

它只是一个指导原则，而不是确切分配的内存或缓存大小。

它不分配实际内存，而是告诉优化器内核中可用的缓存量。

如果将此值设置得太低，查询计划程序可以决定不使用某些索引，即使它们有用。

因此，设置较大的值总是有益的。

effective_cache_size提供可用于磁盘高速缓存的内存量的估计值。它只是一个建议值，而不是确切分配的内存或缓存大小。它不会实际分配内存，而是会告知优化器内核中可用的缓存量。在一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。在设置这个参数时，还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。默认值是4GB。

查看当前effective_cache_size的值：

work_mem

指定在写入磁盘上的临时文件之前，ORDER BY，DISTINCT，JOIN和哈希表的内部操作将使用的内存量。

此配置用于复杂排序，如果必须进行复杂排序，则增加work_mem的值以获得良好结果。

内存中的排序比溢出到磁盘的排序快得多。

设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是按用户排序操作。

如果您有许多用户尝试执行排序操作，系统将为所有用户分配 work_mem * 总排序操作 。

全局设置此参数可能会导致内存使用率过高，强烈建议在会话级别修改它。

初始查询的排序节点的估计成本为514431.86。成本是一个任意的计算单位。对于上面的查询，我们的work_mem只有2MB。出于测试目的，让我们将其增加到256MB并查看是否对成本有任何影响。

查询成本从514431.86减少到360617.36 - 减少30％。

此配置用于复合排序。内存中的排序比溢出到磁盘的排序快得多，设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是按用户排序操作。如果有多个用户尝试执行排序操作，则系统将为所有用户分配大小为work_mem *总排序操作数的空间。全局设置此参数可能会导致内存使用率过高，因此强烈建议在会话级别修改此参数值。默认值为4MB。

查看当前work_mem的值：

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设置。

当maintenance_work_mem设置为仅10MB时，索引创建时间为170091.371ms，但当我们将maintenance_work_mem设置增加到256MB时，该时间减少到111274.903 ms。

maintenance_work_mem是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。

查看当前maintenance_work_mem的值：

fsync

如果启用了fsync，PostgreSQL将尝试确保将更新写入物理磁盘。

这可确保在操作系统或硬件崩溃后可以将数据库群集恢复到一致状态。

禁用fsync通常可以提高性能，但在发生电源故障或系统崩溃时可能会导致数据丢失。

因此，如果您可以轻松地从外部数据重新创建整个数据库，则建议停用fsync。

强制把数据同步更新到磁盘,如果系统的IO压力很大，把改参数改为off

在fsync打开的情况下，优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势，如果到实际测试中，提升应该不止30%。

测试的过程中，主要的瓶颈就在系统的IO，如果需要减少IO的负荷，最直接的方法就是把fsync关闭，但是这样就会在掉电的情况下，可能会丢失部分数据。

synchronous_commit

指定在命令向客户端返回“成功”指示之前，事务提交是否将等待WAL记录写入磁盘。

这是性能和可靠性之间的权衡。默认设置为“on”。

可能的值包括：“on”，“remote_apply”，“remote_write”，“local”和“off”。

与fsync不同，禁用此参数不会产生任何数据库不一致的风险：操作系统或数据库崩溃可能导致丢失一些最近发生的可能提交的事务，但数据库的状态将与这些事务完全相同，未提交的将被抛弃。

当性能比事务持久性更重要时，停用synchronous_commit可能是一个有用的替代方法。

这意味着成功状态与保证写入磁盘之间会存在时间差。

在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。

在这种情况下，事务提交非常快，因为它不会等待刷新WAL文件，但可靠性受到损害。

此参数的作用为在向客户端返回成功状态之前，强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要，那么关闭synchronous_commit。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。

查看当前synchronous_commit的设置值：

checkpoint_timeout，checkpoint_completion_target

PostgreSQL将更改写入WAL。

检查点进程将数据刷新到数据文件中。

发生CHECKPOINT时完成此活动。这是一项昂贵的操作，可能会导致大量的IO。

整个过程涉及昂贵的磁盘读/写操作。

checkpoint_timeout：检查点启动的时间间隔

将此设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但由于每个检查点都会占用宝贵的系统资源，因此也会损害性能。高频率的检查点可能会影响性能。

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

注意：可以调整更多参数以获得更好的性能，但这些参数的影响小于此处突出显示的参数。最后，我们必须始终牢记并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能，有些则不会。必须针对应用程序及其运行的OS的特定需求调整数据库参数。

PostgreSQL将更改写入WAL。检查点进程将数据刷新到数据文件中。发生CHECKPOINT时完成此操作。这是一项开销很大的操作，整个过程涉及大量的磁盘读/写操作。用户可以在需要时随时发出CHECKPOINT指令，或者通过PostgreSQL的参数checkpoint_timeout和checkpoint_completion_target来自动完成。

checkpoint_timeout参数用于设置WAL检查点之间的时间。将此设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但由于每个检查点都会占用系统资源，因此也会损害性能。此参数只能在postgresql.conf文件中或在服务器命令行上设置。

checkpoint_completion_target指定检查点完成的目标，作为检查点之间总时间的一部分。默认值是 0.5。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。高频率的检查点可能会影响性能。

查看当前checkpoint_timeout和checkpoint_completion_target的值：

MAX_CONNECTIONS

确定与数据库同时连接的最大数量。

每个客户端都可以配置内存资源，因此，客户机的最大数量表明使用的内存的最大数量。

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。