调整您的 PostgreSQL 服务器
作者：Greg Smith、Robert Treat 和 Christopher Browne
PostgreSQL 附带了基本配置，这些配置在很大程度上是为了兼容性而不是性能而设计的。默认参数很可能远低于您的系统所能承受的水平。我们不会详细介绍（您可以在 GUC 三小时之旅 中找到详细信息），而只提供一些基本内容的快速概述，涵盖了 PostgreSQL 新手最常遇到的一些问题。在快速阅读本简介后，您可以点击相应部分中的每个参数名称直接跳转到 PostgreSQL 手册（英文版）中的相关文档，以了解更多详细信息。
基本配置信息
PostgreSQL 的设置可以通过多种方式进行管理，但通常您可能更愿意在 postgresql.conf 文件中更新它们。可用的选项会因版本而异；最终的列表位于 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：需要服务器重启，或者执行 'kill -HUP'（通常为 -1）、pg_ctl reload 或 'select pg_reload_config();'
- 用户：可以在每个单独的会话中设置，对该会话生效
- 内部：在编译时设置，无法更改，主要用于参考
- 后端：将在启动会话之前设置
- 超级用户：仅超级用户可以在服务器运行时应用
大多数情况下您会使用第一个级别，但第二个级别在您不想停止服务器的情况下非常有用。第三个级别在特殊情况下很有用。您可以通过查看 pg_settings 视图中的“context”列来了解每个选项的级别。
有关 postgresql.conf 的重要说明
- 您应该可以在 $PGDATA/postgresql.conf 中找到它，查看符号链接以及其他可能的技巧。
- SHOW config_file 会告诉您配置文件的位置。
- 以 # 开头的行是注释，不会生效。对于新的数据库来说，这意味着正在使用默认值，但在正在运行的系统中，不要取消注释！在 8.3 之前的版本中，注释掉一行不会恢复到默认值。即使在更高版本中，postgresql.conf 中的更改也需要重启/重新加载才能生效；因此，系统运行的配置可能与该文件中配置的不一致。
- 如果同一个值被声明多次，最后一次声明的值将生效。
查看当前配置
- 查看 postgresql.conf 文件。如果您遵循良好的做法，这很有用，但它并不一定准确！
- show all、show <setting> 会显示某个变量的当前值。注意会话特定值的更改。
- select * from pg_settings 会显示您在本地修改的会话特定更改。
listen_addresses
默认情况下，PostgreSQL 仅响应来自 localhost 的连接。如果您希望您的服务器可以通过 TCP/IP 网络从其他系统访问，则需要更改 listen_addresses 的默认值。最常见的方法是将其配置为以下方式：
listen_addresses = '*'
然后，通过 pg_hba.conf 文件控制哪些用户无法连接。
max_connections
max_connections 指定允许的最大客户端连接数。这对以下一些参数（尤其是 work_mem）非常重要，因为每个客户端都可以占用内存资源，因此最大客户端数量会影响潜在的最大内存使用量。通常情况下，在性能良好的硬件上，PostgreSQL 可以支持数百个连接。如果您希望拥有数千个连接，则需要考虑使用 连接池软件 来减少连接开销。
shared_buffers 参数的配置决定了 PostgreSQL 用于缓存数据的内存量。默认情况下，它的值很低，因为在某些平台（例如旧版本的 Solaris 和 SGI）上，较高的值需要进行侵入式操作，例如重新编译内核。如果您有一个 RAM 大于或等于 1GB 的系统，则合理的初始值是该内存的 1/4。如果 RAM 更小，则需要根据操作系统仔细计算这个值，最常见情况下大约为 15%。
请注意，在 Windows 和旧版本的 PostgreSQL（早于 8.1）中，较高的 shared_buffers 值并不有效，将它保持在较低的水平（大约 50,000，可能更低）并充分利用操作系统缓存会获得更好的效果。
类似地，增加操作系统的内存量将允许您设置更高的 shared_buffers 值。如果您设置的值超过了系统所能承受的范围，您会收到类似以下错误消息：
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
这应该设置为操作系统可用于磁盘上的中间内存的估计量，在考虑了操作系统使用、分配给 PostgreSQL 内存和其他应用程序的内存之后。它是一个指南，指示了预计有多少内存可用于操作系统缓存缓冲区，而不是分配的内存！此值仅由查询规划器用于考虑哪些计划可能适合内存，哪些计划可能不适合。如果设置过低，索引将无法按照预期的方式使用。
将 effective_cache_size 设置为总内存的一半，应该是一个更保守的选择，而设置成总内存的 3/4 则是一个更激进但仍然合理的选项。建议您根据操作系统的统计信息来选择此值。在 Unix/Linux 系统中，您可以将 free 或 top 命令返回的 free + cached 的总和作为估计值。在 Windows 中，请查看任务管理器选项卡中的“系统缓存”的大小。更改此值不需要重启服务器（HUP 或 RELOAD 即可）。
checkpoint_segments checkpoint_completion_target
PostgreSQL 将新事务写入数据库的称为 WAL 段的文件中，这些文件的大小为 16MB。每当写入 checkpoint_segments 指定的值（默认值为 3）时，就会发生一个“检查点”。检查点可能会占用大量资源，在现代系统中，每 48MB 进行一次检查点可能会导致瓶颈。将此值设置得略高一些可以缓解这个问题。如果您运行的是小型配置，则应该至少将其设置为 10，以便能够达到目标。
对于高写入量系统，从 32（每个 512MB 进行检查点）到 256（每个 128GB 进行检查点）的范围都是流行的选择。非常大的系统需要大量磁盘空间，恢复时间会更长，因此您需要选择一个适合自己的范围。通常情况下，较高的值（>64/1GB）用于高吞吐量负载。无论您选择多少段，至少需要每 5 分钟进行一次检查点，除非您提高 checkpoint_timeout（在许多系统中不需要这样做）。
从 PostgreSQL 8.3 开始，检查点写入在开始处理下一个检查点时会稍微延迟一些。您可以通过将 checkpoint_completion_target 参数增加到其最大值 0.9（目标是在下一个检查点开始前完成 90%）来分散新写入，降低平均写入量，而不是使用默认值 0.5（在下一个检查点开始时完成 50%）。将其设置为 0 类似于早期版本中的行为。0.9 不是默认值的主要原因是，需要较高的 checkpoint_segments 值才能使这种分散机制正常工作。有关检查点改进的更多信息，请查看 检查点和后台写入程序（您将了解如何改进后台写入程序参数，特别是 8.2 或更早版本，它们会带来挑战）。
autovacuum max_fsm_pages,max_fsm_relations
注意：Vacuum 实际上指的是“清理”。VACUUM 进程实际上是对已标记为已删除或已修改的死元组进行清理，因为数据库引擎不会立即从物理部分中删除它们，以避免给正常操作带来过大的负担。
自动 Vacuum 进程（autovacuum）会执行数据库维护的一系列操作，您需要这些操作。从 8.3 开始，它默认处于启用状态，您应该保持这种状态。在 8.1 和 8.2 中，您需要启用它。请注意，在这些旧版本中，您需要配置变量才能使其产生更大的影响；如果您的数据库非常大或对数据的更新非常频繁，默认配置可能无法完全满足需求。
通常情况下，如果您认为需要禁用它是因为它占用了过多资源，这意味着您的配置存在问题。解决 Vacuum 问题的答案是更频繁地执行它，而不是更少地执行它，这样每次 Vacuum 操作的维护量就会减少。
那么你应该增加 max_fsm_pages 和 max_fsm_relations 的值，直到满足你的需求。空闲空间映射（Free Space Map）用于跟踪死元组（行）的位置。只有当死元组可以在 FSM 中列出时，你才能从 VACUUM 查询中获得有效的释放。因此，如果你不打算频繁地运行 VACUUM，并且预计会有很多更新，你应该确保这些值足够大（请记住，这些值是针对整个集群的，而不是针对数据库的）。设置 max_fsm_relations 应该很容易，因为通常情况下，max_fsm_pages 没有被设置为足够高的值才是最常见的问题。一旦 FSM 满了，VACUUM 将被禁用，无法继续跟踪死页面。在数据库活动频繁的情况下，它需要设置到 1000 以上... 另外请记住，更改这些值需要重启数据库，因此最好设定一个合适的范围。
如果你在数据库上运行 VACUUM VERBOSE，它会告诉你使用了多少页面和关系（以及在 8.3 版本以上，它们分别的限制值）。例如
INFO: free space map contains 5293 pages in 214 relations DETAIL: A total of 8528 page slots are in use (including overhead). 8528 page slots are required to track all free space. Current limits are: 204800 page slots, 1000 relations, using 1265 kB.
如果你发现你的值非常低，那么你需要对你的系统进行更彻底的 VACUUM，可能还需要重新索引并执行 vacuum full。如果你的页面范围接近上限，通常的做法是将其设置为当前值的 2 倍，当你的值非常高时，你可以尝试稍微小一些的增长比例（例如百万级）。对于 'max relations' 的值，请注意该值包含集群中的所有数据库。
另一个需要注意的地方是 autovacuum_max_freeze_age。当数据库接近该值时，它会对数据库中未被该过程处理过的每个表进行 vacuum 操作。对于一些活动不高的系统来说，这没什么问题，但在一些拥有大量未清理表的系统中，可能会导致更高的发生率（即使系统处于转储/还原状态）。这意味着即使你的系统设置了合适的 fsm 值，一旦你的系统开始对额外表进行 vacuum 操作，你的旧 fsm 将不再适用。
日志记录
有很多内容可以记录，而这些内容对你来说可能重要，也可能不重要。你应该查看文档中所有选项的说明，但这里有一些入门技巧。
- log_destination & log_directory (& log_filename)：设置这些选项并不是那么重要，重要的是要了解它们可以提供有关服务器日志记录位置的信息。最佳做法是尝试在所有服务器上保持一致。在某些情况下，启动脚本会使用命令行中指定的日志记录目标启动数据库，覆盖 postgresql.conf 中的设置（因此使用 pg_ctl 的行为可能与使用启动脚本不同）。
- log_min_error_statement：你应该确保至少设置为 error 等级，以便查看哪些语句出现了错误。在较新的版本中，将它保持默认值即可。
- 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” 会影响性能。 “ddl” 可以帮助你发现由非预期流程（例如“牛仔 DBA”）进行的更改。
default_statistics_target
数据库软件会收集每个数据库中每个表的统计信息，以决定如何执行对它们的查询。默认情况下，它不会收集太多信息。如果在某些大型表（或复杂表）上没有得到很好的执行计划，特别是那些很长时间没有更新的表，你应该增加 default_statistics_target 的值，然后重新运行 ANALYZE 命令（或者等待 autovacuum 自动执行）。许多人认为，在更现代的硬件上，default_statistics_target 的默认值应该提高到 100（从 10 增加），这可以降低执行错误计划的可能性，而且代价只是增加一些后台活动。
work_mem maintainance_work_mem
如果你执行很多复杂的排序操作，并且有足够的内存，增加这个变量可以使 PostgreSQL 在内存中进行更宽松的排序，这显然比基于磁盘的排序效率更高。
这个大小适用于每个用户的排序操作，而复杂的查询可能会使用多个内存缓冲区来进行排序。假设你将它设置为 50MB，有 30 个用户正在运行查询，那么实际使用的内存将达到 1.5GB。此外，如果一个查询涉及对 8 个表进行排序并联接，那么它需要 8 倍的 work_mem。你应该考虑你的 max_connections 设置，以便正确设置 work_mem。对于数据仓库，用户可能在大型查询中使用大量内存，在这种情况下，可能需要设置为几个 GB。
maintenance_work_mem 用于 vacuum 操作。使用非常高的值不会带来很大帮助，因为你应该在 vacuum 运行时保留该内存，以便在它真正需要的时候可以使用。在这种情况下，256mb 对于较高的值来说是一个合理的选择。
在 8.3 版本中，你可以使用 log_temp_files 来检查排序操作是否使用了磁盘而不是内存。在更早的版本中，你需要通过查看 $PGDATA/base/
wal_sync_method wal_buffers
在每次事务提交后，Postgresql 会强制写入 WAL 以保证事务的持久性。这可以通过多种方式实现，在某些平台上，其他选项可能比默认的保守选项更快。open_sync 是其他选项中最常见的，它可以在支持它的平台上使用，但默认情况下，它使用的是 fsync 方法之一。你可以查看 Tuning PostgreSQL WAL Synchronization 获取更多信息。请注意，open_sync 在某些平台上存在一些错误，因此你应该（像往常一样）进行大量的大规模插入测试，以确保它不会使你的系统不稳定。
将 wal_buffers 从其默认的几 KB 值增加到更大的值，可以帮助解决大型插入操作。统计数据表明，将它增加到 1MB 就足以满足大型系统。更改此值需要重启数据库。
constraint_exclusion
如果你打算使用表分区，你需要将此值设置为 'on'。除非它会导致查询规划器产生过高的开销，否则建议在非分区场景中将其保持为 'off'。
max_prepared_transactions
此值用于处理 两阶段提交 事务。如果你没有使用它（并且不知道它是什么，或者没有使用它），你可以将其设置为 0。这将节省一些共享内存。对于具有大量（至少 100 个）并发连接的数据库系统，要小心，因为此值还会影响可用的 pg_locks 锁位置数量，因此你可能希望将其保留为默认值。你可以参考 文档 中的公式，了解它如何影响内存使用，以及 postgresql.conf 中的默认值。
更改 max_prepared_transactions 需要重启服务器。
synchronous_commit
PostgreSQL 只有在有电池备份的情况下才能使用安全的异步写入。查看 WAL可靠性 获取有关此主题的介绍。认真阅读！现在就阅读它，了解如何让你的数据库正常工作。
在没有持久异步写入的情况下，你的数据库每秒每客户端只能执行大约 100 个事务提交（即使有许多客户端，每秒可能也只有 500 个）。对于那些可以接受少量数据丢失以换取高吞吐量（即每秒能进行多少更新）的场景，可以考虑使用非同步提交。特别是在没有磁盘控制器电池备份的情况下，它非常有用，因为你可能每秒可以进行数千次提交，而不是数百次。
它在 PostgreSQL 8.3 版本中引入。对于更早版本的 PostgreSQL，你可能会发现有人建议将 fsync=off 设置为提高高负载系统上的写入速度。这很危险！断电会导致数据库损坏，无法重启。这种机制不会带来这些风险，只会导致少量数据丢失。
random_page_cost
这个值告诉优化器你的磁盘读取一个随机磁盘页面的时间，与顺序读取多个页面（成本为 1.0）相比。如果你有快速的磁盘，比如常见的 SCSI RAID 磁盘，可以将其设置为较小的值，这将鼓励优化器使用随机访问扫描。有些人认为 4.0 对于当前的硬件来说太高了，但是标准化在 2.0 到 3.0 之间的值并不罕见。在某些情况下，这种行为是 PostgreSQL 早期版本的一个遗留问题，当时 random_page_cost 的值很高，这会干扰优化器计划（并且经常需要将其设置为 2.0 或更高）。因为这些成本估计只是估计，因此使用较小的值不会有负面影响。
但这不应该成为你首先要查找计划问题的地方。请注意，random_page_cost 在这个列表中排得很低（实际上是在最后）。如果你遇到了糟糕的计划，不应该首先考虑它，即使降低它的值也可能有效。但是，你应该确保 autovacuum 工作正常，它一直在收集统计信息，并且你已正确设置了服务器的内存参数（前面提到的所有事项）。在你检查了所有这些重要事项之后，如果你仍然遇到糟糕的计划，只有在那时才考虑降低它的值是否仍然对你有用。
