Kungekudala ndikuxelele indlela, usebenzisa iindlela zokupheka eziqhelekileyo ukusuka kwisiseko sedatha yePostgreSQL. Namhlanje siza kuthetha ngendlela ukurekhoda kunokwenziwa ngokufanelekileyo kwisiseko sedatha ngaphandle kokusebenzisa nayiphi na "i-twist" kwi-config - ngokulula ngokulungelelanisa ngokuchanekileyo ukuhamba kwedatha.
#1. Ukwahlulahlula
Inqaku malunga nendlela kwaye kutheni kufanelekile ukulungiselela sele ikhona, apha siza kuthetha malunga nendlela yokusebenzisa ezinye iindlela ngaphakathi kwethu .
"Izinto zeentsuku ezidlulileyo ..."
Ekuqaleni, njengayo nayiphi na i-MVP, iprojekthi yethu yaqala phantsi komthwalo olula ngokufanelekileyo - ukubeka iliso kuqhutywe kuphela kwiiseva ezilishumi ezibaluleke kakhulu, zonke iitafile zazihlangene ... , kwaye kwakhona sazama ukwenza into ngenye ye iitafile 1.5TB ngobukhulu, saqonda ukuba nangona kwakunokwenzeka ukuqhubeka siphila ngolu hlobo, kwakunzima gqitha.
Amaxesha ayephantse afane namaxesha abalulekileyo, iinguqulelo ezahlukeneyo zePostgreSQL 9.x zazifanelekile, ngoko ke lonke ulwahlulo kwakufuneka lwenziwe “ngesandla” - ngokusebenzisa itheyibhile ilifa kunye nezibangela indlela enentshukumo EXECUTE.
Isisombululo esibe sisiphumo sajika saba jikelele ngokwaneleyo kangangokuba sinokuguqulelwa kuzo zonke iitafile:
- Itheyibhile yomzali engenanto "yentloko" yabhengezwa, echaza konke izalathisi eziyimfuneko kunye nezibangela.
- Irekhodi elivela kwindawo yokujonga umxhasi yenziwe kwitheyibhile "yengcambu", kunye nokusetyenziswa kwangaphakathi umqalisi wendlela
BEFORE INSERTirekhodi yafakwa "ngokomzimba" kwicandelo elifunekayo. Ukuba bekungekabikho nto injalo, sibambe okwahlukileyo kwaye... - ... ngokusebenzisa yenziwe ngokusekwe kwithempleyithi yetheyibhile yomzali icandelo elinomda womhla ofunekayoukuze xa idatha ifunyenwe, ukufundwa kwenziwa kuyo kuphela.
PG10: umzamo wokuqala
Kodwa ukwahlula ngelifa bekungafanelekanga ngokwembali ukujongana nomjelo wokubhala osebenzayo okanye inani elikhulu lezahlulo zabantwana. Umzekelo, unokukhumbula ukuba i-algorithm yokukhetha icandelo elifunekayo ubunzima bequadratic, ukuba isebenza ngamacandelo angama-100+, wena ngokwakho uyayiqonda indlela...
Kwi-PG10 le meko yaphuculwa kakhulu ngokuphunyezwa kwenkxaso . Ke ngoko, sazama ukuyisebenzisa kwangoko emva kokufuduka kwindawo yokugcina, kodwa...
Njengoko kuye kwavela emva kokugrumba incwadi, itafile eyahlulahlulwe ngokwemveli kule nguqulelo yile:
- ayixhasi iinkcazelo zesalathiso
- ayixhasi izichukumisi kuyo
- ayinakuba "yinzala" kabani
- musa ukuxhasa
INSERT ... ON CONFLICT - ayinakwenza icandelo ngokuzenzekelayo
Ekubeni sasifumene isibetho esibuhlungu ebunzi ngeharika, saqonda ukuba kwakungenakwenzeka ukwenza ngaphandle kokuguqula isicelo, yaye saluhlehlisa olunye uphando kangangeenyanga ezintandathu.
PG10: ithuba lesibini
Ke, saqala ukusombulula iingxaki eziye zavela nganye nganye:
- Ngenxa yokuba izinto ezibangela kunye
ON CONFLICTSafumanisa ukuba sisazidinga apha naphaya, ngoko ke senza iqonga eliphakathi ukuze sizilungise itafile yommeli. - Yahlukana "nendlela" kwizichukumisi - oko kukuthi, ukusuka
EXECUTE. - Bayikhupha ngokwahlukeneyo itheyibhile yetemplate enazo zonke izalathiukuze bangabikho nakwitafile yommeli.
Ekugqibeleni, emva kwayo yonke le nto, sahlulahlula itafile ephambili ngokwemveli. Ukwenziwa kwecandelo elitsha kusashiywe kwisazela somfaki-sicelo.
"Sawing" izichazi-magama
Njengakuyo nayiphi na inkqubo yohlalutyo, nathi sasinayo "iinyani" kunye "nokusikwa" (izichazi-magama). Kwimeko yethu, kwesi sikhundla benza, umzekelo, imibuzo ecothayo efanayo okanye okubhaliweyo kombuzo ngokwawo.
"Iinyani" zahlulwa emini ixesha elide, ngoko ke sacima ngokuzolileyo amacandelo aphelelwe lixesha, kwaye awazange asikhathaze (iilogi!). Kodwa bekukho ingxaki kwizichazi-magama...
Andithethi ukuba zininzi, kodwa malunga I-100TB "yeenyani" ibangele isichazi-magama se-2.5TB. Awukwazi ukucima nantoni na kwitafile enjalo, awukwazi ukuyicinezela ngexesha elifanelekileyo, kwaye ukuyibhalela ngokuthe ngcembe kuye kwacotha.
Njengesichazi-magama... kuso, ungeno ngalunye kufuneka lunikezelwe kanye kanye... kwaye oku kuchanekile, kodwa!.. Akukho mntu usinqandayo ekubeni sibe nalo. isichazi-magama esahlukileyo sosuku ngalunye! Ewe, oku kuzisa ukungafuneki okuthile, kodwa kuvumela:
- bhala/funda ngokukhawuleza ngenxa yobungakanani becandelo elincinci
- sebenzisa imemori encinci ngokusebenza ngezalathisi ezixineneyo
- gcina idatha encinci ngenxa yokukwazi ukususa ngokukhawuleza ixesha
Ngenxa yobunzima bamanyathelo Umthwalo we-CPU wehle nge ~ 30%, umthwalo wedisk nge ~ 50%:
Ngexesha elifanayo, saqhubeka sibhala into efanayo kwi-database, kunye nomthwalo omncinci.
#2. Ukuzivelela kweDatabase kunye nohlengahlengiso
Sazinza ke kule nto sinayo usuku ngalunye lunecandelo lalo ngedatha. Ngokwenene, CHECK (dt = '2018-10-12'::date) - kwaye kukho isitshixo sokwahlula kunye nemeko yokuba irekhodi iwele kwicandelo elithile.
Kuba zonke iingxelo ezikwinkonzo yethu zakhiwe kumxholo womhla othile, izalathisi zazo ukusukela “ngamaxesha angahlulwahlulwayo” ibe zizo zonke iintlobo. (Iseva, Umhla, iSakhelo sesicwangciso), (Iseva, Umhla, indawo yokucwangcisa), (Umhla, Udidi lwemposiso, iseva)...
Kodwa ngoku bahlala kuwo onke amacandelo iikopi zakho isalathiso ngasinye esinjalo... Kwaye ngaphakathi kwecandelo ngalinye umhla uhleli... Kuvela ukuba ngoku sikwisalathiso ngasinye esinjalo ngena ngokulula njengenye yemimandla, eyandisa ivolumu yayo kunye nexesha lokukhangela kuyo, kodwa ayizisi nayiphi na isiphumo. Bazishiyele iraka, yho...
Isalathiso sokuphucula sicacile - silula susa umhlaba womhla kuzo zonke izalathisi kwiitafile ezahlulwahlulwayo. Ngokunikwa imiqulu yethu, inzuzo imalunga 1TB/iveki!
Ngoku masiqaphele ukuba le terabyte kusafuneka irekhodwe ngandlela thile. Oko kukuthi, nathi idiski ngoku kufuneka ilayishe kancinci! Lo mfanekiso ubonisa ngokucacileyo umphumo ofunyenwe ekucoceni, esizinikele kuyo ngeveki:
#3. "Ukusasaza" umthwalo ophakamileyo
Enye yeengxaki ezinkulu kwiinkqubo ezilayishiweyo ungqamaniso olungafunekiyo eminye imisebenzi engayifuniyo. Ngamanye amaxesha "ngenxa yokuba abazange baqaphele", ngamanye amaxesha "kwakulula ngaloo ndlela", kodwa ngokukhawuleza okanye kamva kufuneka uyilahle.
Masisondele kumfanekiso odlulileyo kwaye sibone ukuba sinediski "iipompo" phantsi komthwalo kunye ne-double amplitude phakathi kweesampulu ezikufutshane, ngokucacileyo "ngokwezibalo" akufuneki zenzeke ngenani lemisebenzi:
Oku kulula kakhulu ukufikelela. Sele siqalile ukubeka iliso phantse 1000 abancedisi, nganye iqhutywe ngumsonto wengqiqo owahlukileyo, kwaye umsonto ngamnye useta kwakhona ulwazi oluqokelelweyo ukuba luthunyelwe kwisiseko sedatha ngamaxesha athile, into enje:
setInterval(sendToDB, interval)Ingxaki apha ilele kanye kwinto yokuba yonke imisonto iqala malunga nexesha elinye, ngoko amaxesha abo okuthumela phantse asoloko engqamana “nakwingongoma.” Yhu #2...
Ngethamsanqa, oku kulula kakhulu ukukulungisa, Ukongeza "ngokungaqhelekanga" ukuqhubela phambili ngexesha:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Sigcina into esiyidingayo
Ingxaki yesithathu yemveli ephezulu akukho cache apho akhoyo unakho kuba.
Umzekelo, senze ukuba sikwazi ukuhlalutya ngokwemigaqo yeendawo zokuhlala (zonke ezi Seq Scan on users), kodwa ngokukhawuleza bacinge ukuba, ubukhulu becala, bayafana - balibele.
Hayi, ngokuqinisekileyo, akukho nto ibhaliweyo kwi-database kwakhona, oku kunqumla i-trigger nge INSERT ... ON CONFLICT DO NOTHING. Kodwa le datha isafikelela kwisiseko sedatha, kwaye akuyomfuneko ukufunda ukujonga impixano kufuneka wenze. Yhu #3...
Umahluko kwinani leerekhodi ezithunyelwe kwisiseko sedatha phambi/emva kokuba i-caching yenziwe yasebenza icacile:
Kwaye oku kukwehla okukhaphayo komthwalo wokugcina:
Iyonke
"I-Terabyte-ngosuku" ivakala nje isoyikisa. Ukuba wenza yonke into ngokufanelekileyo, ngoko oku kulungile 2^40 bytes / 86400 imizuzwana = ~ 12.5MB/sukuba nezikrufu ze-IDE zedesktop zibanjwe. 🙂
Kodwa ngokunzulu, nangona "skew" yomthwalo ophindwe kashumi emini, unokuhlangabezana ngokulula nobuchule bee-SSD zanamhlanje.
umthombo: www.habr.com