PostgreSQL 缓存命中率低？可以这么做！

介绍

磁盘活动比从内存读取数据慢得多。根据当今设备的性能规格，从内存读取大约需要 100 纳秒，而从物理驱动器读取需要 10 微秒（对于 SSD）到 10 毫秒（对于 HDD）。这比访问随机内存存储器慢 100,000 倍。从 L1 缓存读取甚至更快，可能只需要 3 个 CPU 周期，不到 1 纳秒。因此，从物理驱动器进行每次读取都会对性能造成巨大影响，应避免使用。在本教程中，我们将了解如何诊断分析，我们无法利用缓存数据，并需要从物理驱动器读取数据的场景。我们将了解为什么它很重要，要寻找什么，以及要使用哪些工具和扩展。

数据库如何读取数据

数据库在直接从硬盘驱动器读取数据时，可以很好地意识到性能问题。因此，它们结合了许多复杂的技术来提高性能，并尽可能缓存数据。让我们看看数据库如何访问数据，以及接下来会发生什么。

读取数据的各种方式

最基本的方法是，当数据库需要读取未配置聚集索引的表时，这样的表有时称为堆表。引擎只需逐一扫描每一行，相应地过滤它们，然后再做进一步处理。

数据存储在所谓的页面中。数据库引擎将数据分块为较小的部分，然后将这些部分存储在驱动器上。每个部分称为一个页面，通常为 8kB 大小（尽管这是可配置的）。遗憾的是，由于数据库通常不允许一行跨越多个页面，因此浪费了一些空间。这种现象称为 “碎片化”，会导致一些空间浪费。扫描整个表时，数据库引擎必须读取的数据量，会超过数据的实际大小，这使得整个过程更加缓慢。

当我们考虑数据修改时，事情会变得更加复杂。当我们删除一行时，数据库引擎必须将其从页面中删除，而这种情况实际上很少发生。更常见的是，数据库只是将行标记为已删除（“已死”），然后在扫描表时忽略它。但是，数据库仍然需要读取死行，当我们有很多死元组时，这可能会进一步降低性能。

这意味着随着时间的推移，我们的数据库会变慢。为了辅助改善这一点，我们需要对表进行碎片整理（有时称为 “VACUUM”）。此过程使数据库读取所有数据，并以更有条理的方式将其写入一侧，以删除死元组并恢复一些浪费的空间。

由于所有这些内部细节，数据库引擎有很多方法可以加快速度。第一种方法是用多个读取器并行扫描表。如果我们可以一次执行多个读取，这会使事情变得更快，当我们将驱动器放在 RAID 磁盘阵列中时，这通常是可能的。然而，真正改变游戏规则的是索引。

聚集索引是一种将数据保存在 B 树（广义二叉树）中的结构。所有行都根据一些属性（如行号）进行排序，因此我们可以更快地搜索行。最重要的是，我们不需要读取所有数据来根据标识符查找行。这样我们就可以避免昂贵的读取，并轻松 “跳转” 到所需的记录。请务必了解，当我们在表上配置聚集索引时，索引将成为表。如果可能，所有数据都保存在索引节点中。这在一定程度上降低了读取性能（因为我们需要完整地读取大行），但仍然允许我们利用索引和二分查找。如果我们不想将整个数据存储在索引节点中，我们可以构建额外的索引，这些索引将仅包含一些列和指向实际行的指针，然后数据库将首先从辅助索引中读取，然后转到主表以获取行的内容。

还有更多技术可以提高性能（如位图扫描、列存储和其他类型的索引），但是，它们都专注于一件简单的事情 - 尽可能少地读取。

读取后会发生什么

从驱动器读取数据后，数据库引擎可能希望将其保存在内存中，以加快接下来的操作的速度。因此，每个页面都可以存储在缓存中。根据数据库类型，可能有许多级别的缓存和不同的缓存策略。我们可以直观地将这些视为内存的一部分，用于保留从驱动器读取的内容，即使在我们完全处理了数据之后也是如此。

数据库缓存只是一个级别的缓存。另一个级别是操作系统的缓存，还有一个级别是 CPU 缓存。即使我们在数据库缓存中找不到数据，它仍然可能在系统缓存中（因此文件系统不需要从驱动器中读取），或者在 CPU 缓存中。当数据库想要读取一个内存页时，缓存的实际工作方式如下：

• 首先，数据库检查自己的缓存。如果数据在那里，那么数据库想要读取它

• 要从数据库缓存中读取数据，数据库需要从某个实际的 DRAM 内存地址中读取数据。在进入 DRAM 内存之前，会查找 CPU 缓存（首先是 L1，然后是 L2 和 L3）。如果数据不存在，则 CPU 从 DRAM 内存获取数据，填充 CPU 缓存，然后继续。

• 如果数据不在数据库缓存中，则数据库会从某个内存页进行读取（对应的是驱动器上实际文件的某些部分）。此时，文件系统会检查文件部分是否已经在文件系统缓存中（而文件系统缓存又存储在 DRAM 内存中，因此会再次查询 CPU 缓存）。如果数据存在，则从 DRAM 内存返回数据。否则，文件系统将转到驱动器。

• 驱动器又有自己的缓存。它会检查数据是否存在，并在可能的情况下返回数据。否则，将读取实际的物理驱动器，并将数据存储在驱动器缓存、文件系统缓存、数据库缓存和 CPU 缓存中。

• 如果这些缓存中的任何一个已满，我们需要驱逐一些旧的条目。有许多策略可以做到这一点，特别是最近最少使用（LRU）算法，它会删除最长时间未使用的条目。这意味着放置一些“新”数据，会导致从缓存中删除一些“旧”数据。

正如我们所看到的，读取路径上有很多缓存。虽然我们很少关注这些技术细节，但我们需要了解以下几点：

• 为了让事情变得更快，我们需要尽可能多地利用我们的缓存

• 读取一些 “新” 数据，可能会影响到其他地方的性能。例如，文件系统读取数据给操作系统更新，可能会从为数据库提供服务的文件系统缓存中移出一些条目。因此，操作系统更新可能会影响数据库的性能

• 我们为数据库配置的缓存大小，并不是我们“唯一”使用的缓存

公平地说，最好不要将未使用的数据保留在内存中，因为它可能会影响其他一些操作。在可能的情况下，最好清除所有不需要的东西！

缓存未充分利用时会出现什么问题

当缓存使用效率低下时，许多事情都会出问题。

首先，延迟会增加。这是数据库从较慢的数据源读取数据的自然结果（从硬盘驱动器，而不是 CPU 缓存或数据库缓存）。这反过来又增加了面向用户的操作的持续时间，并对所有事情的性能产生负面影响。

其次，缓存利用率低，会导致系统技术栈中其他部分的压力更大。文件系统需要读取更多数据，驱动器需要读取更多数据，而其他进程会减慢速度。如果我们不能使用缓存，一切都会变慢。

接下来，我们增加资源使用，并降低能源效率。由于我们的设备主要在半导体改变其状态并且物理设备执行一些工作时消耗能量，因此缓存利用不足会导致更多的能量消耗。同样，我们需要从驱动器中读取更多数据，这会使系统总线或网络连接过载，这反过来又会导致更多地使用其他资源。

较高的资源使用率会导致失败次数增加。物理设备在使用次数越多时就会损坏，因此使用频率越高，会导致更多的硬件故障和问题。这会导致崩溃、系统无响应，我们可能会面临停机或数据丢失。

简而言之，缓存命中率低会对一切产生负面影响。因此，我们应该尽可能地提高缓存利用率。

缓存的全部意义以及如何充分利用缓存

缓存利用率在很大程度上取决于您与数据的交互方式。我们通常假设数据库为 OLTP 业务负载提供服务，在该业务模式下，我们假设某特定行会被多次使用，最好是多个事务使用。在这种情况下，缓存该行以避免需要访问磁盘（我们知道这要慢得多）是有益的。一旦该行被缓存，事务就可以从位于内存中的缓存中读取它，并使读取更快。

但是，如果您的业务负载不是 OLTP 模式，则假设不成立。一个特定的行可以只使用一次，并且永远不会再次使用。在这种情况下，将其放入缓存中不会带来任何价值，因为我们不会从中获得任何好处。实际上更糟糕，因为我们仍然需要管理缓存，因此性能可能会降低。

处理缓存时，首先要了解的是，分析是什么模式的业务负载。如果系统为 OLTP 业务负载提供服务，那么我们可能预期增加缓存大小将会提高性能。我们可以通过增加缓存大小，并验证缓存命中率是否增加，来简单地验证这一点。由于低效的查询、碎片、索引、操作系统等情况，还会有更多的手段，但从本质上讲，这一切都归结为增加缓存并检查是否有帮助。这也可能是相反的 - 你可以减小缓存大小，并检查指标是否减少了一点，这可能表明你的缓存太大了，没有带来更多价值。

但是，如果系统服务器用于数据仓库或分析报告场景，那么我们实际上可能希望让缓存配置更小（比如千字节小），以避免维护它们的代价，因为我们没有从中获得任何好处。要了解是否是这种情况，我们需要验证我们运行了哪些查询，哪些数据流入系统，以及随着时间的推移缓存了哪些数据。我们还可以应用一些标准策略，这些策略对数据库“一般”有帮助，并且可以直接影响缓存性能。请继续阅读以了解它们。

如何提高缓存命中率

我们需要优化数据库系统的许多方面，以提高缓存命中率。让我们来了解一下它们。

慢查询

如前所述，我们应该处理尽可能少的数据。我们应该始终致力于在适用的情况下去优化查询。这包括以下内容：

• 尽可能使用索引，以最大程度地减少数据读取

• 高效使用过滤来处理更少的行，并尽早过滤它们

• 避免需要存储在一侧的临时数据，否则会占用缓存

• 删除不需要的列或连接

• 尽可能对数据进行非规范化操作，以对标识符而不是整行进行操作

• 避免使用字符串类型的查询，优选直接的形式

• 尽可能使用物化视图

• 将查询拆分为较小的查询，以避免连接

• 避免锁定

• 确保统计信息是最新的，以免产生效率低下的查询计划

• 删除死行

我们可以关注的方面还有很多。它们都遵循这个简单的规则 - 尽可能少地读取，和避免污染缓存。

索引

为了提高缓存命中率，我们可以简单地读取更少的数据。为此，我们可以尽可能使用索引，使数据库只访问所需的记录。

分析您的所有查询，并在适用的情况下配置索引。

静态配置

我们可以直接调整的一个方面是缓存配置。根据我们使用的数据库引擎，有许多可调参数。例如，PostgreSQL 使用shared_buffers、effective_cache_size和work_mem等。

您应该始终针对您的特定业务负载和托管的物理环境，调整这些参数。

碎片化

表碎片会对性能产生负面影响。由于我们有死行，因此数据库引擎会浪费时间读取这些死行，然后忽略它们。碎片化的原因有很多。

第一个原因是，我们不断修改数据。使用像varchar的列类型可能会导致碎片，因为行会更改其大小。假设您更新了存储在页面中间某个位置的行，并且更新会导致该行变大（因为您在其中一列中存储了更大的字符串）。这样的行不能再放入原页面，必须移动到其他位置。因此，数据库需要将行移动到其他位置，这反过来又会导致数据库引擎在读取数据时多使用一个内存页。

另一种情况是，当您想在其他两行之间插入新行时。当您以有序方式存储数据（如在聚集索引中），并在中间的某个位置而不是末尾添加一行时，可能会发生这种情况。若要在驱动器上保持顺序，数据库引擎需要将行放在其他位置，并使用指针来保持顺序。在不太常见的情况下，这可能会导致聚集索引不再有序，这需要数据库在读取行后再次对行进行排序。

为避免这些问题，我们需要定期删除死元组以减少碎片。要做到这一点，我们需要对表进行清理，这需要时间，而且必须小心进行，以免影响正在运行的事务。

未使用的索引

当我们在表上配置了辅助索引时，数据库引擎需要使它们与表内容保持同步。这意味着，每个修改的查询都必须在多个位置更新数据 - 表本身和所有索引。要更新索引，数据库必须将它们带到内存中，这反过来又会导致缓存利用率降低。

要避免此问题，请删除所有不需要的索引。只需分析它们随时间的使用情况，并尽快删除它们。

操作系统

操作系统也可能影响内存使用。例如，Linux 允许过量使用内存，并且有其臭名昭著的内存不足（OOM）杀手，它会杀死使用过多内存的进程。即使您的进程认为它已成功分配内存，操作系统也可能决定稍后终止它，因为其他进程想要分配一些内存，而操作系统认为它没有足够的内存供所有进程使用。

要避免此问题，请调整 OOM Killer，使其不终止数据库进程，而是终止其他进程。您甚至可以考虑禁用内存过量使用，以完全避免此问题。

更多内存

解决缓存命中率低最明显的一个解决方案是，创建更大的缓存。只需向您的计算机添加内存，并配置缓存以使用此额外内存即可。

如何使用 pg_buffercache

pg_buffercache 是一个 PostgreSQL 扩展，可让您检查数据库的共享缓冲区缓存中发生的情况。它提供的功能，可以向您显示缓存页面、统计信息，和正在发生的事情的概况。

要使用该扩展，只需这样安装：

create extension pg_buffercache;

该扩展提供了一个视图pg_buffercache，其中会列出以下数据：

• bufferid - 服务器缓冲块缓存中的数据块 ID

• relfilenode - 关系的节点号

• reltablespace - 表空间使用的对象标识符类型（OID）

• reldatabase - 数据库的 OID

• relforknumber - 关系中的分支编号

• relblocknumber - 关系中的页号

• isdirty - 指示给定页面是否脏的标志

• usagecount - 访问计数统计数据

• pinning_backends_integer - 此缓冲块的访问后端数

由于扩展会在页面上运行，我们需要将缓存大小除以 8kB。因此，如果我们有 256MB 的shared_buffers缓冲区，默认页面大小为 8kB，那么我们将有 32,768 个缓冲块。因此，视图 pg_buffercache 将具有相同的行数（每个块一行）。

我们可以使用该扩展，来查找使用了缓存的表。例如，此查询显示每个表消耗的缓冲块数目：

SELECT c.relname AS relation_name, count(*) AS buffers
FROM pg_buffercache b
INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

此查询显示每个表使用了多少缓冲块，以及实际缓冲了多大比率的数据：

SELECT
        c.relname,
        pg_size_pretty(count(*) * 8192) as buffered,
        round(100.0 * count(*) / ( SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent,
        round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
WHERE pg_relation_size(c.oid) > 0
GROUP BY c.oid, c.relname
ORDER BY 3 DESC
LIMIT 10;

您可以使用该扩展来跟踪哪些表占用了您的缓冲区，并验证这是否是预期的。

一旦您看到缓存中的内容，并了解您的业务负载是否为 OLTP 模式（缓存在理论上应该有所帮助），您就可以调整缓存大小。如果您观察到缓存更改太频繁（例如，您每分钟都会在缓存中看到不同的表），那么增加缓存大小可能是值得的，并且命中率应该会增加（从而提高整体性能）。同时，如果您看到大部分缓存保持稳定，那么您可以考虑减小缓存大小，这应该不会对命中率产生太大影响，并且性能不会降低。每次修改缓存大小后，您都可以用pg_buffercache分析缓存内容，并验证您的假设。

所以实际过程是：

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

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

• 添加更多共享缓冲区内存（例如额外的 10% 或 1GB）

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

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

您可以使用相同的推理，来减小缓存的内存大小，而不会劣化系统性能。

总结

优化缓存命中率低的问题，对于保持数据库的良好状态至关重要。缓存会极大地影响整个系统的性能，并且访问路径上的许多部件都会影响缓存利用率。pg_buffercache扩展可以帮助分析内存缓冲区，并向您展示要关注的内容。