Transkripsjon av Alexey Lesovskys rapport fra 2015 "Dyp dykk inn i PostgreSQL intern statistikk"
Ansvarsfraskrivelse fra forfatteren av rapporten: Jeg noterer meg at denne rapporten er datert november 2015 - det har gått mer enn 4 år og det har gått mye tid. Versjon 9.4 som er omtalt i rapporten støttes ikke lenger. I løpet av de siste 4 årene har det blitt sluppet 5 nye utgivelser hvor det er mange innovasjoner, forbedringer og endringer angående statistikk, og noe av materialet er utdatert og uaktuelt. Mens jeg anmelder, prøvde jeg å markere disse stedene for ikke å villede leseren. Jeg skrev ikke om disse passasjene, det er mange av dem og resultatet blir en helt annen rapport.
PostgreSQL DBMS er en enorm mekanisme, og denne mekanismen består av mange undersystemer, hvis koordinerte drift direkte påvirker ytelsen til DBMS. Under drift samles statistikk og informasjon om driften av komponenter, som lar deg evaluere effektiviteten til PostgreSQL og iverksette tiltak for å forbedre ytelsen. Imidlertid er det mye av denne informasjonen, og den presenteres i en ganske forenklet form. Å behandle denne informasjonen og tolke den er noen ganger en helt ikke-triviell oppgave, og "zoo" av verktøy og verktøy kan lett forvirre selv en avansert DBA.
God ettermiddag Jeg heter Aleksey. Som Ilya sa, jeg vil snakke om PostgreSQL-statistikk.
PostgreSQL aktivitetsstatistikk. PostgreSQL har to statistikker. Aktivitetsstatistikk som vil bli diskutert. Og planleggerstatistikk om datadistribusjon. Jeg vil snakke spesifikt om PostgreSQL-aktivitetsstatistikk, som lar oss bedømme ytelse og på en eller annen måte forbedre den.
Jeg skal fortelle deg hvordan du effektivt kan bruke statistikk for å løse en rekke problemer du har eller kan ha.
Hva vil ikke stå i rapporten? I rapporten vil jeg ikke berøre planleggerstatistikk, fordi... Dette er et eget emne for en egen rapport om hvordan data lagres i databasen og hvordan spørringsplanleggeren får en ide om de kvalitative og kvantitative egenskapene til disse dataene.
Og det vil ikke være noen verktøyanmeldelser, jeg vil ikke sammenligne ett produkt med et annet. Det blir ingen reklame. La oss legge det til side.
Jeg vil vise deg at det er nyttig å bruke statistikk. Det er nødvendig. Det er trygt å bruke. Alt vi trenger er vanlig SQL og grunnleggende kunnskap om SQL.
Og la oss snakke om hvilken statistikk du skal velge for å løse problemer.
Hvis vi ser på PostgreSQL og kjører kommandoen på operativsystemet for å se prosesser, vil vi se en "svart boks". Vi vil se noen prosesser som gjør noe, og ut fra navnet kan vi omtrent forestille oss hva de gjør der, hva de gjør. Men i hovedsak er det en svart boks; vi kan ikke se inn.
Vi kan se CPU-belastningen top, vi kan se på minneutnyttelse av noen systemverktøy, men vi vil ikke kunne se inne i PostgreSQL. Til dette trenger vi andre verktøy.
Og fortsetter videre, jeg vil fortelle deg hvor tiden brukes. Hvis vi ser for oss PostgreSQL i form av et slikt diagram, så kan vi svare på hvor tiden blir brukt. Dette er to ting: det behandler klientforespørsler fra applikasjoner og bakgrunnsoppgavene som PostgreSQL utfører for å holde seg i gang.
Hvis vi begynner å se øverst i venstre hjørne, kan vi se hvordan klientforespørsler behandles. Forespørselen kommer fra applikasjonen og en klientøkt åpnes for videre arbeid. Forespørselen sendes til planleggeren. Planleggeren bygger en spørringsplan. Sender den videre for utførelse. Det er en slags blokkdatainngang/-utgang knyttet til tabeller og indekser. De nødvendige dataene leses fra diskene inn i minnet til et spesielt område "delte buffere". Resultatene av forespørselen, hvis de er oppdateringer, slettinger, registreres i transaksjonsloggen i WAL. Noe statistisk informasjon havner i loggen eller statistikksamleren. Og resultatet av forespørselen sendes tilbake til klienten. Deretter kan klienten gjenta alt på nytt med en ny forespørsel.
Hva med bakgrunnsoppgaver og bakgrunnsprosesser? Vi har flere prosesser som holder databasen oppe og kjører i normal driftsmodus. Disse prosessene vil også bli berørt i rapporten: autovakuum, sjekkpunkt, replikeringsrelaterte prosesser, bakgrunnsskriver. Jeg vil berøre hver av dem mens jeg rapporterer.
Hvilke problemer er det med statistikk?
- Det er mye informasjon. PostgreSQL 9.4 gir 109 beregninger for visning av statistikkdata. Men hvis databasen lagrer mange tabeller, skjemaer, databaser, må alle disse beregningene multipliseres med det tilsvarende antallet tabeller, databaser. Det vil si at det er enda mer informasjon. Og det er veldig lett å drukne i det.
- Det neste problemet er at statistikken er representert av tellere. Ser vi på denne statistikken vil vi se stadig økende tellere. Og hvis det har gått mye tid siden statistikken ble tilbakestilt, vil vi se verdier i milliarder. Og de forteller oss ingenting.
- Ingen historie. Hvis du hadde en form for feil, noe falt for 15-30 minutter siden, vil du ikke kunne bruke statistikk og se hva som skjedde for 15-30 minutter siden. Dette er et problem.
- Mangelen på et verktøy innebygd i PostgreSQL er et problem. Kjerneutviklerne tilbyr ikke noe verktøy. De har ikke noe sånt. De gir ganske enkelt statistikk i databasen. Bruk den, legg en forespørsel til den, gjør hva du vil.
- Siden det ikke er noe verktøy innebygd i PostgreSQL, forårsaker dette et annet problem. Mange tredjepartsverktøy. Hvert selskap som har mer eller mindre direkte hender, prøver å skrive sitt eget program. Og som et resultat av dette har fellesskapet mange verktøy som kan brukes til å jobbe med statistikk. Og noen verktøy har visse muligheter, andre verktøy har ikke andre muligheter, eller det er noen nye muligheter. Og det oppstår en situasjon at du må bruke to, tre eller fire verktøy som overlapper hverandre og har ulike funksjoner. Dette er veldig ubehagelig.
Hva følger av dette? Det er viktig å kunne ta statistikk direkte, for ikke å være avhengig av programmer, eller på en eller annen måte forbedre disse programmene selv: legg til noen funksjoner for å få din egen fordel.
Og du trenger grunnleggende kunnskap om SQL. For å få noen data fra statistikk, må du lage SQL-spørringer, det vil si at du må vite hvordan select og join blir kompilert.
Statistikk forteller oss flere ting. De kan deles inn i kategorier.
- Den første kategorien er hendelser som skjer i databasen. Dette er når en hendelse oppstår i databasen: en forespørsel, tilgang til et bord, autovakuum, forplikter, så er dette alle hendelser. Tellerne som tilsvarer disse hendelsene økes. Og vi kan spore disse hendelsene.
- Den andre kategorien er egenskapene til objekter som tabeller og databaser. De har egenskaper. Dette er størrelsen på bordene. Vi kan spore veksten av tabeller og veksten av indekser. Vi kan se endringer i dynamikken.
- Og den tredje kategorien er tiden brukt på arrangementet. En forespørsel er en hendelse. Den har sitt eget spesifikke mål for varighet. Startet her, sluttet her. Vi kan spore det. Enten tiden det tar å lese en blokk fra disk eller skrive den. Slike ting blir også overvåket.
Kilder til statistikk er presentert som følger:
- I delt minne (delte buffere) er det et segment for lagring av statiske data, det er også de tellerne som hele tiden økes når visse hendelser inntreffer, eller noen øyeblikk oppstår i driften av databasen.
- Alle disse tellerne er ikke tilgjengelige for brukeren og ikke engang tilgjengelige for administratoren. Dette er ting på lavt nivå. For å få tilgang til dem tilbyr PostgreSQL et grensesnitt i form av SQL-funksjoner. Vi kan lage utvalgte kast ved å bruke disse funksjonene og få en slags metrikk (eller sett med metrikk).
- Det er imidlertid ikke alltid praktisk å bruke disse funksjonene, så funksjoner er grunnlaget for visninger (VIEWs). Dette er virtuelle tabeller som gir statistikk om et spesifikt delsystem, eller om et bestemt sett med hendelser i databasen.
- Disse innebygde visningene (VIEWs) er det primære brukergrensesnittet for arbeid med statistikk. De er tilgjengelige som standard uten noen ekstra innstillinger, du kan umiddelbart bruke dem, se på dem og ta informasjon fra dem. Og så er det bidrag. Bidrag er offisielle. Du kan installere postgresql-contrib-pakken (for eksempel postgresql94-contrib), laste den nødvendige modulen i konfigurasjonen, spesifisere parametere for den, starte PostgreSQL på nytt og du kan bruke den. (Merk. Avhengig av distribusjonen er bidragspakken i nyere versjoner en del av hovedpakken).
- Og det er uoffisielle bidrag. De er ikke inkludert i standard PostgreSQL-distribusjon. De må enten være kompilert eller installert som et bibliotek. Alternativene kan være svært forskjellige, avhengig av hva utvikleren av dette uoffisielle bidraget kom på.
Dette lysbildet presenterer alle disse VIEWS og noen av funksjonene som er tilgjengelige i PostgreSQL 9.4. Som vi ser, er det mange av dem. Og det er ganske lett å bli forvirret hvis du møter det for første gang.
Men hvis vi tar det forrige bildet Как тратится время на PostgreSQL og kompatibel med denne listen får vi dette bildet. Vi kan bruke hver visning (VIEWs) eller hver funksjon til et eller annet formål for å få den tilsvarende statistikken når PostgreSQL kjører. Og vi kan allerede få litt informasjon om driften av delsystemet.
Det første vi skal se på er pg_stat_database. Som vi kan se er dette en forestilling. Det er mye informasjon i den. Den mest varierte informasjonen. Og det gir svært nyttig kunnskap om hva som skjer i databasen vår.
Hvilke nyttige ting kan vi ta derfra? La oss starte med de enkleste tingene.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Det første vi kan se på er cache-treffprosenten. Buffertrefffrekvens er en nyttig beregning. Den lar deg estimere hvor mye data som tas fra den delte bufferens cache og hvor mye som leses fra disken.
Det er klart det jo flere cache-treff vi har, jo bedre. Vi måler denne beregningen i prosent. Og for eksempel, hvis prosentandelen vår av disse cachetreffene er mer enn 90 %, så er dette bra. Hvis den faller under 90 %, betyr det at vi ikke har nok minne til å holde det varme datahodet i minnet. Og for å bruke disse dataene, er PostgreSQL tvunget til å få tilgang til disken, og dette er tregere enn om dataene ble lest fra minnet. Og du må tenke på å øke minnet: enten øke delte buffere eller øke maskinvareminnet (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Hva annet kan du ta fra denne forestillingen? Du kan se uregelmessigheter i databasen. Hva vises her? Det er forpliktelser, tilbakeføringer, opprettelse av midlertidige filer, deres størrelse, vranglås og konflikter.
Vi kan bruke denne forespørselen. Denne SQL-en er ganske enkel. Og vi kan se på disse dataene her.
Og her er terskelverdiene. Vi ser på forholdet mellom forpliktelser og tilbakeføringer. Commits er en vellykket bekreftelse av en transaksjon. Tilbakeføringer er en tilbakeføring, det vil si at en transaksjon gjorde noe arbeid, anstrengte databasen, beregnet noe, og så oppsto en feil og resultatene av transaksjonen forkastes. Det er antall tilbakeføringer som stadig øker er dårlig. Og du bør liksom unngå dem, og redigere koden slik at dette ikke skjer.
Konflikter er relatert til replikering. Og de bør også unngås. Hvis du har noen spørringer som utføres på en replika og det oppstår konflikter, må du sortere ut disse konfliktene og se hva som skjer. Detaljer finner du i loggene. Og eliminer konfliktsituasjoner slik at applikasjonsforespørsler fungerer uten feil.
Vranglås er også en dårlig situasjon. Når forespørsler kjemper om ressurser, fikk en forespørsel tilgang til en ressurs og tok låsen, en andre forespørsel fikk tilgang til den andre ressursen og tok også låsen, og deretter fikk begge forespørslene tilgang til hverandres ressurser og blokkerte mens de ventet på at naboen skulle frigjøre låsen. Dette er også en problematisk situasjon. De må behandles på nivået med å omskrive applikasjoner og serialisere tilgang til ressurser. Og hvis du ser at vranglåsene dine stadig øker, må du se på detaljene i loggene, analysere situasjonene som oppstår og se hva problemet er.
Midlertidige filer (temp_files) er også dårlige. Når en brukerforespørsel ikke har nok minne til å romme operative, midlertidige data, oppretter den en fil på disken. Og alle operasjonene den kan utføre i en midlertidig buffer i minnet begynner å bli utført på disken. Det er tregt. Dette øker utførelsestiden for spørringen. Og klienten som sendte en forespørsel til PostgreSQL vil få svar litt senere. Hvis alle disse operasjonene utføres i minnet, vil Postgres reagere mye raskere og klienten vil vente mindre.
Pg_stat_bgwriter - Denne visningen beskriver driften av to PostgreSQL-bakgrunnsundersystemer: dette checkpointer и background writer.
La oss først se på kontrollpunktene, de såkalte. checkpoints. Hva er kontrollpunkter? Et sjekkpunkt er en posisjon i transaksjonsloggen som indikerer at alle dataendringer registrert i loggen er vellykket synkronisert med dataene på disken. Prosessen, avhengig av arbeidsmengde og innstillinger, kan være langvarig og består for det meste av synkronisering av skitne sider i delte buffere med datafiler på disk. Hva er den til? Hvis PostgreSQL stadig hadde tilgang til disken og hentet data derfra, og skrev data på hver tilgang, ville det gå tregt. Derfor har PostgreSQL et minnesegment hvis størrelse avhenger av innstillingene i konfigurasjonen. Postgres lagrer live data i dette minnet for senere behandling eller spørring. Ved forespørsler om å endre dataene, endres de. Og vi får to versjoner av dataene. Det ene er i minnet vårt, det andre er på disk. Og med jevne mellomrom må du synkronisere disse dataene. Vi må synkronisere det som er endret i minnet til disk. For dette trenger du sjekkpunkter.
Sjekkpunkt går gjennom delte buffere, markerer skitne sider som de er nødvendige for sjekkpunkt. Deretter starter den en andre passasje gjennom de delte bufferne. Og sidene som er merket for sjekkpunkt, den synkroniserer dem allerede. På denne måten blir dataene synkronisert med disken.
Det finnes to typer sjekkpunkter. Ett sjekkpunkt utføres med en timeout. Dette sjekkpunktet er nyttig og bra – checkpoint_timed. Og det er sjekkpunkter på forespørsel - checkpoint required. Dette sjekkpunktet oppstår når vi har en veldig stor datapost. Vi registrerte mange transaksjonslogger. Og PostgreSQL mener at den må synkronisere alt dette så raskt som mulig, lage et sjekkpunkt og gå videre.
Og hvis du så på statistikken pg_stat_bgwriter og så hva du har checkpoint_req er mye større enn checkpoint_timed, så er dette dårlig. Hvorfor dårlig? Dette betyr at PostgreSQL er under konstant stress når den trenger å skrive data til disk. Timeout-sjekkpunkt er mindre stressende og utføres i henhold til den interne tidsplanen og er på en måte spredt over tid. PostgreSQL har muligheten til å sette arbeidet på pause og ikke belaste diskundersystemet. Dette er nyttig for PostgreSQL. Og spørringer som utføres under sjekkpunkt vil ikke oppleve stress fra det faktum at diskundersystemet er opptatt.
Og for å justere sjekkpunktet er det tre parametere:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
De lar deg regulere driften av kontrollpunktene. Men jeg vil ikke dvele ved dem. Deres innflytelse er et eget tema.
Advarsel: Versjon 9.4 omtalt i rapporten er ikke lenger relevant. I moderne versjoner av PostgreSQL er parameteren checkpoint_segments erstattet av parametere min_wal_size и max_wal_size.
Det neste undersystemet er bakgrunnsforfatteren − background writer. Hva er det han gjør? Den kjører konstant i en endeløs loop. Skanner sider i delte buffere og dumper skitne sider som den finner til disk. Dermed hjelper det sjekkpointeren til å gjøre mindre arbeid under utførelse av sjekkpunkt.
Hva mer trengs det til? Det gir behov for tomme sider i delte buffere hvis de plutselig kreves (i store mengder og umiddelbart) for å romme data. Anta at det oppsto en situasjon da blanke sider var nødvendig for å fullføre en forespørsel og de allerede var i de delte bufferne. Postgresive backend han bare plukker dem opp og bruker dem, han trenger ikke rense noe selv. Men hvis det plutselig ikke er slike sider, stopper backend arbeidet og begynner å søke etter sider for å dumpe dem på disken og ta dem til egne behov - noe som negativt påvirker tidspunktet for den aktuelle forespørselen. Hvis du ser at du har en parameter maxwritten_clean stor, betyr dette at bakgrunnsforfatteren ikke gjør jobben sin, og du må øke parameterne bgwriter_lru_maxpages, slik at han kan gjøre mer arbeid i en syklus, slett flere sider.
Og en annen veldig nyttig indikator er buffers_backend_fsync. Backends fsynkroniserer ikke fordi det er tregt. De sender fsync opp IO-stabelkontrolleren. Sjekkpekeren har sin egen kø, den behandler periodisk fsync og synkroniserer sider i minnet med filer på disk. Hvis køen ved sjekkpunktet er stor og full, tvinges backend til å gjøre fsync selv og dette bremser arbeidet til backend, det vil si at klienten vil få svar senere enn den kunne. Hvis du ser at verdien din er større enn null, er dette allerede et problem og du må ta hensyn til bakgrunnsskriverens innstillinger og også evaluere ytelsen til diskundersystemet.
Advarsel: _Den følgende teksten beskriver de statistiske visningene knyttet til replikering. De fleste visnings- og funksjonsnavnene ble omdøpt i Postgres 10. Essensen av omdøpingen var å erstatte xlog på wal и location på lsn i funksjons-/visningsnavn osv. Spesielt eksempel, funksjon pg_xlog_location_diff() ble omdøpt til pg_wal_lsn_diff()._
Vi har mye her også. Men vi trenger bare varer knyttet til plassering.
Hvis vi ser at alle verdier er like, er dette et ideelt alternativ, og kopien henger ikke etter mesteren.
Denne heksadesimale posisjonen her er posisjonen i transaksjonsloggen. Den øker stadig hvis det er aktivitet i databasen: setter inn, sletter osv.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Hvis disse tingene er forskjellige, er det en slags etterslep. Lag er forsinkelsen mellom replikaen og masteren, dvs. dataene varierer mellom servere.
Det er tre årsaker til etterslepet:
- Dette diskundersystemet kan ikke takle synkronisering av opptaksfil.
- Dette er mulige nettverksfeil, eller nettverksoverbelastning, når dataene ikke har tid til å nå kopien og den ikke kan reprodusere den.
- Og prosessoren. Prosessoren er et svært sjeldent tilfelle. Og jeg så dette to eller tre ganger, men dette kan også skje.
Og her er tre spørringer som lar oss bruke statistikk. Vi kan anslå hvor mye vi har registrert i transaksjonsloggen. Det er en slik funksjon pg_xlog_location_diff og vi kan estimere replikasjonsforsinkelsen i byte og sekunder. Vi bruker også verdien fra denne visningen (VIEWs) for dette.
Merk: _I stedet for pg_xlog_locationDiff()-funksjonen kan bruke subtraksjonsoperatoren og trekke en plassering fra en annen. Komfortabel.
Det er ett poeng med etterslepet, som er i sekunder. Hvis det ikke er noen aktivitet på masteren, var transaksjonen der for ca. 15 minutter siden og det er ingen aktivitet, og hvis vi ser på denne forsinkelsen på kopien, vil vi se en forsinkelse på 15 minutter. Dette er verdt å huske. Og dette kan være forvirrende når du ser dette etterslepet.
Pg_stat_all_tables er en annen nyttig visning. Det viser statistikk på tabeller. Når vi har tabeller i databasen, er det noe aktivitet med den, noen handlinger, vi kan hente denne informasjonen fra denne visningen.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;Det første vi kan se på er de sekvensielle skanningene over bordet. Selve tallet etter disse passeringene er ikke nødvendigvis dårlig og er ikke en indikator på at vi trenger å gjøre noe.
Imidlertid er det en annen beregning - seq_tup_read. Dette er antallet rader som returneres fra den sekvensielle skanningen. Hvis det gjennomsnittlige antallet overstiger 1 000, 10 000, 50 000, 100 000, så er dette allerede en indikator på at du kanskje må bygge en indeks et sted slik at spørringer er basert på indeksen, eller det er mulig å optimalisere spørringer som bruker slike sekvensielle skanninger. at dette ikke skjer var.
Et enkelt eksempel - la oss si en forespørsel med en stor OFFSET og LIMIT kostnader. For eksempel skannes 100 000 rader i en tabell og deretter tas 50 000 nødvendige rader, og de forrige skannede radene forkastes. Dette er også en dårlig sak. Og slike spørsmål må optimaliseres. Og her er en enkel SQL-spørring der du kan se på dette og evaluere de resulterende tallene.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Tabellstørrelser kan også fås ved å bruke denne tabellen og ved å bruke tilleggsfunksjoner pg_total_relation_size(), pg_relation_size().
Generelt er det metakommandoer dt и di, som kan brukes i PSQL og også se størrelsene på tabeller og indekser.
Men å bruke funksjoner hjelper oss å se på størrelsene på tabeller, selv med hensyn til indekser, eller uten å ta hensyn til indekser, og allerede gjøre noen estimater basert på veksten til databasen, dvs. hvordan den vokser, med hvilken intensitet, og trekke noen konklusjoner om dimensjoneringsoptimalisering.
Registrering av aktivitet. Hva er et opptak? La oss se på operasjonen UPDATE – operasjonen med å oppdatere rader i en tabell. Faktisk er oppdatering to operasjoner (eller enda flere). Dette er å sette inn en ny versjon av raden og merke den gamle versjonen av raden som foreldet. Deretter vil autovakuum komme og rense ut disse utdaterte versjonene av linjene, og markere dette stedet som tilgjengelig for gjenbruk.
I tillegg handler oppdatering ikke bare om å oppdatere en tabell. Dette er også en indeksoppdatering. Hvis du har mange indekser på tabellen, må alle indekser som inneholder felt oppdatert i spørringen også oppdateres under oppdatering. Disse indeksene vil også ha foreldede versjoner av rader som må ryddes opp.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;Og på grunn av det nye designet er UPDATE en tungvektsoperasjon. Men de kan gjøres enklere. Spise hot updates. De dukket opp i PostgreSQL versjon 8.3. Og hva er dette? Dette er en lett oppdatering som ikke fører til at indekser gjenoppbygges. Det vil si at vi oppdaterte posten, men bare posten på siden (som tilhører tabellen) ble oppdatert, og indeksene peker fortsatt til den samme posten på siden. Det er litt av en interessant driftslogikk: når et vakuum kommer, skaper det disse kjedene hot gjenoppbygges og alt fortsetter å fungere uten å oppdatere indekser, og alt skjer med mindre sløsing med ressurser.
Og når gjør du det n_tup_hot_upd stor, så er det veldig bra. Dette betyr at lette oppdateringer dominerer og dette er billigere for oss når det gjelder ressurser og alt er i orden.
ALTER TABLE table_name SET (fillfactor = 70);Hvordan øke volumet hot updateov? Vi kan bruke fillfactor. Den bestemmer størrelsen på den reserverte ledige plassen når du fyller en side i en tabell ved hjelp av INSERT. Når innlegg legges til i en tabell, fyller de hele siden og etterlater ingen tom plass. Deretter utheves en ny side. Dataene fylles ut igjen. Og dette er standard oppførsel, fillfactor = 100%.
Vi kan gjøre fyllfaktoren 70%. Det vil si at under innsettinger ble en ny side uthevet, men bare 70 % av siden ble fylt. Og vi har 30 % igjen som reserve. Når du skal foreta en oppdatering vil det mest sannsynlig skje på samme side, og den nye versjonen av linjen får plass på samme side. Og hot_update vil bli gjort. Dette gjør det lettere å skrive på tabeller.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Autovakuum kø. Autovacuum er et undersystem som det er svært lite statistikk for i PostgreSQL. Vi kan kun se i tabellene i pg_stat_activity hvor mange støvsugere vi har for øyeblikket. Det er imidlertid svært vanskelig å forstå hvor mange bord som står i køen med en gang.
Merk: _Fra og med Postgres 10 har situasjonen med Vatovac-sporing forbedret seg betraktelig - pg_stat_progress-visningen har dukket oppvakuum, noe som betydelig forenkler spørsmålet om overvåking av bilens vakuum.
Vi kan bruke denne forenklede spørringen. Og vi kan se når vakuumet må lages. Men hvordan og når skal vakuumet starte? Dette er de eldre versjonene av linjene jeg snakket om tidligere. Oppdatering skjedde, en ny versjon av linjen ble satt inn. En utdatert versjon av strengen har dukket opp. I bordet pg_stat_user_tables det er en slik parameter n_dead_tup. Den viser antall "døde" linjer. Og så snart antallet døde rader blir større enn en viss terskel, vil et autovakuum komme til bordet.
Og hvordan beregnes denne terskelen? Dette er en veldig spesifikk prosentandel av det totale antallet rader i tabellen. Det er en parameter autovacuum_vacuum_scale_factor. Det bestemmer prosenten. La oss si 10 % + det er en ekstra grunnleggende terskel på 50 linjer. Og hva skjer? Når vi har flere døde rader enn "10% + 50" av alle rader i tabellen, setter vi tabellen på autovakuum.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Det er imidlertid ett poeng. Grunnleggende terskler for parametere av_base_thresh и av_scale_factor kan tildeles individuelt. Og følgelig vil terskelen ikke være global, men individuell for bordet. Derfor, for å beregne, må du bruke triks og triks. Og hvis du er interessert, så kan du se på opplevelsen til våre kolleger fra Avito (lenken på lysbildet er ugyldig og har blitt oppdatert i teksten).
De skrev for , som tar hensyn til disse tingene. Det er en to-arks fotklut der. Men den regner riktig og ganske effektivt lar oss vurdere hvor vi trenger mye vakuum for bord der det er lite.
Hva kan vi gjøre med det? Hvis vi har en stor kø og autovakuumet ikke takler det, kan vi øke antallet vakuumarbeidere, eller rett og slett gjøre vakuumet mer aggressivt, slik at den utløses tidligere, behandler bordet i små biter. Og dermed vil køen minke. — Hovedsaken her er å overvåke belastningen på diskene, fordi... Vakuum er ikke en gratis ting, selv om problemet med SSD/NVMe-enheter har blitt mindre merkbart.
Pg_stat_all_indexes er statistikk over indekser. Hun er ikke stor. Og vi kan bruke den til å få informasjon om bruken av indekser. Og for eksempel kan vi bestemme hvilke indekser vi har ekstra.
Som jeg allerede har sagt, oppdatering er ikke bare en oppdatering av tabeller, det er også en oppdatering av indekser. Følgelig, hvis vi har mange indekser på tabellen, så når du oppdaterer radene i tabellen, må indeksene til de indekserte feltene også oppdateres, og hvis vi har ubrukte indekser som det ikke er indeksskanninger for, så henger de som ballast. Og vi må bli kvitt dem. Til dette trenger vi et felt idx_scan. Vi ser ganske enkelt på antall indeksskanninger. Hvis indekser har null skanninger over en relativt lang periode med statistikklagring (minst 2-3 uker), så er dette mest sannsynlig dårlige indekser, vi må bli kvitt dem.
Merk: Når du søker etter ubrukte indekser i tilfelle av streaming-replikeringsklynger, må du sjekke alle klyngennoder, fordi statistikk er ikke global, og hvis indeksen ikke brukes på masteren, kan den brukes på replikaer (hvis det er en belastning der).
To linker:
Dette er mer avanserte spørringseksempler på hvordan du slår opp ubrukte indekser.
Den andre lenken er en ganske interessant forespørsel. Det er en veldig ikke-triviell logikk der. Jeg anbefaler det for referanse.
Hva annet er verdt å oppsummere ved å bruke indekser?
-
Ubrukte indekser er dårlige.
-
De tar plass.
-
Senk oppdateringsoperasjonen.
-
Ekstra arbeid for støvsugeren.
Hvis vi fjerner ubrukte indekser, vil vi bare gjøre databasen bedre.
Neste presentasjon er pg_stat_activity. Dette er en analog av verktøyet ps, bare i PostgreSQL. Hvis ps'om du ser på prosessene i operativsystemet, da pg_stat_activity Den vil vise deg aktiviteten i PostgreSQL.
Hvilke nyttige ting kan vi ta derfra?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Vi kan se den generelle aktiviteten, hva som skjer i databasen. Vi kan gjøre en ny distribusjon. Alt her har eksplodert, nye tilkoblinger aksepteres ikke, feil strømmer inn i applikasjonen.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Vi kan kjøre en spørring som dette og se den totale prosentandelen av tilkoblinger i forhold til maksimal tilkoblingsgrense og se hvem som har flest tilkoblinger. Og i dette gitte tilfellet ser vi den brukeren cron_role åpnet 508 forbindelser. Og noe skjedde med ham der. Vi må forholde oss til det og se på det. Og det er ganske mulig at dette er en slags unormalt antall forbindelser.
Hvis vi har en OLTP-arbeidsmengde, skal spørringene være raske, veldig raske og det skal ikke være lange spørringer. Men hvis det oppstår lange spørsmål, er det på kort sikt ingenting å bekymre seg for, men I det lange løp skader lange spørringer databasen; de øker oppblåsningseffekten til tabeller når tabellfragmentering oppstår. Du må bli kvitt både oppblåsthet og lange spørsmål.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Vennligst merk: med denne forespørselen kan vi identifisere lange spørsmål og transaksjoner. Vi bruker funksjonen clock_timestamp() for å bestemme driftstiden. Lange spørsmål vi fant, vi kan huske dem, oppfylle dem explain, se på planene og optimaliser på en eller annen måte. Vi skyter ned gjeldende lange forespørsler og går videre med livene våre.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Dårlige transaksjoner er transaksjoner i inaktiv i transaksjon og inaktiv i transaksjon (avbrutt) tilstander.
Hva betyr det? Transaksjoner har flere tilstander. Og en av disse tilstandene kan antas når som helst. Det er et felt for å definere tilstander state i denne presentasjonen. Og vi bruker den til å bestemme staten.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Og, som jeg sa ovenfor, disse to statene inaktiv i transaksjon og inaktiv i transaksjon (avbrutt) er dårlige. Hva det er? Dette er når applikasjonen åpnet en transaksjon, gjorde noen handlinger og gikk i gang. Transaksjonen forblir åpen. Den henger, ingenting skjer i den, den tar opp forbindelsen, låser på endrede rader og øker potensielt oppblåsningen av andre tabeller, på grunn av arkitekturen til Postrges-transaksjonsmotoren. Og slike transaksjoner bør også skytes ned, for de er generelt skadelige, uansett.
Hvis du ser at du har mer enn 5-10-20 av dem i databasen din, må du bekymre deg og begynne å gjøre noe med dem.
Her bruker vi også for beregningstiden clock_timestamp(). Vi skyter transaksjoner og optimerer applikasjonen.
Som jeg sa ovenfor, er blokkering når to eller flere transaksjoner kjemper om en eller en gruppe ressurser. Til dette har vi et felt waiting med boolsk verdi true eller false.
Sant – dette betyr at prosessen venter, noe må gjøres. Når en prosess venter, betyr det at klienten som startet denne prosessen også venter. Klienten sitter i nettleseren og venter også.
Advarsel: _Starter fra Postgres versjon 9.6-feltet waiting fjernet og ytterligere to informative felt lagt til i stedet wait_event_type и wait_event._
Hva gjør jeg? Hvis du ser sant i lang tid, betyr det at du må kvitte deg med slike forespørsler. Vi skyter rett og slett ned slike transaksjoner. Vi skriver til utviklerne at de på en eller annen måte må optimalisere slik at det ikke blir et kappløp om ressurser. Og så optimerer utviklerne applikasjonen slik at dette ikke skjer.
Og den ekstreme, men potensielt ikke-dødelige saken er forekomst av vranglås. To transaksjoner oppdaterte to ressurser, og fikk deretter tilgang til dem igjen, denne gangen til motsatte ressurser. I dette tilfellet dreper PostgreSQL selve transaksjonen slik at en annen kan fortsette å jobbe. Dette er en blindveissituasjon, og hun kan ikke finne ut av det på egen hånd. Derfor er PostgreSQL tvunget til å ta ekstreme tiltak.
Og her er to spørsmål som lar deg spore blokkering. Vi bruker utsikt pg_locks, som lar deg spore tunge låser.
Og den første lenken er selve forespørselsteksten. Den er ganske lang.
Og den andre lenken er en artikkel om låser. Det er nyttig å lese, det er veldig interessant.
Så hva ser vi? Vi ser to forespørsler. Transaksjon med ALTER TABLE er en blokkerende transaksjon. Den startet, men ble ikke fullført, og applikasjonen som registrerte denne transaksjonen gjør andre ting et sted. Og den andre forespørselen er oppdatering. Han venter på at alterbordet skal avsluttes før han kan fortsette arbeidet.
Slik kan vi finne ut hvem som låste hvem, som holder hvem, og vi kan håndtere det videre.
Neste modul er pg_stat_statements. Som sagt, dette er en modul. For å bruke den må du laste inn biblioteket i konfigurasjonen, starte PostgreSQL på nytt, installere modulen (med én kommando) og så får vi en ny visning.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Hva kan vi ta derfra? Hvis vi snakker om enkle ting, kan vi ta den gjennomsnittlige utføringstiden for spørringen. Tiden vokser, noe som betyr at PostgreSQL reagerer sakte og vi må gjøre noe.
Vi kan se på de mest aktive skrivetransaksjonene i databasen som endrer data i delte buffere. Se hvem som oppdaterer eller sletter data der.
Og vi kan ganske enkelt se på forskjellige statistikker for disse forespørslene.
Vi pg_stat_statements Vi bruker den til å lage rapporter. Vi nullstiller statistikken en gang om dagen. La oss samle det. Før vi tilbakestiller statistikken neste gang, la oss lage en rapport. Her er en lenke til rapporten. Du kan se den.
Hva gjør vi? Vi beregner generell statistikk for alle forespørsler. Deretter, for hver forespørsel, teller vi dets individuelle bidrag til denne samlede statistikken.
Og hva kan vi se? Vi kan se på den totale utførelsestiden for alle forespørsler av en bestemt type på bakgrunn av alle andre forespørsler. Vi kan se på CPU- og I/O-ressursbruken i forhold til helhetsbildet. Og optimaliser allerede disse spørringene. Vi bygger de mest populære spørringene basert på denne rapporten og får allerede tankevekker om hva vi skal optimalisere.
Hva har vi igjen bak kulissene? Det er fortsatt noen få bidrag igjen som jeg ikke har vurdert fordi tiden er begrenset.
Det er pgstattuple er også en tilleggsmodul fra standard bidragspakke. Det lar deg evaluere bloat tabeller, såkalte tabellfragmentering. Og hvis det er mye fragmentering, må du fjerne det og bruke forskjellige verktøy. Og funksjon pgstattuple fungerer lenge. Og jo flere bord det er, jo lenger vil det fungere.
Neste bidrag er pg_buffercache. Den lar deg inspisere delte buffere: hvor intensivt og for hvilke tabeller buffersider brukes. Og det lar deg ganske enkelt se på delte buffere og evaluere hva som skjer der.
Neste modul er pgfincore. Den tillater bordoperasjoner på lavt nivå via et systemanrop mincore(), dvs. den lar deg laste en tabell inn i delte buffere, eller laste den ut. Og det tillater blant annet å inspisere operativsystemets sidebuffer, dvs. hvor mye plass tabellen opptar i sidebuffer, i delte buffere, og lar oss ganske enkelt evaluere arbeidsmengden til tabellen.
Neste modul – pg_stat_kcache. Den bruker også et systemanrop getrusage(). Og den utfører den før og etter at forespørselen er utført. Og i den resulterende statistikken lar den oss estimere hvor mye forespørselen vår brukte på disk I/O, dvs. operasjoner med filsystemet og ser på prosessorbruken. Imidlertid er modulen ung (hostehoste) og for driften krever den PostgreSQL 9.4 og pg_stat_statements, som jeg nevnte tidligere.
-
Det er nyttig å vite hvordan man bruker statistikk. Du trenger ikke tredjepartsprogrammer. Du kan komme inn, se, gjøre noe, oppnå noe.
-
Å bruke statistikk er ikke vanskelig, det er bare vanlig SQL. Du samlet forespørselen, kompilerte den, sendte den, så på den.
-
Statistikk hjelper deg med å svare på spørsmål. Hvis du har spørsmål, går du til statistikk - se, trekk konklusjoner, analyser resultatene.
-
Og eksperimentere. Det er mange forespørsler, mye data. Du kan alltid optimalisere et eksisterende søk. Du kan lage din egen versjon av forespørselen som passer deg mer enn originalen og bruke den.
referanser
Egnede lenker som ble funnet i artikkelen, basert på materialer, var i rapporten.
Forfatter skriv mer
(eng)
Statistikksamleren
Systemadministrasjonsfunksjoner
Bidragsmoduler
SQL utils og sql-kode eksempler
Takk skal du ha for din oppmerksomhet!
Kilde: www.habr.com