IO:BufFileRead および IO:BufFileWrite - Amazon Relational Database Service
サポート対象エンジンバージョンContext待機時間が増加する原因の可能性アクション

IO:BufFileRead および IO:BufFileWrite

IO:BufFileRead と IO:BufFileWrite イベントは、RDS for PostgreSQL がテンポラリファイルを作成するときに発生します。作業メモリパラメータが現在の定義より多くのメモリを必要とするオペレーションは、テンポラリデータを永続的ストレージに書き込みます。この操作は「spilling to disk (ディスクへの流出)」と呼ばれることがあります。

サポート対象エンジンバージョン

この待機イベント情報は、RDS for PostgreSQL のすべてのバージョンでサポートされています。

Context

IO:BufFileReadそしてIO:BufFileWriteは、作業メモリ領域とメンテナンス作業用メモリ領域に関連します。これらのローカルメモリ領域の詳細については、PostgreSQL ドキュメントの「リソース消費」を参照してください。

デフォルト値は work_mem 4 MB です。一つのセッションがパラレルにオペレーションを実行する場合、パラレル処理を行う各ワーカーは 4 MB のメモリを使用します。このため、work_memを慎重に設定してください。値を大きくしすぎると、多くのセッションを実行しているデータベースがメモリを過剰に消費することがあります。値を低く設定しすぎると、RDS for PostgreSQL はローカルストレージに一時ファイルを作成します。これらのテンポラリファイルのためのディスク I/O により、パフォーマンスが低下する可能性があります。

次のようなイベントが発生する場合、データベースがテンポラリファイルを生成している可能性があります。

  1. 可用性の急激な低下

  2. 空き領域の高速リカバリ

また、「チェーンソー」のパターンが表示されるかもしれません。このパターンは、データベースが小さなファイルを常に作成していることを示す可能性があります。

待機時間が増加する原因の可能性

一般に、これらの待機イベントは、work_memまたはmaintenance_work_memパラメータが割り当てられるよりも多くのメモリを消費するオペレーションによって発生します。補うために、オペレーションはテンポラリファイルに書き込みます。IO:BufFileReadそしてIO:BufFileWriteイベントの一般的な原因には、次のようなものがあります。

作業用メモリ領域に存在するメモリより多くのメモリを必要とするクエリ

次の特性を持つクエリは、作業メモリ領域を使用します。

  • ハッシュ結合

  • ORDER BY 句

  • GROUP BY 句

  • DISTINCT

  • Window 関数

  • CREATE TABLE AS SELECT

  • REFRESH MATERIALIZED VIEW

メンテナンス作業メモリ領域に存在するメモリより多くのメモリを必要とするステートメント

次のステートメントは、メンテナンス作業メモリ領域を使用します。

  • CREATE INDEX

  • CLUSTER

アクション

待機イベントの原因に応じたさまざまなアクションをお勧めします。

問題の特定

Performance Insights がオンではない状況で、IO:BufFileReadとIO:BufFileWriteが通常よりも頻繁に発生している疑いがあると想定します。問題の原因を特定するには、指定したしきい値 KB を超える一時ファイルを生成するすべてのクエリをログに記録するように log_temp_files パラメータを設定できます。デフォルトでは、log_temp_files は -1 に設定され、このロギング機能は無効になります。このパラメータを 0 に設定した場合は、RDS for PostgreSQL はすべての一時ファイルをログに記録します。値を 1024 に設定した場合、RDS for PostgreSQL は 1 MB を超える一時ファイルを生成するすべてのクエリをログに記録します。log_temp_filesについての詳細は、PostgreSQL ドキュメントのError reporting and logging を参照してください。

ジョイントクエリを検証する

クエリでは、結合が使用されている可能性があります。例えば、次のクエリは 4 つのテーブルをジョイントします。

SELECT * 
       FROM "order" 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

テンポラリファイル使用量が急増する原因は、クエリ自体の問題の可能性があります。例えば、壊れた節はジョイントを適切にフィルタリングしない可能性があります。次の例では 2 番目の内部ジョイントを考えてみましょう。

SELECT * 
       FROM "order"
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

前のクエリが誤ってcustomer.idをcustomer.idにジョイントし、すべての顧客とすべての注文の間にデカルト積を生成します。このタイプの偶発的なジョイントは、大きなテンポラリファイルを生成します。テーブルのサイズによっては、デカルトクエリでストレージがいっぱいになることもあります。以下の条件を満たす場合は、アプリケーションにデカルトジョインが生成される場合があります。

  • ストレージの可用性が大きく急激に低下し、その後、高速リカバリが起こります。

  • インデックスは作成されていません。

  • CREATE TABLE FROM SELECTステートメントは発行されていません。

  • マテリアライズドビューはリフレッシュされません。

テーブルが適切なキーを使用してジョイントされているかどうかを確認するには、クエリおよびオブジェクト関係マッピングディレクティブを調べます。アプリケーションの特定のクエリは常に呼び出されるわけではなく、一部のクエリは動的に生成されることに注意してください。

ORDER BY クエリと GROUP BY クエリを検証する

場合によっては、ORDER BY節を使用するとテンポラリファイルが過剰になる可能性があります。以下のガイドラインを検討します。

  • 順序付けが必要な場合のみ、ORDER BYに列を含めてください。このガイドラインは、数千行を返し、ORDER BY節で多数の列を指定するクエリでは特に重要です。

  • ORDER BY節が同じ昇順または降順の列にマッチする場合、高速化するためにインデックスの作成を検討します。パーシャルインデックスのほうが小さいため好ましいです。小さいインデックスは、より迅速に読み込まれ、トラバースされます。

  • NULL 値を受け入れることができる列のインデックスを作成する場合は、NULL 値をインデックスの最後に格納するか、先頭に格納するかを検討します。

    可能であれば、結果セットをフィルタリングして、順序付けが必要な行の数を減らします。WITH節ステートメントまたはサブクエリを使用する場合、内部クエリが結果セットを生成し、外部クエリに渡すことに注意してください。クエリが行をより多くフィルタリングすると、クエリが行う必要がある順序付けは減ります。

  • 完全な結果セットを取得する必要がない場合は、LIMIT節を使用します。例えば、上位 5 行だけが必要な場合、LIMIT節を使用したクエリは結果を生成し続けることはありません。このように、クエリに必要なメモリとテンポラリファイルが減ります。

GROUP BY 句を使用するクエリは、テンポラリファイルを要求することもできます。GROUP BY クエリは、次のような関数を使用して値を要約します。

  • COUNT

  • AVG

  • MIN

  • MAX

  • SUM

  • STDDEV

GROUP BYクエリをチューニングするには、ORDER BYクエリの推奨事項に従ってください。

DISTINCT オペレーションの使用を避ける

可能であれば、DISTINCTオペレーションを使用して重複した行を削除することは避けてください。クエリが返す行が不要かつ重複していればいるほど、VDISTINCTオペレーションのコストは高くなります。可能であれば、異なるテーブルに対して同じフィルターを使用している場合でも、WHERE節でフィルターを追加してください。クエリをフィルタリングして正しく結合すると、パフォーマンスが向上し、リソースの使用量が削減されます。また、誤ったレポートや結果を防ぐことができます。

DISTINCTを同じテーブルの複数の行に使用する必要がある場合、複合インデックスの作成を検討してください。インデックスに複数の列をグループ化すると、個別の行を評価する時間を短縮できます。また、RDS for PostgreSQL バージョン 10 以降を使用している場合は、CREATE STATISTICS コマンドを使用して複数の列間で統計を関連付けられます。

GROUP BY 関数の代わりにウィンドウ関数の使用を検討してください。

GROUP BYを使用すると、結果セットを変更し、集計結果を取得できます。ウィンドウ関数を使用すると、結果セットを変更せずにデータを集計できます。ウィンドウ関数は、OVER句を使用して、クエリによって定義されたセット間で計算を実行し、ある行を別の行に関連付けます。ウィンドウ関数に含まれるすべてのGROUP BY関数は使用できますが、次のような関数も使用可能です。

  • RANK

  • ARRAY_AGG

  • ROW_NUMBER

  • LAG

  • LEAD

ウィンドウ関数によって生成されるテンポラリファイルの数を最小限に抑えるには、2 つの異なる集計が必要な場合は同じ結果セットの重複を削除してください。次のクエリについて考えます。

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

WINDOW節のクエリは、次のように書き換えることができます。

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

デフォルトでは、RDS for PostgreSQL 実行プランナーは類似したノードを統合し、オペレーションが重複しないようにします。ただし、ウィンドウブロックに明示的な宣言を使用すると、クエリをより簡単に維持できます。また、重複を防止するとパフォーマンスの向上につながることがあります。

マテリアライズドビューと CTAS ステートメントの調査

マテリアライズドビューがリフレッシュされると、クエリが実行されます。このクエリには、GROUP BY、ORDER BY、DISTINCTのような操作を含めることができます。リフレッシュ中に、大量のテンポラリファイルや待機イベントIO:BufFileWriteおよびIO:BufFileReadが発生することがあります。同様に、SELECTに基づいてテーブルを作成すると、CREATE TABLEステートメントはクエリを実行します。必要なテンポラリファイルを減らすには、クエリを最適化します。

インデックスの再構築時に pg_repack を使用する

インデックスを作成すると、エンジンは結果セットを順序付けます。テーブルのサイズが大きくなり、インデックスで指定された列の値が多様化していくと、テンポラリファイルはより多くの領域を必要とします。ほとんどの場合、メンテナンス作業のメモリ領域を変更しなければ、大きなテーブルのテンポラリファイルの作成を防ぐことはできません。maintenance_work_mem の詳細については、PostgreSQL のドキュメントの「https://www.postgresql.org/docs/current/runtime-config-resource.html」を参照してください。

大きなインデックスを再作成するときに考えられる回避策としては、pg_repack 拡張機能を使用することが挙げられます。詳細については、「pg_repack のドキュメント」で「最小限のロックで PostgreSQL データベース内のテーブルを再編成する」を参照してください。RDS for PostgreSQL DB インスタンスに対する拡張機能の設定については、「pg_repack 拡張機能を使用して、テーブルやインデックスの膨張を抑制する」を参照してください。

テーブルをクラスター化するときに maintenance_work_mem を増やす

CLUSTERコマンドは、index_nameで指定した既存のインデックスに基づいて、table_nameで指定したテーブルをクラスター化します。RDS for PostgreSQL は、指定されたインデックスの順序に一致するようにテーブルを物理的に再作成します。

磁気ストレージが普及していたころは、ストレージのスループットが限られていたため、クラスター化が一般的でした。今では、SSD ベースのストレージが一般的となり、クラスター化はあまり一般的ではなくなっています。ただし、テーブルをクラスター化すると、テーブルのサイズ、インデックス、クエリなどによってパフォーマンスが多少向上することがあります。

CLUSTERコマンドを実行して、待機イベントIO:BufFileWrite、IO:BufFileReadをモニタリングし、maintenance_work_memをチューニングします。メモリサイズをかなり大きくしてください。高い値は、エンジンがクラスター化オペレーションのためにより多くのメモリを使用できることを意味します。

IO:BufFileRead および IO:BufFileWrite を防ぐためにメモリを調整します

状況によっては、メモリのチューニングが必要です。以下のような適切なパラメータを使用して、消費領域にわたってメモリのバランスを取ることが目的です。

  • work_mem 値。

  • shared_buffers を割り引いた後の残りのメモリ

  • max_connectionsで制限されるオープンおよび使用中の最大接続数

これらのメモリのチューニングの詳細については、PostgreSQL ドキュメントの「リソース消費」を参照してください。

作業メモリ領域のサイズを拡大する

状況によっては、セッションで使用されるメモリを増やすことが唯一の選択肢となることもあります。クエリが正しく記述され、ジョイントに正しいキーを使用している場合は、work_mem値の増加を検討してください。

クエリが生成するテンポラリファイルの数を調べるには、log_temp_files を 0 に設定します。work_mem 値をログで識別される最大値まで上げると、クエリでテンポラリファイルが生成されるのを防ぎます。ただし、work_memは各接続またはパラレルワーカーにプランノードあたりの最大値を設定します。データベースに 5,000 の接続があり、それぞれが 256 MiB のメモリを使用する場合、エンジンは 1.2 TiB の RAM を必要とします。そのため、インスタンスのメモリが不足する可能性があります。

共有バッファプールに十分なメモリを予約する

データベースでは、作業用メモリ領域だけでなく、共有バッファプールなどのメモリ領域が使用されます。work_memを増加する前に、これらの追加メモリ領域の要件を考慮してください。

例えば、RDS for PostgreSQL インスタンスクラスが db.r5.2xlarge であると仮定します。このクラスには 64 GiB のメモリがあります。デフォルトでは、メモリの 25% が共有バッファプール用に予約されています。共有メモリ領域に割り当てられた量を引くと、16,384 MB が残ります。OS やエンジンもメモリを必要とするため、残りのメモリを作業メモリ領域にのみ割り当てないでください。

work_memに割り当て可能なメモリはインスタンスクラスによって異なります。より大きなインスタンスクラスを使用すると、より多くのメモリが使用できます。ただし、前の例では 16 GiB 以上は使用できません。そうでなければ、メモリ不足に陥ったときにインスタンスが使用できなくなります。インスタンスを利用できない状態から回復するには、RDS for PostgreSQL オートメーションサービスが自動的に再起動します。

接続の数を管理する

データベースインスタンスでの同時接続が 5,000 とします。各接続では、work_memのうち少なくとも 4 MiB を使用します。接続に必要なメモリ消費量が多いと、パフォーマンスが低下する可能性があります。これに対して、次のオプションがあります。

  • より大きなインスタンスクラスにアップグレードします。

  • 接続プロキシまたはプーラーを使用することで、データベースの同時接続の数を減らします。

プロキシの場合は、アプリケーションに基づいて Amazon RDS プロキシ、pgBouncer、または接続プーラーを検討してください。この解決策は CPU ロードを軽減します。また、すべての接続が作業メモリ領域を必要とする場合のリスクも軽減します。データベース接続数が少ない場合は、work_memの値を増やすことができます。このように、IO:BufFileReadそしてIO:BufFileWrite待機イベントの発生を減らします。また、作業メモリ領域で待っているクエリが大幅に高速化します。