Привет.
Jag heter Vanya och jag är en Java-utvecklare. Det råkar vara så att jag jobbar mycket med PostgreSQL – att sätta upp databasen, optimera strukturen, prestanda och spela lite DBA på helgerna.
Nyligen har jag gjort i ordning flera databaser i våra mikrotjänster och skrivit ett java-bibliotek , vilket gör det här arbetet enklare, sparar tid och hjälper mig att undvika några vanliga misstag som utvecklare gör. Det är detta bibliotek vi ska prata om idag.
Villkor
Huvudversionen av PostgreSQL jag arbetar med är 10. Alla SQL-frågor jag använder testas även på version 11. Den minsta versionen som stöds är 9.6.
förhistoria
Allt började för nästan ett år sedan med en situation som var märklig för mig: tävlingsskapandet av ett index slutade med ett fel. Själva indexet förblev som vanligt i databasen i ett ogiltigt tillstånd. Logganalys visade på brist . Och iväg... När jag grävde djupare upptäckte jag en hel massa problem i databaskonfigurationen och jag kavlade upp ärmarna och började fixa dem med en gnistra i ögonen.
Problem ett - standardkonfiguration
Förmodligen är alla ganska trötta på metaforen om Postgres, som kan köras på en kaffebryggare, men... standardkonfigurationen väcker verkligen ett antal frågor. Åtminstone är det värt att uppmärksamma underhållsarbete_mem, temp_file_limit, statement_timeout и lock_timeout.
I vårat fall underhållsarbete_mem var standard 64 MB, och temp_file_limit något runt 2 GB - vi hade helt enkelt inte tillräckligt med minne för att skapa ett index på ett stort bord.
Därför, i pg-index-hälsa Jag samlade en serie , enligt min mening, parametrarna som bör konfigureras för varje databas.
Problem två - dubbletter av index
Våra databaser lever på SSD-enheter, och vi använder HA-konfiguration med flera datacenter, mastervärd och n-antal repliker. Diskutrymme är en mycket värdefull resurs för oss; det är inte mindre viktigt än prestanda och CPU-förbrukning. Därför behöver vi å ena sidan index för snabb läsning, och å andra sidan vill vi inte se onödiga index i databasen, eftersom de äter upp utrymme och saktar ner datauppdateringen.
Och nu, efter att ha återställt allt och att ha sett tillräckligt , bestämde jag mig för att organisera en "bra" utrensning. Det visade sig att utvecklare inte gillar att läsa databasdokumentation. De gillar det inte särskilt mycket. På grund av detta uppstår två typiska fel - ett manuellt skapat index på en primärnyckel och ett liknande "manuellt" index på en unik kolumn. Faktum är att de inte behövs - Postgres kommer att göra allt själv. Sådana index kan säkert tas bort, och diagnostik har dykt upp för detta ändamål .
Problem tre - skärande index
De flesta nybörjare skapar index på en enda kolumn. Efter att ha upplevt denna verksamhet grundligt börjar folk gradvis optimera sina frågor och lägga till mer komplexa index som innehåller flera kolumner. Så här visas index på kolumner A, A + B, A + B + C och så vidare. De två första av dessa index kan säkert kastas ut, eftersom de är prefix till det tredje. Detta sparar också mycket diskutrymme och det finns diagnostik för detta .
Problem fyra - främmande nycklar utan index
Postgres låter dig skapa främmande nyckelbegränsningar utan att ange ett stödindex. I många situationer är detta inte ett problem, och kanske inte ens visar sig... För tillfället...
Det var samma sak med oss: det är bara det att vid någon tidpunkt började ett jobb, som körs enligt ett schema och rensa databasen för testorder, att "läggas till" till oss av huvudvärden. CPU och IO gick till spillo, förfrågningar saktade ner och fick timeout, tjänsten var femhundra. Snabb analys visade att frågor som:
delete from <table> where id in (…)I det här fallet fanns det naturligtvis ett index efter id i måltabellen, och väldigt få poster raderades enligt villkoret. Det verkade som att allt skulle fungera, men tyvärr gjorde det inte det.
Den underbara kom till undsättning förklara analysera och sa att förutom att radera poster i måltabellen finns det också en referensintegritetskontroll, och på en av de relaterade tabellerna misslyckas denna kontroll sekventiell skanning på grund av bristen på ett lämpligt index. Så föddes diagnostik .
Problem fem – nollvärde i index
Som standard inkluderar Postgres nollvärden i btree-index, men de behövs vanligtvis inte där. Därför försöker jag flitigt att kasta ut dessa nollor (diagnostik ), skapa partiella index på nollbara kolumner efter typ where <A> is not null. På så sätt kunde jag minska storleken på ett av våra index från 1877 MB till 16 KB. Och i en av tjänsterna minskade databasstorleken totalt med 16% (med 4.3 GB i absoluta tal) på grund av uteslutningen av nollvärden från indexen. Enorma besparingar på diskutrymme med mycket enkla modifieringar. 🙂
Problem sex – brist på primärnycklar
På grund av mekanismens natur en situation som denna är möjlig när storleken på ditt bord växer snabbt på grund av ett stort antal döda poster. Jag trodde naivt att detta inte skulle hota oss, och att detta inte skulle hända vår bas, för, wow!!!, vi är normala utvecklare... Vad dum och naiv jag var...
En dag tog en underbar migration och uppdaterade alla poster i en stor och aktivt använd tabell. Vi fick +100 GB till bordsstorleken direkt. Det var jävligt synd, men våra missöden slutade inte där. Efter att autovakuumet på detta bord avslutades 15 timmar senare stod det klart att den fysiska platsen inte skulle återvända. Vi kunde inte stoppa tjänsten och göra VACUUM FULL, så vi bestämde oss för att använda . Och så visade det sig pg_repack vet inte hur man bearbetar tabeller utan en primärnyckel eller annan unikhetsbegränsning, och vår tabell hade ingen primärnyckel. Så föddes diagnostik .
I biblioteksversionen 0.1.5 Möjligheten att samla in data från uppsvällda tabeller och index och svara på det i tid har lagts till.
Problem sju och åtta - otillräckliga index och oanvända index
Följande två diagnostik är: и – dök upp i sin slutliga form relativt nyligen. Poängen är att de inte bara kunde tas och läggas till.
Som jag redan skrivit använder vi en konfiguration med flera repliker, och läsbelastningen på olika värdar är fundamentalt olika. Som ett resultat visar sig situationen att vissa tabeller och index på vissa värdar praktiskt taget inte används, och för analys måste du samla in statistik från alla värdar i klustret. Detta är också nödvändigt på varje värd i klustret; du kan inte göra detta bara på mastern.
Detta tillvägagångssätt gjorde det möjligt för oss att spara flera tiotals gigabyte genom att ta bort index som aldrig användes, samt lägga till saknade index i tabeller som sällan används.
Som en slutsats
Naturligtvis kan du konfigurera nästan all diagnostik . På så sätt kan du snabbt implementera kontroller i din applikation, förhindra att nya fel dyker upp och sedan gradvis fixa gamla.
Viss diagnostik kan utföras i funktionstester omedelbart efter utrullning av databasmigreringar. Och detta är kanske en av de mest kraftfulla funktionerna i mitt bibliotek. Ett exempel på användning finns i .
Det är vettigt att utföra kontroller för oanvända eller saknade index, såväl som för bloat, endast på en riktig databas. De insamlade värdena kan registreras i eller skickas till övervakningssystemet.
Jag hoppas verkligen det pg-index-hälsa kommer att vara användbar och efterfrågad. Du kan också bidra till bibliotekets utveckling genom att rapportera problem du hittar och föreslå ny diagnostik.
Källa: will.com