Gestion des fichiers temporaires avec Postgre SQL

temp_file_limit : ce paramètre annule toute requête dépassant la taille des fichiers temp_files en Ko. Cette limite empêche toute requête de s'exécuter indéfiniment et de consommer de l'espace disque avec des fichiers temporaires. Vous pouvez estimer la valeur à l'aide des résultats du paramètre log_temp_files. Nous vous recommandons d'examiner le comportement de la charge de travail et de définir la limite en fonction de l'estimation. L'exemple suivant présente la manière dont une requête est annulée lorsqu'elle dépasse la limite.

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

ERROR: temporary file size exceeds temp_file_limit (64kB)

log_temp_files : ce paramètre envoie des messages au fichier postgresql.log lorsque les fichiers temporaires d'une session sont supprimés. Ce paramètre produit des journaux lorsqu'une requête est terminée avec succès. Par conséquent, cela peut ne pas aider à résoudre les requêtes actives et de longue durée.

L'exemple suivant montre que lorsque la requête aboutit, les entrées sont journalisées dans le fichier postgresql.log pendant que les fichiers temporaires sont nettoyés.

                    
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— Cette fonction disponible à partir de RDS Postgre SQL 13 et versions ultérieures fournit une visibilité sur l'utilisation actuelle des fichiers temporaires. La requête terminée n'apparaît pas dans les résultats de la fonction. Dans l'exemple suivant, vous pouvez visualiser les résultats de cette fonction.

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)

Le nom du fichier inclut l'ID de traitement (PID) de la session qui a généré le fichier temporaire. Une requête plus avancée, comme dans l'exemple suivant, effectue la somme des fichiers temporaires pour chacun d'entre euxPID.

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 : si vous activez le paramètre pg_stat_statements, vous pouvez consulter l'utilisation moyenne des fichiers temporaires par appel. Vous pouvez identifier le query_id de la requête et l'utiliser pour examiner l'utilisation des fichiers temporaires, comme indiqué dans l'exemple suivant.

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 : dans le tableau de bord Performance Insights, vous pouvez consulter l'utilisation des fichiers temporaires en activant les métriques temp_bytes et temp_files. Vous pouvez ensuite voir la moyenne de ces deux métriques et voir comment elles correspondent à la charge de travail des requêtes. La vue de Performance Insights n'affiche pas spécifiquement les requêtes qui génèrent les fichiers temporaires. Toutefois, lorsque vous associez Performance Insights à la requête indiquée pour pg_ls_tmpdir, vous pouvez dépanner, analyser et déterminer les modifications apportées à la charge de travail de vos requêtes.

Pour plus d'informations sur la façon d'analyser les métriques et les requêtes avec Performance Insights, consultezAnalyse des métriques à l'aide du tableau de bord de Performance Insights.

Pour un exemple d'affichage de l'utilisation de fichiers temporaires avec Performance Insights, voir Visualisation de l'utilisation des fichiers temporaires avec Performance Insights