IO:BufFileRead 和 IO:BufFileWrite - Amazon Relational Database Service
支持的引擎版本上下文等待次数增加的可能原因操作
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

IO:BufFileRead 和 IO:BufFileWrite

IO:BufFileRead 和 IO:BufFileWrite 事件发生在 RDS for PostgreSQL 创建临时文件时。当操作需要的内存超过当前定义的工作内存参数时，它们会将临时数据写入持久性存储。此操作有时被称为“溢出到磁盘”。

支持的引擎版本

RDS for PostgreSQL 的所有版本均支持此等待事件信息。

上下文

IO:BufFileRead 和 IO:BufFileWrite 与工作内存区域和维护工作内存区域有关。有关这些本地内存区域的更多信息，请参阅 PostgreSQL 文档中的资源消耗量。

work_mem 的原定设置值为 4MB。如果一个会话并行执行操作，则处理并行性的每个工件将使用 4MB 的内存。出于此原因，请仔细设置 work_mem。如果您将值增加的太大，则运行很多会话的数据库可能会占用太多内存。如果您将值设置得太低，RDS for PostgreSQL 会在本地存储中创建临时文件。这些临时文件的磁盘输入/输出可能会降低性能。

如果观察到以下事件顺序，则数据库可能正在生成临时文件：

  1. 可用性突然急剧下降

  2. 可用空间的快速恢复

您可能还会看到“chainsaw”模式。此模式可能表明您的数据库在不断创建小文件。

等待次数增加的可能原因

一般来说，这些等待事件是占用内存比 work_mem 或 maintenance_work_mem 参数分配的内存更多的操作造成。为了进行补偿，操作会写入临时文件。IO:BufFileRead 和 IO:BufFileWrite 事件的常见原因包括以下内容：

需要比工作内存区域中存在的内存更多的查询

具有以下特征的查询使用工作内存区域：

  • 哈希联接

  • ORDER BY 子句

  • GROUP BY 子句

  • DISTINCT

  • 窗口函数

  • CREATE TABLE AS SELECT

  • 具体化视图刷新

需要比维护工作内存区域中存在的内存更多的语句

以下语句使用维护工作内存区域：

  • CREATE INDEX

  • CLUSTER

操作

根据等待事件的原因，我们建议采取不同的操作。

识别问题

假设存在一种情况，性能详情尚未开启的一种情况，而您怀疑 IO:BufFileRead 和 IO:BufFileWrite 的发生频率比正常情况高。要确定问题的根源，可以将 log_temp_files 参数设置为记录所生成的临时文件超过指定阈值 KB 的所有查询。原定设置情况下，log_temp_files 设置为 -1，这将关闭此日志记录功能。如果您将此参数设置为 0，RDS for PostgreSQL 会记录所有临时文件。如果值为 1024，RDS for PostgreSQL 会记录所生成的临时文件大于 1MB 的所有查询。有关 log_temp_files 更多信息，请参阅 PostgreSQL 文档中的错误报告和日志记录。

请检查您的联接查询

您的查询很可能使用联接。例如，以下查询将四个表联接到一起。

SELECT * 
       FROM "order" 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

临时文件使用率激增的可能原因是查询本身存在问题。例如，中断的子句可能无法正确筛选联接。考虑以下示例中的第二个内联接。

SELECT * 
       FROM "order"
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

前面的查询错误地将 customer.id 与 customer.id 进行了联接，在每个客户和每个订单之间生成了笛卡尔积。这种类型的意外联接会生成大型临时文件。根据表的大小，笛卡尔查询甚至可以填满存储空间。满足以下条件时，您的应用程序可能会有笛卡尔联接：

  • 您可以看到存储可用性大幅下降，然后是快速恢复。

  • 现在没有创建任何索引。

  • 现在没有发布任何 CREATE TABLE FROM SELECT 语句。

  • 没有进行任何具体化视图的刷新。

要查看是否使用正确的键联接表，请检查查询和对象关系映射指令。请记住，应用程序的某些查询不会总是被调用，而且有些查询是动态生成的。

检查您的 ORDER BY 和 GROUP BY 查询

在某些情况下，ORDER BY 子句可能会导致过多的临时文件。请考虑以下准则：

  • 当需要对它们进行排序时，只包括 ORDER BY 子句中的列。本指南对于返回数千行并在 ORDER BY 子句中指定很多列的查询尤其重要。

  • 考虑创建索引以在 ORDER BY 子句与具有相同升序或降序的列匹配时对它们进行加速。部分索引更可取，因为它们较小。较小的索引可以更快地读取和遍历。

  • 如果为可以接受 null 值的列创建索引，请考虑是希望将 null 值存储在索引的末尾还是在索引的开头存储。

    如果可能，通过筛选结果集来减少需要排序的行数。如果您使用 WITH 子句语句或子查询，请记住，内部查询会生成一个结果集并会将其传递给外部查询。查询可以筛选出的行越多，查询需要进行的排序就越少。

  • 如果您不需要获取完整的结果集，请使用 LIMIT 子句。例如，如果您只想要前五行，则使用 LIMIT 子句的查询不会继续生成结果。这样，查询需要更少的内存和临时文件。

使用 GROUP BY 子句的查询也可能需要临时文件。GROUP BY 查询通过使用以下函数汇总值：

  • COUNT

  • AVG

  • MIN

  • MAX

  • SUM

  • STDDEV

要优化 GROUP BY 查询，请按照 ORDER BY 查询的建议。

避免使用 DISTINCT 操作

如果可能的话，避免使用 DISTINCT 操作来删除重复的行。查询返回的不必要和重复的行越多，DISTINCT 操作就会越昂贵。如果可能，请在 WHERE 子句中添加筛选条件，即使您对不同的表使用相同的筛选条件。筛选查询并正确联接可以提高性能并减少资源使用。它还可以防止错误的报告和结果。

如果您需要将 DISTINCT 用于同一个表的多行，请考虑创建复合索引。将索引中的多个列进行分组可以缩短评估不同行的时间。此外，如果您使用 RDS for PostgreSQL 版本 10 或更高版本，则可以使用 CREATE STATISTICS 命令在多个列之间关联统计数据。

考虑使用窗口函数而不是 GROUP BY 函数

使用 GROUP BY，您可以更改结果集，然后检索聚合的结果。使用窗口函数，可以在不更改结果集的情况下聚合数据。窗口函数使用 OVER 子句来跨查询定义的集执行计算，从而将一行与另一行关联。您可以使用窗口函数中的所有 GROUP BY 函数，但也可以使用以下函数：

  • RANK

  • ARRAY_AGG

  • ROW_NUMBER

  • LAG

  • LEAD

为了尽量减少窗口函数生成的临时文件的数量，请在需要两个不同的聚合时删除同一结果集的重复项。请考虑以下查询。

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

您可以使用如下 WINDOW 子句重新写入查询。

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

原定设置情况下，RDS for PostgreSQL 执行计划器会整合类似的节点，这样它就不会重复操作。但是，通过对窗口数据块使用显式声明，您可以更轻松地维护查询。您还可以通过防止重复来提高性能。

调查具体化视图和 CTAS 语句

当具体化视图刷新时，它会运行查询。此查询可以包含 GROUP BY、ORDER BY 或 DISTINCT 之类的操作。刷新期间，您可能会观察到大量临时文件以及等待事件 IO:BufFileWrite 和 IO:BufFileRead。同样地，当您根据 SELECT 语句创建表时，CREATE TABLE 语句会运行查询。要减少所需的临时文件，请优化查询。

在重新构建索引时使用 pg_repack

创建索引时，引擎会对结果集进行排序。随着表的大小增加以及索引列中的值变得更加多样化，临时文件需要更多的空间。在大多数情况下，如果不修改维护工作内存区域，就无法阻止为大型表创建临时文件。有关 maintenance_work_mem 的更多信息，请参阅 PostgreSQL 文档中的https://www.postgresql.org/docs/current/runtime-config-resource.html。

重新创建大型索引时可能的解决方法是使用 pg_repack 扩展。有关更多信息，请参阅 pg_repack 文档中的用最少的锁定重新组织 PostgreSQL 数据库中的表。有关在 RDS for PostgreSQL 数据库实例中设置此扩展的信息，请参阅使用 pg_repack 扩展减少表和索引的膨胀。

聚集表时，增加 maintenance_work_mem

CLUSTER 命令基于 index_name 指定的现有索引聚集 table_name 指定的表。RDS for PostgreSQL 以物理方式重新创建表以匹配给定索引的顺序。

当磁性存储普遍存在时，集群很常见，因为存储吞吐量有限。由于基于 SSD 的存储已经很常见，因此集群不太受欢迎。但是，如果对表进行聚集，您仍然可以根据表大小、索引、查询等稍微提高性能。

如果您运行 CLUSTER 命令并观察到等待事件 IO:BufFileWrite 和 IO:BufFileRead，请优化 maintenance_work_mem。将内存大小增加到相当大的量。较高的值意味着引擎可以使用更多内存进行集群操作。

优化内存以防止 IO:BufFileRead 和 IO:BufFileWrite

在某些情况下，您需要优化内存。您的目标是使用相应的参数平衡以下消耗区域间的内存，如下所示。

  • work_mem 值

  • 折扣 shared_buffers 值后剩余的内存

  • 已打开和使用中的最大连接数，受限于 max_connections

有关优化内存的更多信息，请参阅 PostgreSQL 文档中的资源消耗量。

增加工作内存区域的大小

在某些情况下，唯一的选项是增加会话使用的内存。如果您的查询编写正确并且正在使用正确的键进行连接，请考虑增加 work_mem 值。

要了解查询生成了多少个临时文件，请将 log_temp_files 设置为 0。如果您将 work_mem 值增加为日志中标识的最大值，则可以防止查询生成临时文件。但是，work_mem 为每个连接或并行工件设置每个计划节点的最大值。如果数据库有 5000 个连接，并且每个连接使用 256MiB 内存，则引擎需要 1.2TiB 的 RAM。因此，您的实例可能会耗尽内存。

为共享缓冲池预留足够的内存

您的数据库使用很多内存区域，例如共享缓冲池，而不仅仅是工作内存区域。在增加 work_mem 之前考虑这些额外的内存区域的要求。

例如，假设您的 RDS for PostgreSQL 实例类为 db.r5.2xlarge。此实例类拥有 64GiB 的内存。原定设置情况下，将 25% 的内存预留为共享缓冲池。减去分配给共享内存区域的量后，仍然有 16384 MB。不要将剩余内存专门分配给工作内存区域，因为操作系统和引擎还需要内存。

您可以分配给 work_mem 的内存取决于实例类。如果您使用较大的实例类，则可用的内存更多。但是，在前面的示例中，您不能使用超过 16GiB 的内存。否则，当内存耗尽时，您的实例将变得不可用。要从不可用状态恢复实例，RDS for PostgreSQL 自动化服务会自动重新启动。

管理连接数

假设您的数据库实例具有 5000 个同时连接。每个连接至少使用 4MiB 的 work_mem 连接的内存消耗过高可能会降低性能。作为响应，您可进行以下选择：

  • 升级到更大的实例类。

  • 使用连接代理或池程序减少同时数据库连接的数量。

对于代理，请考虑 Amazon RDS 代理、pgBouncer 或基于您的应用程序的连接池程序。此解决方案减轻了 CPU 负载。它还可以降低所有连接都需要工作内存区域时的风险。当数据库连接较少时，您可以增加 work_mem 的值。通过这种方式，您可以减少 IO:BufFileRead 和 IO:BufFileWrite 等待事件的发生率。此外，等待工作内存区域的查询显著加速。