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à.

Risoluzione dei blocchi identificabili per i processi di vacuum in RDS per PostgreSQL

La funzionalità di autovacuum esegue processi di vacuum aggressivi e porta l’età degli ID transazione al di sotto della soglia specificata dal parametro autovacuum_freeze_max_age dell’istanza RDS in uso. Puoi tenere traccia del valore di età utilizzando la metrica MaximumUsedTransactionIDs di Amazon CloudWatch.

Per trovare l’impostazione di autovacuum_freeze_max_age (che ha un valore predefinito di 200 milioni di ID transazione) per la tua istanza Amazon RDS, puoi utilizzare la seguente query:

SELECT
    TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
    pg_settings
WHERE
    name = 'autovacuum_freeze_max_age';

postgres_get_av_diag() verifica la presenza di eventuali elementi bloccanti per i processi di vacuum aggressivi solo quando l’età supera la soglia di autovacuum adattivo di Amazon RDS, che corrisponde a 500 milioni di ID transazione. Affinché postgres_get_av_diag() li rilevi, gli elementi bloccanti devono avere un’età di almeno 500 milioni di transazioni.

La funzione postgres_get_av_diag() identifica i seguenti tipi di elementi bloccanti:

Istruzione attiva

In PostgreSQL, un’istruzione attiva è un’istruzione SQL attualmente che è in fase di esecuzione da parte del database. Sono incluse query, transazioni o eventuali operazione in corso. Durante il monitoraggio tramite pg_stat_activity, la colonna dello stato indica che il processo con il PID corrispondente è attivo.

La funzione postgres_get_av_diag() visualizza un output simile a quanto segue quando identifica un’istruzione che è attiva.

blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (29621);"}

Azione suggerita

Seguendo le indicazioni riportate nella colonna suggestion, l’utente può connettersi al database in cui è presente l’istruzione attiva e, come specificato nella colonna suggested_action, è consigliabile esaminare attentamente l’opzione per terminare la sessione. Se è sicuro eseguire la terminazione, è possibile utilizzare la funzione pg_terminate_backend() per terminare la sessione. Questa azione può essere eseguita da un utente amministratore (come l’account principale RDS) o da un utente con il privilegio pg_terminate_backend() richiesto.

Inattivo in transazione

Un’istruzione di inattività in transazione si riferisce a una sessione che ha aperto una transazione esplicita (ad esempio, emettendo un’istruzione BEGIN), ha eseguito del lavoro e ora è in attesa che il client passi altro lavoro o segnali la fine della transazione emettendo un’istruzione COMMIT, ROLLBACK o END (che darebbe luogo a un’operazione COMMIT implicita).

La funzione postgres_get_av_diag() visualizza un output simile a quanto segue quando identifica un’istruzione idle in transaction come elemento bloccante.

blocker               | idle in transaction
database              | my_database
blocker_identifier    | INSERT INTO tt SELECT * FROM tt;
wait_event            | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (28438);"}

Azione suggerita

Come indicato nella colonna suggestion, è possibile connettersi al database in cui è presente una sessione di inattività in transazione e terminare la sessione utilizzando la funzione pg_terminate_backend(). L’utente può essere l’utente amministratore (account principale RDS) o un utente con il privilegio pg_terminate_backend().

Transazione preparata

PostgreSQL consente le transazioni che fanno parte di una strategia di commit in due fasi, denominate transazioni preparate. Queste vengono abilitate impostando il parametro max_prepared_transactions su un valore diverso da zero. Le transazioni preparate sono concepite per garantire che una transazione sia duratura e rimanga disponibile anche dopo crash del database, riavvii o disconnessioni del client. Così come avviene con le transazioni standard, anche le transazioni preparate ricevono in assegnazione un ID transazione e possono influire sul processo di autovacuum. Se lasciato in stato preparato, il processo di autovacuum non può eseguire il blocco e può portare al wraparound degli ID transazione.

Quando le transazioni vengono lasciate in stato preparato indefinitamente senza essere risolte da un gestore di transazioni, diventano transazioni preparate orfane. L’unica soluzione consiste nell’eseguire il commit o il rollback della transazione utilizzando rispettivamente i comandi COMMIT PREPARED o ROLLBACK PREPARED.

La funzione postgres_get_av_diag() visualizza il seguente output quando identifica un elemento bloccante che è una transazione preparata.

blocker               | Prepared transaction
database              | my_database
blocker_identifier    | myptx
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}

Azione suggerita

Come indicato nella colonna dei suggerimenti, connettiti al database in cui si trova la transazione preparata. In base alla colonna suggested_action, valuta attentamente se eseguire un’operazione di COMMIT o ROLLBACK e poi l’azione appropriata.

Per monitorare le transazioni preparate in generale, PostgreSQL offre una vista in stile catalogo denominata pg_prepared_xacts. La seguente query può essere utilizzata per trovare le transazioni preparate.

SELECT
    gid,
    prepared,
    owner,
    database,
    transaction AS oldest_xmin
FROM
    pg_prepared_xacts
ORDER BY
    age(transaction) DESC;

Slot di replica logica

Lo scopo di uno slot di replica è conservare le modifiche non utilizzate finché non vengono replicate su un server di destinazione. Per ulteriori informazioni, consulta la pagina relativa alla replica logica di PostgreSQL.

Esistono due tipi di slot di replica logica.

Slot di replica logica inattivi

Quando la replica viene terminata, i log delle transazioni non utilizzati non possono essere rimossi e lo slot di replica diventa inattivo. Gli slot di replica logica inattivi non vengono utilizzati dai nodi subscriber, ma rimangono comunque sul server, il che comporta la conservazione dei file WAL e impedisce la rimozione dei log delle transazioni meno recenti. Ciò può aumentare l’utilizzo del disco e, nello specifico, impedire al processo di autovacuum di eseguire la pulizia delle tabelle interne del catalogo, poiché il sistema deve impedire che le informazioni LSN vengano sovrascritte. Se il problema non viene risolto, possono verificarsi conseguenze come bloat del catalogo, riduzione delle prestazioni, aumento del rischio di wraparound nel processo di vacuum e quindi potenziali tempi di inattività per le transazioni.

Slot di replica logica attivi ma lenti

Talvolta la rimozione delle tuple morte del catalogo viene ritardata a causa della riduzione delle prestazioni della replica logica. Questo ritardo nella replica, che rallenta l’aggiornamento di catalog_xmin, può causare bloat del catalogo e wraparound nel processo di vacuum.

La funzione postgres_get_av_diag() visualizza un output simile a quanto segue quando individua in uno slot di replica logica un elemento bloccante.

blocker               | Logical replication slot
database              | my_database
blocker_identifier    | slot1
wait_event            | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion            | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}

Azione suggerita

Per risolvere la situazione, controlla la configurazione di replica per individuare eventuali problemi con i dati o lo schema di destinazione che potrebbero determinare la terminazione del processo di applicazione. I problemi più comuni sono i seguenti:

Se il problema è legato a problemi relativi all’infrastruttura:

Se l’istanza si trova al di fuori della rete AWS oppure su AWS EC2, contatta l’utente amministratore per sapere come risolvere i problemi relativi alla disponibilità o all’infrastruttura.

Eliminazione dello slot inattivo

Dopo aver verificato che lo slot di replica non è più necessario, eliminalo per far sì che il processo di autovacuum possa continuare. La condizione active = 'f' garantisce che uno slot venga eliminato solo se inattivo.

SELECT pg_drop_replication_slot('slot1') WHERE active ='f'

Repliche di lettura

Quando l’impostazione hot_standby_feedback è abilitata per le repliche di lettura di Amazon RDS, il processo di autovacuum sul database primario non riesce a rimuovere le righe morte che potrebbero essere ancora necessarie per le query in esecuzione sulla replica di lettura. Questo influisce su tutti i tipi di replica di lettura fisica, inclusi quelli gestiti con o senza slot di replica. Questo comportamento è necessario perché le query in esecuzione sulla replica in standby richiedono che tali righe rimangano disponibili sul nodo primario, prevenendo conflitti e annullamenti delle query.

Replica di lettura con slot di replica fisica

Le repliche di lettura con slot di replica fisica migliorano in modo significativo l’affidabilità e la stabilità della replica in RDS per PostgreSQL. Questi slot assicurano che il database primario conservi i file WAL (Write-Ahead Log) essenziali finché il processo di replica non li elabora, mantenendo la coerenza dei dati anche durante le interruzioni della rete.

A partire dalla versione 14 di RDS per PostgreSQL, tutte le repliche utilizzano slot di replica. Nelle versioni precedenti, solo le repliche tra Regioni utilizzavano slot di replica.

La funzione postgres_get_av_diag() visualizza un output simile a quando segue quando individua in una replica di lettura con slot di replica fisica un elemento bloccante.

blocker               | Read replica with physical replication slot
database              |
blocker_identifier    | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx
wait_event            | Not applicable
autovacuum_lagging_by | 554,080,689
suggestion            | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query:                           
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;                                                       
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                 +                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;","                                                                                 +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                   +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Delete the read replica if not needed"}

Replica di lettura con replica in streaming

Amazon RDS consente di configurare repliche di lettura senza uno slot di replica fisica nelle versioni precedenti, fino alla versione 13. Questo approccio riduce il sovraccarico consentendo al nodo primario di riciclare i file WAL in modo più aggressivo, il che è vantaggioso in ambienti con spazio su disco limitato e può tollerare ReplicaLag occasionali. Tuttavia, senza uno slot, la replica in standby deve rimanere sincronizzata per evitare file WAL mancanti. Amazon RDS utilizza file WAL archiviati per consentire alla replica di recuperare gli eventuali ritardi, ma questo processo richiede un monitoraggio accurato e può essere lento.

La funzione postgres_get_av_diag() visualizza un output simile a quanto segue quando individua in una replica di lettura in streaming un elemento bloccante.

blocker               | Read replica with streaming replication slot
database              | Not applicable
blocker_identifier    | xx.x.x.xxx/xx
wait_event            | Not applicable
autovacuum_lagging_by | 610,146,760
suggestion            | Run the following query on the replica "xx.x.x.xxx" to find the long running query:                                                                                                                                                         +
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;                                                                                                                                                     +
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                       +
                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;","                                                                                                                        +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                                                          +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Delete the read replica if not needed"}

Azione suggerita

Come consigliato nella colonna suggested_action, esamina attentamente queste opzioni per sbloccare il processo di autovacuum.

Tabelle temporanee

Le tabelle temporanee, create utilizzando la parola chiave TEMPORARY, risiedono nello schema temporaneo (ad esempio, pg_temp_xxx) e sono accessibili solo alla sessione che le ha create. Le tabelle temporanee vengono eliminate al termine della sessione. Tuttavia, queste tabelle sono invisibili al processo di autovacuum di PostgreSQL e devono sottoposte manualmente a vacuum dalla sessione che le ha create. Il tentativo di eseguire il processo di vacuum sulla tabella temporanea da un’altra sessione non ha alcun effetto.

In circostanze insolite, una tabella temporanea esiste senza che una sessione attiva ne sia proprietaria. Se la sessione proprietaria si conclude inaspettatamente a causa di un crash irreversibile, di un problema di rete o di un evento simile, è possibile che la pulizia della tabella temporanea non venga eseguita e che di conseguenza la tabella rimanga “orfana”. Quando il processo di autovacuum di PostgreSQL rileva una tabella temporanea orfana, registra il seguente messaggio:

LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"

La funzione postgres_get_av_diag() visualizza un output simile a quanto segue quando identifica in una tabella temporanea un elemento bloccante. Affinché la funzione mostri correttamente l’output relativo alle tabelle temporanee, deve essere eseguita all’interno dello stesso database in cui esistono tali tabelle.

blocker               | Temporary table
database              | my_database
blocker_identifier    | pg_temp_14.ttemp
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"DROP TABLE ttemp;"}

Azione suggerita

Segui le istruzioni fornite nella colonna suggestion dell’output per identificare e rimuovere la tabella temporanea che impedisce l’esecuzione del processo di autovacuum. Utilizza il comando seguente per eliminare la tabella temporanea riportata da postgres_get_av_diag(). Sostituisci il nome della tabella in base all’output fornito dalla funzione postgres_get_av_diag().

DROP TABLE my_temp_schema.my_temp_table;

La seguente query può essere utilizzata per identificare le tabelle temporanee:

SELECT
    oid,
    relname,
    relnamespace::regnamespace,
    age(relfrozenxid)
FROM
    pg_class
WHERE
relpersistence = 't'
ORDER BY
    age(relfrozenxid) DESC;