作者：Ben Packer 和 Carlota Soto
您发布了产品，获得了一些用户，现在... 事情变得 更慢了。过去在 100 毫秒内运行的查询现在需要一秒钟，您的团队（过去六个月都在编写应用程序代码）面临着一系列关于 PostgreSQL 性能优化和优化的新问题。
扩展 PostgreSQL 可能具有挑战性，但您无需惊慌。有很多方法可以扩展 PostgreSQL 数据库。您可能需要
更多 CPU 功率或内存
分区策略
更改 PostgreSQL 设置，例如 shared_buffers 或 max_parallel_workers
优化索引
更新数据库架构
首先，恭喜！这些都是很好的问题：它们意味着您正在扩展 PostgreSQL 数据库。
其次，不要惊慌，也不要疯狂地搜索要使用的其他数据库。PostgreSQL 是 最受欢迎的数据库 ，也是应用程序的最佳基础，但如果您要进行扩展，则可能需要额外注意。
在本文中，我们将介绍数据库的 CPU 和内存大小调整。与数据库一样，我们无法保证万无一失的准则，在没有直接实验和观察的情况下，这些准则也无法保证有效：真正的常量很少，而且总是有需要注意的地方。不过幸运的是，您可以系统地遵循一些原则，我们将在本系列中探讨这些原则。
查看本系列中的其他文章，这些文章涵盖了分区策略、PostgreSQL 参数、索引优化和架构设计。
您可能会想通过投入更多资源（也就是金钱）来解决所有性能问题，但不要这样做。这是一种不可持续的解决方案，会在未来让您的账单飞涨。因此，让我们首先来分析一下可用数据库资源（CPU 和内存）与性能之间的关系。
要正确调整数据库 CPU 资源的大小，您需要估计需求并在系统到位后对其进行监控。让我们先来看一个可靠的估计，了解 CPU 资源如何影响数据库吞吐量。
运行数据库的（通常是虚拟）机器有三个主要组件：内存 (RAM)、CPU 和磁盘 I/O。本文将重点介绍 CPU 和内存。
CPU 是数据库中的核心工作单元。数据库和客户端之间的数据传输、索引扫描、数据联接和 WHERE 子句的评估等操作都依赖于 CPU。通常情况下，在没有内存或磁盘限制的情况下，PostgreSQL 的读取吞吐量与可用核心数量成正比。
CPU 还处理数据库中的并发性。PostgreSQL 可以在每个核心上运行多个查询。不过，通常情况下，您应该使用以下公式粗略估计每秒查询数
💡每秒查询数 (QPS) ≈ (1 / 平均查询运行时间（以秒为单位）) × 核心数量
此估计将平均查询时间、每秒查询数和可用 CPU 核心数量关联起来。该公式假设每个核心一次可以处理一个查询，并且其他因素（如内存或磁盘访问）不是瓶颈。您可以使用它来估计目标 CPU 容量或可用吞吐量。
例如
如果您预计系统需要每秒处理 320 个查询，并且知道平均查询需要 50 毫秒，那么您将需要大约 16 个核心（假设线性扩展）。
相反，如果您有固定数量的可用核心（例如 8 个）并且知道平均查询时间，则可以估计系统可以有效处理的负载。
请记住，这只是一个粗略的估计。由于许多其他因素，实际性能会有所不同。在大多数情况下，您可以预期 QPS 高于此公式提供的值。
一旦您有足够的 CPU 功率来处理吞吐量，就需要监控系统以确保其正常工作。
如果您已经对 CPU 进行了适当的大小调整，但数据库仍然运行缓慢，那么以下是一些迹象，表明您可能需要增加计算机上的核心数量
观察到的 CPU 使用率持续居高不下。您应该始终关注当前和历史 CPU 利用率信息，以便将利用率与应用程序活动相关联。如果您使用的是 Timescale，则可以通过控制台中的指标面板访问此信息。请记住，增加 CPU 并不总是提高性能的最佳解决方案。您的 CPU 使用率可能已达到最大值，因为您使用资源的效率低下，例如，由于分区配置错误或缺少索引。
当活动增加时，即使没有更多数据，查询速度也会变慢。如果在低容量期间，大多数查询速度都很快，但在达到每秒一定数量的查询时，许多查询速度变慢，则这可能表明您需要更多核心。
您正在大量使用分析查询（也称为聚合）。 所有查询都会使用 CPU，但大型联接、聚合和顺序扫描会使用更多 CPU（它们可能使用多个核心）并且持续时间更长。如果您有对大量数据计算聚合的查询，请确保您有足够的 CPU 来满足应用程序的请求。Timescale 的持续聚合也解决了这个问题——它们将大大减少您的 CPU 需求，同时保持您的分析查询速度非常快。如果您是聚合的重度用户，请考虑使用它们。
要捕获这些问题，您必须监控系统性能并相应地调整资源。以下是一些关于 CPU 监控的技巧
使用 pg_stat_activity 监控 wait_event：pg_stat_activity 是一个 PostgreSQL 视图，提供有关进程当前活动的信。 wait_event 列专门告诉您进程正在等待什么。如果此列中的值为 null，则通常表示该进程未在等待任何 I/O 或锁操作，而是在积极处理和利用 CPU。简单来说，null 值表示查询或操作受 CPU 限制，这意味着 CPU 是该特定进程正在使用的主要资源。
使用 EXPLAIN_ANALYZE 监控并行度：PostgreSQL 中的 EXPLAIN ANALYZE 命令显示查询的详细执行计划以及实际运行时统计信息。这包括有关查询的哪些部分并行执行、使用了多少工作线程以及并行处理效率的信息。此信息可以帮助您最大限度地利用 CPU 的多核设置。
使用 Pgwatch2 监控整个系统：Pgwatch2 是一款流行的开源监控工具，专为 PostgreSQL 数据库而设计。其突出特点之一是详细分析了 PostgreSQL 实例中不同组件和操作的 CPU 使用情况。通过使用 Pgwatch2，您可以深入了解工作负载的哪些部分消耗的 CPU 最多，以及哪些地方可能需要优化。
现在我们已经了解了如何确定 CPU 的大小，接下来让我们看看如何为您的数据库确定内存大小。
PostgreSQL 数据库主要通过两种方式使用内存，每种方式都由不同的参数控制
缓存数据和索引。 PostgreSQL 在内存中维护原始数据和索引的缓存。shared_buffers 参数确定此缓存的大小。数据存储在“页面”中，它们本质上是数据块。这适用于数据文件和索引。页面是否存在于内存中决定了它是否被缓存。虽然有足够的资源来深入研究缓存驱逐算法和页面结构的复杂性，但要记住的关键一点是 PostgreSQL 对缓存驱逐使用 LRU（最近最少使用）系统。
用于中间数据结构的工作内存。 在处理查询时，PostgreSQL 通常需要创建临时数据结构来帮助提供结果。这可能是连接操作、排序甚至生成聚合的一部分。可分配给这些结构的内存量由 work_mem 参数管理。例如，考虑 PostgreSQL 使用的各种连接算法：许多算法需要内存来执行排序或创建哈希表等操作。同样，聚合函数（如 GROUP BY 和 DISTINCT）或使用 ORDER BY （不使用索引进行排序）的查询也可能会创建占用内存的临时数据结构。如果连接或哈希需要的内存超过您的 work_mem 设置，它将溢出到磁盘，这将慢得多。
这两个参数（shared_buffers 和 work_mem）尤为重要。我们将在本系列的后面部分详细介绍它们，因为它们有助于理解 PostgreSQL 中与内存相关的性能问题
shared_buffers 中维护的缓存对于加速数据访问至关重要。较低的缓存命中率表示数据库必须经常从磁盘读取数据，而不是从速度更快的内存缓存中读取数据。增加 shared_buffers 可能会有所帮助，但要确保它与操作系统缓存保持平衡。
如果复杂操作（如排序或连接）由于超过配置的 work_mem 而频繁溢出到磁盘，则性能会受到影响。增加 work_mem 可能会有所帮助，但您也必须小心，因为如果设置得太高，最终可能会消耗所有内存，尤其是在并发连接很多的情况下。
值得注意的是，缓存有两层：PostgreSQL 内存缓存和操作系统缓存。前者由数据库直接管理，是保存数据和索引的主要缓存级别；后者是由操作系统管理的辅助缓存级别。虽然 PostgreSQL 并不知道这里缓存了什么，但这一层通过保留最近从磁盘读取的文件，大大加快了文件读取操作。
通常，如果您的运行数据库有更多内存，它就需要更少地从磁盘读取（有时甚至写入）数据，这是一件好事。但是额外的内存很昂贵。您如何知道您 必须 增加机器上的 RAM 量？
查询性能不可预测。如果一个查询在第一次运行时很慢，而在第二次运行时很快，这可能表明它需要的数据无法始终保留在缓存中。但是，这种速度模式还有其他潜在原因（例如，缓存的查询计划时间长），因此值得通过比较查询计划中的 shared read 与 shared hit 来直接观察这种差异。
您有许多大索引。如果您的所有查询都可以通过索引扫描来满足，并且您的所有索引都可以放入内存，那么即使数据库必须访问磁盘才能获取整个返回的行，您也可能会获得 足够好 的性能。许多用户都知道，表上的索引越多意味着插入、更新和删除必须做更多的工作，但事实也是，为了充分利用额外的索引，您可能需要增加机器上的内存，以便它们可以保留在缓存中。
您的排序正在溢出到磁盘。如前所述，如果您在调试慢速查询时看到 External Sort 或 External Merge 节点，则意味着操作所需的内存超过了可用的 work_mem。因此，PostgreSQL 必须将其中间数据结构写入磁盘，然后在最后进行清理。
您在 pg_stat_activity 中看到很多 DataFileRead 等待事件。每当查询从磁盘读取数据时，都会触发等待事件。直接在 pg_stat_activity 中观察这些事件或比较事件随时间推移的数量可以让您了解您可能需要调整内存。大量的等待事件也表明正在进行顺序扫描，因此在花更多钱之前，请确保您没有遗漏索引（或者编写的查询无法使用索引）。
最后，以下是一些关于内存监控的额外提示。
索引在加载到缓存时会消耗大量的内存，尤其是大型索引。了解索引的大小有助于您了解它们可能占用多少内存，并确定哪些索引最重要。为此，请使用 pg_relation_size() 函数以及索引名称
SELECT pg_size_pretty(pg_relation_size('index_name_here'));
未使用的索引不仅会消耗磁盘空间，还会在写入操作期间导致不必要的开销。但是，值得注意的是，只有查询使用的索引块会在加载到缓存时占用内存空间。
您可以通过 pg_stat_user_indexes 视图定期监控哪些索引正在被访问，哪些没有被访问，该视图包含有关用户定义索引的统计信息。具体来说，idx_scan 列将指示索引被扫描的次数。如果该值在较长时间段内接近或等于零，则表明该索引可能未使用。
评估这些点需要进行监控。以下是一些关于监控数据库内存使用情况的额外技巧。
使用 pg_relation_size() 测量索引大小：索引，尤其是大型索引，在加载到缓存时会占用大量的内存。了解索引的大小有助于您了解它们可能占用的内存量，并使您能够决定哪些索引是最关键的。为此，请将 pg_relation_size() 函数与索引名称一起使用。
使用 pg_stat_user_indexes 监控索引使用情况：未使用的索引会占用磁盘空间，并在写入操作期间导致不必要的开销。您可以通过 pg_stat_user_indexes 视图定期监控哪些索引正在被访问，哪些没有被访问，该视图包含有关用户定义索引的统计信息。idx_scan 列将指示索引被扫描的次数。如果该值持续较低，您可能需要删除此索引。
使用 EXPLAIN_ANALYZE 判断是否从磁盘获取了数据：要详细了解内存交互，您应该使用 EXPLAIN (ANALYZE, VERBOSE, BUFFERS)。这些标志的组合提供了对内存使用情况、缓冲区命中和磁盘读取的洞察。在输出中，您将看到“共享命中”和“共享读取”。“共享命中”表示在缓存中找到了所需数据，而“共享读取”表示必须从磁盘获取数据。命中与读取的比率越高通常表示内存优化越好。
使用 pg_stat_activity 检查内存：查询 pg_stat_activity 视图可以显示哪些查询正在等待与内存相关的事件或正在消耗大量资源。此视图对于查看某些查询是否持续导致问题特别有用。
在本文中，我们了解了如何调整 CPU 和内存大小以保持 PostgreSQL 数据库处于最佳状态。请查看本系列中的其他文章，这些文章涵盖了分区策略、PostgreSQL 参数、索引优化和模式设计。
