作者：Ben Packer 和 Carlota Soto
当数据库表不断增长并且性能开始受到影响时，就该对 PostgreSQL 性能数据库进行一些微调了。
在本系列的第一部分中，我们已经讨论了 CPU 和内存大小调整，包括有关监控用例最佳大小的技巧。在第二部分中，我们将深入探讨可以控制的关键 PostgreSQL 参数，以优化数据库性能，并提供完整的配置建议。
让我们开始吧！
max_worker_processes 参数决定了 PostgreSQL 可以启动的最大工作进程数。这不仅包括查询并行，还包括其他辅助进程，如逻辑复制和后台任务。适当地设置此参数将有助于确保有足够的进程可用于这些任务，而不会使系统过载。
对于普通 PostgreSQL，一个好的通用建议是将 max_worker_processes 设置为与可用内核或 CPU 的总数相匹配。但是，根据您的工作负载，可能建议使用更大的数字。例如，如果您正在使用 Timescale（它大量使用并行性），我们建议使用以下公式来设置 max_worker_processes
max_worker_processes = 3 + timescaledb.max_background_workers + max_parallel_workers
这为检查点进程、预写日志 (WAL) 写入器、vacuum 进程、后台工作线程等进程留出了空间。您的 max_worker_processes 设置最终可能是可用 CPU 的 4 倍左右。
我们将在后面的文章中讨论 Timescale 特定的参数，如 timescaledb.max_background_workers。
此参数指定可用于支持并行查询的并行工作线程的最大数量。并行查询执行可以显著加快某些类型操作的处理速度，例如顺序扫描、连接和聚合。此参数可确保有效地使用并行性，而不会耗尽资源。
与之前类似，一个好的默认值是将 max_parallel_workers 设置为等于可用的 CPU 数量。考虑到现代多核 CPU 固有的并行处理能力，这种方法通常与典型的工作负载非常吻合。
但是，根据查询和工作负载的具体性质，您仍然可以尝试调整它。例如，如果您的机器有八个内核，但大多数查询都没有从并行性中受益，那么将所有八个内核都分配为并行工作线程可能就太过分了。相反，增加 max_parallel_workers 可以提高可以有效利用并行性的大型查询的查询性能。
如果您要将 max_parallel_workers 提高到建议值以上，请逐步谨慎地进行，因为过度激进地增加它可能会导致资源争用或增加等待时间。
此参数控制单个查询执行节点可以使用的并行工作线程数。默认值为 2，但 CPU 数量的四分之一到二分之一是一个不错的选择。在 Timescale 中，我们从 2 开始，随着 CPU 的增加逐渐增加，以匹配 CPU 的一半。
与 max_paralell_workers 类似，如果您有可以从并行性中受益的 CPU 密集型查询，则将 max_parallel_workers_per_gather 设置得更高可能会使这些查询运行得更快—PG Mustard 的这篇博文 深入探讨了注意事项。
请考虑，允许单个查询使用大量内核可能会导致没有足够的 CPU 来快速响应较小的查询。如果您希望确保应用程序流量得到优先处理，并且可以处理需要更长时间的更强大的查询，则应保持 max_paralell_workers_per_gather 较低或考虑完全禁用并行性。
max_parallel_maintenance_workers 控制 PostgreSQL 中可用于维护操作的并行工作线程数。默认值也是 2。
此设置会影响维护任务，如 CREATE INDEX 和某些 VACUUM 操作。如果这类任务运行缓慢，并且 CPU 有可用空间，则增加 max_parallel_maintenance_workers 超过建议值可能会有所帮助，但不建议将其提高到 CPU 的三分之一以上，以确保为常规查询处理留出资源。
但需要了解的是， max_parallel_maintenance_workers 不会影响自动清理进程。虽然它会影响手动清理操作，但自动清理系统有其独立的设置，因此如果您想优化此进程，可能需要微调 autovacuum_max_workers 。接下来我们将对此进行介绍。
一旦您的 PostgreSQL 数据库不断增长（尤其是在写入负载较高的情况下），通过删除无效行、防止不必要的膨胀以及保持最佳性能来维护表的健康状况并回收存储空间就变得非常重要。这就是自动清理进程的用武之地。
autovacuum_max_workers 参数决定了可以并发运行的自动清理进程的最大数量，每个工作进程通常在其指定的内核上运行（尽管如果有多个自动清理工作进程同时处于活动状态，则它们可以共同使用多个内核）。默认值为 3。
此值适用于许多工作负载，但如果您的数据库增长非常大，您可能需要增加此值。但是，请确保根据您的 CPU 和 I/O 资源进行设置，因为 autovacuum_max_workers 参数设置得越高，自动清理进程消耗的资源就越多。在 Timescale 中，默认情况下我们将其设置为 10。
在扩展 PostgreSQL 时，更深入地了解膨胀和清理非常重要，因为对此领域的进一步优化可以帮助您保持大型数据库的良好性能（并控制成本）。请继续关注有关此主题的更多文章。
max_connections 决定了允许连接到数据库服务器的最大并发连接数。这包括所有连接，无论是来自超级用户、应用程序、后台进程还是交互式用户。PostgreSQL 中 max_connections 的常见默认值为 100。特别是在 Timescale 中， max_connections 在最小的计算计划中设置为 25，并随着 CPU 数量的增加而增加到 500。
如果您的数据库需要许多短期连接，则默认的 max_connections 值可能不足。但与其提高它，更好的解决方案是使用连接池，它可以消除数据库端打开和关闭许多短期连接的开销。在 Timescale 中，我们提供 基于 pgBouncer 的连接池。
如果您决定提高 max_connections，请考虑 PostgreSQL 中的每个连接通常都需要额外的内存，大约在 5-10 MB 范围内。如果您决定增加 max_connections，请确保您的系统有足够的内存来处理额外的需求。
我们已经在本系列的第 I 部分中讨论过 shared_buffers。正如我们所讨论的，此参数控制 PostgreSQL 将为其页面缓存使用多少内存。
作为一般的经验法则，您需要将 shared_buffers 设置为系统总 RAM 的 25% 左右
shared_buffer = 25% 的 RAM
这是一个很好的起点，但并非一成不变。此 25% 的比率可以根据特定的用例和工作负载而变化。例如，如果您主要进行大量读取操作，则增加 shared_buffer 超过建议的 25% 可能会获得更好的性能，但请谨慎操作。通常不建议将其提高到 RAM 的 40% 以上。
PostgreSQL 缓存和操作系统缓存之间也存在（复杂的）关系。 正如我们在第 I 部分中提到的那样，PostgreSQL 维护着自己的缓存（由 shared_buffers 定义），但操作系统也有一个文件系统缓存来保留从磁盘读取的文件。这两种缓存共存，并用于加速数据检索。
PostgreSQL 缓存和操作系统缓存之间存在微妙的关系。当 PostgreSQL 读取或写入数据时，它首先与 shared_buffers 交互。当这些缓冲区中不存在数据或刷新它们时，就会使用操作系统缓存。
如果 shared_buffers 设置得太低，PostgreSQL 将更多地依赖操作系统缓存，这在性能方面可能效率不高；如果 shared_buffers 设置得太高，您可能会在 PostgreSQL 中看到内存不足错误，甚至会影响主机机器的稳定性。这就是为什么通常建议从较低的 shared_buffer 值（即 RAM 的 25%）开始，然后谨慎地从该值开始提高的原因。
我们还在第 I 部分中提到了 work_mem。此参数控制为查询中的每个操作（例如排序或散列）分配的内存量。如果您的繁重查询未按预期执行，则可能是时候微调 work_mem 了。如果您将 PostgreSQL 用于分析，则这一点尤其重要，因为复杂的操作通常需要更大的内存占用量。
在 Timescale 中，以下是我们对 work_mem 的建议
work_mem = (25% 的 RAM) / max_connections
此外，如果您是聚合的重度用户，请查看 Timescale 的 连续聚合，它将帮助您更高效地执行分析查询。
EXPLAIN ANALYZE 命令是您理解此设置的有用工具，因为它提供了有关写入磁盘的数据量的相关信息。例如，请考虑以下查询
EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id ORDER BY orders.order_date;
输出如下
...
Hash Join (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Hash Cond: (orders.customer_id = customers.id)
...
-> Seq Scan on orders (cost=... rows=... width=...) (actual time=... rows=... loops=1)
...
-> Hash (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buckets: 4096 Batches: 4 Memory Usage: 40kB
...
Sort Method: external merge Disk: 7208kB
...
Sort Method 表示使用了外部合并，磁盘占用量为 7208 kB，这表明正在排序的数据对于 work_mem 设置来说太大。 Hash 操作使用了四个批次，这表明它也溢出到了磁盘。
最后三个注意事项
如果多个操作并发运行，则每个操作将最多可以使用 work_mem 的内存——请记住，使用的总内存可能是 work_mem值的许多倍，因为这是一个按操作设置的参数。
注意不要将 work_mem 设置得太高，否则会出现内存不足错误。我们将在本文结尾处回到这一点。
就像 max_parallel_workers_per_gather 一样，不要使 work_mem 过大，请考虑对于某些查询来说，使用磁盘以较慢的速度运行以保留更多内存用于其他用途可能是一个很好的折衷方案。或者至少在增加 work_mem之前，确保不能以其他方式优化这些查询。
maintenance_work_mem 是另一个重要参数，因为它决定了分配给 PostgreSQL 维护操作的内存量，例如 VACUUM、 CREATE INDEX 和 ALTER TABLE，以及使用 pg_restore 等工具进行的数据库恢复活动。内存不足会导致这些维护任务速度变慢，从而可能影响数据库的整体性能。如果您的维护操作执行时间过长，则表明需要微调 mainenance_work_mem。
maintenance_work_mem 的默认值为 64 MB。在 Timescale 中，我们建议将其设置为高于 work_mem 的值，因为清理或转储/恢复等操作通常需要更多内存，并且比常规查询操作的频率要低。作为一般的经验法则，您可以将 maintenance_work_mem 设置为系统总 RAM 的 5% 左右
maintenance_work_mem = 0.05 * 总 RAM
配置此设置时，务必考虑同时运行的多个维护操作。例如，如果在高峰时段同时发生多个 VACUUM 操作，请确保您的系统有足够的内存来处理所有这些并发操作的 maintenance_work_mem，同时注意为 shared_buffers 和 work_mem 分配的内存。如果设置过于激进，则可能会遇到内存不足错误。
关于 pg_restore 的最后一点说明：您可能希望在还原期间临时增加 maintenance_work_mem 以获得更好的性能。但请记住，在还原完成后恢复到正常值。
最后，让我们介绍一下 max_locks_per_transation。此参数确定单个事务可以持有的最大锁数。 在 PostgreSQL 中，锁用于控制对数据库资源的访问并确保数据一致性； max_locks_per_transaction 确保系统已预先分配了足够的内存来处理这些锁。
默认情况下，PostgreSQL 中的 max_locks_per_transaction 设置为 64。但是，对于具有复杂事务的数据库，或者在使用使用更多锁的扩展或功能（例如 PostGIS 或 Timescale）时，这可能不够用。您可以在峰值负载或复杂操作期间通过 pg_locks 检查当前锁的使用情况，以了解当前锁的数量。
在 Timescale 中，我们建议将 max_locks_per_transaction 设置得稍高一些，使其达到 超表中合理预期最大分区（块）数的两倍。这是因为 Timescale 通常使用的锁至少与超表中的块数相同，如果使用索引，则使用的锁数是其两倍。
其中一些关键参数与内存相关。如果配置错误，您可能会看到 错误：共享内存不足。让我们来看一个简短的清单，其中列出了可能导致出现内存不足错误的最常见错误配置原因
如前所述，每个修改数据或架构的事务都需要锁。出现内存不足错误的最常见原因之一是 max_locks_per_transaction 设置得太低。在峰值负载期间使用 pg_locks 检查锁的数量。
如果您怀疑问题出在并行性上，可以查看 max_parallel_workers。如果查询中存在高度并行性，则可能会导致动态共享内存耗尽 - 您可能需要考虑将 max_parallel_workers 减少到 CPU 数量减去一或两个。您还可以考虑减少 max_parallel_workers_per_gather 以限制单个查询的并行度，甚至提高 shared_buffers - 但不要太多，否则也会耗尽内存。
强调一下 shared_buffers：正如我们之前所说，如果将此参数设置得太高，也可能会看到内存不足错误。请记住，建议不要将其提高到系统总 RAM 的 40% 以上，尤其是在 PostgreSQL 不是计算机上唯一运行的应用程序的情况下。
导致内存不足错误的另一个常见原因是 max_connections。如果 max_connections 设置得太高，并且 PostgreSQL 实例遇到并发连接激增，则可能会耗尽系统的共享内存。解决此问题的办法取决于您的应用程序是否确实需要大量连接。如果需要，请考虑使用 连接池。如果不需要，请检查保持空闲状态的打开连接。
在结束之前，快速提醒一下：在 Timescale 中创建数据库时，配置值将根据服务的计算和存储设置采用最佳实践（并且会在您升级实例时发生变化），但如果需要微调它们， 您可以通过导航到“操作”>“数据库配置”轻松完成
您可以通过 Timescale UI 中的“数据库配置”屏幕轻松修改关键的 PostgreSQL 参数
在本系列的前两篇文章中，我们讨论了数据库大小以及需要监控和微调的关键参数。 在下一篇文章中，我们将讨论 PostgreSQL 配置的一个关键方面：索引。
优化索引可以极大地提高 PostgreSQL 的性能，尤其是在表变大之后。 让我们开始吧！