Ibali lokucinywa koMzimba kweeRekhodi ze-300 yezigidi kwi-MySQL

Intshayelelo

Mholo. NdinguNingenMe, umakhi wewebhu.

Njengoko isihloko sithi, ibali lam libali lokucima ngokomzimba iirekhodi ze-300 yezigidi kwi-MySQL.

Ndaba nomdla koku, ngoko ke ndagqiba ekubeni ndenze isikhumbuzo (imiyalelo).

Ikhaya-Isilumkiso

Iseva yebhetshi endiyisebenzisayo kwaye ndiyigcinayo inenkqubo eqhelekileyo eqokelela idatha yenyanga yokugqibela ukusuka kwi-MySQL kanye ngosuku.

Ngokuqhelekileyo le nkqubo igqitywe malunga neyure enye, kodwa ngeli xesha ayizange igqibe iiyure ezisi-1 okanye ezi-7, kwaye isilumkiso asizange siyeke ukuvela ...

Ukufumana isizathu

Ndazama ukuqalisa kwakhona inkqubo kwaye ndijonge izigodo, kodwa andizange ndibone into engalunganga.
Umbuzo ubhalwe ngokuchanekileyo. Kodwa ndathi ndakucinga ngento eyayingahambi kakuhle, ndiye ndaqonda ukuba ubungakanani bedatabase bukhulu kakhulu.

hoge_table | 350'000'000 |

350 yezigidi zeerekhodi. Ukwalathisa kubonakale kusebenza ngokuchanekileyo, kucotha kakhulu.

Ukuqokelelwa kwedatha efunekayo ngenyanga ibimalunga neerekhodi ezili-12. Kubonakala ngathi umyalelo okhethiweyo uthathe ixesha elide kwaye intengiselwano ayizange iqhutywe ixesha elide.

DB

Yitafile ekhula malunga nama-400 amangeno yonke imihla. I-database bekufanele iqokelele idatha kuphela kwinyanga edlulileyo, ngoko ke, bekulindeleke ukuba imele ngokuchanekileyo le mali yedatha, kodwa, ngelishwa, umsebenzi wokujikeleza awuzange ufakwe.

Le database ayizange iphuhliswe ndim. Ndiyithathele komnye umphuhlisi, ngoko ke ibivakala ngathi lityala lobugcisa.

Kwafika inqaku xa umthamo wedatha efakwe imihla ngemihla yaba nkulu kwaye ekugqibeleni yafikelela kumda wayo. Kucingelwa ukuba xa usebenza ngenani elikhulu kangaka ledatha, kuya kufuneka ukubahlula, kodwa oku, ngelishwa, akuzange kwenziwe.

Ndaye ndangena esenzweni.

Ukulungiswa

Kwakunengqiqo ngakumbi ukunciphisa ubungakanani bedatha ngokwayo kunye nokunciphisa ixesha lokuyilungisa kunokutshintsha ingqiqo ngokwayo.

Imeko kufuneka itshintshe kakhulu ukuba ucima iirekhodi zezigidi ezingama-300, ngoko ke ndagqiba ekubeni ndenze njalo ... Ewe, ndacinga ukuba oku kuya kusebenza ngokuqinisekileyo.

Isenzo 1

Emva kokuba ndilungiselele i-backup ethembekileyo, ekugqibeleni ndaqala ukuthumela izicelo.

"Ukuthumela isicelo"

DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';

"..."

"..."

“Hmm... Akukho mpendulo. Mhlawumbi le nkqubo ithatha ixesha elide?” - Ndacinga, kodwa xa kwenzeka, ndajonga i-grafana kwaye ndabona ukuba umthwalo wediski wawukhula ngokukhawuleza.
“Iyingozi,” ndacinga kwakhona ndaza ngoko nangoko ndasiyeka eso sicelo.

Isenzo 2

Emva kokuhlalutya yonke into, ndaqonda ukuba umthamo wedatha wawumkhulu kakhulu ukucima yonke into kanye.

Ndagqiba ekubeni ndibhale iskripthi esinokucima malunga neerekhodi ze-1 kwaye ndiqalise.

「Ndisebenzisa iscript」

“Ngoku oku kuya kusebenza ngokuqinisekileyo,” ndacinga njalo.

Isenzo 3

Indlela yesibini yasebenza, kodwa yabonakala ifuna umsebenzi onzima.
Ukwenza yonke into ngononophelo, ngaphandle kwemithambo-luvo engeyomfuneko, kuya kuthatha malunga neeveki ezimbini. Kodwa sekunjalo, le meko yayingafikeleli kwiimfuneko zenkonzo, ngoko kwafuneka siyishiye.

Nantsi into endigqibe ukuyenza:

Khuphela itheyibhile kwaye uyinike elinye igama

Ukususela kwisinyathelo sangaphambili, ndaqonda ukuba ukucima inani elikhulu ledatha kudala umthwalo omkhulu ngokulinganayo. Ndiye ndagqiba ekubeni ndenze itafile entsha ukusuka ekuqaleni ndisebenzisa i-insert kwaye ndihambise idatha endizakuyicima kuyo.

| hoge_table     | 350'000'000|
| tmp_hoge_table |  50'000'000|

Ukuba wenza itheyibhile entsha ilingane nalapha ngasentla, isantya sokucwangcisa idatha kufuneka sibe yi-1/7 ngokukhawuleza.

Emva kokudala itafile kwaye ndiyithiye ngokutsha, ndaqala ukuyisebenzisa njengetafile enkulu. Ngoku ukuba ndiwisa itafile ngeerekhodi ezizigidi ezingama-300 yonke into kufuneka ilungile.
Ndifumanise ukuba i-truncate okanye i-drop idala i-overhead encinci kunokucima kwaye ndagqiba ekubeni ndisebenzise le ndlela.

Intsebenzo

"Ukuthumela isicelo"

INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';

"..."
"..."
"Em...?"

Isenzo 4

Ndacinga ukuba umbono wangaphambili uza kusebenza, kodwa emva kokuthumela isicelo sokufaka, kwavela iimpazamo ezininzi. I-MySQL ayixoleli.

Ndandisele ndidiniwe kangangokuba ndaqalisa ukucinga ukuba andisafuni ukuyenza le nto.

Ndahlala ndacinga ndaqonda inoba mininzi insert queries for one time...
Ndizame ukuthumela isicelo sokufaka inani ledatha ekufuneka i-database iqhubeke ngosuku olu-1. Yenzekile!

Ewe, emva koko siyaqhubeka nokuthumela izicelo zesixa esifanayo sedatha. Kuba kufuneka sisuse idatha yenyanga, siphinda lo msebenzi malunga namaxesha angama-35.

Ukuthiya ngokutsha itafile

Nantsi ithamsanqa lalisecaleni lam: yonke into yahamba kakuhle.

Isivuseleli silahlekile

Isantya sokwenziwa kwebhetshi sinyukile.

Ngaphambili le nkqubo ithatha malunga neyure, ngoku ithatha malunga nemizuzu emi-2.

Emva kokuba ndiqinisekile ukuba zonke iingxaki zisonjululwe, ndalahla iirekhodi ze-300 yezigidi. Ndicime itafile ndaziva ndizelwe ngokutsha.

Isishwankathelo

Ndaye ndaqaphela ukuba ukujikeleza ukujikeleza kwakungekho kwi-batch processing, kwaye yayiyeyona ngxaki iphambili. Olu hlobo lwempazamo yoyilo lukhokelela kwinkcitho yexesha.

Ngaba ucinga ngomthwalo ngexesha lokuphindaphinda idatha xa ucima iirekhodi kwisiseko sedatha? Masingalayishi kakhulu iMySQL.

Abo bazi kakuhle kugcino-lwazi ngokuqinisekileyo abayi kuhlangabezana nengxaki enjalo. Kuni nonke, ndiyathemba ukuba eli nqaku beliluncedo.

Enkosi ngokufunda!

Siya kuvuya kakhulu ukuba usixelele ukuba ulithandile eli nqaku, nokuba inguqulelo icacile, nokuba ibiluncedo kuwe?

umthombo: www.habr.com