Share via

Automatisch afstemmen in Azure Database for PostgreSQL - Flexibele server

  • Artikel

VAN TOEPASSING OP: Azure Database for PostgreSQL - Flexibele server

Dit artikel bevat een overzicht van de functie autovacuum voor flexibele Azure Database for PostgreSQL-server en de handleidingen voor het oplossen van problemen met functies die beschikbaar zijn voor het bewaken van de database-bloat, automatischevacuum-blokkers en ook informatie over hoe ver de database van nood- of terugloopsituatie is.

Wat is autovacuum?

Interne gegevensconsistentie in PostgreSQL is gebaseerd op het MVCC-mechanisme (Multi-Version Concurrency Control), waarmee de database-engine meerdere versies van een rij kan onderhouden en meer gelijktijdigheid biedt met minimale blokkering tussen de verschillende processen.

PostgreSQL-databases hebben het juiste onderhoud nodig. Wanneer een rij bijvoorbeeld wordt verwijderd, wordt deze niet fysiek verwijderd. In plaats daarvan wordt de rij gemarkeerd als 'dood'. Op dezelfde manier voor updates wordt de rij gemarkeerd als 'dood' en wordt een nieuwe versie van de rij ingevoegd. Deze bewerkingen laten dode records achter, ook wel dode tuples genoemd, zelfs nadat alle transacties die deze versies kunnen zien voltooien. Tenzij opgeschoond, blijven dode tuples behouden, verbruikt u schijfruimte en bloating tabellen en indexen die leiden tot trage queryprestaties.

PostgreSQL maakt gebruik van een proces met de naam autovacuum om dode tuples automatisch op te schonen.

Autovacuum internals

Autovacuum leest pagina's die zoeken naar dode tuples en als er geen worden gevonden, wordt de pagina verwijderd door Autovacuum. Wanneer autovacuum dode tuples vindt, worden ze verwijderd. De kosten zijn gebaseerd op:

  • vacuum_cost_page_hit: Kosten voor het lezen van een pagina die zich al in gedeelde buffers bevindt en waarvoor geen schijfleesbewerking nodig is. De standaardwaarde is ingesteld op 1.
  • vacuum_cost_page_miss: Kosten voor het ophalen van een pagina die zich niet in gedeelde buffers bevindt. De standaardwaarde is ingesteld op 10.
  • vacuum_cost_page_dirty: Kosten van het schrijven naar een pagina wanneer dode tuples erin worden gevonden. De standaardwaarde is ingesteld op 20.

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

  • autovacuum_vacuum_cost_limit is de hoeveelheid werk die autovacuum in één gebruik doet.
  • autovacuum_vacuum_cost_delay aantal milliseconden dat autovacuum in slaap staat nadat deze de kostenlimiet heeft bereikt die is opgegeven door de autovacuum_vacuum_cost_limit parameter.

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

Wat betreft autovacuum_vacuum_cost_delay, in Postgres versie 11 wordt het standaard ingesteld op 20 milliseconden, terwijl in Postgres-versies 12 en hoger standaard 2 milliseconden worden gebruikt.

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 autovacuum van één seconde 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

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;

De volgende kolommen helpen bepalen of autovacuum de tabelactiviteit inhaalt:

  • dead_pct: percentage dode tuples in vergelijking met levende tuples.
  • last_autovacuum: De datum van de laatste keer dat de tabel automatisch is leeggeveerd.
  • last_autoanalyze: de datum van de laatste keer dat de tabel automatisch is geanalyseerd.

Wanneer activeert PostgreSQL autovacuum

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

De exacte vergelijkingen voor elke actie zijn:

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

Analyseer bijvoorbeeld triggers nadat 60 rijen zijn gewijzigd in een tabel die 100 rijen bevat en vacuümtriggers wanneer 70 rijen in de tabel veranderen met behulp van de volgende vergelijkingen:

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

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;

Notitie

De query houdt er niet rekening mee dat autovacuum per tabel kan worden geconfigureerd met behulp van de DDL-opdracht 'tabel wijzigen'.

Veelvoorkomende problemen met autovacuum

Bekijk de volgende lijst met mogelijke 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. autovacuum_vacuum_cost_delay en autovacuum_vacuum_cost_limit zijn de twee serverparameters die in het proces worden gebruikt.

autovacuum_vacuum_cost_limit Standaard is ingesteld op –1, wat betekent dat de limiet voor autovacuumkosten dezelfde waarde is als de parametervacuum_cost_limit, die standaard 200 is. vacuum_cost_limit is de kosten van een handmatig vacuüm.

Als autovacuum_vacuum_cost_limit deze optie is ingesteld -1 op dan gebruikt Autovacuum de vacuum_cost_limit parameter, maar als autovacuum_vacuum_cost_limit zichzelf is ingesteld op groter dan -1 dan dan wordt autovacuum_vacuum_cost_limit de parameter beschouwd.

Als de autovacuum niet bijhoudt, kunnen de volgende parameters worden gewijzigd:

Parameter Description
autovacuum_vacuum_scale_factor Standaard: 0.2, 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_vacuum_cost_limit Standaard: 200. De kostenlimiet kan worden verhoogd. CPU- en I/O-gebruik op de database moeten worden bewaakt voor en na het aanbrengen van wijzigingen.
autovacuum_vacuum_cost_delay Postgres versie 11 - Standaard: 20 ms. De parameter kan worden verlaagd tot 2-10 ms.
Postgres-versies 12 en hoger - Standaard: 2 ms.

Notitie

De autovacuum_vacuum_cost_limit waarde wordt proportioneel verdeeld over de actieve autovacuum-werkrollen, zodat als er meer dan één is, de som van de limieten voor elke werkrol niet groter is dan de waarde van de autovacuum_vacuum_cost_limit parameter

Autovacuum voortdurend actief

Continu uitvoeren van autovacuum kan van invloed zijn op het CPU- en IO-gebruik op de server. Het volgende kan mogelijke oorzaken hebben:

maintenance_work_mem

Autovacuum-daemon die autovacuum_work_mem standaard is ingesteld op -1 betekenis autovacuum_work_mem , heeft dezelfde waarde als de parameter maintenance_work_mem. In dit document wordt ervan uitgegaan dat autovacuum_work_mem deze is ingesteld -1 op en maintenance_work_mem wordt gebruikt door de autovacuum-daemon.

Als maintenance_work_mem deze laag is, kan deze worden verhoogd tot maximaal 2 GB op een flexibele Server van Azure Database for PostgreSQL. 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 DB's].

Het is een goed idee om te verhogen autovacuum_naptime als er meer databases in een cluster zijn. Tegelijkertijd kan het autovacuumproces agressiever worden gemaakt door de autovacuum_cost_limit parameters te verhogen en te verlagen autovacuum_cost_delay en de autovacuum_max_workers standaardwaarde van 3 tot 4 of 5 te verhogen.

Fouten door onvoldoende geheugen

Te agressieve maintenance_work_mem waarden kunnen periodiek fouten in het geheugen veroorzaken in het systeem. Het is belangrijk om inzicht te hebben in het beschikbare RAM-geheugen op de server voordat er wijzigingen in de maintenance_work_mem parameter worden aangebracht.

Autovacuum is te verstorend

Als autovacuum veel resources verbruikt, kunt u het volgende doen:

Parameters voor autovacuum

Evalueer de parameters autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, 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 het volgende overwegen:

  • Verhogen autovacuum_vacuum_cost_delay en verlagen autovacuum_vacuum_cost_limit als deze hoger is dan de standaardwaarde van 200.
  • Verminder het aantal autovacuum_max_workers als deze hoger is ingesteld dan de standaardwaarde van 3.

Te veel autovacuumwerkers

Het verhogen van het aantal autovacuumwerkers zal niet noodzakelijkerwijs de snelheid van vacuüm verhogen. Het is niet raadzaam om een groot aantal autovacuumwerkers te hebben.

Het verhogen van het aantal autovacuum-werkrollen leidt tot meer geheugenverbruik en kan, afhankelijk van de waarde van, leiden tot een verslechtering van maintenance_work_mem 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 het aantal werknemers wordt verhoogd, autovacuum_vacuum_cost_limit moet ook worden verhoogd en/of autovacuum_vacuum_cost_delay moet worden verlaagd om het vacuümproces sneller te maken.

Als we echter het tabelniveau autovacuum_vacuum_cost_delay of autovacuum_vacuum_cost_limit de parameters hebben gewijzigd, worden de werkrollen die op deze tabellen worden uitgevoerd, uitgesloten van overweging in het taakverdelingsalgoritmen [autovacuum_cost_limit/autovacuum_max_workers].

Autovacuum transaction ID (TXID) wraparound-beveiliging

Wanneer een database in de wraparound-beveiliging voor transactie-id's wordt uitgevoerd, kan er een foutbericht zoals het volgende worden waargenomen:

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

Notitie

Dit foutbericht is een langdurig overzicht. U hoeft doorgaans 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 Manipulat Language) kunt uitvoeren, kunt u nog steeds VACUUM uitvoeren.

Het probleem met terugloop treedt op wanneer de database niet leeg is of er te veel dode tuples zijn die niet kunnen worden verwijderd door autovacuum. Dit kan de volgende oorzaken hebben:

Zware werkbelasting

De workload kan in een korte periode te veel dode tuples veroorzaken, waardoor het voor autovacuum lastig is om in te halen. 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

Eventuele langlopende transacties in het systeem staan niet toe dat dode tuples worden verwijderd terwijl autovacuum wordt uitgevoerd. 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, zouden ze voorkomen dat dode tuples worden verwijderd.
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 PREPARED 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 deze handmatig voor autovacuum om 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 worden echter niet toegepast op bestaande autovacuum-werkrollen. Start de database opnieuw op of beëindig bestaande werkrollen handmatig om parameterwijzigingen toe te passen.

Tabelspecifieke vereisten

Autovacuum-parameters kunnen worden ingesteld voor afzonderlijke tabellen. Het is vooral belangrijk voor kleine en grote tabellen. Voor een kleine tabel die slechts 100 rijen bevat, activeert autovacuum bijvoorbeeld de vacuümbewerking wanneer 70 rijen worden gewijzigd (zoals eerder is berekend). Als deze tabel regelmatig wordt bijgewerkt, ziet u mogelijk honderden autovacuumbewerkingen per dag. Hierdoor voorkomt u dat autovacuum andere tabellen onderhoudt waarvoor het percentage wijzigingen niet zo groot 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 de instelling voor automatischevacuum per tabel wilt instellen, wijzigt u de serverparameters als 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);

Alleen-invoegen workloads

In versies van PostgreSQL vóór 13 wordt Autovacuum niet uitgevoerd op tabellen met een werkbelasting die alleen invoegt. Als er geen updates of verwijderingen zijn, zijn er geen dode tuples en geen vrije ruimte die moet worden vrijgemaakt. Autoanalyze wordt echter uitgevoerd voor alleen-invoegworkloads, omdat er nieuwe gegevens zijn. De nadelen hiervan 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 worden niet ingesteld.

Oplossingen

Postgres-versies vóór 13

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

Raadpleeg Extensies voor stapsgewijze instructies met behulp van pg_cron.

Postgres 13 en hogere versies

Autovacuum wordt uitgevoerd op tabellen met een alleen-invoegen workload. Twee nieuwe serverparameters autovacuum_vacuum_insert_threshold en autovacuum_vacuum_insert_scale_factor helpen bepalen wanneer autovacuum kan worden geactiveerd voor tabellen met alleen-invoegen.

Handleidingen voor probleemoplossing

Met behulp van de handleidingen voor het oplossen van problemen met functies die beschikbaar zijn in de flexibele Server-portal van Azure Database for PostgreSQL, is het mogelijk om bloat op database- of afzonderlijk schemaniveau te bewaken, samen met het identificeren van potentiële obstakels voor het autovacuumproces. Er zijn eerst twee handleidingen voor probleemoplossing beschikbaar: automatischevacuum-bewaking die kan worden gebruikt voor het bewaken van bloat op database- of afzonderlijk schemaniveau. De tweede gids voor probleemoplossing is autovacuum-blockers en wraparound waarmee potentiële autovacuum-obstakels kunnen worden geïdentificeerd, samen met informatie over hoe ver de databases op de server van terugloop of noodsituaties aflopen. De handleidingen voor probleemoplossing delen ook aanbevelingen om potentiële problemen te beperken. De handleidingen voor probleemoplossing instellen om ze te gebruiken, volgt u de handleidingen voor het oplossen van problemen met setups.

Gerelateerde inhoud