# IO:BufFileRead および IO:BufFileWrite
`IO:BufFileRead` と `IO:BufFileWrite` イベントは、Aurora PostgreSQL がテンポラリファイルを作成するときに発生します。作業メモリパラメータが現在の定義より多くのメモリを必要とするオペレーションは、テンポラリデータを永続的ストレージに書き込みます。この操作は「*spilling to disk (ディスクへの流出)*」と呼ばれることがあります。一時ファイルとその使用方法の詳細については、「[PostgreSQL による一時ファイルの管理](PostgreSQL.ManagingTempFiles.md)」を参照してください。
**Topics**
+ [サポート対象エンジンバージョン](#apg-waits.iobuffile.context.supported)
+ [Context](#apg-waits.iobuffile.context)
+ [待機時間が増加する原因の可能性](#apg-waits.iobuffile.causes)
+ [アクション](#apg-waits.iobuffile.actions)
## サポート対象エンジンバージョン
この待機イベント情報は、Aurora PostgreSQL のすべてのバージョンでサポートされています。
## Context
`IO:BufFileRead`そして`IO:BufFileWrite`は、作業メモリ領域とメンテナンス作業用メモリ領域に関連します。これらのローカルメモリ領域の詳細については、「[ワークメモリ領域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem)」と「[メンテナンス作業用メモリ領域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.maintenance_work_mem)」を参照してください。
デフォルト値は `work_mem` 4 MB です。一つのセッションがパラレルにオペレーションを実行する場合、パラレル処理を行う各ワーカーは 4 MB のメモリを使用します。このため、`work_mem`を慎重に設定してください。値を大きくしすぎると、多くのセッションを実行しているデータベースがメモリを過剰に消費することがあります。値を低く設定しすぎると、Aurora PostgreSQL はローカルストレージにテンポラリファイルを作成します。これらのテンポラリファイルのためのディスク I/O により、パフォーマンスが低下する可能性があります。
次のようなイベントが発生する場合、データベースがテンポラリファイルを生成している可能性があります。
1. 可用性の急激な低下
1. 空き領域の高速リカバリ
また、「チェーンソー」のパターンが表示されるかもしれません。このパターンは、データベースが小さなファイルを常に作成していることを示す可能性があります。
## 待機時間が増加する原因の可能性
一般に、これらの待機イベントは、`work_mem`または`maintenance_work_mem`パラメータが割り当てられるよりも多くのメモリを消費するオペレーションによって発生します。補うために、オペレーションはテンポラリファイルに書き込みます。`IO:BufFileRead`そして`IO:BufFileWrite`イベントの一般的な原因には、次のようなものがあります。
**作業用メモリ領域に存在するメモリより多くのメモリを必要とするクエリ**
次の特性を持つクエリは、作業メモリ領域を使用します。
+ ハッシュ結合
+ `ORDER BY` 句
+ `GROUP BY` 句
+ `DISTINCT`
+ ウィンドウ関数
+ `CREATE TABLE AS SELECT`
+ REFRESH MATERIALIZED VIEW
**メンテナンス作業メモリ領域に存在するメモリより多くのメモリを必要とするステートメント**
次のステートメントは、メンテナンス作業メモリ領域を使用します。
+ `CREATE INDEX`
+ `CLUSTER`
## アクション
待機イベントの原因に応じたさまざまなアクションをお勧めします。
**Topics**
+ [問題の特定](#apg-waits.iobuffile.actions.problem)
+ [ジョイントクエリを検証する](#apg-waits.iobuffile.actions.joins)
+ [ORDER BY クエリと GROUP BY クエリを検証する](#apg-waits.iobuffile.actions.order-by)
+ [DISTINCT オペレーションの使用を避ける](#apg-waits.iobuffile.actions.distinct)
+ [GROUP BY 関数の代わりにウィンドウ関数の使用を検討してください。](#apg-waits.iobuffile.actions.window)
+ [マテリアライズドビューと CTAS ステートメントの調査](#apg-waits.iobuffile.actions.mv-refresh)
+ [インデックスの作成時に pg\$1repack を使用する](#apg-waits.iobuffile.actions.pg_repack)
+ [テーブルをクラスター化するときに maintenance\$1work\$1mem を増やす](#apg-waits.iobuffile.actions.cluster)
+ [IO:BufFileRead および IO:BufFileWrite を防ぐためにメモリを調整します](#apg-waits.iobuffile.actions.tuning-memory)
### 問題の特定
一時ファイルの使用状況は、Performance Insights で直接表示できます。詳細については、「[Performance Insights を使用した一時ファイルの使用状況の確認](PostgreSQL.ManagingTempFiles.Example.md)」を参照してください。Performance Insights を無効にすると、`IO:BufFileRead` および `IO:BufFileWrite` オペレーションが増加することがあります。トラブルシューティングを行うには、次を実行します。
1. `FreeLocalStorage`Amazon CloudWatch のメトリクスを検証します。
1. ギザギザのトゲが連なるチェーンソーパターンを探してみてください。
チェーンソーパターンは、多くの場合はテンポラリファイルに関連付けられ、ストレージの迅速な消費と解放を示します。このパターンが見られたら、「Performance Insights」 をオンにします。Performance Insights を使用すると、待機イベントがいつ発生し、どのクエリに関連するかを特定できます。解決策は、イベントを引き起こす特定のクエリによって異なります。
または、パラメータ`log_temp_files`を設定します。このパラメータは、しきい値 KB を超えるテンポラリファイルを生成するすべてのクエリをログに記録します。値が `0` の場合、Aurora PostgreSQL すべてのテンポラリファイルをログに記録します。値が `1024` の場合、Aurora PostgreSQL は 1 MB を超えるテンポラリファイルを生成するすべてのクエリをログに記録します。`log_temp_files`についての詳細は、PostgreSQL ドキュメントの[Error reporting and logging](https://www.postgresql.org/docs/10/runtime-config-logging.html) を参照してください。
### ジョイントクエリを検証する
アプリケーションでは、おそらくジョイントを使用しています。例えば、次のクエリは 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`オペレーションを使用して重複した行を削除することは避けてください。クエリが返す行が不要かつ重複していればいるほど、V`DISTINCT`オペレーションのコストは高くなります。可能であれば、異なるテーブルに対して同じフィルターを使用している場合でも、`WHERE`節でフィルターを追加してください。クエリをフィルタリングして正しく結合すると、パフォーマンスが向上し、リソースの使用量が削減されます。また、誤ったレポートや結果を防ぐことができます。
`DISTINCT`を同じテーブルの複数の行に使用する必要がある場合、複合インデックスの作成を検討してください。インデックスに複数の列をグループ化すると、個別の行を評価する時間を短縮できます。また、Amazon Aurora 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);
```
デフォルトでは、Aurora PostgreSQL 実行プランナーは類似したノードを統合し、オペレーションが重複しないようにします。ただし、ウィンドウブロックに明示的な宣言を使用すると、クエリをより簡単に維持できます。また、重複を防止するとパフォーマンスの向上につながることがあります。
### マテリアライズドビューと CTAS ステートメントの調査
マテリアライズドビューがリフレッシュされると、クエリが実行されます。このクエリには、`GROUP BY`、`ORDER BY`、`DISTINCT`のような操作を含めることができます。リフレッシュ中に、大量のテンポラリファイルや待機イベント`IO:BufFileWrite`および`IO:BufFileRead`が発生することがあります。同様に、`SELECT`に基づいてテーブルを作成すると、`CREATE TABLE`ステートメントはクエリを実行します。必要なテンポラリファイルを減らすには、クエリを最適化します。
### インデックスの作成時に pg\$1repack を使用する
インデックスを作成すると、エンジンは結果セットを順序付けます。テーブルのサイズが大きくなり、インデックスで指定された列の値が多様化していくと、テンポラリファイルはより多くの領域を必要とします。ほとんどの場合、メンテナンス作業のメモリ領域を変更しなければ、大きなテーブルのテンポラリファイルの作成を防ぐことはできません。詳細については、「[メンテナンス作業用メモリ領域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.maintenance_work_mem)」を参照してください。
大きなインデックスを再作成するときに考えられる回避策としては、pg\$1repack ツールを使用することが挙げられます。詳細については、「pg\$1repack のドキュメント」で「[最小限のロックで PostgreSQL データベース内のテーブルを再編成する](https://reorg.github.io/pg_repack/)」を参照してください。
### テーブルをクラスター化するときに maintenance\$1work\$1mem を増やす
`CLUSTER`コマンドは、*index\$1name*で指定した既存のインデックスに基づいて、*table\$1name*で指定したテーブルをクラスター化します。Aurora PostgreSQL は、指定されたインデックスの順序に一致するようにテーブルを物理的に再作成します。
磁気ストレージが普及していたころは、ストレージのスループットが限られていたため、クラスター化が一般的でした。今では、SSD ベースのストレージが一般的となり、クラスター化はあまり一般的ではなくなっています。ただし、テーブルをクラスター化すると、テーブルのサイズ、インデックス、クエリなどによってパフォーマンスが多少向上することがあります。
`CLUSTER`コマンドを実行して、待機イベント`IO:BufFileWrite`、`IO:BufFileRead`をモニタリングし、`maintenance_work_mem`をチューニングします。メモリサイズをかなり大きくしてください。高い値は、エンジンがクラスター化オペレーションのためにより多くのメモリを使用できることを意味します。
### IO:BufFileRead および IO:BufFileWrite を防ぐためにメモリを調整します
状況によっては、メモリのチューニングが必要です。次の要件のバランスをとることが目標です。
+ `work_mem`値 (「[ワークメモリ領域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem)」を参照)
+ 割り引いた後の残りのメモリ `shared_buffers` 値 (「[バッファプール](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool)」を参照)
+ `max_connections`で制限されるオープンおよび使用中の最大接続数
#### 作業メモリ領域のサイズを拡大する
状況によっては、セッションで使用されるメモリを増やすことが唯一の選択肢となることもあります。クエリが正しく記述され、ジョイントに正しいキーを使用している場合は、`work_mem`値の増加を検討してください。詳細については、「[ワークメモリ領域](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem)」を参照してください。
クエリが生成するテンポラリファイルの数を調べるには、`log_temp_files` を `0` に設定します。`work_mem` 値をログで識別される最大値まで上げると、クエリでテンポラリファイルが生成されるのを防ぎます。ただし、`work_mem`は各接続またはパラレルワーカーにプランノードあたりの最大値を設定します。データベースに 5,000 の接続があり、それぞれが 256 MiB のメモリを使用する場合、エンジンは 1.2 TiB の RAM を必要とします。そのため、インスタンスのメモリが不足する可能性があります。
#### 共有バッファプールに十分なメモリを予約する
データベースでは、作業用メモリ領域だけでなく、共有バッファプールなどのメモリ領域が使用されます。`work_mem`を増加する前に、これらの追加メモリ領域の要件を考慮してください。バッファープールの詳細については、「[バッファプール](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool)」を参照してください。
例えば、Aurora PostgreSQL インスタンスクラスが db.r5.2xlarge であると仮定します。このクラスには 64 GiB のメモリがあります。デフォルトでは、メモリの 75% が共有バッファプール用に予約されています。共有メモリ領域に割り当てられた量を引くと、16,384 MB が残ります。OS やエンジンもメモリを必要とするため、残りのメモリを作業メモリ領域にのみ割り当てないでください。
`work_mem`に割り当て可能なメモリはインスタンスクラスによって異なります。より大きなインスタンスクラスを使用すると、より多くのメモリが使用できます。ただし、前の例では 16 GiB 以上は使用できません。そうでなければ、メモリ不足に陥ったときにインスタンスが使用できなくなります。インスタンスを利用できない状態から回復するには、Aurora PostgreSQL オートメーションサービスが自動的に再起動します。
#### 接続の数を管理する
データベースインスタンスでの同時接続が 5,000 とします。各接続では、`work_mem`のうち少なくとも 4 MiB を使用します。接続に必要なメモリ消費量が多いと、パフォーマンスが低下する可能性があります。これに対して、次のオプションがあります。
+ より大きなインスタンスクラスにアップグレードします。
+ 接続プロキシまたはプーラーを使用することで、データベースの同時接続の数を減らします。
プロキシの場合は、アプリケーションに基づいて Amazon RDS プロキシ、pgBouncer、または接続プーラーを検討してください。この解決策は CPU ロードを軽減します。また、すべての接続が作業メモリ領域を必要とする場合のリスクも軽減します。データベース接続数が少ない場合は、`work_mem`の値を増やすことができます。このように、`IO:BufFileRead`そして`IO:BufFileWrite`待機イベントの発生を減らします。また、作業メモリ領域で待っているクエリが大幅に高速化します。