PricingLoginStart free
PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

Taulujen ja indeksien paisumisen tarkka mittaaminen

Käyttäkää pgstattuplea ja arviointikyselyitä vapaan tilan määrän selvittämiseen ennen korjaustavan valintaa.

Oppitunti 1/413 vaihetta

Taulujen ja indeksien paisumisen tarkka mittaaminen on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 1/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Miksi bloat syntyy

PostgreSQL käyttää MVCC-mallia (Multi-Version Concurrency Control). Kun UPDATE- tai DELETE-toiminto muuttaa riviä, vanhaa versiota ei poisteta heti. Siitä tulee kuollut monikko (dead tuple), joka vie edelleen tilaa, kunnes VACUUM merkitsee sen uudelleenkäytettäväksi.

  • Bloat = kuolleiden monikkojen viemä tila sekä täyttämätön vapaa tila, jota taulu tai indeksi ei enää tarvitse.
  • Bloat kasvattaa levyllä olevaa kokoa, hidastaa peräkkäisiä skannauksia ja heikentää välimuistin tehokkuutta.
  • Myös indeksit paisuvat: B-tree-sivut säilyttävät osoittimet kuolleisiin keon monikkoihin, kunnes ne siivotaan.

Ennen korjausmenetelmän (VACUUM, VACUUM FULL, pg_repack tai REINDEX) valintaa on ensin mitattava, kuinka paljon bloatia todella on. Arvaaminen johtaa tarpeettomaan ja häiritsevään ylläpitoon.

Elävät ja kuolleet monikot

Edullisin ensimmäinen mittari saadaan tilastojen kerääjältä. pg_stat_user_tables seuraa taulukohtaisia arvioita elävien ja kuolleiden monikkojen määristä; ANALYZE ja autovacuum päivittävät niitä.

  • n_live_tup — arvio elävien rivien määrästä.
  • n_dead_tup — puhdistusta odottavien kuolleiden rivien arvioitu määrä.
  • Korkea n_dead_tup-suhde viittaa siihen, että autovacuum ei pysy muutosten tahdissa.

Tämä on arvio, ei tavu- tarkka mitta, mutta se ei maksa mitään ja toimii erinomaisena alustavana karsintana.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Arviointi ja tarkka mittaus

Bloatin mittaamiseen on kaksi päämenetelmää, joilla on erilaiset kompromissit:

  • Arviointikyselyt lukevat vain luettelotilastoja (pg_class, pg_statistic). Ne ovat nopeita eivätkä vaadi lukituksia, mutta ovat likimääräisiä — tarkkuus riippuu tuoreesta ANALYZE-ajosta ja sarakkeiden leveyttä koskevista oletuksista.
  • pgstattuple skannaa relaation fyysisesti ja laskee elävien ja kuolleiden tavujen täsmälliset määrät. Se on tarkka, mutta suurilla tauluilla I/O-raskas.

Käytännöllinen työnkulku on käyttää edullista arviota ehdokkaiden löytämiseen ja tarkistaa pahimmat tapaukset sitten pgstattuplella ennen korjaustoimiin sitoutumista.

pgstattuplen asentaminen

pgstattuple on PostgreSQL:n mukana toimitettava contrib-laajennus. Se on otettava käyttöön tietokantakohtaisesti ennen käyttöä.

  • Käyttöönotto edellyttää superuser-käyttäjää tai roolia, jolla on tietokannassa CREATE-oikeus.
  • Sen funktiot edellyttävät pg_stat_scan_tables-roolia (tai superuser-käyttäjää), jotta niitä voi käyttää mielivaltaisiin relaatioihin.

Asennuksen jälkeen käytettävissä ovat pgstattuple(), pgstatindex() ja kevyempi pgstattuple_approx().

CREATE EXTENSION IF NOT EXISTS pgstattuple;

pgstattuple-tulosteen lukeminen

pgstattuple('relation') suorittaa täyden skannauksen ja palauttaa yhden rivin tavutason tietoja keosta.

  • table_len — relaation kokonaiskoko tavuina.
  • tuple_count / tuple_len — elävien monikkojen määrä ja tavujen kokonaismäärä.
  • dead_tuple_count / dead_tuple_len — kuolleiden monikkojen määrä ja niiden viemät tavut.
  • free_space / free_percent — uudelleenkäytettävissä oleva vapaa tila.

Keskeinen bloat-mittari on dead_tuple_percent yhdessä arvon free_percent kanssa: ne kertovat, kuinka suuri osa tiedostosta ei sisällä eläviä tietoja.

SELECT table_len,
       tuple_count,
       tuple_len,
       dead_tuple_count,
       dead_tuple_len,
       dead_tuple_percent,
       free_space,
       free_percent
FROM pgstattuple('public.orders');

Täyden skannauksen kustannus

pgstattuple() lukee relaation jokaisen sivun. 500 Gt:n taululla tämä tarkoittaa paljon I/O:ta ja voi syrjäyttää hyödyllisiä tietoja välimuistista.

  • Se tarvitsee vain ACCESS SHARE -lukituksen, joten se ei estä lukuja tai kirjoituksia — I/O-kuormitus on silti todellinen.
  • Käyttäkää suurilla tauluilla mieluummin pgstattuple_approx()-funktiota, joka hyödyntää näkyvyyskarttaa ohittaakseen täysin näkyvät sivut ja otantaa loput.
  • approx palauttaa arvot approx_free_percent ja dead_tuple_percent, jotka ovat lähellä tarkkoja arvoja murto-osalla kustannuksista.

Nyrkkisääntö: arvioikaa ensin, suorittakaa approx keskikokoisilla tauluilla ja varatkaa tarkka pgstattuple() tietyn epäillyn kohteen lopulliseen varmistukseen.

SELECT table_len,
       approx_tuple_count,
       approx_tuple_percent,
       dead_tuple_count,
       dead_tuple_percent,
       approx_free_percent
FROM pgstattuple_approx('public.orders');

Indeksien bloatin mittaaminen

Indeksit paisuvat taulusta riippumatta. Käyttäkää B-tree-indekseille pgstatindex()-funktiota rakenteellisten tietojen saamiseksi.

  • avg_leaf_density — hyödyllisellä tiedolla täytettyjen lehtisivujen prosenttiosuus. Hyvien indeksien arvo on lähellä 90:tä prosenttia; kohti 50:tä prosenttia laskevat arvot ovat merkki voimakkaasta bloatista.
  • leaf_fragmentation — lehtisivujen järjestyksen vastaisuus; suuri pirstoutuneisuus heikentää aluehakuja.
  • index_size ja internal_pages / leaf_pages kuvaavat puun rakennetta.

Matala avg_leaf_density on selkein peruste REINDEX-toiminnolle, mieluiten muodossa REINDEX ... CONCURRENTLY.

SELECT version,
       index_size,
       leaf_pages,
       avg_leaf_density,
       leaf_fragmentation
FROM pgstatindex('public.orders_customer_id_idx');

Arviointikyselyihin perustuva lähestymistapa

Kun skannausta ei voida tehdä lainkaan, yhteisön bloat-arviointikysely (lähteistä check_postgres / pgsql-bloat-estimation) laskee tilastojen perusteella odotetun koon ja vertaa sitä todelliseen kokoon.

Sen perusidea:

  • Otetaan rivin keskimääräinen leveys pg_statistic-taulusta (ANALYZEn kullekin sarakkeelle tallentama avg_width).
  • Lisätään monikkokohtaisen otsakkeen ja kohdistuksen aiheuttama lisätila ja jaetaan taulun koko sivukohtaisten odotettujen monikkomäärien perusteella.
  • Odotettujen ja todellisten sivujen välinen ero on arvioitu bloat.

Menetelmä on likimääräinen ja herkkä vanhentuneille tilastoille, mutta se suoritetaan millisekunneissa koko tietokannalle.

Miksi arviot vääristyvät

Arvioinnin tarkkuus romahtaa, jos sen lähtötiedot ovat virheellisiä. Varokaa seuraavia sudenkuoppia:

  • Vanhentuneet tilastot: jos ANALYZEa ei ole suoritettu äskettäin, avg_width ja rivimäärät ovat vanhentuneita. Suorittakaa ANALYZE ennen kuin luotatte arvioihin.
  • Leveät vaihtelevan pituiset sarakkeet: erittäin vaihtelevat text- ja jsonb-leveydet tekevät rivikohtaisista keskiarvoista epäluotettavia.
  • TOAST: erilliseen TOAST-tauluun rivin ulkopuolelle tallennetut suuret arvot jäävät kokonaan keon arvioiden ulkopuolelle.
  • Fillfactor: arvolla fillfactor < 100 luotu taulu jättää tarkoituksella vapaata tilaa — sitä ei pidä tulkita bloatiksi.

Tarkistakaa yllättävä arvio aina ristiin pgstattuple_approx()-funktiolla ennen toimenpiteitä.

ANALYZE public.orders;

Älkää unohtako TOAST-taulua

Suuret sarakearvot siirretään piilotettuun TOAST-tauluun, joka paisuu omia aikojaan. Keko voi näyttää siistiltä, vaikka sen TOAST-relaatio olisi valtava.

  • Etsikää TOAST-relaatio käyttämällä arvoa pg_class.reltoastrelid.
  • Suorittakaa pgstattuple() suoraan TOAST-relaation OID-tunnisteelle sen kuolleen tilan mittaamiseksi.

Tauluissa, joiden jsonb- tai bytea-sarakkeita päivitetään usein, suurin osa bloatista on usein piilossa TOASTissa.

SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size,
       t.dead_tuple_percent
FROM pg_class c
CROSS JOIN LATERAL pgstattuple(c.reltoastrelid) AS t
WHERE c.relname = 'orders'
  AND c.reltoastrelid <> 0;

Luvuista päätökseen

Kun luvut ovat tarkkoja, yhdistäkää ne sopivaan korjausmenetelmään:

  • dead_tuple_percent suuri, free_percent suuri: autovacuum on jäänyt jälkeen — tavallinen VACUUM (tai autovacuumin säätäminen) vapauttaa yleensä uudelleenkäytettävää tilaa kirjoittamatta tiedostoa uudelleen.
  • free_percent erittäin suuri, mutta taulu ei pienene: tiedoston lopussa on vapaata tilaa, jota VACUUM ei voi palauttaa käyttöjärjestelmälle — harkitkaa pg_repack-toimintoa (online) tai VACUUM FULL-toimintoa (lukitsee taulun).
  • Indeksin avg_leaf_density matala: käyttäkää REINDEX CONCURRENTLY-toimintoa.

Asettakaa kynnysarvo (esimerkiksi toimikaa vasta, kun bloatia on noin yli 20 % ja absoluuttinen koko on merkittävä), jotta ette suorita häiritsevää ylläpitoa vähäisten hyötyjen vuoksi.

Pikatarkistus

Epäilette, että 400 Gt:n taulu on pahasti paisunut, ja haluatte tarkan bloat-määrän mahdollisimman vähäisellä I/O-vaikutuksella, kun autovacuum pitää näkyvyyskartan melko hyvin ajan tasalla. Mikä työkalu sopii parhaiten?

Vastaa interaktiivisessa oppitunnissa

Kertaus

Käytettävissänne on nyt kerroksittainen menetelmä bloatin määrän selvittämiseen ennen toimenpiteitä:

  • Alustavaan karsintaan käytetään pg_stat_user_tables-taulua (n_dead_tup-suhde) — ilmaista ja välitöntä.
  • Arvioikaa koko tietokanta tilastopohjaisilla bloat-kyselyillä — nopeaa, mutta varmistakaa tulos tuoreella ANALYZE-ajolla.
  • Varmistakaa tulos tarkasti pgstattuple()-funktiolla tai suurilla tauluilla pgstattuple_approx()-funktiolla tarkastelemalla arvoja dead_tuple_percent ja free_percent.
  • Indeksit: käyttäkää pgstatindex()-funktiota ja seuratkaa arvoja avg_leaf_density ja leaf_fragmentation.
  • Älkää unohtako TOASTia ja vähentäkää tarkoituksella jätetty fillfactor-vapaa tila arvioista.

Vasta kun luvut ylittävät merkityksellisen kynnyksen, valitkaa VACUUM, pg_repack, VACUUM FULL tai REINDEX — mittaus ohjaa korjausta, ei koskaan päinvastoin.

Aloita maksutta

Opi SQL tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit
22
Oppitunnit
88
Aloita opiskelu maksuttaNäytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Taulujen ja indeksien paisumisen tarkka mittaaminen” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Taulujen ja indeksien paisumisen tarkka mittaaminen”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Taulujen ja indeksien paisumisen tarkka mittaaminen”?

Käyttäkää pgstattuplea ja arviointikyselyitä vapaan tilan määrän selvittämiseen ennen korjaustavan valintaa. Harjoittelet PostgreSQL:n suorituskyky ja kyselyjen optimointi-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni PostgreSQL:n suorituskyky ja kyselyjen optimointi-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 1/4.

Kuinka kauan ”Taulujen ja indeksien paisumisen tarkka mittaaminen”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunnilla?

Kyllä. Jokainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

  1. Taulujen ja indeksien paisumisen tarkka mittaaminen
  2. Tilan vapauttaminen pg_repackilla
  3. Fillfactor-asetuksen säätäminen paljon päivitettäville tauluille
  4. TOASTin sisäinen toiminta ja suurten arvojen tallennus
← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi