{"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 regjistrave n\u00eb MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hyrje<\/h2>\n

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

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

M\u00eb interesoi ky tem\u00eb, prandaj vendosa t\u00eb b\u00ebj nj\u00eb udh\u00ebzues (instruksion).<\/p>\n

Fillimi \u2014 Alert<\/h2>\n

\nN\u00eb paket\u00ebn server<\/a>, t\u00eb cil\u00ebn e p\u00ebrdor dhe mir\u00ebmbaj, 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 mbyllet brenda rreth 1 ore, por k\u00ebt\u00eb her\u00eb nuk u mbyll p\u00ebr 7 ose 8 or\u00eb, dhe alarmin nuk e ndalej\u2026 <\/a><\/noindex><\/p>\n

K\u00ebrkimi i shkakut<\/h2>\n

\nKam provuar t\u00eb riaktivizoj procesin, t\u00eb shikoj log-et, por nuk pash\u00eb asgj\u00eb alarmante.
\nK\u00ebrkesa u indeksua si\u00e7 duhet. Por kur mendova se \u00e7far\u00eb nuk shkon, e kuptova se volume i databaz\u00ebs ishte mjaft i madh. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 milion rekord\u00eb. Duket se indeksi punonte si\u00e7 duhet, thjesht shum\u00eb ngadal\u00eb.<\/p>\n
Grumbullimi i t\u00eb dh\u00ebnave p\u00ebr muajin ishte rreth 12 000 000 rekord\u00eb. Duket se ekipi i select i mori shum\u00eb koh\u00eb, dhe transaksioni nuk p\u00ebrfundonte p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb. <\/p>\n
DB<\/h2>\n
\nN\u00eb thelb, kjo \u00ebsht\u00eb nj\u00eb tabel\u00eb q\u00eb \u00e7do dit\u00eb rritet me rreth 400 000 rekord\u00eb. Baza duhej t\u00eb grumbullonte t\u00eb dh\u00ebna vet\u00ebm p\u00ebr muajin e fundit, prandaj llogaritja ishte q\u00eb do t\u00eb p\u00ebrballonte pik\u00ebrisht k\u00ebt\u00eb volum t\u00eb t\u00eb dh\u00ebnave, por, fatkeq\u00ebsisht, operacioni rotate nuk ishte aktivizuar.<\/p>\n
Kjo databaz\u00eb nuk ishte zhvilluar nga un\u00eb. E kam marr\u00eb at\u00eb nga nj\u00eb zhvillues tjet\u00ebr, prandaj mbetet nj\u00eb ndjenj\u00eb e k\u00ebtij borxhi teknik. <\/p>\n
Arriti momenti kur volumi i t\u00eb dh\u00ebnave t\u00eb vendosura p\u00ebrdit\u00eb u b\u00eb i madh dhe p\u00ebrfundimisht arriti kufirin. Suppozimi \u00ebsht\u00eb se duke punuar me nj\u00eb volum t\u00eb till\u00eb t\u00eb dh\u00ebnash, do t\u00eb duhej t'i ndan\u00eb ato, por fatkeq\u00ebsisht, kjo nuk ishte b\u00ebr\u00eb.<\/p>\n
Dhe k\u00ebtu hyra n\u00eb veprim.<\/p>\n
Korrigjimi<\/h2>\n
\nIshte m\u00eb racional t\u00eb zvog\u00ebloja veten e databaz\u00ebs dhe t\u00eb shkurtoja koh\u00ebn e saj p\u00ebr p\u00ebrpunim, sesa t\u00eb ndryshoja logjik\u00ebn e saj.<\/p>\n
Situata duhet t\u00eb ndryshoj\u00eb ndjesh\u00ebm n\u00ebse fshij 300 milion rekord\u00eb, prandaj vendosa ta b\u00ebja k\u00ebt\u00eb\u2026 Eh, mendova se do t\u00eb funksiononte patjet\u00ebr.<\/p>\n
Veprimi 1<\/h2>\n
\nPas p\u00ebrgatitjes s\u00eb nj\u00eb kopjeje t\u00eb besueshme, m\u00eb n\u00eb fund fillova t\u00eb d\u00ebrgoj k\u00ebrkesat.<\/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\u2026 Nuk ka p\u00ebrgjigje. Mund t\u00eb jet\u00eb se procesi merr shum\u00eb koh\u00eb?\u201d \u2014 mendova un\u00eb, por t\u00eb b\u00ebja nj\u00eb verifikim n\u00eb grafana dhe pash\u00eb se ngarkesa e diskut po rritej shum\u00eb shpejt. 
\n\u00abI should be careful\u00bb \u2014 I thought again and immediately stopped the request.<\/p>\n
Action 2<\/h2>\n
\nAfter analyzing everything, I realized that the amount of data was too great to delete it all at once.<\/p>\n
I decided to write a script that could delete about 1,000,000 records and started it.<\/p>\n
\u300cI'll implement the script\u300d<\/p>\n
\u201cNow it will definitely work,\u201d \u2014 I thought.<\/p>\n
Action 3<\/h2>\n
\nThe second method worked but turned out to be very labor-intensive.
\nTo do everything neatly and without extra nerves, it would take about two weeks. However, this scenario did not meet the service requirements, so I had to abandon it.<\/p>\n
Therefore, here\u2019s what I decided to do:<\/p>\n
We copy the table and rename it<\/h3>\n
\nFrom the previous step, I understood that deleting such a large volume of data creates an equally large load. Therefore, I decided to create a new table from scratch using insert and move the data I intended to delete into it.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nIf I create a new table of the same size as indicated above, the speed of data processing should also become 1\/7 faster.<\/p>\n
After creating the table and renaming it, I began using it as the master table. Now, if I delete a table with 300 million records, everything should be fine.
\nI learned that truncate or drop create less load than delete, and I decided to use this method.<\/p>\n
Execution<\/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\u300cem...\uff1f\u300d<\/p>\n
Action 4<\/h2>\n
\nI thought the previous idea would work, but after sending the insert request, there were multiple errors. MySQL is unforgiving.<\/p>\n
I was so exhausted that I started to think I didn\u2019t want to deal with this anymore.<\/p>\n
I sat down and thought about it and realized that maybe, for one time, there were too many insert requests\u2026
\nI tried to send an insert request for the amount of data that the database should process in one day. It worked!<\/p>\n
Well, after that, we continue sending requests for the same amount of data. Since we need to remove a month's worth of data, we repeat this operation about 35 times.<\/p>\n
Renaming the table <\/h3>\n
\nHere, luck was on my side: everything went smoothly.<\/p>\n
Alerts disappeared<\/h3>\n
\nThe speed of batch processing increased.<\/p>\n
Previously, this process took about an hour; now it takes about 2 minutes. <\/p>\n
Pasi i u sigurda q\u00eb t\u00eb gjitha problemet jan\u00eb zgjidhur, kam hequr 300 milion rekordet. Kam fshir\u00eb tabel\u00ebn dhe ndihesha si i rinovuar.<\/p>\n
P\u00ebrmbledhja<\/h2>\n
\nKuptova se gjat\u00eb p\u00ebrpunimit n\u00eb grup, ishte humbur procesi 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 shqet\u00ebsoheni p\u00ebr ngarkes\u00ebn gjat\u00eb replikimit t\u00eb t\u00eb dh\u00ebnave, duke fshir\u00eb rekordet nga baza e t\u00eb dh\u00ebnave? Le t\u00eb mos e mbingarkojm\u00eb MySQL.<\/p>\n
Ata q\u00eb e kuptojn\u00eb mir\u00eb baz\u00ebn e t\u00eb dh\u00ebnave padyshim nuk do t\u00eb p\u00ebrballen me nj\u00eb problem t\u00eb till\u00eb. Shpresoj q\u00eb ky artikull t\u00eb ket\u00eb qen\u00eb i dobish\u00ebm p\u00ebr t\u00eb tjer\u00ebt.<\/p>\n
Faleminderit q\u00eb e lexuat!<\/p>\n
Do t\u00eb ishim shum\u00eb t\u00eb lumtur n\u00ebse do t\u00eb na thoni n\u00ebse ju p\u00eblqi 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 rekord\u00ebve n\u00eb MySQL | ProHoster","description":"Hyrje 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}]}}