Eventi
Ottieni gratuitamente la certificazione in Microsoft Fabric.
19 nov, 23 - 10 dic, 23
Per un periodo di tempo limitato, il team della community di Microsoft Fabric offre buoni per esami DP-600 gratuiti.
Prepara oraQuesto browser non è più supportato.
Esegui l'aggiornamento a Microsoft Edge per sfruttare i vantaggi di funzionalità più recenti, aggiornamenti della sicurezza e supporto tecnico.
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.
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:
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 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:
vacuum_cost_page_hit
) * 50 * 8 kB per pagina] se tutte le pagine con tuple non recapitabili vengono trovate nei buffer condivisi.vacuum_cost_page_miss
) * 50 * 8 kB per pagina] se tutte le pagine con tuple non recapitabili vengono lette dal disco.vacuum_cost_page_dirty
) * 50 * 8 kB per pagina] autovacuum può scrivere fino a 4 MB/sec.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. |
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:
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".
Esaminare l'elenco seguente dei possibili problemi comuni relativi al processo autovacuum.
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
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.L'esecuzione continua di autovacuum potrebbe influire sull'utilizzo di CPU e I/O nel server. Ecco alcuni dei possibili motivi:
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.
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.
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
.
Se autovacuum usa più risorse, è possibile eseguire le azioni seguenti:
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:
autovacuum_vacuum_cost_delay
e ridurre autovacuum_vacuum_cost_limit
se impostato su un valore superiore al valore predefinito 200.autovacuum_max_workers
se impostato su un valore superiore al valore predefinito 3.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].
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:
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.
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;
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.
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.
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);
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:
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.
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.
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.
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.
Eventi
Ottieni gratuitamente la certificazione in Microsoft Fabric.
19 nov, 23 - 10 dic, 23
Per un periodo di tempo limitato, il team della community di Microsoft Fabric offre buoni per esami DP-600 gratuiti.
Prepara ora