Zolakwika zodziwika bwino pamapulogalamu omwe amatsogolera ku bloat mu postgresql. Andrey Salnikov

Ndikupangira kuti muwerenge zolembedwa za lipoti kuyambira koyambirira kwa 2016 ndi Andrey Salnikov "Zolakwika zenizeni pamapulogalamu omwe amatsogolera kuphulika mu postgresql"

Mu lipotili, ndisanthula zolakwika zazikulu pamapulogalamu omwe amabwera pakupanga ndi kulemba ma code ogwiritsira ntchito. Ndipo nditenga zolakwika zokhazo zomwe zimatsogolera ku bloat ku Postgresql. Monga lamulo, ichi ndi chiyambi cha mapeto a machitidwe a dongosolo lanu lonse, ngakhale poyamba palibe zofunikira pa izi zomwe zinkawoneka.

Wokondwa kulandira aliyense! Lipotili silili laukadaulo ngati lapitalo kuchokera kwa mnzanga. Lipotili makamaka limayang'ana opanga ma backend system chifukwa tili ndi makasitomala ambiri. Ndipo onse amalakwitsa zinthu zofanana. Ndikuuzani za iwo. Ndifotokoza zinthu zowopsa komanso zoyipa zomwe zolakwitsa izi zimatsogolera.

Chifukwa chiyani zolakwa zimachitika? Amachitidwa pazifukwa ziwiri: mwachisawawa, mwina zitheka ndipo chifukwa chosadziwa njira zina zomwe zimachitika pamlingo pakati pa database ndi kugwiritsa ntchito, komanso mu database yokha.

Ndikupatsani zitsanzo zitatu zokhala ndi zithunzi zoyipa za momwe zinthu zidalili. Ndikuuzani mwachidule za makina omwe amachitika pamenepo. Ndi momwe mungathanirane nawo, zidachitika liti, ndi njira zodzitetezera zomwe mungagwiritse ntchito kupewa zolakwika. Ndidzakuuzani za zida zothandizira ndikupereka maulalo othandiza.

Ndinagwiritsa ntchito malo osungira mayeso komwe ndinali ndi matebulo awiri. Mbale imodzi yokhala ndi maakaunti amakasitomala, ina yokhala ndi zochitika pamaakaunti awa. Ndipo pafupipafupi timasintha masikelo amaakaunti awa.

Deta yoyamba ya mbale: ndi yaying'ono, 2 MB. Nthawi yoyankha pankhokwe komanso makamaka chizindikiro ndi yabwino kwambiri. Ndipo katundu wabwino kwambiri - 2 ntchito pa sekondi imodzi malinga ndi mbale.

Ndipo kudzera mu lipotili ndikuwonetsani ma graph kuti mumvetsetse bwino zomwe zikuchitika. Padzakhala nthawi zonse 2 masilaidi okhala ndi ma graph. slide yoyamba ndi zomwe zimachitika kawirikawiri pa seva.

Ndipo muzochitika izi, tikuwona kuti tili ndi chizindikiro chaching'ono. Mlozerawu ndi wocheperako ku 2 MB. Ili ndi graph yoyamba kumanzere.

Nthawi yapakati yoyankha pa seva imakhalanso yokhazikika komanso yayifupi. Ili ndiye graph yapamwamba kumanja.

Chithunzi chakumanzere chakumanzere chikuwonetsa zochitika zazitali kwambiri. Tikuwona kuti zogulitsa zimamalizidwa mwachangu. Ndipo autovacuum sikugwira ntchito pano pano, chifukwa chinali kuyesa koyambira. Idzapitirizabe kugwira ntchito ndipo idzakhala yothandiza kwa ife.

Yachiwiri slide adzakhala odzipereka nthawi zonse mbale kuyesedwa. Munthawi imeneyi, timasintha ma akaunti a kasitomala nthawi zonse. Ndipo tikuwona kuti nthawi yoyankhira pakusintha kwanthawi yayitali ndi yabwino kwambiri, yochepera millisecond. Tikuwona kuti zida za purosesa (ichi ndiye graph yakumanja) zimadyedwanso mofanana komanso zazing'ono.

Chithunzi chakumanja chakumanja chikuwonetsa kuchuluka kwa magwiridwe antchito ndi ma diski omwe timadutsamo posaka mzere womwe tikufuna tisanasinthire. Ndipo kuchuluka kwa ntchito molingana ndi chizindikiro ndi 2 pamphindikati, monga ndidanenera pachiyambi.

Ndipo tsopano tili ndi tsoka. Pazifukwa zina pali kugulitsa kwanthawi yayitali. Zifukwa nthawi zambiri zimakhala zoletsedwa:

• Chimodzi mwazofala kwambiri ndikuti tinayamba kupeza ntchito yakunja mu code yogwiritsira ntchito. Ndipo utumiki uwu sutiyankha. Ndiko kuti, tidatsegula malonda, tidasintha nkhokwe ndikuchoka ku pulogalamuyo kuti tiwerenge makalata kapena ntchito ina mkati mwazomangamanga zathu, ndipo pazifukwa zina sizitiyankha. Ndipo gawo lathu lakakamira pomwe silikudziwika kuti lidzathetsedwa liti.
• Chinthu chachiwiri ndi pamene kusiyana kunachitika mu code yathu pazifukwa zina. Ndipo kupatulapo sitinakonze kutseka kwa malondawo. Ndipo tinamaliza ndi gawo lopachikika ndi ntchito yotseguka.
• Ndipo womaliza ndi wofala kwambiri. Iyi ndi code yotsika. Zolinga zina zimatsegula malonda. Imapachikidwa, ndipo mwina simungadziwe muzolemba kuti mwapachikidwa.

Kodi zinthu zoterezi zimapita kuti?

Mpaka magome athu ndi ma index akuyamba kutukusira kwambiri. Izi ndi chimodzimodzi bloat zotsatira. Kwa database, izi zikutanthauza kuti nthawi yoyankha pa database idzawonjezeka kwambiri ndipo katundu pa seva ya database adzawonjezeka. Ndipo chifukwa cha zimenezi, kugwiritsira ntchito kwathu kudzakhala kovuta. Chifukwa ngati mudakhala 10 milliseconds mu code yanu popempha ku database, 10 milliseconds pamalingaliro anu, ndiye kuti ntchito yanu idatenga 20 milliseconds kuti ithe. Ndipo tsopano mkhalidwe wanu udzakhala womvetsa chisoni kwambiri.

Ndipo tiyeni tione zimene zidzachitike. Chithunzi chakumanzere chakumanzere chikuwonetsa kuti tili ndi nthawi yayitali yayitali. Ndipo ngati tiyang'ana pa graph yakumtunda kumanzere, timawona kuti kukula kwa tebulo lathu kwadumpha mwadzidzidzi kuchoka pa ma megabytes awiri kufika pa 300 megabytes. Nthawi yomweyo, kuchuluka kwa data patebulo sikunasinthe, i.e. pali zinyalala zambiri pamenepo.

Zomwe zimachitika nthawi zambiri zoyankha pa seva zasinthanso ndi maulamuliro angapo a ukulu. Ndiye kuti, zopempha zonse pa seva zidayamba kugwa kwathunthu. Ndipo panthawi imodzimodziyo, njira za Postgres zamkati zinayambitsidwa mwa mawonekedwe a autovacuum, omwe akuyesera kuchita chinachake ndi kuwononga chuma.

Nchiyani chikuchitika ndi chizindikiro chathu? Momwemonso. Avereji yathu yanthawi yoyankha molingana ndi chikwangwani idalumpha maulalo angapo. Makamaka ponena za zinthu zomwe zimagwiritsidwa ntchito, tikuwona kuti katundu pa purosesa wakula kwambiri. Ili ndiye graph yapamwamba kumanja. Ndipo chawonjezeka chifukwa purosesa imayenera kutsata mulu wa mizere yopanda pake kufunafuna yomwe ikufunika. Ili ndiye graph yakumanja yakumunsi. Ndipo chifukwa chake, chiwerengero chathu cha mafoni pa sekondi chinayamba kutsika kwambiri, chifukwa databaseyo inalibe nthawi yokonza zopempha zomwezo.

Tiyenera kukhalanso ndi moyo. Timapita pa intaneti ndikupeza kuti kuchitapo kanthu kwanthawi yayitali kumabweretsa mavuto. Timapeza ndikupha izi. Ndipo zonse zikukhala zabwinobwino kwa ife. Chilichonse chimagwira ntchito momwe chiyenera.

Tinadekha, koma patapita kanthawi timayamba kuzindikira kuti ntchitoyo sikugwira ntchito mofanana ndi momwe zisanachitike mwadzidzidzi. Zopempha zimakonzedwa pang'onopang'ono, komanso pang'onopang'ono. Imodzi ndi theka kuwiri kawiri pang'onopang'ono makamaka mu chitsanzo changa. Katundu pa seva nawonso ndi wapamwamba kuposa momwe zinalili ngozi isanachitike.

Ndipo funso: "Chikuchitika ndi chiyani pamunsi pano?" Ndipo zotsatirazi zimachitika ndi maziko. Pa tchati chogulitsa mutha kuwona kuti yayima ndipo palibenso zochitika zanthawi yayitali. Koma kukula kwa chizindikirocho kunakula kwambiri panthawi ya ngozi. Ndipo kuyambira pamenepo sanachepe. Nthawi yapakati pa maziko yakhazikika. Ndipo mayankho akuwoneka kuti akubwera mokwanira pa liwiro lovomerezeka kwa ife. The autovacuum inakhala yogwira ntchito kwambiri ndipo inayamba kuchita chinachake ndi chizindikirocho, chifukwa imayenera kusefa zambiri.

Mwachindunji, molingana ndi mbale yoyesera ndi maakaunti, komwe timasintha masikelo: nthawi yoyankha pempho ikuwoneka kuti yabwerera mwakale. Koma zoona zake n’zakuti n’kuposa nthawi imodzi ndi theka.

Ndipo kuchokera pa katundu pa purosesa, tikuwona kuti katundu pa purosesa sanabwerere kumtengo wofunikira chisanachitike ngozi. Ndipo zifukwa pamenepo zili ndendende pa graph yakumanja yakumanja. Zitha kuwoneka kuti kuchuluka kwa kukumbukira kumafufuzidwa pamenepo. Ndiko kuti, kuti tipeze mzere wofunikira, timawononga zinthu za seva ya database pamene tikukonza deta yopanda ntchito. Chiwerengero cha zochitika pa sekondi iliyonse chakhazikika.

Zonse zabwino, koma zinthu zafika poipa kuposa momwe zinalili. Chotsani kuwonongeka kwa database chifukwa cha ntchito yathu yomwe imagwira ntchito ndi databaseyi.

Ndipo kuti mumvetse zomwe zikuchitika kumeneko, ngati simunali pa lipoti lapitalo, tsopano tiyeni titenge lingaliro laling'ono. Chiphunzitso cha ndondomeko ya mkati. N'chifukwa chiyani galimoto vacuum ndipo amachita chiyani?

Mwachidule mwachidule kuti mumvetsetse. Nthawi zina timakhala ndi tebulo. Tili ndi mizere mu tebulo. Mizere iyi ikhoza kukhala yogwira ntchito, yamoyo, ndi zomwe tikufuna tsopano. Zalembedwa zobiriwira pachithunzichi. Ndipo pali mizere yakufa yomwe yakonzedwa kale, yasinthidwa, ndipo zatsopano zawonekera pa iwo. Ndipo amalembedwa kuti sakusangalatsanso nkhokwe. Koma ali patebulo chifukwa cha mawonekedwe a Postgres.

N'chifukwa chiyani mukufunikira chopukutira galimoto? Nthawi ina, autovacuum imabwera, imalowa m'dawunilodi ndikuifunsa kuti: "Chonde ndipatseni id ya zomwe zachitika kale zomwe zatsegulidwa pakadali pano." Nawonso database ibweza ID iyi. Ndipo autovacuum, kudalira izo, amasankha mizere mu tebulo. Ndipo ngati awona kuti mizere ina idasinthidwa ndi zochitika zakale kwambiri, ndiye kuti ali ndi ufulu wozilemba ngati mizere yomwe tingagwiritsenso ntchito mtsogolo polemba deta yatsopano pamenepo. Iyi ndi ndondomeko yakumbuyo.

Panthawiyi, tikupitiriza kugwira ntchito ndi database ndikupitirizabe kusintha zina patebulo. Ndipo pamizere iyi, yomwe titha kugwiritsanso ntchito, timalemba zatsopano. Ndipo motero timapeza kuzungulira, mwachitsanzo, nthawi zonse mizere yakale yakufa ikuwonekera pamenepo, m'malo mwake timalemba mizere yatsopano yomwe timafunikira. Ndipo ichi ndi chikhalidwe chabwino kuti PostgreSQL igwire ntchito.

Kodi chinachitika ndi chiyani pa ngoziyi? Kodi zimenezi zinachitika bwanji kumeneko?

Ife tinali nacho chizindikiro mu chikhalidwe china, china chamoyo, china chakufa. Galimoto vacuum yafika. Iye adafunsa nkhokwe zomwe tidachita kale kwambiri ndi id yake. Ndinalandira id iyi, yomwe ikhoza kukhala maola ambiri apitawo, mwina mphindi khumi zapitazo. Zimatengera kulemera komwe muli nako pa database yanu. Ndipo anapita kukafunafuna mizere yomwe angailembe ngati yagwiritsidwanso ntchito. Ndipo sindinapeze mizere yotere patebulo lathu.

Koma panthawiyi tikupitiriza kugwira ntchito ndi tebulo. Timachita chinachake mmenemo, kusintha, kusintha deta. Kodi database iyenera kuchita chiyani panthawiyi? Alibe chochita koma kuwonjezera mizere yatsopano kumapeto kwa tebulo lomwe lilipo. Ndipo motero kukula kwa tebulo lathu kumayamba kukula.

Zowona, timafunikira mizere yobiriwira kuti tigwire ntchito. Koma pavuto lotere, zimakhala kuti kuchuluka kwa mizere yobiriwira kumakhala kotsika kwambiri patebulo lonselo.

Ndipo tikamafunsa, nkhokwe iyenera kudutsa mizere yonse: yofiira ndi yobiriwira, kuti mupeze mzere womwe mukufuna. Ndipo zotsatira za bloating tebulo ndi deta zopanda pake zimatchedwa "bloat", zomwe zimadyanso malo athu a disk. Kumbukirani, inali 2 MB, idakhala 300 MB? Tsopano sinthani ma megabytes kukhala ma gigabytes ndipo mudzataya mwachangu zida zanu zonse zama disk.

Kodi zotsatira zake zingakhale zotani kwa ife?

• Mu chitsanzo changa, tebulo ndi index zidakula nthawi 150. Ena mwamakasitomala athu akhala ndi milandu yowopsa pomwe adangoyamba kutha danga la disk.
• Kukula kwa matebulo komwe sikudzachepa konse. Autovacuum nthawi zina imatha kudula mchira wa tebulo ngati pali mizere yakufa yokha. Koma popeza pali kusinthasintha kosalekeza, mzere umodzi wobiriwira ukhoza kuzizira kumapeto ndipo osasinthidwa, pamene ena onse adzalembedwa penapake kumayambiriro kwa mbaleyo. Koma ichi ndi chochitika chosayembekezereka kotero kuti tebulo lanu lokha lidzacheperachepera, kotero simuyenera kuyembekezera.
• Nawonso database iyenera kusanja mulu wonse wa mizere yopanda ntchito. Ndipo timawononga zida za disk, timawononga purosesa ndi magetsi.
• Ndipo izi zimakhudza mwachindunji ntchito yathu, chifukwa ngati pachiyambi tidakhala 10 milliseconds pa pempho, 10 milliseconds pa code yathu, ndiye panthawi ya ngozi tinayamba kuthera yachiwiri pa pempho ndi 10 milliseconds pa code, i.e. dongosolo la kukula kwa ntchito kwachepa. Ndipo ngoziyo itathetsedwa, tinayamba kugwiritsa ntchito 20 milliseconds pa pempho, 10 milliseconds pa code. Izi zikutanthauza kuti tidatsikabe ndi nthawi imodzi ndi theka pakukolola. Ndipo zonsezi ndi chifukwa cha kugulitsa kumodzi komwe kudayima, mwina chifukwa cha vuto lathu.
• Ndipo funso: "Tingabweze bwanji zonse?" kotero kuti zonse zili bwino ndi ife ndipo zopempha zimabwera mofulumira monga ngozi isanachitike.

Pachifukwa ichi pali ndondomeko ina ya ntchito yomwe ikuchitika.

Choyamba tiyenera kupeza zovuta matebulo amene bloated. Timamvetsetsa kuti m'matebulo ena kujambula kumakhala kogwira ntchito, kwina kumakhala kocheperako. Ndipo pa izi timagwiritsa ntchito kuwonjezera pgstattuple. Pokhazikitsa chowonjezera ichi, mutha kulemba mafunso omwe angakuthandizeni kupeza matebulo omwe ali otupa.

Mukapeza matebulo awa, muyenera kuwapanikiza. Pali kale zida za izi. Pakampani yathu timagwiritsa ntchito zida zitatu. Yoyamba ndi yomangidwa mu VACUUM FULL. Iye ndi wankhanza, wankhanza komanso wopanda chifundo, koma nthawi zina ndi wothandiza kwambiri. Pg_pack и pgcompacttable - Izi ndi zida za chipani chachitatu pakupondereza matebulo. Ndipo amasamalira nkhokwe mosamala kwambiri.

Amagwiritsidwa ntchito kutengera zomwe zili zabwino kwa inu. Koma ndikuuzani za izi kumapeto kwenikweni. Chinthu chachikulu ndi chakuti pali zida zitatu. Pali zambiri zoti musankhe.

Titakonza zonse ndikuwonetsetsa kuti zonse zili bwino, tiyenera kudziwa momwe tingapewere izi mtsogolomu:

• Itha kupewedwa mosavuta. Muyenera kuyang'anira nthawi ya magawo pa Master seva. Makamaka magawo owopsa osagwira ntchito ngati mukuchita. Awa ndi omwe adangotsegula malonda, adachitapo kanthu ndikusiya, kapena kungopachikidwa, adatayika mu code.
• Ndipo kwa inu, monga omanga, ndikofunikira kuyesa nambala yanu izi zikachitika. Sizovuta kuchita. Ichi chikhala cheke chothandiza. Mudzapewa kuchuluka kwa mavuto "achibwana" okhudzana ndi zochitika zazitali.

M'ma grafu awa, ndimafuna kukuwonetsani momwe chizindikiro ndi khalidwe la database linasinthira nditadutsa chizindikiro ndi VACUUM FULL pankhaniyi. Izi sizondipangira ine.

Kukula kwa tebulo nthawi yomweyo kunabwereranso kumalo ake ogwiritsira ntchito ma megabytes angapo. Izi sizinakhudze kwambiri nthawi yoyankha pa seva.

Koma makamaka pachizindikiro chathu choyesa, pomwe tidasintha masikelo aakaunti, tikuwona kuti nthawi yanthawi yoyankhira pa pempho losinthira deta pachizindikirocho idatsitsidwa kukhala milingo yadzidzidzi. Zothandizira zomwe purosesa amagwiritsira ntchito kuti amalize pempholi zidatsikiranso pamlingo wa ngozi zisanachitike. Ndipo graph yakumanja yakumanja ikuwonetsa kuti tsopano timapeza ndendende mzere womwe timafunikira nthawi yomweyo, osadutsa milu ya mizere yakufa yomwe inalipo tebulo lisanapanikizidwe. Ndipo pafupifupi nthawi yopempha idakhalabe pamlingo womwewo. Koma apa ndili ndi cholakwika mu hardware yanga.

Apa ndi pamene nkhani yoyamba ikutha. Ndiwofala kwambiri. Ndipo zimachitika kwa aliyense, mosasamala kanthu za zomwe kasitomala amakumana nazo komanso momwe opanga mapulogalamuwo ali oyenerera. Posakhalitsa izi zimachitika.

Nkhani yachiwiri, momwe timagawira katunduyo ndikukonza zothandizira seva

• Takula kale ndikukhala serious guys. Ndipo timamvetsetsa kuti tili ndi chofanizira ndipo zingakhale bwino kwa ife kulinganiza katunduyo: lembani kwa Ambuye, ndikuwerenga kuchokera pachifanizirocho. Ndipo nthawi zambiri izi zimachitika tikafuna kukonzekera malipoti ena kapena ETL. Ndipo bizinesi ndi yokondwa kwambiri ndi izi. Amafunadi malipoti osiyanasiyana okhala ndi ma analytics ovuta kwambiri.
• Malipoti amatenga maola ambiri, chifukwa ma analytics ovuta sangathe kuwerengeka mu milliseconds. Ife, monga anyamata olimba mtima, timalemba khodi. Mu pulogalamu yoyika timajambula pa Master, ndikuchita malipoti pachifanizirocho.
• Kugawa katundu.
• Chirichonse chimagwira ntchito mwangwiro. Ndife opambana.

Nanga mkhalidwe umenewu umaoneka bwanji? Makamaka pamagrafu awa, ndidawonjezeranso nthawi yamalonda kuchokera ku chithunzi chanthawi yonseyi. Ma graph ena onse amangotanthauza seva ya Master yokha.

Pa nthawiyi, lipoti langa linali litakula. Palinso ena a iwo. Tikuwona kuti nthawi yoyankha pa seva ndiyokhazikika. Tikuwona kuti pachifanizirocho tili ndi ntchito yayitali yomwe imatenga maola a 2. Tikuwona kugwira ntchito kwachete kwa autovacuum, yomwe imayendetsa mizere yomaliza. Ndipo zonse zili bwino ndi ife.

Makamaka, malinga ndi mbale yoyesedwa, tikupitilizabe kusinthira ma akaunti a akaunti kumeneko. Ndipo tilinso ndi nthawi yokhazikika yoyankha pazopempha, kugwiritsa ntchito zida zokhazikika. Zonse zili bwino ndi ife.

Chilichonse chili bwino mpaka pomwe malipotiwo ayamba kubwereranso chifukwa chotsutsana ndi kubwereza. Ndipo amawombera nthawi ndi nthawi.

Timapita pa intaneti ndikuyamba kuwerenga chifukwa chake izi zikuchitika. Ndipo timapeza yankho.

Yankho loyamba ndikuwonjezera kubwereza latency. Tikudziwa kuti lipoti lathu limatha maola atatu. Timayika kuchedwa kubwereza kwa maola atatu. Tikuyambitsa zonse, koma tikupitilizabe kukhala ndi zovuta pomwe malipoti amathetsedwa nthawi zina.

Timafuna kuti chilichonse chikhale changwiro. Timakwera mopitirira. Ndipo tidapeza malo abwino pa intaneti - hot_standby_feedback. Tiyeni tiyatse. Hot_standby_feedback imatilola kuletsa autovacuum pa Master. Motero, timachotseratu mikangano yobwerezabwereza. Ndipo zonse zimatiyendera bwino ndi malipoti.

Ndipo chikuchitika ndi chiyani ndi Master seva panthawiyi? Ndipo tili m'mavuto ndi Master seva. Tsopano tikuwona ma grafu ndikakhazikitsa zonse ziwirizi. Ndipo tikuwona kuti gawoli pazithunzi zathu mwanjira ina idayamba kukhudza momwe zinthu ziliri pa seva ya Master. Ali ndi mphamvu chifukwa adayimitsa kaye autovacuum yomwe imachotsa mizere yomaliza. Kukula kwa tebulo lathu kwakweranso. Avereji yanthawi yofunsa mafunso pankhokwe yonse idakweranso kwambiri. The autovacuum yolimba pang'ono.

Mwachindunji, kuchokera ku mbale yathu, tikuwona kuti zosintha za data pa izo zidalumphiranso kumwamba. Kugwiritsa ntchito kwa CPU kwakulanso chimodzimodzi. Tikudutsanso mizere yambiri yakufa, yopanda ntchito. Ndipo nthawi yoyankha pachizindikirochi komanso kuchuluka kwa zomwe zachitika zatsika.

Zidzawoneka bwanji ngati sitikudziwa zomwe ndikunena kale?

• Timayamba kufunafuna mavuto. Ngati tidakumana ndi zovuta mu gawo loyamba, tikudziwa kuti izi zitha kukhala chifukwa chakuchitapo kanthu ndikupita kwa Mbuye. Tili ndi vuto pa Master. Soseji iye. Imatenthetsa, Avereji Yake Yonyamula ndi pafupifupi zana.
• Zopempha kumeneko zimachedwa, koma sitikuwona zochitika zomwe zatenga nthawi yayitali kumeneko. Ndipo sitikumvetsa chomwe chiri vuto. Sitikumvetsa komwe tingayang'ane.
• Timayang'ana zida za seva. Mwina kuwukira kwathu kudagwa. Mwina memory stick yathu yapsa. Inde, chirichonse chikhoza kuchitika. Koma ayi, ma seva ndi atsopano, zonse zimayenda bwino.
• Aliyense akuthamanga: olamulira, omanga ndi otsogolera. Palibe chomwe chimathandiza.
• Ndipo nthawi ina zonse zimayamba kudzikonza mwadzidzidzi.

Panthawiyi, pempho lachifaniziro chathu lidakonzedwa ndikusiyidwa. Tinalandira lipoti. Business akadali wokondwa. Monga mukuonera, chizindikiro chathu chakulanso ndipo sichidzachepa. Pa graph yokhala ndi magawo, ndidasiya kagawo kakang'ono kakang'ono kameneka kuchokera pachifaniziro kuti mutha kuyerekeza kuti zimatenga nthawi yayitali bwanji mpaka zinthu zitakhazikika.

Gawo latha. Ndipo pokhapokha pakapita nthawi seva imabwera mochuluka kapena mocheperapo. Ndipo pafupifupi nthawi yoyankha zopempha pa seva ya Master imabwerera mwakale. Chifukwa, potsiriza, autovacuum ili ndi mwayi woyeretsa ndikulemba mizere yakufayi. Ndipo anayamba kugwira ntchito yake. Ndipo momwe amachitira mwachangu, mwachangu kwambiri tikhala mu dongosolo.

Malinga ndi piritsi loyesedwa, pomwe timasintha masinthidwe a akaunti, timawona chithunzi chomwechi. Nthawi yapakati yosinthira akaunti imakhazikikanso pang'onopang'ono. Zomwe zimagwiritsidwa ntchito ndi purosesa zimachepetsedwanso. Ndipo chiwerengero cha zochitika pa sekondi iliyonse chimabwerera mwakale. Koma panonso tabwerera mwakale, osati momwe tinalili ngozi isanachitike.

Mulimonsemo, timapeza kutsika kwa magwiridwe antchito, monga momwe zinalili koyamba, kamodzi ndi theka mpaka kawiri, ndipo nthawi zina zambiri.

Zikuwoneka kuti tachita zonse bwino. Gawani katunduyo. Zida sizimangokhala. Tinagawana zopemphazo malinga ndi malingaliro athu, komabe zonse zidayenda bwino.

• Osatsegula hot_standby_feedback? Inde, sikovomerezeka kuyatsa popanda zifukwa zamphamvu. Chifukwa kupotoza uku kumakhudza mwachindunji seva ya Master ndikuyimitsa ntchito ya autovacuum pamenepo. Mwa kuzipangitsa pazithunzi zina ndikuyiwala za izi, mutha kupha Master ndikupeza mavuto akulu ndikugwiritsa ntchito.
• Onjezani max_standby_streaming_delay? Inde, kwa malipoti izi ndi zoona. Ngati muli ndi lipoti la maola atatu ndipo simukufuna kuti liwonongeke chifukwa cha mikangano yobwerezabwereza, ingowonjezerani kuchedwa. Lipoti la nthawi yayitali silimafuna deta yomwe yafika mu database pompano. Ngati muli nayo kwa maola atatu, ndiye kuti mukuyendetsa nthawi yakale ya data. Ndipo kwa inu, kuchedwetsa kwa maola atatu kapena kuchedwetsa maola asanu ndi limodzi sikungasinthe chilichonse, koma mudzalandira malipoti mosalekeza ndipo simudzakhala ndi vuto ndi kugwa kwawo.
• Mwachilengedwe, muyenera kuwongolera magawo aatali pazofananira, makamaka ngati mwaganiza zoyambitsa hot_standby_feedback pa chofananira. Chifukwa chilichonse chikhoza kuchitika. Tinapereka chithunzichi kwa wopanga mapulogalamu kuti athe kuyesa zopemphazo. Adalemba pempho lopenga. Iye anayambitsa izo ndipo anapita kukamwa tiyi, ndipo ife tinapeza Master anakhazikitsidwa. Kapena mwina tayika zolakwika m'menemo. Mikhalidwe imasiyanasiyana. Magawo pazofananira ayenera kuyang'aniridwa mosamala monga momwe ziliri kwa Master.
• Ndipo ngati muli ndi mafunso ofulumira komanso aatali pamapangidwe, ndiye kuti ndibwino kuwagawa kuti mugawire katunduyo. Uwu ndi ulalo wa streaming_delay. Kwa othamanga, khalani ndi chofanizira chimodzi chochedwa kubwereza pang'ono. Pazofunsira kwanthawi yayitali, khalani ndi chofanizira chomwe chimatha kuchedwa ndi maola 6 kapena tsiku. Izi ndizochitika zachilendo.

Timachotsa zotsatira zake chimodzimodzi:

• Timapeza matebulo otupa.
• Ndipo timachipondereza ndi chida chothandiza kwambiri chomwe chimatikwanira.

Nkhani yachiwiri ikuthera apa. Tiyeni tipitirire ku nkhani yachitatu.

Komanso zofala kwa ife mmene timasamuka.

• Pulogalamu iliyonse yamapulogalamu ikukula. Zofunikira zake zikusintha. Mulimonsemo, tikufuna kukulitsa. Ndipo zimachitika kuti tifunika kusintha zomwe zili patebulo, kutanthauza kuyendetsa zosintha zokhudzana ndi kusamuka kwathu chifukwa cha ntchito zatsopano zomwe tikuyambitsa monga gawo la chitukuko chathu.
• Mtundu wakale wa data sizokhutiritsa. Tinene kuti tsopano titembenukira ku tebulo lachiwiri, pomwe ndili ndi zochitika pamaakaunti awa. Ndipo tinene kuti iwo anali mu ruble, ndipo tinaganiza zoonjezera zolondola ndikuzichita mu kopecks. Ndipo chifukwa cha izi tiyenera kupanga zosintha: chulukitsani gawolo ndi kuchuluka kwa malonda ndi zana.
• M'dziko lamasiku ano, timagwiritsa ntchito zida zowongolera zamtundu wa database. Tinene Liquibase. Timalembetsa kusamuka kwathu kumeneko. Timayesa pamayeso athu. Zonse zili bwino. Zosintha zikudutsa. Zimalepheretsa ntchito kwakanthawi, koma timapeza zosintha. Ndipo tikhoza kuyambitsa ntchito zatsopano pa izi. Zonse zidayesedwa ndikufufuzidwa. Chirichonse chinatsimikiziridwa.
• Tinagwira ntchito yokonzekera ndikusamuka.

Pano pali kusamuka komwe kuli ndi zosintha zomwe zaperekedwa patsogolo panu. Popeza awa ndi akaunti yanga, mbaleyo inali 15 GB. Ndipo popeza timasintha mzere uliwonse, timawonjezera kukula kwa tebulo ndi zosintha, chifukwa timalembanso mzere uliwonse.

Pa kusamuka, sitinathe kuchita chilichonse ndi mbale iyi, chifukwa zopempha zonse kwa izo zinali pamzere ndikudikirira mpaka izi zitamalizidwa. Koma apa ndikufuna kutengera chidwi chanu ku manambala omwe ali pa axis ofukula. Ndiko kuti, tili ndi nthawi yopempha nthawi zambiri tisanasamuke pafupifupi 5 milliseconds ndi purosesa katundu, chiwerengero cha ntchito chipika powerenga disk kukumbukira ndi zosakwana 7,5.

Tinasamuka ndipo tinakumananso ndi mavuto.

Kusamukako kunayenda bwino, koma:

• Ntchito yakale tsopano imatenga nthawi yayitali kuti ithe.
• Gomelo linakulanso kukula.
• Katundu pa seva adakhalanso wamkulu kuposa kale.
• Ndipo, zowona, tikuyang'anabe ndi magwiridwe antchito omwe adagwira bwino, tawongolera pang'ono.

Ndipo izi ndi zotupa, zomwe zimawononganso miyoyo yathu.

Apa ndikuwonetsa kuti tebulo, monga milandu iwiri yapitayi, silibwereranso kukula kwake. Kuchuluka kwa seva kumakhala kokwanira.

Ndipo ngati titembenukira patebulo ndi maakaunti, tiwona kuti nthawi yopempha yawirikiza kawiri pa tebulo ili. Katundu pa purosesa ndi kuchuluka kwa mizere yosungidwa kukumbukira kunalumpha pamwamba pa 7,5, koma inali yotsika. Ndipo idalumphira ka 2 ngati ma processor, nthawi 1,5 pakuchita ntchito za block, i.e. tidawonongeka pakugwirira ntchito kwa seva. Ndipo zotsatira zake - kuwonongeka kwa magwiridwe antchito athu. Pa nthawi yomweyo, chiwerengero cha mafoni anakhalabe pafupifupi pa mlingo womwewo.

Ndipo chinthu chachikulu apa ndikumvetsetsa momwe mungachitire kusamuka koteroko molondola. Ndipo ziyenera kuchitidwa. Timasamuka izi mosasinthasintha.

• Kusamuka kwakukulu koteroko sikumangochitika zokha. Ayenera kulamulidwa nthawi zonse.
• Kuyang'aniridwa ndi munthu wodziwa zambiri ndikofunikira. Ngati muli ndi DBA pagulu lanu, ndiye lolani DBA itero. Ndi ntchito yake. Ngati sichoncho, ndiye kuti munthu wodziwa zambiri achite, yemwe amadziwa momwe angagwiritsire ntchito ma database.
• Dongosolo latsopano la database, ngakhale tisintha gawo limodzi, timakonzekera pang'onopang'ono, mwachitsanzo, pasadakhale mtundu watsopano wa pulogalamuyo usanatulutsidwe:
• Magawo atsopano amawonjezedwa momwe tidzajambulira zomwe zasinthidwa.
• Timasamutsa deta kuchokera kumunda wakale kupita kumunda watsopano m'magawo ang'onoang'ono. N’chifukwa chiyani tikuchita zimenezi? Choyamba, nthawi zonse timayendetsa ndondomekoyi. Tikudziwa kuti tasamutsa kale magulu ambiri ndipo tatsala ambiri.
• Ndipo zotsatira zabwino zachiwiri ndikuti pakati pa gulu lililonse lotere timatseka malondawo, tsegulani yatsopano, ndipo izi zimalola kuti autovacuum igwire ntchito molingana ndi mbale, lembani mizere yakufa kuti igwiritsidwenso ntchito.
• Kwa mizere yomwe idzawonekere pamene pulogalamuyo ikugwira ntchito (tidakali ndi ntchito yakale), timawonjezera choyambitsa chomwe chimalemba zatsopano kumadera atsopano. Kwa ife, uku ndikuchulukitsa ndi zana lamtengo wakale.
• Ngati tili ouma khosi ndipo tikufuna gawo lomwelo, ndiye kuti tikamaliza kusamuka konse komanso tisanatulutse pulogalamu yatsopano, timangotchulanso magawowo. Zakale zimapatsidwa dzina lina lopangidwa, ndipo minda yatsopano imasinthidwa kukhala yakale.
• Ndipo zitatha izi timakhazikitsa mtundu watsopano wa pulogalamuyi.

Ndipo panthawi imodzimodziyo sitidzakhala ndi bloat ndipo sitidzavutika ndi ntchito.

Apa ndi pamene nkhani yachitatu ikutha.

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

Ndipo tsopano tsatanetsatane pang'ono za zida zomwe ndatchula m'nkhani yoyamba.

Musanafufuze bloat, muyenera kukhazikitsa zowonjezera pgstattuple.

Kuti musakhale ndi mafunso, talemba kale mafunsowa m'ntchito yathu. Mukhoza kuzigwiritsa ntchito. Pali zopempha ziwiri apa.

• Yoyamba imatenga nthawi yayitali kuti igwire ntchito, koma ikuwonetsani zomwe zili patebulopo.
• Yachiwiri imagwira ntchito mofulumira ndipo imakhala yothandiza kwambiri pamene mukufunikira kufufuza mwamsanga ngati pali bloat kapena ayi molingana ndi tebulo. Ndipo muyenera kumvetsetsa kuti bloat nthawi zonse imakhala patebulo la Postgres. Ichi ndi mawonekedwe ake a MVCC.
• Ndipo 20% bloat ndi yachilendo kwa matebulo nthawi zambiri. Ndiko kuti, musadandaule ndikupondereza tebulo ili.

Tinalingalira momwe tingadziwire matebulo omwe ali otupa ndi deta yopanda pake.

Tsopano za momwe mungakonzere bloat:

• Ngati tili ndi piritsi yaying'ono ndi ma disks abwino, ndiye kuti, pa piritsi mpaka gigabyte, ndizotheka kugwiritsa ntchito VACUUM FULL. Adzakutengerani loko patebulo kwa masekondi angapo ndipo zili bwino, koma adzachita zonse mwachangu komanso mwankhanza. Kodi VACUUM FULL imachita chiyani? Pamafunika loko patebulo ndikulembanso mizere yamoyo kuchokera pamatebulo akale kupita patebulo latsopano. Ndipo pamapeto amawalowetsa m’malo. Imachotsa mafayilo akale ndikusintha akale ndi atsopano. Koma kwa nthawi yonse ya ntchito yake, zimatengera loko yokha patebulo. Izi zikutanthauza kuti simungathe kuchita chilichonse ndi gome ili: kapena kulilembera, kapena kuwerenga, kapena kusintha. Ndipo VACUUM FULL imafuna malo owonjezera a disk kuti alembe deta.
• Chida chotsatira pg_pack. Mwachidziwitso chake, ndizofanana kwambiri ndi VACUUM FULL, chifukwa imalembanso deta kuchokera ku mafayilo akale kupita ku atsopano ndikuyika m'malo mwa tebulo. Koma nthawi yomweyo, sizitenga loko yokha patebulo kumayambiriro kwa ntchito yake, koma zimangotengera nthawi yomwe ili ndi deta yokonzeka kuti isinthe mafayilo. Zofunikira zake zama disk ndizofanana ndi za VACUUM FULL. Mufunika malo owonjezera a disk, ndipo nthawi zina zimakhala zovuta ngati muli ndi matebulo a terabyte. Ndipo ili ndi njala ya purosesa chifukwa imagwira ntchito ndi I/O.
• Ntchito yachitatu ndi pgcompacttable. Ndiwosamala kwambiri ndi zothandizira chifukwa zimagwira ntchito molingana ndi mfundo zosiyana pang'ono. Lingaliro lalikulu la pgcompacttable ndikuti imasuntha mizere yonse mpaka koyambirira kwa tebulo pogwiritsa ntchito zosintha patebulo. Ndiyeno patebuloli pamakhala chopanda kanthu, chifukwa tikudziwa kuti tili ndi mizere yamoyo pachiyambi ndi mizere yakufa kumapeto. Ndipo vacuum yokhayo imadula mchira uwu, mwachitsanzo, sichifuna malo owonjezera a disk. Ndipo panthawi imodzimodziyo, ikhoza kufinyidwa malinga ndi zofunikira.

Chilichonse ndi zida.

Ngati mupeza mutu wa bloat wosangalatsa pakufufuza mkati, nayi maulalo othandiza:

• https://www.slideshare.net/alexius2Mb/where-is-the-space-postgres - Ili ndi lipoti lochokera kwa mnzanga. Ndizodziwika komwe malo a Postgres amapita panthawi ya ntchito ndi moyo wake. Ndipo pali chida chaukadaulo chachikulu komanso chatsatanetsatane cha oyang'anira nkhokwe za bloat.
• https://github.com/dataegret/pg-utils - uwu ndi ulalo kunkhokwe yathu, pomwe timasunga zolemba zambiri zothandiza kuti tiwone momwe malowo alili. Kumeneko mungapeze zolemba kuti mufufuze bloat.
• Chachitatu и chachinayi kulumikizana ndi zida zomwe zingakuthandizeni kuchepetsa zizindikiro.
• http://blog.dataegret.com/2Mb018/03/postgresql-bloatbusters.html - iyi ndi positi kuchokera kwa mnzanga. Kumeneko iye mozama komanso mwaukadaulo amasanthula bloat mwatsatanetsatane pamlingo pafupi ndi oyang'anira.

Ndidayesa zambiri kuwonetsa nkhani yowopsa kwa opanga, chifukwa ndi makasitomala athu achindunji ndipo ayenera kumvetsetsa zomwe zimatsogolera komanso zomwe zimatsogolera. Ndikukhulupirira kuti ndapambana. Zikomo chifukwa chakumvetsera!

Mafunso anu

Zikomo chifukwa cha lipoti! Munakambirana za momwe mungadziwire mavuto. Kodi angachenjezedwe bwanji? Ndiko kuti, ndinali ndi vuto lomwe zopempha zinapachikidwa osati chifukwa chakuti adapeza ntchito zina zakunja. Awa anali malumikizanidwe osalongosoka. Panali zopempha zing'onozing'ono, zopanda vuto zomwe zinkakhala kwa tsiku limodzi, kenako zinayamba kuchita zopanda pake. Ndiko kuti, zofanana kwambiri ndi zomwe mumafotokoza. Kodi kutsatira izi? Khalani ndi kuyang'ana nthawi zonse ndi pempho liti lomwe lakakamira? Kodi zimenezi zingapewedwe bwanji?

Pankhaniyi, iyi ndi ntchito ya oyang'anira kampani yanu, osati ya DBA.

Ndine woyang'anira.

PostgreSQL ili ndi mawonekedwe otchedwa pg_stat_activity omwe amawonetsa mafunso akulendewera. Ndipo inu mukhoza kuwona kutalika kwake kwapachikidwa pamenepo.

Kodi ndiyenera kubwera ndikuyang'ana mphindi zisanu zilizonse?

Konzani cron ndikuyang'ana. Ngati muli ndi pempho lalitali, lembani kalata ndipo ndizomwezo. Ndiye kuti, simuyenera kuyang'ana ndi maso anu, zitha kukhala zokha. Mudzalandira kalata, mukuchitapo kanthu. Kapena mutha kuwombera zokha.

Kodi pali zifukwa zodziwikiratu zomwe izi zimachitika?

Ndalembapo zina. Zitsanzo zina zovuta kwambiri. Ndipo pakhoza kukhala kukambirana kwa nthawi yaitali.

Zikomo chifukwa cha lipoti! Ndinkafuna kufotokozera za pg_repack utility. Ngati sapanga loko yekha, ndiye ...

Amapanga loko yekha.

... ndiye ndikhoza kutaya deta. Kodi pulogalamu yanga sikuyenera kujambula chilichonse panthawiyi?

Ayi, imagwira ntchito bwino ndi tebulo, mwachitsanzo, pg_repack imasamutsa mizere yonse yomwe ilipo. Mwachilengedwe, kulowa kwamtundu wina patebulo kumachitika pamenepo. Iye akungoponyera mchira wa kavalo uwu.

Ndiko kuti, iye amachita izo pamapeto pake?

Pomaliza, amatenga loko yokhayo kuti asinthe mafayilowa.

Kodi idzakhala yachangu kuposa VACUUM FULL?

VACUUM FULL, itangoyamba, nthawi yomweyo idatenga loko yokha. Ndipo mpaka atachita zonse, sangamulole kupita. Ndipo pg_repack imatenga loko yokhayokha panthawi yosinthira mafayilo. Panthawiyi simudzalemba pamenepo, koma deta sidzatayika, zonse zikhala bwino.

Moni! Munalankhula za kachitidwe ka vacuum yamagalimoto. Panali graph yokhala ndi ma cell ojambulira ofiira, achikasu ndi obiriwira. Ndiko kuti, zachikasu - adaziyika ngati zachotsedwa. Ndipo monga chotulukapo chake, chinachake chatsopano chingalembedwemo?

Inde. Postgres samachotsa mizere. Ali ndi tsatanetsatane wotero. Tikasintha mzere, timayika wakale ngati wachotsedwa. Id ya zomwe zasintha mzerewu zikuwonekera pamenepo, ndipo timalemba mzere watsopano. Ndipo tili ndi magawo omwe amatha kuwawerenga. Nthawi zina amakalamba ndithu. Ndipo chofunikira cha momwe autovacuum imagwirira ntchito ndikuti imadutsa mizere iyi ndikuyiyika ngati yosafunikira. Ndipo mukhoza kulemba deta pamenepo.

Ndikumvetsa. Koma sichomwe funso likunena. Sindinatsirize. Tiyerekeze kuti tili ndi tebulo. Ili ndi minda ya kukula kosiyanasiyana. Ndipo ngati ndiyesera kulowetsa china chatsopano, sichingafike m’selo yakaleyo.

Ayi, mulimonse mzere wonsewo umasinthidwa pamenepo. Postgres ili ndi mitundu iwiri yosungira deta. Zimasankha kuchokera kumtundu wa data. Pali deta yomwe imasungidwa mwachindunji patebulo, ndipo palinso deta ya tos. Izi ndi zambiri za data: zolemba, json. Iwo amasungidwa mu mbale zosiyana. Ndipo molingana ndi mapiritsiwa, nkhani yofanana ndi bloat imachitika, i.e. zonse ndizofanana. Iwo amangotchulidwa padera.

Zikomo chifukwa cha lipoti! Kodi ndizovomerezeka kugwiritsa ntchito mafunso omaliza kuti muchepetse nthawi?

Zovomerezeka kwambiri. Timagwiritsa ntchito izi kulikonse. Ndipo popeza tilibe ntchito zathu, timapereka chithandizo chakutali, tili ndi makasitomala osiyanasiyana. Ndipo aliyense amakhutira kwathunthu ndi izi. Ndiye kuti, tili ndi ntchito za cron zomwe zimayang'ana. Kutalika kwa magawo kumangogwirizana ndi kasitomala, zomwe tisanagwirizane nazo. Itha kukhala miniti, ikhoza kukhala mphindi 10. Zimatengera katundu pamunsi ndi cholinga chake. Koma tonse timagwiritsa ntchito pg_stat_activity.

Zikomo chifukwa cha lipoti! Ndikuyesera kuyika lipoti lanu pazofunsira zanga. Ndipo zikuwoneka ngati timayamba kugulitsa kulikonse, ndikumaliza momveka bwino kulikonse. Ngati pali zosiyana, ndiye kuti kubwezeretsanso kumachitika. Ndiyeno ndinayamba kuganiza. Kupatula apo, kugulitsako sikungayambe momveka bwino. Izi mwina ndi lingaliro kwa mtsikanayo. Ngati ndingosintha mbiri, kodi kugulitsako kudzayamba ku PostgreSQL ndikungomaliza pomwe kulumikizana kulibe?

Ngati mukulankhula tsopano za mulingo wogwiritsa ntchito, ndiye kuti zimatengera dalaivala yemwe mukugwiritsa ntchito, pa ORM yomwe ikugwiritsidwa ntchito. Pali zokonda zambiri pamenepo. Ngati muli ndi mwayi wodzipereka, ndiye kuti kugulitsa kumayambira pamenepo ndikutseka nthawi yomweyo.

Ndiko kuti, imatseka atangomaliza kumene?

Zimatengera zoikamo. Ndinatchula malo amodzi. Uku ndikudzipereka kwa auto. Ndizofala kwambiri. Ngati yayatsidwa, ndiye kuti ntchitoyo yatsegulidwa ndikutseka. Pokhapokha mutanena momveka bwino kuti "yambani kuchitapo kanthu" ndi "kuthetsa ntchito", koma mwangoyambitsa pempho mu gawoli.

Moni! Zikomo chifukwa cha lipoti! Tiyerekeze kuti tili ndi database yomwe ikutupa ndi kutupa ndiyeno malo pa seva amatha. Kodi pali zida zothetsera vutoli?

Malo pa seva akuyenera kuyang'aniridwa bwino.

Mwachitsanzo, DBA adapita kukamwa tiyi, anali pamalo ochezera, etc.

Fayilo ikapangidwa, osachepera mtundu wina wa malo osungira amapangidwa pomwe deta sinalembedwe.

Nanga bwanji ngati ili pansi pa ziro?

Kumeneko kumatchedwa malo osungidwa, mwachitsanzo, akhoza kumasulidwa ndipo malingana ndi kukula kwake komwe kunapangidwira, mumapeza malo aulere. Mwachikhazikitso sindikudziwa kuti alipo angati. Ndipo nthawi ina, perekani ma disks kuti mukhale ndi malo opangira ntchito yokonzanso. Mutha kufufuta tebulo lina lomwe mwatsimikizika kuti simulifunikira.

Kodi pali zida zina?

Nthawi zonse zimakhala zopangidwa ndi manja. Ndipo kwanuko zimadziwikiratu zomwe zili bwino kuchita kumeneko, chifukwa zina ndizovuta ndipo zina sizowopsa. Ndipo pa database iliyonse ndi ntchito yomwe imagwira ntchito nayo, zimatengera bizinesi. Nthawi zonse zimasankhidwa kwanuko.

Zikomo chifukwa cha lipoti! Ndili ndi mafunso awiri. Choyamba, munawonetsa zithunzi zomwe zikuwonetsa kuti malonda akakakamira, kukula kwa tebulo komanso kukula kwa index kumakula. Komanso pa lipotilo panali zinthu zambiri zomwe zimanyamula piritsi. Nanga bwanji index?

Amawanyamulanso.

Koma vacuum sichikhudza index?

Ena amagwira ntchito ndi index. Mwachitsanzo, pg_rapack, pgcompacttable. Vutoli limapanganso ma index ndikuwakhudza. Ndi VACUUM FULL lingaliro ndikulemba zonse, mwachitsanzo, zimagwira ntchito ndi aliyense.

Ndipo funso lachiwiri. Sindikumvetsa chifukwa chake malipoti ofananira amadalira kwambiri kubwereza komweko. Zinkawoneka kwa ine kuti malipoti amawerengedwa, ndipo kubwereza ndikulemba.

Nchiyani chimayambitsa mkangano wobwerezabwereza? Tili ndi Master pazomwe zimachitika. Tili ndi vacuum yamagalimoto ikuchitika. Kodi autovacuum imachita chiyani? Iye akudula mizere ina yakale. Ngati panthawiyi tili ndi pempho pa chofanizira chomwe chimawerenga mizere yakale iyi, ndipo pa Master zinthu zidachitika kuti autovacuum idalemba mizere iyi momwe ingathere kuti ilembedwenso, ndiye tidawalemba. Ndipo tidalandira paketi ya data, tikafunika kulembanso mizere yomwe pempho likufunika pachifaniziro, kubwereza kumadikirira nthawi yomwe mudakonza. Kenako PostgreSQL idzasankha zomwe zili zofunika kwambiri kwa izo. Ndipo kubwereza kuli kofunika kwambiri kwa iye kuposa pempho, ndipo adzawombera pempho kuti asinthe izi pa chithunzicho.

Andrey, ndili ndi funso. Ma graph odabwitsa awa omwe mudawonetsa powonetsa, kodi izi ndi zotsatira za ntchito yamtundu wina wantchito yanu? Kodi ma graph anapangidwa bwanji?

Iyi ndi ntchito Okmeter.

Kodi iyi ndi malonda?

Inde. Ichi ndi malonda ogulitsa.

Source: www.habr.com