Gestione dei file temporanei con PostgreSQL

In PostgreSQL, una query complessa può eseguire diverse operazioni di ordinamento o hash contemporaneamente, con ogni operazione che 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.

Consigliamo di utilizzare i cluster di istanze Aurora Optimized per carichi di lavoro che coinvolgono più query simultanee che aumentano l'utilizzo di file temporanei. Questi cluster di utilizzano storage locale a livello di blocco basato su unità a stato solido (SSDNVMe) basate su unità a stato solido (SSD) non volatile Memory Express () per collocare i file temporanei. Per ulteriori informazioni, consulta Prestazioni delle query migliorate per Aurora PostgreSQL con Letture ottimizzate per Aurora.

È 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 Performance Insights, consulta. Per analizzare il parametro utilizzando il pannello di controllo di Performance Insights

Per un esempio di visualizzazione dell'utilizzo di file temporanei con Performance Insights, vedere Visualizzazione dell'utilizzo temporaneo dei file con Performance Insights

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Specificare il disco RAM per stats_temp_directory

Visualizzazione dell'utilizzo temporaneo dei file con Performance Insights