{"id":95518,"date":"2020-09-30T19:42:27","date_gmt":"2020-09-30T17:42:27","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql"},"modified":"2020-09-30T19:42:27","modified_gmt":"2020-09-30T17:42:27","slug":"istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"Historia e fshirjes fizike t\u00eb 300 milion\u00eb regjistrimeve n\u00eb MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hyrje<\/h2>\n

\nP\u00ebrsh\u00ebndetje. Un\u00eb jam ningenMe, zhvillues web-i.<\/p>\n

Si\u00e7 e thot\u00eb titulli, historia ime \u00ebsht\u00eb nj\u00eb histori mbi fshirjen fizike t\u00eb 300 milion\u00eb rekord\u00ebve n\u00eb MySQL.<\/p>\n

Isha i interesuar p\u00ebr k\u00ebt\u00eb, k\u00ebshtu q\u00eb vendosa t\u00eb b\u00ebj nj\u00eb sh\u00ebnim (udh\u00ebzues).<\/p>\n

Fillimi \u2014 Alert<\/h2>\n

\nN\u00eb paket\u00ebn serveri<\/a>, e cila p\u00ebrdorim dhe mbaj, ka nj\u00eb proces t\u00eb rregullt q\u00eb nj\u00eb her\u00eb n\u00eb dit\u00eb mbledh t\u00eb dh\u00ebnat e muajit t\u00eb fundit nga MySQL. <\/p>\n

Zakonisht, ky proces p\u00ebrfundon rreth 1 ore, por k\u00ebt\u00eb her\u00eb nuk p\u00ebrfundonte p\u00ebr 7 ose 8 or\u00eb, dhe alert nuk ndalej... <\/a><\/noindex><\/p>\n

Gjetja e shkakut<\/h2>\n

\nProvoja t\u00eb rinis procesin, t\u00eb shikoj log-et, por nuk pash\u00eb asgj\u00eb t\u00eb keqe.
\nK\u00ebrkesa ishte e indeksuar si\u00e7 duhet. Por kur mendoja se \u00e7far\u00eb po shkonte keq, kuptova se volumi i DB ishte mjaft i madh. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 milion\u00eb rekord\u00eb. Duket se indeksimi po punonte si\u00e7 duhet, thjesht shum\u00eb ngadal\u00eb.<\/p>\n
Mbledhja e t\u00eb dh\u00ebnave p\u00ebr muajin ishte rreth 12,000,000 rekord\u00eb. Duket se ekipi select mori shum\u00eb koh\u00eb, dhe transaksioni nuk p\u00ebrfundonte p\u00ebr nj\u00eb periudh\u00eb t\u00eb gjat\u00eb. <\/p>\n
DB<\/h2>\n
\nN\u00eb thelb, kjo \u00ebsht\u00eb nj\u00eb tabel\u00eb q\u00eb rritet \u00e7do dit\u00eb me rreth 400,000 rekord\u00eb. Baza duhej t\u00eb mbledh\u00eb t\u00eb dh\u00ebnat vet\u00ebm p\u00ebr muajin e fundit, prandaj llogaritja ishte p\u00ebr k\u00ebt\u00eb volum t\u00eb dh\u00ebnash, por, p\u00ebr fat t\u00eb keq, operacioni rotate nuk ishte aktivizuar.<\/p>\n
Kjo baz\u00eb t\u00eb dh\u00ebnash nuk u zhvillua nga un\u00eb. E mora nga nj\u00eb zhvillues tjet\u00ebr, prandaj ka nj\u00eb ndjenj\u00eb t\u00eb borxhit teknik. <\/p>\n
Arriti nj\u00eb moment kur volumi i t\u00eb dh\u00ebnave t\u00eb futur ditor u b\u00eb i madh dhe m\u00eb n\u00eb fund arriti kufirin. Supozohen q\u00eb, duke punuar me nj\u00eb volum kaq t\u00eb madh t\u00eb dh\u00ebnash, do t\u00eb duhej t'i ndaheshin, por p\u00ebr fat t\u00eb keq, kjo nuk u b\u00eb.<\/p>\n
Dhe k\u00ebtu hyra un\u00eb.<\/p>\n
Rregullimi<\/h2>\n
\nIshin m\u00eb racional t\u00eb zvog\u00eblohej vet\u00eb DB dhe t\u00eb shkurtohej koha e saj e p\u00ebrpunimit, sesa t\u00eb ndryshohej logjika e saj.<\/p>\n
Situata duhet t\u00eb ndryshoj\u00eb ndjesh\u00ebm, n\u00ebse fshihen 300 milion\u00eb rekord\u00eb, k\u00ebshtu q\u00eb vendosa ta b\u00ebj at\u00eb... Eh, mendoja se kjo do t\u00eb funksiononte.<\/p>\n
Veprimi 1<\/h2>\n
\nPas p\u00ebrgatitjes s\u00eb nj\u00eb kopje rezerv\u00eb t\u00eb besueshme, p\u00ebrfundimisht fillova t\u00eb d\u00ebrgoj k\u00ebrkesa.<\/p>\n
\u300cD\u00ebrgimi i k\u00ebrkes\u00ebs\u300d<\/p>\n
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u300c…\u300d<\/p>\n
\u300c…\u300d<\/p>\n
\u201cHmm... S'ka p\u00ebrgjigje. Ndoshta procesi merr shum\u00eb koh\u00eb?\u201d \u2014 mendova, por p\u00ebr \u00e7do rast shikova n\u00eb grafana dhe pash\u00eb se ngarkesa e diskut po rritej shum\u00eb shpejt. 
\n\u201cRreziksh\u00ebm\u201d \u2014 mendova prap\u00eb dhe menj\u00ebher\u00eb ndalova k\u00ebrkes\u00ebn.<\/p>\n
Veprimi 2<\/h2>\n
\nPas analizimit t\u00eb gjith\u00e7kaje, kuptova se volumi i t\u00eb dh\u00ebnave ishte shum\u00eb i madh p\u00ebr ta fshir\u00eb t\u00eb gjith\u00eb nj\u00eb her\u00eb.<\/p>\n
Vendosa t\u00eb shkruaj nj\u00eb skenar q\u00eb mund t\u00eb fshij\u00eb rreth 1,000,000 rekord\u00eb dhe e nisa at\u00eb.<\/p>\n
\u300cImplementoj skenarin\u300d<\/p>\n
\u201cTani do t\u00eb funksionoj\u00eb sigurisht,\u201d \u2014 mendova.<\/p>\n
Veprimi 3<\/h2>\n
\nMetoda e dyt\u00eb funksionoi, por u soll shum\u00eb pun\u00eb intensive.
\nP\u00ebr ta b\u00ebr\u00eb gjith\u00e7ka n\u00eb m\u00ebnyr\u00eb korrekte, pa nerva t\u00eb tep\u00ebrta, do t\u00eb nevojiteshin rreth dy jav\u00eb. Megjithat\u00eb, ky skenar nuk p\u00ebrputhej me k\u00ebrkesat e sh\u00ebrbimeve, k\u00ebshtu q\u00eb duhej ta lija m\u00ebnjan\u00eb.<\/p>\n
Prandaj, ja \u00e7far\u00eb vendosa t\u00eb b\u00ebj:<\/p>\n
Kopjoj tabel\u00ebn dhe e ribesoj<\/h3>\n
\nNga hapi i m\u00ebparsh\u00ebm kuptova se fshirja e nj\u00eb volumi kaq t\u00eb madh t\u00eb dh\u00ebnash krijon nj\u00eb ngarkes\u00eb t\u00eb till\u00eb t\u00eb madhe. Prandaj, vendosa t\u00eb krijoj nj\u00eb tabel\u00eb t\u00eb re nga zero me ndihm\u00ebn e insert dhe p\u00ebr t\u00eb transferuar t\u00eb dh\u00ebnat q\u00eb duhej t\u00eb fshiheshin.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nN\u00ebse e krijoj tabel\u00ebn e re me madh\u00ebsin\u00eb e m\u00ebsip\u00ebrme, shpejt\u00ebsia e p\u00ebrpunimit t\u00eb t\u00eb dh\u00ebnave gjithashtu duhet t\u00eb b\u00ebhet 1\/7 m\u00eb e shpejt\u00eb.<\/p>\n
Pasi krijova tabel\u00ebn dhe e ribesha, fillova ta p\u00ebrdor si tabel\u00ebn master. Tani, n\u00ebse fshij 300 milion\u00eb rekord\u00eb, gjith\u00e7ka duhet t\u00eb jet\u00eb n\u00eb rregull.
\nM\u00ebsoha se truncate ose drop krijojn\u00eb nj\u00eb ngarkes\u00eb m\u00eb t\u00eb vog\u00ebl sesa delete dhe vendosa t\u00eb p\u00ebrdor k\u00ebt\u00eb m\u00ebnyr\u00eb.<\/p>\n
Ekzekutimi<\/h3>\n
\n\u300cD\u00ebrgimi i k\u00ebrkes\u00ebs\u300d<\/p>\n
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u300c…\u300d
\n\u300c…\u300d
\n\u300c\u044d\u043c…\uff1f\u300d<\/p>\n
Veprimi 4<\/h2>\n
\nMendoja se ideja e m\u00ebparshme do t\u00eb funksiononte, por pas d\u00ebrgimit t\u00eb k\u00ebrkes\u00ebs insert u shfaq\u00ebn shum\u00eb gabime. MySQL nuk kursen.<\/p>\n
Isha aq i lodhur sa fillova t\u00eb mendoj se nuk do t\u00eb doja m\u00eb t\u00eb merrem me k\u00ebt\u00eb.<\/p>\n
Pas nj\u00eb kohe, mendova se ndoshta p\u00ebr nj\u00eb her\u00eb ishin shum\u00eb k\u00ebrkesa insert...
\nProvova t\u00eb d\u00ebrgoj nj\u00eb k\u00ebrkes\u00eb insert p\u00ebr volumin e t\u00eb dh\u00ebnave q\u00eb baza kishte p\u00ebr t\u00eb p\u00ebrpunuar p\u00ebr nj\u00eb dit\u00eb. Arriti!<\/p>\n
Pas k\u00ebsaj vazhdoj t\u00eb d\u00ebrgoj k\u00ebrkesa p\u00ebr t\u00eb nj\u00ebjtin volum t\u00eb t\u00eb dh\u00ebnave. Duke qen\u00eb se duhet t\u00eb largohet volumi mujor i t\u00eb dh\u00ebnave, e p\u00ebrs\u00ebris k\u00ebt\u00eb operacion rreth 35 her\u00eb.<\/p>\n
Ribeshimi i tabel\u00ebs <\/h3>\n
\nK\u00ebtu mu dha fati: gjith\u00e7ka shkoi pa probleme.<\/p>\n
Alertet u zhduk\u00ebn.<\/h3>\n
\nShpejt\u00ebsia e p\u00ebrpunimit t\u00eb paketave u rrit.<\/p>\n
M\u00eb par\u00eb ky proces zgjaste rreth nj\u00eb or\u00eb, tani zgjat rreth 2 minuta. <\/p>\n
Pasi u sigurova q\u00eb t\u00eb gjitha problemet ishin zgjidhur, e hodha posht\u00eb 300 milion\u00ebt e rekord\u00ebve. E hodha tabel\u00ebn dhe ndjeva se isha rilindur.<\/p>\n
P\u00ebrmbledhje<\/h2>\n
\nKam kuptova se gjat\u00eb p\u00ebrpunimit me grup, u humb p\u00ebrpunimi i rotacionit, dhe kjo ishte problemi kryesor. Nj\u00eb gabim i till\u00eb n\u00eb arkitektur\u00eb \u00e7on n\u00eb humbje t\u00eb kot\u00eb kohe. <\/p>\n
A mendoni p\u00ebr ngarkes\u00ebn gjat\u00eb replikimit t\u00eb t\u00eb dh\u00ebnave, kur fshini regjistrime nga baza? Le ta shmangim mbingarkes\u00ebn n\u00eb MySQL.<\/p>\n
Ata q\u00eb din\u00eb mir\u00eb p\u00ebr bazat e t\u00eb dh\u00ebnave nuk do t\u00eb hasin nj\u00eb problem t\u00eb till\u00eb. Nd\u00ebrsa p\u00ebr t\u00eb tjer\u00ebt, shpresoj se ky artikull ishte i dobish\u00ebm.<\/p>\n
Faleminderit p\u00ebr leximin!<\/p>\n
Do t\u00eb ishim shum\u00eb t\u00eb lumtur n\u00ebse na tregoni n\u00ebse ju p\u00eblqeu ky artikull, n\u00ebse p\u00ebrkthimi ishte i qart\u00eb, dhe n\u00ebse ishte i dobish\u00ebm p\u00ebr ju?<\/i>
\n
Burimi: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u041f\u0440\u0438\u0432\u0435\u0442. \u042f ningenMe, \u0432\u0435\u0431-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a. \u041a\u0430\u043a \u0441\u043a\u0430\u0437\u0430\u043d\u043e \u0432 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0438, \u043c\u043e\u044f \u0438\u0441\u0442\u043e\u0440\u0438\u044f \u2014 \u044d\u0442\u043e \u0438\u0441\u0442\u043e\u0440\u0438\u044f \u043e \u0444\u0438\u0437\u0438\u0447\u0435\u0441\u043a\u043e\u043c \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0438 300 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u043e\u0432 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 \u0432 MySQL. \u042f \u0437\u0430\u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043e\u0432\u0430\u043b\u0441\u044f \u044d\u0442\u0438\u043c, \u043f\u043e\u044d\u0442\u043e\u043c\u0443 \u0440\u0435\u0448\u0438\u043b \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043f\u0430\u043c\u044f\u0442\u043a\u0443 (\u0438\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044e). \u041d\u0430\u0447\u0430\u043b\u043e \u2014 Alert \u0412 \u043f\u0430\u043a\u0435\u0442\u043d\u043e\u043c \u0441\u0435\u0440\u0432\u0435\u0440\u0435, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044e \u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u044e, \u0438\u043c\u0435\u0435\u0442\u0441\u044f \u0440\u0435\u0433\u0443\u043b\u044f\u0440\u043d\u044b\u0439 \u043f\u0440\u043e\u0446\u0435\u0441\u0441, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043e\u0434\u0438\u043d \u0440\u0430\u0437 \u0432 \u0434\u0435\u043d\u044c \u0441\u043e\u0431\u0438\u0440\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435 \u0437\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0439 \u043c\u0435\u0441\u044f\u0446 \u0438\u0437 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-95518","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Historia e fshirjes fizike t\u00eb 300 milion regjistrimeve n\u00eb MySQL | ProHoster","description":"Hyrja P\u00ebrsh\u00ebndetje.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e \u0444\u0438\u0437\u0438\u0447\u0435\u0441\u043a\u043e\u043c \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0438 300 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u043e\u0432 \u0437\u0430\u043f\u0438\u0441\u0435\u0439 \u0432 MySQL | ProHoster","og:description":"\u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u041f\u0440\u0438\u0432\u0435\u0442.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-09-30T17:42:27+00:00","article:modified_time":"2020-09-30T17:42:27+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"95518","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 11:04:40","updated":"2026-02-09 21:38:05","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}