Sveikinimai.
Mano vardas Vanya ir aš esu Java kūrėjas. Taip atsitiko, kad aš daug dirbu su PostgreSQL – nustatau duomenų bazę, optimizuoju struktūrą, našumą ir savaitgaliais pažaidžiu DBA.
Neseniai sutvarkiau keletą duomenų bazių mūsų mikroservisuose ir parašiau java biblioteką , kuris palengvina šį darbą, sutaupo laiko ir padeda išvengti kai kurių dažniausiai kūrėjų daromų klaidų. Būtent apie šią biblioteką šiandien ir kalbėsime.
Atsakomybės neigimas
Pagrindinė PostgreSQL versija, su kuria dirbu, yra 10. Visos mano naudojamos SQL užklausos taip pat išbandytos 11 versijoje. Mažiausiai palaikoma versija yra 9.6.
priešistorė
Viskas prasidėjo beveik prieš metus nuo man keistos situacijos: konkursinis indekso kūrimas netikėtai baigėsi klaida. Pats indeksas, kaip įprasta, liko duomenų bazėje netinkamos būsenos. Žurnalų analizė parodė trūkumą . Ir važiuojam... Gilindamasis aptikau visą krūvą problemų duomenų bazės konfigūracijoje ir, pasiraitojęs rankoves, su blizgučiais akyse ėmiau jas taisyti.
Viena problema – numatytoji konfigūracija
Tikriausiai visi jau gerokai pavargę nuo metaforos apie Postgres, kurią galima paleisti kavos virimo aparatu, bet... numatytoji konfigūracija tikrai kelia nemažai klausimų. Bent jau verta atkreipti dėmesį priežiūros_darbo_mem, temp_file_limit, pareiškimo_laikas и lock_timeout.
Mūsų atveju priežiūros_darbo_mem buvo numatytasis 64 MB ir temp_file_limit maždaug 2 GB – tiesiog neturėjome pakankamai atminties didelės lentelės indeksui sukurti.
Todėl į pg-index-health Surinkau seriją , mano nuomone, parametrai, kurie turėtų būti sukonfigūruoti kiekvienai duomenų bazei.
Antra problema – pasikartojantys indeksai
Mūsų duomenų bazės veikia SSD diskuose, ir mes naudojame HA-Konfigūracija su keliais duomenų centrais, pagrindiniu kompiuteriu ir n- kopijų skaičius. Vieta diske mums yra labai vertingas šaltinis; tai ne mažiau svarbu nei našumas ir procesoriaus suvartojimas. Todėl, viena vertus, mums reikia indeksų greitam skaitymui, kita vertus, nenorime duomenų bazėje matyti nereikalingų indeksų, nes jie užima vietą ir sulėtina duomenų atnaujinimą.
O dabar, viską atstačius ir pakankamai matęs , nusprendžiau surengti „puikų“ valymą. Paaiškėjo, kad kūrėjai nemėgsta skaityti duomenų bazės dokumentacijos. Jiems tai labai nepatinka. Dėl šios priežasties atsiranda dvi tipinės klaidos - rankiniu būdu sukurtas indeksas pirminiame rakte ir panašus „rankinis“ indeksas unikaliame stulpelyje. Faktas yra tas, kad jie nereikalingi – Postgres viską padarys pats. Tokius indeksus galima saugiai ištrinti, tam atsirado diagnostika .
Trečia problema – susikertantys indeksai
Dauguma pradedančiųjų kūrėjų indeksus kuria viename stulpelyje. Palaipsniui, nuodugniai patyrę šį verslą, žmonės pradeda optimizuoti savo užklausas ir pridėti sudėtingesnių indeksų, kuriuose yra keli stulpeliai. Taip atsiranda stulpelių rodyklės A, A + B, A+B+C. ir taip toliau. Pirmuosius du iš šių indeksų galima saugiai išmesti, nes jie yra trečiojo priešdėliai. Tai taip pat sutaupo daug vietos diske ir tam yra diagnostika .
Ketvirta problema – išoriniai raktai be indeksų
„Postgres“ leidžia kurti išorinio rakto apribojimus nenurodant atsarginio indekso. Daugeliu atvejų tai nėra problema ir gali net nepasireikšti... Kol kas...
Taip buvo ir su mumis: tiesiog tam tikru momentu darbą, vykdantį pagal grafiką ir išvalantį bandomųjų užsakymų duomenų bazę, mums pradėjo „pridėti“ pagrindinis šeimininkas. CPU ir IO nuėjo perniek, užklausos sulėtėjo ir baigėsi, paslauga buvo penki šimtai. Greita analizė parodė, kad tokios užklausos:
delete from <table> where id in (…)Šiuo atveju, žinoma, tikslinėje lentelėje buvo indeksas pagal id ir labai mažai įrašų buvo ištrinta pagal sąlygą. Atrodė, kad viskas turėtų veikti, bet, deja, nepavyko.
Į pagalbą atėjo nuostabusis paaiškinti analizuoti ir pasakė, kad be įrašų ištrynimo tikslinėje lentelėje, taip pat yra tikrinamas nuorodos vientisumas, o vienoje iš susijusių lentelių šis patikrinimas nepavyko. nuoseklus nuskaitymas dėl tinkamo indekso trūkumo. Taip gimė diagnostika .
Penkta užduotis – nulinė reikšmė indeksuose
Pagal numatytuosius nustatymus „Postgres“ į „btree“ indeksus įtraukia nulines reikšmes, tačiau paprastai jų ten nereikia. Todėl stropiai stengiuosi išmesti šiuos nulius (diagnostika ), sukuriant dalinius indeksus nuliniuose stulpeliuose pagal tipą where <A> is not null. Tokiu būdu man pavyko sumažinti vieno iš mūsų indeksų dydį nuo 1877 MB iki 16 KB. Ir vienoje iš paslaugų duomenų bazės dydis iš viso sumažėjo 16% (4.3 GB absoliučiais skaičiais), nes iš indeksų neįtrauktos nulinės reikšmės. Didžiuliai sutaupoma vietos diske su labai paprastais pakeitimais. 🙂
Šešta problema – pirminių raktų trūkumas
Dėl mechanizmo pobūdžio tokia situacija galima kai jūsų lentelės dydis sparčiai auga dėl daugybės negyvų įrašų. Naiviai tikėjau, kad tai mums negresia, o mūsų bazei taip neatsitiks, nes, va!!!, mes normalūs kūrėjai... Koks aš kvailas ir naivus buvau...
Vieną dieną viena nuostabi migracija paėmė ir atnaujino visus įrašus didelėje ir aktyviai naudojamoje lentelėje. Iš netikėtumo gavome +100 GB iki stalo dydžio. Buvo velniškai gaila, bet mūsų nesėkmės tuo nesibaigė. Po 15 valandų pasibaigus autovakuumui ant šio stalo tapo aišku, kad fizinė vieta nebegrįš. Negalėjome sustabdyti paslaugos ir padaryti VACUUM FULL, todėl nusprendėme pasinaudoti . Ir tada paaiškėjo, kad pg_repack nežino, kaip apdoroti lenteles be pirminio rakto ar kito unikalumo apribojimo, o mūsų lentelė neturėjo pirminio rakto. Taip gimė diagnostika .
Bibliotekos versijoje 0.1.5 Pridėta galimybė rinkti duomenis iš išpūstų lentelių ir indeksų ir laiku į juos reaguoti.
Septintos ir aštuntos problemos – nepakankami indeksai ir nenaudojami indeksai
Šios dvi diagnostikos yra: и – galutine forma pasirodė palyginti neseniai. Esmė ta, kad jų negalima tiesiog paimti ir pridėti.
Kaip jau rašiau, mes naudojame konfigūraciją su keliomis kopijomis, o skirtingų kompiuterių skaitymo apkrova iš esmės skiriasi. Dėl to situacija paaiškėja, kad kai kurios lentelės ir indeksai kai kuriuose kompiuteriuose praktiškai nenaudojami, o analizei reikia rinkti statistiką iš visų klasterio kompiuterių. Tai taip pat būtina kiekviename klasterio pagrindiniame kompiuteryje; to negalite padaryti tik pagrindiniame kompiuteryje.
Šis metodas leido sutaupyti kelias dešimtis gigabaitų pašalinant indeksus, kurie niekada nebuvo naudojami, taip pat įtraukiant trūkstamus indeksus į retai naudojamas lenteles.
Kaip išvadą
Žinoma, beveik visos diagnostikos galite konfigūruoti . Tokiu būdu galite greitai įdiegti patikrinimus savo programoje, kad neatsirastų naujų klaidų, o tada palaipsniui ištaisyti senas.
Kai kurios diagnostikos gali būti atliekamos atliekant funkcinius testus iš karto po duomenų bazės perkėlimo. Ir tai turbūt viena galingiausių mano bibliotekos funkcijų. Naudojimo pavyzdį galite rasti .
Tikslinga tikrinti, ar nėra nenaudojamų ar trūkstamų indeksų, taip pat ar nėra išsipūtimo, tik tikroje duomenų bazėje. Surinktas vertes galima įrašyti arba siunčiami į stebėjimo sistemą.
Aš tikrai to tikiuosi pg-index-health bus naudingi ir paklausūs. Taip pat galite prisidėti prie bibliotekos plėtros pranešdami apie rastas problemas ir siūlydami naujas diagnostikos priemones.
Šaltinis: www.habr.com