Ottimizzazione automatica in Database di Azure per PostgreSQL - Server flessibile

  • Articolo

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

Questo articolo offre una panoramica della funzionalità autovacuum per Database di Azure per PostgreSQL server flessibile e le guide alla risoluzione dei problemi delle funzionalità disponibili per monitorare il blob del database, i blocchi autovacuum e anche le informazioni relative alla distanza del database dalla situazione di emergenza o di wrapping.

Che cos'è autovacuum

La coerenza interna dei dati in PostgreSQL si basa sul meccanismo MVCC (Multi-Version Concurrency Control), che consente al motore di database di mantenere più versioni di una riga e offre maggiore concorrenza con un blocco minimo tra i diversi processi.

I database PostgreSQL richiedono una manutenzione appropriata. Ad esempio, quando una riga viene eliminata, non viene rimossa fisicamente. La riga viene invece contrassegnata come "dead". Analogamente per gli aggiornamenti, la riga viene contrassegnata come "morta" e viene inserita una nuova versione della riga. Queste operazioni lasciano i record non recapitabili, denominati tuple non recapitabili, anche dopo il completamento di tutte le transazioni che potrebbero visualizzare tali versioni. A meno che non vengano ripulite, le tuple inattivo rimangono, consumando spazio su disco e tabelle e indici che comportano un rallentamento delle prestazioni delle query.

PostgreSQL usa un processo denominato autovacuum per pulire automaticamente le tuple inattiche.

Internals autovacuum

Autovacuum legge le pagine che cercano tuple inattivi e, se non viene trovato alcuno, autovacuum rimuove la pagina. Quando autovacuum trova tuple morte, le rimuove. Il costo è basato su:

  • 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 è 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 vengono trovate tuple inattiche. Il valore predefinito è impostato su 20.

La quantità di lavoro autovacuum dipende da due parametri:

  • autovacuum_vacuum_cost_limit è la quantità di lavoro che l'autovacuum esegue in un'unica operazione.
  • autovacuum_vacuum_cost_delay numero di millisecondi in cui autovacuum è in stato di sospensione dopo aver raggiunto il limite di costo specificato dal autovacuum_vacuum_cost_limit parametro .

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 sveglia, 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 attive 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 attive 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:

  • dead_pct: percentuale di tuple inattivi 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. ANALIZZA, per impostazione predefinita, si attiva 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 VACUUM è pari al doppio della soglia ANALYZE, ANALYZE viene attivata prima di VACUUM.

Le equazioni esatte per ogni azione sono:

  • Autoanalyze = autovacuum_analyze_scale_factor * tuple + autovacuum_analyze_threshold
  • Autovacuum = autovacuum_vacuum_scale_factor * tuple + autovacuum_vacuum_threshold

Ad esempio, analizzare i trigger dopo la modifica di 60 righe in una tabella contenente 100 righe e trigger vacuum quando 70 righe cambiano nella tabella, usando le equazioni seguenti:

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

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_analyze_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 vuoto manuale.

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

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

Parametro Descrizione
autovacuum_vacuum_scale_factor Impostazione predefinita: 0.2, intervallo: 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_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 autovacuum_vacuum_cost_limit valore 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 autovacuum_vacuum_cost_limit parametro

Autovacuum in esecuzione costante

L'esecuzione continua di autovacuum potrebbe influire sull'utilizzo di CPU e I/O nel server. I motivi seguenti potrebbero essere i seguenti:

maintenance_work_mem

Il daemon Autovacuum usa autovacuum_work_mem che è impostato per impostazione predefinita su un valore autovacuum_work_mem uguale al -1 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 in Database di Azure per PostgreSQL server flessibile. Una regola generale 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 secondo.

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 e autovacuum_cost_limit riducendo i autovacuum_cost_delay parametri e aumentando l'oggetto autovacuum_max_workers dal valore predefinito da 3 a 4 o 5.

Errori di memoria insufficiente

I valori eccessivamente aggressivi maintenance_work_mem potrebbero causare periodicamente errori di memoria insufficiente nel sistema. È importante comprendere la RAM disponibile nel server prima che venga apportata una modifica al maintenance_work_mem parametro.

Autovacuum è troppo problematico

Se autovacuum utilizza molte risorse, è possibile eseguire le operazioni seguenti:

Parametri autovacuum

Valutare i autovacuum_vacuum_cost_delayparametri , 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, considerare quanto segue:

  • 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 lavoratori autovacuum

L'aumento del numero di lavoratori autovacuum non aumenterà necessariamente la velocità del vuoto. Non è consigliabile disporre di un numero elevato di ruoli di lavoro autovacuum.

L'aumento del numero di ruoli di lavoro autovacuum comporterà un maggiore consumo di memoria 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 lavoratori è aumentato, autovacuum_vacuum_cost_limit dovrebbe anche essere aumentato e/o autovacuum_vacuum_cost_delay dovrebbe essere ridotto per velocizzare il processo di vuoto.

Tuttavia, se sono stati modificati il livello autovacuum_vacuum_cost_delay di tabella o autovacuum_vacuum_cost_limit i parametri, 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 qualsiasi linguaggio di manipolazione dei dati (DML), è comunque possibile eseguire VACUUM.

Il problema di wrapping si verifica quando il database non è vuoto o ci sono troppe tuple morte che non possono essere 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 morte in un breve periodo che rende difficile l'aggiornamento automatico. Le tuple inattiche 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

Tutte le transazioni a esecuzione prolungata nel sistema non consentono la rimozione delle tuple non attive durante l'esecuzione dell'autovacuum. Sono un blocco per il processo di vuoto. La rimozione delle transazioni a esecuzione prolungata libera le tuple non attive 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 morte.
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 a autovacuum di richiedere tuple inattiche. 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 rimuoverli manualmente per il completamento e il completamento dell'autovacuum. È anche possibile aumentare la velocità di autovacuum impostando autovacuum_cost_delay su 0 e aumentando il autovacuum_cost_limit valore su un valore maggiore di 200. Tuttavia, le modifiche apportate a questi parametri non verranno applicate 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. È particolarmente importante per le 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, potrebbero essere visualizzate centinaia di operazioni autovacuum al giorno. In questo modo si impedisce la gestione automatica di altre tabelle in cui la percentuale di modifiche non è così grande. 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 sola inserimento

Nelle versioni di PostgreSQL precedenti a 13, autovacuum non verrà eseguito nelle tabelle con un carico di lavoro di sola inserimento, perché se non sono presenti aggiornamenti o eliminazioni, non sono presenti tuple inattivo e non è necessario recuperare spazio disponibile. Tuttavia, l'analisi automatica verrà eseguita per i carichi di lavoro di sola inserimento perché sono presenti nuovi dati. Gli svantaggi di questo 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ò essere eseguito nella protezione wraparound dell'ID transazione.
  • I bit di hint non verranno impostati.

Soluzioni

Versioni di Postgres precedenti alla 13

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 dettagliate sull'uso di pg_cron, vedere Estensioni.

Postgres 13 e versioni successive

Autovacuum verrà eseguito su tabelle con un carico di lavoro di sola inserimento. Due nuovi parametri del server e autovacuum_vacuum_insert_scale_factor consentono di autovacuum_vacuum_insert_threshold controllare 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 Database di Azure per PostgreSQL è possibile monitorare il blob a livello di database o di singolo schema insieme all'identificazione di potenziali blocchi al processo di autovacuum. Due guide alla risoluzione dei problemi sono disponibili per prima cosa è 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, che consente di identificare i potenziali blocchi autovacuum insieme alle informazioni sulla distanza dei database nel server dalla situazione di wrapping 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, seguire le guide alla risoluzione dei problemi di installazione.

Contenuto correlato