PostgreSQL による一時ファイルの管理
PostgreSQL では、ソート操作とハッシュ操作を実行するクエリは、インスタンスメモリを使用して、work_mem
FreeStorageSpace
メトリクスを常にモニタリングして、DB インスタンスのストレージに十分な空き容量があることを確認できます。詳細については、「FreeStorageSpace
複数のクエリが同時に実行され、一時ファイルの使用量が増えるワークロードには、Amazon RDS Optimized Read インスタンスを使用することをお勧めします。これらのインスタンスがローカルの不揮発性メモリエクスプレス (NVMe) ベースのソリッドステートドライブ (SSD) ブロックレベルストレージを使用することで、臨時ファイルを配置します。詳細については、「Amazon RDS Optimized Reads」を参照してください。
以下のパラメータと関数を使用して、インスタンスの一時ファイルを管理することができます。
-
temp_file_limit
— このパラメータは、temp_files のサイズ (KB 単位) を超えるクエリをすべてキャンセルします。この制限により、クエリが延々と実行され、一時ファイルでディスクスペースが消費されるのを防ぐことができます。 log_temp_files
パラメータの結果を使用して値を推定できます。ベストプラクティスとして、ワークロードの動作を調べ、推定値に従って制限を設定してください。次の例は、クエリが制限を超えた場合にキャンセルされる様子を示しています。postgres=> select * from pgbench_accounts, pg_class, big_table;
ERROR: temporary file size exceeds temp_file_limit (64kB)
log_temp_files
— このパラメータは、セッションの一時ファイルが削除されたときに postgresql.log にメッセージを送信します。このパラメータは、クエリが正常に完了した後にログを生成します。そのため、アクティブで長時間実行されるクエリのトラブルシューティングには役立たない可能性があります。 次の例は、クエリが正常に完了すると、エントリが postgresql.log ファイルに記録され、一時ファイルがクリーンアップされることを示しています。
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10; 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
pg_ls_tmpdir
— この関数は RDS for PostgreSQL 13 以降で使用でき、現在の一時ファイルの使用状況を可視化できます。完了したクエリは、関数の結果には表示されません。次の例では、この関数の結果を表示できます。 postgres=> select * from pg_ls_tmpdir();
name | size | modification -----------------+------------+------------------------ pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00 pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00 pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00 pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00 pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00 (7 rows)
postgres=> select query from pg_stat_activity where pid = 8355;
query ---------------------------------------------------------------------------------------- select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid (1 row)
ファイル名には、一時ファイルを生成したセッションの処理 ID (PID) が含まれます。次の例のような、より高度なクエリでは、各 PID の一時ファイルの合計が実行されます。
postgres=> select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;
pid | count | sum ------+------------------- 8355 | 2 | 2144501760 8351 | 2 | 2090770432 8327 | 1 | 1072250880 8328 | 2 | 2144501760 (4 rows)
-
pg_stat_statements
— pg_stat_statements パラメータを有効にすると、呼び出しごとの一時ファイルの平均使用量を表示できます。次の例に示すように、クエリの query_id を特定し、それを使用して一時ファイルの使用状況を調べることができます。postgres=> select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';
queryid ---------------------- -7170349228837045701 (1 row)
postgres=> select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;
queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call ----------------------+---------------------------+-------+-------------------------+---------------------------- -7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678 (1 row)
-
Performance Insights
— Performance Insights ダッシュボードで、temp_bytes と temp_files のメトリクスをオンにすると、一時ファイルの使用状況を確認できます。次に、これら両方のメトリクスの平均と、それらがクエリワークロードにどのように対応しているかを確認できます。Performance Insights 内のビューには、一時ファイルを生成しているクエリが具体的に表示されません。ただし、Performance Insights とpg_ls_tmpdir
に示されるクエリを組み合わせると、クエリワークロードの変化をトラブルシューティング、分析、判断できます。Performance Insights を使用してメトリクスとクエリを分析する方法については、「Performance Insights ダッシュボードを使用してメトリクスを分析する」を参照してください
Performance Insights を使用して一時ファイルの使用状況を確認するには
-
Performance Insights ダッシュボードで、[メトリクスを管理] を選択します。
-
次の画像に示すように、[データベースメトリクス] を選択して、[temp_bytes] と [temp_files] を選択します。
-
[トップ SQL] タブで、[設定] アイコンを選択します。
-
[設定] ウィンドウで、[トップ SQL] タブに以下の統計が表示されるようにして、[続行] を選択します。
-
Temp writes/sec
-
Temp reads/sec
-
Tmp blk write/call
-
Tmp blk read/call
-
-
次の例に示すように、
pg_ls_tmpdir
で示したクエリと組み合わせると、この一時ファイルが抜き出されます。
-
IO:BufFileRead
と IO:BufFileWrite
イベントは、ワークロードの上位クエリが一時ファイルを頻繁に作成するときに発生します。Performance Insights を使用することで、「データベース負荷」と「上位 SQL」セクションの「平均アクティブセッション (AAS)」を参照して、IO:BufFileRead
と IO:BufFileWrite
を待機中の上位クエリの特定が可能になります。
![グラフの IO:BufFileRead および IO:BufFileWrite。](images/perfinsights_IOBufFile.png)
Performance Insights を使用して上位クエリを分析して、待機イベント別にロードする方法については、「[トップ SQL] タブの概要」を参照してください テンポラリファイルの使用量と関連する待機イベントの増加の原因となるクエリを特定し、調整する必要があります。これらの待機イベントと修復方法の詳細については、「IO:BufFileRead および IO:BufFileWrite」「」を参照してください。
注記
work_mem
ベストプラクティスとして、複数の結合とソートを含む大規模なレポートがある場合は、SET work_mem
コマンドを使用してこのパラメータをセッションレベルで設定します。そうすれば、変更は現在のセッションにのみ適用され、値がグローバルに変更されることはありません。