Autovacuumjustering i Azure Database for PostgreSQL – flexibel server
GÄLLER FÖR: 
Azure Database for PostgreSQL – flexibel server
Den här artikeln innehåller en översikt över funktionen autovacuum för flexibel Azure Database for PostgreSQL-server och de felsökningsguider för funktioner som är tillgängliga för att övervaka databasens uppsvällda, autovacuum-blockerare. Den innehåller också information om hur långt databasen är från en nödsituation eller omslutande situation.
Vad är autovacuum
Autovacuum är en PostgreSQL-bakgrundsprocess som automatiskt rensar upp döda tupplar och uppdaterar statistik. Det hjälper till att upprätthålla databasens prestanda genom att automatiskt köra två viktiga underhållsaktiviteter:
- VACUUM – Frigör diskutrymme genom att ta bort döda tupplar.
- ANALYSERA – Samlar in statistik som hjälper PostgreSQL Optimizer att välja de bästa körningsvägarna för frågor.
För att säkerställa att autovacuum fungerar korrekt bör serverparametern autovacuum alltid vara inställd på PÅ. När det är aktiverat bestämmer PostgreSQL automatiskt när vacuum eller ANALYZE ska köras på en tabell, vilket säkerställer att databasen förblir effektiv och optimerad.
Autovacuum internals
Autovacuum läser sidor som letar efter döda tupplar, och om ingen hittas, tar autovacuum bort sidan. När autovacuum hittar döda tupplar tar det bort dem. Kostnaden baseras på:
| Parameter | Description |
|---|---|
vacuum_cost_page_hit |
Kostnaden för att läsa en sida som redan finns i delade buffertar och inte behöver en diskläsning. Standardvärdet är inställt på 1. |
vacuum_cost_page_miss |
Kostnad för att hämta en sida som inte finns i delade buffertar. Standardvärdet är inställt på 10. |
vacuum_cost_page_dirty |
Kostnaden för att skriva till en sida när döda tupplar hittas i den. Standardvärdet är inställt på 20. |
Mängden arbete som autovacuum utför beror på två parametrar:
| Parameter | Description |
|---|---|
autovacuum_vacuum_cost_limit |
Mängden arbete autovacuum gör på en go. |
autovacuum_vacuum_cost_delay |
Antal millisekunder som autovacuum ligger i viloläge när den når den kostnadsgräns som anges av parametern autovacuum_vacuum_cost_limit . |
I alla versioner av Postgres som stöds för närvarande är standardvärdet för 200 (i själva verket inställt på -1, vilket gör det lika med värdet för autovacuum_vacuum_cost_limit det vanliga vacuum_cost_limit, som som standard är 200).
När det gäller autovacuum_vacuum_cost_delay, i Postgres version 11 är den som standard 20 millisekunder, medan den i Postgres version 12 och senare är som standard 2 millisekunder.
Autovacuum vaknar 50 gånger (50*20 ms=1 000 ms) varje sekund. Varje gång den vaknar läser autovacuum 200 sidor.
Det innebär att i en sekund autovacuum kan göra:
- ~80 MB/s [ (200 sidor/
vacuum_cost_page_hit) * 50 * 8 KB per sida] om alla sidor med döda tupplar finns i delade buffertar. - ~8 MB/s [ (200 sidor/
vacuum_cost_page_miss) * 50 * 8 KB per sida] om alla sidor med döda tupplar läses från disken. - ~4 MB/s [ (200 sidor/
vacuum_cost_page_dirty) * 50 * 8 KB per sida] autovacuum kan skriva upp till 4 MB/s.
Övervaka autovacuum
Använd följande frågor för att övervaka 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;
Följande kolumner hjälper dig att avgöra om autovacuum kommer ikapp tabellaktiviteten:
| Parameter | Description |
|---|---|
dead_pct |
Procentandel döda tupplar jämfört med levande tupplar. |
last_autovacuum |
Datumet för den senaste gången tabellen var autovacuumed. |
last_autoanalyze |
Datumet för den senaste gången tabellen analyserades automatiskt. |
När utlöser PostgreSQL autovacuum
En autovacuum-åtgärd (antingen ANALYZE eller VACUUM) utlöses när antalet döda tupplar överskrider ett visst tal som är beroende av två faktorer: det totala antalet rader i en tabell plus ett fast tröskelvärde. ANALYSERA utlöser som standard när 10 % av tabellen plus 50 radändringar, medan VACUUM utlöses när 20 % av tabellen plus 50 radändringar. Eftersom VAKUUM-tröskelvärdet är dubbelt så högt som ANALYZE-tröskelvärdet utlöses ANALYZE tidigare än VACUUM. För PG-versioner >=13; ANALYSERA som standard utlöses när 20 % av tabellen plus 1 000 radinfogningar.
De exakta ekvationerna för varje åtgärd är:
- Autoanalyze = autovacuum_analyze_scale_factor * tupplar + autovacuum_analyze_threshold eller autovacuum_vacuum_insert_scale_factor * tupplar + autovacuum_vacuum_insert_threshold (för PG-versioner >= 13)
- Autovacuum = autovacuum_vacuum_scale_factor * tupplar + autovacuum_vacuum_threshold
Om vi till exempel har en tabell med 100 rader. Följande ekvation ger sedan information om när analys- och vakuumutlösare:
För uppdateringar/borttagningar: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70
Analysera utlösare efter att 60 rader har ändrats i en tabell och Vakuumutlösare när 70 rader ändras i en tabell.
För infogningar: Autoanalyze = 0.2 * 100 + 1000 = 1020
Analysera utlösare efter att 1 020 rader har infogats i en tabell
Här är beskrivningen av de parametrar som används i ekvationen:
| Parameter | Description |
|---|---|
autovacuum_analyze_scale_factor |
Procentandel infogningar/uppdateringar/borttagningar som utlöser ANALYSERA i tabellen. |
autovacuum_analyze_threshold |
Anger det minsta antalet tupplar som infogats/uppdaterats/tagits bort för att ANALYSERA en tabell. |
autovacuum_vacuum_insert_scale_factor |
Procentandel infogningar som utlöser ANLYZE i tabellen. |
autovacuum_vacuum_insert_threshold |
Anger det minsta antalet tupplar som infogats i ANALYSERA en tabell. |
autovacuum_vacuum_scale_factor |
Procentandel uppdateringar/borttagningar som utlöser VACUUM i tabellen. |
Använd följande fråga för att lista tabellerna i en databas och identifiera de tabeller som är kvalificerade för autovacuum-processen:
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;
Kommentar
Frågan tar inte hänsyn till att autovacuum kan konfigureras per tabell med hjälp av DDL-kommandot "alter table".
Vanliga problem med autovacuum
Granska följande lista över möjliga vanliga problem med autovacuum-processen.
Inte hålla jämna steg med upptagen server
Autovacuum-processen beräknar kostnaden för varje I/O-åtgärd, ackumulerar en summa för varje åtgärd den utför och pausar när den övre gränsen för kostnaden har nåtts. autovacuum_vacuum_cost_delay och autovacuum_vacuum_cost_limit är de två serverparametrar som används i processen.
Som standard autovacuum_vacuum_cost_limit är värdet -1, vilket innebär att autovacuum-kostnadsgränsen är samma värde som parametern vacuum_cost_limit, som standard är 200. vacuum_cost_limit är kostnaden för ett manuellt vakuum.
Om autovacuum_vacuum_cost_limit är inställt på -1använder autovacuum parametern vacuum_cost_limit , men om autovacuum_vacuum_cost_limit den är inställd på större än -1 så autovacuum_vacuum_cost_limit beaktas parametern.
Om autovacuum inte håller jämna steg kan följande parametrar ändras:
| Parameter | Description |
|---|---|
autovacuum_vacuum_cost_limit |
Standard: 200. Kostnadsgränsen kan ökas. CPU- och I/O-användning i databasen bör övervakas före och efter ändringar. |
autovacuum_vacuum_cost_delay |
Postgres version 11 – Standard: 20 ms. Parametern kan minskas till 2-10 ms.Postgres version 12 och senare – Standard: 2 ms. |
Kommentar
- Värdet
autovacuum_vacuum_cost_limitfördelas proportionellt mellan de aktiva autovacuum-arbetarna, så om det finns fler än en överskrider summan av gränserna för varje arbetare inte värdet för parameternautovacuum_vacuum_cost_limit. autovacuum_vacuum_scale_factorär en annan parameter som kan utlösa vakuum på en tabell baserat på död tuppeln ackumulering. Standard:0.2, Tillåtet intervall:0.05 - 0.1. Skalningsfaktorn är arbetsbelastningsspecifik och bör anges beroende på mängden data i tabellerna. Innan du ändrar värdet undersöker du arbetsbelastningen och enskilda tabellvolymer.
Autovacuum körs hela tiden
Kontinuerlig körning av autovacuum kan påverka processor- och I/O-användning på servern. Här är några av de möjliga orsakerna:
maintenance_work_mem
Autovacuum daemon använder autovacuum_work_mem som standard inställt på -1 vilket innebär autovacuum_work_mem att skulle ha samma värde som parametern maintenance_work_mem. Det här dokumentet förutsätter autovacuum_work_mem att är inställt på -1 och maintenance_work_mem används av autovacuum daemon.
Om maintenance_work_mem den är låg kan den ökas till upp till 2 GB på en flexibel Azure Database for PostgreSQL-server. En allmän tumregel är att allokera 50 MB till maintenance_work_mem för varje 1 GB RAM-minne.
Stort antal databaser
Autovacuum försöker starta en arbetare på varje databas varje autovacuum_naptime sekund.
Om en server till exempel har 60 databaser och autovacuum_naptime är inställd på 60 sekunder, startar autovacuum-arbetaren varje sekund [autovacuum_naptime/Antal databaser].
Det är en bra idé att öka autovacuum_naptime om det finns fler databaser i ett kluster. Samtidigt kan autovacuum-processen göras mer aggressiv genom att öka autovacuum_cost_limit och minska parametrarna autovacuum_cost_delay och öka autovacuum_max_workers från standardvärdet 3 till 4 eller 5.
Minnesfel
Alltför aggressiva maintenance_work_mem värden kan regelbundet orsaka minnesfel i systemet. Det är viktigt att förstå tillgängligt RAM-minne på servern innan någon ändring av parametern maintenance_work_mem görs.
Autovacuum är för störande
Om autovacuum förbrukar mer resurser kan följande åtgärder utföras:
Autovacuum-parametrar
Utvärdera parametrarna autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_max_workers. Felaktig inställning av autovacuum-parametrar kan leda till scenarier där autovacuum blir för störande.
Om autovacuum är för störande bör du överväga följande åtgärder:
- Öka
autovacuum_vacuum_cost_delayoch minskaautovacuum_vacuum_cost_limitom värdet är högre än standardvärdet 200. - Minska antalet
autovacuum_max_workersom värdet är högre än standardvärdet 3.
För många autovacuum-arbetare
Att öka antalet autovacuumarbetare ökar inte vakuumhastigheten. Att ha ett stort antal autovacuum-arbetare rekommenderas inte.
Att öka antalet autovacuum-arbetare resulterar i mer minnesförbrukning, och beroende på värdet för maintenance_work_mem kan det orsaka prestandaförsämring.
Varje autovacuum-arbetsprocess får bara (1/autovacuum_max_workers) av det totala antalet autovacuum_cost_limit, så att ha ett stort antal arbetare gör att var och en går långsammare.
Om antalet arbetstagare ökar autovacuum_vacuum_cost_limit bör också ökas och/eller autovacuum_vacuum_cost_delay minskas för att göra vakuumprocessen snabbare.
Men om vi anger parametern på tabellnivå autovacuum_vacuum_cost_delay eller autovacuum_vacuum_cost_limit parametrar undantas de arbetare som körs på dessa tabeller från att beaktas i utjämningsalgoritmen [autovacuum_cost_limit/autovacuum_max_workers].
Autovacuum transaktions-ID (TXID) omslutningsskydd
När en databas stöter på skydd mot transaktions-ID:t kan ett felmeddelande som liknar följande fel observeras:
Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.
Kommentar
Det här felmeddelandet är ett långvarigt förbiseende. Vanligtvis behöver du inte växla till enanvändarläge. Istället kan du köra de nödvändiga VACUUM-kommandona och utföra justering för att VACUUM ska köras snabbt. Även om du inte kan köra något datamanipuleringsspråk (DML), kan du fortfarande köra VACUUM.
Omslutningsproblemet uppstår när databasen antingen inte dammsugs eller om det finns för många döda tupplar som inte tas bort av autovacuum. Orsaken till det här problemet kan vara:
Tung arbetsbelastning
Arbetsbelastningen kan orsaka för många döda tupplar under en kort period som gör det svårt för autovacuum att komma ikapp. De döda tupplar i systemet läggs ihop under en period vilket leder till försämrad frågeprestanda och leder till omslutningssituation. En orsak till att den här situationen uppstår kan vara att autovacuum-parametrar inte är tillräckligt inställda och inte håller jämna steg med en upptagen server.
Långvariga transaktioner
Alla långvariga transaktioner i systemet tillåter inte att döda tupplar tas bort medan autovacuum körs. De blockerar vakuumprocessen. Om du tar bort de långvariga transaktionerna frigörs döda tupplar för borttagning när autovacuum körs.
Långvariga transaktioner kan identifieras med hjälp av följande fråga:
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;
Förberedda instruktioner
Om det finns förberedda instruktioner som inte har begåtts skulle de förhindra att döda tupplar tas bort.
Följande fråga hjälper dig att hitta obekräftade förberedda instruktioner:
SELECT gid, prepared, owner, database, transaction
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
Använd COMMIT PREPARED eller ROLLBACK PREPARED för att checka in eller återställa dessa instruktioner.
Oanvända replikeringsplatser
Oanvända replikeringsfack förhindrar att autovacuum gör anspråk på döda tupplar. Följande fråga hjälper dig att identifiera oanvända replikeringsplatser:
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
Använd pg_drop_replication_slot() för att ta bort oanvända replikeringsplatser.
När databasen stöter på skydd mot transaktions-ID:t söker du efter eventuella blockerare som nämnts tidigare och tar bort blockerarna manuellt för att autovacuum ska fortsätta och slutföras. Du kan också öka hastigheten för autovacuum genom att ange autovacuum_cost_delay till 0 och öka autovacuum_cost_limit till ett värde större än 200. Ändringar av dessa parametrar gäller dock inte för befintliga autovacuum-arbetare. Starta antingen om databasen eller avsluta befintliga arbetare manuellt för att tillämpa parameterändringar.
Tabellspecifika krav
Autovacuum-parametrar kan anges för enskilda tabeller. Det är särskilt viktigt för små och stora tabeller. För en liten tabell som endast innehåller 100 rader utlöser autovacuum till exempel VACUUM-åtgärd när 70 rader ändras (beräknat tidigare). Om den här tabellen uppdateras ofta kan du se hundratals autovacuum-åtgärder per dag, vilket hindrar autovacuum från att underhålla andra tabeller där procentandelen ändringar inte är lika betydande. Alternativt måste en tabell som innehåller en miljard rader ändra 200 miljoner rader för att utlösa autovacuum-åtgärder. Om du ställer in autovacuum-parametrar på lämpligt sätt förhindras sådana scenarier.
Om du vill ange autovacuum-inställning per tabell ändrar du serverparametrarna som följande exempel:
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);
Infoga endast arbetsbelastningar
I versioner av PostgreSQL <= 13 körs autovacuum inte på tabeller med en infogningsarbetsbelastning, eftersom det inte finns några döda tupplar och inget ledigt utrymme som behöver frigöras. Autoanalys körs dock för endast infogningsarbetsbelastningar eftersom det finns nya data. Nackdelarna med detta är:
- Synlighetskartan för tabellerna uppdateras inte, och därför börjar frågeprestanda, särskilt där det finns endast indexgenomsökningar, att drabbas över tid.
- Databasen kan stöta på transaktions-ID:ts omslutande skydd.
- Tipsbitar har inte angetts.
Lösningar
Postgres-versioner <= 13
Med hjälp av tillägget pg_cron kan ett cron-jobb konfigureras för att schemalägga en periodisk vakuumanalys i tabellen. Frekvensen för cron-jobbet beror på arbetsbelastningen.
Stegvis vägledning med hjälp av pg_cron finns i Tillägg.
Postgres 13 och högre versioner
Autovacuum körs på tabeller med en infogningsbaserad arbetsbelastning. Två nya serverparametrar autovacuum_vacuum_insert_threshold och autovacuum_vacuum_insert_scale_factor hjälper till att styra när autovacuum kan utlösas i infogningstabeller.
Felsökningsguider
Med hjälp av funktionsfelsökningsguiderna som är tillgängliga på Azure Database for PostgreSQL–portalen för flexibel server är det möjligt att övervaka uppsvälldhet på databas- eller individuell schemanivå tillsammans med att identifiera potentiella blockerare till autovacuumprocess. Två felsökningsguider är tillgängliga först en är autovacuum övervakning som kan användas för att övervaka uppsvälldhet på databas- eller individuell schemanivå. Den andra felsökningsguiden är autovacuumblockerare och wraparound, som hjälper till att identifiera potentiella autovacuum-blockerare. Den innehåller också information om hur långt databaserna på servern är från omslutning eller nödsituation. Felsökningsguiderna delar även rekommendationer för att åtgärda potentiella problem. Så här konfigurerar du felsökningsguiderna för att använda dem genom att följa felsökningsguiderna för installation.
Azure Advisor-rekommendationer
Azure Advisor-rekommendationer är ett proaktivt sätt att identifiera om en server har ett högt uppsvälld förhållande eller om servern närmar sig ett scenario med transaktionsomslutning. Du kan också ange aviseringar för rekommendationerna med hjälp av Skapa Azure Advisor-aviseringar för nya rekommendationer med hjälp av Azure Portal
Rekommendationerna är:
Högt uppsvälld förhållande: Ett högt uppsvälld förhållande kan påverka serverns prestanda på flera sätt. Ett viktigt problem är att PostgreSQL Engine Optimizer kan ha svårt att välja den bästa körningsplanen, vilket leder till försämrade frågeprestanda. Därför utlöses en rekommendation när uppsvälldhetsprocenten på en server når ett visst tröskelvärde för att undvika sådana prestandaproblem.
Transaktionsomslutning: Det här scenariot är ett av de allvarligaste problem som en server kan stöta på. När servern är i det här tillståndet kan den sluta acceptera fler transaktioner, vilket gör att servern blir skrivskyddad. Därför utlöses en rekommendation när vi ser att servern har överskridit tröskelvärdet på 1 miljard transaktioner.