调优 PostgreSQL 服务器

作者：Greg Smith，Robert Treat 和 Christopher Browne

PostgreSQL 附带一个基本的配置，该配置针对广泛的兼容性而非性能进行调整。默认参数很可能对你系统的尺寸过小。与其陷入你最终应该了解的所有细节的泥潭中（如果你想了解这些细节，可以在 GUC 三小时之旅中找到），这里我们将快速浏览基本知识的简化视图，并关注 PostgreSQL 新手不知道的最常见的事情。在阅读完此处的快速介绍后，你应该点击每个部分中的参数名称，跳转到 PostgreSQL 手册中相关的文档以了解更多细节。此外，还可以在服务器配置调优中找到有关这些参数中的许多参数的更多信息以及不应该调整的参数列表。

有关配置设置的背景信息

PostgreSQL 设置可以通过多种方式进行操作，但通常你希望在配置文件中更改它们，无论是直接更改还是从 PostgreSQL 9.4 开始通过 ALTER SYSTEM 进行更改。可用的具体选项会因版本而异，完整列表位于 PostgreSQL 版本的 src/backend/utils/misc/guc.c 源代码中（但 pg_settings 视图对于大多数目的已经足够）。

设置类型

有几种不同的配置设置类型，根据它们可以接受的输入进行划分

布尔型：true、false、on、off
整数型：整数（2112）
浮点型：小数（21.12）
内存/磁盘：整数（2112）或“计算机单位”（512MB、2112GB）。避免使用整数 - 你需要了解底层单位才能弄清楚它们的意思。
时间：时间单位，即 d、m、s（30s）。有时会省略单位；不要这样做
字符串：用单引号括起来的文本（'pg_log'）
枚举：字符串，但来自特定列表（'WARNING'、'ERROR'）
列表：用逗号分隔的字符串列表（'"$user",public,tsearch2'）

何时生效

PostgreSQL 设置在更改时间方面具有不同的灵活性级别，通常与内部代码限制有关。完整级别列表如下：

Postmaster：需要重启服务器
Sighup：需要对服务器进行 HUP 操作，可以通过 kill -HUP（通常为 -1）、pg_ctl reload 或SELECT pg_reload_conf();
用户：可以在单个会话中设置，仅在该会话内生效
内部：在编译时设置，无法更改，主要用于参考
后端：必须在会话开始之前设置的设置
超级用户：可以在运行时由超级用户为服务器设置

大多数情况下，你只需要使用其中第一个，但第二个在你不想关闭服务器时很有用，而用户会话设置在某些特殊情况下很有用。你可以查看 pg_settings 视图中的“context”字段，以了解设置是哪种类型的参数。

有关配置文件的重要说明

命令行选项会覆盖 postgresql.auto.conf 设置，postgresql.auto.conf 设置会覆盖 postgresql.conf 设置。
如果同一个设置被列出了多次，则最后一次设置生效。
你可以通过以下命令确定 postgresql.conf 的位置：SHOW config_file。它通常位于 $PGDATA/postgresql.conf（SHOW data_directory）中，但要小心符号链接、postmaster.opts 和其他复杂情况
以 # 开头的行是注释，没有效果。对于新数据库，这意味着该设置将使用默认值，但对于正在运行的系统，这可能不成立！对配置文件的更改不会在重新加载/重启之前生效，因此系统可能正在运行与文件中不同的内容。

查看当前设置

查看配置文件。这通常不是确定性的！
SHOW ALL, SHOW <setting>将显示设置的当前值。注意会话特定的更改
SELECT * FROM pg_settings将标记会话特定的更改为本地修改

调优工具

dbForge Studio for PostgreSQL 有助于识别生产力瓶颈，并提供 PostgreSQL 性能调优。
该 postgresqltuner.pl 脚本可以分析配置并提出调优建议。
PgBadger 分析 PostgreSQL 日志以生成性能报告。
pgMustard 根据 EXPLAIN ANALYZE 输出提供调优建议。

listen_addresses

默认情况下，PostgreSQL 仅响应来自本地主机的连接。如果你希望你的服务器能够通过标准 TCP/IP 网络从其他系统访问，则需要更改 listen_addresses 的默认值。通常的做法是将其设置为监听所有地址，如下所示：

listen_addresses = '*'

然后通过 pg_hba.conf 文件控制谁可以连接和谁不能连接。

max_connections

max_connections 设置的正是：允许的最大客户端连接数。这对于以下一些参数（尤其是 work_mem）非常重要，因为有些内存资源是或可以按客户端分配的，因此最大客户端数量暗示了最大可能的内存使用量。通常，PostgreSQL 在良好的硬件上可以支持数百个连接。如果你想要数千个连接，你应该考虑使用连接池软件来减少连接开销。

shared_buffers

shared_buffers 配置参数决定了 PostgreSQL 用于缓存数据的内存量。默认值较低的一个原因是在某些平台（如旧的 Solaris 版本和 SGI）上，设置较大的值需要侵入性的操作，如重新编译内核。即使在现代的 Linux 系统上，默认内核也很可能不允许在不调整内核设置的情况下将 shared_buffers 设置为超过 32MB。（PostgreSQL 9.4 及更高版本使用不同的共享内存机制，因此通常不需要调整内核设置。）

如果你拥有 1GB 或更多 RAM 的系统，shared_buffers 的合理起始值为系统内存的 1/4。如果你拥有更少的 RAM，则需要更仔细地考虑操作系统占用多少 RAM；更典型的是接近 15%。在某些工作负载中，即使是更大的 shared_buffers 设置也很有效，但考虑到 PostgreSQL 也依赖于操作系统缓存，使用超过 40% 的 RAM 可能不会比使用较少量的 RAM 更好。

请注意，如果你的系统或 PostgreSQL 构建是 32 位的，则可能不切实际将 shared_buffers 设置为高于 2 ~ 2.5GB。有关详细信息，请参阅这篇博文。

更改此设置需要重启数据库。此外，这是一个硬性分配的内存；数据库启动时会从虚拟内存中分配出整个内存。

PostgreSQL 9.2 或更早版本

如果你运行的是 PostgreSQL 9.2 或更早版本，则很可能为了增加 shared_buffers 的值，你需要增加操作系统允许你分配给单个共享内存段的内存量。在类 UNIX 系统上，如果你将其设置为超出支持范围，你将收到类似以下的错误消息

IpcMemoryCreate: shmget(key=5432001, size=415776768, 03600) failed: Invalid argument 

This error usually means that PostgreSQL's request for a shared memory 
segment exceeded your kernel's SHMMAX parameter. You can either 
reduce the request size or reconfigure the kernel with larger SHMMAX. 
To reduce the request size (currently 415776768 bytes), reduce 
PostgreSQL's shared_buffers parameter (currently 50000) and/or 
its max_connections parameter (currently 12).

有关如何纠正此问题的详细信息，请参阅管理内核资源。

effective_cache_size

effective_cache_size 应设置为对操作系统和数据库本身可用于磁盘缓存的内存量进行估计，同时考虑操作系统本身和其他应用程序使用的内存。这是一个指导，表示你预期在操作系统和 PostgreSQL 缓冲区缓存中可用的内存量，而不是分配！此值仅由 PostgreSQL 查询规划器用来确定它正在考虑的计划是否预期适合内存。如果它设置得太低，索引可能不会像预期那样用于执行查询。shared_buffers 的设置在这里没有被考虑在内 - 只有 effective_cache_size 值被考虑在内，因此它应该包含专门用于数据库的内存。

将 effective_cache_size 设置为总内存的 1/2 将是一个正常的保守设置，而 3/4 的内存是一个更积极但仍然合理的设置。你可以通过查看操作系统的统计信息来找到更好的估计。在类 UNIX 系统上，将 free 或 top 中的 free+cached 数字加起来即可获得估计值。在 Windows 上，请查看 Windows 任务管理器“性能”选项卡中的“系统缓存”大小。更改此设置不需要重启数据库（HUP 就足够了）。

checkpoint_segments checkpoint_completion_target

注意：这适用于 PostgreSQL 9.4 及更早版本。 PostgreSQL 9.5 引入了 min_wal_size 和 max_wal_size 配置参数，并删除了 checkpoint_segments。请查看发行说明以及有关 min_wal_size、max_wal_size 和 WAL 配置的文档。

PostgreSQL 将新的事务写入数据库中称为 WAL 段的文件，这些文件的大小为 16MB。默认情况下，每写入 checkpoint_segments 个文件（默认值为 3 个），就会执行一次检查点。检查点可能非常占用资源，在现代系统上每 48MB 执行一次检查点将是严重的性能瓶颈。将 checkpoint_segments 设置为更大的值可以改善这种情况。除非你运行的是非常小的配置，否则你几乎肯定最好将其设置为至少 10，这也允许有意义地增加完成目标。

对于更多写入密集型系统，如今 32（每 512MB 检查点）到 256（每 4GB 检查点）的值很受欢迎。非常大的设置会使用更多的磁盘，并且会导致你的数据库恢复时间更长，因此在大幅增加之前，请确保你对这两件事都感到满意。通常，较大的设置（>64/1GB）仅用于批量加载。请注意，无论你为段选择什么值，除非你同时增加 checkpoint_timeout（在大多数系统上没有必要），否则你仍然会至少每 5 分钟执行一次检查点。

在系统开始努力进行下一次检查点时，检查点写入会稍微分散一些。您可以通过将 checkpoint_completion_target 参数增加到其有用的最大值 0.9（目标是在下一个检查点的 90% 到达时完成）而不是默认值 0.5（目标是在下一个检查点完成 50% 时完成）来进一步分散这些写入，从而降低平均写入开销。设置 0 会类似于过时版本的行为。默认值不直接设置为 0.9 的主要原因是，您需要比默认值更大的 checkpoint_segments 值才能使更广泛的传播正常工作。有关检查点调整的更多信息，请参阅检查点和后台写入器（您还将了解为什么调整后台写入器参数在实际应用中具有挑战性）。

自动清理

autovacuum 进程负责处理数据库内部的几个维护任务，这些任务是您真正需要的。通常，如果您认为需要关闭定期清理，因为它占用太多时间或资源，那么您做错了。几乎所有清理问题的答案都是更频繁地清理，而不是更少地清理，这样每个单独的清理操作都只需要清理较少的内容。

但是，在短期内禁用 autovacuum 是可以接受的，例如，当大量加载数据时。

日志记录

您可以记录许多可能对您重要或不重要的内容。您应该调查所有选项的文档，但这里有一些提示和技巧可以帮助您入门

log_destination 和 log_directory（以及 log_filename）：您将这些选项设置为多少并不重要，重要的是要了解它们可以为您提供提示来确定您的数据库服务器在哪里记录日志。最佳做法是尝试使所有服务器上的此设置尽可能相似。请注意，在某些情况下，启动数据库的 init 脚本可能会在用于启动数据库的命令行中自定义日志目标，从而覆盖配置文件中的设置（并导致在手动运行 pg_ctl 而不是使用 init 脚本时获得不同的行为）。

log_min_error_statement：您应该确保这至少在错误时生效，这样您将看到导致错误的任何 SQL 命令。应该是最近版本中的默认设置。

log_min_duration_statement：日常使用不需要，但它可以生成您系统上 “慢查询”的日志。

log_line_prefix：将信息追加到每行的开头。一个通用的良好建议是 '%t:%r:%u@%d:[%p]: ' : %t=时间戳，%u=数据库用户名，%r=连接来源主机，%d=连接目标数据库，%p=连接的 PID。一开始可能不明显 PID 在哪里有用，但它对于在将来尝试解决问题至关重要，因此最好从一开始就将其放入日志中。

log_statement：选项有 none、ddl、mod、all。在生产环境中使用 all 或 mod 会引入日志记录的开销。如果工作负载是选择密集型的，那么“all”的性能损失将是巨大的，而对于写入密集型的工作负载，性能损失将不那么明显。将 synchronous_commit 设置为 off 会导致更严重的性能下降。DDL 有时可以帮助发现由“牛仔 DBA”等方式在您的推荐流程之外进行的不良更改。

还有 pgbadger 等外部工具可以分析 Postgres 日志，请参阅监控以获取完整列表。

default_statistics_target

数据库软件会收集有关数据库中每个表的统计信息，以决定如何执行针对该表的查询。如果您没有获得良好的查询执行计划，特别是在更大的（或更多样化的）表上，您应该增加 default_statistics_target，然后再次分析数据库（或等待 autovacuum 为您完成）。

增加 default_statistics_target 可能有用，但 PostgreSQL 附带的默认值是一个合理的起点。

PostgreSQL 8.3 及更早版本

在 PostgreSQL 8.3 及更早版本中，增加提供的 default_statistics_target 通常会极大地改善查询计划。在 PostgreSQL 8.4 中，默认的 default_statistics_target 值从 10 增加到 100。在 8.4 中，该参数的最大值也从 1000 增加到 10,000。

work_mem

如果您进行了大量复杂的排序，并且有大量内存，那么增加 work_mem 参数将允许 PostgreSQL 进行更大的内存内排序，这将比基于磁盘的排序更快，这并不奇怪。

此大小适用于每个用户执行的每个排序，复杂查询可以使用多个工作内存排序缓冲区。将其设置为 50MB，有 30 个用户提交查询，那么您很快就会使用 1.5GB 的实际内存。此外，如果查询涉及对 8 个表的合并排序，则需要 8 倍的 work_mem。您需要考虑将 max_connections 设置为多少才能正确调整此参数的大小。对于数据仓库系统，在这些系统中，用户会提交非常大的查询，这种设置可以轻松地利用许多 GB 的内存。

log_temp_files 可用于记录排序、哈希和临时文件，这对于确定排序是否溢出到磁盘而不是适合内存很有用。您还可以使用 EXPLAIN ANALYZE 计划来查看排序是否溢出到磁盘。例如，如果您在 EXPLAIN ANALYZE 的输出中看到类似 Sort Method: external merge Disk: 7526kB 的一行，那么至少为 8MB 的 work_mem 将使中间数据保留在内存中，并且可能改善查询响应时间（尽管可能需要远远超过 8MB 才能完全在内存中进行排序，因为磁盘上的数据以更紧凑的格式存储）。

maintenance_work_mem

指定维护操作（例如 VACUUM、CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY）可以使用内存的最大数量。从 9.4 版本开始，它默认为 64 兆字节 (64MB)。由于数据库会话一次只能执行这些操作中的一个，并且安装通常不会有很多操作同时运行，因此将此值设置为明显大于 work_mem 是安全的。更大的设置可能会改善清理和还原数据库转储的性能。

wal_sync_method wal_buffers

在每次事务后，PostgreSQL 会将提交强制写入其预写日志的磁盘。这可以通过几种方式完成，并且在某些平台上，与提供的 wal_sync_method 相比，其他选项比保守的默认选项快得多。open_sync 是最常见的非默认设置，切换到支持它的平台，但默认使用其中一种 fsync 方法。请参阅调整 PostgreSQL WAL 同步以了解有关此主题的大量背景信息。请注意，open_sync 写入在某些平台上存在错误（例如 Linux），并且您应该（始终）在大量写入负载下进行大量测试，以确保您没有通过此更改使系统变得不稳定。可靠写入包含有关此主题的更多信息。

wal_buffers 默认为 shared_buffers 大小的 1/32，上限为 16MB（当 shared_buffers=512MB 时达到）。与早期 PostgreSQL 版本相比，调整默认设置的频率要低得多。

PostgreSQL 9.0 及更早版本

从 2.6.33 版本开始的 Linux 内核会导致 9.0.2 之前的 PostgreSQL 版本默认使用 wal_sync_method=open_datasync；在 2.6.32 内核之前，默认情况下始终选择 fdatasync。当与小写入和/或 wal_buffers 的小值结合使用时，这会导致性能显着下降。从 9.0.2 版本开始的 PostgreSQL 版本在运行在 Linux 上时，再次将 wal_sync_method 的默认值设置为 fdatasync。

在 PostgreSQL 9.0 及更早版本中，将 wal_buffers 从其极小的默认值（几个 KB）增加到更大的值对于写入量大的系统很有帮助。基准测试通常表明，对于一些大型系统，仅增加到 1MB 就足够了，并且鉴于现代服务器中 RAM 的数量，分配一个完整的 WAL 段（16MB，这里有用的上限）是合理的。

更改 wal_buffers 需要重新启动数据库。

constraint_exclusion

constraint_exclusion现在默认为新的选择partition. 这将仅为分区表启用约束排除，这几乎在所有情况下都是正确的做法。

max_prepared_transactions

此设置用于管理两阶段提交。如果您不使用两阶段提交（如果您不知道它是什么，您就不使用它），那么您可以将此值设置为 0。这将节省一些共享内存。对于具有大量（至少数百个）并发连接的数据库系统，请注意，此设置还会影响 pg_locks 中可用锁槽的数量，因此您可能需要将其保留为默认设置。文档和默认 postgresql.conf 中有一个关于分配多少内存的公式。

更改 max_prepared_transactions 需要重新启动服务器。

synchronous_commit

PostgreSQL 只有在具有电池备份的情况下才能安全地使用写入缓存。请参阅 WAL 可靠性以了解有关此主题的基本介绍。是的，真的；立即阅读它，它对于理解如果您希望数据库正常运行至关重要。

在您没有这样持久的写入缓存的情况下，您可能会限制每个客户端每秒大约 100 次事务提交（即使有大量客户端，每秒也可能只有 500 次）。

对于可以接受少量数据丢失以换取大幅提高每秒可以对数据库进行的更新次数的情况，请考虑关闭同步提交。这在您在磁盘控制器上没有电池备份的写入缓存的情况下特别有用，因为您可能每秒可以获得数千次提交，而不仅仅是几百次。

对于过时的 PostgreSQL 版本，您可能会发现有人建议您设置 fsync=off 以在繁忙的系统上加快写入速度。这很危险——断电会导致数据库损坏，无法再次启动。同步提交不会引入损坏的风险，这真的很糟糕，只会带来一些数据丢失的风险。

random_page_cost

此设置建议优化器，磁盘访问随机磁盘页面的时间是连续读取（成本为 1.0）时间的多少倍。如果您有特别快的磁盘，如通常在 SCSI 磁盘的 RAID 阵列中发现的那样，降低 random_page_cost 可能很合适，这将鼓励查询优化器使用随机访问索引扫描。有些人认为，在当前硬件上，4.0 始终过大；管理员通常将此值标准化为始终设置为 2.0 到 3.0 之间。在某些情况下，这种行为是早期 PostgreSQL 版本的遗留问题，在这些版本中，random_page_cost 过高更有可能破坏计划优化，而不是现在（并且设置在 2.0 或以下通常是必要的）。由于这些成本估算是仅仅是估算，因此尝试使用更低的值不会造成任何损害。

但这不应该是您开始寻找计划问题的地方。请注意，random_page_cost 在此列表中处于相当靠后的位置（实际上位于末尾）。如果您获得了糟糕的计划，这应该不是您首先要查看的内容，即使降低此值可能有效。相反，您应该首先确保 autovacuum 正常工作，您正在收集足够的统计信息，并且您已针对服务器正确调整了内存参数——所有这些都是上面讨论过的内容。在完成所有这些更重要的操作后，如果您仍然获得了糟糕的计划，那么您应该查看降低 random_page_cost 是否仍然有用。