Zdravlje indeksa u PostgreSQL očima Java programera

Zdravo.

Zovem se Ivan i ja sam Java developer. Tako se dogodi da puno radim s PostgreSQL-om – bavim se podešavanjem baza podataka, optimizacijom strukture, performansi i pomalo igram DBA-a tokom vikenda.

U poslednje vreme sam doveo u red nekoliko baza podataka u našim mikroservisima i napisao java biblioteku pg-index-health, koja olakšava ovaj posao, štedi moje vreme i pomaže da izbegnem neke uobičajene greške koje prave programeri. O ovoj biblioteci će danas biti reči.

Zdravlje indeksa u PostgreSQL očima Java programera

Odricanje od odgovornosti

Glavna verzija PostgreSQL-a sa kojom radim je 10. Sve SQL upite koje koristim sam takođe proverio na 11. verziji. Minimalno podržavana verzija je 9.6.

Pozadina

Sve je počelo pre skoro godinu dana sa situacijom koja mi je bila čudna: konkurentno kreiranje indeksa na ravnom terenu završilo je greškom. Sam indeks je, kao što to obično biva, ostao u nevažećem stanju u bazi. Analizom logova je otkriveno da nedostaje temp_file_limit. I onda je krenulo... Kada sam dublje kopao, otkrio sam čitav niz problema u konfiguraciji baze podataka, i, zamućujući rukave, sa sjajem u očima, počeo da ih otklanjam.

Prvi problem – podrazumevana konfiguracija

Verovatno, metafora o Postgresu koji se može pokrenuti na kafiću je svima već dosadila, ali... podrazumevana konfiguracija zaista postavlja nekoliko pitanja. Kao minimum, treba obratiti pažnju na maintenance_work_mem, temp_file_limit, statement_timeout i lock_timeout.

U našem slučaju maintenance_work_mem je podrazumevano bilo 64 MB, dok je temp_file_limit nešto oko 2 GB – jednostavno nam je nedostajalo memorije za kreiranje indeksa na velikoj tabeli.

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

Drugi problem – duplicirani indeksi

Naše baze se nalaze na SSD diskovima, i koristimo HA-konfiguraciju sa više data centara, master hostom i n-tim brojem replika. Prostor na disku je veoma dragocen resurs za nas; on je jednako važan kao performanse i potrošnja CPU-a. Zato, s jedne strane, potrebni su nam indeksi za brzo čitanje, a s druge strane, ne želimo videti suvišne indekse u bazi, jer oni troše prostor i usporavaju ažuriranje podataka.

I tako, obnovivši sve nevažeće indekse i nagledavši se izvještaja Olega Bartunova, odlučio sam napraviti "veliku" čistku. Ispostavilo se da programeri ne vole čitati dokumentaciju o bazi podataka. Jako ne vole. Zbog toga nastaju dvije tipične greške – ručno stvoren indeks na primarni ključ i sličan "ručni" indeks na jedinstveni stupac. Stvar je u tome da nisu potrebni – Postgres će sve učiniti sam. Takve indekse se može slobodno ukloniti, a za to postoji dijagnostika duplicated_indexes.

Treći problem – preklapajući indeksi

Većina početnih programera stvara indekse na jedan stupac. Postupno, dok se upoznaju s tim, ljudi počinju optimizirati svoje upite i dodavati složenije indekse koji uključuju više stupaca. Tako nastaju indeksi na stupce A, A+B, A+B+C itd. Prva dva od ovih indeksa se može slobodno baciti, jer su prefiksi trećeg. To također značajno štedi prostor na disku i za to postoji dijagnostika intersected_indexes.

Četvrti problem – strani ključevi bez indeksa

Postgres omogućuje stvaranje ograničenja stranog ključa bez navođenja podržavajućeg indeksa. U mnogim situacijama to nije problem i čak se možda neće ni manifestirati... Do jednog trenutka...

Tako je bilo i kod nas: samo je u nekom trenutku posao, koji se izvršavao prema rasporedu i čistio bazu od testnih narudžbi, počeo "slagati" nam majstora host. CPU i IO su opali, upiti su usporeni i prekidani zbog vremenskog ograničenja, servis se rušio. Brza analiza pg_stat_activity je pokazala da su se usporavali upiti ovog tipa:

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

Prilikom toga, indeks po id-u u ciljoj tablici, naravno, je postojao, i zapisi su se brisali prema uvjetu sasvim malo. Činilo se da sve treba raditi, ali, nažalost, nije.

U pomoć je došao čudesni explain analyze i rekao da osim brisanja zapisa u ciljnoj tablici, još ide provjera referentne cjelovitosti, i na jednoj od povezanih tablica ta provjera pada u sequential scan zbog nedostatka odgovarajućeg indeksa. Tako je rođena dijagnostika foreign_keys_without_index.

Peti problem – null vrijednosti u indeksima

Zadano, Postgres uključuje null vrijednosti u btree-indekse, ali one su obično nepotrebne. Stoga nastojim ukloniti te null-ove (dijagnostika indexes_with_null_values), stvarajući parcijalne indekse na nullable-stupcima poput where is not null. Na ovaj način uspio sam smanjiti veličinu jednog od naših indeksa sa 1877 MB na 16 KB. A u jednoj usluzi veličina DB-a se ukupno smanjila 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 dorade. 🙂

Šesti problem – nedostatak primarnih ključeva

Zbog karakteristika mehanizma MVCC u Postgresu može doći do situacije kao bloat, kada veličina vaše tabele brzo raste zbog velikog broja mrtvih zapisa. Naivno sam mislio da nam to ne prijeti i da se to neće desiti našoj bazi, jer smo, uostalom!!!, dobri programeri... Kako sam bio glup i naivan…

Jednog lijepog dana, jedna divna migracija je ažurirala sve zapise u velikoj i aktivno korištenoj tabeli. Dobili smo +100 GB na veličini tabele niotkuda. Bilo je izuzetno frustrirajuće, ali naši problemi tu nisu završili. Nakon što je automatska vakuumacija na ovoj tabeli završena za 15 sati, postalo je jasno da fizičko mjesto neće biti vraćeno. Nismo mogli zaustaviti servis i uraditi VACUUM FULL, pa je donesena odluka da se koristi pg_repack. I tu se pokazalo da pg_repack ne može obraditi tabele bez primarnog ključa ili drugog ograničenja jedinstvenosti, a na našoj tabeli 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 bloatu tabela i indeksa i pravovremeno reagovanje na njega.

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

Sljedeće dijagnostike su tables_with_missing_indexes i unused_indexes – u svom konačnom obliku pojavili su se relativno nedavno. Stvar je u tome što ih nije bilo moguće jednostavno tako dodati.

Kao što sam već pisao, koristimo konfiguraciju sa više replika, i opterećenje pri čitanju na različitim hostovima je suštinski različito. U konačnici, dolazi do situacije da se određene tabele i indeksi na određenim hostovima praktično ne koriste, i za analizu je potrebno prikupiti statistiku sa svih hostova u klasteru. Resetovanje statistike takođe treba vršiti na svakom hostu u klasteru, ne može se to učiniti samo na masteru.

Ovaj pristup nam je omogućio da uštedimo nekoliko desetina gigabajta uklanjanjem indeksa koji nikada nisu korišćeni, kao i da dodamo nedostajuće indekse na retko korišćene tabele.

Kao zaključak

Naravno, gotovo za sve dijagnostike mogu se podesiti spisak izuzetaka. Na ovaj način se brzo mogu implementirati provere u vašoj aplikaciji, sprečavajući pojavu novih grešaka, a zatim postepeno ispraviti stare.

Neki dijagnostički testovi mogu se izvoditi već u funkcionalnim testovima odmah nakon primene migracija baze podataka. I to je verovatno jedna od najmoćnijih mogućnosti moje biblioteke. Primer korišćenja možete pogledati u demonstracija.

Provere za neiskorišćene ili nedostajuće indekse, kao i za bloat, ima smisla izvršavati samo na stvarnoj bazi podataka. Prikupljene vrednosti mogu se zabeležiti u ClickHouse ili poslati u sistem za nadzor.

Veoma se nadam da pg-index-health će biti korisna i tražena. Takođe možete doprineti razvoju biblioteke izveštavajući o otkrivenim problemima i predlažući nove dijagnostike.

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster