PostgreSQL による一時ファイルの管理

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 を使用した一時ファイルの使用状況の確認」を参照してください。