Hej.
Jag heter Vanya och jag Àr Java-utvecklare. Det rÄkade bli sÄ att jag arbetar mycket med PostgreSQL - jag sysslar med att konfigurera databaser, optimera strukturer, prestanda och leker lite DBA pÄ helgerna.
Under senaste tiden har jag ordnat upp flera databaser i vÄra mikrotjÀnster och skrivit ett Java-bibliotek , som underlÀttar detta arbete, sparar min tid och hjÀlper till att undvika vissa typiska misstag som utvecklare gör. Just om detta bibliotek kommer jag att prata idag.
Ansvarsfriskrivning
Den huvudsakliga versionen av PostgreSQL som jag arbetar med Àr 10. Alla SQL-frÄgor jag anvÀnder har ocksÄ testats pÄ version 11. Den minimi stödja versionen Àr 9.6.
Bakgrund
Det hela började för nÀstan ett Är sedan med en situation som kÀndes mÀrklig för mig: konkurrerande skapande av index pÄ en tom plats avslutades med ett fel. SjÀlva indexet, som det ofta Àr, förblev i ett ogiltigt tillstÄnd i databasen. Logganalysen visade brist pÄ . Och sÄ började det... NÀr jag grÀvde djupare upptÀckte jag en hel uppsÀttning problem i databasens konfiguration och, med Àrmarna uppkavlade och glans i ögonen, satte jag igÄng att ÄtgÀrda dem.
Första problemet â standardkonfigurationen
Kanske Àr metaforen om Postgres som kan köras pÄ en kaffebryggare redan tröttsam för mÄnga, men... standardkonfigurationen vÀcker verkligen en hel del frÄgor. à tminstone bör man uppmÀrksamma maintenance_work_mem, temp_file_limit, statement_timeout och lock_timeout.
I vÄrt fall maintenance_work_mem var som standard 64 megabyte, och temp_file_limit nÄgot runt 2 gigabyte - vi hade helt enkelt inte tillrÀckligt med minne för att skapa ett index pÄ en stor tabell.
DÀrför har jag i pg-index-health samlat ett antal parametrar som jag tycker bör justeras för varje databas.
Andra problemet â dubblettindex
VÄra databaser lever pÄ SSD-diskar, och vi anvÀnder HA-konfiguration med flera datacenter, en huvudvÀrd och n-antal replikat. Utrymme pÄ disken Àr en mycket vÀrdefull resurs för oss; det Àr lika viktigt som prestanda och CPU-anvÀndning. SÄ, Ä ena sidan behöver vi index för snabb lÀsning, Ä andra sidan vill vi inte se onödiga index i databasen, eftersom de Àter utrymme och fördröjer uppdatering av data.
Och sÄ, efter att vi ÄterstÀllt alla och sett pÄ , jag beslutade mig för att genomföra en "stor" rensning. Det visade sig att utvecklare inte gillar att lÀsa dokumentation om databaser. De gillar det verkligen inte. PÄ grund av detta uppstÄr tvÄ typiska fel - manuellt skapade index pÄ primÀrnyckeln och ett liknande "handgjort" index pÄ en unik kolumn. Saken Àr den att de inte behövs - Postgres sköter det sjÀlv. SÄdana index kan du tryggt ta bort, och för detta har vi en diagnostik. .
Tredje problemet - överlappande index
De flesta nybörjande utvecklare skapar index för en kolumn. Gradvis, efter att ha vant sig vid detta, börjar folk optimera sina frÄgor och lÀgga till mer komplexa index som inkluderar flera kolumner. SÄdana index uppstÄr för kolumner A, A+B, A+B+C och sÄ vidare. De första tvÄ av dessa index kan du tryggt kasta bort, eftersom de Àr prefix till det tredje. Detta sparar ocksÄ mycket plats pÄ disken, och för detta finns en diagnostik. .
FjÀrde problemet - frÀmmande nycklar utan index
Postgres tillÄter skapande av begrÀnsningar för frÀmmande nycklar utan att ange stödjande index. I mÄnga situationer Àr detta inget problem, och det kan till och med inte pÄverka... Tills vidare...
SÄ var det Àven för oss: bara vid nÄgot tillfÀlle började jobbet, som kördes enligt schema och rensade databasen frÄn testbestÀllningar, att "lagra" oss en master host. CPU och IO sköt i höjden, förfrÄgningarna plockade och avbröts pÄ grund av timeout, tjÀnsten svarade med femhundra. En snabb analys visade att förfrÄgningar av typen:
ta bort från <table> där id i (…)Samtidigt fanns det naturligtvis ett index pĂ„ id i mĂ„ltabellen, och poster togs bort enligt villkoret, vilket var alldeles för lite. Det kĂ€ndes som om allt borde fungera, men tyvĂ€rr fungerade det inte.
Till hjÀlp kom den underbara explain analyze och berÀttade att förutom att radera poster i mÄltabellen, sÄ gjordes det ocksÄ en kontroll av referensintegriteten, och pÄ en av de relaterade tabellerna föll denna kontroll ner i sequential scan pÄ grund av bristande passande index. SÄ föddes diagnostiken .
Femte problemet - null-vÀrden i index
Som standard inkluderar Postgres null-vĂ€rden i btree-index, men de Ă€r oftast inte nödvĂ€ndiga dĂ€r. DĂ€rför anstrĂ€nger jag mig för att ta bort dessa null-vĂ€rden (diagnostik ), genom att skapa partiella index pĂ„ nullable-kolumner av typen where is not null. Genom denna metod lyckades jag minska storleken pĂ„ ett av vĂ„ra index frĂ„n 1877 MB till 16 KB. I en av tjĂ€nsterna minskade storleken pĂ„ databasen totalt sett med 16 % (med 4,3 GB i absoluta siffror) genom att utesluta null-vĂ€rden frĂ„n indexen. En kolossal besparing av diskuppgifter med relativt enkla förĂ€ndringar. đ
Problem sex â avsaknad av primĂ€ra nycklar
PÄ grund av egenskaperna hos mekanismen kan en sÄdan situation uppstÄ som , nÀr storleken pÄ din tabell snabbt vÀxer pÄ grund av ett stort antal döda poster. Jag var naiv nog att tro att detta inte skulle drabba oss och att vÄr databas inte skulle drabbas, för vi Àr ju, wow!!!, helt normala utvecklare... Hur dum och naiv jag var...
En vacker dag uppdaterade en fantastisk migration alla poster i en stor och aktivt anvÀnd tabell. Vi fick +100 GB till storleken pÄ tabellen utan anledning. Det var jÀttefrustrerande, men vÄra motgÄngar slutade inte dÀr. Efter att autovakuum hade avslutats pÄ denna tabell efter 15 timmar stod det klart att det fysiska utrymmet inte skulle Äterkomma. Vi kunde inte stoppa tjÀnsten och göra VACUUM FULL, sÄ beslutet togs att anvÀnda . HÀr visade det sig att pg_repack inte kan hantera tabeller utan primÀrnyckel eller nÄgot annat unikt begrÀnsningskrav, och i vÄr tabell fanns ingen primÀrnyckel. SÄ diagnostiken föddes .
I versionen av biblioteket 0.1.5 lÀggs möjligheten till att samla in data om tabellernas och indexens bloat och reagera pÄ det i tid.
Problem sju och Ă„tta â brist pĂ„ index och oanvĂ€nda index
De tvĂ„ följande diagnostikerna â och â i sin slutliga version dök upp relativt nyligen. Anledningen Ă€r att de inte bara kunde lĂ€ggas till.
Som jag redan har nÀmnt anvÀnder vi en konfiguration med flera repliker, och lÀsbelastningen pÄ olika vÀrdar Àr principiellt olika. I slutÀndan fÄr vi en situation dÀr vissa tabeller och index pÄ vissa vÀrdar praktiskt taget inte anvÀnds, och för analysen mÄste vi samla statistiken frÄn alla vÀrdar i klustret. Àr ocksÄ nödvÀndigt pÄ varje vÀrd i klustret, det gÄr inte att göra detta endast pÄ master.
Denna metod har gjort det möjligt för oss att spara flera tiotals gigabyte genom att ta bort index som aldrig har anvÀnts, samt lÀgga till saknade index pÄ sÀllan anvÀnda tabeller.
Som en avslutning
SjÀlvklart kan de flesta diagnoser stÀllas in . PÄ sÄ sÀtt kan kontroller snabbt implementeras i din applikation, vilket förhindrar uppkomsten av nya fel, och dÀrefter kan gamla fel gradvis fixas.
En del diagnoser kan utföras redan under funktionella tester direkt efter att databasmigreringar har genomförts. Och detta Àr kanske en av de kraftfullaste funktionerna i mitt bibliotek. Exempel pÄ anvÀndning kan ses i .
Kontroller för oanvÀnda eller saknade index, samt för bloat, bör endast utföras pÄ en verklig databas. Samlade vÀrden kan skrivas till eller skickas till övervakningssystemet.
Jag hoppas verkligen att pg-index-health kommer att vara anvÀndbar och efterfrÄgad. Du kan ocksÄ bidra till utvecklingen av biblioteket genom att rapportera upptÀckta problem och föreslÄ nya diagnoser.
KĂ€lla: habr.com
