Amaphutha ajwayelekile ezinhlelweni zokusebenza aholela ekukhukhumeni ku-postgresql. U-Andrey Salnikov

Ngiphakamisa ukuthi ufunde okulotshiwe kombiko kusukela ekuqaleni kuka-2016 ngu-Andrey Salnikov "Amaphutha ajwayelekile kuzinhlelo zokusebenza eziholela ekukhukhumeni ku-postgresql"

Kulo mbiko, ngizohlaziya amaphutha amakhulu kuzinhlelo zokusebenza ezivela esigabeni sokuklama nokubhala ikhodi yesicelo. Futhi ngizothatha kuphela lawo maphutha aholela ku-bloat ku-Postgresql. Njengomthetho, lesi yisiqalo sokuphela kokusebenza kwesistimu yakho iyonke, nakuba ekuqaleni zazingekho izimfuneko zalokhu ezazibonakala.

Ngiyajabula ukwamukela wonke umuntu! Lo mbiko awuwona uchwepheshe njengowokuqala ovela kuzakwethu. Lo mbiko uhloselwe kakhulu onjiniyela besistimu ye-backend ngoba sinenani elikhulu lamaklayenti. Futhi bonke benza amaphutha afanayo. Ngizokutshela ngabo. Ngizochaza ukuthi la maphutha aholela kuziphi izinto ezibulalayo nezimbi.

Kungani kwenziwa amaphutha? Zenziwa ngezizathu ezimbili: ngokungahleliwe, mhlawumbe zizosebenza futhi ngenxa yokungazi ezinye izindlela ezenzeka ezingeni eliphakathi kwe-database kanye nesicelo, kanye naku-database ngokwayo.

Ngizokunikeza izibonelo ezintathu ezinezithombe ezimbi zokuthi izinto zaba zimbi kangakanani. Ngizokutshela kafushane ngomshini okwenzeka lapho. Futhi ukuthi ungabhekana kanjani nazo, lapho zenzeka, nokuthi yiziphi izindlela zokuvimbela ongazisebenzisa ukuvimbela amaphutha. Ngizokutshela ngamathuluzi asizayo futhi nginikeze izixhumanisi eziwusizo.

Ngisebenzise isizindalwazi sokuhlola lapho nganginamatafula amabili. Ipuleti elilodwa elinama-akhawunti ekhasimende, elinye linokwenziwe kulawa ma-akhawunti. Futhi ngezinye izikhathi sibuyekeza amabhalansi kulawa ma-akhawunti.

Idatha yokuqala yepuleti: incane impela, i-2 MB. Isikhathi sokuphendula sesizindalwazi kanye ngqo nophawu sihle kakhulu. Futhi umthwalo omuhle impela - imisebenzi eyi-2 ngomzuzwana ngokuya ngepuleti.

Futhi ngalo mbiko ngizokukhombisa amagrafu ukuze uqonde kahle okwenzekayo. Kuzohlala kukhona amaslayidi angu-2 anamagrafu. Isilayidi sokuqala yilokho okwenzeka ngokujwayelekile kuseva.

Futhi kulesi simo, siyabona ukuthi sinesibonakaliso esincane ngempela. Inkomba incane ngo-2 MB. Leli igrafu yokuqala kwesokunxele.

Isikhathi sokuphendula esimaphakathi kuseva naso sizinzile futhi sifushane. Lena igrafu ephezulu kwesokudla.

Igrafu engezansi kwesokunxele ibonisa okwenziwayo okude kakhulu. Siyabona ukuthi ukuthengiselana kuqedwa ngokushesha. Futhi i-autovacuum ayisebenzi lapha okwamanje, ngoba bekuwukuhlolwa kokuqala. Izoqhubeka nokusebenza futhi izoba wusizo kithi.

Isilayidi sesibili sizohlala sinikezelwe epuletini elihlolwayo. Kulesi simo, sihlala sibuyekeza amabhalansi e-akhawunti yeklayenti. Futhi siyabona ukuthi isikhathi sokuphendula esimaphakathi somsebenzi wokubuyekeza sihle impela, singaphansi kwe-millisecond. Siyabona ukuthi izinsiza zeprosesa (lena igrafu engenhla kwesokudla) nazo zisetshenziswa ngokulinganayo futhi zincane kakhulu.

Igrafu engezansi kwesokudla ibonisa ukuthi ingakanani inkumbulo yokusebenza nediski esidlula kuyo lapho sicinga ulayini esiwufunayo ngaphambi kokuwubuyekeza. Futhi inani lemisebenzi ngokwesibonakaliso liyi-2 ngomzuzwana, njengoba ngishilo ekuqaleni.

Futhi manje sinenhlekelele. Ngesizathu esithile kukhona ukuthengiselana okukhohliwe isikhathi eside. Izizathu ngokuvamile ziyi-banal:

• Okunye okuvame kakhulu ukuthi siqale ukufinyelela isevisi yangaphandle kukhodi yesicelo. Futhi le nkonzo ayisiphenduli. Okusho ukuthi, sivule umsebenzi, senza ushintsho kusizindalwazi futhi sasuka kuhlelo lokusebenza ukuze sifunde imeyili noma kwenye isevisi ngaphakathi kwengqalasizinda yethu, futhi ngesizathu esithile ayiphenduli kithi. Futhi iseshini yethu ibambeke esimeni lapho kungaziwa ukuthi izoxazululwa nini.
• Isimo sesibili yilapho okuhlukile kwenzeka khona kukhodi yethu ngesizathu esithile. Futhi ngokuhlukile asizange sikucubungule ukuvala umsebenzi. Futhi sigcine ngeseshini yokulenga ngomsebenzi ovulekile.
• Futhi elokugcina libuye libe yicala elivamile. Lena ikhodi yekhwalithi ephansi. Ezinye izinhlaka zivula umsebenzi. Iyalenga, futhi ungase ungazi ohlelweni lokusebenza ukuthi unayo.

Ziholelaphi izinto ezinjalo?

Kuze kube yilapho amathebula nezinkomba zethu ziqala ukuvuvukala kakhulu. Lokhu kuwumphumela ofanayo wokuqunjelwa. Ku-database, lokhu kuzosho ukuthi isikhathi sokuphendula sesizindalwazi sizokhula kakhulu futhi umthwalo kuseva yedathabheyisi uzokwanda. Futhi ngenxa yalokho, isicelo sethu sizolimala. Ngoba uma usebenzise ama-millisecond angu-10 kukhodi yakho ngesicelo kusizindalwazi, ama-millisecond angu-10 kulogic yakho, umsebenzi wakho uzothatha ama-millisecond angu-20 ukuze uqedele. Futhi manje isimo sakho sizodabukisa kakhulu.

Ake sibone ukuthi kwenzekani. Igrafu engezansi kwesokunxele ibonisa ukuthi sinomsebenzi omude omude. Futhi uma sibheka igrafu engenhla kwesokunxele, sibona ukuthi ubukhulu betafula lethu buye beqa ngokuzumayo ukusuka kumamegabhayithi amabili kuya kumamegabhayithi angu-300. Ngesikhathi esifanayo, inani ledatha etafuleni alishintshile, okungukuthi kunenani elikhulu likadoti lapho.

Isimo esijwayelekile mayelana nesilinganiso sesikhathi sokuphendula seseva siphinde sashintsha ngama-oda amaningana wobukhulu. Okusho ukuthi, zonke izicelo kuseva zaqala ukwehla ngokuphelele. Futhi ngesikhathi esifanayo, izinqubo ze-Postgres zangaphakathi zaqalwa ngendlela ye-autovacuum, ezama ukwenza okuthile futhi idle izinsiza.

Kwenzakalani ngophawu lwethu? Okufanayo. Isikhathi sethu sokuphendula esimaphakathi ngokwesibonakaliso sikhuphuke ngama-oda amaningana wobukhulu. Ngokuqondile mayelana nezinsiza ezisetshenzisiwe, sibona ukuthi umthwalo kuphrosesa ukhule kakhulu. Lena igrafu ephezulu kwesokudla. Futhi inyukile ngoba iprosesa kufanele ihlunge ngenqwaba yemigqa engenamsebenzi ifuna lowo odingekayo. Leli igrafu elingezansi kwesokudla. Futhi ngenxa yalokho, inombolo yethu yezingcingo ngomzuzwana yaqala ukwehla kakhulu, ngoba i-database yayingenaso isikhathi sokucubungula inani elifanayo lezicelo.

Kudingeka sibuyele ekuphileni. Singena ku-inthanethi futhi sithole ukuthi ukuthengiselana okude kuholela ezinkingeni. Siyakuthola futhi sikubulale lokhu okwenziwayo. Futhi yonke into iba yinto evamile kithi. Konke kusebenza njengoba kufanele.

Sathula, kodwa ngemva kwesikhashana siqala ukuqaphela ukuthi isicelo asisebenzi ngendlela efanayo nangaphambi kwesimo esiphuthumayo. Izicelo zisacutshungulwa kancane, futhi zihamba kancane kakhulu. Isikhathi esisodwa nesigamu kuya kwezimbili kancane kancane esibonelweni sami. Umthwalo osesiphakelini nawo uphakeme kunalokho owawuyikho ngaphambi kwengozi.

Futhi umbuzo: "Kwenzekani esisekelweni ngalesi sikhathi?" Futhi isimo esilandelayo senzeka ngesisekelo. Eshadini lokwenziwayo ungabona ukuthi kumile futhi azikho ngempela izinkokhelo zesikhathi eside. Kodwa ubukhulu bophawu banda ngokubulalayo phakathi nengozi. Futhi kusukela lapho awazange anciphe. Isikhathi esimaphakathi kusisekelo sizinzile. Futhi izimpendulo zibonakala ziza ngokwanele ngesivinini esamukelekayo kithi. I-autovacuum yaqala ukusebenza futhi yaqala ukwenza okuthile ngophawu, ngoba idinga ukuhlunga idatha eyengeziwe.

Ngokuqondile, ngokusho kwepuleti lokuhlola elinama-akhawunti, lapho sishintsha khona ibhalansi: isikhathi sokuphendula sesicelo sibonakala sibuyele kokujwayelekile. Kodwa eqinisweni liphakeme ngokuphindwe izikhathi ezine nengxenye.

Futhi kusukela ekulayisheni kuphrosesa, sibona ukuthi umthwalo kuphrosesa awubuyelanga kunani elidingekayo ngaphambi kokuphahlazeka. Futhi izizathu lapho zilele ngokunembile kugrafu engezansi kwesokudla. Kuyabonakala ukuthi inani elithile lenkumbulo liyaseshwa lapho. Okusho ukuthi, ukuthola umugqa odingekayo, sisaphaza izinsiza zeseva yedathabhesi ngenkathi sihlunga idatha engenamsebenzi. Inani lemisebenzi ngesekhondi lizinzile.

Sekukonke kuhle, kodwa isimo sibi kakhulu kunalokho ebesiyikho. Sula ukonakaliswa kwesizindalwazi njengomphumela wohlelo lwethu lokusebenza olusebenza nale database.

Futhi ukuze uqonde ukuthi kwenzekani lapho, uma ubungekho embikweni odlule, manje ake sithole ithiyori encane. Ithiyori mayelana nenqubo yangaphakathi. Kungani i-vacuum yemoto futhi yenzani?

Ngokwezwi nezwi kafushane ukuze kuqondwe. Ngesinye isikhathi sinetafula. Sinemigqa etafuleni. Le migqa ingaba esebenzayo, iphile, futhi yini esiyidingayo manje. Amakwe ngokuluhlaza esithombeni. Futhi kukhona imigqa efile eseyenziwe kakade, yabuyekezwa, futhi okufakiwe okusha kuye kwavela kuyo. Futhi ziphawulwe ukuthi azisathakaseleki kusizindalwazi. Kepha basetafuleni ngenxa yesici se-Postgres.

Kungani udinga i-vacuum yemoto? Ngesinye isikhathi, i-autovacuum ifika, ifinyelela kusizindalwazi bese iyibuza: "Ngicela unginikeze i-id ye-transaction endala evuliwe okwamanje kusizindalwazi." Isizindalwazi sibuyisela le ID. Futhi i-autovacuum, incike kuyo, ihlunga emigqeni yetafula. Futhi uma ebona ukuthi imigqa ethile yashintshwa ukuthengiselana okudala kakhulu, khona-ke unelungelo lokuyimaka njengemigqa esingayisebenzisa futhi esikhathini esizayo ngokubhala idatha entsha lapho. Lena inqubo yangemuva.

Ngalesi sikhathi, siyaqhubeka nokusebenza nesizindalwazi futhi siyaqhubeka nokwenza izinguquko ezithile etafuleni. Futhi kule migqa, esingayisebenzisa kabusha, sibhala idatha entsha. Futhi ngaleyo ndlela sithola umjikelezo, okungukuthi ngaso sonke isikhathi imigqa emidala efile ivela lapho, esikhundleni sayo sibhala imigqa emisha esiyidingayo. Futhi lesi yisimo esijwayelekile sokuthi i-PostgreSQL isebenze.

Kwenzekani ngesikhathi sengozi? Le nqubo yenzeka kanjani lapho?

Sasinesibonakaliso esimweni esithile, abanye bayaphila, abanye imigqa efile. Ivacuum yemoto isifikile. Ubuze isizindalwazi ukuthi yini umsebenzi wethu omdala futhi iyini i-id yawo. Ngithole lomazisi, okungaba emahoreni amaningi edlule, mhlawumbe emizuzwini eyishumi edlule. Kuya ngokuthi usinda kangakanani umthwalo kusizindalwazi sakho. Wahamba efuna imigqa ayengayimaka njengesetshenziswa kabusha. Futhi angizange ngithole imigqa enjalo etafuleni lethu.

Kodwa ngalesi sikhathi siyaqhubeka nokusebenza netafula. Senza okuthile kuyo, siyibuyekeze, sishintshe idatha. Yini okufanele isizindalwazi yenze ngalesi sikhathi? Akukho angakwenza ngaphandle kokwengeza imigqa emisha ekugcineni kwetafula elikhona. Futhi ngaleyo ndlela usayizi wetafula lethu uqala ukukhukhumala.

Eqinisweni, sidinga imigqa eluhlaza ukuze sisebenze. Kodwa ngesikhathi senkinga enjalo, kuvela ukuthi iphesenti lemigqa eluhlaza liphansi kakhulu kulo lonke ithebula.

Futhi uma senza umbuzo, isizindalwazi kufanele sidlule kuyo yonke imigqa: kokubili okubomvu nokuluhlaza, ukuze kutholwe umugqa owufunayo. Futhi umphumela wokugcoba itafula ngedatha engenamsebenzi ubizwa ngokuthi "i-bloat", ebuye idle isikhala sethu sediski. Khumbula, kwakungu-2 MB, kwaba ngu-300 MB? Manje shintsha ama-megabytes abe amagigabhayithi futhi uzolahlekelwa ngokushesha zonke izinsiza zakho zediski.

Imiphi imiphumela engase ibe khona kithi?

• Esibonelweni sami, ithebula kanye nenkomba kukhule izikhathi eziyi-150. Amanye amaklayenti ethu abe nezigameko ezibulalayo kakhulu lapho evele eqala ukuphelelwa isikhala sediski.
• Usayizi wamatafula ngokwawo awusoze wehla. I-Autovacuum kwezinye izimo inganqamula umsila wetafula uma kunemigqa efile kuphela. Kodwa njengoba kukhona ukuzungezisa njalo, umugqa owodwa oluhlaza ungase ube yiqhwa ekugcineni futhi ungabuyekezwa, kuyilapho zonke ezinye zizobhalwa phansi endaweni ethile ekuqaleni kwepuleti. Kodwa lokhu kuyisenzakalo esingenakwenzeka ukuthi itafula lakho ngokwalo lizoncipha ngosayizi, ngakho akufanele uthembele.
• Isizindalwazi sidinga ukuhlunga phakathi kwenqwaba yemigqa engenamsebenzi. Futhi sisaphaza izinsiza zediski, sisaphaza izinsiza zeprosesa nogesi.
• Futhi lokhu kuthinta ngokuqondile isicelo sethu, ngoba uma ekuqaleni sichithe ama-millisecond angu-10 esicelweni, ama-millisecond angu-10 kukhodi yethu, khona-ke phakathi nokuphahlazeka saqala ukuchitha isekhondi esicelweni kanye nama-millisecond angu-10 kukhodi, okungukuthi i-oda ubukhulu ekusebenzeni kwesicelo sehlile. Futhi lapho ingozi isixazululiwe, saqala ukusebenzisa ama-millisecond angu-20 esicelweni, ama-millisecond angu-10 kukhodi. Lokhu kusho ukuthi sisehle ngokuphindwe kanye nesigamu ekukhiqizeni. Futhi konke lokhu kungenxa yokuthengiselana okukodwa okumile, mhlawumbe ngephutha lethu.
• Futhi umbuzo: "Singakwazi kanjani ukubuyisela konke?" ukuze konke kuhambe kahle nathi futhi izicelo zingene ngokushesha njengaphambi kwengozi.

Ngale njongo kunomjikelezo othile womsebenzi owenziwayo.

Okokuqala sidinga ukuthola amatafula ayinkinga aqunjelwe. Siyaqonda ukuthi kwamanye amathebula ukurekhoda kusebenza kakhulu, kwamanye akusebenzi kangako. Futhi kulokhu sisebenzisa isandiso pgstattuple. Ngokufaka lesi sandiso, ungabhala imibuzo ezokusiza ukuthi uthole amathebula akhukhumele impela.

Uma usuwatholile la mathebula, udinga ukuwacindezela. Akhona kakade amathuluzi alokhu. Enkampanini yethu sisebenzisa amathuluzi amathathu. Esokuqala yi-VACUUM eyakhelwe ngaphakathi FULL. Unonya, unokhahlo futhi akanasihawu, kodwa ngezinye izikhathi uwusizo kakhulu. Pg_pakisha kabusha и pgcompacttable - Lezi yizinsiza zezinkampani zangaphandle zokucindezela amatafula. Futhi baphatha i-database ngokucophelela.

Zisetshenziswa kuye ngokuthi yini ekulungele kakhudlwana. Kodwa ngizokutshela ngalokhu ekugcineni impela. Into eyinhloko ukuthi kukhona amathuluzi amathathu. Kuningi ongakhetha kukho.

Ngemva kokuba sesilungise yonke into futhi saqinisekisa ukuthi konke kuhamba kahle, kufanele sazi ukuthi singasivikela kanjani lesi simo esikhathini esizayo:

• Kungavinjelwa kalula. Udinga ukuqapha ubude besikhathi seseshini kuseva Eyinhloko. Izikhathi eziyingozi kakhulu uma ungenzi lutho esimweni sokuthenga. Laba yibo abasanda kuvula i-transaction, benza okuthile futhi bashiya, noma bavele balenga, balahleka kukhodi.
• Futhi kuwena, njengabathuthukisi, kubalulekile ukuhlola ikhodi yakho uma lezi zimo ziphakama. Akunzima ukukwenza. Lokhu kuzoba isheke eliwusizo. Uzogwema inombolo enkulu yezinkinga "zezingane" ezihambisana nokuthengiselana okude.

Kulawa magrafu, bengifuna ukukukhombisa ukuthi isibonakaliso nokuziphatha kwe-database kwashintsha kanjani ngemuva kokudlula uphawu nge-VACUUM FULL kuleli cala. Lokhu akukona ukukhiqizwa kimi.

Usayizi wethebula ngokushesha wabuyela esimweni sawo esivamile sokusebenza samamegabhayithi ambalwa. Lokhu akuzange kuthinte kakhulu isikhathi sokuphendula esimaphakathi seseva.

Kodwa ngokukhethekile kuphawu lwethu lokuhlola, lapho sibuyekeze khona amabhalansi e-akhawunti, sibona ukuthi isikhathi sokuphendula esimaphakathi sesicelo sokubuyekeza idatha kuphawu sehliselwe kumazinga angaphambi kwesimo esiphuthumayo. Izinsiza ezisetshenziswa iphrosesa ukuqedela lesi sicelo nazo zehlele kumazinga angaphambi kokuphahlazeka. Futhi igrafu engezansi kwesokudla ikhombisa ukuthi manje sithola ngqo umugqa esiwudingayo ngaso leso sikhathi, ngaphandle kokudabula inqwaba yemigqa efile eyayikhona ngaphambi kokuba itafula licindezelwe. Futhi isilinganiso sesikhathi sokucela sisale silingana nezinga elifanayo. Kodwa lapha nginephutha ku-hardware yami.

Yilapho indaba yokuqala iphela khona. Yiyona evame kakhulu. Futhi kwenzeka kuwo wonke umuntu, kungakhathaliseki ukuthi iklayenti linolwazi olungakanani nokuthi abahleli bezinhlelo bafaneleke kangakanani. Ngokushesha noma kamuva lokhu kwenzeka.

Indaba yesibili, lapho sisabalalisa khona umthwalo futhi sandise izinsiza zeseva

• Sesikhulile sesiba serious guys. Futhi siyaqonda ukuthi sine-replica futhi kungaba kuhle ngathi ukulinganisa umthwalo: bhalela u-Master, futhi ufunde kusukela ku-replica. Futhi ngokuvamile lesi simo siphakama lapho sifuna ukulungisa imibiko ethile noma i-ETL. Futhi ibhizinisi lijabule kakhulu ngalokhu. Ngempela ufuna imibiko ehlukahlukene enezibalo eziningi eziyinkimbinkimbi.
• Imibiko ithatha amahora amaningi, ngoba izibalo eziyinkimbinkimbi azikwazi ukubalwa ngama-millisecond. Thina, njengabafana abanesibindi, sibhala ikhodi. Kuhlelo lokusebenza lokufaka senza ukuqopha ku-Master, futhi sikhiphe imibiko kusifaniso.
• Ukusabalalisa umthwalo.
• Konke kusebenza kahle. Sibahle.

Futhi lesi simo sibukeka kanjani? Ikakhulukazi kulawa magrafu, ngiphinde ngengeza ubude besikhathi bemisebenzi obusuka kumfanekiso wesikhathi sokwenziwe. Wonke amanye amagrafu abhekisela kuphela kuseva Eyinhloko.

Ngalesi sikhathi, ibhodi lami lokubika lase likhulile. Ziningi zazo. Siyabona ukuthi isilinganiso sesikhathi sokuphendula seseva sizinzile. Siyabona ukuthi kusifaniso sinomsebenzi omude osebenza amahora angu-2. Sibona ukusebenza okuthulile kwe-autovacuum, ecubungula imigqa emisiwe. Futhi konke kuhamba kahle ngathi.

Ngokuqondile, ngokuya ngepuleti elihloliwe, siyaqhubeka nokubuyekeza amabhalansi e-akhawunti lapho. Futhi sinesikhathi sokuphendula esizinzile sezicelo, ukusetshenziswa kwezinsiza okuzinzile. Konke kuhamba kahle ngathi.

Konke kuhamba kahle kuze kube yilapho le mibiko iqala ukuvutha ngenxa yokungqubuzana nokuphindaphinda. Futhi ziphindisela emuva ngezikhathi ezithile.

Singena ku-inthanethi futhi siqale ukufunda ukuthi kungani lokhu kwenzeka. Futhi sithola isixazululo.

Isixazululo sokuqala ukukhulisa ukubambezeleka kokuphindaphinda. Siyazi ukuthi umbiko wethu usebenza amahora angu-3. Setha ukulibaziseka kokuphindaphinda kube amahora angu-3. Sethula yonke into, kodwa sisaqhubeka nokuba nezinkinga ngemibiko ekhanselwa kwesinye isikhathi.

Sifuna yonke into iphelele. Sikhuphukela phambili. Futhi sithole isilungiselelo esihle ku-inthanethi - hot_standby_feedback. Asiyivule. I-Hot_standby_feedback isivumela ukuthi sibambe i-autovacuum ku-Master. Ngakho-ke, sisusa ngokuphelele izingxabano zokuphindaphinda. Futhi konke kusisebenzela kahle ngemibiko.

Futhi kwenzekani ngeseva Eyinhloko ngalesi sikhathi? Futhi sisenkingeni ephelele nge-Master server. Manje sibona amagrafu lapho ngizinike amandla zombili lezi zilungiselelo. Futhi siyabona ukuthi iseshini esifanekisweni sethu ngandlela thize yaqala ukuba nomthelela esimweni kuseva Eyinhloko. Unawo umphumela ngoba umise isikhashana i-autovacuum, esusa imigqa emisiwe. Usayizi wetafula lethu ukhuphuke futhi. Isilinganiso sesikhathi sokwenza umbuzo kuyo yonke isizindalwazi sikhuphuke kakhulu. Ama-autovacuum aqina kancane.

Ngokuqondile, kusuka epuleti lethu, sibona ukuthi isibuyekezo sedatha kuso futhi sigxumele esibhakabhakeni. Ukusetshenziswa kwe-CPU kukhuphuke kakhulu ngendlela efanayo. Siphinda futhi sidlula inqwaba yemigqa efile, engenamsebenzi. Futhi isikhathi sokuphendula salolu phawu kanye nenani lemisebenzi yehlile.

Kuyoba njani uma singazi ukuthi bengikhuluma ngani phambilini?

• Siqala ukufuna izinkinga. Uma sihlangabezane nezinkinga engxenyeni yokuqala, siyazi ukuthi lokhu kungase kube ngenxa yokuthengiselana okude nokuya kuMphathi. Sinenkinga kuMphathi. Amasoseji yena. Iyashisa, I-Load Average yayo icishe ibe yikhulu.
• Izicelo zihamba kancane, kodwa asiboni noma yimiphi imisebenzi edonsa isikhathi eside lapho. Futhi asiqondi ukuthi yini inkinga. Asiqondi ukuthi sibheke kuphi.
• Sihlola imishini yeseva. Mhlawumbe ukuhlasela kwethu kuphahlazekile. Mhlawumbe i-memory stick yethu ishile. Yebo, kungenzeka noma yini. Kodwa cha, amaseva masha, yonke into isebenza kahle.
• Wonke umuntu uyasebenza: abalawuli, onjiniyela kanye nomqondisi. Akukho okusizayo.
• Futhi ngesinye isikhathi yonke into ngokuzumayo iqala ukuzilungisa.

Ngalesi sikhathi, isicelo esisesithombeni sethu sicutshunguliwe futhi sashiywa. Siwutholile umbiko. Ibhizinisi lisajabule. Njengoba ubona, isibonakaliso sethu sesikhulile futhi futhi ngeke sinciphe. Kugrafu enamaseshini, ngishiye ucezu lwalokhu kuthenga okude ukusuka kumfanekiso ukuze ukwazi ukulinganisa ukuthi kuthatha isikhathi esingakanani kuze kube isimo sizinza.

Iseshini isiphelile. Futhi kuphela ngemva kwesikhathi esithile iseva iza kakhulu noma ngaphansi ngohlelo. Futhi isilinganiso sesikhathi sokuphendula sezicelo kuseva Eyinhloko sibuyela kwesijwayelekile. Ngoba, ekugcineni, i-autovacuum inethuba lokuhlanza nokumaka le migqa efile. Futhi waqala ukwenza umsebenzi wakhe. Futhi ukuthi ukwenza ngokushesha kangakanani, masinyane sizofika ngohlelo.

Ngokusho kwethebhulethi ehloliwe, lapho sibuyekeza khona amabhalansi e-akhawunti, sibona isithombe esifanayo ncamashi. Isikhathi sokubuyekeza i-akhawunti esimaphakathi sibuye sibe jwayelekile kancane kancane. Izinsiza ezisetshenziswa umprosesa nazo ziyancishiswa. Futhi inani lemisebenzi ngomzuzwana libuyela kokujwayelekile. Kodwa futhi sesibuyele kwesijwayelekile, akusafani nakuqala kwengozi.

Kunoma ikuphi, sithola ukwehla kokusebenza, njengasesimeni sokuqala, ngesinye nesigamu izikhathi ezimbili, futhi kwesinye isikhathi ngaphezulu.

Kubonakala sengathi senze konke ngendlela efanele. Sabalalisa umthwalo. Impahla ayisebenzi. Sahlukanisa izicelo ngokwezingqondo zethu, kodwa nokho konke kwahamba kabi.

• Ungawaniki amandla i-hot_standby_feedback? Yebo, akunconywa ukuyivula ngaphandle kwezizathu eziqinile. Ngoba lokhu kusonteka kuthinta ngqo iseva Eyinhloko futhi kumise ukusebenza kwe-autovacuum lapho. Ngokuyivumela kwenye ikhophi futhi uyikhohlwe, ungabulala i-Master futhi uthole izinkinga ezinkulu ngohlelo lokusebenza.
• Khulisa ukulibaziseka_kokusakaza_kokulinda_okuphezulu? Yebo, ngemibiko lokhu kuyiqiniso. Uma unombiko wamahora amathathu futhi ungafuni ukuthi uphahlazeke ngenxa yezingxabano zokuphindaphinda, vele wandise ukubambezeleka. Umbiko wesikhathi eside awudingi idatha efikile kusizindalwazi njengamanje. Uma unayo amahora amathathu, uzobe usuyisebenzisela isikhathi esidala sedatha. Futhi kuwe, noma ngabe kukhona ukubambezeleka kwamahora amathathu noma ukubambezeleka kwamahora ayisithupha ngeke kwenze mehluko, kodwa uzothola imibiko njalo futhi ngeke ube nezinkinga ngokuwa kwayo.
• Ngokwemvelo, udinga ukulawula izikhathi ezinde kuzifaniso, ikakhulukazi uma unquma ukunika amandla i-hot_standby_feedback kusifaniso. Ngoba kungenzeka noma yini. Sinikeze lesi sifaniso kunjiniyela ukuze ahlole imibuzo. Wabhala isicelo esingasile. Wayethula wahamba wayophuza itiye, sathola uMfundisi omisiwe. Noma mhlawumbe sifake isicelo esingalungile lapho. Izimo ziyahlukahluka. Izikhathi zokuphindaphinda kufanele ziqashwe ngokucophelela njengaku-Master.
• Futhi uma unemibuzo esheshayo nemide kuma-replicas, khona-ke kulokhu kungcono ukuwahlukanisa ukuze usakaze umthwalo. Lesi isixhumanisi sokusakaza_ukulibaziseka. Kwabasheshayo, yiba nesifaniso esisodwa esinokulibaziseka okuncane kokuphindaphinda. Ngezicelo zokubika ezisebenza isikhathi eside, yiba nesifaniso esingase siphuze amahora angu-6 noma ngosuku. Lesi isimo esijwayelekile ngokuphelele.

Sisusa imiphumela ngendlela efanayo:

• Sithola amatafula aqumbile.
• Futhi siyiminyanisa ngethuluzi elikahle kakhulu elisifanelayo.

Indaba yesibili iphelela lapha. Ake sidlulele endabeni yesithathu.

Okunye okujwayelekile kithi lapho sifuduka khona.

• Noma yimuphi umkhiqizo wesofthiwe uyakhula. Izidingo zakhona ziyashintsha. Kunoma yikuphi, sifuna ukuthuthukisa. Futhi kwenzeka ukuthi sidinga ukubuyekeza idatha kuthebula, okungukuthi ukwenza isibuyekezo ngokwemibandela yokuthuthela kwethu emsebenzini omusha esiwethulayo njengengxenye yentuthuko yethu.
• Ifomethi yedatha endala ayigculisi. Ake sithi manje siphendukela etafuleni lesibili, lapho enginakho ukuthengiselana kulawa ma-akhawunti. Futhi ake sithi babengama-ruble, futhi sanquma ukwandisa ukunemba futhi sikwenze ngama-kopecks. Futhi kulokhu sidinga ukwenza isibuyekezo: phindaphinda inkambu ngenani lokwenziwe ngekhulu.
• Emhlabeni wanamuhla, sisebenzisa amathuluzi okulawula inguqulo yesizindalwazi esizenzakalelayo. Ake sithi I-Liquibase. Sibhalisa ukuthuthela kwethu lapho. Siyihlola esisekelweni sethu sokuhlola. Konke kuhamba kahle. Isibuyekezo siyaqhubeka. Ivimba umsebenzi isikhashana, kodwa sithola idatha ebuyekeziwe. Futhi singaqalisa ukusebenza okusha kulokhu. Konke kwahlolwa futhi kwahlolwa. Konke kwaqinisekiswa.
• Senza umsebenzi owawuhleliwe futhi sathuthela kwelinye izwe.

Nakhu ukufuduka okunesibuyekezo esethulwa phambi kwakho. Njengoba lokhu kuyimisebenzi ye-akhawunti yami, ipuleti lalingu-15 GB. Futhi njengoba sibuyekeza wonke umugqa, siphindaphinde usayizi wetafula ngokubuyekeza, ngoba sibhala kabusha wonke umugqa.

Ngesikhathi sokufuduka, asikwazanga ukwenza lutho ngalelipuleti, ngoba zonke izicelo eziya kulo zazifakwe kulayini futhi zalinda kuze kuqedwe lesi sibuyekezo. Kodwa lapha ngifuna ukudonsela ukunaka kwakho ezinombolweni ezise-eksisi eqondile. Okusho ukuthi, sinesilinganiso sesikhathi sokucela ngaphambi kokufuduka okungama-millisecond angu-5 kanye nomthwalo weprosesa, inani lemisebenzi ye-block yokufunda inkumbulo yediski ingaphansi kuka-7,5.

Sathuthela kwelinye izwe saphinde sathola izinkinga.

Ukuthutha kube yimpumelelo, kodwa:

• Ukusebenza okudala manje kuthatha isikhathi eside ukuqeda.
• Itafula lakhula ngosayizi futhi.
• Umthwalo kuseva uphinde waba mkhulu kunangaphambili.
• Futhi, kunjalo, sisacubungula ukusebenza okusebenze kahle, sikuthuthukise kancane.

Futhi lokhu kuphinda kuqunjelwe, okuphinde konakalise izimpilo zethu.

Lapha ngikhombisa ukuthi ithebula, njengamacala amabili adlule, ngeke libuyele kosayizi balo bangaphambilini. Isilinganiso sokulayisha kweseva sibonakala sanele.

Futhi uma siphendukela etafuleni ngama-akhawunti, sizobona ukuthi isilinganiso sesikhathi sokucela siphindwe kabili kuleli thebula. Umthwalo kuphrosesa kanye nenani lemigqa ehlungwe kumemori yeqa ngaphezu kuka-7,5, kodwa yayiphansi. Futhi yeqa izikhathi ezi-2 esimweni samaphrosesa, izikhathi ezingu-1,5 esimweni sokusebenza kwe-block, okungukuthi sithole ukuwohloka ekusebenzeni kweseva. Futhi ngenxa yalokho - ukucekelwa phansi kokusebenza kwesicelo sethu. Ngesikhathi esifanayo, inani lezingcingo lahlala cishe lisezingeni elifanayo.

Futhi into eyinhloko lapha ukuqonda ukuthi ungakwenza kanjani ukufuduka okunjalo ngendlela efanele. Futhi zidinga ukwenziwa. Lokhu kufuduka sikwenza ngokungaguquki.

• Ukufuduka okukhulu okunjalo akwenzeki ngokuzenzakalelayo. Kufanele bahlale belawulwa.
• Kudingeka ukugadwa ngumuntu onolwazi. Uma une-DBA eqenjini lakho, vumela i-DBA ikwenze. Umsebenzi wakhe. Uma kungenjalo, khona-ke vumela umuntu onolwazi kakhulu akwenze, owazi ukusebenza nge-database.
• I-schema yesizindalwazi esisha, noma sibuyekeza ikholomu eyodwa, sihlala silungiselela ngezigaba, okungukuthi kusengaphambili ngaphambi kokuthi kukhishwe inguqulo entsha yohlelo lokusebenza:
• Izinkambu ezintsha zengezwa lapho sizorekhoda khona idatha ebuyekeziwe.
• Sidlulisa idatha kusuka ensimini endala kuya ensimini entsha ezingxenyeni ezincane. Kungani senza lokhu? Okokuqala, sihlala silawula inqubo yale nqubo. Siyazi ukuthi sesiwadlulisele amaqoqo amaningi futhi asebaningi kakhulu asele.
• Futhi umphumela omuhle wesibili ukuthi phakathi kweqoqo ngalinye elinjalo sivala ukuthengiselana, sivula entsha, futhi lokhu kuvumela i-autovacuum ukuthi isebenze ngokuvumelana nepuleti, imaka imigqa efile ukuze isetshenziswe kabusha.
• Emigqeni ezovela ngenkathi uhlelo lokusebenza lusebenza (sisenohlelo lokusebenza oludala olusebenzayo), sengeza i-trigger ebhala amanani amasha ezinkambini ezintsha. Esimweni sethu, lokhu ukuphindaphinda ngekhulu lenani elidala.
• Uma sinenkani ngokuphelele futhi sifuna inkambu efanayo, lapho-ke sesiqedile konke ukuthutha nangaphambi kokukhipha inguqulo entsha yohlelo lokusebenza, simane siqambe kabusha izinkambu. Ezindala zinikezwa amagama athile asunguliwe, futhi amasimu amasha aqanjwe kabusha ngokuthi amadala.
• Futhi kuphela ngemva kwalokho sethula inguqulo entsha yohlelo lokusebenza.

Futhi ngesikhathi esifanayo ngeke sithole i-bloat futhi ngeke sihlupheke ngokusebenza.

Yilapho indaba yesithathu iphela 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

Futhi manje imininingwane eyengeziwe mayelana namathuluzi engiwashilo endabeni yokuqala.

Ngaphambi kokucinga i-bloat, kufanele ufake isandiso pgstattuple.

Ukuze kungadingeki uqhamuke nemibuzo, sesiyibhalile kakade le mibuzo emsebenzini wethu. Ungazisebenzisa. Kunezicelo ezimbili lapha.

• Eyokuqala ithatha isikhathi eside impela ukusebenza, kodwa izokukhombisa amanani aqondile e-bloat avela etafuleni.
• Owesibili usebenza ngokushesha futhi uphumelela kakhulu uma udinga ukuhlola ngokushesha ukuthi kukhona yini i-bloat noma cha ngokusho kwetafula. Futhi kufanele uqonde ukuthi i-bloat ihlala ikhona etafuleni le-Postgres. Lesi isici semodeli yayo ye-MVCC.
• Futhi i-20% bloat ijwayelekile kumatafula ezimweni eziningi. Okusho ukuthi, akufanele ukhathazeke futhi ucindezele leli tafula.

Sithole ukuthi singawahlonza kanjani amatafula avuvukele ngedatha engenamsebenzi.

Manje mayelana nendlela yokulungisa i-bloat:

• Uma sinethebhulethi encane namadiski amahle, okungukuthi, kuthebhulethi kuze kufike ku-gigabyte, kungenzeka ukusebenzisa i-VACUUM FULL. Uzothatha ukukhiya okukhethekile kuwe etafuleni imizuzwana embalwa futhi kulungile, kodwa uzokwenza konke ngokushesha nangokhahlo. Yenzani i-VACUUM FULL? Kudingeka ukukhiya okukhethekile etafuleni futhi ibhale kabusha imigqa ebukhoma isuka kumathebula amadala iye kuthebula elisha. Futhi ekugcineni uyabashintsha. Isusa amafayela amadala futhi ifake amadala esikhundleni sawo. Kodwa ngesikhathi somsebenzi wayo, idinga ukukhiya okukhethekile etafuleni. Lokhu kusho ukuthi awukwazi ukwenza lutho ngaleli thebula: noma ulibhalele, noma ulifunde, noma uliguqule. Futhi I-VACUUM FULL idinga isikhala esengeziwe sediski ukuze ibhale idatha.
• Ithuluzi elilandelayo pg_repack. Ngokomthetho wayo, ifana kakhulu ne-VACUUM FULL, ngoba iphinde ibhale idatha kusuka kumafayela amadala kuya kwantsha futhi iwafake esikhundleni setafula. Kodwa ngesikhathi esifanayo, ayithathi ukukhiya okukhethekile etafuleni ekuqaleni komsebenzi wayo, kodwa ithatha kuphela okwamanje lapho isivele inedatha elungile ukuze ithathe indawo yamafayela. Izidingo zayo zensiza yediski ziyefana nalezo ze-VACUUM FULL. Udinga isikhala esengeziwe sediski, futhi lokhu ngezinye izikhathi kubalulekile uma unamathebula e-terabyte. Futhi ilambile impela ngoba isebenza nge-I/O.
• Isisetshenziswa sesithathu pgcompacttable. Icophelela kakhulu ngezinsiza ngoba isebenza ngokwemigomo ethe ukuhluka kancane. Umqondo oyinhloko we-pgcompacttable ukuthi ihambisa yonke imigqa ebukhoma ekuqaleni kwetafula isebenzisa izibuyekezo etafuleni. Bese kuba nesikhala kuleli thebula, ngoba siyazi ukuthi sinemigqa ephilayo ekuqaleni kanye nemigqa efile ekugcineni. Futhi i-vacuum ngokwayo inqamula lo msila, okungukuthi ayidingi isikhala esiningi sediski. Futhi ngesikhathi esifanayo, isengakwazi ukuminyanisa mayelana nezinsiza.

Konke ngamathuluzi.

Uma uthola isihloko se-bloat sithakazelisa ngokuya ngokuqhubeka nokuqhubeka ngaphakathi, nazi izixhumanisi eziwusizo:

• https://www.slideshare.net/alexius2Mb/where-is-the-space-postgres - Lona umbiko wozakwethu. Kujwayelekile mayelana nokuthi indawo kaPostgres iya kuphi phakathi nomsebenzi nempilo yayo. Futhi kukhona ucezu lobuchwepheshe olukhulu kakhulu futhi oluningiliziwe lwabaphathi besizindalwazi mayelana ne-bloat.
• https://github.com/dataegret/pg-utils - lesi isixhumanisi senqolobane yethu, lapho sigcina khona inqwaba yemibhalo ewusizo yokuhlola isimo sesizindalwazi. Lapho ungathola imibhalo yokusesha i-bloat.
• Okwesithathu и yesine izixhumanisi zamathuluzi azokusiza ukuthi unciphise izimpawu.
• http://blog.dataegret.com/2Mb018/03/postgresql-bloatbusters.html - lokhu okuthunyelwe okuvela kumlingani wami. Lapho uhlaziya ngokujulile i-bloat ngokuningiliziwe ezingeni eliseduze nabaphathi.

Ngizame kakhudlwana ukukhombisa indaba ethusayo konjiniyela, ngoba bangamakhasimende ethu aqondile esizindalwazi futhi kufanele baqonde ukuthi yini futhi yiziphi izenzo eziholela kuyo. Ngethemba ukuthi ngiphumelele. Ngiyabonga ukulalela kwenu!

Imibuzo yakho

Siyabonga ngombiko! Ukhulume ngokuthi ungazihlonza kanjani izinkinga. Bangaxwayiswa kanjani? Okusho ukuthi, ngaba nesimo lapho izicelo zazilenga nje kuphela ngenxa yokuthi zithole izinsiza ezithile zangaphandle. Lawa kwakungamanye amajoyina angalawuleki. Kwakukhona izicelo ezincane, ezingenabungozi ezahlala usuku lonke, zase ziqala ukwenza umbhedo. Okungukuthi, kufana kakhulu nalokho okuchazayo. Ungakulandela kanjani lokhu? Hlala futhi ubuke njalo ukuthi yisiphi isicelo esibambekile? Kungavinjelwa kanjani lokhu?

Kulokhu, lona umsebenzi wabaphathi benkampani yakho, hhayi owe-DBA.

Ngingumlawuli.

I-PostgreSQL inokubuka okubizwa ngokuthi pg_stat_activity okubonisa imibuzo elengayo. Futhi uyabona ukuthi lilenga isikhathi esingakanani lapho.

Ingabe kufanele ngingene futhi ngibheke njalo emizuzwini emi-5?

Setha i-cron futhi uhlole. Uma unesicelo sesikhathi eside, bhala incwadi futhi yilokho. Okusho ukuthi, awudingi ukubheka ngamehlo akho, kungenziwa ngokuzenzakalelayo. Uzothola incwadi, usabela kuyo. Noma ungadubula ngokuzenzakalela.

Ingabe zikhona izizathu ezisobala zokuthi kungani lokhu kwenzeka?

Ngizibalile ezinye. Ezinye izibonelo eziyinkimbinkimbi. Futhi kungaba nengxoxo isikhathi eside.

Siyabonga ngombiko! Bengifuna ukucacisa mayelana nensiza ye-pg_repack. Uma engawenzi ukhiye wekhethelo, khona-ke...

Wenza ukhiye wekhethelo.

... bese ngingakwazi ukulahlekelwa idatha. Ingabe isicelo sami akufanele sirekhode lutho ngalesi sikhathi?

Cha, isebenza kahle ngetafula, okungukuthi pg_repack idlulisa kuqala yonke imigqa ebukhoma ekhona. Ngokwemvelo, uhlobo oluthile lokungena etafuleni lwenzeka lapho. Uphonsela le ponytail nje.

Okungukuthi, empeleni uyakwenza ekugcineni?

Ekugcineni, uthatha ukhiye okhethekile ukuze ashintshe lawa mafayela.

Ingabe izoshesha kune-VACUUM FULL?

I-VACUUM FULL, ngokushesha nje lapho iqala, ngokushesha yathatha ilokhi ekhethekile. Futhi kuze kube wenza konke, ngeke amdedele ahambe. Futhi pg_repack ithatha ukukhiya okukhethekile kuphela ngesikhathi sokushintshwa kwefayela. Kulo mzuzu ngeke ubhale lapho, kodwa idatha ngeke ilahleke, konke kuzohamba kahle.

Sawubona! Ukhulume ngokusebenza kwevacuum yemoto. Bekunegrafu enamaseli okuqopha abomvu, aphuzi naluhlaza. Okusho ukuthi, eziphuzi - wazimaka njengezisusiwe. Futhi ngenxa yalokho, kungase kubhalwe okuthile okusha kuzo?

Yebo. I-Postgres ayisusi imigqa. Unayo imininingwane enjalo. Uma sibuyekeza umugqa, simaka omdala njengosusiwe. Umazisi wokwenziwe oguqule lo mugqa uvela lapho, futhi sibhala umugqa omusha. Futhi sinezikhathi ezingase zikwazi ukuzifunda. Kwesinye isikhathi baguga impela. Futhi ingqikithi yokuthi i-autovacuum isebenza kanjani ukuthi idlula kule migqa futhi imaka njengengadingekile. Futhi ungabhala phezu kwedatha lapho.

Ngiyaqonda. Kodwa akusikho lokho umbuzo omayelana. Angiqedanga. Ake sicabange ukuthi sinetafula. Inezinkambu ezinosayizi oguquguqukayo. Futhi uma ngizama ukufaka into entsha, ingase ingangeni esitokisini esidala.

Cha, kunoma yikuphi ulayini wonke ubuyekezwa lapho. I-Postgres inamamodeli amabili okugcina idatha. Ikhetha ohlotsheni lwedatha. Kukhona idatha egcinwa ngqo kuthebula, futhi kukhona nedatha ye-tos. Lawa amanani amakhulu edatha: umbhalo, json. Agcinwa emapuletini ahlukene. Futhi ngokwalezi zibhebhe, indaba efanayo ne-bloat iyenzeka, okungukuthi yonke into iyafana. Zivele zibhalwe ngokwehlukana.

Siyabonga ngombiko! Ingabe kwamukelekile ukusebenzisa imibuzo yesitatimende sokuvala umkhawulo wesikhathi?

Kwamukeleka kakhulu. Sisebenzisa lokhu yonke indawo. Futhi njengoba singenazo izinsizakalo zethu, sinikeza ukwesekwa okukude, sinamakhasimende ahlukahlukene. Futhi wonke umuntu waneliseke ngokuphelele ngalokhu. Okusho ukuthi, sinemisebenzi ye-cron ehlolayo. Isikhathi seseshini simane kuvunyelwene ngaso neklayenti, ngaphambi kwalokho asivumelani. Kungaba umzuzu, kungaba imizuzu eyi-10. Kuncike emthwalweni esisekelweni kanye nenjongo yawo. Kepha sonke sisebenzisa i-pg_stat_activity.

Siyabonga ngombiko! Ngizama ukusebenzisa umbiko wakho ezicelweni zami. Futhi kubonakala sengathi siqala umsebenzi yonke indawo, futhi siqedele ngokucacile yonke indawo. Uma kukhona okuhlukile, ukubuyisela emuva kusenzeka. Ngase ngiqala ukucabanga. Phela, ukuthengiselana kungase kungaqali ngokusobala. Lokhu mhlawumbe kuyiseluleko entombazaneni. Uma ngisanda kuvuselela irekhodi, ingabe ukuthengiselana kuzoqala ku-PostgreSQL futhi kuqedelwe kuphela lapho uxhumano lunqanyuliwe?

Uma ukhuluma manje mayelana nezinga lesicelo, ngakho-ke kuya kumshayeli osebenzisayo, ku-ORM esetshenziswayo. Kunezilungiselelo eziningi lapho. Uma uzibophezele ngokuzenzakalelayo ekunikwe amandla, umsebenzi uqala lapho bese uvala ngokushesha.

Okusho ukuthi, ivala ngokushesha ngemva kokubuyekezwa?

Kuya ngezilungiselelo. Ngiqambe isilungiselelo esisodwa. Lokhu ukuzibophezela okuzenzakalelayo kuvuliwe. Kuvamile impela. Uma inikwe amandla, umsebenzi usuvuliwe futhi uvaliwe. Ngaphandle kwalapho usho ngokucacile “qala umsebenzi” futhi “phelisa okwenziwayo”, kodwa uvele wethule isicelo kuseshini.

Sawubona! Siyabonga ngombiko! Ake sicabange ukuthi sinesizindalwazi esivuvukalayo nokuvuvukala bese siphela isikhala kuseva. Ingabe akhona amathuluzi okulungisa lesi simo?

Isikhala kuseva sidinga ukugadwa kahle.

Isibonelo, i-DBA yaya etiye, yayise-resort, njll.

Uma isistimu yefayela idaliwe, okungenani uhlobo oluthile lwesikhala sokulondoloza luyakhiwa lapho idatha ingabhalwanga khona.

Kuthiwani uma ingaphansi kweziro ngokuphelele?

Lapho ibizwa ngokuthi indawo egodliwe, okungukuthi ingakhululwa futhi kuye ngokuthi inkulu kangakanani, uthola indawo yamahhala. Ngokuzenzakalelayo angazi ukuthi zingaki. Futhi kwesinye isimo, hambisa amadiski ukuze ube nendawo yokwenza umsebenzi wokwakha kabusha. Ungasusa ithebula elithile oqinisekisiwe ukuthi ngeke ulidinge.

Ingabe akhona amanye amathuluzi?

Kuhlale kwenziwa ngezandla. Futhi endaweni kuyacaca ukuthi yini engcono kakhulu ukuyenza lapho, ngoba enye idatha ibalulekile kanti enye ayibalulekile. Futhi kusizindalwazi ngasinye kanye nohlelo lokusebenza olusebenza nalo, kuya ngebhizinisi. Kuhlala kunqunywa endaweni.

Siyabonga ngombiko! Nginemibuzo emibili. Okokuqala, ubonise amaslayidi abonisa ukuthi lapho okwenziwayo kubambekile, kokubili usayizi wendawo yetafula kanye nosayizi wenkomba kuyakhula. Futhi embikweni bekukhona inqwaba yezinsiza ezipakisha ithebhulethi. Kuthiwani ngenkomba?

Nabo bayawapakisha.

Kodwa i-vacuum ayithinti inkomba?

Abanye basebenza ngenkomba. Isibonelo, pg_rapack, pgcompacttable. I-vacuum idala kabusha izinkomba futhi iyawathinta. Nge-VACUUM FULL umbono uwukuba ubhale phezu kwayo yonke into, okungukuthi isebenza nawo wonke umuntu.

Nombuzo wesibili. Angiqondi ukuthi kungani imibiko ngezifaniso incike kakhulu ekuphindaphindweni ngokwayo. Kimina kwabonakala sengathi imibiko iyafundwa, nokuphindaphinda kuyabhalwa.

Yini ebangela ukungqubuzana kokuphindaphinda? SineNgcweti lapho izinqubo zenzeka khona. Sine-vacuum yemoto eqhubekayo. Yenzani ngempela i-autovacuum? Usika imigqa emidala. Uma ngalesi sikhathi sinesicelo ku-replica efunda le migqa emidala, futhi ku-Master kwenzeka isimo sokuthi i-autovacuum imake le migqa ngangokunokwenzeka ukuze ibhalwe ngaphezulu, bese siyibhala phezu kwayo. Futhi sithole iphakethe ledatha, uma sidinga ukubhala kabusha leyo migqa edingwa isicelo esifanekisweni, inqubo yokuphindaphinda izolinda isikhathi sokuvala osimisile. Bese i-PostgreSQL izonquma ukuthi yini ebaluleke kakhulu kuyo. Futhi ukuphindaphinda kubaluleke kakhulu kuye kunesicelo, futhi uzodubula isicelo ukuze enze lezi zinguquko kumfanekiso.

Andrey, nginombuzo. Lawa magrafu amahle owabonisa ngesikhathi sokwethula, ingabe lawa angumphumela womsebenzi wohlobo oluthile lwensiza yakho? Enziwa kanjani amagrafu?

Lena isevisi I-Okmeter.

Ingabe lona umkhiqizo wezohwebo?

Yebo. Lona umkhiqizo wezohwebo.

Source: www.habr.com