Muva nje ngikutshele ukuthi kanjani, usebenzisa izindlela zokupheka ezijwayelekile kusuka ku-database ye-PostgreSQL. Namuhla sizokhuluma ngokuthi kanjani ukurekhoda kungenziwa ngokuphumelelayo ku-database ngaphandle kokusebenzisa noma yikuphi "ukusonta" ku-config - ngokumane uhlele kahle ukugeleza kwedatha.
#1. Ukuhlukaniswa
Indatshana emayelana nokuthi kungani kufanelekile ukuyihlela isivele ikhona, lapha sizokhuluma ngomkhuba wokusebenzisa ezinye izindlela ngaphakathi kwethu .
"Izinto zezinsuku ezedlule..."
Ekuqaleni, njenganoma iyiphi i-MVP, iphrojekthi yethu yaqala ngaphansi komthwalo olula - ukuqapha kwenziwa kuphela kumaseva ayishumi abaluleke kakhulu, wonke amatafula ayehlangene ngokuqhathaniswa... , futhi saphinda sazama ukwenza okuthile ngeyodwa yazo amathebula 1.5TB ngosayizi, saqaphela ukuthi nakuba kwakungenzeka ukuqhubeka siphila ngale ndlela, kwakungelula neze.
Izikhathi zazicishe zifane nezikhathi ezimangalisayo, izinguqulo ezihlukene ze-PostgreSQL 9.x zazifanelekile, ngakho konke ukwahlukanisa kwakufanele kwenziwe “ngesandla” - ngokusebenzisa ifa letafula kanye nezicupha umzila nge-dynamic EXECUTE.
Isixazululo esibe umphumela sivele sasebenza jikelele ngokwanele ukuthi singahunyushwa kuwo wonke amathebula:
- Ithebula lomzali “elingenalutho” limenyezelwe, elichaza konke izinkomba ezidingekayo kanye nokucupha.
- Irekhodi ngokombono weklayenti lenziwe kuthebula “lezimpande”, futhi kusetshenziswa ngaphakathi inhlamvu yomzila
BEFORE INSERTirekhodi lafakwa “ngokomzimba” esigabeni esidingekayo. Uma ingakabi khona into enjalo, sibambe okuhlukile futhi... - … ngokusebenzisa yakhiwe ngokusekelwe kusifanekiso sethebula lomzali isigaba esinomkhawulo ngosuku olufunayoukuze uma idatha ibuyiswa, ukufunda kwenziwa kuyo kuphela.
PG10: umzamo wokuqala
Kodwa ukwahlukanisa ngefa ngokomlando bekungafaneleki kahle ukubhekana nokusakazwa kokubhala okusebenzayo noma inani elikhulu lokuhlukaniswa kwezingane. Isibonelo, ungakhumbula ukuthi i-algorithm yokukhetha isigaba esidingekayo yayinayo ubunzima be-quadratic, ukuthi isebenza ngezigaba eziyi-100+, wena ngokwakho uyaqonda ukuthi kanjani...
Ku-PG10 lesi simo sathuthukiswa kakhulu ngokusebenzisa ukwesekwa . Ngakho-ke, sazama ngokushesha ukusisebenzisa ngokushesha ngemva kokuthutha isitoreji, kodwa...
Njengoba kuvele ngemuva kokumba imanuwali, ithebula elihlukaniswe ngokomdabu kule nguqulo lithi:
- ayisekeli izincazelo zenkomba
- ayisekeli izibangeli kuyo
- angeke kube "inzalo" yanoma ubani
- ungasekeli
INSERT ... ON CONFLICT - ayikwazi ukukhiqiza isigaba ngokuzenzakalelayo
Ngemva kokuthola inxeba elibuhlungu esiphongweni ngeraki, sabona ukuthi kwakungeke kwenzeke ngaphandle kokuguqula isicelo, futhi sihlehlise olunye ucwaningo izinyanga eziyisithupha.
PG10: ithuba lesibili
Ngakho-ke, saqala ukuxazulula izinkinga ezavela ngamunye ngamunye:
- Ngoba izimbangela kanye
ON CONFLICTSithole ukuthi sisawadinga lapha nalapha, ngakho senza isiteji esiphakathi ukuze silungise ithebula lommeleli. - Lahla "umzila" kuma-triggers - okungukuthi, kusuka
EXECUTE. - Bayikhiphe ngokwehlukana ithebula lesifanekiso elinazo zonke izinkombaukuze bangabi khona ngisho kuthebula lommeleli.
Ekugcineni, ngemva kwakho konke lokhu, sahlukanisa itafula elikhulu ngokwendabuko. Ukwakhiwa kwesigaba esisha kusasele kunembeza wohlelo lokusebenza.
"Sawing" izichazamazwi
Njengakunoma yiluphi uhlelo lokuhlaziya, nathi saba nakho "amaqiniso" kanye "nokusikeka" (izichazamazwi). Esimweni sethu, kulesi sikhundla benza, isibonelo, imibuzo enensa efanayo noma umbhalo wombuzo ngokwawo.
"Amaqiniso" ahlukaniswa emini isikhathi eside kakade, ngakho sasusa ngokuthula izingxenye eziphelelwe yisikhathi, futhi azizange zisikhathaze (izingodo!). Kodwa kube nenkinga ngezichazamazwi...
Hhayi ukusho ukuthi zaziningi zazo, kodwa cishe I-100TB "yamaqiniso" ibangele isichazamazwi esingu-2.5TB. Awukwazi ukususa noma yini etafuleni elinjalo, awukwazi ukulicindezela ngesikhathi esanele, futhi ukulibhalela kancane kancane.
Njengesichazamazwi... kuso, okufakiwe ngakunye kufanele kwethulwe kanye ncamashi... futhi lokhu kulungile, kodwa!.. Akekho osivimbayo ekubeni sibe nayo isichazamazwi esihlukile sosuku ngalunye! Yebo, lokhu kuletha ukuphindaphindeka okuthile, kodwa kuvumela:
- bhala/funda ngokushesha ngenxa yosayizi wesigaba esincane
- sebenzisa inkumbulo encane ngokusebenzisana nezinkomba ezihlangene
- gcina idatha encane ngenxa yekhono lokukhipha ngokushesha okuphelelwe yisikhathi
Njengomphumela wayo yonke inkimbinkimbi yezinyathelo Umthwalo we-CPU wehle ngo-~30%, umthwalo wediski ngo-~50%:
Ngesikhathi esifanayo, saqhubeka nokubhala into efanayo ku-database, ngomthwalo omncane nje.
#2. Database evolution kanye refactoring
Ngakho sazinza kulokho esinakho usuku ngalunye lunengxenye yalo ngedatha. Empeleni, CHECK (dt = '2018-10-12'::date) - futhi kukhona ukhiye wokuhlukanisa kanye nombandela wokuthi irekhodi liwele esigabeni esithile.
Njengoba yonke imibiko ekusevisi yethu yakhiwe kumongo wedethi ethile, izinkomba zayo kusukela "ngezikhathi ezingahlukanisiwe" bekuyizo zonke izinhlobo. (Iseva, Usuku, Isifanekiso Sohlelo), (Iseva, Usuku, Uhlelo lwenodi), (Usuku, Isigaba samaphutha, Iseva)...
Kodwa manje bahlala kuzo zonke izingxenye amakhophi akho inkomba ngayinye enjalo... Futhi ngaphakathi kwesigaba ngasinye usuku alushintshi... Kuvela ukuthi manje sikukhombe ngayinye enjalo mane ufake okungaguquki njengenye yezinkambu, okwandisa kokubili umthamo wayo kanye nesikhathi sokusesha, kodwa akulethi noma yimuphi umphumela. Bazishiyele ireki, hawu...
Isiqondiso sokwenza kahle sisobala - silula susa inkambu yedethi kuzo zonke izinkomba ematafuleni ahlukanisiwe. Uma kubhekwa imiqulu yethu, inzuzo imayelana 1TB/ngeviki!
Manje ake siqaphele ukuthi le terabyte kwakusafanele iqoshwe ngandlela thile. Okungukuthi, nathi idiski kufanele manje ilayishe kancane! Lesi sithombe sibonisa ngokucacile umphumela otholakala ekuhlanzeni, esikunikeze isonto lonke:
#3. "Ukusabalalisa" umthwalo ophezulu
Enye yezinkinga ezinkulu zamasistimu alayishiwe ukuvumelanisa okungafuneki eminye imisebenzi engayidingi. Ngezinye izikhathi "ngoba abazange baqaphele", ngezinye izikhathi "kwakulula ngaleyo ndlela", kodwa ngokushesha noma kamuva kufanele uyisuse.
Ake sisondeza isithombe sangaphambilini futhi sibone ukuthi sinediski "amaphampu" ngaphansi komthwalo nge-amplitude ephindwe kabili phakathi kwamasampuli aseduze, okusobala “ngokwezibalo” akufanele kwenzeke ngenani lemisebenzi enjalo:
Lokhu kulula kakhulu ukukufeza. Sesivele siqalile ukuqapha cishe amaseva ayi-1000, ngayinye icutshungulwa ngomucu onengqondo ohlukile, futhi umucu ngamunye usetha kabusha ulwazi oluqoqiwe ukuze luthunyelwe kusizindalwazi ngezikhathi ezithile, into efana nale:
setInterval(sendToDB, interval)Inkinga lapha ilele eqinisweni lokuthi yonke imicu iqala cishe ngesikhathi esifanayo, ngakho izikhathi zabo zokuthumela cishe njalo ziqondana “ngokuqondile.” Eish #2...
Ngenhlanhla, lokhu kulula kakhulu ukukulungisa, yengeza "okungahleliwe". ngesikhathi:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Sigcina lokho esikudingayo
Inkinga yesithathu yendabuko yokulayisha i ayikho inqolobane lapho ekhona kungenzeka uku.
Isibonelo, senze kwaba nokwenzeka ukuhlaziya ngokuya ngamanodi epulani (konke lokhu Seq Scan on users), kodwa ngokushesha bacabange ukuthi, ngokwengxenye enkulu, bayafana - bakhohlwe.
Cha, vele, akukho okubhaliwe ku-database futhi, lokhu kunqamula i-trigger ngayo INSERT ... ON CONFLICT DO NOTHING. Kodwa le datha isafinyelela ku-database, futhi akudingekile ukufunda ukuhlola ukungqubuzana okufanele ukwenze. Eish #3...
Umehluko enanini lamarekhodi athunyelwe kusizindalwazi ngaphambi/ngemuva kokufakwa kunqolobane kunikwe amandla uyabonakala:
Futhi lokhu ukwehla okuhambisana nomthwalo wesitoreji:
Inani
"I-Terabyte-ngosuku" izwakala ithusa nje. Uma wenza konke kahle, khona-ke lokhu kulungile 2^40 amabhayithi / 86400 imizuzwana = ~12.5MB/sukuthi ngisho nezikulufu ze-IDE zedeskithophu zibanjwe. 🙂
Kodwa ngokungathí sina, ngisho “ne-skew” ephindwe kashumi yomthwalo phakathi nosuku, ungakwazi ukuhlangabezana kalula namakhono ama-SSD anamuhla.
Source: www.habr.com