IO:DataFileRead - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

IO:DataFileRead

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

支持的引擎版本

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

Context

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

由于共享缓冲池是有限的,所以它可以填满。在这种情况下,对不在内存中的页面的请求会强制数据库从磁盘中读取数据块。如果 IO:DataFileRead 事件频繁发生,您的共享缓冲池可能太小,从而无法容纳您的工作负载。对于读取大量不适合缓冲池的行的 SELECT 查询,此问题很严重。有关缓冲池的更多信息,请参阅 缓冲池

等待次数增加的可能原因

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 文档default_statistics_target 参数接受 1 到 10000 之间的值,其中原定设置值为 100。

资源匮乏

如果消耗了实例网络带宽或 CPU,IO:DataFileRead 事件可能会更频繁地发生。

Actions

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

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

假设您确定了正在生成 IO:DataFileRead 等待事件的特定查询。您可以使用以下方法识别它们:

  • Performance Insights

  • 目录视图,例如扩展程序 pg_stat_statements 提供的视图

  • 目录视图 pg_stat_all_tables,如果它定期显示物理读取数量增加

  • pg_statio_all_tables 视图,如果它显示 _read 计数器正在增加

我们建议您确定这些查询的谓词(WHERE 子句)中使用了哪些筛选条件。请遵循以下准则:

  • 运行 EXPLAIN 命令。在输出中,确定使用的扫描类型。顺序扫描不一定表示存在问题。与使用筛选条件的查询相比,使用顺序扫描的查询自然会产生更多的 IO:DataFileRead 事件。

    了解 WHERE 子句中列出的列是否已编入索引。如果没有,请考虑为此列创建索引。这种方法避免了顺序扫描并减少了 IO:DataFileRead 事件。如果某个查询具有限制性筛选条件并且仍然生成顺序扫描,请评估是否使用了正确的索引。

  • 了解查询是否正在访问非常大的表。在某些情况下,对表进行分区可以提高性能,从而允许查询只读取必要的分区。

  • 检查联接操作的基数(总行数)。请注意您在筛选条件中为您的 WHERE 子句传递的值的限制性。如果可能,请优化查询以减少在计划的每个步骤中传递的行数。

尽量减少维护操作的影响

维护操作(例如 VACUUMANALYZE)非常重要。我们建议您不要将其关闭,因为您会找到与这些维护操作相关的 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 个元组)通常在全局范围内为整个实例设置。但是,您可以为特定表设置不同的值。

查找占用不必要空间的表

要查找占用不必要空间的表,请运行以下查询。

/* WARNING: Run with a nonsuperuser role, the query inspects only tables * that you have the permission to read. * This query is compatible with PostgreSQL 9.0 and later. */ SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size, (tblpages-est_tblpages)*bs AS extra_size, CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages)/tblpages::float ELSE 0 END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float ELSE 0 END AS bloat_ratio, is_na -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages, ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT ( 4 + tpl_hdr_size + tpl_data_size + (2*ma) - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size, sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids ORDER BY 2,3 ) AS s ) AS s2 ) AS s3 ; -- WHERE NOT is_na -- AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1

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

要查找占用不必要空间的索引,请运行以下查询。

-- WARNING: run with a nonsuperuser role, the query inspects -- only indexes on tables you have permissions to read. -- 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 functionnal 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) DES;

响应大量连接

当您监控 Amazon CloudWatch 时,您可能会发现 DatabaseConnections 指标激增。这种增加表示与数据库的连接数量有所增加。我们建议采取以下方法:

  • 限制应用程序可与每个实例一起打开的连接数。如果您的应用程序具有嵌入式连接池功能,请设置合理数量的连接。根据实例中的 vCPU 可以有效并行处理的数量来确定数量。

    如果您的应用程序没有使用连接池功能,请考虑使用 Amazon RDS 代理或替代方案。这种方法允许您的应用程序打开与负载均衡器的多个连接。然后,均衡器可以打开与数据库的数量有限的连接。由于并行运行的连接减少,您的数据库实例在内核中执行的上下文切换会减少。查询的进度应该更快,从而导致等待事件减少。有关更多信息,请参阅 使用 Amazon RDS Proxy

  • 尽可能利用 Aurora PostgreSQL 的读取器节点和 RDS for PostgreSQL 的只读副本。当您的应用程序运行只读操作时,将这些请求发送到只读端点。此方法将应用程序请求分布到所有读取器节点,从而减少了写入器节点的输入/输出压力。

  • 请考虑纵向扩展数据库实例。更高容量的实例类可提供更多内存,这为 Aurora PostgreSQL 提供了一个更大的共享缓冲池来容纳页面。较大的大小还为数据库实例提供了更多的 vCPU 来处理连接。当生成 IO:DataFileRead 等待事件的操作为写入时,更多的 vCPU 会特别有用。