Hello.
Mi chjamu Vanya è sò un sviluppatore Java. Hè cusì chì u travagliu assai cù PostgreSQL - cunfigurà a basa di dati, ottimizendu a struttura, u rendiment, è ghjucà un pocu DBA in u weekend.
Recentemente aghju urdinatu parechje basa di dati in i nostri microservizi è hà scrittu una biblioteca java , chì face stu travagliu più faciule, mi risparmia u tempu è m'aiuti à evità qualchì sbagliu cumuni fattu da i sviluppatori. Ghjè di sta biblioteca chì avemu da parlà oghje.
Légales
A versione principale di PostgreSQL cù quale travagliu hè 10. Tutte e dumande SQL chì aghju utilizatu sò ancu pruvati nantu à a versione 11. A versione minima supportata hè 9.6.
Pristoria
Tuttu hà cuminciatu guasi un annu fà cù una situazione chì era strana per mè: a creazione competitiva di un indice fora di u turchinu finita cù un errore. L'indici stessu, cum'è di solitu, ferma in a basa di dati in un statu invalidu. L'analisi di log dimustrava una carenza . È andemu ... Scava più profonda, aghju scupertu una mansa di prublemi in a cunfigurazione di a basa di dati è, arrontendu e maniche, cuminciò à riparà cù una scintilla in i mo ochji.
Prublemu unu - cunfigurazione predeterminata
Probabilmente tutti sò abbastanza stanchi di a metafora di Postgres, chì pò esse eseguita nantu à una cafetera, ma ... a cunfigurazione predeterminata susciteghja veramente una quantità di dumande. À u minimu, vale a pena attenti mantenimentu_travagliu_mem, temp_file_limit, statement_timeout и lock_timeout.
In u nostru casu mantenimentu_travagliu_mem era u default 64 MB, è temp_file_limit qualcosa di circa 2 GB - simpricimenti ùn avemu micca abbastanza memoria per creà un indice nantu à una grande tavola.
Dunque, in pg-index-health Aghju cullatu una seria , in my opinion, i paràmetri chì deve esse cunfigurati per ogni basa di dati.
Prublemu dui - indici duplicati
A nostra basa di dati vive nantu à unità SSD, è avemu aduprà HA-configurazione cù centri di dati multipli, host maestru è n- u numeru di repliche. U spaziu di discu hè una risorsa assai preziosa per noi; ùn hè micca menu impurtante di u rendiment è u cunsumu di CPU. Dunque, da una banda, avemu bisognu d'indici per a lettura veloce, è da l'altra banda, ùn vulemu micca vede indici innecessarii in a basa di dati, postu chì manghjanu spaziu è rallenta l'aghjurnamentu di dati.
È avà, avè restauratu tuttu è avè vistu abbastanza , Aghju decisu di urganizà una purga "grande". Hè risultatu chì i sviluppatori ùn piace micca leghje a documentazione di basa di dati. Ùn li piace micca assai. Per via di questu, sò dui errori tipici - un indexu creatu manualmente nantu à una chjave primaria è un indice "manuale" simili nantu à una colonna unica. U fattu hè chì ùn sò micca necessariu - Postgres farà tuttu. Tali indici ponu esse eliminati in modu sicuru, è i diagnostichi sò apparsu per questu scopu .
Prublemu trè - indici intersecting
A maiò parte di i sviluppatori novi creanu indici nantu à una sola colonna. A pocu à pocu, dopu avè tastatu bè stu affari, a ghjente cumincia à ottimisà e so dumande è aghjunghje indici più cumplessi chì includenu parechje colonne. Questu hè cumu appare l'indici nantu à e colonne A, A + B, A + B + C eccetera. I primi dui di sti indici ponu esse cacciati in modu sicuru, postu chì sò prefissi di u terzu. Questu salva ancu assai spaziu di discu è ci sò diagnostichi per questu .
Prublemu quattru - chjave straneri senza indici
Postgres permette di creà restrizioni di chjave straneri senza specificà un indice di sustegnu. In parechje situazione ùn hè micca un prublema, è ùn pò ancu esse manifestatu ... Per u mumentu ...
Era listessa cun noi: hè solu chì in un certu puntu in u tempu un travagliu, currendu secondu un calendariu è sguassate a basa di dati di ordini di teste, hà cuminciatu à esse "aghjuntu" à noi da u maestru host. U CPU è l'IO andonu à perdi, e richieste rallentate è sò state timed out, u serviziu era cinque centu. Analisi rapida hà dimustratu chì e dumande cum'è:
delete from <table> where id in (…)In questu casu, sicuru, ci era un indexu per id in a tavola di destinazione, è assai pochi registri sò stati sguassati secondu a cundizione. Paria chì tuttu duverebbe travaglià, ma, alas, ùn hè micca.
U maravigliu hè vinutu in salvezza spiegà analizà è hà dettu chì in più di sguassà i registri in a tavola di destinazione, ci hè ancu un cuntrollu di integrità referenziale, è nantu à una di e tavule cunnesse stu cuntrollu falla. scan sequential a causa di a mancanza di un indice adattatu. Cusì hè natu u diagnosticu .
Prublemu cinque - valore nulu in l'indici
Per automaticamente, Postgres include valori nulli in l'indici btree, ma ùn sò generalmente micca necessariu quì. Dunque, diligentemente pruvate à scaccià questi nulli (diagnostica ), creendu indici parziali nantu à colonne nullable per tipu where <A> is not null. In questu modu, aghju pussutu riduce a dimensione di unu di i nostri indici da 1877 MB à 16 KB. È in unu di i servizii, a dimensione di a basa di dati diminuite in totale da 16% (da 4.3 GB in numeri assoluti) per via di l'esclusione di valori nulli da l'indici. Enorme risparmiu in u spaziu di discu cù mudificazioni assai simplici. 🙂
Prublemu sei - mancanza di chjave primaria
A causa di a natura di u mecanismu una situazione cusì hè pussibule quandu a dimensione di u vostru tavulinu cresce rapidamente per via di un gran numaru di registri morti. Aghju cridutu ingenuamente chì questu ùn ci minacciava micca, è chì questu ùn saria micca accadutu à a nostra basa, perchè noi, wow !!!, simu sviluppatori normali... Quantu stupidu è ingenu era...
Un ghjornu, una migrazione maravigliosa hà pigliatu è aghjurnatu tutti i registri in una tavula grande è attivamente utilizata. Avemu avutu + 100 GB à a dimensione di a tavola fora di u turchinu. Era una vergogna, ma e nostre disavventure ùn sò micca finite quì. Dopu chì l'autovacuum nantu à sta tavula finisci 15 ore dopu, hè diventatu chjaru chì u locu fisicu ùn vulterà micca. Ùn pudemu micca piantà u serviziu è fà VACUUM FULL, cusì avemu decisu di utilizà . E poi si n'andò pg_repack ùn sà micca cumu processà e tavule senza una chjave primaria o altra limitazione di unicità, è a nostra tavula ùn hà micca una chjave primaria. Cusì hè natu u diagnosticu .
In a versione di a biblioteca 0.1.5 Hè stata aghjunta a capacità di cullà e dati da bloat of tables è indici è risponde à questu in una manera puntuale.
Prublemi sette è ottu - indici insufficienti è indici inutilizati
I seguenti dui diagnostichi sò: и - apparsu in a so forma finale relativamente pocu tempu. U puntu hè chì ùn puderanu micca solu esse pigliatu è aghjuntu.
Cum'è l'aghju digià scrittu, usemu una cunfigurazione cù parechje rèpliche, è a carica di lettura nantu à diversi ospiti hè fundamentalmente diversa. In u risultatu, a situazione risulta chì certi tavulini è indici nantu à certi òspiti sò praticamenti micca usati, è per l'analisi avete bisognu di cullà statistiche da tutti l'ospiti in u cluster. Questu hè ancu necessariu in ogni host in u cluster; ùn pudete micca fà questu solu nantu à u maestru.
Stu approcciu ci hà permessu di salvà parechji decine di gigabyte sguassate l'indici chì ùn sò mai stati utilizati, è aghjunghje l'indici mancanti à e tavule raramente usate.
Cum'è cunclusione
Di sicuru, per quasi tutti i diagnostichi pudete cunfigurà . In questu modu, pudete implementà rapidamente cuntrolli in a vostra applicazione, impediscendu l'apparizione di novi errori, è poi riparà gradualmente i vechji.
Certi diagnostichi ponu esse realizati in testi funziunali immediatamenti dopu l'implementazione di migrazioni di basa di dati. È questu hè forse una di e funzioni più putenti di a mo biblioteca. Un esempiu di usu pò esse trovu in .
Hè sensu per eseguisce cuntrolli per indici inutilizati o mancanti, è ancu per bloat, solu nantu à una basa di dati vera. I valori raccolti ponu esse registrati in o mandatu à u sistema di surviglianza.
Spergu veramente chì pg-index-health serà utile è in dumanda. Pudete ancu cuntribuisce à u sviluppu di a biblioteca, rappurtandu i prublemi chì truvate è suggerenu novi diagnostichi.
Source: www.habr.com