Delen via

Automatisch afstemmen in Azure Database for PostgreSQL

Dit artikel bevat een overzicht van de functie autovacuum voor Azure Database for PostgreSQL en de handleidingen voor het oplossen van problemen met functies die beschikbaar zijn om de databasebloat- en autovacuumblokkeringen te bewaken. Het biedt ook informatie over hoe ver de database zich bevindt van een nood- of terugloopsituatie.

Opmerking

In dit artikel wordt de autovacuumafstemming behandeld voor alle ondersteunde PostgreSQL-versies in Azure Database for PostgreSQL Flexible Server. Sommige functies die worden vermeld, zijn versiespecifiek (zoals vacuum_buffer_usage_limit voor PostgreSQL 16 en hoger, en autovacuum_vacuum_max_threshold voor PostgreSQL 18 en hoger).

Wat is autovacuum?

Autovacuum is een PostgreSQL-achtergrondproces waarmee dode tuples automatisch worden opgeschoond en statistieken worden bijgewerkt. Hiermee kunt u de databaseprestaties behouden door automatisch twee belangrijke onderhoudstaken uit te voeren:

  • VACUUM: maakt ruimte vrij binnen de bestanden van de database door dode tuples te verwijderen en die ruimte te markeren als herbruikbaar door PostgreSQL. Het vermindert niet noodzakelijkerwijs de fysieke grootte van de databasebestanden op schijf. Als u ruimte wilt retourneren aan het besturingssysteem, gebruikt u bewerkingen die de tabel herschrijven (bijvoorbeeld VACUUM FULL of pg_repack), die aanvullende overwegingen hebben, zoals exclusieve vergrendelingen of onderhoudsvensters.
  • ANALYZE : verzamelt tabel- en indexstatistieken die de PostgreSQL-queryplanner gebruikt om efficiënte uitvoeringsplannen te kiezen.

Om ervoor te zorgen dat autovacuum correct werkt, stelt u de parameter autovacuum-server in op ON. Wanneer deze functie is ingeschakeld, bepaalt PostgreSQL automatisch wanneer u VACUUM of ANALYZE wilt uitvoeren op een tabel, zodat de database efficiënt en geoptimaliseerd blijft.

De interne werking van autovacuum

Autovacuum leest pagina's om dode tuples te vinden. Als er geen dode tuples worden gevonden, wordt de pagina verwijderd door Autovacuum. Wanneer autovacuum dode tuples vindt, worden ze verwijderd. De kosten zijn gebaseerd op de volgende parameters:

Kenmerk Description
vacuum_cost_page_hit Kosten voor het lezen van een pagina die zich al in gedeelde buffers bevindt en waartoe geen schijftoegang nodig is. De standaardwaarde is 1.
vacuum_cost_page_miss Kosten voor het ophalen van een pagina die zich niet in gedeelde buffers bevindt. De standaardwaarde is 10.
vacuum_cost_page_dirty De kosten van het schrijven naar een pagina wanneer er dode tuples in worden gevonden. De standaardwaarde is 20.

De hoeveelheid werk die autovacuum uitvoert, is afhankelijk van twee parameters:

Kenmerk Description
autovacuum_vacuum_cost_limit De hoeveelheid werk die autovacuum in één gebruik doet.
autovacuum_vacuum_cost_delay Het aantal milliseconden dat autovacuum in slaap staat nadat de kostenlimiet is bereikt die is opgegeven door de autovacuum_vacuum_cost_limit parameter.

In alle momenteel ondersteunde versies van PostgreSQL is de standaardwaarde autovacuum_vacuum_cost_limit 200 (in feite ingesteld op -1, waardoor deze gelijk is aan de waarde van de normale vacuum_cost_limitwaarde, die standaard 200 is).

De standaardwaarde autovacuum_vacuum_cost_delay is 2 milliseconden in PostgreSQL-versies 12 en hoger (het was 20 milliseconden in versie 11).

Buffergebruikslimiet (PostgreSQL 16+)

Vanaf PostgreSQL versie 16 kunt u de vacuum_buffer_usage_limit parameter gebruiken om het geheugengebruik te beheren tijdens VACUUM-, ANALYZE- en autovacuum-bewerkingen.

Kenmerk Description
vacuum_buffer_usage_limit Hiermee stelt u de grootte van de buffergroep in voor VACUUM-, ANALYZE- en autovacuum-bewerkingen. Deze parameter beperkt de hoeveelheid gedeelde buffercache die door deze bewerkingen kan worden gebruikt, waardoor ze geen overmatig geheugenresources kunnen gebruiken.

Deze parameter helpt te voorkomen dat VACUUM en autovacuum te veel nuttige pagina's uit gedeelde buffers verwijderen, waardoor de algehele databaseprestaties tijdens onderhoudsbewerkingen kunnen worden verbeterd. De standaardwaarde wordt doorgaans ingesteld op shared_buffersbasis van en u kunt deze zo configureren dat de vacuümprestaties worden afgestemd op de behoeften van reguliere databasebewerkingen.

Maximumdrempel voor autovacuum (PostgreSQL 18+)

Vanaf PostgreSQL versie 18 kunt u de autovacuum_vacuum_max_threshold parameter gebruiken om een bovengrens in te stellen voor het aantal tuple-updates of verwijderingen waarmee autovacuum wordt geactiveerd.

Kenmerk Description
autovacuum_vacuum_max_threshold Stelt een maximum aantal tuple-updates of -verwijderingen in voordat u vacuum uitvoert. Als deze waarde is ingesteld -1, wordt de maximumdrempel uitgeschakeld. Gebruik deze parameter voor fijnafgestelde controle van autovacuum-triggering op zeer grote tabellen.

Deze parameter is met name handig voor grote tabellen waarbij de standaard op schaal gebaseerde triggering ertoe kan leiden dat autovacuum te lang wacht voordat wordt uitgevoerd.

Autovacuum wordt elke seconde 50 keer wakker (50*20 ms=1000 ms). Telkens wanneer het wakker wordt, leest autovacuum 200 pagina's.

Dat betekent dat in één seconde autovacuum het volgende kan doen:

  • ~80 MB per seconde [ (200 pagina's/vacuum_cost_page_hit) * 50 * 8 kB per pagina] als alle pagina's met dode tuples worden gevonden in gedeelde buffers.
  • ~8 MB per seconde [ (200 pagina's/vacuum_cost_page_miss) * 50 * 8 kB per pagina] als alle pagina's met dode tuples van schijf worden gelezen.
  • ~4 MB per seconde [ (200 pagina's/vacuum_cost_page_dirty) * 50 * 8 kB per pagina] autovacuum kan maximaal 4 MB per seconde schrijven.

Autovacuum bewaken

Azure Database for PostgreSQL biedt de volgende metrische gegevens voor het bewaken van autovacuum.

Metrische gegevens van Autovacuum kunnen worden gebruikt om de prestaties van autovacuum voor flexibele Azure Database for PostgreSQL-server te bewaken en af te stemmen. Elke metriek wordt verzonden met een interval van 30 minuten en heeft maximaal 93 dagen retentie. U kunt waarschuwingen maken voor specifieke metrische gegevens en u kunt metrische gegevens splitsen en filteren met behulp van de DatabaseName dimensie.

Hoe autovacuum-metingen inschakelen

  • Autovacuum-metrische gegevens zijn standaard uitgeschakeld.
  • Als u deze metrische gegevens wilt inschakelen, stelt u de serverparameter metrics.autovacuum_diagnostics in op ON.
  • Deze parameter is dynamisch, dus het opnieuw opstarten van het exemplaar is niet vereist.

Lijst met metrische gegevens van autovacuum

Weergavenaam Metrische ID Unit Description Dimensie Standaard ingeschakeld
Tellergebruikerstabellen analyseren analyze_count_user_tables Aantal Aantal keren dat tabellen met alleen gebruikers handmatig zijn geanalyseerd in deze database. DatabaseName Nee.
Gebruikerstabellen van AutoAnalyze-teller autoanalyze_count_user_tables Aantal Aantal keren dat tabellen met alleen gebruikers zijn geanalyseerd door de autovacuum-daemon in deze database. DatabaseName Nee.
Gebruikerstabellen van AutoVacuum-teller autovacuum_count_user_tables Aantal Aantal keren dat tabellen die alleen door de gebruiker zijn gebruikt, zijn opgezogen door de autovacuum-daemon in deze database. DatabaseName Nee.
Opblaaspercentage (Preview) bloat_percent Procent Het geschatte bloatpercentage voor alleen gebruikerstabellen. DatabaseName Nee.
Geschatte gebruikerstabellen voor dode rijen n_dead_tup_user_tables Aantal Het geschatte aantal dode rijen voor tabellen die alleen voor gebruikers in deze database worden gebruikt. DatabaseName Nee.
Geschatte live-rijen gebruikerstabellen n_live_tup_user_tables Aantal Geschatte hoeveelheid liverijen voor tabellen die alleen voor gebruikers in deze database worden gebruikt. DatabaseName Nee.
Gebruikerstabellen geschatte wijzigingen n_mod_since_analyze_user_tables Aantal Het geschatte aantal rijen dat is gewijzigd sinds alleen-gebruikerstabellen voor het laatst zijn geanalyseerd. DatabaseName Nee.
Geanalyseerde gebruikerstabellen tables_analyzed_user_tables Aantal Aantal tabellen die alleen voor gebruikers zijn geanalyseerd in deze database. DatabaseName Nee.
Gebruikerstabellen automatisch geanalyseerd tables_autoanalyzed_user_tables Aantal Het aantal tabellen met alleen gebruikers dat is geanalyseerd door de autovacuum-daemon in deze database. DatabaseName Nee.
Gebruikerstabellen AutoVacuumed tables_autovacuumed_user_tables Aantal Aantal tabellen die alleen door de gebruiker zijn leeggemaakt door de autovacuum-daemon in deze database. DatabaseName Nee.
Gebruikerstabelteller tables_counter_user_tables Aantal Aantal tabellen met alleen gebruikers in deze database. DatabaseName Nee.
Gebruikerstabellen leeggezogen tables_vacuumed_user_tables Aantal Het aantal tabellen met alleen gebruikers dat in deze database is leeggemaakt. DatabaseName Nee.
Vacuümteller-gebruikerstabellen vacuum_count_user_tables Aantal Aantal keren dat tabellen met alleen gebruikers handmatig zijn leeggemaakt in deze database (tellen niet).VACUUM FULL DatabaseName Nee.

Overwegingen voor het gebruik van metrische gegevens van Autovacuum

  • Metrische gegevens van Autovacuum die gebruikmaken van de dimensie DatabaseName, hebben een limiet van 30 databases .
  • In de Burstable SKU is de limiet 10 databases voor metrische gegevens die gebruikmaken van de dimensie DatabaseName.
  • De dimensielimiet voor DatabaseName wordt toegepast op de kolom OID, die de volgorde van het maken van de database weerspiegelt.

Zie Autovacuum Metrics voor meer informatie.

Gebruik de volgende query's om autovacuum te bewaken:

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;

Met de volgende kolommen kunt u bepalen of autovacuum de tabelactiviteit bijhoudt.

Kenmerk Description
dead_pct Percentage dode tuples in vergelijking met levende tuples.
last_autovacuum De datum van de laatste keer dat de tabel automatisch werd afgevvacueerd.
last_autoanalyze De datum van de laatste keer dat de tabel automatisch is geanalyseerd.

Automatischvacuum activeren

Een autovacuum-actie (ANALYZE of VACUUM) activeert wanneer het aantal dode tuples een bepaald aantal overschrijdt. Dit getal is afhankelijk van twee factoren: het totale aantal rijen in een tabel, plus een vaste drempelwaarde. ANALYZE-triggers worden standaard geactiveerd wanneer 10% van de tabel plus 50 rijwijzigingen optreden, terwijl VACUUM wordt geactiveerd wanneer 20% van de tabel plus 50 rijwijzigingen optreden. Omdat de DREMPELWAARDE VACUUM twee keer zo hoog is als de DREMPELWAARDE ANALYSEREN , activeert ANALYZE eerder dan VACUUM.

Voor PostgreSQL-versies 13 en later wordt ANALYZE standaard geactiveerd wanneer 20% van de tabel plus 1000 rijen worden ingevoegd.

De exacte vergelijkingen voor elke actie zijn:

  • Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold ofwel autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (Voor PostgreSQL versies 13 en hoger)
  • Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Als u bijvoorbeeld een tabel met 100 rijen hebt, worden de volgende vergelijkingen weergegeven wanneer de acties analyseren en vacuüm activeren:

Voor updates en verwijderingen: Autoanalyze = 0.1 * 100 + 50 = 60Autovacuum = 0.2 * 100 + 50 = 70

ANALYSEERtriggers na 60 rijen worden gewijzigd in een tabel en VACUUM-triggers wanneer er 70 rijen in een tabel worden gewijzigd.

Voor invoegingen: Autoanalyze = 0.2 * 100 + 1000 = 1020

ANALYSE-triggers na het invoegen van 1.020 rijen in een tabel.

Hier volgt de beschrijving van de parameters die in de vergelijking worden gebruikt:

Kenmerk Description
autovacuum_analyze_scale_factor Percentage invoegingen, updates en verwijderingen waarmee ANALYSEREN in de tabel wordt geactiveerd.
autovacuum_analyze_threshold Minimaal aantal tuples dat is ingevoegd, bijgewerkt of verwijderd om een tabel te analyseren .
autovacuum_vacuum_insert_scale_factor Percentage invoegingen waarmee ANALYZE in de tabel wordt geactiveerd.
autovacuum_vacuum_insert_threshold Het minimum aantal tuples dat is ingevoegd voor het analyseren van een tabel.
autovacuum_vacuum_scale_factor Percentage updates en verwijderingen waarmee VACUUM op de tabel wordt geactiveerd.

Gebruik de volgende query om de tabellen in een database weer te geven en de tabellen te identificeren die in aanmerking komen voor het autovacuum-proces:

 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;

Opmerking

De query houdt geen rekening met het feit dat u autovacuum per tabel kunt configureren met behulp van de DDL-opdracht 'tabel wijzigen'.

Veelvoorkomende problemen met autovacuum

Bekijk de volgende lijst met veelvoorkomende problemen met het autovacuum-proces.

Niet bijhouden met bezet server

Het autovacuum-proces schat de kosten van elke I/O-bewerking, verzamelt een totaal voor elke bewerking die wordt uitgevoerd en onderbreekt zodra de bovengrens van de kosten is bereikt. Het proces maakt gebruik van twee serverparameters: autovacuum_vacuum_cost_delay en autovacuum_vacuum_cost_limit.

autovacuum_vacuum_cost_limit Standaard is ingesteld op -1, wat betekent dat de limiet voor autovacuumkosten dezelfde waarde gebruikt als de vacuum_cost_limit parameter. De standaardwaarde voor vacuum_cost_limit is 200. vacuum_cost_limit vertegenwoordigt de kosten van een handmatig vacuüm.

Als u instelt autovacuum_vacuum_cost_limit op -1, gebruikt Autovacuum de vacuum_cost_limit parameter. Als u instelt autovacuum_vacuum_cost_limit op een waarde die groter is dan -1, gebruikt Autovacuum de autovacuum_vacuum_cost_limit parameter.

Als autovacuum niet bijhoudt, kunt u overwegen de volgende parameters te wijzigen:

Kenmerk Description
autovacuum_vacuum_cost_limit Standaard: 200. U kunt de kostenlimiet verhogen. Bewaak het CPU- en I/O-gebruik op de database voor en na het aanbrengen van wijzigingen.
autovacuum_vacuum_cost_delay PostgreSQL versie 12 en hoger : standaard: 2 ms. U kunt deze waarde verlagen voor een meer agressieve autovacuüm.
vacuum_buffer_usage_limit PostgreSQL-versies 16 en hoger : hiermee stelt u de grootte van de buffergroep in voor VACUUM- en autovacuum-bewerkingen. Door deze parameter aan te passen, kunt u de prestaties van autovacuum in balans houden met de algehele systeemprestaties door te bepalen hoeveel gedeelde buffercache wordt gebruikt tijdens vacuümbewerkingen.

Opmerking

  • De autovacuum_vacuum_cost_limit waarde wordt proportioneel verdeeld over de actieve autovacuum-werkers. Als er meer dan één werkrol is, overschrijdt de som van de limieten voor elke werkrol niet de waarde van de autovacuum_vacuum_cost_limit parameter.
  • autovacuum_vacuum_scale_factor is een andere parameter die een vacuüm op een tabel kan activeren op grond van opeenhoping van dode tuples. Standaard: 0.2, Toegestaan bereik: 0.05 - 0.1. De schaalfactor is workloadspecifiek en moet worden ingesteld, afhankelijk van de hoeveelheid gegevens in de tabellen. Voordat u de waarde wijzigt, onderzoekt u de werkbelasting en afzonderlijke tabelvolumes.

Autovacuum voortdurend actief

Als autovacuum continu wordt uitgevoerd, kan dit van invloed zijn op het CPU- en I/O-gebruik op de server. Hier volgen enkele mogelijke redenen:

maintenance_work_mem

De autovacuum-daemon gebruikt autovacuum_work_mem, die standaard is ingesteld op -1 . Deze standaardinstelling betekent dat autovacuum_work_mem dezelfde waarde wordt gebruikt als de maintenance_work_mem parameter. In dit artikel wordt ervan uitgegaan dat autovacuum_work_mem is ingesteld op -1 en dat de autovacuum-demon maintenance_work_mem gebruikt.

Als maintenance_work_mem laag is, kunt u deze vergroten naar 2 GB op een Azure Database for PostgreSQL flexibele serverinstantie. Een algemene vuistregel is om 50 MB toe te wijzen aan maintenance_work_mem elke 1 GB RAM-geheugen.

Groot aantal databases

Autovacuum probeert elke autovacuum_naptime seconden een werkrol op elke database te starten.

Als een server bijvoorbeeld 60 databases heeft en autovacuum_naptime is ingesteld op 60 seconden, wordt de autovacuum-werkrol elke seconde gestart [autovacuum_naptime/aantal databases].

Als er meer databases in een cluster zijn, verhoogt u autovacuum_naptime. Tegelijkertijd zorgt u ervoor dat het autovacuumproces agressiever wordt door de autovacuum_cost_limitautovacuum_cost_delay parameters te verhogen en te verlagen. U kunt ook de standaardwaarde van 3 tot 4 of 5 verhogen autovacuum_max_workers .

Fouten door onvoldoende geheugen

Te agressieve maintenance_work_mem waarden kunnen periodiek fouten in het geheugen veroorzaken in het systeem. Inzicht in het beschikbare RAM-geheugen op de server voordat u de maintenance_work_mem parameter wijzigt.

Autovacuum is te verstorend

Als autovacuum te veel resources verbruikt, voert u de volgende acties uit:

Parameters voor autovacuum

Evalueer de parameters autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limiten autovacuum_max_workers. Het onjuist instellen van autovacuumparameters kan leiden tot scenario's waarbij autovacuum te verstorend wordt.

Als autovacuum te verstorend is, kunt u de volgende acties overwegen:

  • Verhoog autovacuum_vacuum_cost_delay en verminder autovacuum_vacuum_cost_limit als u deze hoger instelt dan de standaardwaarde van 200.
  • Verminder het aantal autovacuum_max_workers als u deze hoger instelt dan de standaardwaarde van 3.

Te veel autovacuumwerkers

Het verhogen van het aantal autovacuumwerkers verhoogt niet de snelheid van vacuüm. Gebruik geen groot aantal autovacuumwerkers.

Het verhogen van het aantal autovacuumwerkers leidt tot een hoger geheugenverbruik. Afhankelijk van de waarde van maintenance_work_mem, kan dit leiden tot een verslechtering van de prestaties.

Elk autovacuum-werkproces wordt alleen (1/autovacuum_max_workers) van het totaal autovacuum_cost_limit, dus als u een groot aantal werknemers hebt, wordt elke werkrol langzamer.

Als u het aantal werknemers verhoogt, verhoog dan autovacuum_vacuum_cost_limit en/of verlaag autovacuum_vacuum_cost_delay om het vacuümproces sneller te maken.

Als u de parameter echter instelt op tabelniveau autovacuum_vacuum_cost_delay of autovacuum_vacuum_cost_limit, worden de workers die op deze tabellen worden uitgevoerd, niet opgenomen in het evenwichtsalgoritme [autovacuum_cost_limit/autovacuum_max_workers].

Autovacuum transaction ID (TXID) wraparound-beveiliging

Wanneer een database een transactie-ID wraparoundbescherming tegenkomt, krijgt u een foutmelding zoals de volgende fout:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Opmerking

Dit foutbericht is een langdurig overzicht. Normaal gesproken hoeft u niet over te schakelen naar de modus voor één gebruiker. In plaats daarvan kunt u de vereiste VACUUM-opdrachten uitvoeren en afstemmen om VACUUM snel te laten werken. Hoewel u geen DML (Data Manipulation Language) kunt uitvoeren, kunt u nog steeds VACUUM uitvoeren.

Het wraparound probleem treedt op wanneer de database niet is gevacuumd of wanneer autovacuum niet genoeg dode tuples verwijdert.

Mogelijke redenen voor dit probleem zijn de volgende redenen:

Zware werkbelasting

Een zware werkbelasting zorgt voor te veel dode tuples in een korte periode, waardoor het voor autovacuum moeilijk is om bij te houden. De dode tuples in het systeem vormen een periode die leidt tot een verslechtering van de queryprestaties en leiden tot een terugloopsituatie. Een van de redenen waarom deze situatie zich voordoet, kan zijn dat autovacuum-parameters niet voldoende zijn ingesteld en de server niet bezet blijft.

Langlopende transacties

Een langlopende transactie in het systeem verhindert dat autovacuum dode tuples verwijdert. Ze zijn een obstakel voor het vacuümproces. Als u de langlopende transacties verwijdert, worden dode tuples vrijgemaakt voor verwijdering wanneer autovacuum wordt uitgevoerd.

Langlopende transacties kunnen worden gedetecteerd met behulp van de volgende query:

    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;

Voorbereide instructies

Als er voorbereide instructies zijn die niet worden doorgevoerd, voorkomen ze dat autovacuum dode tuples verwijdert. Met de volgende query kunt u niet-gegenereerde voorbereide instructies vinden:

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

Gebruik COMMIT VOORBEREID of ROLLBACK VOORBEREID om deze instructies door te voeren of terug te draaien.

Ongebruikte replicatiesites

Ongebruikte replicatiesites voorkomen dat autovacuum dode tuples claimt. Met de volgende query kunt u ongebruikte replicatiesites identificeren:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Gebruik pg_drop_replication_slot() dit om ongebruikte replicatiesites te verwijderen.

Wanneer de database wordt uitgevoerd op transactie-id wraparound-beveiliging, controleert u op eventuele blokkeringen zoals eerder vermeld en verwijdert u de obstakels handmatig om automatisch door te gaan en te voltooien. U kunt ook de snelheid van autovacuum verhogen door in te stellen autovacuum_cost_delay op 0 en de autovacuum_cost_limit waarde groter dan 200 te verhogen. Wijzigingen in deze parameters zijn echter niet van toepassing op bestaande autovacuum-werkrollen. Start de database opnieuw op of beëindig bestaande werkrollen handmatig om parameterwijzigingen toe te passen.

Tabelspecifieke vereisten

U kunt autovacuumparameters instellen voor afzonderlijke tabellen. Deze instellingen zijn vooral belangrijk voor kleine en grote tabellen. Voor een kleine tabel met slechts 100 rijen activeert Autovacuum bijvoorbeeld de bewerking VACUUM wanneer 70 rijen worden gewijzigd (zoals eerder is berekend). Als u deze tabel regelmatig bijwerkt, ziet u mogelijk honderden autovacuumbewerkingen per dag. Met deze bewerkingen voorkomt u dat autovacuum andere tabellen onderhoudt waarbij het percentage wijzigingen niet zo belangrijk is. Een tabel met een miljard rijen moet ook 200 miljoen rijen wijzigen om automatischevacuum-bewerkingen te activeren. Als u autovacuumparameters instelt, voorkomt u dergelijke scenario's op de juiste manier.

Als u instellingen voor automatischevacuum wilt instellen voor elke tabel, wijzigt u de serverparameters, zoals wordt weergegeven in de volgende voorbeelden:

    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);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Alleen-invoegen workloads

In PostgreSQL-versies 13 en eerder wordt autovacuum niet uitgevoerd op tabellen met een workload die alleen invoegt, omdat er geen dode tuples zijn en er geen vrije ruimte is die moet worden vrijgemaakt. Autoanalyze wordt echter uitgevoerd voor werkbelastingen die alleen invoegen, omdat er nieuwe gegevens zijn. De nadelen van dit gedrag zijn:

  • De zichtbaarheidskaart van de tabellen wordt niet bijgewerkt, waardoor queryprestaties, met name wanneer er alleen indexscans zijn, na verloop van tijd last krijgen van queryprestaties.
  • De database kan worden uitgevoerd op transactie-id wraparound-beveiliging.
  • Hint-bits zijn niet ingesteld.

Solutions

PostgreSQL-versies 13 en eerder

Met behulp van de pg_cron-extensie kunt u een cron-taak instellen om een periodieke vacuümanalyse op de tabel te plannen. De frequentie van de cron-taak is afhankelijk van de werkbelasting.

Zie voor richtlijnen speciale overwegingen voor het gebruik van pg_cron in Azure Database for PostgreSQL.

PostgreSQL 13 en latere versies

Autovacuum wordt uitgevoerd op tabellen met een alleen-invoegworkload. Twee serverparameters autovacuum_vacuum_insert_threshold en autovacuum_vacuum_insert_scale_factor helpen bepalen wanneer autovacuum kan worden geactiveerd voor insert-only tabellen.

Handleidingen voor probleemoplossing

Flexibele Azure Database for PostgreSQL-server biedt handleidingen voor probleemoplossing in de portal waarmee u bloat op database- of afzonderlijk schemaniveau kunt bewaken en potentiële obstakels voor het autovacuum-proces kunt identificeren.

Er zijn twee handleidingen voor probleemoplossing beschikbaar:

  • Autovacuum-bewaking : gebruik deze handleiding om bloat op database- of afzonderlijk schemaniveau te bewaken.
  • Autovacuumverstoringen en wraparound - Deze handleiding helpt u potentiële autovacuumverstoringen te identificeren en biedt informatie over hoe ver de databases op de server verwijderd zijn van wraparound- of noodsituaties.

De handleidingen voor probleemoplossing delen ook aanbevelingen om potentiële problemen te beperken. Zie de handleidingen voor het instellen en gebruiken van probleemoplossing voor instructies over het opzetten en gebruiken van deze handleidingen.

Proces voor automatischvacuum beëindigen: pg_signal_autovacuum_worker rol

Autovacuum is een belangrijk achtergrondproces omdat het helpt bij efficiënt opslag- en prestatieonderhoud in de database. In het normale autovacuumproces annuleert het zichzelf na de deadlock_timeout. Als een gebruiker een DDL-instructie op een tabel uitvoert, moet de gebruiker mogelijk wachten tot het deadlock_timeout interval. Autovacuum staat niet toe dat lees- of schrijfbewerkingen worden uitgevoerd op de tabel die is aangevraagd door verschillende verbindingsaanvragen, waardoor de latentie in de transactie wordt toegevoegd.

We hebben een nieuwe rol pg_signal_autovacuum_worker geïntroduceerd vanuit PostgreSQL, waarmee niet-supergebruikerleden een doorlopende autovacuum-taak kunnen beëindigen. De nieuwe rol helpt gebruikers beveiligde en gecontroleerde toegang te krijgen tot het autovacuum-proces. Niet-supergebruikers kunnen het autovacuumproces annuleren zodra ze de pg_signal_autovacuum_worker rol krijgen met behulp van de pg_terminate_backend opdracht. De rol pg_signal_autovacuum_worker is beschikbaar in Azure Database for PostgreSQL in PostgreSQL-versies 15 en hoger.

In zeldzame scenario's, zoals anti-wraparound autovacuum, kunnen werknemers onmiddellijk na beëindiging opnieuw opstarten omdat ze essentieel zijn voor het voorkomen van uitputting van transactie-id's. Volg deze stappen om herhaalde conflicten te minimaliseren:

  • De DDL-bewerking in de wachtrij plaatsen vóór beëindiging:

    • Sessie 1: De DDL-verklaring voorbereiden en uitvoeren.

    • Sessie 2: Beëindig het autovacuumproces.

      Belangrijk

      Deze twee stappen moeten achter elkaar worden uitgevoerd. Als de DDL-instructie te lang geblokkeerd blijft, kan deze vergrendelingen bevatten en andere DML-bewerkingen op de server blokkeren.

  • Beëindig autovacuum en voer DDL uit: Als de DDL onmiddellijk moet worden uitgevoerd:

    • Beëindig het autovacuumproces met behulp van pg_terminate_backend().
    • Voer de DDL-instructie direct na beëindiging uit.

Stappen om herhaalde conflicten te voorkomen:

  1. Rol verlenen aan gebruiker

    GRANT pg_signal_autovacuum_worker TO app_user;
    1. Proces-id voor autovacuum identificeren
    SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();

  2. Autovacuum beëindigen

    SELECT pg_terminate_backend(<pid>);

  3. DDL-instructie onmiddellijk uitvoeren

    ALTER TABLE my_table ADD COLUMN new_col TEXT;

Opmerking

We raden u niet aan doorlopende autovacuum-processen te beëindigen, omdat dit kan leiden tot tabel- en databasebloat, wat verder kan leiden tot prestatieregressies. In gevallen waarin er echter een bedrijfskritieke vereiste is met betrekking tot de geplande uitvoering van een DDL-instructie die overeenkomt met het autovacuum-proces, kunnen niet-supergebruikers de autovacuum op een gecontroleerde en veilige manier beëindigen met behulp van de pg_signal_autovacuum_worker rol.

Aanbevelingen voor Azure Advisor

Aanbevelingen van Azure Advisor identificeren proactief of een server een hoge bloatverhouding heeft of als de server een transactieterugloopscenario nadert. U kunt ook Azure Advisor-waarschuwingen maken voor de aanbevelingen.

De aanbevelingen zijn:

  • Hoge bloatverhouding: Een hoge bloatverhouding kan de serverprestaties op verschillende manieren beïnvloeden. Een belangrijk probleem is dat de PostgreSQL Engine Optimizer moeite kan hebben om het beste uitvoeringsplan te selecteren, wat leidt tot verminderde queryprestaties. Daarom wordt een aanbeveling geactiveerd wanneer het bloatpercentage op een server een bepaalde drempelwaarde bereikt om dergelijke prestatieproblemen te voorkomen.

  • Transactieterugloop: dit scenario is een van de ernstigste problemen die een server kan tegenkomen. Zodra uw server deze status bereikt, kan het stoppen met het accepteren van nieuwe transacties, waardoor de server alleen-lezen modus wordt. Daarom wordt een aanbeveling geactiveerd wanneer de server de drempelwaarde voor 1 miljard transacties overschrijdt.

Verwante inhoud

  • Last updated on