🥇 Dyb dyk ned i PostgreSQL interne statistikker. Alexey Lesovsky

Transskription af Alexey Lesovskys rapport fra 2015 "Dyb dyk ind i PostgreSQL intern statistik"

Ansvarsfraskrivelse fra forfatteren af rapporten: Jeg bemærker, at denne rapport er dateret november 2015 - der er gået mere end 4 år, og der er gået meget tid. Den version 9.4, der er omtalt i rapporten, understøttes ikke længere. I løbet af de seneste 4 år er der udgivet 5 nye udgivelser, hvor der er en masse nyskabelser, forbedringer og ændringer vedrørende statistik, og noget af materialet er forældet og ikke relevant. Mens jeg anmelder, forsøgte jeg at markere disse steder for ikke at vildlede læseren. Jeg har ikke omskrevet disse passager, der er mange af dem, og resultatet bliver en helt anden rapport.

PostgreSQL DBMS er en enorm mekanisme, og denne mekanisme består af mange undersystemer, hvis koordinerede drift direkte påvirker DBMS'ens ydeevne. Under driften indsamles statistik og information om driften af komponenter, som giver dig mulighed for at evaluere effektiviteten af PostgreSQL og træffe foranstaltninger til at forbedre ydeevnen. Der er dog mange af disse oplysninger, og de præsenteres i en ret forenklet form. At behandle disse oplysninger og fortolke dem er nogle gange en fuldstændig ikke-triviel opgave, og "zoo" af værktøjer og hjælpeprogrammer kan nemt forvirre selv en avanceret DBA.

God eftermiddag Mit navn er Aleksey. Som Ilya sagde, vil jeg tale om PostgreSQL-statistikker.

PostgreSQL aktivitetsstatistik. PostgreSQL har to statistikker. Aktivitetsstatistik, der vil blive diskuteret. Og planlægningsstatistik om datadistribution. Jeg vil tale specifikt om PostgreSQL aktivitetsstatistik, som giver os mulighed for at bedømme præstation og på en eller anden måde forbedre den.

Jeg vil fortælle dig, hvordan du effektivt bruger statistik til at løse en række problemer, som du har eller kan have.

Hvad vil ikke være i rapporten? I rapporten vil jeg ikke komme ind på planlægningsstatistik, fordi... Dette er et separat emne for en separat rapport om, hvordan data gemmes i databasen, og hvordan forespørgselsplanlæggeren får en idé om de kvalitative og kvantitative karakteristika ved disse data.

Og der vil ikke være nogen værktøjsanmeldelser, jeg vil ikke sammenligne et produkt med et andet. Der vil ikke være nogen reklame. Lad os lægge det til side.

Jeg vil gerne vise dig, at det er nyttigt at bruge statistik. Er det nødvendigt. Det er sikkert at bruge. Alt hvad vi behøver er almindelig SQL og grundlæggende kendskab til SQL.

Og lad os tale om, hvilken statistik vi skal vælge for at løse problemer.

Hvis vi ser på PostgreSQL og kører kommandoen på operativsystemet for at se processer, vil vi se en "sort boks". Vi vil se nogle processer, der gør noget, og ud fra navnet kan vi nogenlunde forestille os, hvad de laver der, hvad de laver. Men i bund og grund er det en sort boks, vi kan ikke se ind.

Vi kan se CPU-belastningen ind top, vi kan se på hukommelsesudnyttelse af nogle systemværktøjer, men vi vil ikke være i stand til at se inde i PostgreSQL. Til dette har vi brug for andre værktøjer.

Og fortsætter jeg videre, vil jeg fortælle dig, hvor tiden bliver brugt. Hvis vi forestiller os PostgreSQL i form af sådan et diagram, så kan vi svare på, hvor tiden bliver brugt. Det er to ting: det behandler klientanmodninger fra applikationer og de baggrundsopgaver, som PostgreSQL udfører for at holde sig selv kørende.

Hvis vi begynder at se i øverste venstre hjørne, kan vi se, hvordan klientanmodninger behandles. Anmodningen kommer fra applikationen, og en klientsession åbnes for videre arbejde. Anmodningen sendes til planlæggeren. Planlæggeren opbygger en forespørgselsplan. Sender den videre til udførelse. Der er en slags blokdatainput/-output forbundet med tabeller og indekser. De nødvendige data læses fra diskene ind i hukommelsen til et særligt område "delte buffere". Resultaterne af anmodningen, hvis de er opdateringer, sletninger, registreres i transaktionsloggen i WAL. Nogle statistiske oplysninger ender i loggen eller statistiksamleren. Og resultatet af anmodningen sendes tilbage til klienten. Hvorefter klienten kan gentage alt igen med en ny anmodning.

Hvad med baggrundsopgaver og baggrundsprocesser? Vi har flere processer, der holder databasen oppe og køre i normal driftstilstand. Disse processer vil også blive berørt i rapporten: autovakuum, checkpointer, replikationsrelaterede processer, baggrundsforfatter. Jeg vil berøre hver af dem, mens jeg rapporterer.

Hvilke problemer er der med statistik?

Der er meget information. PostgreSQL 9.4 giver 109 metrics til visning af statistikdata. Men hvis databasen gemmer mange tabeller, skemaer, databaser, så skal alle disse metrics ganges med det tilsvarende antal tabeller, databaser. Det vil sige, at der er endnu flere oplysninger. Og det er meget nemt at drukne i det.
Det næste problem er, at statistik er repræsenteret af tællere. Hvis vi ser på disse statistikker, vil vi se konstant stigende tællere. Og hvis der er gået lang tid siden statistikken blev nulstillet, vil vi se værdier i milliarder. Og de fortæller os ikke noget.
Ingen historie. Hvis du havde en form for fejl, faldt noget for 15-30 minutter siden, du vil ikke være i stand til at bruge statistik og se, hvad der skete for 15-30 minutter siden. Dette er et problem.
Manglen på et værktøj indbygget i PostgreSQL er et problem. Kerneudviklerne leverer ikke noget hjælpeprogram. Sådan noget har de ikke. De leverer blot statistik i databasen. Brug det, bed om det, gør hvad du vil.
Da der ikke er noget værktøj indbygget i PostgreSQL, forårsager dette et andet problem. Masser af tredjepartsværktøjer. Hver virksomhed, der har mere eller mindre direkte hænder, forsøger at skrive sit eget program. Og som følge heraf har fællesskabet en masse værktøjer, der kan bruges til at arbejde med statistik. Og nogle værktøjer har visse egenskaber, andre værktøjer har ikke andre muligheder, eller der er nogle nye muligheder. Og der opstår en situation, hvor man skal bruge to, tre eller fire værktøjer, der overlapper hinanden og har forskellige funktioner. Det er meget ubehageligt.

Hvad følger deraf? Det er vigtigt at kunne tage statistik direkte, for ikke at være afhængig af programmer, eller på en eller anden måde selv forbedre disse programmer: Tilføj nogle funktioner for at få din egen fordel.

Og du har brug for grundlæggende kendskab til SQL. For at få nogle data fra statistik, skal du oprette SQL-forespørgsler, dvs. du skal vide, hvordan select og join er kompileret.

Statistik fortæller os flere ting. De kan opdeles i kategorier.

Den første kategori er hændelser, der forekommer i databasen. Det er, når en hændelse opstår i databasen: en anmodning, adgang til en tabel, autovacuum, commits, så er disse alle hændelser. Tællerne, der svarer til disse hændelser, øges. Og vi kan spore disse begivenheder.
Den anden kategori er egenskaberne for objekter såsom tabeller og databaser. De har egenskaber. Dette er størrelsen på bordene. Vi kan spore væksten af tabeller og væksten af indekser. Vi kan se ændringer i dynamikken.
Og den tredje kategori er den tid, der bruges på arrangementet. En anmodning er en begivenhed. Det har sit eget specifikke mål for varighed. Startede her, sluttede her. Vi kan spore det. Enten den tid det tager at læse en blok fra disken eller skrive den. Sådanne ting spores også.

Kilder til statistik er præsenteret som følger:

I delt hukommelse (delte buffere) er der et segment til lagring af statiske data, der er også de tællere, der konstant inkrementeres, når visse hændelser opstår, eller der opstår nogle øjeblikke i driften af databasen.
Alle disse tællere er ikke tilgængelige for brugeren og ikke engang tilgængelige for administratoren. Det er ting på lavt niveau. For at få adgang til dem giver PostgreSQL en grænseflade i form af SQL-funktioner. Vi kan lave udvalgte kast ved hjælp af disse funktioner og få en form for metrik (eller et sæt af metrics).
Det er dog ikke altid praktisk at bruge disse funktioner, så funktioner er grundlaget for visninger (VIEWs). Disse er virtuelle tabeller, der giver statistik om et specifikt undersystem eller om et bestemt sæt hændelser i databasen.
Disse indlejrede visninger (VIEWs) er den primære brugergrænseflade til at arbejde med statistik. De er tilgængelige som standard uden yderligere indstillinger, du kan straks bruge dem, se på dem og tage information fra dem. Og så er der bidrag. Bidrag er officielle. Du kan installere postgresql-contrib-pakken (for eksempel postgresql94-contrib), indlæse det nødvendige modul i konfigurationen, specificere parametre for det, genstarte PostgreSQL, og du kan bruge det. (Bemærk. Afhængigt af distributionen er bidragspakken i de seneste versioner en del af hovedpakken).
Og der er uofficielle bidrag. De er ikke inkluderet i standard PostgreSQL-distributionen. De skal enten være kompileret eller installeret som et bibliotek. Mulighederne kan være meget forskellige, afhængigt af hvad udvikleren af dette uofficielle bidrag fandt på.

Dette slide præsenterer alle disse VIEWS og nogle af de funktioner, der er tilgængelige i PostgreSQL 9.4. Som vi ser, er der mange af dem. Og det er ret nemt at blive forvirret, hvis du støder på det for første gang.

Men hvis vi tager det forrige billede Как тратится время на PostgreSQL og kompatibel med denne liste, får vi dette billede. Vi kan bruge hver visning (VIEWs) eller hver funktion til et eller andet formål for at få den tilsvarende statistik, når PostgreSQL kører. Og vi kan allerede få nogle oplysninger om driften af delsystemet.

Det første vi vil se på er pg_stat_database. Som vi kan se, er dette en forestilling. Der er meget information i det. Den mest varierede information. Og det giver meget nyttig viden om, hvad der sker i vores database.

Hvilke nyttige ting kan vi tage derfra? Lad os starte med de enkleste ting.

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-hitprocenten. Cache hit rate er en nyttig metrik. Det giver dig mulighed for at estimere, hvor meget data der tages fra den delte buffers cache, og hvor meget der læses fra disken.

Det er klart jo flere cache-hits vi har, jo bedre. Vi måler denne metric som en procentdel. Og for eksempel, hvis vores procentdel af disse cache-hits er mere end 90%, så er det godt. Hvis det falder til under 90 %, betyder det, at vi ikke har nok hukommelse til at holde det varme hoved af data i hukommelsen. Og for at bruge disse data, er PostgreSQL tvunget til at få adgang til disken, og det er langsommere, end hvis dataene blev læst fra hukommelsen. Og du skal tænke på at øge hukommelsen: enten øge delte buffere eller øge hardwarehukommelsen (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;

Hvad kan du ellers tage fra denne forestilling? Du kan se uregelmæssigheder i databasen. Hvad vises her? Der er commits, rollbacks, oprettelse af midlertidige filer, deres størrelse, deadlocks og konflikter.

Vi kan bruge denne anmodning. Denne SQL er ret simpel. Og vi kan se på disse data her.

Og her er tærskelværdierne. Vi ser på forholdet mellem commits og rollbacks. Commits er en vellykket bekræftelse af en transaktion. Tilbageføringer er en tilbagerulning, dvs. en transaktion gjorde noget arbejde, anstrengte databasen, beregnede noget, og så opstod der en fejl, og resultaterne af transaktionen kasseres. Det er antallet af tilbageførsler, der konstant stiger, er dårligt. Og du bør på en eller anden måde undgå dem, og redigere koden, så dette ikke sker.

Konflikter er relateret til replikation. Og de bør også undgås. Hvis du har nogle forespørgsler, der udføres på en replika, og der opstår konflikter, så skal du sortere disse konflikter og se, hvad der sker. Detaljer kan findes i loggene. Og eliminer konfliktsituationer, så applikationsanmodninger fungerer uden fejl.

Deadlocks er også en dårlig situation. Når anmodninger kæmper om ressourcer, fik en anmodning adgang til en ressource og tog låsen, en anden anmodning fik adgang til den anden ressource og tog også låsen, og derefter fik begge anmodninger adgang til hinandens ressourcer og blokerede, mens de ventede på, at naboen frigjorde låsen. Dette er også en problematisk situation. De skal behandles på niveau med omskrivning af applikationer og serialisering af adgang til ressourcer. Og hvis du ser, at dine dødvande konstant stiger, skal du se på detaljerne i logfilerne, analysere de situationer, der opstår, og se, hvad problemet er.

Midlertidige filer (temp_files) er også dårlige. Når en brugeranmodning ikke har nok hukommelse til at rumme operationelle, midlertidige data, opretter den en fil på disken. Og alle de operationer, som den kunne udføre i en midlertidig buffer i hukommelsen, begynder at blive udført på disken. Det er langsomt. Dette øger udførelsestiden for forespørgsler. Og den klient, der har sendt en forespørgsel til PostgreSQL, får et svar lidt senere. Hvis alle disse operationer udføres i hukommelsen, vil Postgres reagere meget hurtigere, og klienten vil vente mindre.

Pg_stat_bgwriter - Denne visning beskriver driften af to PostgreSQL-baggrundsundersystemer: dette checkpointer и background writer.

Lad os først se på kontrolpunkterne, de såkaldte. checkpoints. Hvad er kontrolpunkter? Et kontrolpunkt er en position i transaktionsloggen, der indikerer, at alle dataændringer registreret i loggen er blevet synkroniseret med dataene på disken. Processen, afhængig af arbejdsbyrden og indstillingerne, kan være langvarig og består for det meste af synkronisering af beskidte sider i delte buffere med datafiler på disken. Hvad er det for? Hvis PostgreSQL konstant fik adgang til disken og hentede data derfra og skrev data på hver adgang, ville det være langsomt. Derfor har PostgreSQL et hukommelsessegment, hvis størrelse afhænger af indstillingerne i konfigurationen. Postgres gemmer live data i denne hukommelse til senere behandling eller forespørgsel. I tilfælde af anmodninger om ændring af data, ændres det. Og vi får to versioner af dataene. Den ene er i vores hukommelse, den anden er på disk. Og med jævne mellemrum skal du synkronisere disse data. Vi skal synkronisere, hvad der er ændret i hukommelsen til disk. Til dette har du brug for checkpoints.

Checkpoint går gennem delte buffere, markerer beskidte sider, at de er nødvendige for checkpoint. Derefter starter den en anden passage gennem de delte buffere. Og de sider, der er markeret til kontrolpunkt, det synkroniserer dem allerede. På denne måde synkroniseres dataene med disken.

Der er to typer kontrolpunkter. Et kontrolpunkt udføres med en timeout. Dette kontrolpunkt er nyttigt og godt – checkpoint_timed. Og der er checkpoints på efterspørgsel - checkpoint required. Dette kontrolpunkt opstår, når vi har en meget stor datapost. Vi registrerede en masse transaktionslogfiler. Og PostgreSQL mener, at det skal synkronisere alt dette så hurtigt som muligt, lave et checkpoint og komme videre.

Og hvis man så på statistikken pg_stat_bgwriter og så hvad du har checkpoint_req er meget større end checkpoint_timed, så er dette dårligt. Hvorfor dårligt? Det betyder, at PostgreSQL er under konstant stress, når den skal skrive data til disk. Timeout checkpoint er mindre stressende og udføres i overensstemmelse med den interne tidsplan og er på en måde spredt ud over tid. PostgreSQL har evnen til at sætte arbejdet på pause og ikke belaste diskundersystemet. Dette er nyttigt til PostgreSQL. Og forespørgsler, der udføres under kontrolpunktet, vil ikke opleve stress fra det faktum, at diskundersystemet er optaget.

Og for at justere kontrolpunktet er der tre parametre:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

De giver dig mulighed for at regulere driften af kontrolpunkter. Men jeg vil ikke dvæle ved dem. Deres indflydelse er et særskilt emne.

Advarsel: Den version 9.4, der er omtalt i rapporten, er ikke længere relevant. I moderne versioner af PostgreSQL er parameteren checkpoint_segments erstattet af parametre min_wal_size и max_wal_size.

Det næste undersystem er baggrundsforfatteren − background writer. Hvad laver han? Den kører konstant i en endeløs løkke. Scanner sider i delte buffere og dumper beskidte sider, som den finder, til disken. Det hjælper således checkpointeren til at udføre mindre arbejde under udførelse af checkpoint.

Hvad skal den ellers bruge til? Det giver behovet for tomme sider i delte buffere, hvis de pludselig er nødvendige (i store mængder og med det samme) for at rumme data. Antag, at der opstod en situation, hvor tomme sider var påkrævet for at fuldføre en anmodning, og de allerede var i de delte buffere. Postgresive backend han tager dem bare op og bruger dem, han skal ikke selv rense noget. Men hvis der pludselig ikke er sådanne sider, pauser backend arbejdet og begynder at søge efter sider for at dumpe dem på disken og tage dem til sine egne behov - hvilket negativt påvirker tidspunktet for den aktuelt eksekverende anmodning. Hvis du ser, at du har en parameter maxwritten_clean stor, betyder det, at baggrundsforfatteren ikke gør sit arbejde, og du skal øge parametrene bgwriter_lru_maxpages, så han kan udføre mere arbejde i én cyklus, ryd flere sider.

Og en anden meget nyttig indikator er buffers_backend_fsync. Backends fsynkroniserer ikke, fordi det er langsomt. De sender fsync op i IO-stakkens checkpointer. Checkpointeren har sin egen kø, den behandler periodisk fsync og synkroniserer sider i hukommelsen med filer på disk. Hvis køen ved checkpointeren er stor og fuld, så er backend tvunget til selv at lave fsync, og det bremser arbejdet i backend, dvs. klienten vil modtage et svar senere, end det kunne. Hvis du ser, at din værdi er større end nul, så er dette allerede et problem og du skal være opmærksom på baggrundsskriverens indstillinger og også evaluere ydeevnen af diskundersystemet.

Advarsel: _Den følgende tekst beskriver de statistiske visninger forbundet med replikering. De fleste visnings- og funktionsnavne blev omdøbt i Postgres 10. Essensen af omdøbningen var at erstatte xlog på wal и location på lsn i funktion/visningsnavne mv. Særligt eksempel, funktion pg_xlog_location_diff() blev omdøbt til pg_wal_lsn_diff()._

Vi har også mange ting her. Men vi har kun brug for varer relateret til placering.

Hvis vi ser, at alle værdier er ens, er dette en ideel mulighed, og replikaen halter ikke bagefter mesteren.

Denne hexadecimale position er her positionen i transaktionsloggen. Det stiger konstant, hvis der er aktivitet i databasen: indsætter, 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 ting er anderledes, så er der en form for forsinkelse. Lag er forsinkelsen mellem replikaen og masteren, dvs. dataene er forskellige mellem serverne.

Der er tre årsager til forsinkelsen:

Dette diskundersystem kan ikke klare optagelsesfilsynkronisering.
Disse er mulige netværksfejl eller netværksoverbelastning, når dataene ikke når at nå replikaen, og de ikke kan gengive den.
Og processoren. Processoren er et meget sjældent tilfælde. Og jeg så det to eller tre gange, men det kan også ske.

Og her er tre forespørgsler, der giver os mulighed for at bruge statistik. Vi kan estimere, hvor meget vi har registreret i transaktionsloggen. Der er sådan en funktion pg_xlog_location_diff og vi kan estimere replikationsforsinkelsen i bytes og sekunder. Vi bruger også værdien fra denne visning (VIEWs) til dette.

Note: _I stedet for pg_xlog_locationFunktionen diff() kan bruge subtraktionsoperatoren og trække en placering fra en anden. Komfortabel.

Der er et punkt med forsinkelsen, som er i sekunder. Hvis der ikke er nogen aktivitet på masteren, var transaktionen der for omkring 15 minutter siden, og der er ingen aktivitet, og hvis vi ser på denne forsinkelse på replikaen, vil vi se en forsinkelse på 15 minutter. Dette er værd at huske. Og det kan være forvirrende, når du ser denne forsinkelse.

Pg_stat_all_tables er en anden nyttig visning. Det viser statistik på tabeller. Når vi har tabeller i databasen, er der noget aktivitet med den, nogle handlinger, vi kan få denne information fra denne visning.

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 sekventielle scanninger henover bordet. Selve tallet efter disse afleveringer er ikke nødvendigvis dårligt og er ikke en indikator for, at vi skal gøre noget.

Der er dog en anden metrik - seq_tup_read. Dette er antallet af rækker, der returneres fra den sekventielle scanning. Hvis det gennemsnitlige antal overstiger 1, 000, 10, 000, så er dette allerede en indikator på, at du måske skal bygge et indeks et sted, så forespørgsler er baseret på indekset, eller det er muligt at optimere forespørgsler, der bruger sådanne sekventielle scanninger, så at dette ikke sker var.

Et simpelt eksempel - lad os sige en anmodning med en stor OFFSET og LIMIT omkostninger. For eksempel scannes 100 rækker i en tabel, og derefter tages 000 påkrævede rækker, og de tidligere scannede rækker kasseres. Dette er også en dårlig sag. Og sådanne forespørgsler skal optimeres. Og her er en simpel SQL-forespørgsel, hvor du kan se på dette og evaluere de resulterende tal.

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;

Bordstørrelser kan også fås ved hjælp af denne tabel og ved hjælp af yderligere funktioner pg_total_relation_size(), pg_relation_size().

Generelt er der metakommandoer dt и di, som kan bruges i PSQL og også se størrelserne på tabeller og indekser.

Men brugen af funktioner hjælper os med at se på størrelsen af tabeller, selv under hensyntagen til indekser, eller uden at tage hensyn til indekser, og allerede lave nogle estimater baseret på væksten af databasen, dvs. hvordan den vokser, med hvilken intensitet og drage nogle konklusioner om størrelsesoptimering.

Registrering af aktivitet. Hvad er en optagelse? Lad os se på operationen UPDATE – handlingen med at opdatere rækker i en tabel. Faktisk er opdatering to operationer (eller endnu flere). Dette er at indsætte en ny version af rækken og markere den gamle version af rækken som forældet. Efterfølgende vil autostøvsugeren komme og rense disse forældede versioner af linjerne og markere dette sted som tilgængeligt til genbrug.

Derudover handler opdatering ikke kun om at opdatere en tabel. Dette er også en indeksopdatering. Hvis du har mange indekser på bordet, skal alle indekser, der indeholder felter opdateret i forespørgslen, også opdateres under opdateringen. Disse indekser vil også have forældede versioner af rækker, der skal ryddes op.

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å grund af dets nye design er UPDATE en tungvægtsoperation. Men de kan gøres nemmere. Spise hot updates. De dukkede op i PostgreSQL version 8.3. Og hvad er det her? Dette er en letvægtsopdatering, der ikke får indekser til at blive genopbygget. Det vil sige, at vi opdaterede posten, men kun posten på siden (som hører til tabellen) blev opdateret, og indekserne peger stadig på den samme post på siden. Der er lidt af en interessant driftslogik: Når et vakuum kommer, skaber det disse kæder hot genopbygges, og alt fortsætter med at fungere uden at opdatere indekser, og alt sker med mindre spild af ressourcer.

Og hvornår gør du det n_tup_hot_upd stort, så er det meget godt. Det betyder, at lette opdateringer dominerer, og det er billigere for os med hensyn til ressourcer, og alt er fint.

ALTER TABLE table_name SET (fillfactor = 70);

Sådan øger du lydstyrken hot updateov? Vi kan bruge fillfactor. Det bestemmer størrelsen af den reserverede ledige plads, når du udfylder en side i en tabel ved hjælp af INSERTs. Når indstik føjes til en tabel, fylder de siden fuldstændigt og efterlader ingen tom plads. Så er en ny side fremhævet. Dataene udfyldes igen. Og dette er standardadfærden, fillfactor = 100%.

Vi kan lave fyldfaktoren 70%. Det vil sige, at der under indsættelser blev fremhævet en ny side, men kun 70 % af siden blev udfyldt. Og vi har 30% tilbage som reserve. Når du skal lave en opdatering, vil det højst sandsynligt ske på samme side, og den nye version af linjen vil passe på samme side. Og hot_update vil blive udført. Dette gør det nemmere at 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 der er meget få statistikker for i PostgreSQL. Vi kan kun se i tabellerne i pg_stat_activity, hvor mange vakuum vi har i øjeblikket. Det er dog meget svært at forstå, hvor mange borde der står i køen med det samme.

Note: _Startende med Postgres 10 er situationen med Vatovac-sporing forbedret meget - pg_stat_progress-visningen er dukket opvakuum, hvilket væsentligt forenkler spørgsmålet om overvågning af bilens vakuum.

Vi kan bruge denne forenklede forespørgsel. Og vi kan se, hvornår vakuumet skal laves. Men hvordan og hvornår skal vakuumet starte? Dette er de gamle versioner af de linjer, jeg talte om tidligere. Opdatering skete, en ny version af linjen blev indsat. En forældet version af strengen er dukket op. I bordet pg_stat_user_tables der er sådan en parameter n_dead_tup. Den viser antallet af "døde" linjer. Og så snart antallet af døde rækker bliver større end en vis tærskel, vil der komme et autovakuum til bordet.

Og hvordan beregnes denne tærskel? Dette er en meget specifik procentdel af det samlede antal rækker i tabellen. Der er en parameter autovacuum_vacuum_scale_factor. Det bestemmer procentdelen. Lad os sige 10 % + der er en ekstra grundlæggende tærskel på 50 linjer. Og hvad sker der? Når vi har flere døde rækker end "10% + 50" af alle rækker i tabellen, så sætter 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));

Der er dog én pointe. Grundlæggende tærskler for parametre av_base_thresh и av_scale_factor kan tildeles individuelt. Og følgelig vil tærsklen ikke være global, men individuel for bordet. Derfor skal du bruge tricks og tricks for at beregne. Og hvis du er interesseret, så kan du se på erfaringerne fra vores kollegaer fra Avito (linket på sliden er ugyldigt og er blevet opdateret i teksten).

De skrev for munin plugin, som tager højde for disse ting. Der er en to-arks fodklud der. Men den beregner korrekt og giver os ret effektivt mulighed for at vurdere, hvor vi har brug for meget vakuum til borde, hvor der er lidt.

Hvad kan vi gøre ved det? Hvis vi har en stor kø, og autostøvsugeren ikke kan klare det, så kan vi øge antallet af vakuumarbejdere, eller blot gøre vakuumet mere aggressivt, så den udløser tidligere, behandler bordet i små stykker. Og dermed bliver køen mindre. — Det vigtigste her er at overvåge belastningen på diskene, fordi... vakuum er ikke en gratis ting, selvom med fremkomsten af SSD/NVMe-enheder er problemet blevet mindre mærkbart.

Pg_stat_all_indexes er statistik over indekser. Hun er ikke stor. Og vi kan bruge det til at få information om brugen af indekser. Og for eksempel kan vi bestemme, hvilke indekser vi har ekstra.

Som jeg allerede har sagt, opdatering er ikke kun en opdatering af tabeller, det er også en opdatering af indekser. Derfor, hvis vi har mange indekser på tabellen, så skal indekserne for de indekserede felter også opdateres, når rækkerne i tabellen opdateres, og hvis vi har ubrugte indekser, som der ikke er indeksscanninger for, så hænger de som ballast. Og vi skal af med dem. Til dette har vi brug for en mark idx_scan. Vi ser blot på antallet af indeksscanninger. Hvis indekser har nul scanninger over en relativt lang periode med statistiklagring (mindst 2-3 uger), så er disse højst sandsynligt dårlige indekser, vi skal af med dem.

Note: Når du søger efter ubrugte indekser i tilfælde af streaming-replikeringsklynger, skal du kontrollere alle klynge noder, fordi statistikker er ikke globale, og hvis indekset ikke bruges på masteren, så kan det bruges på replikaer (hvis der er en belastning der).

To links:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Disse er mere avancerede forespørgselseksempler på, hvordan man slår ubrugte indekser op.

Det andet link er en ret interessant anmodning. Der er en meget ikke-triviel logik der. Jeg anbefaler det til reference.

Hvad er ellers værd at opsummere ved hjælp af indekser?

Ubrugte indekser er dårlige.
De optager plads.
Sænk opdateringsoperationer.
Ekstra arbejde til støvsugeren.

Hvis vi fjerner ubrugte indekser, vil vi kun gøre databasen bedre.

Næste oplæg er pg_stat_activity. Dette er en analog af værktøjet ps, kun i PostgreSQL. Hvis psSå ser du på processerne i operativsystemet pg_stat_activity Det vil vise dig aktiviteten inde i PostgreSQL.

Hvilke nyttige ting kan vi tage derfra?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Vi kan se den generelle aktivitet, hvad der sker i databasen. Vi kan lave en ny implementering. Alt her er eksploderet, nye forbindelser accepteres ikke, fejl strømmer ind i applikationen.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Vi kan køre en forespørgsel som denne og se den samlede procentdel af forbindelser i forhold til den maksimale forbindelsesgrænse og se, hvem der har flest forbindelser. Og i dette givne tilfælde ser vi denne bruger cron_role åbnet 508 forbindelser. Og der skete der noget med ham. Vi skal forholde os til det og se på det. Og det er meget muligt, at dette er en slags unormalt antal forbindelser.

Hvis vi har en OLTP-arbejdsbelastning, skal forespørgslerne være hurtige, meget hurtige, og der skal ikke være lange forespørgsler. Men hvis der opstår lange forespørgsler, så er der på kort sigt ikke noget at bekymre sig om, men I det lange løb skader lange forespørgsler databasen; de øger tabellernes bloat-effekt, når der opstår tabelfragmentering. Du skal slippe af med både oppustethed og lange forespørgsler.

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;

Bemærk venligst: med denne anmodning kan vi identificere lange forespørgsler og transaktioner. Vi bruger funktionen clock_timestamp() at bestemme driftstiden. Lange forespørgsler, som vi fandt, vi kan huske dem, opfylde dem explain, se på planerne og på en eller anden måde optimere. Vi skyder de nuværende lange anmodninger ned og går videre med vores liv.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Dårlige transaktioner er transaktioner i inaktiv i transaktion og inaktiv i transaktion (afbrudt) tilstande.

Hvad betyder det? Transaktioner har flere tilstande. Og en af disse tilstande kan antages til enhver tid. Der er et felt til at definere tilstande state i denne præsentation. Og vi bruger det til at bestemme staten.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Og, som jeg sagde ovenfor, disse to stater inaktiv i transaktion og inaktiv i transaktion (afbrudt) er dårlige. Hvad er det? Dette er, når applikationen åbnede en transaktion, udførte nogle handlinger og gik i gang. Transaktionen forbliver åben. Den hænger, der sker ikke noget i den, den optager forbindelsen, låser på ændrede rækker og øger potentielt svulsten af andre tabeller på grund af arkitekturen i Postrges transaktionsmotor. Og sådanne transaktioner bør også skydes ned, for de er generelt skadelige under alle omstændigheder.

Hvis du ser, at du har mere end 5-10-20 af dem i din database, så skal du bekymre dig og begynde at gøre noget med dem.

Her bruger vi også til beregningstiden clock_timestamp(). Vi skyder transaktioner og optimerer applikationen.

Som jeg sagde ovenfor, er blokering, når to eller flere transaktioner kæmper om en eller en gruppe af ressourcer. Til dette har vi et felt waiting med boolesk værdi true eller false.

Sandt nok - det betyder, at processen er afventende, noget skal gøres. Når en proces venter, betyder det, at den klient, der påbegyndte denne proces, også venter. Klienten sidder i browseren og venter også.

Advarsel: _Starter fra Postgres version 9.6-feltet waiting fjernet og yderligere to informative felter tilføjet i stedet wait_event_type и wait_event._

Hvad skal jeg gøre? Hvis du ser sandt i lang tid, betyder det, at du skal slippe af med sådanne anmodninger. Vi skyder simpelthen sådanne transaktioner ned. Vi skriver til udviklerne, at de på en eller anden måde skal optimere, så der ikke bliver kapløb om ressourcerne. Og så optimerer udviklerne applikationen, så det ikke sker.

Og den ekstreme, men potentielt ikke-dødelige sag er forekomst af dødvande. To transaktioner opdaterede to ressourcer og fik derefter adgang til dem igen, denne gang til modsatte ressourcer. I dette tilfælde dræber PostgreSQL selve transaktionen, så en anden kan fortsætte med at arbejde. Dette er en blindgyde situation, og hun kan ikke finde ud af det på egen hånd. Derfor er PostgreSQL tvunget til at tage ekstreme foranstaltninger.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Og her er to forespørgsler, der giver dig mulighed for at spore blokering. Vi bruger view pg_locks, som giver dig mulighed for at spore tunge låse.

Og det første link er selve anmodningsteksten. Den er ret lang.

Og det andet link er en artikel om låse. Det er nyttigt at læse, det er meget interessant.

Så hvad ser vi? Vi ser to anmodninger. Transaktion med ALTER TABLE er en blokerende transaktion. Det startede, men blev ikke fuldført, og programmet, der registrerede denne transaktion, gør andre ting et eller andet sted. Og den anden anmodning er opdatering. Han venter på, at alterbordet slutter, før han kan fortsætte sit arbejde.

Sådan kan vi finde ud af, hvem der låste hvem, der holder hvem, og vi kan håndtere det yderligere.

Næste modul er pg_stat_statements. Som sagt er dette et modul. For at bruge det skal du indlæse dets bibliotek i konfigurationen, genstarte PostgreSQL, installere modulet (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;

Hvad kan vi tage derfra? Hvis vi taler om simple ting, kan vi tage den gennemsnitlige udførelsestid for forespørgsler. Tiden vokser, hvilket betyder, at PostgreSQL reagerer langsomt, og vi skal gøre noget.

Vi kan se på de mest aktive skrivetransaktioner i databasen, der ændrer data i delte buffere. Se, hvem der opdaterer eller sletter data der.

Og vi kan simpelthen se på forskellige statistikker for disse anmodninger.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Vi pg_stat_statements Vi bruger det til at lave rapporter. Vi nulstiller statistikken en gang om dagen. Lad os akkumulere det. Inden vi nulstiller statistikken næste gang, lad os lave en rapport. Her er et link til rapporten. Du kan se den.

Hvad laver vi? Vi beregner generel statistik for alle forespørgsler. Derefter tæller vi for hver anmodning dets individuelle bidrag til disse overordnede statistikker.

Og hvad kan vi se? Vi kan se på den samlede eksekveringstid for alle anmodninger af en bestemt type på baggrund af alle andre anmodninger. Vi kan se på CPU- og I/O-ressourceforbruget i forhold til det overordnede billede. Og optimer allerede disse forespørgsler. Vi bygger de mest populære forespørgsler baseret på denne rapport og får allerede stof til eftertanke om, hvad vi skal optimere.

Hvad har vi efterladt bag kulisserne? Der er stadig et par indlæg tilbage, som jeg ikke overvejede, fordi tiden er begrænset.

Der er pgstattuple er også et ekstra modul fra standardbidragspakken. Det giver dig mulighed for at evaluere bloat borde, såkaldte tabelfragmentering. Og hvis der er meget fragmentering, skal du fjerne det og bruge forskellige værktøjer. Og funktion pgstattuple virker i lang tid. Og jo flere borde der er, jo længere vil det virke.

Næste bidrag er pg_buffercache. Det giver dig mulighed for at inspicere delte buffere: hvor intensivt og til hvilke tabeller buffersider bruges. Og det giver dig simpelthen mulighed for at se på delte buffere og evaluere, hvad der sker der.

Næste modul er pgfincore. Det tillader bordoperationer på lavt niveau via et systemkald mincore(), dvs. det giver dig mulighed for at indlæse en tabel i delte buffere eller fjerne den. Og det giver dig blandt andet mulighed for at inspicere operativsystemets sidecache, dvs. hvor meget plads tabellen optager i sidecachen, i delte buffere, og giver dig simpelthen mulighed for at evaluere tabellens arbejdsbyrde.

Næste modul - pg_stat_kcache. Den bruger også et systemopkald getrusage(). Og den udfører den før og efter anmodningen er udført. Og i den resulterende statistik giver det os mulighed for at estimere, hvor meget vores anmodning brugt på disk I/O, dvs. operationer med filsystemet og ser på processorbrugen. Modulet er dog ungt (hostehoste) og til dets drift kræver det PostgreSQL 9.4 og pg_stat_statements, som jeg nævnte tidligere.

Det er nyttigt at vide, hvordan man bruger statistik. Du behøver ikke tredjepartsprogrammer. Du kan komme ind, se, gøre noget, udrette noget.
Det er ikke svært at bruge statistik, det er bare almindelig SQL. Du samlede anmodningen, kompilerede den, sendte den, så på den.
Statistik hjælper med at besvare spørgsmål. Hvis du har spørgsmål, vender du dig til statistik - se, drag konklusioner, analyser resultaterne.
Og eksperimentere. Der er mange forespørgsler, en masse data. Du kan altid optimere en eksisterende forespørgsel. Du kan lave din egen version af anmodningen, der passer dig mere end originalen og bruge den.