Pulogalamu ya ProHoster > Blog > Ulamuliro > Nkhani Yakuchotsedwa Kwakuthupi Kwa Ma Records 300 Miliyoni mu MySQL

Nkhani Yakuchotsedwa Kwakuthupi Kwa Ma Records 300 Miliyoni mu MySQL

Mau oyamba

Moni. Ndine ningenMe, wopanga masamba.

Monga mutu umanenera, nkhani yanga ndi nkhani yochotsa ma 300 miliyoni mu MySQL.

Ndinachita chidwi ndi izi, choncho ndinaganiza zopanga chikumbutso (malangizo).

Kunyumba - Chidziwitso

Seva ya batch yomwe ndimagwiritsa ntchito ndikusunga imakhala ndi njira yokhazikika yomwe imasonkhanitsa deta ya mwezi watha kuchokera ku MySQL kamodzi patsiku.

Nthawi zambiri izi zimatha mkati mwa ola limodzi, koma nthawi ino sizinathe kwa maola 1 kapena 7, ndipo chenjezo silinasiye kutuluka ...

Kufufuza chifukwa

Ndinayesera kuyambiranso ndondomekoyi ndikuyang'ana zipika, koma sindinawone cholakwika.
Funso linalembedwa molondola. Koma nditaganizira zomwe zikulakwika, ndinazindikira kuti kukula kwa database ndikwambiri. 

hoge_table | 350'000'000 |

350 miliyoni zolemba. Indexing inkawoneka ikugwira ntchito moyenera, pang'onopang'ono.

Zosonkhanitsira deta zofunika pamwezi zinali pafupifupi 12 zolemba. Zikuwoneka kuti lamulo losankhidwa lidatenga nthawi yayitali ndipo ntchitoyo sinachitike kwa nthawi yayitali.

DB

Ndi tebulo lomwe limakula ndi zolembera pafupifupi 400 tsiku lililonse. Dongosololi limayenera kusonkhanitsa deta kwa mwezi watha, choncho, zinkayembekezeredwa kuti zidzapirire ndendende kuchuluka kwa deta iyi, koma, mwatsoka, ntchito yozungulira sinaphatikizidwe.

Database iyi sinapangidwe ndi ine. Ndinazitenga kuchokera kwa wopanga wina, kotero zimamvekabe ngati ngongole yaukadaulo.

Panafika nthawi yomwe kuchuluka kwa data yomwe idayikidwa tsiku lililonse idakhala yayikulu ndipo pamapeto pake idafikira malire. Zimaganiziridwa kuti pogwira ntchito ndi chiwerengero chachikulu choterechi, zingakhale zofunikira kuwalekanitsa, koma izi, mwatsoka, sizinachitike.

Ndiyeno ndinayamba kuchitapo kanthu.

Kukonza

Zinali zomveka kuchepetsa kukula kwa database yokha ndikuchepetsa nthawi yokonza kusiyana ndi kusintha malingaliro ake.

Mkhalidwe uyenera kusintha kwambiri ngati mutachotsa zolemba za 300 miliyoni, kotero ndinaganiza kutero ... Eh, ndinaganiza kuti izi zidzagwira ntchito.

Ntchito 1

Nditakonza zosunga zodalirika, pomaliza pake ndinayamba kutumiza zopempha.

"Kutumiza pempho"

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

"..."

"..."

β€œHmm... Palibe yankho. Mwina ntchitoyi imatenga nthawi yayitali?" - Ndinaganiza, koma pokhapokha, ndinayang'ana pa grafana ndikuwona kuti katundu wa disk akukula mofulumira kwambiri.
β€œZowopsa,” ndinalingaliranso ndipo nthaΕ΅i yomweyo ndinasiya pempholo.

Ntchito 2

Nditasanthula zonse, ndinazindikira kuti kuchuluka kwa data kunali kokulirapo kuti ndichotse chilichonse nthawi imodzi.

Ndinaganiza zolemba zolemba zomwe zingathe kuchotsa zolemba pafupifupi 1 ndikuziyambitsa.

"Ndimagwiritsa ntchito script"

β€œTsopano izi zigwiradi ntchito,” ndinalingalira motero.

Ntchito 3

Njira yachiwiri inagwira ntchito, koma inakhala yovuta kwambiri.
Kuchita zonse mosamala, popanda mitsempha yosafunika, zingatenge pafupifupi milungu iwiri. Komabe, chochitikachi sichinafikire zofunika zautumiki, chotero tinayenera kuchisiya.

Ndiye izi ndi zomwe ndinaganiza kuchita:

Koperani tebulo ndikulitcha dzina

Kuchokera pa sitepe yapitayi, ndinazindikira kuti kuchotsa kuchuluka kwa deta kumapanga katundu wofanana. Chifukwa chake ndidaganiza zopanga tebulo latsopano kuchokera poyambira pogwiritsa ntchito kuyika ndikusuntha zomwe nditi ndichotsemo.

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

Ngati mupanga tebulo latsopano kukula kofanana ndi pamwambapa, kuthamanga kwa data kuyeneranso kukhala 1/7 mwachangu.

Nditapanga tebulo ndikulisinthanso, ndidayamba kugwiritsa ntchito ngati tebulo lalikulu. Tsopano ngati ndigwetsa tebulo ndi zolemba 300 miliyoni zonse ziyenera kukhala bwino.
Ndinapeza kuti truncate kapena drop imapanga zochepa kuposa kuchotsa ndipo ndinaganiza zogwiritsa ntchito njirayi.

Kuphedwa

"Kutumiza pempho"

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

"..."
"..."
"Emwe...?"

Ntchito 4

Ndinaganiza kuti lingaliro lapitalo lidzagwira ntchito, koma nditatha kutumiza pempho loyikapo, zolakwika zambiri zidawonekera. MySQL sindikukhululuka.

Ndinatopa kale moti ndinayamba kuganiza kuti sindikufunanso kuchita zimenezi.

Ndinakhala ndikulingalira ndipo ndinazindikira kuti mwina panali mafunso ambiri oyika nthawi imodzi...
Ndinayesa kutumiza pempho loyika za kuchuluka kwa deta yomwe malo osungirako zinthu ayenera kusinthidwa tsiku limodzi. Zachitika!

Chabwino, pambuyo pake timapitiriza kutumiza zopempha za kuchuluka kwa deta. Popeza tifunika kuchotsa deta ya mwezi umodzi, timabwereza ntchitoyi pafupifupi maulendo 35.

Kutchulanso tebulo

Apa mwayi unali kumbali yanga: zonse zidayenda bwino.

Chenjezo chasowa

Kuthamanga kwa batch processing chawonjezeka.

M'mbuyomu izi zidatenga pafupifupi ola limodzi, tsopano zimatenga pafupifupi mphindi ziwiri.

Nditatsimikiza kuti mavuto onse atha, ndinagwetsa ma 300 miliyoni. Ndinachotsa tebulo ndikumva kubadwanso.

Chidule

Ndinazindikira kuti kukonza kasinthasintha kunalibe pakukonza batch, ndipo ndilo linali vuto lalikulu. Zolakwika zamtundu woterezi zimatsogolera pakuwononga nthawi.

Kodi mumaganizira za katunduyo panthawi yobwereza deta pamene mukuchotsa zolemba kuchokera ku database? Tiyeni tisachulukitse MySQL.

Iwo omwe amadziwa bwino zamasamba sangakumane ndi vuto lotere. Kwa inu nonse, ndikukhulupirira kuti nkhaniyi inali yothandiza.

Zikomo powerenga!

Tidzasangalala kwambiri ngati mutatiuza ngati munakonda nkhaniyi, kaya kumasulira kwake ndi komveka, kaya kunali kothandiza kwa inu?

Source: www.habr.com

Kuwonjezera ndemanga