本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # IO:DataFileRead `IO:DataFileRead` 事件表示因為所需的分頁不在共用記憶體中，連線等待後端程序從儲存讀取該分頁。 **Topics** + [支援的引擎版本](#wait-event.iodatafileread.context.supported) + [Context](#wait-event.iodatafileread.context) + [等待時間增加的可能原因](#wait-event.iodatafileread.causes) + [動作](#wait-event.iodatafileread.actions) ## 支援的引擎版本 所有 RDS for PostgreSQL 版本都支援此等待事件資訊。 ## Context 所有查詢和資料操作 (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` 讀取。 **多個清理工作者同時執行** 清理工作者是手動或自動觸發。建議採取積極清理策略。不過，當資料表更新或刪除許多資料列時，`IO:DataFileRead` 等待會變多。回收空間後，花在 `IO:DataFileRead` 的清理時間就會減少。 **擷取大量資料** 當應用程式擷取大量資料時，`ANALYZE` 操作可能更頻繁發生。`ANALYZE` 程序可以由自動資料清理啟動器觸發，或手動叫用。 `ANALYZE` 操作讀取資料表的子集。將 30 乘以 `default_statistics_target` 值，即可算出必須掃描的分頁數。如需詳細資訊，請參閱 [PostgreSQL 文件](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET)。`default_statistics_target` 參數接受 1 到 10,000 之間的值，預設值為 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` 等待事件而關閉維護。下列方法可以將這些操作的影響降至最低： + 在離峰時段手動執行維護操作。這項技巧可防止資料庫達到自動化操作的閾值。 + 如果資料表非常大，請考慮分割資料表。這項技巧可減少維護操作的額外負荷。資料庫只會存取需要維護的分割區。 + 擷取大量資料時，請考慮停用自動分析功能。 下列公式成立時會自動對資料表觸發自動資料清理功能。 ``` 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) + [尋找適合自動資料清理的資料表](#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.aws.amazon.com//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; ``` #### 尋找適合自動資料清理的資料表 若要尋找適合自動資料清理的資料表，請執行下列查詢。 ``` --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 Proxy 或替代方案。這種作法可讓應用程式對負載平衡器開啟多個連線。因此，平衡器就能對資料庫開啟較少的連線。由於平行執行的連線較少，資料庫執行個體在核心中就能減少切換環境。查詢應該會進行得更快，使得等待事件變少。如需詳細資訊，請參閱[Amazon RDS Proxy ](rds-proxy.md)。 + 盡可能利用 RDS for PostgreSQL 的僅供讀取複本。當您的應用程式執行唯讀操作時，請將這些請求傳送至僅供讀取複本。這項技巧可減少主要 (寫入器) 節點的 I/O 壓力。 + 考慮擴充資料庫執行個體的規模。容量較大的執行個體類別提供更多記憶體，讓 RDS for PostgreSQL 有較大的共用緩衝集區可保留分頁。越大也讓資料庫執行個體有越多 vCPU 來處理連線。當寫入操作產生 `IO:DataFileRead` 等待事件時，較多 vCPU 會特別有用。