{"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\/sk\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"Pr\u00edbeh o fyzickom odstr\u00e1nen\u00ed 300 mili\u00f3nov z\u00e1znamov v MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\u00davod<\/h2>\n

\nAhoj. Som ningenMe, webov\u00fd v\u00fdvoj\u00e1r.<\/p>\n

Ako nazna\u010duje n\u00e1zov, m\u00f4j pr\u00edbeh je pr\u00edbehom o fyzickom odstr\u00e1nen\u00ed 300 mili\u00f3nov z\u00e1znamov v MySQL.<\/p>\n

Zaujal ma tento probl\u00e9m, a tak som sa rozhodol vytvori\u0165 pozn\u00e1mku (n\u00e1vod).<\/p>\n

Za\u010diatok \u2014 Alert<\/h2>\n

\nV d\u00e1vkovom serveri<\/a>, ktor\u00fd pou\u017e\u00edvam a spravujem, je pravideln\u00fd proces, ktor\u00fd ka\u017ed\u00fd de\u0148 zbiera \u00fadaje za posledn\u00fd mesiac z MySQL. <\/p>\n

Zvy\u010dajne tento proces trv\u00e1 pribli\u017ene 1 hodinu, ale tentoraz sa nedokon\u010dil 7 alebo 8 hod\u00edn a upozornenie sa nezastavilo... <\/a><\/noindex><\/p>\n

H\u013eadanie pr\u00ed\u010diny<\/h2>\n

\nSna\u017eil som sa re\u0161tartova\u0165 proces, pozrie\u0165 sa na logy, ale ni\u010d v\u00e1\u017ene som nevidel.
\nDotaz bol spr\u00e1vne indexovan\u00fd. Ale ke\u010f som uva\u017eoval, \u010do sa deje zle, uvedomil som si, \u017ee objem DB je pomerne ve\u013ek\u00fd. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 mili\u00f3nov z\u00e1znamov. Zd\u00e1 sa, \u017ee indexovanie fungovalo spr\u00e1vne, iba ve\u013emi pomaly.<\/p>\n
Po\u017eadovan\u00fd zber \u00fadajov za mesiac bol pribli\u017ene 12 000 000 z\u00e1znamov. Zd\u00e1 sa, \u017ee pr\u00edkaz select trval dlho a transakcia sa dlho nevykon\u00e1vala. <\/p>\n
DB<\/h2>\n
\nV podstate ide o tabu\u013eku, ktor\u00e1 sa ka\u017ed\u00fd de\u0148 zvy\u0161uje o pribli\u017ene 400 000 z\u00e1znamov. Datab\u00e1za mala zbiera\u0165 \u00fadaje len za posledn\u00fd mesiac, a preto sa predpokladalo, \u017ee zvl\u00e1dne tento objem \u00fadajov, ale \u017eia\u013e, oper\u00e1cia rotate nebola zapnut\u00e1.<\/p>\n
T\u00fato datab\u00e1zu nevytvoril som ja. Prevzal som ju od in\u00e9ho v\u00fdvoj\u00e1ra, tak\u017ee zostal pocit technick\u00e9ho dlhu. <\/p>\n
Pri\u0161iel moment, ke\u010f objem denne vkladan\u00fdch \u00fadajov sa stal ve\u013ek\u00fdm a nakoniec dosiahol svoj limit. Predpoklad\u00e1 sa, \u017ee pri pr\u00e1ci s tak\u00fdm ve\u013ek\u00fdm objemom \u00fadajov by ich malo by\u0165 potrebn\u00e9 rozde\u013eova\u0165, ale to, \u017eia\u013e, nebolo vykonan\u00e9.<\/p>\n
A tu som zasadol do hry ja.<\/p>\n
Oprava<\/h2>\n
\nBolo rozumnej\u0161ie zmen\u0161i\u0165 samotn\u00fa datab\u00e1zu a skr\u00e1ti\u0165 \u010das jej spracovania, ne\u017e meni\u0165 samotn\u00fa logiku.<\/p>\n
Situ\u00e1cia by sa mala v\u00fdrazne zmeni\u0165, ak vyma\u017eeme 300 mili\u00f3nov z\u00e1znamov, tak\u017ee som sa rozhodol tak spravi\u0165... Ach, myslel som si, \u017ee to ur\u010dite zafunguje.<\/p>\n
Akcia 1<\/h2>\n
\nPo pr\u00edprave spo\u013eahlivej z\u00e1lohy som kone\u010dne za\u010dal posiela\u0165 po\u017eiadavky.<\/p>\n
\u300cOdoslanie po\u017eiadavky\u300d<\/p>\n
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u201e\u2026\u201c<\/p>\n
\u201e\u2026\u201c<\/p>\n
\u201cHm\u2026 Odpovede niet. Mo\u017eno proces trv\u00e1 dlho?\u201d \u2014 pomyslel som si, ale pre istotu som sa pozrel do grafana a videl, \u017ee za\u0165a\u017eenie disku ve\u013emi r\u00fdchlo r\u00e1stlo. 
\n\u201eNebezpe\u010dne\u201c \u2014 pomyslel som si znova a hne\u010f som zastavil po\u017eiadavku.<\/p>\n
Akcia 2<\/h2>\n
\nAnalyzovan\u00edm v\u0161etk\u00e9ho som zistil, \u017ee objem d\u00e1t bol pr\u00edli\u0161 ve\u013ek\u00fd na to, aby som v\u0161etko vymazal naraz.<\/p>\n
Rozhodol som sa nap\u00edsa\u0165 skript, ktor\u00fd dok\u00e1\u017ee vymaz\u00e1va\u0165 asi 1 000 000 z\u00e1znamov a spustil som ho.<\/p>\n
\u300crealizujem skript\u300d<\/p>\n
\u201eTeraz to ur\u010dite zafunguje,\u201c pomyslel som si.<\/p>\n
Akcia 3<\/h2>\n
\nDruh\u00fd sp\u00f4sob zafungoval, ale bol ve\u013emi pracn\u00fd.
\nAby som v\u0161etko urobil opatrne, bez zbyto\u010dn\u00e9ho stresu, trvalo by to pribli\u017ene dva t\u00fd\u017edne. Av\u0161ak tento scen\u00e1r nevyhovoval po\u017eiadavk\u00e1m slu\u017eby, a preto som sa musel od neho vzda\u0165.<\/p>\n
Tak\u017ee, toto je to, \u010do som sa rozhodol urobi\u0165:<\/p>\n
Skop\u00edrujeme tabu\u013eku a premenov\u00e1me ju<\/h3>\n
\nZ predch\u00e1dzaj\u00faceho kroku som pochopil, \u017ee vymazanie tak\u00e9ho ve\u013ek\u00e9ho objemu d\u00e1t vytv\u00e1ra tak\u00fa ist\u00fa ve\u013ek\u00fa z\u00e1\u0165a\u017e. Preto som sa rozhodol vytvori\u0165 nov\u00fa tabu\u013eku od nuly pomocou insert a presun\u00fa\u0165 do nej d\u00e1ta, ktor\u00e9 som chcel vymaza\u0165.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nAk vytvor\u00edte nov\u00fa tabu\u013eku rovnakej ve\u013ekosti, ak\u00fa som uviedol vy\u0161\u0161ie, r\u00fdchlos\u0165 spracovania d\u00e1t by mala by\u0165 aj 1\/7 r\u00fdchlej\u0161ia.<\/p>\n
Potom, \u010do som vytvoril tabu\u013eku a premenoval ju, som za\u010dal pou\u017e\u00edva\u0165 ju ako hlavn\u00fa (master) tabu\u013eku. Teraz, ak vyma\u017eem tabu\u013eku so 300 mili\u00f3nmi z\u00e1znamov, malo by to by\u0165 v poriadku.
\nZistil som, \u017ee truncate alebo drop sp\u00f4sobuj\u00fa men\u0161iu z\u00e1\u0165a\u017e ako delete, a rozhodol som sa pou\u017ei\u0165 tento sp\u00f4sob.<\/p>\n
Vykon\u00e1vanie<\/h3>\n
\n\u300cOdoslanie po\u017eiadavky\u300d<\/p>\n
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u201e\u2026\u201c
\n\u201e\u2026\u201c
\n\u201eem\u2026?\u201c<\/p>\n
Akcia 4<\/h2>\n
\nMyslel som, \u017ee predch\u00e1dzaj\u00faci n\u00e1pad zafunguje, ale po odoslan\u00ed po\u017eiadavky insert sa objavila mno\u017estvo ch\u00fdb. MySQL ne\u0161etr\u00ed.<\/p>\n
Bol som u\u017e tak unaven\u00fd, \u017ee som za\u010dal myslie\u0165, \u017ee u\u017e to nechcem robi\u0165.<\/p>\n
Posadil som sa a prem\u00fd\u0161\u013eal a uvedomil som si, \u017ee mo\u017eno pre jedenkr\u00e1t bolo po\u017eiadaviek insert pr\u00edli\u0161 ve\u013ea...
\nSk\u00fasil som posla\u0165 po\u017eiadavku insert na objem d\u00e1t, ktor\u00e9 by mala datab\u00e1za spracova\u0165 za 1 de\u0148. Podarilo sa!<\/p>\n
A potom pokra\u010dujeme v zasielan\u00ed po\u017eiadaviek na rovnak\u00fd objem d\u00e1t. Ke\u010f\u017ee m\u00e1m odstr\u00e1ni\u0165 mesa\u010dn\u00fd objem \u00fadajov, opakujem t\u00fato oper\u00e1ciu pribli\u017ene 35-kr\u00e1t.<\/p>\n
Premenovanie tabu\u013eky <\/h3>\n
\nTu mi \u0161\u0165astie st\u00e1lo na strane: v\u0161etko prebehlo hladko.<\/p>\n
Alert zmizli<\/h3>\n
\nR\u00fdchlos\u0165 d\u00e1vkov\u00e9ho spracovania vzr\u00e1stla.<\/p>\n
Sk\u00f4r tento proces trval pribli\u017ene hodinu, teraz trv\u00e1 asi 2 min\u00faty. <\/p>\n
Ke\u010f som sa uistil, \u017ee v\u0161etky probl\u00e9my s\u00fa vyrie\u0161en\u00e9, odstr\u00e1nil som 300 mili\u00f3nov z\u00e1znamov. Vymazal som tabu\u013eku a c\u00edtil som sa ako znovuzroden\u00fd.<\/p>\n
Zhrnutie<\/h2>\n
\nUvedomil som si, \u017ee v d\u00e1vkovom spracovan\u00ed bola vynechan\u00e1 rot\u00e1cia spracovania, a to bola hlavn\u00e1 pr\u00ed\u010dina probl\u00e9mu. Tak\u00e1to chyba v architekt\u00fare vedie k zbyto\u010dn\u00e9mu plytvaniu \u010dasom. <\/p>\n
Zam\u00fd\u0161\u013eate nad z\u00e1\u0165a\u017eou pri replik\u00e1cii \u00fadajov, ke\u010f odstra\u0148ujete z\u00e1znamy z datab\u00e1zy? Po\u010fme nepre\u0165a\u017eova\u0165 MySQL.<\/p>\n
T\u00ed, ktor\u00ed sa dobre vyznaj\u00fa v datab\u00e1zach, sa s tak\u00fdmto probl\u00e9mom ur\u010dite nestretn\u00fa. D\u00fafam, \u017ee tento \u010dl\u00e1nok bol u\u017eito\u010dn\u00fd pre ostatn\u00fdch.<\/p>\n
\u010eakujeme za pre\u010d\u00edtanie!<\/p>\n
Ve\u013emi n\u00e1s pote\u0161\u00ed, ak n\u00e1m poviete, \u010di sa v\u00e1m tento \u010dl\u00e1nok p\u00e1\u010dil, \u010di bol preklad zrozumite\u013en\u00fd a \u010di bol pre v\u00e1s u\u017eito\u010dn\u00fd.<\/i>
\n
Zdroj: 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\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\udd47Pr\u00edbeh o fyzickom odstr\u00e1nen\u00ed 300 mili\u00f3nov z\u00e1znamov v MySQL | ProHoster","description":"\u00davod Ahoj.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}