IO:BufFileRead 和 IO:BufFileWrite - Amazon Relational Database Service
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

IO:BufFileRead 和 IO:BufFileWrite

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

支持的引擎版本

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

上下文

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

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

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

  1. 可用性突然急剧下降

  2. 可用空间的快速恢复

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

等待次数增加的可能原因

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

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

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

  • 哈希联接

  • ORDER BY 子句

  • GROUP BY 子句

  • DISTINCT

  • 窗口函数

  • CREATE TABLE AS SELECT

  • 具体化视图刷新

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

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

  • CREATE INDEX

  • CLUSTER

操作

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

识别问题

假设存在一种情况,性能详情尚未开启的一种情况,而您怀疑 IO:BufFileReadIO: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.idcustomer.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 BYORDER BYDISTINCT 之类的操作。刷新期间,您可能会观察到大量临时文件以及等待事件 IO:BufFileWriteIO: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:BufFileWriteIO: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:BufFileReadIO:BufFileWrite 等待事件的发生率。此外,等待工作内存区域的查询显著加速。