大型的数据库系统，往往都需要消耗大量的内存资源，如果资源配置不合理，很容易出现系统可用内存不足的问题。

内存使用是数据库系统最重要的方面之一。内存不足会直接影响每个性能指标，并对性能产生负面影响。这反过来又会影响我们的用户和业务。在本文中，我们将了解 PostgreSQL 数据库如何管理内存，以及如何排查可用内存不足的问题。

数据库如何读取数据

要了解如何处理内存，我们需要了解事情是如何运作的。让我们来看一些基本的数据库和操作系统机制。

内存页

数据库组织数据的方式，需要能提高性能，并使读取和写入更易于处理。为了实现最佳性能，业务负载必须是可预测的，并划分为可管理的块。数据库通过页面来实现这一点。

数据库上下文中的页是一个固定长度的数据块，它表示数据库系统处理的最小存储单位。页面允许数据库有效地组织磁盘上的数据，这反过来又可以提高性能，并使业务负载可预测。

页面大小参数决定页面的大小。通常，它设置为 8192 字节（8kB），某些数据库允许用户对其进行配置，例如，PostgreSQL 中的 block_size。页面可以分组到所谓的区中，这使得页面管理更容易。

数据库有效地存储在页面中。每次磁盘读取或写入，最终都会读取或写入整个页面，这意味着会写入 8 KB 的数据。即使我们想读取一个字节的数据（比如表中一行的单个比特位的列），我们也需要将整个页面加载到内存中。

碎片化

通常，数据库将一个表行仅存储在一个页面上。它们不允许将行存储在多个页面上。这会导致一种称为碎片化的现象。每行可能具有不同的长度，特别是如果我们使用可变长度类型，例如varchar。为了有效地利用内存，数据库会尝试一行接一行地进行存储。如果我们现在修改位于其他两行之间的一行，则修改后的行可能会变大，并且无法再存储在页面上。要解决此问题，数据库必须将行移动到其他位置，并且原始的空间将变为空（或将具有指向新位置的指针）。此空间现在被浪费并产生了碎片化。

预读和性能

当访问的页面构成一个连续的内存块时，读取和写入的速度要快得多。这利用了内存预取机制，其中内存管理单元（MMU）会进行预测，在不久的将来可能访问哪些内存页，并更早地加载它们。如果预测正确，则整体性能会随着 MMU 更早地读取数据而提高。为了更好地进行预测，内存页必须构成一个连续的块（因此页面必须一个接一个地出现在物理内存中）。

典型的页面大小为 8 KB。这在大多数情况下都很有效，但是，有时我们希望拥有更大的页面，以减少读写操作的次数。页面大小主要由操作系统和 CPU 架构决定。由于操作系统通常使用 8kB 页面，因此数据库系统希望使用相同的设置。为了减少 I/O 操作的次数，我们需要使页面更大。为此，我们可以使用长度为 2MB、1GB 甚至 16GB 的所谓大页面。我们需要在操作系统中启用它们，然后在数据库中启用它们。例如，PostgreSQL 为此提供了 huge_page_size 参数。通过启用大页面，数据库可以获得更大的页面，从而减少数据库必须执行的 I/O 读取和写入次数。这也使页面能构成更大的连续块，从而提高预取和整体性能。

但是，操作系统可能会动态调整页面大小。例如，Linux 支持透明大页面（THP），它会自动提升和降低页面大小。这会对应用程序隐藏大页面，理论上可以在应用程序没有使用大页面时提高性能，因为操作系统可能会将多个应用程序的页面合并为一个大页面。遗憾的是，当应用程序显式使用大页面，而操作系统在后台将大页面表示为常规页面时，性能会很快下降。如果您在数据库中启用了大页面，请在操作系统中禁用 THP。

内存过量使用

在为应用程序分配的内存量方面，操作系统也可能会作弊。当应用程序尝试分配内存时，即使没有可用的内存，操作系统也始终会确认内存已分配。这称为内存过量使用。

此方法可在许多应用程序运行时提高系统的可用性。在准备处理输入的数据时，应用程序分配的缓冲区通常比所需的缓冲区更大。即使应用程序不使用这些内存，操作系统也需要分配大块内存，这会很快耗尽资源，并且能够运行的应用程序更少。

为避免此问题，操作系统假装所有内存分配都已完成，并且内存是可用的。只有当应用程序尝试访问数据时，操作系统才会引发内存不足的异常。在这种情况下，可能会启动 Out-of-Memory-Killer，并杀死其中一个进程。当应用程序不想使用比机器物理内存更多的内存时，这一切都很好。如果他们想使用它，那么问题就开始了。

数据库通常就是这种情况。他们希望在开始时分配大块内存，以便为任何业务负载做好准备。不幸的是，即使内存不可用，操作系统也会简单地假装内存已分配。因此，请在您的操作系统中禁用内存过量使用。在 Linux 中，您可以使用 vm.overcommit_memory 参数。

PostgreSQL 如何分配内存

当 PostgreSQL 服务器启动时，它会分配许多不同的内存块。让我们一一看看。

共享缓冲区

最重要的内存块称为共享缓冲区。它是用于缓存最常用的页面的内存块（涵盖了数据库中的行、索引和其他内容）。数据库会使用几个指标，来识别最受欢迎的页面，但它们主要归结为对读取和写入进行计数。有趣的是，您甚至可以读取页面，并将其重定向到/dev/null，使其缓存在共享缓冲区中，因为 PostgreSQL 还会检查操作系统指标。

共享缓冲区在开始时分配，在运行期间无法更改它们的大小。因此，要更改大小，您需要重新启动数据库。

共享缓冲区是数据库内存中最重要的部分。默认大小设置为 128MB，一般建议将其设置为机器内存的 25%。但是，这是一个非常古老且不准确的建议，因此请继续阅读以了解如何调整它，也可参阅我们另一篇文章，关于如何处理缓存命中率低的问题，以了解更多信息。

工作内存

另一个内存块是，为每个查询中的每个执行节点分配的工作内存。此内存用于处理节点的输出，并生成结果。因此，我们拥有的查询和节点越多，我们使用的内存就会越多。

要了解其工作原理，我们需要了解查询是如何执行的。每当我们运行查询时，数据库必须分阶段执行它。首先，它从表中提取数据并进行连接。接下来，数据库执行过滤和其他处理。最后，对结果进行排序。在每个阶段，数据库可能需要生成一大块数据（比如一个表的内容），这会消耗大量内存。

无需过多的论述，每一个此类型的操作都可能是执行计划中的一个节点。因此，要从多个表进行读取的一个查询，可能具有多个执行计划节点。对于每个这样的节点，数据库都会分配工作内存。如果节点的结果集大于工作内存，则会溢出到磁盘（这比将数据保存在内存中要慢得多）。

PostgreSQL 中的 work_mem 设置，控制为每个查询中的每个执行节点分配的内存量。默认情况下，它设置为 4MB。通常建议将此参数设置为总内存量除以连接数，然后再除以 4 或 16。这取决于您正在运行的业务负载，因此请继续阅读以了解如何对其进行优化。

维护工作内存

我们要考虑的下一个内存块是维护工作内存。该内存块用于执行后台操作，如清理（碎片整理）、索引创建或 DDL 操作（比如添加外键）。

每个后台任务都有自己的内存块，因此，如果有许多 autovacuum 进程正在运行，则每个进程都会有自己的内存块。默认情况下，该内存块的大小设置为 64MB。如果您的服务器有足够的内存，通常建议将其设置为更高的值，例如 1GB。

临时缓冲区

每个会话还会获得另一个用于会话本地缓冲区的内存块。该内存块用于创建临时数据，如临时表。会话会根据需要分配临时缓冲区。

默认情况下，允许每个会话分配 8MB 的临时缓冲区。此内存不与其他会话共享，并且是会话专用的。如果您处理的会话需要分配许多临时表，则可以考虑调整此参数。

内存调优

现在让我们看看，如何对 PostgreSQL 服务器中的内存进行调优。

静态配置

首先，您需要做配置的调优。

shared_buffers

对shared_buffers参数的典型建议是，将其设置为内存的 25%。这是一个很好的起点，但它并不能说明问题。

内存利用率在很大程度上取决于您与数据的交互方式。如果您运行的是一个 OLTP 系统，那么我们可以假设，许多事务将在短时间内触及相同的行。在这种情况下，缓存这些行而不是一遍又一遍地从磁盘中检索它们会是有益的。在这种情况下，增加缓存大小是一个好主意。

但是，如果您运行的是数仓或报表分析数据库，则不太可能在短时间内读取任何行两次。这意味着缓存数据没有意义。相反，我们应该让缓存更小！

如果运行的是 OLTP 业务，则优化 shared_buffers 参数的实际过程应如下所示：

• • 从数据库的共享缓冲区内存初始值开始，比如 1GB

• • 使用 pg_buffercache 检查命中率和未命中率

• • 再添加 1GB 的共享缓冲区内存

• • 再次检查命中率和未命中率。此时命中率应该会增加

• • 继续添加内存，直到您看到命中率不再增加

如果您运行 OLAP 或数仓，则可以使用相同的策略，来减小缓存的内存大小，而不会降低系统的性能。请参阅我们关于如何处理缓存命中率低的问题的文章，以了解更多信息。

work_mem

应根据您在数据库中配置的连接数，来设置 work_mem 参数。在获得连接数后，可这样计算

work_mem = TOTAL_RAM / #connections / 16

如果您观察到仍然有许多查询会溢出到磁盘，请将 work_mem 参数值继续加倍，直到您的查询不再经常溢出。

maintenance_work_mem

将maintenace_work_mem参数初始设置为 1GB。如果您发现清理操作或其他后台进程太慢，请将大小加倍。

temp_buffers

将temp_buffers参数设置为 2GB 除以连接数的值，作为初始值。如果您观察到有许多查询在创建临时表，则将大小加倍。

连接

每个连接都会消耗一些内存。连接过多会降低系统性能，并消耗大量内存。因此，您应该限制连接数，并尽可能使用连接池。

请参阅我们的配置连接池指南，了解如何配置它们。

查询优化

很明显，慢查询可能会影响到可用的内存量。低效的查询可能会读取过多数据（通过扫描表而不是使用索引）、溢出到磁盘（通过使用低效的连接策略）或降低缓存命中率（通过更新未使用的索引）。

因此，请始终优化好查询。分析他们的连接策略、查询参数、过滤器、溢出到磁盘，以及会降低性能的其他方面。

索引

未使用的索引可能会降低您的可用内存。每次更新表中的数据时，可能还需要更新索引。即使未使用的索引，它们也需要与表保持同步。这意味着更新表中的行，可能会导致执行更多的更新。

此处的一般建议是，删除所有未使用的索引。在查找未使用的索引时，请考虑以下事项：

• • 为什么索引未被使用？也许应该使用它，但您的查询有问题。在这种情况下，请不要修改索引，而是修复查询

• • 索引是否在所有地方都未被使用？也许它在只读副本上用到了？保持副本之间的数据库配置一致是有益的，因此在这种情况下不要删除索引。

• • 索引是否未被使用？也许它用于一些罕见的场景，比如月度的报告。在这种情况下，请考虑删除索引，并且仅在有帮助的情况下，每月为报表重新创建一次索引。

如果您确定该索引未被使用，则只需将其删除即可。如果您观察到某些查询的性能下降，请分析其历史性能（如果它们在删除索引时，速度会变慢）。如果是这样的话，那么也许他们还是使用了索引。

操作系统配置

如前所述，您的操作系统配置可能会影响数据库的性能。一般建议禁用内存过量使用和透明大页面。请查阅您的操作系统的文档，以了解如何执行此操作。

缓存命中率

通常，提高缓存命中率的所有步骤，也都可能改善可用内存不足的情况。请参阅我们关于如何处理缓存命中率低的问题的指南。

表分区

请对表进行分析，是否可以对表进行分区。有很多方法可以进行分区，您可以按照我们的表分区指南，了解更多信息。

扩展

如果都没有什么帮助，您可以考虑扩展服务器。您可以使机器更大（垂直扩展）或将负载分布到多台机器（水平扩展）。可以从垂直扩展开始，因为它要容易得多。如果这没有帮助，请考虑水平扩展，但请记住，这可能需要更改数据库客户端。

总结

可用内存低的问题可能会很难处理。我们需要明白，许多活动的部分是相互关联的。我们需要分析我们的操作系统配置、数据库配置和我们应对的业务负载。OLTP 和 OLAP 系统的情况不同，没有通用的解药。