Iimpazamo eziqhelekileyo kwizicelo ezikhokelela kwi-bloat kwi-postgresql. UAndrey Salnikov

Ndicebisa ukuba ufunde umbhalo wengxelo ukususela ekuqaleni kuka-2016 ngu-Andrey Salnikov "Iimpazamo eziqhelekileyo kwizicelo ezikhokelela kwi-bloat kwi-postgresql"

Kule ngxelo, ndiya kuhlalutya iimpazamo eziphambili kwizicelo ezivela kwinqanaba lokuyila nokubhala ikhowudi yesicelo. Kwaye ndiza kuthatha kuphela ezo mpazamo ezikhokelela kwi-bloat kwi-Postgresql. Njengomthetho, esi sisiqalo sokuphela kokusebenza kwenkqubo yakho iyonke, nangona ekuqaleni akukho zimfuneko zale nto zibonakalayo.

Ndiyavuya ukwamkela wonke umntu! Le ngxelo ayiyobuchule njengeyokuqala kumlingane wam. Le ngxelo ijolise ikakhulu kubaphuhlisi benkqubo ye-backend kuba sinenani elikhulu labathengi. Kwaye bonke benza iimpazamo ezifanayo. Ndiza kukuxelela ngabo. Ndiza kuchaza izinto ezibulalayo nezimbi ezikhokelela kuzo ezi mpazamo.

Kutheni iimpazamo zenziwe? Zenziwa ngezizathu ezibini: ngokungaqhelekanga, mhlawumbi kuya kusebenza kwaye ngenxa yokungazi ezinye iindlela ezenzeka kwinqanaba phakathi kwedatha kunye nesicelo, kunye nakwisiseko sedatha ngokwayo.

Ndiza kukunika imizekelo emithathu enemifanekiso eyoyikekayo yendlela izinto eziye zenzeka ngayo. Ndiza kukuxelela ngokufutshane malunga nendlela eyenzekayo apho. Kwaye indlela yokujongana nazo, xa zenzeke, kwaye zeziphi iindlela zokukhusela zokusetyenziswa ukukhusela iimpazamo. Ndiza kukuxelela malunga nezixhobo ezincedisayo kunye nokubonelela ngamakhonkco aluncedo.

Ndasebenzisa i-database yovavanyo apho ndineetafile ezimbini. Ipleyiti enye eneeakhawunti zabathengi, enye inentengiselwano kwezi akhawunti. Kwaye ngamanye amaxesha sihlaziya iibhalansi kwezi akhawunti.

Idatha yokuqala yepleyiti: incinci kakhulu, 2 MB. Ixesha lokuphendula kwiziko ledatha kwaye ngokukodwa uphawu nalo lihle kakhulu. Kwaye umthwalo olungileyo ngokufanelekileyo - imisebenzi engama-2 ngomzuzwana ngokweplate.

Kwaye ngale ngxelo ndiza kukubonisa iigrafu ukuze uqonde ngokucacileyo okwenzekayo. Kuya kuhlala kukho izilayidi ezi-2 ezinegrafu. Isilayidi sokuqala yinto eyenzekayo ngokubanzi kumncedisi.

Kwaye kule meko, sibona ukuba ngokwenene sinophawu oluncinane. Isalathisi sincinci kwi-2 MB. Le yigrafu yokuqala ngasekhohlo.

Umyinge wexesha lokuphendula kumncedisi naye uzinzile kwaye ufutshane. Le yigrafu ephezulu ekunene.

Igrafu esezantsi ekhohlo ibonisa eyona ntengiselwano inde. Siyabona ukuba ukuthengiselana kugqitywe ngokukhawuleza. Kwaye i-autovacuum ayisebenzi apha okwangoku, kuba yayiluvavanyo lokuqala. Iya kuqhubeka isebenza kwaye iya kuba luncedo kuthi.

Isilayidi sesibini siya kuhlala sinikezelwe kwipleyiti evavanywayo. Kule meko, sihlala sihlaziya ibhalansi yeakhawunti yomthengi. Kwaye siyabona ukuba ixesha eliphakathi lokuphendula lomsebenzi wohlaziyo lilungile, lingaphantsi kwe-millisecond. Siyabona ukuba izixhobo zeprosesa (le yigrafu ephezulu ngasekunene) nazo ziyasetyenziswa ngokulinganayo kwaye zincinci kakhulu.

Igrafu esezantsi ekunene ibonisa ukuba kungakanani na ukusebenza kunye nememori yedisk esidlula kuyo sikhangela umgca esiwufunayo phambi kokuwuhlaziya. Kwaye inani lemisebenzi ngokophawu ngama-2 ngomzuzwana, njengoko benditshilo ekuqaleni.

Kwaye ngoku sinentlekele. Ngesizathu esithile kukho ukuthengiselana okude kulibele. Izizathu zihlala zivaliwe zonke:

• Enye yezona zinto ziqhelekileyo kukuba siqale ukufikelela kwinkonzo yangaphandle kwikhowudi yesicelo. Kwaye le nkonzo ayiphenduli kuthi. Oko kukuthi, savula ukuthengiselana, senze utshintsho kwisiseko sedatha kwaye sahamba kwisicelo sokufunda i-imeyile okanye kwenye inkonzo ngaphakathi kweziseko zethu zeziseko, kwaye ngenxa yesizathu esithile ayiphenduli kuthi. Kwaye iseshoni yethu ibambekile kwimeko apho kungaziwa ukuba iya kusonjululwa nini.
• Imeko yesibini kuxa kwenzeka okungafaniyo kwikhowudi yethu ngesizathu esithile. Kwaye ngaphandle asizange siqhubeke nokuvalwa kwentengiselwano. Kwaye sagqiba ngeseshoni yokuxhoma kunye nokuthengiselana okuvulekileyo.
• Kwaye eyokugqibela ikwayinto eqhelekileyo. Le yikhowudi yomgangatho ophantsi. Ezinye izikhokelo zivula intengiselwano. Ijinga, kwaye usenokungazi kwisicelo ukuba unayo ijinga.

Zisikhokelela phi ezo zinto?

Ukuya kuthi ga kwinqanaba lokuba iitafile kunye nezalathisi zethu ziqale ukudumba kakhulu. Le yimpembelelo ye-bloat efanayo. Kwisiseko sedatha, oku kuya kuthetha ukuba ixesha lokuphendula i-database liya kwanda kakhulu kwaye umthwalo kumncedisi wedatha uya kwanda. Yaye ngenxa yoko, isicelo sethu siya kuphazamiseka. Kuba ukuba uchithe i-10 millisecond kwikhowudi yakho kwisicelo kwisiseko sedatha, i-10 millisecond kwi-logic yakho, ngoko umsebenzi wakho uthathe i-20 millisecond ukugqiba. Kwaye ngoku imeko yakho iya kuba buhlungu kakhulu.

Kwaye makhe sibone ukuba kwenzeka ntoni. Igrafu esezantsi esekhohlo ibonisa ukuba sinentengiselwano ende ende. Kwaye ukuba sijonga igrafu ephezulu ngasekhohlo, sibona ukuba ubungakanani betafile yethu buye baxhuma ngokukhawuleza ukusuka kwi-megabytes ezimbini ukuya kwi-300 megabytes. Ngexesha elifanayo, inani ledatha kwitheyibhile alitshintshanga, oko kukuthi, kukho inani elikhulu lenkunkuma apho.

Imeko jikelele malunga nexesha eliqhelekileyo lokuphendula iseva nayo itshintshile ngemiyalelo emininzi yobukhulu. Oko kukuthi, zonke izicelo kumncedisi zaqala ukuhla ngokupheleleyo. Kwaye ngexesha elifanayo, iinkqubo zePostgres zangaphakathi zaqaliswa ngendlela ye-autovacuum, ezama ukwenza into kunye nokutya izixhobo.

Kuqhubeka ntoni ngophawu lwethu? Enjalo. Ixesha lethu lokuphendula eliphakathi ngokophawu liye laxhuma kwii-odolo ezininzi zobukhulu. Ngokukodwa ngokubhekiselele kwizixhobo ezisetyenzisiweyo, sibona ukuba umthwalo kwiprosesa unyuke kakhulu. Le yigrafu ephezulu ekunene. Kwaye iye yanda ngenxa yokuba iprosesa kufuneka ihlele ngeqela lemigca engenamsebenzi ekukhangeleni lowo ufunekayo. Le yigrafu esezantsi ekunene. Kwaye ngenxa yoko, inani lethu leefowuni ngesekhondi laqala ukuhla kakhulu, kuba i-database yayingenalo ixesha lokucubungula inani elifanayo lezicelo.

Kufuneka sibuyele ebomini. Singena kwi-intanethi kwaye sifumanisa ukuba ukuthengiselana okude kukhokelela kwiingxaki. Sifumana kwaye siyibulale le ntengiselwano. Kwaye yonke into iba yinto eqhelekileyo kuthi. Yonke into isebenza njengoko kufanelekile.

Sazola, kodwa emva kwexesha siqala ukuqaphela ukuba isicelo asisebenzi ngendlela efanayo nangaphambi konxunguphalo. Izicelo zisalungiswa kancinci, kwaye kancinci kakhulu. Isinye nesiqingatha ukuya kumaxesha amabini kancinci kancinci kumzekelo wam. Umthwalo kumncedisi uphezulu kunokuba wawukho ngaphambi kwengozi.

Kwaye umbuzo: "Kwenzeka ntoni kwisiseko ngeli xesha?" Kwaye le meko ilandelayo yenzeka ngesiseko. Kwitshathi yokuthengiselana unokubona ukuba iyekile kwaye ngokwenene akukho ntengiselwano yexesha elide. Kodwa ubungakanani bomqondiso bunyuke ngokubulala ngexesha lengozi. Kwaye ukususela ngoko abazange banciphe. Umyinge wexesha kwisiseko uzinzile. Kwaye iimpendulo zibonakala ziza ngokwaneleyo ngesantya esamkelekileyo kuthi. I-autovacuum yasebenza ngakumbi kwaye yaqala ukwenza into ngophawu, kuba idinga ukuhluza idatha eninzi.

Ngokukodwa, ngokweplate yokuvavanya kunye neeakhawunti, apho sitshintsha ibhalansi: ixesha lokuphendula isicelo libonakala libuyele ngokuqhelekileyo. Kodwa eneneni liphakame ngokuphindwe kanesiqingatha.

Kwaye ukusuka kumthwalo kwiprosesa, sibona ukuba umthwalo kwiprosesa awubuyelanga kwixabiso elifunekayo ngaphambi kokuwa. Kwaye izizathu apho zilele ngokuchanekileyo kwigrafu esezantsi ekunene. Ingabonwa ukuba inani elithile lememori likhangelwa apho. Oko kukuthi, ukufumana umgca ofunekayo, sichitha izibonelelo zomncedisi wedatha ngelixa sihlela ngedatha engenamsebenzi. Inani lentengiselwano ngesekhondi lizinzile.

Lilonke lilungile, kodwa imeko imbi ngakumbi kunokuba yayinjalo. Cacisa ukuthotywa kwesiseko sedatha ngenxa yesicelo sethu esisebenza nesi siseko sedatha.

Kwaye ukuqonda oko kwenzekayo apho, ukuba ubungekho kwingxelo yangaphambili, ngoku makhe sifumane ithiyori encinci. Ithiyori malunga nenkqubo yangaphakathi. Kutheni i-vacuum yemoto kwaye yenza ntoni?

Ngokufutshane ngokufutshane ukuze kuqondwe. Ngexesha elithile sinetafile. Sinemiqolo etafileni. Le migca ingasebenza, iphile, kwaye yintoni esiyidingayo ngoku. Ziphawulwe ngombala oluhlaza emfanekisweni. Kwaye kukho imigca efileyo esele yenziwe, yahlaziywa, kwaye amangeno amatsha avele kuyo. Kwaye ziphawulwe ukuba abasenamdla kwisiseko sedatha. Kodwa basetafileni ngenxa yenqaku lePostgres.

Kutheni ufuna ivacuum yemoto? Ngexesha elithile, i-autovacuum iza, ifikelela kwisiseko sedatha kwaye ibuze: "Nceda undinike i-id yentengiselwano endala evuliweyo ngoku kwisiseko sedatha." Uvimba weenkcukacha ubuyisela le id. Kwaye i-autovacuum, exhomekeke kuyo, ihlela ngemigca etafileni. Kwaye ukuba ubona ukuba imigca ethile yatshintshwa yintengiselwano endala kakhulu, ngoko unelungelo lokumakisha njengemigca esinokuyisebenzisa kwakhona kwixesha elizayo ngokubhala idatha entsha apho. Le yinkqubo yangasemva.

Ngeli xesha, siqhubeka nokusebenza kunye nedatha kwaye siqhubeka nokwenza utshintsho oluthile kwitafile. Kwaye kule migca, esinokuyisebenzisa kwakhona, sibhala idatha entsha. Kwaye ngaloo ndlela sifumana umjikelo, oko kukuthi lonke ixesha imigca emidala efileyo ibonakala apho, endaweni yayo sibhala imigca emitsha esiyidingayo. Kwaye le yimeko eqhelekileyo ukuba iPostgreSQL isebenze.

Kwenzeke ntoni ngengozi? Yenzeka njani le nkqubo apho?

Sasinophawu kwimeko ethile, abanye bayaphila, abanye imigca efileyo. Ivacuum yemoto ifikile. Ubuze kuvimba weenkcukacha ukuba yintoni intengiselwano yethu indala kwaye yintoni id yayo. Ndifumene le id, enokuba ziiyure ezininzi ezidlulileyo, mhlawumbi kwimizuzu elishumi eyadlulayo. Kuxhomekeke kubungakanani bomthwalo onawo kwisiseko sedatha yakho. Kwaye wahamba ekhangela imigca anokuthi ayiphawule njengesetyenziswa kwakhona. Kwaye andizange ndifumane imigca enjalo kwitafile yethu.

Kodwa ngeli xesha siqhubeka nokusebenza kunye netafile. Senza into kuyo, siyihlaziye, sitshintshe idatha. Kufuneka wenze ntoni uvimba weenkcukacha ngeli xesha? Akanakwenza nto ngaphandle kokongeza imigca emitsha ekupheleni kwetafile ekhoyo. Kwaye ke ubungakanani betafile yethu buqala ukudumba.

Enyanisweni, sifuna imigca eluhlaza ukuze sisebenze. Kodwa ngexesha lengxaki enjalo, kuvela ukuba ipesenteji yemigca eluhlaza iphantsi kakhulu kuyo yonke itafile.

Kwaye xa siqhuba umbuzo, i-database kufuneka ihambe kuyo yonke imigca: zombini ebomvu kunye eluhlaza, ukufumana umgca ofunekayo. Kwaye umphumo wokubhula itafile kunye nedatha engenamsebenzi ibizwa ngokuba yi "bloat", edla kwakhona indawo yethu yediski. Khumbula, yayiyi-2 MB, yaba yi-300 MB? Ngoku tshintsha i-megabytes kwiigigabytes kwaye uya kuphulukana ngokukhawuleza nazo zonke izixhobo zakho zediski.

Yiyiphi imiphumo enokubakho kuthi?

• Kumzekelo wam, itafile kunye nesalathisi sakhula ngamaxesha angama-150. Abanye babathengi bethu baye baba neemeko ezibulalayo ngakumbi xa beqala ukuphelelwa yindawo yediski.
• Ubungakanani beetafile ngokwayo abuyi kuncipha. I-Autovacuum kwezinye iimeko inokunqumla umsila wetafile ukuba kukho imigca efileyo kuphela. Kodwa ekubeni kukho ukujikeleza rhoqo, umgca omnye oluhlaza unokuqhwala ekupheleni kwaye ungahlaziywa, ngelixa zonke ezinye ziya kubhalwa phantsi kwindawo ethile ekuqaleni kweplate. Kodwa esi sisiganeko esingenakwenzeka ukuba itafile yakho ngokwayo iya kuncipha ngobukhulu, ngoko akufanele uthembele kuyo.
• Uvimba weenkcukacha kufuneka uhlele ngeqela elipheleleyo lemigca engenamsebenzi. Kwaye sichitha izixhobo zedisk, sichitha izixhobo zeprosesa kunye nombane.
• Kwaye oku kuchaphazela ngqo isicelo sethu, kuba ukuba ekuqaleni sichithe i-millisecond ezili-10 kwisicelo, i-millisecond ezili-10 kwikhowudi yethu, ngoko ngexesha lokuphazamiseka saqala ukuchitha okwesibini kwisicelo kunye ne-10 millisecond kwikhowudi, oko kukuthi umyalelo we ubukhulu bokusebenza kwesicelo behlile. Kwaye xa ingozi isonjululwe, saqala ukuchitha i-20 milliseconds kwisicelo, i-10 millisecond kwikhowudi. Oku kuthetha ukuba sisehle ngesihlandlo esinye esinesiqingatha kwimveliso. Kwaye konke oku kungenxa yentengiselwano enye eye yabanda, mhlawumbi ngempazamo yethu.
• Kwaye umbuzo: "Sinokwenza njani ukubuyisela yonke into?" Ukuze yonke into ihambe kakuhle kunye nathi kwaye izicelo zifike ngokukhawuleza nje ngaphambi kwengozi.

Ngenxa yale njongo kukho umjikelo othile womsebenzi owenziwayo.

Okokuqala kufuneka sifumane iitafile eziyingxaki ezidumbileyo. Siyaqonda ukuba kwezinye iitheyibhile ukurekhoda kusebenza ngakumbi, kwezinye akusebenzi kangako. Kwaye oku sisebenzisa ulwandiso pgstattuple. Ngokufakela olu lwandiso, ungabhala imibuzo eya kukunceda ufumane iitafile ezidumbile.

Nje ukuba uzifumene ezi tafile, kufuneka uzicinezele. Sele kukho izixhobo zoku. Kwinkampani yethu sisebenzisa izixhobo ezintathu. Eyokuqala yi-VACUUM eyakhelwe-ngaphakathi FULL. Ukhohlakele, ungqwabalala kwaye akananceba, kodwa ngamanye amaxesha uluncedo kakhulu. Pg_ukupakisha kwakhona и pgcompacttable - Ezi zixhobo zomntu wesithathu zokucinezela iitafile. Kwaye baphatha i-database ngononophelo ngakumbi.

Zisetyenziswa ngokuxhomekeke koko kukulungele ngakumbi. Kodwa ndiza kukuxelela malunga noku ekupheleni kanye. Into ephambili kukuba kukho izixhobo ezintathu. Kukho okuninzi onokukhetha kuzo.

Emva kokuba silungise yonke into kwaye siqinisekisa ukuba yonke into ilungile, kufuneka siyazi indlela yokuthintela le meko kwixesha elizayo:

• Inokuthintelwa ngokulula. Kufuneka ujonge ixesha leeseshoni kwi-Master server. Ngokukodwa iiseshini eziyingozi xa ungenzi nto kwimeko yentengiselwano. Aba ngabo basanda kuvula i-transaction, benza into kwaye bahamba, okanye bavele baxhoma, balahleka kwikhowudi.
• Kwaye kuwe, njengabaphuhlisi, kubalulekile ukuvavanya ikhowudi yakho xa ezi meko zivela. Akunzima ukwenza. Oku kuya kuba luncedo ukujonga. Uya kuphepha inani elikhulu leengxaki "zabantwana" ezinxulumene nokuthengiselana okude.

Kule grafu, bendifuna ukukubonisa indlela uphawu kunye nokuziphatha kwedatha etshintshileyo emva kokuba ndidlule kwisignali nge-VACUUM FULL kule meko. Oku akuyomveliso kum.

Ubungakanani betafile ngokukhawuleza bubuyele kwimeko yayo yesiqhelo yokusebenza yeemegabytes ezimbalwa. Oku akuzange kuchaphazele kakhulu ixesha lokuphendula eliphakathi kumncedisi.

Kodwa ngokukodwa kwisibonakaliso sethu sovavanyo, apho sihlaziye ibhalansi ye-akhawunti, sibona ukuba ixesha lokuphendula eliqhelekileyo kwisicelo sokuhlaziya idatha kwisibonakaliso sancitshiswa ukuya kumanqanaba angaphambili. Izixhobo ezisetyenzisiweyo yiprosesa ukugqiba esi sicelo nazo ziye zehla ukuya kumanqanaba okuphahlazeka kwangaphambili. Kwaye igrafu esezantsi ekunene ibonisa ukuba ngoku sifumana ngqo umgca esiwufunayo kwangoko, ngaphandle kokudlula kwiimfumba zemigca efileyo eyayikho ngaphambi kokuba itafile ixinzelelwe. Kwaye i-avareji yexesha lesicelo lahlala likwinqanaba elifanayo. Kodwa apha ndinempazamo kwihardware yam.

Kulapho ibali lokuqala liphela khona. Yeyona ixhaphakileyo. Kwaye kwenzeka kuwo wonke umntu, kungakhathaliseki ukuba amava omthengi kunye nokuba bafaneleke kangakanani abadwelisi. Kungekudala oku kwenzeka.

Ibali lesibini, apho sihambisa umthwalo kunye nokwandisa izixhobo zeseva

• Besele sikhulile siserious guys. Kwaye siyaqonda ukuba sine-replica kwaye kuya kuba kuhle ukuba silinganise umthwalo: bhala ku-Master, kwaye ufunde kwi-replica. Kwaye ngokuqhelekileyo le meko ivela xa sifuna ukulungiselela ezinye iingxelo okanye i-ETL. Kwaye ishishini livuya kakhulu ngale nto. Ufuna ngokwenene iindidi zeengxelo ezinohlalutyo oluninzi olunzima.
• Iingxelo zithatha iiyure ezininzi, kuba uhlalutyo oluntsonkothileyo alukwazi ukubalwa kwii-milliseconds. Thina, njengabafo abanesibindi, bhala ikhowudi. Kwisicelo sofakelo senza ukurekhoda kwi-Master, kwaye siphumeze iingxelo kwi-replica.
• Ukuhambisa umthwalo.
• Yonke into isebenza ngokugqibeleleyo. Silungile.

Yaye ikhangeleka njani le meko? Ngokukodwa kwezi grafu, ndongeze ixesha lentengiselwano ukusuka kwikopi yexesha lentengiselwano. Zonke ezinye iigrafu zibhekisa kuphela kwi-Master server.

Ngeli xesha, ibhodi yam yeengxelo yayikhulile. Zininzi zazo. Siyabona ukuba umyinge wexesha lokuphendula iseva lizinzile. Siyabona ukuba kwi-replica sine-transaction yexesha elide eliqhuba iiyure ze-2. Sibona ukusebenza okuthe cwaka kwe-autovacuum, eyenza imigca efileyo. Kwaye yonke into ihamba kakuhle nathi.

Ngokukodwa, ngokweplate evavanyiweyo, siyaqhubeka nokuhlaziya ibhalansi yeakhawunti apho. Kwaye sikwanalo nexesha lokuphendula elizinzileyo kwizicelo, ukusetyenziswa kobutyebi obuzinzileyo. Yonke into ihamba kakuhle nathi.

Yonke into ilungile de kube ngumzuzu ezi ngxelo ziqala ukubuyisela umva ngenxa yokungqubana nokuphindaphinda. Kwaye badubula kwakhona ngamaxesha aqhelekileyo.

Singena kwi-intanethi kwaye siqale ukufunda ukuba kutheni oku kusenzeka. Kwaye sifumana isisombululo.

Isisombululo sokuqala kukwandisa i-replication latency. Siyazi ukuba ingxelo yethu iqhuba iiyure ezi-3. Siseta ukulibaziseka kokuphindaphinda kwiiyure ze-3. Sisungula yonke into, kodwa sisaqhubeka sineengxaki ngeengxelo ngamanye amaxesha ziyacinywa.

Sifuna yonke into igqibelele. Sikhwela ngaphezulu. Kwaye sifumene useto olupholileyo kwi-Intanethi - hot_standby_feedback. Masiyilayite. Hot_standby_feedback ivumela ukuba sibambe i-autovacuum kwi-Master. Ngaloo ndlela, sisusa ngokupheleleyo iingxabano zokuphindaphinda. Kwaye yonke into isebenza kakuhle kuthi ngeengxelo.

Kwaye kwenzeka ntoni nge-Master server ngeli xesha? Kwaye sisengxakini iyonke nge-Master server. Ngoku sibona iigrafu xa ndinazo zombini ezi zicwangciso zenziwe zasebenza. Kwaye siyabona ukuba iseshoni kwi-replica yethu ngandlela thile yaqala ukuphembelela imeko kwi-Master server. Unesiphumo kuba uye wanqumamisa i-autovacuum, esusa imigca efileyo. Ubungakanani betafile yethu bunyuke kwakhona. I-avareji yexesha lokwenziwa kombuzo kuyo yonke i-database iphinde yanyuka. I-autovacuums yaqiniswa kancinci.

Ngokukodwa, kwipleyiti yethu, sibona ukuba uhlaziyo lwedatha kuyo nalo lwatsibela esibhakabhakeni. Ukusetyenziswa kwe-CPU kuye kwanda kakhulu. Siphinda sidlula kwinani elikhulu lemigca efileyo, engenamsebenzi. Kwaye ixesha lokuphendula lo mqondiso kunye nenani leentengiselwano liye lahla.

Izakubanjani xa singayazi le bendithetha ngayo ngaphambili?

• Siqala ukukhangela iingxaki. Ukuba siye sadibana neengxaki kwinxalenye yokuqala, siyazi ukuba oku kusenokuba ngenxa yentengiselwano ende kunye nokuya kwiNkosi. Sinengxaki kuMphathi. Iisoseji yena. Iyafudumeza, uMyinge woMyilelo wayo umalunga nekhulu.
• Izicelo apho zicotha, kodwa asiboni naluphi na utshintshiselwano oluthatha ixesha elide apho. Kwaye asiqondi ukuba yintoni ingxaki. Asiqondi ukuba sijonge phi.
• Sijonga izixhobo zeseva. Mhlawumbi ugqogqo lwethu lwantlitheka. Mhlawumbi i-memory stick yethu itshisiwe. Ewe, nantoni na inokwenzeka. Kodwa hayi, iiseva zintsha, yonke into isebenza kakuhle.
• Wonke umntu uyasebenza: abalawuli, abaphuhlisi kunye nomlawuli. Akukho nto incedayo.
• Kwaye ngexesha elithile yonke into ngokukhawuleza iqala ukuzilungisa.

Ngeli xesha, isicelo esikwi-replica yethu saqwalaselwa kwaye sahamba. Siyifumene ingxelo. Ishishini lisavuya. Njengoko ubona, uphawu lwethu lukhule kwakhona kwaye aluyi kuncipha. Kwigrafu kunye neeseshoni, ndishiye isiqwenga sale ntengiselwano ende ukusuka kwi-replica ukuze ukwazi ukuqikelela ukuba kuthatha ixesha elingakanani de imeko izinzile.

Iseshoni iphelile. Kwaye kuphela emva kwexesha elithile umncedisi uza ngaphezulu okanye ngaphantsi ukuze. Kwaye umyinge wexesha lokuphendula kwizicelo kwi-Master server ibuyela kwisiqhelo. Ngenxa yokuba, ekugqibeleni, i-autovacuum inethuba lokucoca kunye nokumakisha le migca efileyo. Kwaye waqala ukwenza umsebenzi wakhe. Kwaye ukuba uyenza ngokukhawuleza kangakanani, ngokukhawuleza siya kulungelelaniswa.

Ngokwethebhulethi evavanyiweyo, apho sihlaziya ibhalansi yeakhawunti, sibona umfanekiso ofanayo. I-avareji yexesha lohlaziyo lwe-akhawunti kwakhona ngokuthe ngcembe liqhelekile. Izibonelelo ezisetyenziswa yiprosesa nazo ziyancitshiswa. Kwaye inani lentengiselwano ngesekhondi libuyela kwesiqhelo. Kodwa kwakhona sibuyele esiqhelweni, asisafani nakuqala kwengozi.

Kwimeko nayiphi na into, sifumana i-drawdown yokusebenza, njengakwimeko yokuqala, ngesinye nesiqingatha ukuya kumaxesha amabini, kwaye ngamanye amaxesha ngaphezulu.

Kubonakala ngathi yonke into yenziwe kakuhle. Ukuhambisa umthwalo. Isixhobo asisebenzi. Sazahlula-hlula izicelo ngokweengqondo zethu, kodwa yonke into yahamba kakubi.

• Sukwenza hot_standby_feedback? Ewe, akukhuthazwa ukuba uyivule ngaphandle kwezizathu ezinamandla. Ngenxa yokuba le twist ichaphazela ngqo i-Master server kwaye inqumamisa ukusebenza kwe-autovacuum apho. Ngokuyivumela kwi-replica ethile kwaye uyilibale ngayo, ungambulala uMphathi kwaye ufumane iingxaki ezinkulu ngesicelo.
• Yandisa max_standby_streaming_delay? Ewe, kwiingxelo oku kuyinyaniso. Ukuba unengxelo yeeyure ezintathu kwaye awufuni ukuba ingqubene ngenxa yokuphinda-phindwa kongquzulwano, ngoko wandise ukulibaziseka. Ingxelo yexesha elide ayifuni idatha efikile kwisiseko sedatha ngoku. Ukuba unayo iiyure ezintathu, ngoko uyisebenzisela ixesha elithile elidala ledatha. Kwaye kuwe, nokuba kukho ukulibaziseka kweeyure ezintathu okanye ukulibaziseka kweeyure ezintandathu akuyi kwenza nawuphi na umahluko, kodwa uya kufumana iingxelo ngokuqhubekayo kwaye akuyi kuba neengxaki zokuwa kwazo.
• Ngokwendalo, kufuneka ulawule iiseshini ezinde kwii-replicas, ngakumbi ukuba uthatha isigqibo sokuvula i-hot_standby_feedback kwi-replica. Kuba nantoni na ingenzeka. Sinike umphuhlisi lo mfanekiso ukuze avavanye imibuzo. Wabhala isicelo esiphambeneyo. Wayisungula wahamba wayokusela iti, safika uMfundisi esezinzile. Okanye mhlawumbi sifaka isicelo esingalunganga apho. Iimeko ziyahluka. Iiseshoni kwiikopi mazibekwe esweni ngononophelo njengakwi-Master.
• Kwaye ukuba unemibuzo ekhawulezayo kunye nexesha elide kwii-replicas, ke kulo mzekelo kungcono ukuwahlula ukusabalalisa umthwalo. Eli likhonkco lokusasaza_ukulibaziseka. Kwabakhawulezayo, yiba ne-replica enye enolibaziseko oluncinane lokuphindaphinda. Kwizicelo zokunika ingxelo ezithatha ixesha elide, yiba ne-replica enokusilela ngeeyure ezi-6 okanye ngosuku. Le yimeko eqhelekileyo ngokupheleleyo.

Sisusa iziphumo ngendlela efanayo:

• Sifumana iitafile ezidumbileyo.
• Kwaye siyayicinezela ngesona sixhobo sifanelekileyo esisifanelayo.

Ibali lesibini liphela apha. Masiqhubele phambili kwibali lesithathu.

Kwakhona kuqhelekile kuthi apho sifuduka khona.

• Nayiphi na imveliso yesoftware iyakhula. Iimfuno zayo ziyatshintsha. Kuyo nayiphi na imeko, sifuna ukuphuhlisa. Kwaye kwenzeka ukuba kufuneka sihlaziye idatha kwitheyibhile, oko kukuthi ukuqhuba uhlaziyo ngokwemiqathango yokufuduka kwethu kumsebenzi omtsha esiwenzayo njengenxalenye yophuhliso lwethu.
• Ifomati yedatha endala ayonelisi. Masithi ngoku sibhenela kwitafile yesibini, apho ndinentengiselwano kwezi akhawunti. Kwaye masithi bakwi-ruble, kwaye sagqiba ekubeni sandise ukuchaneka kwaye sikwenze kwiikopecks. Kwaye ngenxa yoku kufuneka senze uhlaziyo: phinda-phinda intsimi ngexabiso lentengiselwano ngekhulu.
• Kwihlabathi lanamhlanje, sisebenzisa izixhobo zolawulo lwenguqulelo yedatha ezenzekelayo. Masithi Liquibase. Sibhalisa ukufudukela kwethu apho. Siyivavanya kwisiseko sethu sovavanyo. Yonke into iilungile. Uhlaziyo luyahamba. Ivimba umsebenzi okwethutyana, kodwa sifumana idatha ehlaziyiweyo. Kwaye sinokuqalisa ukusebenza okutsha kule nto. Yonke into yavavanywa kwaye yajongwa. Yonke into yaqinisekiswa.
• Senza umsebenzi ocetyiweyo saza safuduka.

Nantsi imfuduko enohlaziyo oluziswe phambi kwakho. Ekubeni ezi zintengiselwano zeakhawunti yam, ipleyiti yayiyi-15 GB. Kwaye ekubeni sihlaziya umgca ngamnye, siphinda kabini ubungakanani betafile kunye nohlaziyo, kuba siphinda sibhala umgca ngamnye.

Ngexesha lokufuduka, asikwazanga ukwenza nantoni na ngeli cwecwe, kuba zonke izicelo kuyo zifakwe emgceni kwaye zalinda de olu hlaziyo lugqitywe. Kodwa apha ndifuna ukutsalela ingqalelo yakho kumanani akwi-axis ethe nkqo. Oko kukuthi, sinexesha eliqhelekileyo lesicelo ngaphambi kokufuduka malunga ne-5 milliseconds kunye nomthwalo weprosesa, inani lemisebenzi yebhloko yokufunda imemori yediski ingaphantsi kwe-7,5.

Siye safuduka saza safumana iingxaki kwakhona.

Ukufuduka kube yimpumelelo, kodwa:

• Umsebenzi omdala ngoku uthatha ixesha elide ukugqiba.
• Itafile yakhula ngobukhulu kwakhona.
• Umthwalo kumncedisi uphinde waba mkhulu kunangaphambili.
• Kwaye, ewe, sisajongana nokusebenza okusebenze kakuhle, siyiphucule kancinci.

Kwaye oku kwakhona i-bloat, ephinda yonakalise ubomi bethu.

Apha ndibonisa ukuba itafile, njengamatyala amabini angaphambili, ayizubuyela kubungakanani bayo bangaphambili. Umndilili womthwalo womncedisi ubonakala ufanelekile.

Kwaye ukuba sijika kwitafile ngeeakhawunti, siya kubona ukuba ixesha eliphakathi lesicelo liphindwe kabini kule theyibhile. Umthwalo kwiprosesa kunye nenani lemigca ehleliweyo kwimemori yaxhuma ngaphezu kwe-7,5, kodwa yayingaphantsi. Kwaye yaxhuma amaxesha ama-2 kwimeko yeeprosesa, amaxesha angama-1,5 kwimeko yokusebenza kwebhloko, oko kukuthi, sifumene ukuthotywa kwentsebenzo yeseva. Kwaye ngenxa yoko - ukuthotywa kokusebenza kwesicelo sethu. Kwangaxeshanye, inani leefowuni lahlala limalunga kumgangatho ofanayo.

Kwaye into ephambili apha kukuqonda indlela yokwenza loo mfuduko ngokuchanekileyo. Kwaye kufuneka zenziwe. Oku kufuduka sikwenza ngokuthe rhoqo.

• Ukufuduka okukhulu okunjalo akwenzeki ngokuzenzekelayo. Kufuneka bahlale bephantsi kolawulo.
• Ukongamela ngumntu onolwazi luyafuneka. Ukuba une-DBA kwiqela lakho, vumela i-DBA ukuba iyenze. Ngumsebenzi wakhe. Ukuba akunjalo, ke vumela oyena mntu unamava ayenze, owaziyo ukusebenza neenkcukacha zolwazi.
• I-schema yedatha entsha, nokuba sihlaziya ikholamu enye, sihlala silungiselela ngokwezigaba, oko kukuthi, kwangaphambili ngaphambi kokuba inguqulelo entsha yesicelo ikhutshwe:
• Imimandla emitsha yongezwa apho siya kurekhoda idatha ehlaziyiweyo.
• Sidlulisela idatha ukusuka kwintsimi endala ukuya kwintsimi entsha kwiindawo ezincinci. Kutheni sisenza oku? Okokuqala, sihlala silawula inkqubo yale nkqubo. Siyazi ukuba sele sidlulise iibhetshi ezininzi kwaye zininzi ezishiyekileyo.
• Kwaye isiphumo sesibini esihle kukuba phakathi kwebhetshi nganye sivala ukuthengiselana, sivule entsha, kwaye oku kuvumela i-autovacuum ukuba isebenze ngokweplate, phawula imigca efileyo yokusetyenziswa kwakhona.
• Kwimigca eya kuvela ngelixa isicelo sisasebenza (sisenesicelo esidala esisebenzayo), songeza i-trigger ebhala amaxabiso amatsha kwimihlaba emitsha. Kwimeko yethu, oku kuphindaphinda ngekhulu lexabiso elidala.
• Ukuba sinenkani ngokupheleleyo kwaye sifuna umhlaba ofanayo, emva kokuba sigqibe yonke imfuduko naphambi kokukhupha uguqulelo olutsha lwesicelo, simane sithiya ngokutsha iindawo. Ezindala zinikwa amagama aqanjiweyo, kwaye amasimi amatsha athiywa kwakudala.
• Kwaye kuphela emva kokuba siqalise inguqulelo entsha yesicelo.

Kwaye kwangaxeshanye asiyi kufumana i-bloat kwaye asiyi kubandezeleka ngokwemigaqo yokusebenza.

Kulapho ibali lesithathu liphela khona.

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat_approx.sql

Kwaye ngoku iinkcukacha ezincinci malunga nezixhobo endizikhankanyileyo kwibali lokuqala.

Ngaphambi kokuba ukhangele i-bloat, kufuneka ufake ulwandiso pgstattuple.

Ukuze ungaze ubuze mibuzo, sele siyibhalile le mibuzo kumsebenzi wethu. Unokuzisebenzisa. Kukho izicelo ezimbini apha.

• Eyokuqala ithatha ixesha elide ukusebenza, kodwa iya kukubonisa amaxabiso athe ngqo ebloat ukusuka etafileni.
• Owesibini usebenza ngokukhawuleza kwaye usebenza kakhulu xa kufuneka uhlolisise ngokukhawuleza ukuba kukho i-bloat okanye kungekhona ngokwetafile. Kwaye kufuneka uqonde ukuba i-bloat ihlala ikhona kwitafile ye-Postgres. Olu luphawu lwemodeli yayo yeMVCC.
• Kwaye i-20% bloat iqhelekile kwiitafile kwiimeko ezininzi. Oko kukuthi, akufanele ukhathazeke kwaye ucinezele le tafile.

Sifumene indlela yokuchonga iitafile ezidumbileyo ngedatha engenamsebenzi.

Ngoku malunga nendlela yokulungisa i-bloat:

• Ukuba sinayo ithebhulethi encinci kunye neediski ezilungileyo, oko kukuthi, kwithebhulethi ukuya kwi-gigabyte, kunokwenzeka ukuba usebenzise i-VACUUM FULL. Uya kuthatha isitshixo esikhethekileyo kuwe etafileni imizuzwana embalwa kwaye kulungile, kodwa uya kwenza yonke into ngokukhawuleza nabuhlungu. Yenza ntoni iVACUUM FULL? Kuthatha isitshixo esikhethekileyo etafileni kwaye iphinda ibhale imiqolo ephilayo ukusuka kwiitafile ezindala ukuya kwitafile entsha. Kwaye ekugqibeleni uyawathatha endaweni yawo. Icima iifayile ezidala kwaye ibuyisela ezindala ngezitsha. Kodwa ngexesha lomsebenzi wayo, kuthatha iqhaga elikhethekileyo etafileni. Oku kuthetha ukuba aninako ukwenza nantoni na ngale thebhile: ningabhali kuyo, ningafundi kuyo, ningayiguquli. Kwaye i-VACUUM FULL idinga indawo eyongezelelweyo yedisk yokubhala idatha.
• Isixhobo esilandelayo pg_repack. Ngomgaqo wayo, ifana kakhulu ne-VACUUM FULL, kuba iphinda ibhale idatha kwiifayile ezindala ukuya kwezintsha kwaye ifake endaweni yazo kwitafile. Kodwa kwangaxeshanye, ayithathi isitshixo esikhethekileyo etafileni kwasekuqaleni komsebenzi wayo, kodwa iyithatha kuphela ngalo mzuzu xa sele isele ilungile idatha ukuze ithathe indawo yeefayile. Iimfuno zayo zomthombo wediski ziyafana nezo ze-VACUUM FULL. Ufuna indawo eyongezelelweyo kwidisk, kwaye oku ngamanye amaxesha kubalulekile ukuba uneetafile zeterabyte. Kwaye iprosesa-ilambile kakhulu kuba isebenza nge-I/O.
• Isixhobo sesithathu pgcompacttable. Ilumke ngakumbi ngezibonelelo kuba isebenza ngokwemigaqo eyahluke kancinane. Umbono ophambili we-pgcompacttable kukuba ihambisa yonke imiqolo ephilayo ukuya ekuqaleni kwetafile isebenzisa uhlaziyo etafileni. Kwaye ke ivula isikhewu kule theyibhile, kuba siyazi ukuba sinemiqolo ephilayo ekuqaleni kunye nemigca efileyo ekugqibeleni. Kwaye i-vacuum ngokwayo inqumla lo msila, okt ayifuni indawo eyongezelelweyo yedisk. Kwaye kwangaxeshanye, isenako ukucudiswa ngokwemiqathango yezibonelelo.

Yonke into enezixhobo.

Ukuba ufumana isihloko se-bloat sinomdla malunga nokuqhubela phambili ngaphakathi, nanga amanye amakhonkco aluncedo:

• https://www.slideshare.net/alexius2Mb/where-is-the-space-postgres - Le yingxelo evela kumlingane wam. Kuqhelekile malunga nokuba indawo yePostgres iya phi ngexesha lomsebenzi kunye nobomi bayo. Kwaye kukho isiqwenga sobuchwephesha esikhulu kakhulu esineenkcukacha kubaphathi bedatabase malunga ne-bloat.
• https://github.com/dataegret/pg-utils – eli likhonkco kwindawo yethu yokugcina, apho sigcina izikripthi ezininzi eziluncedo ukujonga imeko yesiseko sedatha. Apho ungafumana izikripthi zokukhangela i-bloat.
• Okwesithathu и Isine amakhonkco kwizixhobo eziya kukunceda unciphise iimpawu.
• http://blog.dataegret.com/2Mb018/03/postgresql-bloatbusters.html – esi sisithuba somlingane wam. Apho ngokunzulu nangobuchule uhlalutya i-bloat ngokweenkcukacha kwinqanaba elikufutshane nabalawuli.

Ndizame ngakumbi ukubonisa ibali eloyikisayo kubaphuhlisi, kuba bangabathengi bethu ngokuthe ngqo bogcino-lwazi kwaye kufuneka baqonde ukuba zeziphi izenzo ezikhokelela kuyo. Ndiyathemba ukuba ndiphumelele. Enkosi ngosinaka kwakho!

Imibuzo yakho

Enkosi ngengxelo! Uthethe ngendlela onokuzichonga ngayo iingxaki. Banokulunyukiswa njani? Oko kukuthi, bendinemeko apho izicelo zijingayo kungekuphela nje ngenxa yokuba bafikelele kwiinkonzo zangaphandle. Ezi yayizezinye nje zokudibanisa zasendle. Kwakukho izicelo ezincinci, ezingenabungozi ezazihlala imini yonke, zaza zaqalisa ukwenza izinto ezibhadlileyo. Oko kukuthi, ifana kakhulu nale nto uyichazayo. Indlela yokulandelela oku? Hlala kwaye ujonge rhoqo ukuba sesiphi isicelo esibambekayo? Inokuthintelwa njani oku?

Kule meko, lo ngumsebenzi wabalawuli benkampani yakho, kungekhona i-DBA.

Ndingumlawuli.

I-PostgreSQL inombono obizwa ngokuba yi-pg_stat_activity ebonisa imibuzo ejingayo. Kwaye uyabona ukuba ijinga ixesha elingakanani apho.

Ngaba kufuneka ndingene kwaye ndijonge yonke imizuzu emi-5?

Misa i-cron kwaye ukhangele. Ukuba unesicelo sexesha elide, bhala ileta kwaye yiloo nto. Oko kukuthi, awudingi ukujonga ngamehlo akho, inokuzenzekela. Uya kufumana ileta, usabela kuyo. Okanye unokudubula ngokuzenzekelayo.

Ngaba kukho naziphi na izizathu ezicacileyo ezibangela ukuba oku kwenzeke?

Ndidwelise ezinye. Eminye imizekelo entsonkothileyo. Kwaye kunokubakho incoko ixesha elide.

Enkosi ngengxelo! Bendifuna ukucacisa malunga ne pg_repack eluncedo. Ukuba akenzi isitshixo esikhethekileyo, ngoko...

Wenza isitshixo esikhethekileyo.

... emva koko ndingaphulukana nedatha. Ngaba isicelo sam akufuneki sirekhode kwanto ngeli xesha?

Hayi, isebenza kakuhle kunye netafile, okt pg_repack kuqala idlulisela yonke imigca ephilayo ekhoyo. Ngokwemvelo, uhlobo oluthile lokungena etafileni lwenzeka apho. Uphosa nje lo msila wehashe ngaphandle.

Oko kukuthi, uyenza ngokwenene ekugqibeleni?

Ekugqibeleni, uthatha iqhaga elikhethekileyo ukutshintsha ezi fayile.

Ngaba iya kukhawuleza kune-VACUUM FULL?

I-VACUUM IGCWELE, kamsinya nje yakuqalisa, ngoko nangoko yathatha isitshixo esikhethekileyo. Kwaye de enze yonke into, akayi kumyeka ahambe. Kwaye pg_repack ithatha iqhaga elikhethekileyo kuphela ngexesha lokutshintshwa kwefayile. Kulo mzuzu awuyi kubhala apho, kodwa idatha ayiyi kulahleka, yonke into iya kulunga.

Mholo! Uthethe ngokusebenza kwevacuum yemoto. Kwakukho igrafu eneeseli zokurekhoda ezibomvu, ezityheli neziluhlaza. Oko kukuthi, ezityheli - waziphawula njengezicinyiweyo. Yaye ngenxa yoko, kunokubhalwa into entsha kuzo?

Ewe. I-Postgres ayicimi imigca. Unengcaciso enjalo. Ukuba sihlaziye umgca, siphawule omdala njengocinyiweyo. I-id yentengiselwano etshintshe lo mgca ibonakala apho, kwaye sibhala umgca omtsha. Kwaye sineeseshini ezinokuthi zifundeke. Ngaxa lithile baba badala kakhulu. Kwaye undoqo wendlela i-autovacuum esebenza ngayo kukuba ihamba ngale migca kwaye iphawule njengengeyomfuneko. Kwaye ungabhala ngaphezulu idatha apho.

Ndiyaqonda. Kodwa ayisiyiyo loo nto umbuzo umalunga nayo. Khange ndigqibe. Makhe sicinge ukuba sinetafile. Inemihlaba yobukhulu obuguquguqukayo. Yaye ukuba ndizama ukufaka into entsha, isenokungangeni kwiseli endala.

Hayi, nangayiphi na imeko umgca wonke uhlaziywa apho. I-Postgres ineemodeli ezimbini zokugcina idatha. Ikhetha kuhlobo lwedatha. Kukho idatha egcinwe ngokuthe ngqo kwitheyibhile, kwaye kukho i-tos data. Ezi zizixa ezikhulu zedatha: umbhalo, json. Zigcinwe kwiipleyiti ezahlukeneyo. Kwaye ngokwala macwecwe, ibali elifanayo kunye ne-bloat lenzeka, oko kukuthi yonke into iyafana. Zidweliswe ngokwahlukeneyo.

Enkosi ngengxelo! Ngaba kwamkelekile ukusebenzisa imibuzo yengxelo yexesha lokuvala ukunciphisa ixesha?

Yamkelekile kakhulu. Sisebenzisa oku kuyo yonke indawo. Kwaye ekubeni singenazo ezethu iinkonzo, sibonelela ngenkxaso ekude, sineentlobo ngeentlobo zabathengi. Kwaye wonke umntu wanelisekile ngokupheleleyo oku. Oko kukuthi, sinemisebenzi yecron ejongayo. Ubude beeseshoni buvunyelwene nje kunye nomxhasi, ngaphambi kokuba asivumelani. Inokuba ngumzuzu, inokuba yimizuzu eyi-10. Kuxhomekeke kumthwalo kwisiseko kunye nenjongo yayo. Kodwa sonke sisebenzisa pg_stat_activity.

Enkosi ngengxelo! Ndizama ukufaka ingxelo yakho kwizicelo zam. Kwaye kubonakala ngathi siqala intengiselwano kuyo yonke indawo, kwaye siyigqibe ngokucacileyo yonke indawo. Ukuba kukho umkhethe, ngoko ke ukubuyisela umva kusenzeka. Ndaye ndaqalisa ukucinga. Ngapha koko, intengiselwano isenokungaqali ngokucacileyo. Oku mhlawumbi licebiso kwintombazana. Ukuba ndihlaziya nje irekhodi, ngaba intengiselwano iya kuqala kwi-PostgreSQL kwaye igqibe kuphela xa uxhulumaniso lunqanyuliwe?

Ukuba uthetha ngoku malunga nenqanaba lesicelo, ngoko kuxhomekeke kumqhubi osebenzisayo, kwi-ORM esetyenziswayo. Kukho iisetingi ezininzi apho. Ukuba uzibophelele ngokuzenzekela, emva koko intengiselwano iqala apho kwaye ivale kwangoko.

Oko kukuthi, ivala ngokukhawuleza emva kohlaziyo?

Kuxhomekeke kwizicwangciso. Ndibize isethingi enye. Oku kukuzibophelela ngokuzenzekela. Iqhelekile. Ukuba ivuliwe, intengiselwano ivuliwe kwaye ivaliwe. Ngaphandle kokuba uthe ngokucacileyo "qalisa intengiselwano" kunye "nokuphelisa intengiselwano", kodwa uqalise isicelo kwiseshoni.

Mholo! Enkosi ngengxelo! Masicinge ukuba sinesiseko sedatha esidumba kunye nokudumba kwaye emva koko isithuba kumncedisi siyaphela. Ngaba kukho naziphi na izixhobo zokulungisa le meko?

Isithuba kumncedisi kufuneka sibekwe iliso ngokufanelekileyo.

Umzekelo, i-DBA iye kwiti, yayikwindawo yokuphumla, njl.

Xa inkqubo yefayile yenziwe, ubuncinane uhlobo oluthile lwesithuba sogcino lwenziwe apho idatha ingabhalwanga.

Kuthekani ukuba ingaphantsi kweqanda ngokupheleleyo?

Apho kubizwa ngokuba yindawo egciniweyo, oko kukuthi, inokukhululeka kwaye kuxhomekeke kwindlela eyakhiwe ngayo, ufumana indawo ekhululekile. Ngokungagqibekanga andazi ukuba bangaphi. Kwaye kwenye imeko, hambisa iidiski ukuze ube nendawo yokwenza umsebenzi wokuvuselela. Ungayicima itafile oqinisekisiweyo ukuba awuyikuyidinga.

Ingaba zikhona ezinye izixhobo?

Ihlala yenziwe ngesandla. Kwaye ekuhlaleni kuyacaca ukuba yintoni na engcono ukuyenza apho, kuba enye idatha ibalulekile kwaye enye ayibalulekanga. Kwaye kwi-database nganye kunye nesicelo esisebenza nayo, kuxhomekeke kwishishini. Kuhlala kugqitywa ekuhlaleni.

Enkosi ngengxelo! Ndinemibuzo emibini. Okokuqala, ubonise izilayidi ezibonise ukuba xa iintengiselwano zibambekile, zombini isithuba setafile kunye nobukhulu besalathiso ziyakhula. Kwaye ngakumbi kwingxelo bekukho iqela lezinto eziluncedo ezipakisha ithebhulethi. Kuthekani ngesalathiso?

Bazipakishe nabo.

Kodwa i-vacuum ayichaphazeli isalathisi?

Abanye basebenza ngesalathiso. Umzekelo, pg_rapack, pgcompacttable. Ivacuum yenza kwakhona izalathisi kwaye iyabachaphazela. Nge-VACUUM FULL ingcamango kukubhala phezu kwayo yonke into, oko kukuthi isebenza nomntu wonke.

Kwaye umbuzo wesibini. Andiqondi ukuba kutheni iingxelo kwiikopi zixhomekeke kakhulu kuphindaphindo ngokwalo. Kwakubonakala kum ukuba iingxelo ziyafundwa, kwaye ukuphindaphindwa kubhalwa.

Yintoni ebangela impixano yophindaphindo? SineNgcali apho iinkqubo ziqhubeka khona. Sivacuum yemoto eqhubekayo. Yintoni eyenza i-autovacuum ngokwenene? Usika imigca emidala. Ukuba ngeli xesha sinesicelo kwi-replica efunda le migca emidala, kwaye kwi-Master imeko yenzeke ukuba i-autovacuum iphawule le migca ngokunokwenzeka ukuze ibhale ngaphezulu, ngoko siyibhala ngaphezulu. Kwaye sifumene ipakethi yedatha, xa kufuneka siphinde sibhale loo migca ifunwa sisicelo kwikopi, inkqubo yophindaphindo iya kulinda ixesha lokuvala olimiselweyo. Kwaye emva koko i-PostgreSQL iya kuthatha isigqibo sokuba yintoni ebaluleke ngakumbi kuyo. Kwaye ukuphindaphinda kubaluleke ngakumbi kuye kunesicelo, kwaye uya kudubula isicelo ukuze enze olu tshintsho kwi-replica.

Andrey, ndinombuzo. Ezi grafu zimangalisayo ozibonisileyo ngexesha lokubonisa, ngaba ezi ziziphumo zomsebenzi wohlobo oluthile loncedo lwakho? Zenziwa njani iigrafu?

Le yinkonzo Okmeter.

Ngaba le yimveliso yorhwebo?

Ewe. Le yimveliso yorhwebo.

umthombo: www.habr.com