Pozdrav.
Moje ime je Vanya i ja sam Java programer. Dogodilo se da puno radim s PostgreSQL-om - postavljam bazu podataka, optimiziram strukturu, performanse i malo se igram DBA vikendom.
Nedavno sam sredio nekoliko baza podataka u našim mikroservisima i napisao java biblioteku , što olakšava ovaj posao, štedi mi vrijeme i pomaže mi da izbjegnem neke uobičajene pogreške programera. Upravo o ovoj knjižnici ćemo danas govoriti.
Izjava o odricanju od odgovornosti
Glavna verzija PostgreSQL-a s kojom radim je 10. Svi SQL upiti koje koristim također su testirani na verziji 11. Minimalna podržana verzija je 9.6.
prapovijest
Sve je počelo prije gotovo godinu dana meni čudnom situacijom: natjecateljsko kreiranje indeksa iz vedra neba završilo je greškom. Sam indeks je, kao i obično, ostao u bazi u nevažećem stanju. Analiza dnevnika pokazala je manjak . I idemo... Kopajući dublje, otkrio sam hrpu problema u konfiguraciji baze podataka i, zasukavši rukave, počeo ih rješavati sa sjajem u očima.
Problem jedan - zadana konfiguracija
Vjerojatno su svi već prilično umorni od metafore o Postgresu koji se može pokrenuti na aparatu za kavu, ali... zadana konfiguracija doista otvara niz pitanja. U najmanju ruku, vrijedi obratiti pozornost rad_na_održavanju_mem, ograničenje_temp_datoteke, istek_izjave и lock_timeout.
U našem slučaju rad_na_održavanju_mem bilo je zadanih 64 MB i ograničenje_temp_datoteke nešto oko 2 GB - jednostavno nismo imali dovoljno memorije za izradu indeksa na velikoj tablici.
Stoga, u pg-index-zdravlje Skupio sam niz , po mom mišljenju, parametri koje treba konfigurirati za svaku bazu podataka.
Drugi problem - duplicirani indeksi
Naše baze podataka žive na SSD diskovima, a mi ih koristimo HA-konfiguracija s više podatkovnih centara, glavnim hostom i n-broj replika. Prostor na disku za nas je vrlo vrijedan resurs; nije ništa manje važno od performansi i potrošnje CPU-a. Dakle, s jedne strane, potrebni su nam indeksi za brzo čitanje, as druge strane, ne želimo vidjeti nepotrebne indekse u bazi, jer oni gutaju prostor i usporavaju ažuriranje podataka.
I sada, nakon što je sve obnovljeno a vidjevši dovoljno , odlučio sam organizirati “veliku” čistku. Pokazalo se da programeri ne vole čitati dokumentaciju baze podataka. Ne sviđa im se baš. Zbog toga se pojavljuju dvije tipične pogreške - ručno kreiran indeks na primarnom ključu i sličan "ručni" indeks na jedinstvenom stupcu. Činjenica je da oni nisu potrebni - Postgres će sve učiniti sam. Takvi se indeksi mogu sigurno izbrisati, a za tu svrhu pojavila se i dijagnostika .
Problem tri - indeksi koji se sijeku
Većina programera početnika stvara indekse na jednom stupcu. Postupno, 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 stupcima 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 puno prostora na disku i za to postoji dijagnostika .
Problem četvrti - strani ključevi bez indeksa
Postgres vam omogućuje stvaranje ograničenja stranog ključa bez određivanja pozadinskog indeksa. U mnogim situacijama to nije problem, a možda se niti ne manifestira... Za sada...
Isto je bilo i s nama: samo što nam je u nekom trenutku glavni host počeo "dodavati" posao koji se odvija prema rasporedu i čisti bazu testnih naloga. CPU i IO su propali, zahtjevi su usporeni i isteklo je vrijeme, usluga je bila pet stotina. Brza analiza pokazalo je da upiti poput:
delete from <table> where id in (…)U ovom slučaju, naravno, postojao je indeks prema id-u u ciljnoj tablici, a vrlo je malo zapisa izbrisano prema uvjetu. Činilo se da bi sve trebalo funkcionirati, ali, nažalost, nije.
Divni je priskočio u pomoć objasniti analizirati i rekao da osim brisanja zapisa u ciljnoj tablici postoji i provjera referentnog integriteta, a na jednoj od povezanih tablica ta provjera ne uspijeva sekvencijalno skeniranje zbog nedostatka odgovarajućeg indeksa. Tako je rođena dijagnostika .
Problem pet – null vrijednost u indeksima
Prema zadanim postavkama, Postgres uključuje null vrijednosti u btree indekse, ali one tamo obično nisu potrebne. Stoga marljivo pokušavam izbaciti ove nule (dijagnostika ), stvarajući djelomične indekse na stupcima s nullom prema vrsti where <A> is not null. Na taj sam način uspio smanjiti veličinu jednog od naših indeksa s 1877 MB na 16 KB. A u jednoj od usluga veličina baze podataka smanjila se ukupno za 16% (za 4.3 GB u apsolutnim brojevima) zbog isključivanja nultih vrijednosti iz indeksa. Ogromna ušteda prostora na disku uz vrlo jednostavne izmjene. 🙂
Problem šest – 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 vjerovao da nas ovo neće ugroziti, i da se to neće dogoditi našoj bazi, jer, wow!!!, mi smo normalni programeri... Kako sam bio glup i naivan...
Jednog dana, jedna prekrasna migracija uzela je i ažurirala sve zapise u velikoj i aktivno korištenoj tablici. Dobili smo +100 GB na veličinu tablice 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 pokazalo da pg_repack ne zna kako obraditi tablice bez primarnog ključa ili drugog ograničenja jedinstvenosti, a naša tablica nije imala primarni ključ. Tako je rođena dijagnostika .
U bibliotečkoj verziji 0.1.5 Dodana je mogućnost prikupljanja podataka iz gomile tablica i indeksa i pravovremenog reagiranja na njih.
Problemi sedam i osam - nedovoljni indeksi i neiskorišteni indeksi
Sljedeće dvije dijagnostike su: и – pojavili su se u konačnom obliku relativno nedavno. Poanta je da se ne mogu samo uzeti i dodati.
Kao što sam već napisao, koristimo konfiguraciju s nekoliko replika, a opterećenje čitanja na različitim hostovima bitno je drugačije. 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 potrebno na svakom hostu u klasteru; to ne možete učiniti samo na masteru.
Ovaj pristup omogućio nam je uštedu nekoliko desetaka gigabajta uklanjanjem indeksa koji se nikada nisu koristili, kao i dodavanjem indeksa koji nedostaju u rijetko korištene tablice.
Kao zaključak
Naravno, za gotovo sve dijagnostike možete konfigurirati . Na taj način možete brzo implementirati provjere u svoju aplikaciju, spriječiti pojavu novih grešaka, a zatim postupno popraviti stare.
Neke se dijagnostike mogu izvesti u funkcionalnim testovima odmah nakon pokretanja migracija baze podataka. A ovo je možda jedna od najmoćnijih značajki moje knjižnice. Primjer upotrebe može se naći u .
Ima smisla provoditi provjere za neiskorištene ili nedostajuće indekse, kao i za bloat, samo na stvarnoj bazi podataka. Prikupljene vrijednosti mogu se zabilježiti u ili poslati u sustav praćenja.
Tome se stvarno nadam pg-index-zdravlje bit će korisni i traženi. Također možete pridonijeti razvoju knjižnice prijavljivanjem problema koje pronađete i predlaganjem nove dijagnostike.
Izvor: www.habr.com