调整 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 将显示特定于会话的更改，例如您在本地进行的修改。

max_connections

max_connections 用于限制最大客户端连接数。这对于某些后续参数（特别是 work_mem）非常重要，因为有一些内存资源可以被客户端占用，因此最大客户端数量可以暗示最大可能的内存使用量。一般情况下，在良好硬件配置下，PostgreSQL 可以处理数百个连接。如果您需要处理数千个连接，则应该考虑使用连接池软件来减少连接开销。

shared_buffers

shared_buffers 参数的设置决定了分配给 PostgreSQL 用于缓存数据的内存量。默认值较低，因为在某些平台（如旧版本的 Solaris 和 SGI）上，设置较高的值需要进行侵入性的操作，例如重新编译内核。如果您拥有 1GB 或更多内存的系统，则合理的初始值是该内存的 1/4。如果您拥有更少的内存，则需要根据操作系统仔细计算该值，在大多数情况下接近 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 中，查看任务管理器中的“系统缓存”选项卡。

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 或更早的版本，要正确执行此操作很困难）。

自动清理 max_fsm_pages,max_fsm_relations

注释：真空意味着“清空”。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 full。如果你接近页面的限制，通常的做法是将当前值翻倍，然后在获得非常高的值（百万级）之后，可能以更小的比例增加。对于 'max_relations' 的值，请注意，此值包含集群中的所有数据库。

另一个需要注意的地方是 autovacuum_max_freeze_age。当一个数据库接近此值时，它将对数据库中尚未经过此过程的每个表执行 Vacuum。在一些不太活跃的系统中，这不会造成太大的影响，但在拥有大量未清理表的系统中，可能会导致更多问题（即使系统处于 dump/restore 状态）。这意味着，即使在一个 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/<db oid>/pgsql_tmp 中使用了多少空间来监控它们的大小。你可以在 EXPLAIN ANALYZE 中看到磁盘排序是如何发生的。例如，如果你看到一行显示 "Sort Method: external merge Disk: 7526kB"，你就知道至少 8mb 的 work_mem 可能会提高查询执行速度（因为它是在 RAM 中排序而不是写入磁盘）。

wal_sync_method wal_buffers

在每次事务完成后，Postgresql 会强制提交以更新 WAL。这可以通过多种方式实现，在某些平台上，其他选项可能比默认的保守选项更快。open_sync 是除默认选项之外最常见的选项之一，它在支持它的平台上被使用，但默认情况下它是一种 fsync 方法。你可以查看 Tuning PostgreSQL WAL Synchronization，以了解这方面的更多细节。请注意，open_sync 在某些平台上存在一些错误，你应该（始终）进行多次大量插入测试，以确保它不会使你的系统不稳定。

将你的 wal_buffers 从其默认的小值（几个 KB）增加到几个 MB，应该有助于大型插入操作的系统。统计数据表明，将它增加到 1MB 对大型系统来说已经足够了。更改此值需要重启数据库。

constraint_exclusion

如果你打算使用表分区，你需要将此值设置为 'on'。除非这会导致查询规划器出现过度开销，否则建议你除了这种场景之外，始终将其设置为 'off'。

max_prepared_transactions

此值用于处理 两阶段提交（事务）。如果你没有使用它（并且不知道它是什么，也不使用它），你可以将此值设置为 0。这将节省一些共享内存。对于拥有大量（至少 100 个）并发连接的数据库系统，请小心，因为此值还会影响可用于 pg_locks 中的锁位的数量，因此你可能希望将其保留为默认值。在文档中以及 postgresql.conf 的默认值中，有一个公式可以告诉你它占用了多少内存。

更改 max_prepared_transactions 需要重启服务器。

synchronous_commit

PostgreSQL 只有在有后备电池的情况下才能使用安全的写回缓存。请查看 WAL reliability，以了解这方面的介绍。认真阅读！现在就阅读它，以了解你的数据库必须如何正确运行。

在没有持久写回缓存的情况下，你可能会被限制在每秒每个客户端约 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 正常工作，并且它正在定期收集统计信息，以及您是否已正确设置服务器的内存数量参数（所有上述事项）。在您检查完所有这些重要事项后，如果您仍然遇到糟糕的计划，只有在那之后您才应该检查降低此值是否对您仍然有用。