Mengelola file sementara dengan PostgreSQL

temp_file_limit – Parameter ini membatalkan kueri apa pun yang melebihi ukuran temp_files dalam KB. Batas ini mencegah kueri apa pun berjalan tanpa henti dan menghabiskan ruang disk dengan file sementara. Anda dapat memperkirakan nilai menggunakan hasil dari parameter log_temp_files. Sebagai praktik terbaik, periksa perilaku beban kerja dan tetapkan batas sesuai dengan estimasi. Contoh berikut menunjukkan bagaimana kueri dibatalkan saat melampaui batas.

postgres=>select * from pgbench_accounts, pg_class, big_table;
            

ERROR: temporary file size exceeds temp_file_limit (64kB)

log_temp_files – Parameter ini mengirimkan pesan ke postgresql.log ketika file sementara dari sebuah sesi dihapus. Parameter ini menghasilkan log setelah kueri berhasil diselesaikan. Oleh karena itu, ini mungkin tidak membantu dalam memecahkan masalah kueri yang aktif dan berjalan lama.

Contoh berikut menunjukkan bahwa ketika kueri berhasil diselesaikan, entri dicatat dalam file postgresql.log, sedangkan file sementara dibersihkan.

                    
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 – Fungsi yang tersedia dari RDS untuk PostgreSQL 13 dan versi yang lebih baru ini memberikan visibilitas terhadap penggunaan file sementara saat ini. Kueri yang sudah selesai tidak muncul di hasil fungsi. Dalam contoh berikut, Anda dapat melihat hasil dari fungsi ini.

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)

Nama file mencakup ID pemrosesan (PID) dari sesi yang menghasilkan file sementara. Kueri yang lebih maju, seperti pada contoh berikut, melakukan penjumlahan file sementara untuk setiap 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 – Jika Anda mengaktifkan parameter pg_stat_statements, Anda dapat melihat rata-rata penggunaan file sementara per panggilan. Anda dapat mengidentifikasi query_id dari kueri dan menggunakannya untuk memeriksa penggunaan file sementara seperti yang ditunjukkan pada contoh berikut.

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 – Di dasbor Wawasan Performa, Anda dapat melihat penggunaan file sementara dengan mengaktifkan metrik temp_bytes dan temp_files. Kemudian, Anda dapat melihat rata-rata kedua metrik ini dan melihat sejauh mana kesesuaiannya dengan beban kerja kueri. Tampilan dalam Wawasan Performa tidak secara khusus menampilkan kueri yang menghasilkan file sementara. Namun, jika Anda menggabungkan Wawasan Performa dengan kueri yang ditampilkan untuk pg_ls_tmpdir, Anda dapat memecahkan masalah, menganalisis, dan menentukan perubahan dalam beban kerja kueri.

Untuk informasi selengkapnya tentang cara menganalisis metrik dan kueri dengan Performance Insights, lihat. Menganalisis metrik dengan dasbor Wawasan Performa

Untuk contoh melihat penggunaan file sementara dengan Performance Insights, lihat Melihat penggunaan file sementara dengan Performance Insights