Gestione dei file temporanei con PostgreSQL

In PostgreSQL, una query che esegue operazioni di ordinamento e hash utilizza la memoria dell'istanza per archiviare i risultati fino al valore specificato nel parametro work_mem. Quando la memoria dell'istanza non è sufficiente, vengono creati file temporanei per archiviare i risultati. Questi vengono scritti su disco per completare l'esecuzione della query. Successivamente, questi file vengono rimossi automaticamente al completamento della query. In In Aurora PostgreSQL, questi file condividono l'archiviazione locale con altri file di log. Puoi monitorare lo spazio di archiviazione locale del cluster Aurora PostgreSQL DB osservando la metrica Amazon per. CloudWatch FreeLocalStorage Per ulteriori informazioni, consulta Risoluzione dei problemi relativi all'archiviazione locale.

È possibile utilizzare i seguenti parametri e funzioni per gestire i file temporanei nell'istanza.

• temp_file_limit: questo parametro annulla qualsiasi query che superi la dimensione definita in KB dal parametro temp_files. Questo limite impedisce a qualsiasi query di essere eseguita all'infinito e di consumare spazio su disco con file temporanei. È possibile stimare il valore utilizzando i risultati del parametro log_temp_files. È consigliabile esaminare il comportamento del carico di lavoro e impostare il limite in base alla stima. Gli esempi seguenti mostrano come viene annullata una query quando supera il limite.

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

  
  ERROR: temporary file size exceeds temp_file_limit (64kB)
  

• log_temp_files: questo parametro invia messaggi a postgresql.log quando i file temporanei di una sessione vengono rimossi. Questo parametro produce log dopo che una query è stata completata correttamente. Pertanto, potrebbe non essere utile nella risoluzione dei problemi delle query attive e con tempi di esecuzione lunghi.

  L'esempio seguente mostra che quando la query viene completata correttamente, le voci vengono registrate nel file postgresql.log mentre i file temporanei vengono eliminati.

                      
  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: questa funzione disponibile in RDS per PostgreSQL 13 e versioni successive fornisce visibilità sull'attuale utilizzo dei file temporanei. La query completata non viene visualizzata nei risultati della funzione. Nell'esempio seguente, è possibile visualizzare i risultati di questa funzione.

  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)

  Il nome del file include l'ID di elaborazione (PID) della sessione che ha generato il file temporaneo. Una query più avanzata, come nell'esempio seguente, esegue la somma dei file temporanei per ogni 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: se attivi il parametro pg_stat_statements, puoi visualizzare l'utilizzo medio dei file temporanei per chiamata. È possibile identificare il valore query_id della query e utilizzarlo per esaminare l'utilizzo dei file temporanei, come illustrato nell'esempio seguente.

  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: nel pannello di controllo di Approfondimenti sulle prestazioni, puoi visualizzare l'utilizzo dei file temporanei attivando le metriche temp_bytes e temp_files. Puoi quindi vedere la media di entrambe queste metriche e verificare se corrispondono al carico di lavoro delle query. La visualizzazione all'interno di Approfondimenti sulle prestazioni non evidenzia in modo specifico le query che generano file temporanei. Tuttavia, combinando le informazioni di Approfondimenti sulle prestazioni con la query mostrata per il parametro pg_ls_tmpdir, è possibile definire, analizzare e risolvere eventuali problemi a livello di modifiche del carico di lavoro delle query.

  Per ulteriori informazioni su come analizzare metriche e query con Approfondimenti sulle prestazioni, consulta Per analizzare il parametro utilizzando il pannello di controllo di Performance Insights

  Per visualizzare l'utilizzo dei file temporanei con Approfondimenti sulle prestazioni

  1. Nel pannello di controllo di Approfondimenti sulle prestazioni, scegli Gestisci parametri.

  2. Seleziona Metriche del database e quindi seleziona le metriche temp_bytes e temp_files come illustrato nell'immagine seguente.

     

  3. Nella scheda SQL principale, scegli l'icona Preferenze.

  4. Nella finestra Preferenze, attiva le seguenti statistiche per visualizzarle nella scheda SQL principale e scegli Continua.

     • Scritture temporanee al secondo

     • Letture temporanee al secondo

     • Scritture temporanee in blocco a chiamata

     • Letture temporanee in blocco a chiamata

  5. Il file temporaneo viene suddiviso quando viene combinato con la query visualizzata per pg_ls_tmpdir, come illustrato nell'esempio seguente.

     

Gli eventi IO:BufFileRead e IO:BufFileWrite si verificano quando le query principali del carico di lavoro creano spesso file temporanei. Puoi utilizzare Approfondimenti sulle prestazioni per identificare le query di livello superiore in attesa di IO:BufFileRead e IO:BufFileWrite esaminando la metrica Sessioni attive medie (AAS) nelle sezioni Caricamento del database e SQL principale.

Per ulteriori informazioni su come analizzare metriche principali ed eventi di attesa con Approfondimenti sulle prestazioni, consulta Panoramica della scheda Prime istruzioni SQL. Devi individuare e ottimizzare le query che causano un aumento dell'utilizzo dei file temporanei e dei relativi eventi di attesa. Per ulteriori informazioni su questi eventi di attesa e sulla correzione, vedere IO: BufFileRead and IO: BufFileWrite.

Nota

Il parametro work_mem controlla quando l'operazione di ordinamento esaurisce la memoria; i risultati vengono scritti in file temporanei. Si consiglia di non modificare l'impostazione di questo parametro specificando un valore superiore al valore predefinito perché ciò causerebbe un maggiore utilizzo della memoria da parte di ciascuna sessione del database. Inoltre, una sessione che esegue unioni e ordinamenti complessi può eseguire operazioni parallele in cui ogni operazione consuma memoria.

Come best practice, in presenza di un report di grandi dimensioni con più unioni e ordinamenti, imposta questo parametro a livello di sessione utilizzando il comando SET work_mem. La modifica verrà quindi applicata solo alla sessione corrente e non comporterà la modifica del valore a livello globale.