Ćao
Moje ime je Vanya i ja sam Java programer. Desilo se da dosta radim sa PostgreSQL-om - postavljam bazu podataka, optimizujem strukturu, performanse i igram malo DBA vikendom.
Nedavno sam sredio nekoliko baza podataka u našim mikroservisima i napisao java biblioteku , što olakšava ovaj rad, štedi mi vrijeme i pomaže mi da izbjegnem neke uobičajene greške programera. O ovoj biblioteci ćemo danas govoriti.
odricanje
Glavna verzija PostgreSQL-a sa kojom radim je 10. Svi SQL upiti koje koristim su također testirani na verziji 11. Minimalna podržana verzija je 9.6.
prapovijest
Sve je počelo prije skoro godinu dana sa situacijom koja mi je bila čudna: natjecateljsko kreiranje indeksa iz vedra neba završilo se greškom. Sam indeks je, kao i obično, ostao u bazi podataka u neispravnom stanju. Analiza dnevnika je pokazala manjak . I krećemo... Kopajući dublje, otkrio sam gomilu problema u konfiguraciji baze podataka i, zasukavši rukave, počeo da ih popravljam sa sjajem u očima.
Prvi problem - podrazumevana konfiguracija
Vjerovatno su svi prilično umorni od metafore o Postgresu, koji se može pokrenuti na aparatu za kafu, ali... zadana konfiguracija zaista postavlja brojna pitanja. U najmanju ruku, vrijedi obratiti pažnju održavanje_rad_mem, temp_file_limit, statement_timeout и lock_timeout.
U našem slučaju održavanje_rad_mem je bio zadani 64 MB, i temp_file_limit nešto oko 2 GB - jednostavno nismo imali dovoljno memorije da kreiramo indeks na velikoj tabeli.
Stoga, u pg-indeks-zdravlje Sakupio sam seriju , po mom mišljenju, parametri koje treba konfigurisati za svaku bazu podataka.
Problem dva - dupli indeksi
Naše baze podataka žive na SSD diskovima i koristimo ih HA-konfiguracija sa više data centara, master host i n-broj replika. Prostor na disku je veoma vrijedan resurs za nas; nije ništa manje važno od performansi i potrošnje CPU-a. Stoga su nam s jedne strane potrebni indeksi za brzo čitanje, a s druge strane ne želimo da vidimo nepotrebne indekse u bazi podataka, jer jedu prostor i usporavaju ažuriranje podataka.
A sada, nakon što sam sve obnovio i videvši dovoljno , odlučio sam da organizujem “veliku” čistku. Pokazalo se da programeri ne vole čitati dokumentaciju baze podataka. Ne sviđa im se mnogo. Zbog toga nastaju dvije tipične greške - ručno kreirani indeks na primarnom ključu i sličan "ručni" indeks na jedinstvenom stupcu. Činjenica je da oni nisu potrebni - Postgres će sve uraditi sam. Takvi indeksi se mogu bezbedno brisati, a u tu svrhu se pojavila dijagnostika .
Zadatak tri - indeksi koji se seku
Većina programera početnika kreira indekse na jednoj koloni. Postepeno, nakon što su temeljito iskusili ovaj posao, ljudi počinju optimizirati svoje upite i dodavati složenije indekse koji uključuju nekoliko stupaca. Ovako se pojavljuju indeksi na kolonama A, A + B, A + B + C i tako dalje. Prva dva od ovih indeksa mogu se sigurno izbaciti, jer su prefiksi trećeg. Ovo također štedi mnogo prostora na disku i za to postoji dijagnostika .
Problem četiri - strani ključevi bez indeksa
Postgres vam omogućava da kreirate ograničenja stranog ključa bez navođenja indeksa podrške. U mnogim situacijama to nije problem, a možda se ni ne manifestira... Za sada...
Kod nas je to bio slučaj: samo što je u nekom trenutku posao, koji radi po rasporedu i briše bazu testnih naloga, počeo da nam "slaže" glavni host. CPU i IO su se potrošili, zahtjevi su se usporili i istekao je rok, usluga je bila pet stotina. Brza analiza pokazao da su upiti poput:
delete from <table> where id in (…)U ovom slučaju, naravno, postojao je indeks po id-u u ciljnoj tabeli, a vrlo malo zapisa je obrisano u skladu sa uslovom. Činilo se da bi sve trebalo da funkcioniše, ali, nažalost, nije.
Divna je priskočila u pomoć objasniti analizirati i rekao da pored brisanja zapisa u ciljnoj tabeli postoji i provjera referentnog integriteta, a na jednoj od povezanih tabela ova provjera ne uspijeva sekvencijalno skeniranje zbog nedostatka odgovarajućeg indeksa. Tako je rođena dijagnostika .
Problem peti – nulta vrijednost u indeksima
Po defaultu, Postgres uključuje null vrijednosti u btree indeksima, ali tamo obično nisu potrebne. Stoga, marljivo pokušavam izbaciti ove nule (dijagnostika ), kreiranje parcijalnih indeksa na null kolonama po tipu where <A> is not null. Na taj način sam uspio smanjiti veličinu jednog od naših indeksa sa 1877 MB na 16 KB. A u jednom od servisa, veličina baze podataka smanjena je za ukupno 16% (za 4.3 GB u apsolutnim brojevima) zbog isključenja nul vrijednosti iz indeksa. Ogromna ušteda na disku sa vrlo jednostavnim modifikacijama. 🙂
Šesti problem – nedostatak primarnih ključeva
Zbog prirode mehanizma moguća je ovakva situacija kada veličina vaše tablice brzo raste zbog velikog broja mrtvih zapisa. Naivno sam verovao da nas ovo neće ugroziti, i da se to neće desiti našoj bazi, jer, vau!!!, mi smo normalni programeri... Kako sam bio glup i naivan...
Jednog dana, jedna divna migracija uzela je i ažurirala sve zapise u velikoj i aktivno korištenoj tabeli. Dobili smo +100 GB do veličine tabele iz vedra neba. Bila je to prokleta šteta, ali našim nesrećama tu nije bio kraj. Nakon što je autovakuum na ovom stolu završio 15 sati kasnije, postalo je jasno da se fizička lokacija neće vratiti. Nismo mogli zaustaviti uslugu i napraviti VACUUM FULL, pa smo odlučili koristiti . A onda se to ispostavilo pg_repack ne zna kako da obrađuje tabele bez primarnog ključa ili drugog ograničenja jedinstvenosti, a naša tabela nije imala primarni ključ. Tako je rođena dijagnostika .
U bibliotečkoj verziji 0.1.5 Dodana je mogućnost prikupljanja podataka iz napuhanih tabela i indeksa i pravovremenog odgovora na njih.
Problemi sedam i osam - nedovoljni indeksi i neiskorišteni indeksi
Sljedeće dvije dijagnostike su: и – pojavile su se u svom konačnom obliku relativno nedavno. Poenta je da se oni ne mogu samo uzeti i dodati.
Kao što sam već napisao, koristimo konfiguraciju s nekoliko replika, a opterećenje čitanja na različitim hostovima je bitno različito. Kao rezultat toga, ispada da se neke tablice i indeksi na nekim hostovima praktički ne koriste, a za analizu morate prikupiti statistiku sa svih hostova u klasteru. Ovo je također neophodno na svakom hostu u klasteru; to ne možete učiniti samo na masteru.
Ovaj pristup nam je omogućio da uštedimo nekoliko desetina gigabajta uklanjanjem indeksa koji nikada nisu korišteni, kao i dodavanjem indeksa koji nedostaju u rijetko korištene tabele.
Kao zaključak
Naravno, za gotovo svu dijagnostiku možete konfigurirati . Na ovaj način možete brzo implementirati provjere u svoju aplikaciju, sprječavajući pojavu novih grešaka, a zatim postepeno ispravljati stare.
Neka dijagnostika se može izvesti u funkcionalnim testovima odmah nakon uvođenja migracija baze podataka. A ovo je možda jedna od najmoćnijih karakteristika moje biblioteke. Primjer upotrebe možete pronaći u .
Ima smisla izvršiti provjere za neiskorištene ili nedostajuće indekse, kao i za naduvavanje, samo na stvarnoj bazi podataka. Prikupljene vrijednosti se mogu zabilježiti ili poslati u sistem za praćenje.
Zaista se tome nadam pg-indeks-zdravlje biće korisna i tražena. Također možete doprinijeti razvoju biblioteke tako što ćete prijaviti probleme koje pronađete i predložiti novu dijagnostiku.
izvor: www.habr.com