# IO:DataFileRead El evento `IO:DataFileRead` ocurre cuando una conexión espera en un proceso backend para leer una página requerida del almacenamiento porque la página no está disponible en la memoria compartida. **Topics** + [Versiones del motor admitidas](#apg-waits.iodatafileread.context.supported) + [Contexto](#apg-waits.iodatafileread.context) + [Causas probables del aumento de las esperas](#apg-waits.iodatafileread.causes) + [Acciones](#apg-waits.iodatafileread.actions) ## Versiones del motor admitidas Esta información de eventos de espera es compatible con todas las versiones de Aurora PostgreSQL. ## Contexto Todas las consultas y operaciones de manipulación de datos (DML) acceden a páginas en el grupo de búferes. Las instrucciones que pueden inducir lecturas incluyen `SELECT`, `UPDATE` y `DELETE`. Por ejemplo, un `UPDATE` puede leer páginas de tablas o índices. Si la página que se solicita o actualiza no está en el grupo de búferes compartidos, esta lectura puede provocar el evento `IO:DataFileRead`. Dado que el grupo de búferes compartidos es finito, puede llenarse. En este caso, las solicitudes de páginas que no están en la memoria obligan a la base de datos a leer bloques del disco. Si el evento `IO:DataFileRead` se produce con frecuencia, es posible que el grupo de búferes compartidos sea demasiado pequeño para acomodar la carga de trabajo. Este problema se agudiza en las consultas `SELECT` que leen un gran número de filas que no caben en el grupo de búferes. Para más información sobre el grupo de búferes, consulte [Grupo de búferes](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool). ## Causas probables del aumento de las esperas Las causas más comunes del evento `IO:DataFileRead` son las siguientes: **Picos de conexión** Es posible que varias conexiones generen el mismo número de eventos de espera IO:DataFileRead. En este caso, puede producirse un pico (aumento repentino y grande) en los eventos `IO:DataFileRead`. **Instrucciones SELECT y DML que hacen escaneos secuenciales** Es posible que la aplicación realice una nueva operación. O una operación existente podría cambiar debido a un nuevo plan de ejecución. En estos casos, busque las tablas (particularmente las tablas grandes) que tengan un valor de `seq_scan` mayor. Encuéntrelas mediante la consulta de `pg_stat_user_tables`. Para rastrear las consultas que generan más operaciones de lectura, utilice la extensión `pg_stat_statements`. **CTAS y CREATE INDEX para grandes conjuntos de datos** Un *CTAS* es una instrucción `CREATE TABLE AS SELECT`. Si ejecuta un CTAS con un conjunto de datos grande como origen, o crea un índice en una tabla grande, puede producirse el evento `IO:DataFileRead`. Cuando se crea un índice, es posible que la base de datos tenga que leer todo el objeto mediante una exploración secuencial. Un CTAS genera lecturas `IO:DataFile` cuando las páginas no están en la memoria. **Varios procesos de trabajo de vacío que se ejecutan al mismo tiempo** Los procesos de trabajo de vacío pueden activarse de forma manual o automática. Se recomienda adoptar una estrategia de vacío agresiva. Sin embargo, cuando una tabla tiene muchas filas actualizadas o eliminadas, las esperas `IO:DataFileRead` aumentan. Una vez recuperado el espacio, el tiempo de vacío dedicado a `IO:DataFileRead` disminuye. **Ingesta de grandes cantidades de datos** Cuando la aplicación ingiere grandes cantidades de datos, las operaciones `ANALYZE` pueden producirse con mayor frecuencia. El proceso `ANALYZE` se puede activar mediante un desencadenador de autovacuum o invocarse de forma manual. La operación `ANALYZE` lee un subconjunto de la tabla. El número de páginas que deben ser escaneadas se calcula al multiplicar 30 por el valor de `default_statistics_target`. Para obtener más información, consulte la [documentación de PostgreSQL](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET). El parámetro `default_statistics_target` acepta valores entre 1 y 10 000, siendo el valor por defecto 100. **Falta de recursos** Si el ancho de banda de la red de la instancia o la CPU se consumen, el evento `IO:DataFileRead` podría ocurrir con más frecuencia. ## Acciones Recomendamos diferentes acciones en función de las causas del evento de espera. **Topics** + [Verificar los filtros de predicado para las consultas que generan esperas](#apg-waits.iodatafileread.actions.filters) + [Minimizar el efecto de las operaciones de mantenimiento](#apg-waits.iodatafileread.actions.maintenance) + [Responder a un gran número de conexiones](#apg-waits.iodatafileread.actions.connections) ### Verificar los filtros de predicado para las consultas que generan esperas Supongamos que identifica consultas específicas que generan eventos de espera `IO:DataFileRead`. Puede identificarlos con las siguientes técnicas: + Información sobre rendimiento + Vistas de catálogo como la que proporciona la extensión `pg_stat_statements` + La vista de catálogo `pg_stat_all_tables`, si muestra de forma periódica un aumento del número de lecturas físicas + La vista `pg_statio_all_tables`, si muestra que los contadores `_read` aumentan Le recomendamos que determine qué filtros se utilizan en el predicado (cláusula `WHERE`) de estas consultas. Siga estas instrucciones: + Ejecute el comando `EXPLAIN`. En la salida, identifique qué tipos de escaneos se utilizan. Un escaneo secuencial no indica necesariamente un problema. Las consultas que utilizan escaneos secuenciales producen naturalmente más eventos `IO:DataFileRead` en comparación con las consultas que utilizan filtros. Averigüe si la columna que aparece en la cláusula `WHERE` se encuentra en un índice. Si no es así, considere la posibilidad de crear un índice para esta columna. Este enfoque evita los escaneos secuenciales y reduce los eventos `IO:DataFileRead`. Si una consulta tiene filtros restrictivos y produce aún escaneos secuenciales, evalúe si se utilizan los índices adecuados. + Verifique si la consulta tiene acceso a una tabla muy grande. En algunos casos, la partición de una tabla puede mejorar el rendimiento, ya que permite que la consulta solo lea las particiones necesarias. + Examine la cardinalidad (número total de filas) de sus operaciones de unión. Tenga en cuenta lo restrictivos que son los valores que se pasan en los filtros de la cláusula `WHERE`. Si es posible, ajuste su consulta para reducir el número de filas que se pasan en cada paso del plan. ### Minimizar el efecto de las operaciones de mantenimiento Las operaciones de mantenimiento como `VACUUM` y `ANALYZE` son importantes. Le recomendamos que no las desactive ya que encontrará eventos de espera `IO:DataFileRead` relacionados con estas operaciones de mantenimiento. Los siguientes enfoques pueden minimizar el efecto de estas operaciones: + Ejecute las operaciones de mantenimiento de forma manual durante las horas de menor actividad. Esta técnica evita que la base de datos alcance el umbral de las operaciones automáticas. + Para tablas muy grandes, considere la posibilidad de particionar la tabla. Esta técnica reduce la sobrecarga de las operaciones de mantenimiento. La base de datos solo accede a las particiones que requieren mantenimiento. + Cuando capture grandes cantidades de datos, considere la posibilidad de desactivar la característica de autoanálisis. La característica de autovacuum se activa de forma automática para una tabla cuando la siguiente fórmula es verdadera. ``` pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold ``` La vista `pg_stat_user_tables` y el catálogo `pg_class` tienen varias filas. Una fila puede corresponder a una fila de la tabla. Esta fórmula asume que las `reltuples` son para una tabla específica. Los parámetros `autovacuum_vacuum_scale_factor` (0,20 de forma predeterminada) y `autovacuum_vacuum_threshold` (50 tuplas de forma predeterminada) se suelen establecer de forma global para toda la instancia. Sin embargo, puede establecer valores diferentes para una tabla específica. **Topics** + [Busque tablas que consuman espacio de una forma innecesaria](#apg-waits.iodatafileread.actions.maintenance.tables) + [Buscar índices que consumen espacio innecesario](#apg-waits.iodatafileread.actions.maintenance.indexes) + [Buscar tablas aptas para autovacuum](#apg-waits.iodatafileread.actions.maintenance.autovacuumed) #### Busque tablas que consuman espacio de una forma innecesaria Para buscar tablas que consumen más espacio del necesario, ejecute la siguiente consulta. Cuando esta consulta la ejecuta un rol de usuario de base de datos que no tiene el rol `rds_superuser`, devuelve información únicamente sobre las tablas para las que el rol de usuario tiene permisos de lectura. Esta consulta es compatible con la versión 12 y las versiones posteriores de PostgreSQL. ``` WITH report AS ( SELECT schemaname ,tblname ,n_dead_tup ,n_live_tup ,block_size*tblpages AS real_size ,(tblpages-est_tblpages)*block_size 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)*block_size 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 FROM ( SELECT ceil( reltuples / ( (block_size-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages ,ceil( reltuples / ( (block_size-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff ,tblpages ,fillfactor ,block_size ,tblid ,schemaname ,tblname ,n_dead_tup ,n_live_tup ,heappages ,toastpages ,is_na 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 ,block_size - page_hdr AS size_per_block ,(heappages + toastpages) AS tblpages ,heappages ,toastpages ,reltuples ,toasttuples ,block_size ,page_hdr ,tblid ,schemaname ,tblname ,fillfactor ,is_na ,n_dead_tup ,n_live_tup FROM ( SELECT tbl.oid AS tblid ,ns.nspname AS schemaname ,tbl.relname AS tblname ,tbl.reltuples AS reltuples ,tbl.relpages AS heappages ,coalesce(toast.relpages, 0) AS toastpages ,coalesce(toast.reltuples, 0) AS toasttuples ,psat.n_dead_tup AS n_dead_tup ,psat.n_live_tup AS n_live_tup ,24 AS page_hdr ,current_setting('block_size')::numeric AS block_size ,coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor ,CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma ,23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int 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_stat_all_tables AS psat ON (tbl.oid = psat.relid) 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 tbl.oid, ns.nspname, tbl.relname, tbl.reltuples, tbl.relpages, toastpages, toasttuples, fillfactor, block_size, ma, n_dead_tup, n_live_tup ORDER BY schemaname, tblname ) AS s ) AS s2 ) AS s3 ORDER BY bloat_size DESC ) SELECT * FROM report WHERE bloat_ratio != 0 -- AND schemaname = 'public' -- AND tblname = 'pgbench_accounts' ; -- WHERE NOT is_na -- AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1 ``` Puede comprobar si hay una sobrecarga de tablas e índices en su aplicación. Para obtener más información, consulte [Diagnóstico de sobrecarga de tablas e índices](AuroraPostgreSQL.diag-table-ind-bloat.md). #### Buscar índices que consumen espacio innecesario Para buscar índices que consumen espacio innecesario, ejecute la siguiente consulta. ``` -- 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; ``` #### Buscar tablas aptas para autovacuum Para buscar tablas que se puedan vaciar automáticamente, ejecute la siguiente consulta. ``` --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; ``` ### Responder a un gran número de conexiones Cuando se monitorea Amazon CloudWatch, se puede encontrar que la métrica `DatabaseConnections` se dispara. Este aumento indica un mayor número de conexiones a su base de datos. Se recomienda el siguiente enfoque: + Limite el número de conexiones que la aplicación puede abrir con cada instancia. Si la aplicación tiene una característica de grupo de conexiones integrada, establezca un número razonable de conexiones. Base el número en lo que las vCPU de su instancia puedan paralelizar de forma efectiva. Si su aplicación no utiliza una característica de grupo de conexiones, considere utilizar Amazon RDS Proxy o una alternativa. Este enfoque permite que su aplicación abra varias conexiones con el equilibrador de carga. El equilibrador puede entonces abrir un número restringido de conexiones con la base de datos. Como se ejecutan menos conexiones en paralelo, la instancia de base de datos hace menos cambios de contexto en el núcleo. Las consultas deberían progresar más rápido, lo que provocaría menos eventos de espera. Para obtener más información, consulte [Amazon RDS Proxy para Aurora](rds-proxy.md). + Siempre que sea posible, aproveche los nodos de lectura para Aurora PostgreSQL y las réplicas de lectura para RDS for PostgreSQL. Cuando la aplicación ejecute una operación de solo lectura, envíe estas solicitudes al punto de conexión de solo lectura. Esta técnica reparte las peticiones de la aplicación entre todos los nodos de lectura, lo que reduce la presión de E/S en el nodo de escritura. + Considere la posibilidad de escalar verticalmente su instancia de base de datos. Una clase de instancia de mayor capacidad proporciona más memoria, lo que le da a Aurora PostgreSQL un grupo de búferes compartidos más grande para mantener las páginas. El tamaño más grande también le da a la instancia de base de datos más vCPU para manejar las conexiones. Más vCPU son especialmente útiles cuando las operaciones que generan eventos de espera `IO:DataFileRead` se escriben.