Hej.
Mit navn er Vanya, og jeg er Java-udvikler. Det så tilfældigvis sådan ud, at jeg arbejder meget med PostgreSQL – jeg beskæftiger mig med databaseopsætning, optimering af struktur, ydeevne og leger lidt DBA i weekenden.
På det seneste har jeg ryddet op i flere databaser i vores mikrotjenester og har skrevet et java-bibliotek , som letter dette arbejde, sparer mig tid og hjælper med at undgå nogle typiske fejl begået af udviklere. Det er præcis det bibliotek, vi skal tale om i dag.
Ansvarsfraskrivelse
Den primære version af PostgreSQL, som jeg arbejder med, er 10. Alle de SQL-forespørgsler, jeg bruger, er også testet på version 11. Den mindste understøttede version er 9.6.
Background
Det hele startede for næsten et år siden med en mærkelig situation for mig: en konkurrerende oprettelse af et indeks på et helt blankt område endte med en fejl. Selve indekset, som sædvanligt, forblev i en ugyldig tilstand i databasen. Analyse af loggene viste mangel på . Og så begyndte det... Da jeg gravede dybere, opdagede jeg en hel række problemer i databasekonfigurationen, og med ærmerne oppe begyndte jeg entusiastisk at rette dem.
Det første problem – standardkonfigurationen
Måske er metaforen om Postgres, der kan køre på en kaffemaskine, allerede trættende for alle, men... standardkonfigurationen rejser virkelig en række spørgsmål. I det mindste er det værd at lægge mærke til maintenance_work_mem, temp_file_limit, statement_timeout og lock_timeout.
I vores tilfælde maintenance_work_mem var som standard 64 megabyte, og temp_file_limit omkring 2 gigabyte – vi manglede simpelthen hukommelse til at oprette et indeks på en stor tabel.
Derfor i pg-index-health jeg samlede en række , som efter min mening er parametre, der bør tilpasses til hver database.
Andet problem – duplikerede indekser
Vores databaser lever på SSD-diske, og vi bruger HA-konfiguration med flere datacentre, en master-server og n-et antal replikaer. Pladsen på disken er en meget værdifuld ressource for os; den er lige så vigtig som ydeevne og CPU-forbrug. Derfor, på den ene side, har vi brug for indekser til hurtig læsning, men på den anden side ønsker vi ikke at se unødvendige indekser i databasen, da de optager plads og bremser dataopdateringer.
Og så, efter at have gendannet alle og efter at have set , jeg besluttede at foretage en «stor» oprydning. Det viste sig, at udviklere ikke bryder sig om at læse dokumentationen for databaser. De elsker det ikke overhovedet. Dette fører til to typiske fejl - manuelt oprettede indekser på primære nøgler og et tilsvarende «manuelt» indeks på unikke kolonner. Problemet er, at de ikke er nødvendige - Postgres klarer det selv. Sådanne indekser kan trygt fjernes, og der er nu diagnosticering for dette. .
Problem tre - overlappende indekser
De fleste nye udviklere opretter indekser på en kolonne. Efterhånden, når de for alvor har smagt på dette, begynder folk at optimere deres forespørgsler og tilføje mere komplekse indekser, der inkluderer flere kolonner. Sådan opstår indekser på kolonnerne A, A+B, A+B+C osv. De første to af disse indekser kan trygt fjernes, da de er præfikser til det tredje. Dette sparer også betydeligt plads på disken, og der er diagnosticering for dette. .
Problem fire - fremmede nøgler uden indekser
Postgres tillader oprettelse af begrænsninger for fremmede nøgler uden at angive en understøttende indeks. I mange situationer er dette ikke et problem og kan endda ikke vise sig at have nogen konsekvenser… Indtil videre…
Sådan var det for os: lige pludselig blev jobs, der blev kørt efter tidsplan og rensede databasen for testordrer, begyndt at «stable» vores master host. CPU og IO skød i vejret, forespørgsler blev langsomme og stoppede på grund af timeout, servicen svarede med 500. En hurtig analyse viste, at forespørgsler som:
slet fra <table> hvor id i (…)derudover var indekset på id i mål-tabellen naturligvis til stede, og der blev slettet meget få poster efter betingelsen. Det så ud til, at alt burde fungere, men desværre gjorde det ikke.
Til undsætning kom den vidunderlige explain analyze og fortalte, at udover at slette poster i mål-tabellen, blev der også kontrolleret for referentiel integritet, og på en af de relaterede tabeller ramte denne kontrol sequential scan på grund af manglen på et passende indeks. Således blev diagnosticering født. .
Problem fem - null værdier i indekser
Som standard inkluderer Postgres null værdier i btree-indekser, men de er som regel ikke nødvendige. Derfor gør jeg en indsats for at fjerne disse null'er (diagnosticering ), og opretter partielle indekser på nullable-kolonner af typen where is not null. På denne måde lykkedes det mig at reducere størrelsen af en af vores indekser fra 1877 MB til 16 KB. Og i en af tjenesterne blev den samlede database størrelse reduceret med 16% (med 4,3 GB i absolutte tal) ved at udelukke null-værdier fra indekserne. En kolossal besparelse af diskplads ved relativt enkle justeringer. 🙂
Problem seks - mangel på primære nøgler
På grund af mekanismens særlige karakteristika kan en sådan situation opstå, som , hvor størrelsen af din tabel hurtigt vokser på grund af et stort antal døde poster. Jeg troede naivt, at dette ikke ville ramme os, og at vores database ikke ville opleve det, for vi er jo, wow!!!, normale udviklere... Hvor dum og naiv jeg var...
På en smuk dag tog en vidunderlig migration og opdaterede alle poster i en stor og aktivt anvendt tabel. Vi fik +100 GB til tabelens størrelse ud af det blå. Det var skide frustrerende, men vores elendigheder sluttede ikke her. Efter at autovakuumet på denne tabel sluttede efter 15 timer, blev det klart, at det fysiske plads ikke ville vende tilbage. Vi kunne ikke stoppe tjenesten og udføre VACUUM FULL, så vi besluttede at bruge . Og her viste det sig, at pg_repack den ikke kan håndtere tabeller uden primære nøgler eller andre unikke begrænsninger, og på vores tabel var der ingen primære nøgler. Sådan blev diagnosen .
I versionen af biblioteket 0.1.5 blev muligheden tilføjet til at samle data om bloat fra tabeller og indekser og reagere rettidigt på det.
Problemer syv og otte - mangel på indekser og ubrugte indekser
De næste to diagnoser - og – optrådte relativt for nylig i deres endelige form. Sagen er, at de ikke bare kunne tilføjes sådan.
Som jeg allerede har nævnt, bruger vi en konfiguration med flere replikaer, og belastningen på de læsende noder er fundamentalt forskellig. Resultatet er, at nogle tabeller og indekser på nogle noder stort set ikke bruges, og for at analysere skal man samle statistikker fra alle noder i klyngen. skal også gøres på hver node i klyngen; man kan ikke kun gøre det på masteren.
Denne tilgang gjorde det muligt for os at spare flere ti-GB ved at fjerne indekser, der aldrig blev brugt, samt tilføje manglende indekser til sjældent anvendte tabeller.
Som konklusion
Naturligvis kan næsten alle diagnosticeringer konfigureres . På denne måde kan kontroller hurtigt implementeres i din applikation, hvilket forhindrer nye fejl i at opstå og giver mulighed for gradvist at rette gamle.
Nogle diagnostik kan allerede udføres i funktionelle tests umiddelbart efter anvendelsen af databasemigrationer. Og det er måske en af de mest kraftfulde funktioner i mit bibliotek. Et eksempel på brug kan ses i .
Kontroller for ubrugte eller manglende indekser samt for bloat, giver mening at udføre kun på en reel database. De indsamlede værdier kan skrives til eller sendes til overvågningssystemet.
Jeg håber virkelig, at pg-index-health vil være nyttig og efterspurgt. Du kan også bidrage til udviklingen af biblioteket ved at rapportere om problemer og foreslå nye diagnosticeringer.
Kilde: habr.com
