Condividi tramite

Ottimizzazione di autovacuum nel server flessibile di Database di Azure per PostgreSQL

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

Questo articolo offre una panoramica della funzionalità autovacuum per il server flessibile di Database di Azure per PostgreSQL, nonché delle guide alla risoluzione dei problemi relativi alle funzionalità disponibili per monitorare i blocchi bloat e autovacuum del database. 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 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 da autovacuum 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 di Postgres attualmente supportate il valore predefinito per autovacuum_vacuum_cost_limit è 200 (in realtà, impostato su -1, cosa che lo rende uguale al valore 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 successive 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

Server flessibile di Database di Azure per PostgreSQL fornisce le metriche seguenti per il monitoraggio 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 si sta aggiornando sull'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 che dipende da due fattori: il numero totale di righe in una tabella e 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 * tuples + autovacuum_analyze_threshold or autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (per le versioni PG >= 13)
  • Autovacuum = autovacuum_vacuum_scale_factor * tuples + 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 utilizzati nell'equazione:

Parametro Descrizione
autovacuum_analyze_scale_factor Percentuale di inserimenti/aggiornamenti/eliminazioni che attiva ANALYZE 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.

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 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 viene sospeso appena viene 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 di autovacuum equivale al 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 utilizza il parametro vacuum_cost_limit, ma se autovacuum_vacuum_cost_limit stesso è impostato su un valore maggiore di -1, viene preso in considerazione il parametro autovacuum_vacuum_cost_limit.

Se autovacuum non si aggiorna, è possibile modificare i parametri seguenti:

Parametro Descrizione
autovacuum_vacuum_cost_limit Impostazione predefinita: 200. Il limite di costo potrebbe essere più alto. L'utilizzo della CPU e di 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 ne sono presenti più di uno, la somma dei limiti per ogni ruolo di lavoro non superi 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 rispetto al 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 che venga 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 e nel caso siano presenti troppe tuple inattive non rimosse da autovacuum.

I possibili motivi per questo problema potrebbero essere uno dei seguenti:

Carico di lavoro intenso

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 a esecuzione prolungata

Qualsiasi transazione a esecuzione prolungata nel sistema non consente la rimozione delle tuple inattive 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, vedere Considerazioni speciali sull'uso di pg_cron nel server flessibile di Database di Azure per PostgreSQL.

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 auteovacuum nelle tabelle di solo inserimento.

Guide alla risoluzione dei problemi

Le guide alla risoluzione dei problemi di funzionalità disponibili nel portale del server flessibile di Database di Azure per PostgreSQL permettono di monitorare il bloat a livello di database o di singolo schema, nonché di identificare potenziali blocchi nel 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 utilizzarle, vedere Installare le guide alla risoluzione dei problemi.

Terminazione del processo autovacuum: ruolo pg_signal_autovacuum_worker

Autovacuum è un processo in background molto 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, un utente potrebbe dover attendere fino all'intervallo deadlock_timeout . Autovacuum non consente l'esecuzione di letture/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 senza privilegi avanzati 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 tramite il comando pg_signal_autovacuum_workerpg_terminate_backend. Il ruolo pg_signal_autovacuum_worker funge da backport nel server flessibile di Database di Azure per PostgreSQL, nelle versioni di PostgreSQL 15 e successive.

Nota

Non è consigliabile interrompere alcun processo autovacuum in corso perché la terminazione di un processo autovacuum potrebbe portare a gonfiore di tabelle e database, che può a sua volta causare regressioni delle prestazioni. Tuttavia, nei casi in cui esiste un requisito business critical che implica l'esecuzione pianificata di un'istruzione DDL che coincide con il processo autovacuum, è possibile consentire ai non utenti con privilegi avanzati di terminare l'autovacuum in modo controllato e sicuro usando pg_signal_autovacuum_worker role.

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 creare avvisi di Azure Advisor per le raccomandazioni.

Le raccomandazioni sono le 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 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.

  • Scorrimento automatico delle transazioni: questo scenario è uno dei problemi più gravi che possono verificarsi in un server. Quando il server è in questo stato, potrebbe smettere di accettare altre transazioni, rendendo il server di sola lettura. Di conseguenza, viene attivata una raccomandazione quando il server supera la soglia di 1 miliardo di transazioni.

Contenuti correlati