Iyo nyaya yekudzima 300 miriyoni marekodhi muMySQL

Nhanganyaya

Mhoro. Ndiri ningenMe, mugadziri wewebhu.

Sekutaura kunoita zita, nyaya yangu inyaya yekudzima 300 miriyoni marekodhi muMySQL.

Ndakatanga kufarira izvi, saka ndakasarudza kuita chiyeuchidzo (mirayiridzo).

Musha - Chenjerera

Iyo batch server yandinoshandisa nekuchengetedza ine yakajairika maitiro anounganidza data remwedzi wapfuura kubva kuMySQL kamwe chete pazuva.

Kazhinji maitiro aya anopedzwa mukati meawa imwe chete, asi panguva ino haina kupera kwemaawa manomwe kana masere, uye yambiro haina kumira kubuda ...

Kutsvaga chikonzero

Ndakaedza kutangazve maitiro uye kutarisa matanda, asi handina kuona chakaipa.
Mubvunzo wakanyorwa nemazvo. Asi pandakafunga kuti chii chaiitika, ndakaona kuti saizi yedatabase yakakura.

hoge_table | 350'000'000 |

350 mamiriyoni ezvinyorwa. Indexing yaiita kunge iri kushanda nemazvo, zvishoma nezvishoma.

Kuunganidzwa kwedata kwaidiwa pamwedzi kwanga kuri marekodhi gumi nemaviri. Zvinoita sekuti sarudzo yakatora nguva yakareba uye kutengeserana hakuna kuitwa kwenguva yakareba.

DB

Yakanyanya tafura inokura neinosvika mazana mana ezviuru ekupinda zuva rega rega. Iyo dhatabhesi yaifanirwa kuunganidza data chete kumwedzi wapfuura, saka, yaitarisirwa kuti yaizomira chaizvo iyi huwandu hwe data, asi, zvinosuruvarisa, kutenderera kushanda hakuna kubatanidzwa.

Database iri handina kugadzirwa neni. Ndakaitora kubva kune mumwe mugadziri, saka yaingonzwa sechikwereti chehunyanzvi.

Pakasvika nguva apo huwandu hwe data yakaiswa zuva nezuva yakava yakakura uye yakazosvika painogumira. Zvinofungidzirwa kuti kana uchishanda nehuwandu hwakawanda hwe data, zvingave zvakakosha kuvaparadzanisa, asi izvi, zvinosuruvarisa, hazvina kuitwa.

Zvino ndakabva ndapinda muchiito.

Kugadziriswa

Zvaive zvine musoro kuderedza saizi yedatabase pachayo uye kuderedza nguva yekuigadzirisa pane kushandura iyo logic pachayo.

Mamiriro ezvinhu anofanira kuchinja zvakanyanya kana iwe ukadzima mamiriyoni e300 zvinyorwa, saka ndakasarudza kuita saizvozvo ... Eh, ndakafunga kuti izvi zvaizoshanda zvechokwadi.

Chiito 1

Sezvo ndagadzirira backup yakavimbika, ndakazotanga kutumira zvikumbiro.

"Kutumira chikumbiro"

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

"..."

"..."

“Hmm... Hapana mhinduro. Pamwe maitiro acho anotora nguva yakareba?" - Ndakafunga, asi kana zvikaitika, ndakatarisa grafana uye ndakaona kuti dhisiki mutoro waikura nokukurumidza.
“Dangerous,” ndakafunga zvakare uye ndakabva ndarega chikumbiro chacho.

Chiito 2

Mushure mekuongorora zvese, ndakaona kuti huwandu hwe data hwakanga hwakakurisa kudzima zvese kamwechete.

Ndakafunga kunyora script inogona kudzima nezve 1 rekodhi uye ndakaivhura.

"Ndinoshandisa script"

“Zvino izvi zvichanyatsoshanda,” ndakafunga kudaro.

Chiito 3

Yechipiri nzira yakashanda, asi yakazova yekushanda zvakanyanya.
Kuita zvese nemazvo, pasina tsinga dzisina basa, zvinotora anenge mavhiki maviri. Asi zvakadaro, chiitiko ichi hachina kuzadzisa zvinodiwa zvebasa, saka taifanira kubva pachiri.

Saka hezvino zvandakasarudza kuita:

Kopa tafura woitumidza zita

Kubva padanho rakapfuura, ndakaona kuti kudzima huwandu hwakakura hwe data kunogadzira mutoro wakakura zvakaenzana. Saka ndakafunga kugadzira tafura nyowani kubva kutanga ndichishandisa kuisa uye kufambisa iyo data yandaizobvisa mairi.

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

Kana iwe ukaita iyo tafura nyowani saizi yakafanana nepamusoro, iyo data yekumhanyisa data inofanirawo kuva 1/7 nekukurumidza.

Mushure mekugadzira tafura uye nekuitumidza zita, ndakatanga kuishandisa setafura yepamusoro. Zvino kana ndikadonhedza tafura ine 300 miriyoni zvinyorwa zvese zvinofanirwa kunge zvakanaka.
Ndakaona kuti truncate kana kudonha kunogadzira kushoma pamusoro pane kudzima uye ndakafunga kushandisa nzira iyi.

Performance

"Kutumira chikumbiro"

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

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

Chiito 4

Ndakafunga kuti zano rekare raizoshanda, asi mushure mekutumira chikumbiro chekuisa, zvikanganiso zvakawanda zvakaonekwa. MySQL haina kuregerera.

Ndakanga ndatoneta zvekuti ndakatanga kufunga kuti handichadi kuita izvi.

Ndakagara ndichifunga ndikaona kuti pamwe painge pawandisa ma queries for one time...
Ndakaedza kutumira chikumbiro chekuisa yehuwandu hwe data iyo dhatabhesi inofanirwa kugadzirisa muzuva re1. Zvakaitika!

Zvakanaka, mushure mezvo tinoenderera mberi nekutumira zvikumbiro zvehuwandu hwe data. Sezvo tichida kubvisa data yemwedzi, tinodzokorora kushanda uku kanenge ka35.

Kupa zita retafura

Apa mhanza yaive parutivi rwangu: zvese zvakafamba mushe.

Chenjedzo yashaikwa

Batch processing speed yawedzera.

Kare izvi zvaitora inenge awa, zvino zvinotora anenge maminitsi maviri.

Mushure mekunge ndave nechokwadi chekuti matambudziko ese agadziriswa, ndakadonhedza 300 miriyoni zvinyorwa. Ndakabvisa table ndikanzwa ndazvarwa patsva.

Summary

Ndakaona kuti kutenderera kugadzirisa kwaive kusipo mukugadzirisa batch, uye ndiro raive dambudziko guru. Rudzi urwu rwechikanganiso chekuvaka chinotungamira kutambisa nguva.

Iwe unofunga nezve mutoro panguva yekudzokorodza data paunenge uchidzima marekodhi kubva kudhatabhesi? Ngatirege kuwandisa MySQL.

Avo vanonyatsoziva mudhatabhesi havazosangana nedambudziko rakadaro. Kune vamwe venyu, ndinovimba kuti chinyorwa ichi chakabatsira.

Ndatenda nekuverenga!

Tichafara zvikuru kana iwe ukatiudza kana iwe wakafarira chinyorwa ichi, kana shanduro yacho yakajeka, kana yakakubatsira iwe?

Source: www.habr.com