Hej.
Mit navn er Vanya og jeg er en Java-udvikler. Det forholder sig sådan, at jeg arbejder meget med PostgreSQL – opsætning af databasen, optimering af struktur, ydeevne, og spiller lidt DBA i weekenden.
For nylig har jeg ryddet op i flere databaser i vores mikrotjenester og skrevet et java-bibliotek , som gør dette arbejde lettere, sparer mig tid og hjælper mig med at undgå nogle almindelige fejl begået af udviklere. Det er dette bibliotek, vi vil tale om i dag.
Ansvarsfraskrivelse
Hovedversionen af PostgreSQL, 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.
forhistorie
Det hele startede for næsten et år siden med en situation, der var mærkelig for mig: Konkurrenceskabelsen af et indeks ud af det blå endte med en fejl. Selve indekset forblev som sædvanligt i databasen i en ugyldig tilstand. Loganalyse viste mangel . Og så går vi... Da jeg gravede dybere, opdagede jeg en hel masse problemer i databasekonfigurationen, og jeg rullede ærmerne op og begyndte at rette dem med et glimt i øjnene.
Problem et - standardkonfiguration
Sandsynligvis er alle ret trætte af metaforen om Postgres, som kan køres på en kaffemaskine, men... standardkonfigurationen rejser virkelig en række spørgsmål. Som minimum er det værd at være opmærksom på vedligeholdelsesarbejde_mem, temp_file_limit, statement_timeout и lock_timeout.
I vores tilfælde vedligeholdelsesarbejde_mem var standard 64 MB, og temp_file_limit noget omkring 2 GB - vi havde simpelthen ikke nok hukommelse til at lave et indeks på et stort bord.
Derfor i pg-indeks-sundhed Jeg har samlet en serie , efter min mening, de parametre, der skal konfigureres for hver database.
Problem to - dublerede indekser
Vores databaser lever på SSD-drev, og vi bruger HA-konfiguration med flere datacentre, master host og n-antal replikaer. Diskplads er en meget værdifuld ressource for os; det er ikke mindre vigtigt end ydeevne og CPU-forbrug. Derfor har vi på den ene side brug for indekser til hurtig læsning, og på den anden side ønsker vi ikke at se unødvendige indekser i databasen, da de æder plads og bremser dataopdateringen.
Og nu, efter at have genoprettet alt og har set nok , besluttede jeg at organisere en "stor" udrensning. Det viste sig, at udviklere ikke bryder sig om at læse databasedokumentation. De bryder sig ikke særlig meget om det. På grund af dette opstår der to typiske fejl - et manuelt oprettet indeks på en primærnøgle og et lignende "manuelt" indeks på en unik kolonne. Faktum er, at de ikke er nødvendige - Postgres vil gøre alt selv. Sådanne indekser kan sikkert slettes, og diagnostik er dukket op til dette formål .
Opgave tre - krydsende indekser
De fleste nybegyndere opretter indekser på en enkelt kolonne. Efterhånden, efter at have oplevet denne forretning grundigt, begynder folk at optimere deres forespørgsler og tilføje mere komplekse indekser, der inkluderer flere kolonner. Sådan vises indekser på kolonner A, A + B, A+B+C og så videre. De to første af disse indekser kan sikkert smides ud, da de er præfikser til det tredje. Dette sparer også en masse diskplads, og der er diagnostik til dette .
Problem fire - fremmednøgler uden indekser
Postgres giver dig mulighed for at oprette fremmednøglebegrænsninger uden at angive et backing-indeks. I mange situationer er dette ikke et problem, og kommer måske ikke engang til udtryk... Foreløbig...
Det var det samme med os: det er bare, at et job, der kører i henhold til en tidsplan og rydde databasen for testordrer, på et tidspunkt begyndte at blive "føjet" til os af masterværten. CPU og IO gik til spilde, anmodninger blev langsommere og fik timeout, tjenesten var fem hundrede. Hurtig analyse viste, at forespørgsler som:
delete from <table> where id in (…)I dette tilfælde var der selvfølgelig et indeks efter id i måltabellen, og meget få poster blev slettet i henhold til betingelsen. Det så ud til, at alt skulle fungere, men desværre gjorde det ikke.
Den vidunderlige kom til undsætning forklare analysere og sagde, at udover at slette poster i måltabellen, er der også et referentielt integritetstjek, og på en af de relaterede tabeller mislykkes denne kontrol sekventiel scanning på grund af mangel på et passende indeks. Således blev diagnostik født .
Opgave fem – nulværdi i indekser
Som standard inkluderer Postgres null-værdier i btree-indekser, men de er normalt ikke nødvendige der. Derfor forsøger jeg flittigt at smide disse nuller ud (diagnostik ), opretter delvise indekser på nullbare kolonner efter type where <A> is not null. På denne måde var jeg i stand til at reducere størrelsen på et af vores indekser fra 1877 MB til 16 KB. Og i en af tjenesterne faldt databasestørrelsen i alt med 16% (med 4.3 GB i absolutte tal) på grund af udelukkelsen af nulværdier fra indekserne. Enorme besparelser på diskplads med meget enkle ændringer. 🙂
Problem seks – mangel på primærnøgler
På grund af mekanismens natur en situation som denne er mulig når størrelsen på dit bord vokser hurtigt på grund af et stort antal døde poster. Jeg troede naivt på, at dette ikke ville true os, og at dette ikke ville ske med vores base, for vi, wow!!!, er normale udviklere... Hvor var jeg dum og naiv...
En dag tog en vidunderlig migration og opdaterede alle posterne i en stor og aktivt brugt tabel. Vi fik +100 GB til bordstørrelsen ud af det blå. Det var en skam, men vores uheld sluttede ikke der. Efter at autovakuum på dette bord sluttede 15 timer senere, blev det klart, at den fysiske placering ikke ville vende tilbage. Vi kunne ikke stoppe tjenesten og gøre VACUUM FULL, så vi besluttede at bruge . Og så viste det sig pg_repack ved ikke, hvordan man behandler tabeller uden en primær nøgle eller anden unikhedsbegrænsning, og vores tabel havde ikke en primær nøgle. Således blev diagnostik født .
I biblioteksversionen 0.1.5 Muligheden for at indsamle data fra svulstige tabeller og indekser og reagere på det rettidigt er blevet tilføjet.
Opgaver syv og otte - utilstrækkelige indekser og ubrugte indekser
Følgende to diagnoser er: и – dukkede op i deres endelige form for relativt nylig. Pointen er, at de ikke bare kunne tages og tilføjes.
Som jeg allerede har skrevet, bruger vi en konfiguration med flere replikaer, og læsebelastningen på forskellige værter er fundamentalt forskellig. Som et resultat viser situationen sig, at nogle tabeller og indekser på nogle værter praktisk talt ikke bruges, og til analyse skal du indsamle statistik fra alle værter i klyngen. Dette er også nødvendigt på hver vært i klyngen; du kan ikke kun gøre dette på masteren.
Denne tilgang gjorde det muligt for os at spare adskillige tiere af gigabyte ved at fjerne indekser, der aldrig blev brugt, samt tilføje manglende indekser til sjældent brugte tabeller.
Som en konklusion
Selvfølgelig kan du konfigurere næsten al diagnostik . På denne måde kan du hurtigt implementere tjek i din applikation, forhindre nye fejl i at dukke op, og derefter gradvist rette gamle.
Nogle diagnoser kan udføres i funktionstest umiddelbart efter udrulning af databasemigreringer. Og dette er måske en af de mest kraftfulde funktioner i mit bibliotek. Et eksempel på brug kan findes i .
Det giver mening kun at udføre kontrol for ubrugte eller manglende indekser, såvel som for bloat, på en rigtig database. De indsamlede værdier kan registreres i eller sendes til overvågningssystemet.
Det håber jeg virkelig pg-indeks-sundhed vil være nyttige og efterspurgte. Du kan også bidrage til bibliotekets udvikling ved at rapportere problemer, du finder, og foreslå ny diagnostik.
Kilde: www.habr.com