Hei.
Mitt navn er Vanya og jeg er en Java-utvikler. Det har seg sånn at jeg jobber mye med PostgreSQL – setter opp databasen, optimaliserer strukturen, ytelsen og spiller litt DBA i helgene.
Nylig har jeg ryddet opp i flere databaser i mikrotjenestene våre og skrevet et java-bibliotek , som gjør dette arbeidet enklere, sparer meg for tid og hjelper meg å unngå noen vanlige feil gjort av utviklere. Det er dette biblioteket vi skal snakke om i dag.
Ansvarsfraskrivelse
Hovedversjonen av PostgreSQL jeg jobber med er 10. Alle SQL-spørringene jeg bruker er også testet på versjon 11. Minimum støttet versjon er 9.6.
forhistorie
Det hele startet for nesten et år siden med en situasjon som var merkelig for meg: Konkurranseopprettingen av en indeks ut av det blå endte med en feil. Selve indeksen forble som vanlig i databasen i en ugyldig tilstand. Logganalyse viste mangel . Og så drar vi... Når jeg gravde dypere, oppdaget jeg en hel haug med problemer i databasekonfigurasjonen, og jeg brettet opp ermene og begynte å fikse dem med et glimt i øynene.
Problem én - standardkonfigurasjon
Sannsynligvis er alle ganske lei av metaforen om Postgres, som kan kjøres på en kaffetrakter, men... standardkonfigurasjonen reiser virkelig en rekke spørsmål. I det minste er det verdt å være oppmerksom på vedlikeholdsarbeid_mem, temp_file_limit, statement_timeout и lock_timeout.
I vårt tilfelle vedlikeholdsarbeid_mem var standard 64 MB, og temp_file_limit noe rundt 2 GB - vi hadde rett og slett ikke nok minne til å lage en indeks på et stort bord.
Derfor, i pg-indeks-helse Jeg samlet en serie , etter min mening, parametrene som bør konfigureres for hver database.
Problem to - dupliserte indekser
Våre databaser lever på SSD-stasjoner, og vi bruker HA-konfigurasjon med flere datasentre, mastervert og n-antall kopier. Diskplass er en svært verdifull ressurs for oss; det er ikke mindre viktig enn ytelse og CPU-forbruk. Derfor trenger vi på den ene siden indekser for rask lesing, og på den andre siden ønsker vi ikke å se unødvendige indekser i databasen, siden de spiser opp plass og senker dataoppdateringen.
Og nå, etter å ha gjenopprettet alt og har sett nok , bestemte jeg meg for å organisere en "flott" rensing. Det viste seg at utviklere ikke liker å lese databasedokumentasjon. De liker det ikke så godt. På grunn av dette oppstår to typiske feil - en manuelt opprettet indeks på en primærnøkkel og en lignende "manuell" indeks på en unik kolonne. Faktum er at de ikke er nødvendige - Postgres vil gjøre alt selv. Slike indekser kan trygt slettes, og diagnostikk har dukket opp for dette formålet .
Oppgave tre - kryssende indekser
De fleste nybegynnere utviklere lager indekser på en enkelt kolonne. Gradvis, etter å ha grundig erfart denne virksomheten, begynner folk å optimalisere søkene sine og legge til mer komplekse indekser som inkluderer flere kolonner. Slik vises indekser på kolonner A, A + B, A + B + C og så videre. De to første av disse indeksene kan trygt kastes ut, siden de er prefikser til den tredje. Dette sparer også mye diskplass og det finnes diagnostikk for dette .
Problem fire - fremmednøkler uten indekser
Postgres lar deg opprette fremmednøkkelbegrensninger uten å spesifisere en støtteindeks. I mange situasjoner er ikke dette et problem, og manifesterer seg kanskje ikke engang... Foreløpig...
Det var det samme med oss: det er bare at en jobb, som kjører i henhold til en tidsplan og tømmer databasen for testordrer, på et tidspunkt begynte å bli "lagt" til oss av hovedverten. CPU og IO gikk til spille, forespørsler ble redusert og ble tidsavbrutt, tjenesten var fem hundre. Rask analyse viste at spørsmål som:
delete from <table> where id in (…)I dette tilfellet var det selvfølgelig en indeks etter id i måltabellen, og svært få poster ble slettet i henhold til betingelsen. Det virket som om alt skulle fungere, men dessverre, det gjorde det ikke.
Den fantastiske kom til unnsetning forklare analysere og sa at i tillegg til å slette poster i måltabellen, er det også en referanseintegritetssjekk, og på en av de relaterte tabellene mislykkes denne kontrollen sekvensiell skanning på grunn av mangel på en passende indeks. Slik ble diagnostikk født .
Oppgave fem – nullverdi i indekser
Som standard inkluderer Postgres nullverdier i btree-indekser, men de er vanligvis ikke nødvendige der. Derfor prøver jeg flittig å kaste ut disse nullene (diagnostikk ), oppretter delvise indekser på nullbare kolonner etter type where <A> is not null. På denne måten var jeg i stand til å redusere størrelsen på en av våre indekser fra 1877 MB til 16 KB. Og i en av tjenestene reduserte databasestørrelsen totalt med 16% (med 4.3 GB i absolutte tall) på grunn av utelukkelsen av nullverdier fra indeksene. Enorme besparelser på diskplass med svært enkle modifikasjoner. 🙂
Problem seks – mangel på primærnøkler
På grunn av mekanismens natur en slik situasjon er mulig når størrelsen på bordet ditt vokser raskt på grunn av et stort antall døde poster. Jeg trodde naivt at dette ikke ville true oss, og at dette ikke ville skje med basen vår, for vi, wow!!!, er normale utviklere... Så dum og naiv jeg var...
En dag tok en fantastisk migrering og oppdaterte alle postene i en stor og aktivt brukt tabell. Vi fikk +100 GB til bordstørrelsen helt ut av det blå. Det var en jævla skam, men ulykkene våre sluttet ikke der. Etter at autovakuumet på dette bordet ble avsluttet 15 timer senere, ble det klart at den fysiske plasseringen ikke ville komme tilbake. Vi kunne ikke stoppe tjenesten og gjøre VAKUUM FULL, så vi bestemte oss for å bruke . Og så viste det seg at pg_repack vet ikke hvordan man behandler tabeller uten en primærnøkkel eller annen unikhetsbegrensning, og tabellen vår hadde ikke en primærnøkkel. Slik ble diagnostikk født .
I bibliotekversjonen 0.1.5 Muligheten til å samle inn data fra oppblåsthet av tabeller og indekser og svare på det i tide er lagt til.
Oppgaver syv og åtte - utilstrekkelige indekser og ubrukte indekser
Følgende to diagnoser er: и – dukket opp i sin endelige form relativt nylig. Poenget er at de ikke bare kunne tas og legges til.
Som jeg allerede har skrevet, bruker vi en konfigurasjon med flere replikaer, og lesebelastningen på forskjellige verter er fundamentalt forskjellig. Som et resultat viser situasjonen seg at noen tabeller og indekser på noen verter praktisk talt ikke brukes, og for analyse må du samle inn statistikk fra alle verter i klyngen. Dette er også nødvendig på hver vert i klyngen; du kan ikke gjøre dette bare på masteren.
Denne tilnærmingen tillot oss å spare flere titalls gigabyte ved å fjerne indekser som aldri ble brukt, samt legge til manglende indekser til sjelden brukte tabeller.
Som en konklusjon
Selvfølgelig, for nesten all diagnostikk kan du konfigurere . På denne måten kan du raskt implementere kontroller i applikasjonen din, forhindre at nye feil dukker opp, og deretter gradvis fikse gamle.
Noe diagnostikk kan utføres i funksjonstester umiddelbart etter utrulling av databasemigrering. Og dette er kanskje en av de kraftigste funksjonene i biblioteket mitt. Et eksempel på bruk finnes i .
Det er fornuftig å sjekke for ubrukte eller manglende indekser, så vel som for oppblåsthet, bare på en ekte database. De innsamlede verdiene kan registreres i eller sendes til overvåkingssystemet.
Jeg håper virkelig det pg-indeks-helse vil være nyttig og etterspurt. Du kan også bidra til bibliotekets utvikling ved å rapportere problemer du finner og foreslå ny diagnostikk.
Kilde: www.habr.com