# IO:DataFileRead


当由于分页在共享内存中不可用，连接等待后端进程从存储中读取所需分页时，会发生 `IO:DataFileRead` 事件。

**Topics**
+ [

## 支持的引擎版本
](#wait-event.iodatafileread.context.supported)
+ [

## 上下文
](#wait-event.iodatafileread.context)
+ [

## 等待次数增加的可能原因
](#wait-event.iodatafileread.causes)
+ [

## 操作
](#wait-event.iodatafileread.actions)

## 支持的引擎版本


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

## 上下文


所有查询和数据处理 (DML) 操作都会访问缓冲池中的页面。可以诱导读取的语句包括 `SELECT`、`UPDATE` 和 `DELETE`。例如，`UPDATE` 可以从表或索引中读取页面。如果请求或更新的页面不在共享缓冲池中，则此读取可能会导致 `IO:DataFileRead` 事件。

由于共享缓冲池是有限的，所以它可以填满。在这种情况下，对不在内存中的页面的请求会强制数据库从磁盘中读取数据块。如果 `IO:DataFileRead` 事件频繁发生，您的共享缓冲池可能太小，从而无法容纳您的工作负载。对于读取大量不适合缓冲池的行的 `SELECT` 查询，此问题很严重。有关缓冲区池的更多信息，请参阅 PostgreSQL 文档中的[资源消耗量](https://www.postgresql.org/docs/current/runtime-config-resource.html)。

## 等待次数增加的可能原因


`IO:DataFileRead` 事件的常见原因包括以下各项：

**连接激增**  
您可能会发现多个连接生成相同数量的 IO:DataFileRead 等待事件。在这种情况下，`IO:DataFileRead` 事件可能会发生激增（突然大幅度增加）。

**执行顺序扫描的 SELECT 和 DML 语句**  
您的应用程序可能正在执行新的操作。或者，现有的操作可能会因为新的执行计划而发生变化。在这种情况下，请查找具有更大的 `seq_scan` 值的表格（特别是大型表格）。通过查询 `pg_stat_user_tables` 查找它们。要跟踪生成更多读取操作的查询，请使用扩展 `pg_stat_statements`。

**适用于大型数据集的 CTAS 和 CREATE INDEX**  
*CTAS* 是一个 `CREATE TABLE AS SELECT` 语句。如果您使用大型数据集作为源来运行 CTAS，或者在大型表上创建索引，则可能会发生 `IO:DataFileRead` 事件。创建索引时，数据库可能需要使用顺序扫描读取整个对象。当页面不在内存中时，CTAS 会生成 `IO:DataFile` 读取。

**多个 vacuum 工件同时运行**  
vacuum 工件可以手动或自动触发。我们建议采取积极的 vacuum 策略。但是，当表中有许多更新或删除的行时，`IO:DataFileRead` 等待增加。回收空间后，花在 `IO:DataFileRead` 上的 vacuum 时间减少。

**摄取大量数据**  
当您的应用程序提取大量数据时，`ANALYZE` 操作可能会更频繁地发生。`ANALYZE` 进程可以由 Autovacuum 启动程序触发，也可以手动调用。  
`ANALYZE` 操作可以读取表的子集。必须扫描的页数通过将 30 乘以 `default_statistics_target` 值进行计算。有关更多信息，请参阅 [PostgreSQL 文档](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET)。`default_statistics_target` 参数接受 1 到 10000 之间的值，其中原定设置值为 100。

**资源匮乏**  
如果消耗了实例网络带宽或 CPU，`IO:DataFileRead` 事件可能会更频繁地发生。

## 操作


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

**Topics**
+ [

### 检查谓词筛选条件是否存在生成等待的查询
](#wait-event.iodatafileread.actions.filters)
+ [

### 尽量减少维护操作的影响
](#wait-event.iodatafileread.actions.maintenance)
+ [

### 响应大量连接
](#wait-event.iodatafileread.actions.connections)

### 检查谓词筛选条件是否存在生成等待的查询


假设您确定了正在生成 `IO:DataFileRead` 等待事件的特定查询。您可以使用以下方法识别它们：
+ 性能详情
+ 目录视图，例如扩展程序 `pg_stat_statements` 提供的视图
+ 目录视图 `pg_stat_all_tables`，如果它定期显示物理读取数量增加
+ `pg_statio_all_tables` 视图，如果它显示 `_read` 计数器正在增加

我们建议您确定这些查询的谓词（`WHERE` 子句）中使用了哪些筛选条件。请遵循以下准则：
+ 运行 `EXPLAIN` 命令。在输出中，确定使用的扫描类型。顺序扫描不一定表示存在问题。与使用筛选条件的查询相比，使用顺序扫描的查询自然会产生更多的 `IO:DataFileRead` 事件。

  了解 `WHERE` 子句中列出的列是否已编入索引。如果没有，请考虑为此列创建索引。这种方法避免了顺序扫描并减少了 `IO:DataFileRead` 事件。如果某个查询具有限制性筛选条件并且仍然生成顺序扫描，请评估是否使用了正确的索引。
+ 了解查询是否正在访问非常大的表。在某些情况下，对表进行分区可以提高性能，从而允许查询只读取必要的分区。
+ 检查联接操作的基数（总行数）。请注意您在筛选条件中为您的 `WHERE` 子句传递的值的限制性。如果可能，请优化查询以减少在计划的每个步骤中传递的行数。

### 尽量减少维护操作的影响


维护操作（例如 `VACUUM` 和 `ANALYZE`）非常重要。我们建议您不要将其关闭，因为您会找到与这些维护操作相关的 `IO:DataFileRead` 等待事件。以下方法可以最大限度地减少这些操作的影响：
+ 在非高峰时段手动运行维护操作。此方法可防止数据库达到自动操作的阈值。
+ 对于非常大的表，请考虑对表进行分区。这种方法减少了维护操作的开销。数据库只访问需要维护的分区。
+ 当您摄取大量数据时，请考虑禁用自动分析功能。

当以下公式为真时，系统会自动为表触发 Autovacuum 功能。

```
pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold
```

视图 `pg_stat_user_tables` 和目录 `pg_class` 有多个行。一行可以对应于表中的一行。这个公式假设 `reltuples` 适用于特定的表。参数 `autovacuum_vacuum_scale_factor`（原定设置为 0.20）和 `autovacuum_vacuum_threshold`（原定设置为 50 个元组）通常在全局范围内为整个实例设置。但是，您可以为特定表设置不同的值。

**Topics**
+ [

#### 查找不必要地占用空间的表
](#wait-event.iodatafileread.actions.maintenance.tables)
+ [

#### 查找不必要地占用空间的索引
](#wait-event.iodatafileread.actions.maintenance.indexes)
+ [

#### 查找符合 Autovacuum 操作条件的表
](#wait-event.iodatafileread.actions.maintenance.autovacuumed)

#### 查找不必要地占用空间的表


要查找不必要地占用空间的表，可以使用 PostgreSQL `pgstattuple` 扩展中的函数。原定设置情况下，此扩展（模块）在所有 RDS for PostgreSQL 数据库实例上均可用，并且可以使用以下命令在实例上进行实例化。

```
CREATE EXTENSION pgstattuple;
```

有关此扩展的更多信息，请参阅 PostgreSQL 文档中的 [pgstattuple](https://www.postgresql.org/docs/current/pgstattuple.html)。

您可以在应用程序中检查表和索引膨胀。有关更多信息，请参[诊断表和索引膨胀](https://docs.amazonaws.cn//AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.diag-table-ind-bloat.html)。

#### 查找不必要地占用空间的索引


要查找臃肿的索引并估计在您具有读取权限的表上不必要地消耗的空间量，可以运行以下查询。

```
-- WARNING: rows with is_na = 't' are known to have bad statistics ("name" type is not supported).
-- This query is compatible with PostgreSQL 8.2 and later.

SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
  bs*(relpages-est_pages)::bigint AS extra_size,
  100 * (relpages-est_pages)::float / relpages AS extra_ratio,
  fillfactor, bs*(relpages-est_pages_ff) AS bloat_size,
  100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio,
  is_na
  -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, 
  -- maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages 
  -- (DEBUG INFO)
FROM (
  SELECT coalesce(1 +
       ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 
       -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
    ) AS est_pages,
    coalesce(1 +
       ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
    ) AS est_pages_ff,
    bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na
    -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, 
    -- index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples 
    -- (DEBUG INFO)
  FROM (
    SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,
      ( index_tuple_hdr_bm +
          maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
            WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
            ELSE index_tuple_hdr_bm%maxalign
          END
        + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
            WHEN nulldatawidth = 0 THEN 0
            WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
            ELSE nulldatawidth::integer%maxalign
          END
      )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
      -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
    FROM (
      SELECT
        i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,
        current_setting('block_size')::numeric AS bs, fillfactor,
        CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
          WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
          ELSE 4
        END AS maxalign,
        /* per page header, fixed size: 20 for 7.X, 24 for others */
        24 AS pagehdr,
        /* per page btree opaque data */
        16 AS pageopqdata,
        /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
        CASE WHEN max(coalesce(s.null_frac,0)) = 0
          THEN 2 -- IndexTupleData size
          ELSE 2 + (( 32 + 8 - 1 ) / 8) 
          -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
        END AS index_tuple_hdr_bm,
        /* data len: we remove null values save space using it fractionnal part from stats */
        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
        max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
      FROM pg_attribute AS a
        JOIN (
          SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, 
            idx.reltuples, idx.relpages, idx.relam,
            indrelid, indexrelid, indkey::smallint[] AS attnum,
            coalesce(substring(
              array_to_string(idx.reloptions, ' ')
               from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
          FROM pg_index
            JOIN pg_class idx ON idx.oid=pg_index.indexrelid
            JOIN pg_class tbl ON tbl.oid=pg_index.indrelid
            JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
          WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
        ) AS i ON a.attrelid = i.indexrelid
        JOIN pg_stats AS s ON s.schemaname = i.nspname
          AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) 
          -- stats from tbl
          OR  (s.tablename = i.idxname AND s.attname = a.attname))
          -- stats from functional cols
        JOIN pg_type AS t ON a.atttypid = t.oid
      WHERE a.attnum > 0
      GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
    ) AS s1
  ) AS s2
    JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
) AS sub
-- WHERE NOT is_na
ORDER BY 2,3,4;
```

#### 查找符合 Autovacuum 操作条件的表


要查找符合 Autovacuum 操作条件的表，请运行以下查询。

```
--This query shows tables that need vacuuming and are eligible candidates.
--The following query lists all tables that are due to be processed by autovacuum. 
-- During normal operation, this query should return very little.
WITH  vbt AS (SELECT setting AS autovacuum_vacuum_threshold 
              FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold')
    , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor 
              FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor')
    , fma AS (SELECT setting AS autovacuum_freeze_max_age 
              FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')
    , sto AS (SELECT opt_oid, split_part(setting, '=', 1) as param, 
                split_part(setting, '=', 2) as value 
              FROM (SELECT oid opt_oid, unnest(reloptions) setting FROM pg_class) opt)
SELECT
    '"'||ns.nspname||'"."'||c.relname||'"' as relation
    , pg_size_pretty(pg_table_size(c.oid)) as table_size
    , age(relfrozenxid) as xid_age
    , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age
    , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + 
         coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) 
         as autovacuum_vacuum_tuples
    , n_dead_tup as dead_tuples
FROM pg_class c 
JOIN pg_namespace ns ON ns.oid = c.relnamespace
JOIN pg_stat_all_tables stat ON stat.relid = c.oid
JOIN vbt on (1=1) 
JOIN vsf ON (1=1) 
JOIN fma on (1=1)
LEFT JOIN sto cvbt ON cvbt.param = 'autovacuum_vacuum_threshold' AND c.oid = cvbt.opt_oid
LEFT JOIN sto cvsf ON cvsf.param = 'autovacuum_vacuum_scale_factor' AND c.oid = cvsf.opt_oid
LEFT JOIN sto cfma ON cfma.param = 'autovacuum_freeze_max_age' AND c.oid = cfma.opt_oid
WHERE c.relkind = 'r' 
AND nspname <> 'pg_catalog'
AND (
    age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
    or
    coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + 
      coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup
    -- or 1 = 1
)
ORDER BY age(relfrozenxid) DESC;
```

### 响应大量连接


当您监控 Amazon CloudWatch 时，您可能会发现 `DatabaseConnections` 指标激增。这种增加表示与数据库的连接数量有所增加。我们建议采取以下方法：
+ 限制应用程序可与每个实例一起打开的连接数。如果您的应用程序具有嵌入式连接池功能，请设置合理数量的连接。根据实例中的 vCPU 可以有效并行处理的数量来确定数量。

  如果您的应用程序没有使用连接池功能，请考虑使用 Amazon RDS 代理或替代方案。这种方法允许您的应用程序打开与负载均衡器的多个连接。然后，均衡器可以打开与数据库的数量有限的连接。由于并行运行的连接减少，您的数据库实例在内核中执行的上下文切换会减少。查询的进度应该更快，从而导致等待事件减少。有关更多信息，请参阅 [Amazon RDS 代理](rds-proxy.md)。
+ 尽可能利用 RDS for PostgreSQL 的只读副本。当您的应用程序运行只读操作时，将这些请求发送到只读副本。此方法可减少主（写入器）节点上的输入/输出压力。
+ 请考虑纵向扩展数据库实例。更高容量的实例类可提供更多内存，这为 RDS for PostgreSQL 提供了一个更大的共享缓冲池来容纳页面。较大的大小还为数据库实例提供了更多的 vCPU 来处理连接。当生成 `IO:DataFileRead` 等待事件的操作为写入时，更多的 vCPU 会特别有用。