Ydelsesoptimering og optimering af forespørgsler i PostgreSQL · Lektion

WAL-generering og write amplification

Mål og reducér den WAL-mængde, hver transaktion genererer, for at begrænse I/O- og replikeringsomkostninger.

Lektion 4 af 413 trin

WAL-generering og write amplification er en gratis Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion på CoddyKit. Dette er lektion 4 af 4. Du kan læse alle 3 lektioner i dette læringsspor gratis i deres fulde længde — derefter låser CoddyKit PRO alle lektioner op samt praktiske øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Den er en del af læringsforløbet i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvorfor WAL-mængden betyder noget

Enhver ændring i PostgreSQL skrives først til Write-Ahead Log (WAL), før den berører datafilerne. WAL garanterer holdbarhed og gendannelse efter nedbrud, men de bytes, det producerer, er ikke gratis.

Disk-I/O: WAL fsynkroniseres ved commit, så en stor WAL-mængde betyder større belastning på skrivegennemløbet.
Replikering: hver WAL-byte skal sendes til replikaer og læses af logiske dekodere.
Sikkerhedskopier: arkiveret WAL (via archive_command eller pg_receivewal) øger lagerforbruget og gendannelsestiden.

Denne lektion handler om at måle den WAL, hver transaktion genererer, og reducere den uden at give afkald på holdbarhed.

Hvad genererer egentlig WAL

WAL-poster genereres af langt mere end blot dine rækkeændringer. At kende kilderne er det første skridt til at reducere mængden.

Heap-/indeksændringer: indsættelser, opdateringer, sletninger og de indeksindtastninger, de berører.
Fuldsideafbildninger (FPI'er): den første skrivning til en side efter et checkpoint logger hele siden på 8 KB, ikke kun ændringen.
Opdateringer af HOT-beskæring og synlighedskortet.
Hint-bit-/frysning-skrivninger under VACUUM.

FPI'er er som regel den største og mest overraskende bidragyder til skriveforstærkning.

Måling af WAL pr. sætning med EXPLAIN

Siden PostgreSQL 13 rapporterer EXPLAIN (ANALYZE, WAL) præcist, hvor meget WAL en sætning genererede: antallet af poster, antallet af fuldsideafbildninger og det samlede antal byte.

Dette er det mest præcise værktøj til at knytte WAL til en bestemt forespørgsel. Hold nøje øje med antallet af fpi — et højt FPI-antal er tegn på checkpoint-drevet forstærkning.

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders
SET status = 'shipped'
WHERE shipped_at IS NULL
  AND created_at < now() - interval '1 day';

Læsning af WAL-linjen

WAL-linjen i planen ser sådan ud:

WAL: records=12043 fpi=512 bytes=4823104

Fortolkning:

records: det samlede antal genererede WAL-poster (én eller flere pr. berørt tupel).
fpi: fuldsideafbildninger — hver er cirka 8 KB, så 512 FPI'er svarer alene til cirka 4 MB af det samlede antal.
bytes: det samlede antal byte, der er skrevet til WAL.

Hvis fpi × 8192 udgør en stor del af bytes, skyldes din forstærkning primært fuldsideafbildninger og ikke selve den logiske ændring.

WAL for hele klyngen med pg_stat_wal

Hvis du vil have et overblik på klyngeniveau, samler pg_stat_wal (PostgreSQL 14+) oplysninger om genereringen af WAL. Tag en prøve, kør en arbejdsbelastning, tag en ny prøve, og sammenlign dem.

Vigtige kolonner er wal_records, wal_fpi og wal_bytes. En stigende wal_fpi-rate mellem checkpoints bekræfter FPI-drevet forstærkning på systemniveau.

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_size
FROM pg_stat_wal;

Måling af rå mængde med WAL-LSN'er

Du kan også måle den WAL, der er genereret i et vilkårligt interval, ved at beregne forskellen mellem den aktuelle Log Sequence Number (LSN). LSN er en monotont stigende byteforskydning i WAL-strømmen.

Hent pg_current_wal_lsn() før og efter en arbejdsbelastning, og beregn derefter forskellen. Dette tæller al WAL med, herunder baggrundsarbejde som autovacuum.

SELECT pg_size_pretty(
  pg_wal_lsn_diff('0/9A3B1200', '0/95C40000')
) AS wal_generated;

Fuldsideafbildninger og timing af checkpoints

En FPI skrives ved den første ændring af en side efter et checkpoint. Checkpoints, der udløses for ofte, tvinger derfor de samme belastede sider til at blive afbildet igen og igen.

Løsningen er at sprede checkpoints ud:

Hæv max_wal_size, så checkpoints ikke udløses så hurtigt af mængden.
Hæv checkpoint_timeout (f.eks. 15min), så der forekommer færre tidsbaserede checkpoints.
Hold checkpoint_completion_target tæt på 0.9 for at udjævne flushen, ikke for at reducere antallet af FPI'er.

Færre checkpoints betyder, at hver belastet side afbildes én gang over et længere tidsrum — en direkte reduktion af antallet af WAL-byte.

ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

wal_compression: Reduktion af FPI'er

Når FPI'er ikke kan undgås, kan du komprimere dem. wal_compression komprimerer fuldsideafbildninger, før de skrives til WAL.

off: ingen komprimering (standard i ældre versioner).
pglz: billig komprimering med moderat komprimeringsgrad.
lz4 / zstd (PostgreSQL 15+): bedre komprimeringsgrad, hvor zstd giver den største reduktion.

Det bytter en smule CPU-forbrug for potentielt store WAL-besparelser ved arbejdsbelastninger med mange FPI'er. Det komprimerer IKKE almindelige WAL-poster, kun sideafbildningerne.

ALTER SYSTEM SET wal_compression = 'zstd';
SELECT pg_reload_conf();

-- Verify the change took effect
SHOW wal_compression;

HOT-opdateringer reducerer indeks-WAL

En Heap-Only Tuple (HOT)-opdatering undgår at skrive nye indeksindtastninger, når ingen indekseret kolonne ændres, og den nye tupel kan være på samme side. Færre indeksskrivninger betyder mindre WAL.

To greb maksimerer brugen af HOT:

Opdatér ikke indekserede kolonner, når det ikke er nødvendigt — begræns din SET-liste.
Efterlad ledig plads på siderne med en lavere fillfactor, så den nye tupelversion bliver på samme side.

Kontrollér forholdet n_tup_hot_upd for at bekræfte, at HOT faktisk bliver brugt.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

Batchskrivninger og unlogged-tabeller

Her er yderligere to strukturelle reduktioner:

Batchskrivninger: én stor INSERT ... SELECT eller COPY giver langt mindre WAL-overhead end tusindvis af transaktioner med én række, fordi overheaden pr. post og pr. commit fordeles.
UNLOGGED-tabeller: spring helt over WAL for midlertidige data, stagingdata eller afledte data. Ulempen er, at tabellen tømmes efter et nedbrud og IKKE replikeres.

Brug kun unlogged-tabeller, hvor det er acceptabelt at miste data ved et nedbrud — til ETL-staging, midlertidige materialiserede data og sessionscaches.

CREATE UNLOGGED TABLE staging_events (
    id        bigint,
    payload   jsonb,
    loaded_at timestamptz DEFAULT now()
);

Reduktion af skriveforstærkning gennem design

Ud over indstillingerne er det skemaet og adgangsmønstrene, der bestemmer WAL-mængden:

Undgå brede opdateringer: En opdatering af en række omskriver hele tuplen samt FPI'er for dens side. Flyt brede kolonner, der sjældent opdateres, til en separat tabel.
Sænk fillfactor på belastede tabeller (f.eks. til 80) for at bevare muligheden for HOT-opdateringer.
Fjern overflødige indeks: hvert indeks mangedobler skrive-WAL.
Brug COPY til masseindlæsninger, og overvej stien COPY ... FREEZE for nye tabeller.

Hvert designvalg forstærker effekten: færre FPI'er, færre indeksindtastninger, færre commits.

ALTER TABLE orders SET (fillfactor = 80);
-- Existing rows take effect after a rewrite
VACUUM FULL orders;

Hurtigt tjek: Diagnosticering af høj WAL-mængde

Du kører EXPLAIN (ANALYZE, WAL) på en batch-UPDATE og ser WAL: records=20000 fpi=9800 bytes=82000000. FPI'erne udgør cirka 80 MB af de samlede 82 MB. Hvilken enkelt ændring reducerer mest direkte denne WAL-mængde?

Besvar den i den interaktive lektion

Opsummering: Mål først, og reducér derefter

Du har nu et komplet værktøjssæt til at reducere WAL:

Mål: EXPLAIN (ANALYZE, WAL) pr. sætning, pg_stat_wal for hele klyngen og pg_wal_lsn_diff() til rå mængde over et interval.
Læs signalet: en høj fpi-værdi betyder checkpoint-drevet forstærkning; mange records med få FPI'er betyder en stor mængde logiske ændringer.
Reducér antallet af FPI'er: hæv max_wal_size og checkpoint_timeout; aktivér wal_compression (zstd/lz4).
Reducér antallet af poster: foretræk HOT-opdateringer (smalle SET-lister, lavere fillfactor), batchskrivninger, og fjern overflødige indeks.
Spring WAL over: brug UNLOGGED-tabeller til data, der kan kasseres.

Mål altid først — find ud af, hvad byte-mængden skyldes, før du ændrer en indstilling.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 22
Lektioner: 88

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “WAL-generering og write amplification” gratis?

Ja — alle 3 lektioner i læringssporet Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, inklusive “WAL-generering og write amplification”, kan læses gratis i deres fulde længde her på webstedet. Derefter låser CoddyKit PRO alle lektioner op samt interaktive øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “WAL-generering og write amplification”?

Mål og reducér den WAL-mængde, hver transaktion genererer, for at begrænse I/O- og replikeringsomkostninger. Du øver dig i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Ydelsesoptimering og optimering af forespørgsler i PostgreSQL?

Der kræves ingen tidligere erfaring. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 4 af 4.

Hvor lang tid tager lektionen “WAL-generering og write amplification”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion?

Ja. Alle Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Tuple-synlighed, xmin og xmax
HOT-opdateringer og heap-only tuple-kæder
Visibility map og index-only scans
WAL-generering og write amplification

← Tilbage til Ydelsesoptimering og optimering af forespørgsler i PostgreSQL