Zdravlje indeksa u PostgreSQL očima Java programera

Bok.

Zovem se Vanya i ja sam Java programer. Radim puno s PostgreSQL-om – bavim se konfiguracijom baze podataka, optimizacijom strukture, performansi i pomalo igram DBA-a vikendom.

U posljednje vrijeme doveo sam u red nekoliko baza podataka u našim mikroservisima i napisao java biblioteku pg-index-health, koja olakšava taj posao, štedi moje vrijeme i pomaže u izbjegavanju nekih uobičajenih grešaka koje programeri rade. O toj biblioteci ću danas govoriti.

Zdravlje indeksa u PostgreSQL očima Java programera

Odricanje

Osnovna verzija PostgreSQL-a s kojom radim je verzija 10. Svi SQL upiti koje koristim također su testirani na verziji 11. Najmanje podržavana verzija je 9.6.

Povijest

Sve je počelo prije gotovo godinu dana s neobičnom situacijom za mene: konkurentno stvaranje indeksa na praznom mjestu završilo je greškom. Sam indeks, kao i obično, ostao je u nevažećem stanju u bazi. Analizom logova otkrio sam nedostatak temp_file_limit. I tako je počelo... Kopajući dublje, otkrio sam mnogo problema u konfiguraciji baze podataka i, zasukavši rukave, s sjajem u očima krenuo ih popravljati.

Problem prvi – zadana konfiguracija

Možda je metafora o Postgresu koji se može pokrenuti na aparatu za kavu svima već dosadila, ali... konfiguracija po defaultu doista postavlja neka pitanja. U najmanju ruku, treba obratiti pozornost na maintenance_work_mem, temp_file_limit, statement_timeout i lock_timeout.

U našem slučaju maintenance_work_mem bio je po zadano 64 MB, a temp_file_limit oko 2 GB – jednostavno nam je manjkalo memorije za stvaranje indeksa na velikoj tablici.

Zato sam u pg-index-health skupio niz ključnih, po mom mišljenju, parametara koje treba konfigurirati za svaku bazu podataka.

Problem drugi – duplicirani indeksi

Naše baze se nalaze na SSD diskovima, a koristimo HA-konfiguraciju s više podatkovnih centara, master hostom i n-nim brojem replikacija. Mjesto na disku je za nas jako dragocjen resurs; jednako je važno kao i performanse i potrošnja CPU-a. Stoga, s jedne strane, potrebni su nam indeksi za brzo čitanje, a s druge strane, ne želimo vidjeti u bazi suvišne indekse, jer oni troše prostor i usporavaju ažuriranje podataka.

I tako, nakon što smo obnovili sve nevažeće indekse i pregledali prezentacije Olega Bartunova, odlučio sam provesti „veliko“ čišćenje. Ispostavilo se da programeri ne vole čitati dokumentaciju baze podataka. Jako ne vole. Zbog toga se javljaju dvije uobičajene greške – ručno stvoren indeks na primarni ključ i sličan „ručni“ indeks na jedinstveni stupac. Riječ je o tome da nisu potrebni – Postgres će sve učiniti sam. Takve indekse možete slobodno ukloniti, a za to postoji dijagnostika duplicirani_indeksi.

Treći problem – preklapajući indeksi

Većina početnika programera stvara indekse na jedan stupac. Postupno, kako postaju sigurniji, ljudi počinju optimizirati svoje upite i dodavati složenije indekse koji uključuju više stupaca. Tako nastaju indeksi na stupcima A, A+B, A+B+C itd. Prva dva od ovih indeksa možete slobodno izbaciti, jer su prefiksi trećeg. To također značajno štedi prostor na disku i za to postoji dijagnostika preklapajući_indeksi.

Četvrti problem – vanjski ključevi bez indeksa

Postgres dopušta stvaranje ograničenja vanjskog ključa bez navođenja pratećeg indeksa. U mnogim situacijama to nije problem i može se čak ni ne manifestirati… Do određenog trenutka…

Tako je bilo i kod nas: jednostavno je u nekom trenutku, job koji se izvršavao prema rasporedu i čišćenje baze od testnih narudžbi, počeo “slagati” našeg glavnog hosta. CPU i IO su se naglo povećali, upiti su usporili i prekidali zbog vremenskog limita, servis se srušio. Brza analiza pg_stat_activity pokazala je da su se zapeli upiti tipa:

obriši iz <table> gdje je id u (…)

Pri tome je indeks po id-u u ciljnoj tablici, naravno, bio, a zapisi su se brisali prema uvjetu u vrlo malom broju. Činilo se da sve treba raditi, ali, nažalost, nije radilo.

U pomoć je došla čarobna explain analyze i objasnila da osim brisanja zapisa u ciljnoj tablici, još se vrši provjera referentne cjelovitosti, i u jednoj od povezanih tablica ta provjera ide u sekvencijalno pretraživanje zbog nedostatka odgovarajućeg indeksa. Tako je nastala dijagnostika vanjski_ključevi_bez_indeksa.

Peti problem – null vrijednost u indeksima

Zadano, Postgres uključuje null vrijednosti u btree-indekse, ali su obično nepotrebne. Stoga se trudim da ovakve null vrijednosti uklonim (dijagnostika indeksi_s_null_vrijednostima), stvarajući parcijalne indekse na nullable-stupce poput gdje nije null. Na taj način uspjeli smo smanjiti veličinu jednog od naših indeksa s 1877 MB na 16 KB. A u jednoj od usluga, ukupna veličina baze podataka smanjena je za 16% (za 4,3 GB u apsolutnim brojkama) isključivanjem null vrijednosti iz indeksa. Ogromna ušteda prostora na disku uz prilično jednostavne izmjene. 🙂

Šesti problem – nedostatak primarnih ključeva

Zbog osobitosti mehanizma MVCC u Postgresu moguća je situacija kada bloatse veličina vaše tablice brzo povećava zbog velikog broja mrtvih zapisa. Naivno sam vjerovao da nam to ne prijeti, i da se s našom bazom to neće dogoditi, jer smo, oho-ho!!!, normalni programeri… Kako sam bio glup i naivan…

Jednog divnog dana jedna čudesna migracija je ažurirala sve zapise u velikoj i aktivno korištenoj tablici. Dobili smo +100 GB dodatne veličine tablice iz ničega. Bilo je nevjerojatno frustrirajuće, ali naše nevolje tu nisu stale. Nakon što je nakon 15 sati završila autovacuum na ovoj tablici, postalo je jasno da se fizičko mjesto neće vratiti. Nismo mogli zaustaviti servis i napraviti VACUUM FULL, pa je donesena odluka da se koristi pg_repack. I tu se pokazalo da pg_repack ne može obraditi tablice bez primarnog ključa ili drugog ograničenja jedinstvenosti, a u našoj tablici nije bilo primarnog ključa. Tako je nastala dijagnostika tables_without_primary_key.

U verziji biblioteke 0.1.5 dodana je mogućnost prikupljanja podataka o bloat-u tablica i indeksa i pravovremenog reagiranja na njega.

Problemi sedam i osam – nedostatak indeksa i neiskorišteni indeksi

Dva sljedeća dijagnostička alata — tables_with_missing_indexes i unused_indexes – u svom konačnom obliku pojavili su se relativno nedavno. I stvar je u tome da ih nije bilo moguće jednostavno dodati.

Kao što sam već spomenuo, koristimo konfiguraciju s više replika, a čitateljski opterećenje na različitim hostovima je principijelno različito. Na kraju se stvara situacija da neke tablice i indeksi na nekim hostovima praktički nisu korišteni, a za analizu je potrebno prikupljati statistiku sa svih hostova u klasteru. Resetiranje statistike također treba biti učinjeno na svakom hostu u klasteru, ne može se napraviti samo na glavnom.

Ovaj pristup nam je omogućio da uštedimo nekoliko desetaka gigabajta brisanjem indeksa koji nikada nisu korišćeni, a takođe da dodamo nedostajuće indekse na retko korišćene tabele.

Kao zaključak

Naravno, praktično za sve dijagnostike mogu se postaviti lista izuzetaka. Na taj način možete brzo implementirati provere u vašoj aplikaciji, sprečavajući pojavu novih grešaka, a zatim postepeno ispravljati stare.

Deo dijagnostika može se izvoditi već u funkcionalnim testovima odmah nakon uvođenja migracija baze podataka. I to je, verovatno, jedna od najmoćnijih mogućnosti moje biblioteke. Primer korišćenja možete pogledati u demo.

Provere neupotrebljenih ili nedostajućih indeksa, kao i onih koje su otežane (bloat), ima smisla izvoditi samo na pravoj bazi podataka. Sakupljene vrednosti mogu se zabeležiti u ClickHouse ili poslati u sistem za monitoring.

Zaista se nadam da pg-index-health će biti korisna i tražena. Takođe možete pomoći razvoju biblioteke tako što ćete prijaviti otkrivene probleme i predložiti nove dijagnostike.

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster