Autovacuumoptimering i Azure Database för PostgreSQL

Den här artikeln ger en översikt över autovacuum-funktionen för Azure Database for PostgreSQL och de felsökningsguider som är tillgängliga för att övervaka databasen angående uppblåsthet och 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 utrymme i databasens filer genom att ta bort döda tupplar och markera det utrymmet som återanvändbart av PostgreSQL; Det minskar inte nödvändigtvis den fysiska storleken på databasfilerna på disken. Om du vill returnera utrymme till operativsystemet använder du åtgärder som skriver om tabellen (till exempel VACUUM FULL eller pg_repack), som har ytterligare överväganden, till exempel exklusiva lås eller underhållsfönster.
• ANALYSERA – Samlar in tabell- och indexstatistik som används av PostgreSQL frågeplanerare för att välja effektiva körningsplaner.

För att säkerställa att autovacuum fungerar korrekt bör serverparametern autovacuum alltid vara inställd på ON. 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.

Automatisk dammsugarens inre funktioner

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å:

Mängden arbete som autovacuum utför beror på två parametrar:

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

Azure Database for PostgreSQL innehåller följande mått för övervakning av autovacuum.

Autovacuum-mått kan användas för att övervaka och finjustera autovacuums prestanda för Azure Database for PostgreSQL – flexibel server. Varje mått genereras med ett intervall på 30 minuter och har upp till 93 dagars kvarhållning. Du kan skapa aviseringar för specifika mått och du kan dela upp och filtrera måttdata med hjälp av dimensionen DatabaseName .

Så här aktiverar du autovacuum-metrik

• Autovacuum-mått är inaktiverade som standard.
• Om du vill aktivera dessa mått anger du serverparametern metrics.autovacuum_diagnostics till ON.
• Den här parametern är dynamisk, så det krävs ingen omstart av instansen.

Lista över autovacuum-mått

Faktorer att tänka på vid användning av autovacuum-mått

• Autovacuum-mått som använder dimensionen DatabaseName har en gräns på 30 databaser .
• På SKU:n Burstable är gränsen 10 databaser för mått som använder Dimension DatabaseName.
• Dimensionsgränsen för DatabaseName tillämpas på OID-kolumnen, vilket återspeglar ordningen för att skapa databasen.

Mer information finns i Autovacuum Metrics.

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:

Utlösa 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 = 60Autovacuum = 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:

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;

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:

Autovacuum körs hela tiden

Kontinuerlig körning av autovacuum kan påverka CPU och IO 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 är låg kan den ökas till upp till 2 GB på en flexibel serverinstans av Azure Database for PostgreSQL. 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_delay och minska autovacuum_vacuum_cost_limit om värdet är högre än standardvärdet 200.
• Minska antalet autovacuum_max_workers om 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.

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.

Möjliga orsaker till det här problemet kan vara något av följande:

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 föra igenom eller rulla tillbaka 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 i 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.

Mer information finns i särskilda överväganden om hur du använder pg_cron i Azure Database for PostgreSQL.

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 de funktionsfelsökningsguider som är tillgängliga på Azure Database for PostgreSQL flexibel serverinstansportal ä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.

Avsluta autovacuumprocess – pg_signal_autovacuum_worker roll

Autovacuum är en viktig bakgrundsprocess eftersom den hjälper till med effektivt lagrings- och prestandaunderhåll i databasen. I den normala autovacuum-processen avbryter den sig själv efter deadlock_timeout. Om en användare kör en DDL-instruktion på en tabell kan en användare behöva vänta under intervallet deadlock_timeout. Autovacuum tillåter inte utförande av läs- och skrivoperationer på tabellen som begärs av olika anslutningsförfrågningar, vilket ökar fördröjningen i transaktionen.

Vi introducerade en ny roll pg_signal_autovacuum_worker från PostgreSQL, som gör det möjligt för icke-användare att avsluta en pågående autovacuum-uppgift. Den nya rollen hjälper användarna att få säker och kontrollerad åtkomst till autovacuum-processen. Nonsuper-användare kan avbryta autovacuum-processen när de har fått rollen som pg_signal_autovacuum_worker med hjälp av pg_terminate_backend kommandot. Rollen pg_signal_autovacuum_worker backporteras till Azure Database for PostgreSQL i PostgreSQL version 15 och senare.

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å skapa Azure Advisor-aviseringar för rekommendationerna.

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 överskrider tröskelvärdet för 1 miljard transaktioner.

Relaterat innehåll

• Fullständigt vakuum med pg_repack i Azure Database for PostgreSQL
• Felsöka hög CPU-användning i Azure Database for PostgreSQL
• Felsöka hög minnesanvändning i Azure Database for PostgreSQL
• Felsöka hög IOPS-användning i Azure Database for PostgreSQL
• Felsöka och identifiera långsamma frågor i Azure Database for PostgreSQL
• Serverparametrar i Azure Database for PostgreSQL