Utilizzo della funzionalità di autovacuum di PostgreSQL in Amazon RDS per PostgreSQL

Consigliamo vivamente di usare la caratteristica di autovacuum per mantenere l'integrità dell'istanza database di PostgreSQL. La funzione di autovaacuum automatizza l'esecuzione del comando VACUUM e ANALYZE. Verifica la presenza di tabelle con un numero elevato di tuple inserite, aggiornate o eliminate. Dopo questa verifica, recupera lo storage rimuovendo i dati obsoleti o le tuple da database PostgreSQL.

Per impostazione predefinita, la funzionalità di autovacuum è abilitata per le istanze database di RDS per PostgreSQL create utilizzando uno dei gruppi di parametri di database PostgreSQL predefiniti. Per impostazione predefinita vengono impostati anche altri parametri di configurazione associati alla caratteristica di autovacuum. Poiché questi valori di default sono in qualche modo generici, è possibile trarre vantaggio dalla regolazione di alcuni parametri associati alla caratteristica di autovacuum per il carico di lavoro specifico.

Di seguito, puoi trovare ulteriori informazioni sulla funzionalità di autovacuum e su come regolare alcuni dei relativi parametri sulla tua istanza database di RDS per PostgreSQL. Per informazioni generali, consulta Best practice per l'utilizzo di PostgreSQL.

Argomenti

• Allocazione di memoria per il vacuum

• Riduzione della probabilità che si verifichi il wraparound dell’ID delle transazioni

• Determinare se le tabelle nel database devono essere sottoposte a vacuum

• Determinare quali tabelle sono attualmente idonee per l'Autovacuum

• Determinare se l'Auotvacuum è attualmente in esecuzione e per quanto tempo

• Esecuzione di un congelamento manuale del vacuum

• Indicizzare di nuovo una tabella quando l'autovacuum è in esecuzione

• Gestione di autovacuum con indici di grandi dimensioni

• Altri parametri che influenzano l'autovacuum

• Impostazione dei parametri di autovacuum a livello tabella

• Registrazione delle attività di autovacuum e vacuum

• Comprensione del comportamento della funzionalità di autovacuum con database non validi

• Identificazione e risoluzione dei blocchi per i processi di vacuum aggressivi in RDS per PostgreSQL

Allocazione di memoria per il vacuum

Uno dei parametri più importanti che influenzano le prestazioni dell'autovacuum è il parametro autovacuum_work_mem. Nelle versioni 14 e precedenti di RDS per PostgreSQL, il parametro autovacuum_work_mem è impostato su -1, a indicare che al suo posto viene utilizzata l’impostazione maintenance_work_mem. Per tutte le altre versioni, autovacuum_work_mem è determinato da GREATEST({DBInstanceClassMemory/32768}, 65536).

Le operazioni manuali di vacuum utilizzano sempre l’impostazione maintenance_work_mem, con un’impostazione predefinita di GREATEST({DBInstanceClassMemory/63963136*1024}, 65536) e può anche essere regolata a livello di sessione utilizzando il comando SET per operazioni VACUUM manuali più mirate.

autovacuum_work_mem determina la memoria di autovacuum per contenere gli identificatori delle tuple inattive (pg_stat_all_tables.n_dead_tup) per gli indici dell’attività di vacuum.

Quando esegui i calcoli per determinare il valore del parametro autovacuum_work_mem, tieni presente quanto segue:

• Se imposti il parametro troppo basso, il processo di vacuum potrebbe dover eseguire la scansione della tabella più volte per completare il lavoro. Queste scansioni multiple possono avere un impatto negativo sulle prestazioni Per le istanze più grandi, impostare maintenance_work_mem o autovacuum_work_mem ad almeno 1 GB può migliorare le prestazioni delle tabelle di vacuum con un numero elevato di tuple inattive. Tuttavia, nelle versioni 16 e precedenti di PostgreSQL, l’utilizzo della memoria di vacuum è limitato a 1 GB, il che è sufficiente per elaborare circa 179 milioni di tuple inattive in un unico passaggio. Se una tabella ha più tuple inattive di questa, il vacuum dovrà effettuare più passaggi tra gli indici della tabella, facendo aumentare notevolmente il tempo necessario. A partire dalla versione 17 di PostgreSQL, non esiste un limite di 1 GB e la funzionalità di autovacuum può elaborare più di 179 milioni di tuple utilizzando strutture radice.

  Un identificatore tuple ha una dimensione di 6 byte. Per stimare la memoria necessaria per il processo di vacuum di un indice di una tabella, esegui una query pg_stat_all_tables.n_dead_tup per trovare il numero di tuple inattive, quindi moltiplica questo numero per 6 per determinare la memoria richiesta per svuotare l’indice in un unico passaggio. Puoi utilizzare la seguente query.

  SELECT
      relname AS table_name,
      n_dead_tup,
      pg_size_pretty(n_dead_tup * 6) AS estimated_memory
  FROM
      pg_stat_all_tables
  WHERE
      relname = 'name_of_the_table';

• Il parametro autovacuum_work_mem funziona insieme al parametro autovacuum_max_workers. Ogni worker tra autovacuum_max_workers può utilizzare la memoria allocata. Se si dispone di molte tabelle di piccole dimensioni, assegna più autovacuum_max_workers e meno autovacuum_work_mem. Se disponi di tabelle di grandi dimensioni (ad esempio, maggiori di 100 GB), alloca più memoria e meno processi worker. Si deve avere abbastanza memoria allocata affinché si abbia esito positivo sulle tabelle più grandi. Quindi assicurati che la combinazione di processi worker e memoria sia uguale alla memoria totale che desideri allocare.

Riduzione della probabilità che si verifichi il wraparound dell’ID delle transazioni

In alcuni casi, le impostazioni del gruppo di parametri correlate all'autovacuum potrebbero non essere abbastanza aggressive da prevenire il wraparound dell'ID delle transazioni. Per risolvere questo problema, RDS per PostgreSQL offre un meccanismo che adatta automaticamente i valori del parametro autovacuum. Ottimizzazione adattiva del parametro di autovacuum è una funzionalità di RDS per PostgreSQL. Una spiegazione dettagliata di Wraparound della transazione si trova nella documentazione di PostgreSQL.

La regolazione adattiva del parametro autovacuum è attivata per impostazione predefinita per le istanze RDS per PostgreSQL con il parametro dinamico rds.adaptive_autovacuum impostato su ON. Si consiglia di tenere questa opzione attivata. Tuttavia, per disattivare l'ottimizzazione adattiva del parametro di autovacuum, impostare il parametro rds.adaptive_autovacuum su 0 o OFF.

Il wraparound dell’ID delle transazioni è ancora possibile quando Amazon RDS Amazon RDS regola i parametri di autovacuum. Si consiglia di implementare un allarme Amazon CloudWatch per il wraparound dell'ID della transazione. Per ulteriori informazioni, consulta il post Implementazione di un sistema di avvisi rapidi per il wraparound degli ID transazione in RDS for PostgreSQL del blog sui database di AWS.

Con la regolazione adattiva del parametro di autovacuum attivata, Amazon RDS inizia a regolare i parametri di autovacuum quando il parametro di CloudWatch MaximumUsedTransactionIDs raggiunge il valore del parametro autovacuum_freeze_max_age o 500.000.000, a seconda di quale sia quello maggiore.

Amazon RDS continua ad adattare i parametri per l’autovacuum se una tabella continua a tendere verso i wraparound dell’ID della transazione. Ognuno di questi aggiustamenti dedica più risorse all’autovacuum per evitare il wraparound. Amazon RDS aggiorna i seguenti parametri correlati all’autovacuum:

• autovacuum_vacuum_cost_delay

• autovacuum_vacuum_cost_limit

• autovacuum_work_mem

• autovacuum_naptime

RDS modifica questi parametri solo se il nuovo valore rende l’autovacuum più aggressivo. I parametri vengono modificati nella memoria sull’istanza database. I valori nel gruppo di parametri non vengono modificati. Per visualizzare le impostazioni in memoria correnti, utilizzare il comando SQL PostgreSQL SHOW.

Quando Amazon RDS modifica uno qualsiasi dei parametri autovacuum, genera un evento per l’istanza database interessata. Questo evento è visibile sulla AWS Management Console e tramite l'API Amazon RDS. Quando il parametro CloudWatch MaximumUsedTransactionIDs restituisce un valore a di sotto della soglia, Amazon RDS ripristina i parametri relativi all'autovacuum in memoria riportandoli ai valori specificati nel gruppo di parametri. Quindi genera un altro evento corrispondente a questa modifica.