{"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\/ro\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"O poveste despre \u0219tergerea fizic\u0103 a 300 de milioane de \u00eenregistr\u0103ri \u00een MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Introducere<\/h2>\n

\nSalut. Sunt ningenMe, dezvoltator web.<\/p>\n

A\u0219a cum se spune \u00een titlu, povestea mea este despre eliminarea fizic\u0103 a 300 de milioane de \u00eenregistr\u0103ri din MySQL.<\/p>\n

M-am interesat de acest subiect, a\u0219a c\u0103 am decis s\u0103 fac un ghid (instruc\u021biuni).<\/p>\n

\u00cenceput \u2014 Alert<\/h2>\n

\n\u00cen modul batch server<\/a>, pe care \u00eel folosesc \u0219i \u00eentre\u021bin, exist\u0103 un proces regulat care colecteaz\u0103 datele din ultima lun\u0103 din MySQL, o dat\u0103 pe zi. <\/p>\n

De obicei, acest proces se finalizeaz\u0103 \u00een aproximativ 1 or\u0103, dar de data aceasta nu s-a terminat timp de 7 sau 8 ore, iar alertul continua s\u0103 apar\u0103... <\/a><\/noindex><\/p>\n

C\u0103utarea cauzei<\/h2>\n

\nAm \u00eencercat s\u0103 repornesc procesul, s\u0103 verific jurnalele, dar nu am observat nimic grav.
\nInterogarea s-a indexat corect. Dar c\u00e2nd m-am g\u00e2ndit ce ar putea merge prost, mi-am dat seama c\u0103 dimensiunea bazei de date este destul de mare. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 de milioane de \u00eenregistr\u0103ri. Se pare c\u0103 indexarea a func\u021bionat corect, doar c\u0103 foarte lent.<\/p>\n
Colectarea datelor lunare era de aproximativ 12.000.000 de \u00eenregistr\u0103ri. Se pare c\u0103 comenzile select au durat mult timp, iar tranzac\u021bia nu s-a finalizat mult timp. <\/p>\n
DB<\/h2>\n
\nPractic, este o tabel\u0103 care cre\u0219te zilnic cu aproximativ 400.000 de \u00eenregistr\u0103ri. Baza de date ar fi trebuit s\u0103 colecteze date doar pentru ultima lun\u0103, deci se presupunea c\u0103 va gestiona exact acest volum de date, dar, din p\u0103cate, opera\u021biunea de rotire nu a fost activat\u0103.<\/p>\n
Aceast\u0103 baz\u0103 de date nu a fost dezvoltat\u0103 de mine. Am primit-o de la un alt dezvoltator, a\u0219a c\u0103 am r\u0103mas cu senza\u021bia c\u0103 este o datorie tehnic\u0103. <\/p>\n
A sosit momentul \u00een care volumul de date inserate zilnic a devenit mare \u0219i, \u00een final, a atins limita. Se presupune c\u0103 lucr\u00e2nd cu un volum at\u00e2t de mare de date, ar fi trebuit s\u0103 le segment\u0103m, dar din p\u0103cate, acest lucru nu s-a \u00eent\u00e2mplat.<\/p>\n
\u0218i aici interveni eu.<\/p>\n
Corectare<\/h2>\n
\nA fost mai ra\u021bional s\u0103 reduc baz\u0103 de date \u00een sine \u0219i s\u0103 scad timpul de procesare, dec\u00e2t s\u0103 schimb logica \u00een sine.<\/p>\n
Situa\u021bia ar trebui s\u0103 se schimbe semnificativ dac\u0103 a\u0219 \u0219terge 300 de milioane de \u00eenregistr\u0103ri, a\u0219a c\u0103 am decis s\u0103 fac exact asta... Ah, am crezut c\u0103 va func\u021biona cu siguran\u021b\u0103.<\/p>\n
Ac\u021biunea 1<\/h2>\n
\nDup\u0103 ce am preg\u0103tit un backup solid, \u00een sf\u00e2r\u0219it am \u00eenceput s\u0103 trimit interog\u0103rile.<\/p>\n
\u300cTrimitere interogare\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... Nu am primit r\u0103spuns. Poate procesul dureaz\u0103 mult?\u201d \u2014 m-am g\u00e2ndit, dar, de fiecare dat\u0103, am verificat \u00een grafana \u0219i am observat c\u0103 \u00eenc\u0103rcarea discului cre\u0219tea foarte repede. 
\n\u201eCeva periculos\u201d \u2013 m-am g\u00e2ndit din nou \u0219i imediat am oprit cererea.<\/p>\n
Ac\u021biunea 2<\/h2>\n
\nAnaliz\u00e2nd totul, am realizat c\u0103 volumul de date era prea mare pentru a \u0219terge totul dintr-o dat\u0103.<\/p>\n
Am decis s\u0103 scriu un script care s\u0103 poat\u0103 \u0219terge aproximativ 1.000.000 de \u00eenregistr\u0103ri \u0219i l-am lansat.<\/p>\n
\u300c\u00eemi voi implementa scriptul\u300d<\/p>\n
\u201eAcum cu siguran\u021b\u0103 va func\u021biona\u201d, m-am g\u00e2ndit.<\/p>\n
Ac\u021biunea 3<\/h2>\n
\nA doua metod\u0103 a func\u021bionat, dar s-a dovedit a fi foarte laborioas\u0103.
\nPentru a face totul cu aten\u021bie, f\u0103r\u0103 nervi \u00een plus, ar fi fost nevoie de aproximativ dou\u0103 s\u0103pt\u0103m\u00e2ni. Totu\u0219i, acest scenariu nu corespundea cerin\u021belor de servicii, a\u0219a c\u0103 a trebuit s\u0103 renun\u021b la el.<\/p>\n
A\u0219a c\u0103, iat\u0103 ce am decis s\u0103 fac:<\/p>\n
Copiem tabelul \u0219i \u00eel redenumim.<\/h3>\n
\nDin pasul anterior, am \u00een\u021beles c\u0103 \u0219tergerea unui astfel de volum mare de date creeaz\u0103 o astfel de sarcin\u0103 mare. De aceea, am decis s\u0103 creez un nou tabel de la zero folosind inser\u021bii \u0219i s\u0103 mut datele pe care inten\u021bionam s\u0103 le \u0219terg.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nDac\u0103 cre\u0103m un nou tabel de dimensiuni identice cu cele men\u021bionate mai sus, viteza de procesare a datelor ar trebui s\u0103 fie cu 1\/7 mai rapid\u0103.<\/p>\n
Dup\u0103 ce am creat tabelul \u0219i l-am redenumit, am \u00eenceput s\u0103-l folosesc ca tabel principal. Acum, dac\u0103 elimin tabelul cu 300 de milioane de \u00eenregistr\u0103ri, totul ar trebui s\u0103 fie \u00een regul\u0103.
\nAm aflat c\u0103 truncate sau drop genereaz\u0103 o sarcin\u0103 mai mic\u0103 dec\u00e2t delete \u0219i am decis s\u0103 folosesc aceast\u0103 metod\u0103.<\/p>\n
Execu\u021bie<\/h3>\n
\n\u300cTrimitere interogare\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
Ac\u021biunea 4<\/h2>\n
\nAm crezut c\u0103 ideea anterioar\u0103 va func\u021biona, dar dup\u0103 trimiterea cererii de inserare a ap\u0103rut o multitudine de erori. MySQL nu face rabat.<\/p>\n
Eram deja at\u00e2t de obosit \u00eenc\u00e2t am \u00eenceput s\u0103 cred c\u0103 nu mai vreau s\u0103 m\u0103 ocup de asta.<\/p>\n
M-am g\u00e2ndit \u0219i am realizat c\u0103 poate au fost prea multe cereri de inserare pentru o singur\u0103 dat\u0103\u2026
\nAm \u00eencercat s\u0103 trimit o cerere de inserare pentru volumul de date pe care baza ar trebui s\u0103-l proceseze \u00eentr-o singur\u0103 zi. A mers!<\/p>\n
Ei bine, dup\u0103 asta continu\u0103m s\u0103 trimitem cereri pentru acela\u0219i volum de date. Deoarece trebuie s\u0103 elimin\u0103m volumul de date lunar, repet\u0103m aceast\u0103 opera\u021bie de aproximativ 35 de ori.<\/p>\n
Redenumirea tabelului <\/h3>\n
\nAici norocul a fost de partea mea: totul a decurs lin.<\/p>\n
Alerta a disp\u0103rut.<\/h3>\n
\nViteza de procesare \u00een lot a crescut.<\/p>\n
Anterior, acest proces dura aproximativ o or\u0103, acum dureaz\u0103 aproximativ 2 minute. <\/p>\n
Dup\u0103 ce m-am asigurat c\u0103 toate problemele sunt rezolvate, am eliminat 300 de milioane de \u00eenregistr\u0103ri. Am \u0219ters tabelul \u0219i m-am sim\u021bit ca \u0219i cum m-a\u0219 fi n\u0103scut din nou.<\/p>\n
Rezumat<\/h2>\n
\nAm realizat c\u0103, \u00een procesarea \u00een loturi, a fost omis procesul de rotire, iar aceasta a fost problema principal\u0103. O astfel de eroare \u00een arhitectur\u0103 duce la o pierdere de timp inutil. <\/p>\n
Te g\u00e2nde\u0219ti la sarcina de lucru \u00een timpul replic\u0103rii datelor, atunci c\u00e2nd \u0219tergi \u00eenregistr\u0103ri din baza de date? S\u0103 nu suprasolicit\u0103m MySQL.<\/p>\n
Cei care au cuno\u0219tin\u021be solide despre baze de date nu se vor confrunta cu o astfel de problem\u0103. Sper c\u0103 acest articol a fost util celorlal\u021bi.<\/p>\n
Thank you for reading!<\/p>\n
Am fi foarte bucuro\u0219i dac\u0103 ne-ai povesti dac\u0103 \u021bi-a pl\u0103cut acest articol, dac\u0103 traducerea a fost clar\u0103 \u0219i dac\u0103 \u021bi-a fost de folos?<\/i>
\n
Sursa: 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\udd47Povestea despre \u0219tergerea fizic\u0103 a 300 de milioane de \u00eenregistr\u0103ri \u00een MySQL | ProHoster","description":"Introducere Salut.","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","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\/ro\/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\/ro\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}