# IO:BufFileRead 和 IO:BufFileWrite
`IO:BufFileRead` 和 `IO:BufFileWrite` 事件发生在 Aurora PostgreSQL 创建临时文件时。当操作需要的内存超过当前定义的工作内存参数时，它们会将临时数据写入持久性存储。此操作有时被称为*溢出到磁盘*。有关临时文件及其用法的更多信息，请参阅[使用 PostgreSQL 管理临时文件](PostgreSQL.ManagingTempFiles.md)。
**Topics**
+ [支持的引擎版本](#apg-waits.iobuffile.context.supported)
+ [上下文](#apg-waits.iobuffile.context)
+ [等待次数增加的可能原因](#apg-waits.iobuffile.causes)
+ [操作](#apg-waits.iobuffile.actions)
## 支持的引擎版本
Aurora PostgreSQL 的所有版本均支持此等待事件信息。
## 上下文
`IO:BufFileRead` 和 `IO:BufFileWrite` 与工作内存区域和维护工作内存区域有关。有关这些本地内存区域的更多信息，请参阅 [工作内存区域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem) 和 [维护工作内存区域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.maintenance_work_mem)。
`work_mem` 的原定设置值为 4MB。如果一个会话并行执行操作，则处理并行性的每个工件将使用 4MB 的内存。出于此原因，请仔细设置 `work_mem`。如果您将值增加的太大，则运行很多会话的数据库可能会占用太多内存。如果您将值设置得太低，Aurora PostgreSQL 会在本地存储中创建临时文件。这些临时文件的磁盘输入/输出可能会降低性能。
如果观察到以下事件顺序，则数据库可能正在生成临时文件：
1. 可用性突然急剧下降
1. 可用空间的快速恢复
您可能还会看到“chainsaw”模式。此模式可能表明您的数据库在不断创建小文件。
## 等待次数增加的可能原因
一般来说，这些等待事件是占用内存比 `work_mem` 或 `maintenance_work_mem` 参数分配的内存更多的操作造成。为了进行补偿，操作会写入临时文件。`IO:BufFileRead` 和 `IO:BufFileWrite` 事件的常见原因包括以下内容：
**需要比工作内存区域中存在的内存更多的查询**
具有以下特征的查询使用工作内存区域：
+ 哈希联接
+ `ORDER BY` 子句
+ `GROUP BY` 子句
+ `DISTINCT`
+ 窗口函数
+ `CREATE TABLE AS SELECT`
+ 具体化视图刷新
**需要比维护工作内存区域中存在的内存更多的语句**
以下语句使用维护工作内存区域：
+ `CREATE INDEX`
+ `CLUSTER`
## 操作
根据等待事件的原因，我们建议采取不同的操作。
**Topics**
+ [识别问题](#apg-waits.iobuffile.actions.problem)
+ [请检查您的联接查询](#apg-waits.iobuffile.actions.joins)
+ [检查您的 ORDER BY 和 GROUP BY 查询](#apg-waits.iobuffile.actions.order-by)
+ [避免使用 DISTINCT 操作](#apg-waits.iobuffile.actions.distinct)
+ [考虑使用窗口函数而不是 GROUP BY 函数](#apg-waits.iobuffile.actions.window)
+ [调查具体化视图和 CTAS 语句](#apg-waits.iobuffile.actions.mv-refresh)
+ [在创建索引时使用 pg\$1repack](#apg-waits.iobuffile.actions.pg_repack)
+ [聚集表时，增加 maintenance\$1work\$1mem](#apg-waits.iobuffile.actions.cluster)
+ [优化内存以防止 IO:BufFileRead 和 IO:BufFileWrite](#apg-waits.iobuffile.actions.tuning-memory)
### 识别问题
您可以直接在性能详情中查看临时文件的使用情况。有关更多信息，请参阅 [使用性能详情查看临时文件使用情况](PostgreSQL.ManagingTempFiles.Example.md)。禁用性能详情后，您可能会注意到 `IO:BufFileRead` 和 `IO:BufFileWrite` 操作量有所增加。要排查相关问题，请执行以下操作：
1. 检查 Amazon CloudWatch 中的 `FreeLocalStorage` 指标。
1. 寻找一种电锯模式，该模式为一系列的交错突增。
电锯模式表示存储的快速消耗和释放，通常与临时文件有关。如果您注意到这种模式，请开启性能详情。使用性能详情时，您可以确定等待事件的发生时间以及与这些事件关联的查询。您的解决方案取决于导致事件的特定查询。
或者设置参数 `log_temp_files`。此参数记录生成超出临时文件阈值 KB 的所有查询。如果值为 `0`，Aurora PostgreSQL 会记录所有临时文件。如果值为 `1024`，Aurora PostgreSQL 会记录生成大于 1MB 的临时文件的所有查询。有关 `log_temp_files` 更多信息，请参阅 PostgreSQL 文档中的[错误报告和日志记录](https://www.postgresql.org/docs/10/runtime-config-logging.html)。
### 请检查您的联接查询
您的应用程序可能会使用联接。例如，以下查询将四个表联接到一起。
```
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` 用于同一个表的多行，请考虑创建复合索引。将索引中的多个列进行分组可以缩短评估不同行的时间。此外，如果您使用 Amazon Aurora 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);
```
预设情况下，Aurora PostgreSQL 执行计划器会整合类似的节点，这样它就不会重复操作。但是，通过对窗口数据块使用显式声明，您可以更轻松地维护查询。您还可以通过防止重复来提高性能。
### 调查具体化视图和 CTAS 语句
当具体化视图刷新时，它会运行查询。此查询可以包含 `GROUP BY`、`ORDER BY` 或 `DISTINCT` 之类的操作。刷新期间，您可能会观察到大量临时文件以及等待事件 `IO:BufFileWrite` 和 `IO:BufFileRead`。同样地，当您根据 `SELECT` 语句创建表时，`CREATE TABLE` 语句会运行查询。要减少所需的临时文件，请优化查询。
### 在创建索引时使用 pg\$1repack
创建索引时，引擎会对结果集进行排序。随着表的大小增加以及索引列中的值变得更加多样化，临时文件需要更多的空间。在大多数情况下，如果不修改维护工作内存区域，就无法阻止为大型表创建临时文件。有关更多信息，请参阅 [维护工作内存区域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.maintenance_work_mem)。
重新创建大型索引时可能的解决方法是使用 pg\$1repack 工具。有关更多信息，请参阅 pg\$1repack 文档中的[用最少的锁定重新组织 PostgreSQL 数据库中的表](https://reorg.github.io/pg_repack/)。
### 聚集表时，增加 maintenance\$1work\$1mem
`CLUSTER` 命令基于 *index\$1name* 指定的现有索引聚集 *table\$1name* 指定的表。Aurora PostgreSQL 以物理方式重新创建表以匹配给定索引的顺序。
当磁性存储普遍存在时，集群很常见，因为存储吞吐量有限。由于基于 SSD 的存储已经很常见，因此集群不太受欢迎。但是，如果对表进行聚集，您仍然可以根据表大小、索引、查询等稍微提高性能。
如果您运行 `CLUSTER` 命令并观察到等待事件 `IO:BufFileWrite` 和 `IO:BufFileRead`，请优化 `maintenance_work_mem`。将内存大小增加到相当大的量。较高的值意味着引擎可以使用更多内存进行集群操作。
### 优化内存以防止 IO:BufFileRead 和 IO:BufFileWrite
在某些情况下，您需要优化内存。您的目标是平衡以下要求：
+ `work_mem` 值（请参阅 [工作内存区域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem)）
+ 折扣 `shared_buffers` 值后剩余的内存（请参阅 [缓冲池](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool)）
+ 已打开和使用中的最大连接数，受限于 `max_connections`
#### 增加工作内存区域的大小
在某些情况下，唯一的选项是增加会话使用的内存。如果您的查询编写正确并且正在使用正确的键进行连接，请考虑增加 `work_mem` 值。有关更多信息，请参阅 [工作内存区域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem)。
要了解查询生成了多少个临时文件，请将 `log_temp_files` 设置为 `0`。如果您将 `work_mem` 值增加为日志中标识的最大值，则可以防止查询生成临时文件。但是，`work_mem` 为每个连接或并行工件设置每个计划节点的最大值。如果数据库有 5000 个连接，并且每个连接使用 256MiB 内存，则引擎需要 1.2TiB 的 RAM。因此，您的实例可能会耗尽内存。
#### 为共享缓冲池预留足够的内存
您的数据库使用很多内存区域，例如共享缓冲池，而不仅仅是工作内存区域。在增加 `work_mem` 之前考虑这些额外的内存区域的要求。有关缓冲池的更多信息，请参阅 [缓冲池](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool)。
例如，假设您的 Aurora PostgreSQL 实例类为 db.r5.2xlarge。此实例类拥有 64GiB 的内存。预设情况下，75% 的内存为共享缓冲池预留。减去分配给共享内存区域的量后，仍然有 16384 MB。不要将剩余内存专门分配给工作内存区域，因为操作系统和引擎还需要内存。
您可以分配给 `work_mem` 的内存取决于实例类。如果您使用较大的实例类，则可用的内存更多。但是，在前面的示例中，您不能使用超过 16GiB 的内存。否则，当内存耗尽时，您的实例将变得不可用。要从不可用状态恢复实例，Aurora PostgreSQL 自动化服务会自动重新启动。
#### 管理连接数
假设您的数据库实例具有 5000 个同时连接。每个连接至少使用 4MiB 的 `work_mem` 连接的内存消耗过高可能会降低性能。作为响应，您可进行以下选择：
+ 升级到更大的实例类。
+ 使用连接代理或池程序减少同时数据库连接的数量。
对于代理，请考虑 Amazon RDS 代理、pgBouncer 或基于您的应用程序的连接池程序。此解决方案减轻了 CPU 负载。它还可以降低所有连接都需要工作内存区域时的风险。当数据库连接较少时，您可以增加 `work_mem` 的值。通过这种方式，您可以减少 `IO:BufFileRead` 和 `IO:BufFileWrite` 等待事件的发生率。此外，等待工作内存区域的查询显著加速。