Automatisch vacuüm optimaliseren in Azure Database for PostgreSQL - Enkele server

Artikel
04/11/2024

VAN TOEPASSING OP: Azure Database for PostgreSQL - enkele server

Belangrijk

Azure Database for PostgreSQL - Enkele server bevindt zich op het buitengebruikstellingspad. We raden u ten zeerste aan om een upgrade uit te voeren naar Azure Database for PostgreSQL - Flexible Server. Zie Wat gebeurt er met Azure Database for PostgreSQL Enkele server voor meer informatie over migreren naar Azure Database for PostgreSQL - Flexible Server.

In dit artikel wordt beschreven hoe u autovacuum effectief kunt optimaliseren op een Azure Database for PostgreSQL-server.

Overzicht van autovacuum

PostgreSQL maakt gebruik van MVCC (MultiVersion Concurrency Control) om een grotere gelijktijdigheid van databases toe te staan. Elke update resulteert in een insert en delete, en elke delete resulteert in rijen die voorlopig worden gemarkeerd voor verwijdering. Bij voorlopige markering worden inactieve tuples geïdentificeerd die later worden verwijderd. PostgreSQL voert een vacuümtaak uit om deze taken uit te voeren.

Een vacuümtaak kan handmatig of automatisch worden geactiveerd. Er bestaan meer dode tuples wanneer de database zware update- of verwijderbewerkingen ondervindt. Er bestaan minder dode tuples wanneer de database niet actief is. U moet vaker leegmaken wanneer de databasebelasting zwaar is, waardoor het uitvoeren van vacuümtaken handmatig onhandig wordt.

Autovacuum kan worden geconfigureerd en profiteert van afstemming. De standaardwaarden waarmee PostgreSQL wordt geleverd, proberen ervoor te zorgen dat het product op allerlei soorten apparaten werkt. Deze apparaten omvatten Raspberry. De ideale configuratiewaarden zijn afhankelijk van:

Totale beschikbare resources, zoals SKU en opslaggrootte.
Resourcegebruik.
Afzonderlijke objectkenmerken.

Voordelen van Autovacuum

Als u niet van tijd tot tijd vacuümt, kunnen de dode tuples die zich opstapelen leiden tot:

Gegevensbloat, zoals grotere databases en tabellen.
Grotere suboptimale indexen.
Verhoogde I/O.

Bloat bewaken met autovacuum-query's

De volgende voorbeeldquery is ontworpen om het aantal dode en live tuples in een tabel met de naam XYZ te identificeren:

SELECT relname,
       n_dead_tup,
       n_live_tup,
       (n_dead_tup / n_live_tup) AS DeadTuplesRatio,
       last_vacuum,
       last_autovacuum
FROM pg_catalog.pg_stat_all_tables
WHERE relname = 'XYZ'
ORDER BY n_dead_tup DESC;

Autovacuum-configuraties

De configuratieparameters die autovacuum beheren, zijn gebaseerd op antwoorden op twee belangrijke vragen:

Wanneer moet het beginnen?
Hoeveel moet het schoon maken nadat het begint?

Hier volgen enkele configuratieparameters voor automatischevacuum die u kunt bijwerken op basis van de vorige vragen, samen met enkele richtlijnen.

Parameter	Omschrijving	Default value
autovacuum_vacuum_threshold	Hiermee geeft u het minimum aantal bijgewerkte of verwijderde tuples op dat nodig is om een vacuümbewerking in een tabel te activeren. De standaardwaarde is 50 tuples. Stel deze parameter alleen in het bestand postgresql.conf of op de opdrachtregel van de server in. Als u de instelling voor afzonderlijke tabellen wilt overschrijven, wijzigt u de tabelopslagparameters.	50
autovacuum_vacuum_scale_factor	Hiermee geeft u een fractie van de tabelgrootte op die moet worden toegevoegd aan autovacuum_vacuum_threshold bij het bepalen of een vacuümbewerking moet worden geactiveerd. De standaardwaarde is 0,2, wat 20 procent van de tabelgrootte is. Stel deze parameter alleen in het bestand postgresql.conf of op de opdrachtregel van de server in. Als u de instelling voor afzonderlijke tabellen wilt overschrijven, wijzigt u de tabelopslagparameters.	0,2
autovacuum_vacuum_cost_limit	Hiermee geeft u de kostenlimietwaarde op die wordt gebruikt bij automatische vacuümbewerkingen. Als -1 is opgegeven, wat de standaardwaarde is, wordt de reguliere vacuum_cost_limit waarde gebruikt. Als er meer dan één werkrol is, wordt de waarde proportioneel verdeeld over de actieve autovacuum-werkrollen. De som van de limieten voor elke werkrol overschrijdt niet de waarde van deze variabele. Stel deze parameter alleen in het bestand postgresql.conf of op de opdrachtregel van de server in. Als u de instelling voor afzonderlijke tabellen wilt overschrijven, wijzigt u de tabelopslagparameters.	-1
autovacuum_vacuum_cost_delay	Hiermee geeft u de kostenvertragingswaarde op die wordt gebruikt bij automatische vacuümbewerkingen. Als -1 is opgegeven, wordt de normale vacuum_cost_delay waarde gebruikt. De standaardwaarde is 20 milliseconden. Stel deze parameter alleen in het bestand postgresql.conf of op de opdrachtregel van de server in. Als u de instelling voor afzonderlijke tabellen wilt overschrijven, wijzigt u de tabelopslagparameters.	20 ms
autovacuum_naptime	Hiermee geeft u de minimale vertraging tussen autovacuum wordt uitgevoerd op een bepaalde database. In elke ronde onderzoekt de daemon de database en problemen met VACUUM- en ANALYZE-opdrachten, indien nodig voor tabellen in die database. De vertraging wordt gemeten in seconden. Stel deze parameter alleen in het bestand postgresql.conf of op de opdrachtregel van de server in.	15 s
autovacuum_max_workers	Hiermee geeft u het maximum aantal autovacuum processen, met uitzondering van het startprogramma voor autovacuum, dat op elk gewenst moment kan worden uitgevoerd. De standaardwaarde is drie. Stel deze parameter alleen in bij het starten van de server.	3

Als u de instellingen voor afzonderlijke tabellen wilt overschrijven, wijzigt u de tabelopslagparameters.

Autovacuum kosten

Hier volgen de 'kosten' voor het uitvoeren van een vacuümbewerking:

De gegevenspagina's waarop de vacuüm wordt uitgevoerd, zijn vergrendeld.
Rekenkracht en geheugen worden gebruikt wanneer een vacuümtaak wordt uitgevoerd.

Als gevolg hiervan voert u geen vacuümtaken te vaak of te vaak uit. Een vacuümtaak moet worden aangepast aan de werkbelasting. Test alle wijzigingen in de autovacuum-parameter vanwege de compromissen van elke parameter.

Starttrigger autovacuum

Autovacuum wordt geactiveerd wanneer het aantal dode tuples groter is dan autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples. Hier is reltuples een constante.

Opschonen vanuit autovacuum moet de databasebelasting bijhouden. Anders hebt u mogelijk onvoldoende opslagruimte en ondervindt u een algemene vertraging in query's. Afgeschreven in de loop van de tijd, de snelheid waarmee een vacuümbewerking dode tuples opschoont, moet gelijk zijn aan de snelheid waarmee dode tuples worden gemaakt.

Databases met veel updates en verwijderingen hebben meer dode tuples en hebben meer ruimte nodig. Over het algemeen profiteren databases met veel updates en verwijderingen van lage waarden van autovacuum_vacuum_scale_factor en autovacuum_vacuum_threshold. De lage waarden verhinderen langdurige accumulatie van dode tuples. U kunt hogere waarden gebruiken voor beide parameters met kleinere databases, omdat de noodzaak voor vacuüm minder urgent is. Frequent vacuüm komt ten koste van rekenkracht en geheugen.

De standaardschaalfactor van 20 procent werkt goed voor tabellen met een laag percentage dode tuples. Het werkt niet goed aan tabellen met een hoog percentage dode tuples. In een tabel van 20 GB wordt deze schaalfactor bijvoorbeeld omgezet in 4 GB aan dode tuples. Op een tafel van 1 TB is het 200 GB aan dode tuples.

Met PostgreSQL kunt u deze parameters instellen op tabelniveau of exemplaarniveau. Tegenwoordig kunt u deze parameters alleen instellen op tabelniveau in Azure Database for PostgreSQL.

De kosten van autovacuum schatten

Het uitvoeren van autovacuum is 'kostbaar' en er zijn parameters voor het beheren van de runtime van vacuümbewerkingen. De volgende parameters helpen bij het schatten van de kosten van het uitvoeren van vacuüm:

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

Het vacuümproces leest fysieke pagina's en controleert op dode tuples. Elke pagina in shared_buffers wordt beschouwd als kosten van 1 (vacuum_cost_page_hit). Alle andere pagina's worden beschouwd als kosten van 20 (vacuum_cost_page_dirty), als er dode tuples bestaan, of 10 (vacuum_cost_page_miss), als er geen dode tuples bestaan. De vacuümbewerking stopt wanneer het proces de autovacuum_vacuum_cost_limit overschrijdt.

Nadat de limiet is bereikt, wordt het proces voor de duur die is opgegeven door de parameter autovacuum_vacuum_cost_delay voordat deze opnieuw wordt gestart. Als de limiet niet is bereikt, wordt autovacuum gestart na de waarde die is opgegeven door de parameter autovacuum_naptime.

Samengevat bepalen de parameters autovacuum_vacuum_cost_delay en autovacuum_vacuum_cost_limit hoeveel gegevensopruiming per tijdseenheid is toegestaan. Houd er rekening mee dat de standaardwaarden te laag zijn voor de meeste prijscategorieën. De optimale waarden voor deze parameters zijn afhankelijk van de prijscategorie en moeten dienovereenkomstig worden geconfigureerd.

De parameter autovacuum_max_workers bepaalt het maximum aantal autovacuumprocessen dat tegelijkertijd kan worden uitgevoerd.

Met PostgreSQL kunt u deze parameters instellen op tabelniveau of exemplaarniveau. Tegenwoordig kunt u deze parameters alleen instellen op tabelniveau in Azure Database for PostgreSQL.

Autovacuum per tabel optimaliseren

U kunt alle vorige configuratieparameters per tabel configureren. Hier volgt een voorbeeld:

ALTER TABLE t SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);

Autovacuum is een synchroon proces per tabel. Hoe groter het percentage dode tuples dat een tabel heeft, hoe hoger de 'kosten' voor autovacuum. U kunt tabellen splitsen met een hoge snelheid van updates en verwijderingen in meerdere tabellen. Door tabellen te splitsen, kunt u autovacuum parallelliseren en de kosten verlagen om autovacuum in één tabel te voltooien. U kunt ook het aantal parallelle autovacuum-werknemers verhogen om ervoor te zorgen dat werknemers vrijelijk worden gepland.

Volgende stappen

Zie de volgende PostgreSQL-documentatie voor meer informatie over het gebruik en afstemmen van Autovacuum:

Delen via