Saluton.
Mi nomiĝas Vanya kaj mi estas Java programisto. Okazas, ke mi multe laboras kun PostgreSQL - agordante la datumbazon, optimumigante la strukturon, rendimenton, kaj ludante iomete DBA dum la semajnfinoj.
Lastatempe mi ordigis plurajn datumbazojn en niaj mikroservoj kaj verkis java bibliotekon , kiu faciligas ĉi tiun laboron, ŝparas al mi tempon kaj helpas min eviti kelkajn oftajn erarojn faritajn de programistoj. Estas ĉi tiu biblioteko pri kiu ni parolos hodiaŭ.
malgarantio
La ĉefa versio de PostgreSQL, kun kiu mi laboras, estas 10. Ĉiuj SQL-demandoj, kiujn mi uzas, ankaŭ estas testitaj en versio 11. La minimuma subtenata versio estas 9.6.
antaŭhistorio
Ĉio komenciĝis antaŭ preskaŭ unu jaro kun situacio, kiu estis al mi stranga: la konkurenciva kreado de indekso ekstere finiĝis per eraro. La indekso mem, kiel kutime, restis en la datumbazo en nevalida stato. Loga analizo montris mankon . Kaj ni iras... Pli profunde, mi malkovris multajn problemojn en la datumbaza agordo kaj, kunvolvinte miajn manikojn, komencis ripari ilin kun ekbrilo en miaj okuloj.
Problemo unu - defaŭlta agordo
Verŝajne ĉiuj estas sufiĉe lacaj de la metaforo pri Postgres, kiu povas ruliĝi per kafmaŝino, sed... la defaŭlta agordo vere levas kelkajn demandojn. Minimume, indas atenti bontenado_laboro_mem, temp_dosiero_limo, statement_timeout и lock_timeout.
En nia kazo bontenado_laboro_mem estis la defaŭlta 64 MB, kaj temp_dosiero_limo io ĉirkaŭ 2 GB - ni simple ne havis sufiĉe da memoro por krei indekson sur granda tablo.
Tial, en pg-index-sano Mi kolektis serion , laŭ mi, la parametroj kiuj devus esti agordita por ĉiu datumbazo.
Problemo du - duplikataj indeksoj
Niaj datumbazoj vivas sur SSD-diskoj, kaj ni uzas HA-agordo kun multoblaj datumcentroj, majstra gastiganto kaj n-nombro da kopioj. Diskospaco estas tre valora rimedo por ni; ĝi ne estas malpli grava ol rendimento kaj CPU-konsumo. Tial, unuflanke, ni bezonas indeksojn por rapida legado, kaj aliflanke, ni ne volas vidi nenecesajn indeksojn en la datumbazo, ĉar ili manĝas spacon kaj malrapidigas datuman ĝisdatigon.
Kaj nun, restariginte ĉion kaj vidinte sufiĉe , mi decidis organizi "grandan" purigon. Montriĝis, ke programistoj ne ŝatas legi datumbazan dokumentadon. Ili ne tre ŝatas ĝin. Pro tio, aperas du tipaj eraroj - permane kreita indekso sur ĉefa ŝlosilo kaj simila "manlibro" indekso sur unika kolumno. La fakto estas, ke ili ne estas bezonataj - Postgres faros ĉion mem. Tiaj indeksoj povas esti sekure forigitaj, kaj diagnozoj aperis tiucele .
Problemo tri - intersekcantaj indeksoj
Plej multaj komencaj programistoj kreas indeksojn sur ununura kolumno. Iom post iom, ĝisfunde spertante ĉi tiun komercon, homoj komencas optimumigi siajn demandojn kaj aldoni pli kompleksajn indeksojn, kiuj inkluzivas plurajn kolumnojn. Tiel aperas indeksoj sur kolumnoj A, A + B, A + B + C kaj tiel plu. La unuaj du el ĉi tiuj indeksoj povas esti sekure forĵetitaj, ĉar ili estas prefiksoj de la tria. Ĉi tio ankaŭ ŝparas multe da diskospaco kaj ekzistas diagnozoj por tio .
Problemo kvar - fremdaj ŝlosiloj sen indeksoj
Postgres permesas krei fremdajn ŝlosillimojn sen specifi subtenindekson. En multaj situacioj tio ne estas problemo, kaj eble eĉ ne manifestiĝas... Por la momento...
Estis same ĉe ni: estas nur, ke iam laboro, funkcianta laŭ horaro kaj purigante la datumbazon de testaj mendoj, komencis esti "aldonita" al ni de la majstra gastiganto. CPU kaj IO malŝparis, petoj malrapidiĝis kaj estis tempigitaj, la servo estis kvincent. Rapida analizo montris, ke demandoj kiel:
delete from <table> where id in (…)En ĉi tiu kazo, kompreneble, estis indekso per id en la celtabelo, kaj tre malmultaj registroj estis forigitaj laŭ la kondiĉo. Ŝajnis, ke ĉio devus funkcii, sed, ve, ne okazis.
La mirinda venis al la savo klarigi analizi kaj diris, ke krom forigi rekordojn en la celtabelo, ekzistas ankaŭ referenca integreco-kontrolo, kaj sur unu el la rilataj tabeloj ĉi tiu kontrolo malsukcesas. sinsekva skanado pro la manko de taŭga indekso. Tiel naskiĝis diagnozo .
Problemo kvin - nula valoro en indeksoj
Defaŭlte, Postgres inkluzivas nulajn valorojn en btree-indeksoj, sed ili kutime ne bezonas tie. Tial mi diligente provas forĵeti ĉi tiujn nulojn (diagnozo ), kreante partajn indeksojn sur nuleblaj kolumnoj laŭ tipo where <A> is not null. Tiamaniere mi povis redukti la grandecon de unu el niaj indeksoj de 1877 MB al 16 KB. Kaj en unu el la servoj, la grandeco de la datumbazo malpliiĝis entute je 16% (per 4.3 GB en absolutaj nombroj) pro la ekskludo de nulaj valoroj de la indeksoj. Grandega ŝparado en diskospaco kun tre simplaj modifoj. 🙂
Problemo ses - manko de primaraj ŝlosiloj
Pro la naturo de la mekanismo tia situacio eblas kiam la grandeco de via tablo rapide kreskas pro granda nombro da mortaj rekordoj. Mi naive kredis, ke tio ne minacos nin, kaj ke tio ne okazos al nia bazo, ĉar, ve!!!, ni estas normalaj programistoj... Kiel stulta kaj naiva mi estis...
Iun tagon, unu mirinda migrado prenis kaj ĝisdatigis ĉiujn rekordojn en granda kaj aktive uzata tabelo. Ni akiris + 100 GB al la tablograndeco de la bluo. Estis damne domaĝe, sed niaj misaventuroj ne finiĝis tie. Post kiam la aŭtomata vakuo sur ĉi tiu tablo finiĝis 15 horojn poste, evidentiĝis, ke la fizika loko ne revenos. Ni ne povis haltigi la servon kaj fari VACUUM FULL, do ni decidis uzi . Kaj tiam montriĝis ke pg_repack ne scias kiel prilabori tabelojn sen ĉefa ŝlosilo aŭ alia unikeclimo, kaj nia tabelo ne havis ĉefan ŝlosilon. Tiel naskiĝis diagnozo .
En la biblioteka versio 0.1.5 La kapablo kolekti datumojn de ŝvelaĵo de tabeloj kaj indeksoj kaj respondi al ĝi ĝustatempe estis aldonita.
Problemoj sep kaj ok - nesufiĉaj indeksoj kaj neuzataj indeksoj
La sekvaj du diagnozoj estas: и – aperis en ilia fina formo relative lastatempe. La punkto estas, ke ili ne povus esti nur prenitaj kaj aldonitaj.
Kiel mi jam skribis, ni uzas agordon kun pluraj kopioj, kaj la legado de malsamaj gastigantoj estas fundamente malsama. Kiel rezulto, la situacio rezultas, ke iuj tabeloj kaj indeksoj sur iuj gastigantoj praktike ne estas uzataj, kaj por analizo vi devas kolekti statistikojn de ĉiuj gastigantoj en la areto. Ĉi tio ankaŭ estas necesa ĉe ĉiu gastiganto en la areto; vi ne povas fari tion nur ĉe la majstro.
Ĉi tiu aliro permesis al ni ŝpari plurajn dekojn da gigabajtoj forigante indeksojn kiuj neniam estis uzitaj, same kiel aldonante mankantajn indeksojn al malofte uzitaj tabeloj.
Kiel konkludo
Kompreneble, por preskaŭ ĉiuj diagnozoj vi povas agordi . Tiel, vi povas rapide efektivigi kontrolojn en via aplikaĵo, malhelpante novajn erarojn aperi, kaj poste iom post iom ripari malnovajn.
Iuj diagnozoj povas esti faritaj en funkciaj testoj tuj post lanĉado de datumbazaj migradoj. Kaj ĉi tio eble estas unu el la plej potencaj trajtoj de mia biblioteko. Ekzemplo de uzo troveblas en .
Estas senco fari kontrolojn por neuzataj aŭ mankantaj indeksoj, same kiel por ŝvelaĵo, nur sur reala datumbazo. La kolektitaj valoroj povas esti registritaj en aŭ sendita al la monitora sistemo.
Mi vere esperas tion pg-index-sano estos utila kaj postulata. Vi ankaŭ povas kontribui al la disvolviĝo de la biblioteko raportante problemojn, kiujn vi trovas kaj sugestante novajn diagnozojn.
fonto: www.habr.com