Condividi tramite

Ottimizzazione di autovacuum in Database di Azure per PostgreSQL

Questo articolo offre una panoramica della funzionalità autovacuum per il Database di Azure per PostgreSQL e delle guide disponibili per la risoluzione dei problemi, per monitorare il gonfiore del database e i blocchi autovacuum. Fornisce inoltre informazioni sulla distanza del database da una situazione di emergenza o di wraparound.

Nota

Questo articolo illustra l'ottimizzazione di autovacuum per tutte le versioni supportate di PostgreSQL nel server flessibile di Database di Azure per PostgreSQL. Alcune funzionalità indicate sono specifiche della versione, ad esempio vacuum_buffer_usage_limit per PostgreSQL 16 e versioni successive e autovacuum_vacuum_max_threshold per PostgreSQL 18 e versioni successive.

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: recupera lo spazio all'interno dei file del database rimuovendo tuple inattivi e contrassegnando tale spazio come riutilizzabile da PostgreSQL. Non riduce necessariamente le dimensioni fisiche dei file di database su disco. Per restituire spazio al sistema operativo, usare operazioni che riscrivono la tabella (ad esempio VACUUM FULL o pg_repack), che presentano considerazioni aggiuntive, ad esempio blocchi esclusivi o finestre di manutenzione.
  • ANALYZE: raccoglie statistiche di tabella e indice usate da PostgreSQL Query Planner per scegliere piani di esecuzione efficienti.

Per garantire il corretto funzionamento di autovacuum, impostare il parametro del server autovacuum 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 alla ricerca di tuple inattive e, se non ne trova, rimuove la pagina. Quando autovacuum trova tuple non recapitabili, le rimuove. Il costo si basa sui parametri seguenti:

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

La quantità di lavoro che autovacuum esegue dipende da due parametri:

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

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

Il valore predefinito per autovacuum_vacuum_cost_delay è di 2 millisecondi in PostgreSQL versioni 12 e successive (era di 20 millisecondi nella versione 11).

Limite di utilizzo del buffer (PostgreSQL 16+)

A partire da PostgreSQL versione 16, è possibile usare il parametro per controllare l'utilizzo vacuum_buffer_usage_limit della memoria durante le operazioni VACUUM, ANALYZE e autovacuum.

Parametro Descrizione
vacuum_buffer_usage_limit Imposta le dimensioni del pool di buffer per le operazioni VACUUM, ANALYZE e autovacuum. Questo parametro limita la quantità di cache del buffer condiviso che queste operazioni possono usare, impedendo loro di consumare risorse di memoria eccessive.

Questo parametro consente di impedire a VACUUM e autovacuum di rimuovere troppe pagine utili dai buffer condivisi, migliorando così le prestazioni complessive del database durante le operazioni di manutenzione. Il valore predefinito viene in genere impostato basato su shared_buffers, ed è possibile configurarlo per bilanciare le prestazioni del vacuum con le esigenze delle normali operazioni del database.

Soglia massima per autovacuum (PostgreSQL 18+)

A partire da PostgreSQL versione 18, è possibile usare il parametro autovacuum_vacuum_max_threshold per impostare un limite superiore al numero di aggiornamenti di tuple o eliminazioni che attivano Autovacuum.

Parametro Descrizione
autovacuum_vacuum_max_threshold Imposta il numero massimo di aggiornamenti o eliminazioni di tuple prima del processo di vacuum. Se impostato su -1, la soglia massima è disabilitata. Usa questo parametro per un controllo preciso sull'attivazione dell'autovacuum in tabelle molto grandi.

Questo parametro è particolarmente utile per le tabelle di grandi dimensioni in cui l'innesco predefinito basato sul fattore di scala potrebbe far sì che autovacuum debba attendere troppo a lungo prima di avviarsi.

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

Il database Azure per PostgreSQL fornisce le seguenti metriche per il monitoraggio dell'autovacuum.

Le metriche autovacuum possono essere usate per monitorare e ottimizzare le prestazioni di autovacuum per il server flessibile di Database di Azure per PostgreSQL. Ogni metrica viene generata a intervalli di 30 minuti e viene conservata per un massimo di 93 giorni. È possibile creare avvisi per metriche specifiche ed è possibile suddividere e filtrare i dati delle metriche usando la dimensione DatabaseName.

Come abilitare le metriche autovacuum

  • Le metriche autovacuum sono disabilitate per impostazione predefinita.
  • Per abilitare queste metriche, impostare il parametro del server metrics.autovacuum_diagnostics su ON.
  • Questo parametro è dinamico, quindi non è necessario riavviare un'istanza.

Elenco delle metriche autovacuum

Nome visualizzato ID metrica Unità Descrizione Dimensione Impostazione predefinita abilitata
Contatore analisi tabelle utente analyze_count_user_tables Conteggio Numero di volte in cui le tabelle solo utente sono state analizzate manualmente nel database. DatabaseName NO
Contatore analisi automatica tabelle utente autoanalyze_count_user_tables Conteggio Numero di volte in cui le tabelle solo utente sono state analizzate dal daemon autovacuum nel database. DatabaseName NO
Contatore autovacuum tabelle utente autovacuum_count_user_tables Conteggio Numero di volte in cui il daemon autovacuum ha svuotato le tabelle riservate agli utenti nel database. DatabaseName NO
Percentuale software bloat (anteprima) bloat_percent Percento Percentuale di software bloat stimata per le tabelle solo utente. DatabaseName NO
Righe non recapitabili stimate tabelle utente n_dead_tup_user_tables Conteggio Numero stimato di righe non recapitabili per le tabelle solo utente nel database. DatabaseName NO
Righe attive stimate tabelle utente n_live_tup_user_tables Conteggio Numero stimato di righe attive per le tabelle solo utente nel database. DatabaseName NO
Modifiche stimate delle tabelle utente n_mod_since_analyze_user_tables Conteggio Numero stimato di righe modificate dopo l'ultima analisi delle tabelle solo utente. DatabaseName NO
Tabelle utente analizzate tables_analyzed_user_tables Conteggio Numero di tabelle solo utente analizzate nel database. DatabaseName NO
Tabelle utente analizzate in automatico tables_autoanalyzed_user_tables Conteggio Numero di tabelle solo utente analizzate dal daemon autovacuum nel database. DatabaseName NO
Tabelle utente sottoposte ad autovacuum tables_autovacuumed_user_tables Conteggio Numero di tabelle solo utente svuotate dal daemon autovacuum nel database. DatabaseName NO
Contatore tabelle utente tables_counter_user_tables Conteggio Numero di tabelle solo utente nel database. DatabaseName NO
Tabelle utente sottoposte a vacuum tables_vacuumed_user_tables Conteggio Numero di tabelle solo utente sottoposte a vacuum nel database. DatabaseName NO
Contatore vacuum tabelle utente vacuum_count_user_tables Conteggio Numero di volte in cui le tabelle solo utente sono state svuotate manualmente nel database (senza contare VACUUM FULL). DatabaseName NO

Considerazioni sull'utilizzo delle metriche di autovacuum

  • Le metriche autovacuum che usano la dimensione DatabaseName hanno un limite di 30 database.
  • Nello SKU Burstable, il limite è di 10 database per le metriche che usano la dimensione DatabaseName.
  • Il limite della dimensione DatabaseName viene applicato alla colonna OID, che riflette l'ordine di creazione per il database.

Per altre informazioni, vedere Metriche di Autovacuum.

Utilizzare 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 inattive rispetto a quelle attive.
last_autovacuum Data dell'ultima volta in cui la tabella è stata eseguita automaticamente.
last_autoanalyze Data dell'ultima analisi automatica della tabella.

Attivazione di autovacuum

Un'azione autovacuum (ANALYZE o VACUUM) si attiva quando il numero di tuple inattive supera un determinato numero. Questo numero dipende da due fattori: il numero totale di righe in una tabella, più una soglia fissa. ANALYZE si attiva per impostazione predefinita quando viene modificato il 10% della tabella più 50 righe, mentre VACUUM si attiva quando viene modificato il 20% della tabella più 50 righe. Poiché la soglia VACUUM è due volte superiore alla soglia ANALYZE, ANALYZE viene attivato prima di VACUUM.

Per PostgreSQL versioni 13 e successive, ANALIZZA viene attivato per impostazione predefinita quando si verificano 20% della tabella più 1.000 inserimenti di righe.

Le equazioni esatte per ogni azione sono:

  • Analisi automatica = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold or autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (per PostgreSQL versione 13 e successive)
  • Autovacuum = autovacuum_vacuum_scale_factor * tuple + autovacuum_vacuum_threshold

Ad esempio, se si dispone di una tabella con 100 righe, le equazioni seguenti vengono visualizzate quando si attivano le azioni di analisi e vuoto:

Per gli aggiornamenti e le eliminazioni: Autoanalyze = 0.1 * 100 + 50 = 60Autovacuum = 0.2 * 100 + 50 = 70

ANALYZE viene attivato dopo la modifica di 60 righe in una tabella e VACUUM viene attivato quando vengono modificate 70 righe in una tabella.

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

ANALYZE si attiva dopo che sono state inserite 1.020 righe in una tabella.

Ecco la descrizione dei parametri utilizzati nell'equazione:

Parametro Descrizione
autovacuum_analyze_scale_factor Percentuale di inserimenti, aggiornamenti ed eliminazioni che attiva ANALIZZA nella tabella.
autovacuum_analyze_threshold Numero minimo di tuple inserite/aggiornate/eliminate per eseguire ANALYZE in una tabella.
autovacuum_vacuum_insert_scale_factor Percentuale di inserimenti che attiva ANALYZE nella tabella.
autovacuum_vacuum_insert_threshold Numero minimo di tuple inserite in ANALIZZA una tabella.
autovacuum_vacuum_scale_factor Percentuale di aggiornamenti ed eliminazioni che attiva VACUUM nella tabella.

Utilizzare 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 tramite il comando DDL "alter table".

Problemi comuni di autovacuum

Esaminare l'elenco seguente dei 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 si sospende una volta raggiunto il limite massimo del costo. Il processo usa due parametri del server: autovacuum_vacuum_cost_delay e autovacuum_vacuum_cost_limit.

Per impostazione predefinita, autovacuum_vacuum_cost_limit è impostato su -1, il che significa che il limite di costo autovacuum usa lo stesso valore del vacuum_cost_limit parametro . Il valore predefinito per vacuum_cost_limit è 200. vacuum_cost_limit rappresenta il costo di un aspirapolvere manuale.

Se si imposta il parametro autovacuum_vacuum_cost_limit a -1, autovacuum usa il parametro vacuum_cost_limit. Se si imposta su autovacuum_vacuum_cost_limit un valore maggiore di -1, autovacuum usa il autovacuum_vacuum_cost_limit parametro .

Se autovacuum non sta tenendo il passo, si consiglia di modificare i seguenti parametri:

Parametro Descrizione
autovacuum_vacuum_cost_limit Impostazione predefinita: 200. È possibile aumentare il limite di costi. Monitorare l'utilizzo di CPU e I/O nel database prima e dopo aver apportato modifiche.
autovacuum_vacuum_cost_delay PostgreSQL versione 12 e successive - Impostazione predefinita: 2 ms. È possibile ridurre questo valore per rendere l'autovacuum più aggressivo.
vacuum_buffer_usage_limit PostgreSQL versioni 16 e successive : imposta le dimensioni del pool di buffer per le operazioni VACUUM e autovacuum. La regolazione di questo parametro consente di bilanciare le prestazioni di autovacuum con le prestazioni complessive del sistema controllando la quantità di cache del buffer condiviso usata durante le operazioni vacuum.

Nota

  • Il valore autovacuum_vacuum_cost_limit viene distribuito in modo proporzionale tra i ruoli di lavoro autovacuum in esecuzione. Se sono presenti più ruoli di lavoro, la somma dei limiti per ogni ruolo di lavoro non supera il valore del autovacuum_vacuum_cost_limit parametro.
  • autovacuum_vacuum_scale_factor è un altro parametro che potrebbe attivare il vacuum in una tabella in base all'accumulo di tuple inattive. 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

Se autovacuum viene eseguito in modo continuativo, può influire sull'utilizzo di CPU e I/O nel server. Ecco alcuni possibili motivi:

maintenance_work_mem

Il daemon autovacuum usa autovacuum_work_mem, che è impostato su -1 per impostazione predefinita. Questa impostazione predefinita indica che autovacuum_work_mem usa lo stesso valore del maintenance_work_mem parametro . Questo articolo presuppone che autovacuum_work_mem sia impostato su -1 e il daemon autovacuum usi maintenance_work_mem.

Se maintenance_work_mem è basso, è possibile aumentarlo fino a 2 GB in un'istanza del 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].

Se sono presenti più database in un cluster, aumentare autovacuum_naptime. Allo stesso tempo, rendi il processo autovacuum più aggressivo aumentando il parametro autovacuum_cost_limit e riducendo il parametro autovacuum_cost_delay. È anche possibile aumentare autovacuum_max_workers dal valore predefinito da 3 a 4 o 5.

Errori di memoria insufficiente

I valori eccessivamente aggressivi maintenance_work_mem possono periodicamente causare errori di memoria insufficiente nel sistema. Comprendere la RAM disponibile nel server prima di modificare il maintenance_work_mem parametro.

Autovacuum è troppo problematico

Se autovacuum utilizza troppe risorse, provare le azioni seguenti:

Parametri autovacuum

Valutare i parametri autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit e 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 si imposta un valore superiore al valore predefinito 200.
  • Ridurre il numero di autovacuum_max_workers se si imposta 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 usare un numero elevato di ruoli di lavoro autovacuum.

L'aumento del numero di ruoli di lavoro autovacuum comporta infatti un maggiore consumo di memoria. 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 si aumenta il numero di ruoli di lavoro, aumentare autovacuum_vacuum_cost_limit e/o diminuire autovacuum_vacuum_cost_delay per velocizzare il processo di vacuum.

Tuttavia, se si imposta il parametro al livello dei parametri della tabella autovacuum_vacuum_cost_delay o autovacuum_vacuum_cost_limit, i lavoratori che girano su quelle tabelle sono esentati dall'essere 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, viene visualizzato 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 è un 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 inattive non rimosse da autovacuum.

I possibili motivi per questo problema includono i motivi seguenti:

Carico di lavoro intenso

Un carico di lavoro intenso causa un numero eccessivo di tuple inattive in un breve periodo, rendendo 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 a esecuzione prolungata

Qualsiasi transazione a esecuzione prolungata nel sistema non consente ad autovacuum di rimuovere le tuple inattive. 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 ad autovacuum di rimuovere le tuple inattive. 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 PREPARED COMMIT o PREPARED ROLLBACK 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

È possibile impostare i parametri autovacuum per le singole tabelle. Queste impostazioni sono particolarmente importanti per le tabelle di piccole e grandi dimensioni. 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 si aggiorna spesso questa tabella, potrebbero essere visualizzate centinaia di operazioni autovacuum al giorno. Queste operazioni impediscono all'autovacuum di mantenere altre tabelle in cui la percentuale di modifiche non è altrettanto 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 le impostazioni autovacuum per ogni tabella, modificare i parametri del server come illustrato negli 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);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Carichi di lavoro di solo inserimento

In PostgreSQL versione 13 e precedenti, autovacuum non viene eseguito nelle tabelle con un carico di lavoro di solo inserimento, perché non sono presenti tuple inattive 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 di questo comportamento sono:

  • 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

PostgreSQL versioni 13 e precedenti

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

Per indicazioni, vedere considerazioni speciali sull'uso di pg_cron in Database di Azure per PostgreSQL.

PostgreSQL 13 e versioni successive

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

Guide alla risoluzione dei problemi

Azure Database per PostgreSQL con server flessibile fornisce guide di risoluzione problemi nel portale che consentono di monitorare il bloat a livello di database o di singolo schema e di identificare potenziali blocchi al processo "autovacuum".

Sono disponibili due guide alla risoluzione dei problemi:

  • Monitoraggio di autovacuum: usare questa guida per monitorare il software bloat a livello di database o di singolo schema.
  • Blocchi autovacuum e wraparound: questa guida consente di identificare i potenziali blocchi dei processi autovacuum e fornisce informazioni sulla distanza dei database nel server da situazioni di wraparound o di emergenza.

Le guide alla risoluzione dei problemi condividono anche raccomandazioni per attenuare i potenziali problemi. Per informazioni su come configurare e usare le guide alla risoluzione dei problemi, vedere guide alla risoluzione dei problemi di installazione.

Terminazione del processo autovacuum: ruolo pg_signal_autovacuum_worker

Autovacuum è un processo in background importante perché consente di gestire in modo efficiente l'archiviazione e le prestazioni nel database. In condizioni normali, il processo autovacuum si annulla automaticamente dopo deadlock_timeout. Se un utente esegue un'istruzione DDL in una tabella, l'utente potrebbe dover attendere fino all'intervallo deadlock_timeout . Autovacuum non consente l'esecuzione di letture o scritture nella tabella richiesta da richieste di connessione diverse, aggiungendo alla latenza nella transazione.

È stato introdotto un nuovo ruolo pg_signal_autovacuum_worker di PostgreSQL, che consente ai membri non utenti di terminare un'attività autovacuum in corso. Il nuovo ruolo consente agli utenti di ottenere l'accesso sicuro e controllato al processo autovacuum. Gli utenti senza privilegi avanzati possono annullare il processo autovacuum dopo che è stato assegnato loro il ruolo pg_signal_autovacuum_worker tramite il comando pg_terminate_backend. Il ruolo pg_signal_autovacuum_worker è disponibile in il Database di Azure per il PostgreSQL nelle versioni di PostgreSQL 15 e successive.

In rari scenari, come l'anti-wraparound autovacuum, i ruoli di lavoro possono essere riavviati immediatamente dopo la terminazione perché sono fondamentali per impedire l'esaurimento degli ID di transazione. Per ridurre al minimo i conflitti ripetuti, seguire questa procedura:

  • Accodare l'operazione DDL prima dell'interruzione:

    • Sessione 1: Preparare ed eseguire l'istruzione DDL.

    • Sessione 2: Terminare il processo di autovacuum.

      Importante

      Questi due passaggi devono essere eseguiti back-to-back. Se l'istruzione DDL rimane bloccata per troppo tempo, può contenere blocchi e bloccare altre operazioni DML nel server.

  • Terminare il processo autovacuum ed eseguire il DDL; se il DDL deve essere eseguito immediatamente:

    • Terminare il processo autovacuum usando pg_terminate_backend().
    • Eseguire l'istruzione DDL subito dopo la terminazione.

Passaggi per evitare conflitti ripetuti:

  1. Concedere il ruolo all'utente

    GRANT pg_signal_autovacuum_worker TO app_user;
    1. Identificare l'ID del processo autovacuum
    SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();

  2. Termina autovacuum

    SELECT pg_terminate_backend(<pid>);

  3. Eseguire immediatamente l'istruzione DDL

    ALTER TABLE my_table ADD COLUMN new_col TEXT;

Nota

Non è consigliabile terminare i processi autovacuum in corso perché questa operazione potrebbe portare a blob di tabelle e database, che possono portare a regressioni delle prestazioni. Tuttavia, nei casi in cui sia presente un requisito critico che implica l'esecuzione pianificata di un'istruzione DDL che coincide con il processo autovacuum, gli utenti che non dispongono di privilegi possono terminare il processo autovacuum in modo controllato e sicuro usando il ruolo pg_signal_autovacuum_worker.

Raccomandazioni di Azure Advisor

Le raccomandazioni di Azure Advisor identificano in modo proattivo se un server ha un rapporto di bloat elevato o se il server si avvicina allo scenario di wraparound delle transazioni. È anche possibile creare avvisi di Azure Advisor per le raccomandazioni.

Le raccomandazioni sono le seguenti:

  • Rapporto 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 con una conseguente 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.

  • Wraparound 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 il passaggio del server a uno stato di sola lettura. Di conseguenza, viene attivata una raccomandazione quando il server supera la soglia di 1 miliardo di transazioni.

Contenuti correlati

  • Last updated on