Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

IO:buffileRead e IO:buffileWrite

Gli eventi IO:BufFileRead e IO:BufFileWrite si verificano quando RDS per PostgreSQL crea file temporanei. Quando le operazioni richiedono più memoria rispetto ai parametri della memoria di lavoro attualmente definiti, scrivono dati temporanei sullo storage persistente. Questa operazione è talvolta chiamata «versamento su disco».

Versioni del motore supportate

Queste informazioni relative all'evento di attesa sono supportate per tutte le versioni di RDS per PostgreSQL.

Context

IO:BufFileRead e IO:BufFileWrite riguardano l'area di memoria di lavoro e l'area di memoria di lavoro di manutenzione. Per ulteriori informazioni su queste aree di memoria locale, consulta Resource Consumption (Consumo di risorse) nella documentazione di PostgreSQL.

Il valore predefinito per work_mem è 4 MB. Se una sessione esegue operazioni in parallelo, ogni lavoratore che gestisce il parallelismo utilizza 4 MB di memoria. Per questo motivo, imposta work_mem con attenzione. Se si aumenta troppo il valore, un database che esegue molte sessioni potrebbe consumare troppa memoria. Se si imposta il valore troppo basso, RDS per PostgreSQL crea file temporanei nella memoria locale. L'I/O del disco per questi file temporanei può ridurre le prestazioni.

Se si osserva la seguente sequenza di eventi, il database potrebbe generare file temporanei:

Potresti vedere anche un motivo a «motosega». Questo modello può indicare che il database sta creando costantemente file di piccole dimensioni.

Probabili cause di aumento delle attese

In generale, questi eventi di attesa sono causati da operazioni che consumano più memoria rispetto a quella allocata dai parametri work_mem o maintenance_work_mem. Per compensare, le operazioni scrivono su file temporanei. Cause comuni degli eventi IO:BufFileRead e IO:BufFileWrite includono quanto segue:

Operazioni

Consigliamo azioni diverse a seconda delle cause dell'evento di attesa.

Identificare il problema

Supponiamo una situazione in cui Performance Insights non è attivato e sospetti che IO:BufFileRead e IO:BufFileWrite si verificano più frequentemente del normale. Per identificare l'origine del problema, è possibile impostare il parametro log_temp_files per registrare tutte le query che generano file temporanei superiori alla soglia di KB specificata. Per impostazione predefinita, log_temp_files è impostato su -1, il che disattiva la funzionalità di registrazione. Se imposti questo parametro su 0, RDS for PostgreSQL registra tutti i file temporanei. Se lo imposti su 1024, RDS per PostgreSQL registra tutte le query che producono file temporanei di dimensioni superiori a 1 MB. Per ulteriori informazioni su log_temp_files, consulta Creazione di log e report di errore nella documentazione di PostgreSQL.

Esamina le tue query di join

È probabile che la tua query utilizzi i join. Ad esempio, la seguente query unisce quattro tabelle.

SELECT * 
       FROM "order" 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Una possibile causa di picchi nell'utilizzo temporaneo dei file è un problema nella query stessa. Ad esempio, una clausola interrotta potrebbe non filtrare correttamente i join. Considera il secondo inner join nell'esempio seguente.

SELECT * 
       FROM "order"
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

La query precedente unisce erroneamente customer.id a customer.id, generando un prodotto cartesiano tra ogni cliente e ogni ordine. Questo tipo di join accidentale genera file temporanei di grandi dimensioni. A seconda delle dimensioni delle tabelle, una query cartesiana può anche riempire lo spazio di archiviazione. La domanda potrebbe avere join cartesiani quando vengono soddisfatte le seguenti condizioni:

Per verificare se le tabelle vengono unite utilizzando le chiavi appropriate, ispezionare le direttive di mappatura relazionale di query e oggetti. Tieni presente che alcune query della tua applicazione non vengono sempre chiamate e alcune query vengono generate dinamicamente.

Esamina le query ORDER BY e GROUP BY

In alcuni casi, una clausola ORDER BY può comportare file temporanei eccessivi. Considera le linee guida seguenti:

Una query che utilizza una clausola GROUP BY può richiedere anche file temporanei. Le query GROUP BY riepilogano i valori utilizzando funzioni come le seguenti:

Per sintonizzare le query GROUP BY, segui i consigli per le query ORDER BY.

Evitare di utilizzare l'operazione DISTINCT

Se possibile, evitare di utilizzare l’operazione DISTINCT per rimuovere righe duplicate. Più righe non necessarie e duplicate restituite dalla tua query, più costosa diventa l’operazione DISTINCT. Se possibile, aggiungi filtri nella clausola WHERE anche se utilizzi gli stessi filtri per tabelle diverse. Filtrare la query e unirsi correttamente migliora le prestazioni e riduce l'utilizzo delle risorse. Previene inoltre report e risultati errati.

Se devi usare DISTINCT per più righe di una stessa tabella, prendi in considerazione la possibilità di creare un indice composito. Il raggruppamento di più colonne in un indice può migliorare il tempo necessario per valutare righe distinte. Inoltre, se utilizzi RDS per PostgreSQL versione 10 o successiva, puoi correlare le statistiche tra più colonne utilizzando il comando CREATE STATISTICS.

Considera l'utilizzo di funzioni finestra anziché le funzioni GROUP BY

Utilizzando GROUP BY si modifica il set di risultati e quindi recupera il risultato aggregato. Utilizzando le funzioni della finestra, si aggregano i dati senza modificare il set di risultati. Una funzione finestra utilizza la clausola OVER per eseguire calcoli tra i set definiti dalla query, correlando una riga con un'altra. È possibile utilizzare tutte le funzioni GROUP BY nelle funzioni di finestra, ma anche funzioni come le seguenti:

Per ridurre al minimo il numero di file temporanei generati da una funzione di finestra, rimuovere le duplicazioni per lo stesso set di risultati quando sono necessarie due aggregazioni distinte. Considera la query seguente.

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

È possibile riscrivere la query con la clausola WINDOW come segue.

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Per impostazione predefinita, il planner di esecuzione RDS per PostgreSQL consolida nodi simili in modo da non duplicare le operazioni. Tuttavia, utilizzando una dichiarazione esplicita per il blocco finestra, è possibile mantenere la query più facilmente. È inoltre possibile migliorare le prestazioni impedendo la duplicazione.

Indagare sulle viste materializzate e le istruzioni CTAS

Quando una vista materializzata si aggiorna, esegue una query. Questa query può contenere un'operazione come GROUP BY, ORDER BY oppureDISTINCT. Durante un aggiornamento, è possibile osservare un numero elevato di file temporanei e gli eventi di attesa IO:BufFileWrite e IO:BufFileRead. Allo stesso modo, quando crei una tabella basata su una dichiarazione SELECT, l'istruzione CREATE TABLE esegue una query. Per ridurre i file temporanei necessari, ottimizza la query.

Uso di pg_repack per ricostruire gli indici

Quando crei un indice, il motore ordina il set di risultati. Man mano che le tabelle aumentano di dimensioni e man mano che i valori nella colonna indicizzata diventano più diversi, i file temporanei richiedono più spazio. Nella maggior parte dei casi, non è possibile impedire la creazione di file temporanei per tabelle di grandi dimensioni senza modificare l'area della memoria di lavoro di manutenzione. Per ulteriori informazioni su maintenance_work_mem, consulta https://www.postgresql.org/docs/current/runtime-config-resource.html nella documentazione di PostgreSQL.

Una possibile soluzione alternativa quando si ricrea un indice di grandi dimensioni consiste nell'utilizzare l'estensione pg_repack. Per ulteriori informazioni, consulta Riorganizzare le tabelle nei database PostgreSQL con blocchi minimi nella documentazione di pg_repack. Per informazioni sull'impostazione dell'estensione nell'istanza database RDS per PostgreSQL, consulta Riduzione della dimensione nelle tabelle e negli indici con l’estensione pg_repack.

Aumenta maintenance_work_mem quando esegui cluster

Il comando CLUSTER raggruppa la tabella specificata da table_name in base a un indice esistente specificato da index_name. RDS per PostgreSQL ricrea fisicamente la tabella in modo che corrisponda all'ordine di un determinato indice.

Quando lo storage magnetico era prevalente, il clustering era comune perché il throughput di storage era limitato. Ora che lo storage basato su SSD è comune, il clustering è meno popolare. Tuttavia, se si raggruppano le tabelle, è comunque possibile aumentare leggermente le prestazioni a seconda delle dimensioni della tabella, dell'indice, della query e così via.

Se esegui il comando CLUSTER e osservi gli eventi di attesa IO:BufFileWrite e IO:BufFileRead, sintonizza maintenance_work_mem. Aumenta la dimensione della memoria a una quantità abbastanza grande. Un valore elevato significa che il motore può utilizzare più memoria per l'operazione di clustering.

Sintonizza la memoria per impedire IO:BuffileRead e IO:BuffileWrite

In alcune situazioni, è necessario ottimizzare la memoria. L'obiettivo è bilanciare la memoria tra le seguenti aree di consumo utilizzando i parametri appropriati, come segue.

Per ulteriori informazioni sull'ottimizzazione della memoria, consulta Resource Consumption (Consumo delle risorse) nella documentazione di PostgreSQL.

Aumentare le dimensioni dell'area di memoria di lavoro

In alcune situazioni, l'unica opzione è aumentare la memoria utilizzata dalla sessione. Se le tue query sono scritte correttamente e utilizzano i tasti corretti per i join, prendi in considerazione la possibilità di aumentare il valore work_mem.

Per scoprire quanti file temporanei genera una query, imposta log_temp_files su 0. Aumentando il valore work_mem al valore massimo identificato nei log, si impedisce alla query di generare file temporanei. Tuttavia, work_mem imposta il massimo per nodo piano per ogni connessione o operatore parallelo. Se il database ha 5.000 connessioni e se ciascuna utilizza una memoria di 256 MiB, il motore necessita di 1,2 TiB di RAM. Pertanto, la tua istanza potrebbe esaurirsi dalla memoria.

Riserva una memoria sufficiente per il buffer pool condiviso

Il database utilizza aree di memoria come il buffer pool condiviso, non solo l'area di memoria di lavoro. Considerare i requisiti di queste aree di memoria aggiuntive prima di aumentare work_mem.

Ad esempio, supponi che la classe di istanza RDS per PostgreSQL sia db.r5.2xlarge. Questa classe ha 64 GiB di memoria. Per impostazione predefinita, il 25% della memoria è riservato al buffer pool condiviso. Dopo aver sottratto la quantità allocata all'area di memoria condivisa, rimangono 16.384 MB. Non allocare la memoria rimanente esclusivamente all'area della memoria di lavoro perché anche il sistema operativo e il motore richiedono memoria.

La memoria a cui è possibile allocare per work_mem dipende dalla classe di istanza. Se si utilizza una classe di istanza più grande, è disponibile più memoria. Tuttavia, nell'esempio precedente, non è possibile utilizzare più di 16 GiB. In caso contrario, la tua istanza diventa non disponibile quando esaurisce la memoria. Per ripristinare l'istanza dallo stato non disponibile, i servizi di automazione RDS per PostgreSQL si riavviano automaticamente.

Gestisci il numero di connessioni

Supponiamo che l'istanza del database disponga di 5.000 connessioni simultanee. Ogni connessione utilizza almeno 4 MiB di work_mem. L'elevato consumo di memoria delle connessioni rischia di peggiorare le prestazioni. Sono disponibili le seguenti opzioni:

Per i proxy, considera Amazon RDS Proxy, PGBouncer o un connection pooler basato sulla tua applicazione. Questa soluzione riduce il carico della CPU. Riduce inoltre il rischio quando tutte le connessioni richiedono l'area di memoria di lavoro. Quando esistono meno connessioni al database, è possibile aumentare il valore di work_mem. In questo modo, si riduce il verificarsi degli eventi di attesa IO:BufFileRead e IO:BufFileWrite. Inoltre, le query in attesa dell'area di memoria di lavoro accelerano in modo significativo.