# IO:DataFileRead `IO:DataFileRead`イベントは、バックエンドプロセスが必要なページを読み込む際に、ページが共有メモリで使用できないため接続が待機したときに発生します。 **Topics** + [サポート対象エンジンバージョン](#apg-waits.iodatafileread.context.supported) + [Context](#apg-waits.iodatafileread.context) + [待機時間が増加する原因の可能性](#apg-waits.iodatafileread.causes) + [アクション](#apg-waits.iodatafileread.actions) ## サポート対象エンジンバージョン この待機イベント情報は、Aurora PostgreSQL のすべてのバージョンでサポートされています。 ## Context すべてのクエリおよびデータ操作 (DML) オペレーションは、バッファプール内のページにアクセスします。読み取りを誘発できるステートメントには、`SELECT`、`UPDATE`、`DELETE`があります。例えば、`UPDATE`は、テーブルまたはインデックスからページを読み取ることができます。要求または更新中のページが共有バッファプールにない場合、この読み取りは`IO:DataFileRead`イベントにつながることがあります。 共有バッファプールは有限のため、いっぱいになる可能性があります。この場合、メモリ上にないページをリクエストすると、データベースは強制的にディスクからブロックを読み取ることになります。`IO:DataFileRead`イベントが頻繁に発生する場合は、共有バッファプールが小さすぎるとワークロードに対応できない可能性があります。この問題は、バッファプールに収まらない多数の行読み取る`SELECT`クエリでは深刻です。バッファプールの詳細については、「[バッファプール](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool)」を参照してください。 ## 待機時間が増加する原因の可能性 `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** + [待機を生成するクエリの述語フィルターをチェックする](#apg-waits.iodatafileread.actions.filters) + [メンテナンス作業の影響を最小化する](#apg-waits.iodatafileread.actions.maintenance) + [多数の接続に対応する](#apg-waits.iodatafileread.actions.connections) ### 待機を生成するクエリの述語フィルターをチェックする `IO:DataFileRead`待機イベントを生成する特定のクエリを特定するとします。これらは、次の方法を使用して識別できることがあります。 + Performance Insights + エクステンション`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`には複数の行があります。1 行は、テーブル内の 1 つの行に対応できます。この公式は、`reltuples`が特定のテーブル用だと仮定しています。パラメータ `autovacuum_vacuum_scale_factor` (デフォルトは 0.20) と `autovacuum_vacuum_threshold` (デフォルトでは 50 タプル) は通常、インスタンス全体に対してグローバルに設定されます。ただし、特定のテーブルに対して異なる値を設定できます。 **Topics** + [不要な領域を消費しているテーブルを探す](#apg-waits.iodatafileread.actions.maintenance.tables) + [不要なスペースを消費しているインデックスを見つける](#apg-waits.iodatafileread.actions.maintenance.indexes) + [オートバキュームの対象となるテーブルを見つける](#apg-waits.iodatafileread.actions.maintenance.autovacuumed) #### 不要な領域を消費しているテーブルを探す 必要以上の領域を消費しているテーブルを見つけるには、次のクエリを実行します。このクエリが `rds_superuser` ロールを持たないデータベースユーザーロールによって実行されると、そのユーザーロールが読み取り権限を持っているテーブルに関する情報のみが返されます。このクエリは、PostgreSQL バージョン 12 以降のバージョンでサポートされています。 ``` 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 ``` アプリケーション内のテーブルとインデックスの肥大化をチェックできます。詳細については、「[診断テーブルとインデックスの肥大化](AuroraPostgreSQL.diag-table-ind-bloat.md)」を参照してください。 #### 不要なスペースを消費しているインデックスを見つける 不要な領域を消費しているインデックスを見つけるには、次のクエリを実行します。 ``` -- 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; ``` #### オートバキュームの対象となるテーブルを見つける 自動バキュームの対象となるテーブルを見つけるには、次のクエリを実行します。 ``` --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`メトリックスパイクが見つかることがあります。この増加は、データベースへの接続数が増加していることを示します。次のようなアプローチを推奨します。 + アプリケーションが各インスタンスで開くことができる接続の数を制限します。アプリケーションが組み込み接続プール機能を備えている場合は、適切な数の接続を設定します。インスタンス内の vCPUs が効果的にパラレル化できる数値を基準にします。 アプリケーションで接続プール機能を使用しない場合は、Amazon RDS プロキシまたは代替の使用を検討してください。このアプローチにより、アプリケーションはロードバランサーとの複数の接続を開くことができます。その後、バランサーは、データベースとの制限された数の接続を開くことができます。パラレルで実行される接続が少なくなると、DB インスタンスのカーネル内のコンテキスト切り替えが減少します。クエリの進行が速くなり、待機イベントが減少するはずです。詳細については、「[Amazon RDS Proxy for Aurora](rds-proxy.md)」を参照してください。 + 可能であれば、Aurora PostgreSQL のリーダーノードと RDS for PostgreSQL のリードレプリカを活用してください。アプリケーションが読み取り専用のオペレーションを実行するときは、これらのリクエストを読み取り専用のエンドポイントに送信します。この方法は、アプリケーションのリクエストをすべてのリーダーノードに分散させ、ライターノードの I/O 負荷を軽減します。 + DB インスタンスのスケールアップを検討します。大容量のインスタンスクラスはより多くのメモリを提供するため、Aurora PostgreSQL ではページを保持するためのより大きな共有バッファプールを提供します。サイズが大きければ、DB インスタンスが接続処理する vCPUs も多くなります。特に、`IO:DataFileRead`待機イベントを発生させているオペレーションが書き込みの場合、vCPU の増設は有効です。