Ottimizzazione di processi autovacuum in Database di Azure per PostgreSQL - Server flessibile

SI APPLICA A: Server flessibile di Database di Azure per PostgreSQL

Questo articolo offre una panoramica della funzionalità autovacuum per server flessibile di Database di Azure per PostgreSQL e le guide alla risoluzione dei problemi delle funzionalità disponibili per monitorare i blocchi di blob del database e autovacuum. Fornisce inoltre informazioni sulla distanza del database dalla situazione di emergenza o di ritorno a capo.

Che cos'è autovacuum

Autovacuum è un processo PostgreSQL in background che pulisce automaticamente le tuple non recapitabili e aggiorna le statistiche. Consente di mantenere le prestazioni del database eseguendo automaticamente due attività di manutenzione chiave:

  • VACUUM: libera spazio su disco rimuovendo le tuple non recapitabili.
  • ANALYZE: raccoglie le statistiche per consentire a PostgreSQL Optimizer di scegliere i percorsi di esecuzione migliori per le query.

Per garantire il corretto funzionamento di autovacuum, il parametro del server autovacuum deve essere sempre impostato su ON. Se abilitato, PostgreSQL decide automaticamente quando eseguire VACUUM o ANALYZE in una tabella, assicurando che il database rimanga efficiente e ottimizzato.

Autovacuum interni

Autovacuum legge le pagine che cercano tuple non recapitabili e, se non ne trova, rimuove la pagina. Quando autovacuum trova tuple non recapitabili, le rimuove. Il costo è basato su:

Parametro Descrizione
vacuum_cost_page_hit Costo della lettura di una pagina già presente nei buffer condivisi e che non richiede la lettura di un disco. Il valore predefinito è impostato su 1.
vacuum_cost_page_miss Costo del recupero di una pagina che non si trova nei buffer condivisi. Il valore predefinito è impostato su 10.
vacuum_cost_page_dirty Costo della scrittura in una pagina quando sono presenti tuple non recapitabili. Il valore predefinito è impostato su 20.

La quantità di lavoro eseguita automaticamente dipende da due parametri:

Parametro Descrizione
autovacuum_vacuum_cost_limit La quantità di lavoro che si esegue in un'unica operazione.
autovacuum_vacuum_cost_delay Il numero di millisecondi in cui autovacuum è in stato di sospensione dopo che raggiunge il limite di costo specificato dal parametro autovacuum_vacuum_cost_limit.

In tutte le versioni attualmente supportate di Postgres il valore predefinito per autovacuum_vacuum_cost_limit è 200 (in realtà, impostato su -1, che lo rende uguale al valore del normale vacuum_cost_limit, che per impostazione predefinita è 200).

Per quanto riguarda autovacuum_vacuum_cost_delay, in Postgres versione 11 il valore predefinito è 20 millisecondi, mentre nelle versioni di Postgres 12 e superiori il valore predefinito è 2 millisecondi.

Autovacuum si riattiva 50 volte (50*20 ms=1000 ms) ogni secondo. Ogni volta che si riattiva, autovacuum legge 200 pagine.

Ciò significa che in un secondo autovacuum può eseguire:

  • ~80 MB/sec [ (200 pagine/vacuum_cost_page_hit) * 50 * 8 kB per pagina] se tutte le pagine con tuple non recapitabili vengono trovate nei buffer condivisi.
  • ~8 MB/sec [ (200 pagine/vacuum_cost_page_miss) * 50 * 8 kB per pagina] se tutte le pagine con tuple non recapitabili vengono lette dal disco.
  • ~4 MB/sec [ (200 pagine/vacuum_cost_page_dirty) * 50 * 8 kB per pagina] autovacuum può scrivere fino a 4 MB/sec.

Monitorare autovacuum

Usare le query seguenti per monitorare autovacuum:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

Le colonne seguenti consentono di determinare se autovacuum sta recuperando l'attività della tabella:

Parametro Descrizione
dead_pct Percentuale di tuple non recapitabili rispetto alle tuple attive.
last_autovacuum Data dell'ultima volta in cui la tabella è stata eseguita automaticamente.
last_autoanalyze Data dell'ultima analisi della tabella.

Quando viene attivato il trigger autovacuum di PostgreSQL

Un'azione autovacuum ( ANALYZE o VACUUM) viene attivata quando il numero di tuple non recapitabili supera un determinato numero che dipende da due fattori: il numero totale di righe in una tabella, più una soglia fissa. ANALYZE, per impostazione predefinita, viene attivato quando il 10% della tabella più 50 righe cambia, mentre VACUUM si attiva quando il 20% della tabella più 50 righe cambia. Poiché la soglia di VACUUM è pari al doppio della soglia di ANALYZE, ANALYZE viene attivato prima di VACUUM. Per le versioni PG >=13; ANALYZE per impostazione predefinita, viene attivato quando il 20% della tabella più 1000 inserimenti di righe.

Le equazioni esatte per ogni azione sono:

  • Analisi automatica = autovacuum_analyze_scale_factor * tuple + autovacuum_analyze_threshold o autovacuum_vacuum_insert_scale_factor * tuple + autovacuum_vacuum_insert_threshold (per le versioni PG >= 13)
  • Autovacuum = autovacuum_vacuum_scale_factor * tuple + autovacuum_vacuum_threshold

Ad esempio, se è disponibile una tabella con 100 righe. L'equazione seguente fornisce quindi le informazioni sui trigger di analisi e vacuum:

Per aggiornamenti/eliminazioni: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Analyze si attiva dopo la modifica di 60 righe in una tabella mentre Vacuum si attiva quando vengono modificate 70 righe in una tabella.

Per inserimenti: Autoanalyze = 0.2 * 100 + 1000 = 1020

Analyze si attiva dopo l'inserimento di 1.020 righe in una tabella

Ecco la descrizione dei parametri usati nell'equazione:

Parametro Descrizione
autovacuum_analyze_scale_factor Percentuale di inserimenti/aggiornamenti/eliminazioni che attiva NALYZE nella tabella.
autovacuum_analyze_threshold Specifica il numero minimo di tuple inserite/aggiornate/eliminate per eseguire ANALYZE in una tabella.
autovacuum_vacuum_insert_scale_factor Percentuale di inserimenti che attiva ANLYZE nella tabella.
autovacuum_vacuum_insert_threshold Specifica il numero minimo di tuple inserite per eseguire ANALYZE in una tabella.
autovacuum_vacuum_scale_factor Percentuale di aggiornamenti/eliminazioni che attiva VACUUM nella tabella.

Usare la query seguente per elencare le tabelle in un database e identificare le tabelle idonee per il processo autovacuum:

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Nota

La query non prende in considerazione che autovacuum può essere configurato per ogni tabella usando il comando DDL "alter table".

Problemi comuni di autovacuum

Esaminare l'elenco seguente dei possibili problemi comuni relativi al processo autovacuum.

Non stare al passo con il server occupato

Il processo autovacuum stima il costo di ogni operazione di I/O, accumula un totale per ogni operazione eseguita e sospende una volta raggiunto il limite massimo del costo. autovacuum_vacuum_cost_delay e autovacuum_vacuum_cost_limit sono i due parametri del server usati nel processo.

Per impostazione predefinita, autovacuum_vacuum_cost_limit è impostato su –1, ovvero il limite di costo autovacuum è lo stesso valore del parametro vacuum_cost_limit, che per impostazione predefinita è 200. vacuum_cost_limit è il costo di un vacuum manuale.

Se autovacuum_vacuum_cost_limit è impostato su -1, autovacuum usa il parametro vacuum_cost_limit, ma se autovacuum_vacuum_cost_limit stesso è impostato su un valore maggiore di -1, viene considerato il parametro autovacuum_vacuum_cost_limit.

Nel caso in cui autovacuum non sia aggiornato, è possibile modificare i parametri seguenti:

Parametro Descrizione
autovacuum_vacuum_cost_limit Impostazione predefinita: 200. Il limite di costi potrebbe essere aumentato. L'utilizzo di CPU e I/O nel database deve essere monitorato prima e dopo aver apportato modifiche.
autovacuum_vacuum_cost_delay Postgres versione 11 - Impostazione predefinita: 20 ms. Il parametro potrebbe essere ridotto a 2-10 ms.
Postgres versioni 12 e successive - Impostazione predefinita: 2 ms.

Nota

  • Il valore autovacuum_vacuum_cost_limit viene distribuito proporzionalmente tra i ruoli di lavoro autovacuum in esecuzione, in modo che, se sono presenti più di uno, la somma dei limiti per ogni ruolo di lavoro non supera il valore del parametro autovacuum_vacuum_cost_limit.
  • autovacuum_vacuum_scale_factor è un altro parametro che potrebbe attivare il vacuum in una tabella in base all'accumulo di tuple non recapitabili. Impostazione predefinita: 0.2, Intervallo consentito: 0.05 - 0.1. Il fattore di scala è specifico del carico di lavoro e deve essere impostato a seconda della quantità di dati nelle tabelle. Prima di modificare il valore, esaminare il carico di lavoro e i singoli volumi di tabella.

Autovacuum in esecuzione costante

L'esecuzione continua di autovacuum potrebbe influire sull'utilizzo di CPU e I/O nel server. Ecco alcuni dei possibili motivi:

maintenance_work_mem

Il daemon Autovacuum usa autovacuum_work_mem impostato per impostazione predefinita su -1, il che significa che autovacuum_work_mem avrà lo stesso valore del parametro maintenance_work_mem. Questo documento presuppone che autovacuum_work_mem sia impostato su -1 e maintenance_work_mem venga usato dal daemon autovacuum.

Se maintenance_work_mem è basso, potrebbe essere aumentato fino a 2 GB nel server flessibile di Database di Azure per PostgreSQL. Una regola generale consiste nell'allocare 50 MB a maintenance_work_mem per ogni 1 GB di RAM.

Numero elevato di database

Autovacuum tenta di avviare un ruolo di lavoro in ogni database ogni autovacuum_naptime secondi.

Ad esempio, se un server ha 60 database e autovacuum_naptime è impostato su 60 secondi, il ruolo di lavoro autovacuum viene avviato ogni secondo [autovacuum_naptime/Numero di database].

È consigliabile aumentare autovacuum_naptime se sono presenti più database in un cluster. Allo stesso tempo, il processo autovacuum può essere reso più aggressivo aumentando autovacuum_cost_limit e riducendo i parametri autovacuum_cost_delay e aumentando autovacuum_max_workers dal valore predefinito 3 a 4 o 5.

Errori di memoria insufficiente

Valori maintenance_work_mem eccessivamente aggressivi potrebbero causare periodicamente errori di memoria insufficiente nel sistema. È importante comprendere la RAM disponibile nel server prima di apportare qualsiasi modifica al parametro maintenance_work_mem.

Autovacuum è troppo problematico

Se autovacuum usa più risorse, è possibile eseguire le azioni seguenti:

Parametri autovacuum

Valutare i parametri autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_max_workers. L'impostazione non corretta dei parametri autovacuum potrebbe causare scenari in cui autovacuum diventa troppo problematico.

Se autovacuum è troppo problematico, prendere in considerazione le azioni seguenti:

  • Aumentare autovacuum_vacuum_cost_delay e ridurre autovacuum_vacuum_cost_limit se impostato su un valore superiore al valore predefinito 200.
  • Ridurre il numero di autovacuum_max_workers se impostato su un valore superiore al valore predefinito 3.

Troppi ruoli di lavoro autovacuum

L'aumento del numero di ruoli di lavoro autovacuum non aumenta la velocità di vacuum. Non è consigliabile disporre di un numero elevato di ruoli di lavoro autovacuum.

L'aumento del numero di ruoli di lavoro autovacuum comporta un consumo di memoria maggiore e, a seconda del valore di maintenance_work_mem, potrebbe causare una riduzione delle prestazioni.

Ogni processo di lavoro autovacuum ottiene solo (1/autovacuum_max_workers) del totale autovacuum_cost_limit, quindi la presenza di un numero elevato di ruoli di lavoro causa un rallentamento di ognuno di essi.

Se il numero di ruoli di lavoro è aumentato, autovacuum_vacuum_cost_limit dovrebbe anche essere aumentato e/o autovacuum_vacuum_cost_delay dovrebbe essere ridotto per velocizzare il processo di vacuum.

Tuttavia, se si imposta il parametro a livello di tabella autovacuum_vacuum_cost_delay o autovacuum_vacuum_cost_limit, i ruoli di lavoro in esecuzione in tali tabelle non vengono considerati nell'algoritmo di bilanciamento [autovacuum_cost_limit/autovacuum_max_workers].

Protezione wraparound dell'ID transazione autovacuum (TXID)

Quando un database viene eseguito nella protezione wraparound dell'ID transazione, è possibile osservare un messaggio di errore simile al seguente:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Nota

Questo messaggio di errore è una controllo di lunga durata. In genere, non è necessario passare alla modalità utente singolo. È invece possibile eseguire i comandi VACUUM necessari ed eseguire l'ottimizzazione per l'esecuzione rapida di VACUUM. Anche se non è possibile eseguire alcun linguaggio DML (Data Manipulation Language), è comunque possibile eseguire VACUUM.

Il problema di wraparound si verifica quando il database non è vuoto o sono presenti troppe tuple non recapitabili che non vengono rimosse da autovacuum. I motivi di questo problema potrebbero essere:

Carico di lavoro pesante

Il carico di lavoro potrebbe causare un numero eccessivo di tuple non recapitabili in un breve periodo che rende difficile l'aggiornamento automatico. Le tuple non recapitabili nel sistema si aggiungono in un periodo che comportano una riduzione delle prestazioni delle query e la situazione di ritorno a capo. Un motivo per cui si verifica questa situazione potrebbe essere dovuto al fatto che i parametri autovacuum non sono impostati in modo adeguato e non tengono il passo con un server occupato.

Transazioni con esecuzione prolungata

Qualsiasi transazione a esecuzione prolungata nel sistema non consente la rimozione delle tuple non recapitabili durante l'esecuzione dell'autovacuum. Sono un blocco per il processo di vacuum. La rimozione delle transazioni a esecuzione prolungata libera le tuple non recapitabili per l'eliminazione durante l'esecuzione di autovacuum.

È possibile rilevare transazioni con esecuzione prolungata usando la query seguente:

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

Istruzioni preparate

Se sono presenti istruzioni preparate che non vengono sottoposte a commit, impediscono la rimozione delle tuple non recapitabili.
La query seguente consente di trovare istruzioni preparate non sottoposte a commit:

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

Usare COMMIT PREPARED o ROLLBACK PREPARED per eseguire il commit o il rollback di queste istruzioni.

Slot di replica inutilizzati

Gli slot di replica inutilizzati impediscono ad autovacuum di richiedere tuple non recapitabili. La query seguente consente di identificare gli slot di replica inutilizzati:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Usare pg_drop_replication_slot() per eliminare gli slot di replica inutilizzati.

Quando il database viene eseguito nella protezione wraparound dell'ID transazione, verificare la presenza di eventuali blocchi come indicato in precedenza e rimuovere manualmente i blocchi per il completamento dell'autovacuum. È anche possibile aumentare la velocità di autovacuum impostando autovacuum_cost_delay su 0 e aumentando autovacuum_cost_limit a un valore maggiore di 200. Tuttavia, le modifiche apportate a questi parametri non si applicano ai ruoli di lavoro autovacuum esistenti. Riavviare il database o terminare manualmente i ruoli di lavoro esistenti per applicare le modifiche ai parametri.

Requisiti specifici della tabella

I parametri autovacuum possono essere impostati per le singole tabelle. Questo è particolarmente importante per tabelle piccole e grandi. Ad esempio, per una tabella di piccole dimensioni contenente solo 100 righe, autovacuum attiva l'operazione VACUUM quando vengono modificate 70 righe (come calcolato in precedenza). Se questa tabella viene aggiornata di frequente, è possibile che vengano visualizzate centinaia di operazioni autovacuum al giorno, impedendo ad autovacuum di gestire altre tabelle in cui la percentuale di modifiche non è significativa. In alternativa, una tabella contenente un miliardo di righe deve modificare 200 milioni di righe per attivare le operazioni autovacuum. L'impostazione dei parametri autovacuum impedisce in modo appropriato tali scenari.

Per impostare l'impostazione autovacuum per tabella, modificare i parametri del server come esempi seguenti:

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

Carichi di lavoro di solo inserimento

Nelle versioni di PostgreSQL <= 13, autovacuum non viene eseguito nelle tabelle con un carico di lavoro di solo inserimento, perché non sono presenti tuple non recapitabili e non è necessario recuperare spazio libero. Tuttavia, l'analisi automatica viene eseguita per i carichi di lavoro di solo inserimento poiché sono presenti nuovi dati. Gli svantaggi sono i seguenti:

  • La mappa di visibilità delle tabelle non viene aggiornata e pertanto le prestazioni delle query, soprattutto in cui sono presenti analisi solo indice, iniziano a soffrire nel tempo.
  • Il database può attivare la protezione wraparound dell'ID transazione.
  • I bit di hint non sono impostati.

Soluzioni

Versioni di Postgres <= 13

Usando l'estensione pg_cron, è possibile configurare un processo Cron per pianificare un'analisi periodica del vacuum nella tabella. La frequenza del processo Cron dipende dal carico di lavoro.

Per indicazioni dettagliate sull'uso di pg_cron, vedere Estensioni.

Postgres 13 e versioni successive

Autovacuum viene eseguito su tabelle con un carico di lavoro di solo inserimento. Due nuovi parametri del server autovacuum_vacuum_insert_threshold e autovacuum_vacuum_insert_scale_factor controllano quando è possibile attivare autovacuum nelle tabelle di solo inserimento.

Guide alla risoluzione dei problemi

Usando le guide alla risoluzione dei problemi di funzionalità disponibili nel portale del server flessibile di Database di Azure per PostgreSQL, è possibile monitorare il BLOB a livello di database o di singolo schema insieme all'identificazione di potenziali bloccanti per il processo di autovacuum. Sono disponibili due guide alla risoluzione dei problemi: la prima è il monitoraggio autovacuum che può essere usato per monitorare il bloat a livello di database o di singolo schema. La seconda guida alla risoluzione dei problemi è costituita da blocchi autovacuum e wraparound, e consente di identificare i potenziali blocchi autovacuum. Fornisce inoltre informazioni sulla distanza dei database sul server dalla situazione di wraparound o di emergenza. Le guide alla risoluzione dei problemi condividono anche raccomandazioni per attenuare i potenziali problemi. Come configurare le guide alla risoluzione dei problemi: per usarle, vedere Installare le guide alla risoluzione dei problemi.

Raccomandazioni di Azure Advisor

Le raccomandazioni di Azure Advisor rappresentano un modo proattivo per identificare se un server ha un rapporto di bloat elevato o se il server si avvicina allo scenario di wrapping delle transazioni. È anche possibile impostare avvisi per le raccomandazioni usando Creare avvisi di Azure Advisor per i nuovi elementi consigliati usando il portale di Azure

Gli elementi consigliati sono i seguenti:

  • Rapporto di bloat elevato: un rapporto di bloat elevato può influire sulle prestazioni del server in diversi modi. Un problema significativo è che PostgreSQL Engine Optimizer potrebbe avere difficoltà a selezionare il piano di esecuzione migliore, causando una riduzione delle prestazioni delle query. Pertanto, quando la percentuale di bloat in un server raggiunge una determinata soglia viene attivata una raccomandazione per evitare tali problemi di prestazioni.

  • Scorrimento automatico delle transazioni: questo scenario è uno dei problemi più gravi che possono verificarsi in un server. Una volta che il server è in questo stato, potrebbe smettere di accettare altre transazioni, causando la sola lettura del server. Di conseguenza, viene attivata una raccomandazione quando si nota che il server ha superato la soglia di 1 miliardo di transazioni.