{"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\/cs\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"P\u0159\u00edb\u011bh o fyzick\u00e9m odstran\u011bn\u00ed 300 milion\u016f z\u00e1znam\u016f v MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\u00davod<\/h2>\n

\nAhoj. Jsem ningenMe, webov\u00fd v\u00fdvoj\u00e1\u0159.<\/p>\n

Jak ji\u017e n\u00e1zev napov\u00edd\u00e1, m\u016fj p\u0159\u00edb\u011bh je p\u0159\u00edb\u011bhem o fyzick\u00e9m odstran\u011bn\u00ed 300 milion\u016f z\u00e1znam\u016f v MySQL.<\/p>\n

Zaujal m\u011b tento probl\u00e9m, a proto jsem se rozhodl vytvo\u0159it p\u0159\u00edru\u010dku.<\/p>\n

Za\u010d\u00e1tek \u2014 Upozorn\u011bn\u00ed<\/h2>\n

\nV d\u00e1vkov\u00e9m serveru<\/a>, kter\u00fd pou\u017e\u00edv\u00e1m a spravuji, existuje pravideln\u00fd proces, kter\u00fd jednou denn\u011b shroma\u017e\u010fuje data za posledn\u00ed m\u011bs\u00edc z MySQL. <\/p>\n

Obvykle tento proces trv\u00e1 p\u0159ibli\u017en\u011b 1 hodinu, ale tentokr\u00e1t to trvalo 7 nebo 8 hodin, a upozorn\u011bn\u00ed neust\u00e1le p\u0159ich\u00e1zela\u2026 <\/a><\/noindex><\/p>\n

Hled\u00e1n\u00ed p\u0159\u00ed\u010diny<\/h2>\n

\nZkusil jsem restartovat proces, pod\u00edvat se do log\u016f, ale nic podez\u0159el\u00e9ho jsem nena\u0161el.
\nDotaz se indexoval spr\u00e1vn\u011b. Ale kdy\u017e jsem p\u0159em\u00fd\u0161lel o tom, co nen\u00ed v po\u0159\u00e1dku, uv\u011bdomil jsem si, \u017ee objem datab\u00e1ze je dost velk\u00fd. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 milion\u016f z\u00e1znam\u016f. Zd\u00e1 se, \u017ee indexace fungovala spr\u00e1vn\u011b, jen velmi pomalu.<\/p>\n
Pot\u0159ebn\u00fd sb\u011br dat za m\u011bs\u00edc \u010dinil p\u0159ibli\u017en\u011b 12 000 000 z\u00e1znam\u016f. Vypad\u00e1 to, \u017ee dotaz SELECT trval dlouho a transakce se vykon\u00e1vala jen t\u011b\u017ece. <\/p>\n
DB<\/h2>\n
\nV podstat\u011b je to tabulka, kter\u00e1 ka\u017ed\u00fd den nar\u016fst\u00e1 p\u0159ibli\u017en\u011b o 400 000 z\u00e1znam\u016f. Datab\u00e1ze m\u011bla shroma\u017e\u010fovat data pouze za posledn\u00ed m\u011bs\u00edc, tedy byla po\u010d\u00edt\u00e1na na to, \u017ee bude zvl\u00e1dat pr\u00e1v\u011b tento objem dat, ale bohu\u017eel nebyla zahrnuta operace ROTATE.<\/p>\n
Tato datab\u00e1ze byla navr\u017eena nik\u00fdm jin\u00fdm ne\u017e mnou. P\u0159evzal jsem ji od jin\u00e9ho v\u00fdvoj\u00e1\u0159e, co\u017e zanechalo pocit technick\u00e9ho dluhu. <\/p>\n
Nastal okam\u017eik, kdy objem denn\u011b vkl\u00e1dan\u00fdch dat vzrostl a kone\u010dn\u011b dos\u00e1hl limitu. P\u0159edpokl\u00e1d\u00e1 se, \u017ee p\u0159i pr\u00e1ci s tak velk\u00fdm objemem dat by se m\u011bla data rozd\u011blovat, ale to bohu\u017eel nebylo ud\u011bl\u00e1no.<\/p>\n
A tady jsem vstoupil do procesu j\u00e1.<\/p>\n
Oprava<\/h2>\n
\nBylo rozumn\u011bj\u0161\u00ed zmen\u0161it samotnou datab\u00e1zi a zkr\u00e1tit \u010das na jej\u00ed zpracov\u00e1n\u00ed, ne\u017e m\u011bnit samotnou logiku.<\/p>\n
Situace by se m\u011bla v\u00fdrazn\u011b zm\u011bnit, pokud sma\u017eeme 300 milion\u016f z\u00e1znam\u016f, proto jsem se rozhodl to ud\u011blat\u2026 Ach, myslel jsem, \u017ee to ur\u010dit\u011b zabere.<\/p>\n
Akce 1<\/h2>\n
\nPo p\u0159\u00edprav\u011b spolehliv\u00e9 z\u00e1lohy jsem kone\u010dn\u011b za\u010dal odes\u00edlat dotazy.<\/p>\n
\u201eOdesl\u00e1n\u00ed dotazu\u201c<\/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
\u201eHmm\u2026 \u017d\u00e1dn\u00e1 odpov\u011b\u010f. Mo\u017en\u00e1 proces trv\u00e1 del\u0161\u00ed dobu?\u201c \u2014 pomyslel jsem si, ale pro jistotu jsem se pod\u00edval do Grafany a vid\u011bl, \u017ee zat\u00ed\u017een\u00ed disku velmi rychle rostlo. 
\n\u201eNebezpe\u010dn\u00e9,\u201c pomyslel jsem si znovu a okam\u017eit\u011b zastavil dotaz.<\/p>\n
Akce 2<\/h2>\n
\nPo anal\u00fdze v\u0161eho jsem pochopil, \u017ee objem dat byl p\u0159\u00edli\u0161 velk\u00fd na to, abych odstranil v\u0161echno najednou.<\/p>\n
Rozhodl jsem se napsat skript, kter\u00fd bude schopen odstranit p\u0159ibli\u017en\u011b 1 000 000 z\u00e1znam\u016f, a spustil jsem ho.<\/p>\n
\u201eimplementuji skript\u201c<\/p>\n
\u201eTe\u010f to ur\u010dit\u011b vyjde,\u201c pomyslel jsem si.<\/p>\n
Akce 3<\/h2>\n
\nDruh\u00e1 metoda zafungovala, ale byla velmi pracn\u00e1.
\nAbych to ud\u011blal d\u016fkladn\u011b, bez zbyte\u010dn\u00fdch nerv\u016f, trvalo by to asi dva t\u00fddny. Ale tento sc\u00e9n\u00e1\u0159 st\u00e1le neodpov\u00eddal po\u017eadavk\u016fm slu\u017eby, tak\u017ee jsem se od n\u011bj musel odch\u00fdlit.<\/p>\n
Proto jsem se rozhodl ud\u011blat toto:<\/p>\n
Zkop\u00edrujeme tabulku a p\u0159ejmenujeme ji.<\/h3>\n
\nZ p\u0159edchoz\u00edho kroku jsem pochopil, \u017ee odstran\u011bn\u00ed tak velk\u00e9ho objemu dat vytv\u00e1\u0159\u00ed stejn\u011b velk\u00e9 zat\u00ed\u017een\u00ed. Tak jsem se rozhodl vytvo\u0159it novou tabulku od nuly pomoc\u00ed insert a p\u0159esunout do n\u00ed data, kter\u00e1 jsem cht\u011bl odstranit.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nPokud vytvo\u0159\u00edm novou tabulku o velikosti, jak je uvedeno v\u00fd\u0161e, rychlost zpracov\u00e1n\u00ed dat by tak\u00e9 m\u011bla b\u00fdt 1\/7 rychlej\u0161\u00ed.<\/p>\n
Pot\u00e9, co jsem tabulku vytvo\u0159il a p\u0159ejmenoval, za\u010dal jsem ji pou\u017e\u00edvat jako hlavn\u00ed tabulku. Te\u010f, pokud odstran\u00edm tabulku s 300 miliony z\u00e1znam\u016f, m\u011blo by b\u00fdt v\u0161echno v po\u0159\u00e1dku.
\nZjistil jsem, \u017ee truncate nebo drop vytv\u00e1\u0159ej\u00ed men\u0161\u00ed z\u00e1t\u011b\u017e ne\u017e delete, a rozhodl jsem se pou\u017e\u00edt tento p\u0159\u00edstup.<\/p>\n
Proveden\u00ed<\/h3>\n
\n\u201eOdesl\u00e1n\u00ed dotazu\u201c<\/p>\n
INSERT INTO tmp_hoge_table SELECT * FROM hoge_table WHERE 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
Akce 4<\/h2>\n
\nMyslel jsem, \u017ee p\u0159edchoz\u00ed n\u00e1pad bude fungovat, ale po odesl\u00e1n\u00ed insert dotazu se objevila v\u00edc ne\u017e jedna chyba. MySQL ne\u0161et\u0159\u00ed.<\/p>\n
Byl jsem u\u017e tak unaven\u00fd, \u017ee jsem za\u010dal uva\u017eovat, \u017ee tohle u\u017e d\u011blat nechci.<\/p>\n
Chv\u00edli jsem p\u0159em\u00fd\u0161lel a pochopil jsem, \u017ee mo\u017en\u00e1 pro jednor\u00e1zov\u00fd dotaz bylo insert\u016f p\u0159\u00edli\u0161 mnoho\u2026
\nZkusil jsem odeslat insert dotaz na objem dat, kter\u00fd by datab\u00e1ze m\u011bla zpracovat za 1 den. A povedlo se!<\/p>\n
A potom pokra\u010dujeme v odes\u00edl\u00e1n\u00ed dotaz\u016f na stejn\u00fd objem dat. Proto\u017ee pot\u0159ebujeme odstranit m\u011bs\u00ed\u010dn\u00ed objem dat, opakujeme tuto operaci p\u0159ibli\u017en\u011b 35kr\u00e1t.<\/p>\n
P\u0159ejmenov\u00e1n\u00ed tabulky <\/h3>\n
\nZde m\u011bla \u0161t\u011bst\u00ed na m\u00e9 stran\u011b: v\u0161echno prob\u011bhlo hladce.<\/p>\n
Alert zmizel.<\/h3>\n
\nRychlost d\u00e1vkov\u00e9ho zpracov\u00e1n\u00ed se zv\u00fd\u0161ila.<\/p>\n
Kdysi tento proces trval asi hodinu, nyn\u00ed zabere p\u0159ibli\u017en\u011b 2 minuty. <\/p>\n
Pot\u00e9, co jsem se ujistil, \u017ee jsou v\u0161echny probl\u00e9my vy\u0159e\u0161eny, jsem smazal 300 milion\u016f z\u00e1znam\u016f. Smazal jsem tabulku a c\u00edtil se jako znovuzrozen\u00fd.<\/p>\n
Shrnut\u00ed<\/h2>\n
\nUv\u011bdomil jsem si, \u017ee p\u0159i d\u00e1vkov\u00e9m zpracov\u00e1n\u00ed do\u0161lo k opomenut\u00ed rotate processing, co\u017e byla hlavn\u00ed p\u0159\u00ed\u010dina probl\u00e9mu. Takov\u00e1 architektonick\u00e1 chyba vede k pr\u00e1zdn\u00e9 ztr\u00e1t\u011b \u010dasu. <\/p>\n
Zamysleli jste se n\u011bkdy nad z\u00e1t\u011b\u017e\u00ed p\u0159i replikaci dat, kdy\u017e ma\u017eete z\u00e1znamy z datab\u00e1ze? Poj\u010fme nep\u0159et\u011b\u017eovat MySQL.<\/p>\n
Ti, kdo se dob\u0159e orientuj\u00ed v datab\u00e1z\u00edch, se s takov\u00fdm probl\u00e9mem jist\u011b nesetkaj\u00ed. Ostatn\u00edm douf\u00e1m, \u017ee tento \u010dl\u00e1nek byl u\u017eite\u010dn\u00fd.<\/p>\n
D\u011bkujeme za p\u0159e\u010dten\u00ed!<\/p>\n
Budeme velmi r\u00e1di, pokud n\u00e1m \u0159eknete, zda se v\u00e1m tento \u010dl\u00e1nek l\u00edbil, zda byl p\u0159eklad srozumiteln\u00fd a zda v\u00e1m byl u\u017eite\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\udd47P\u0159\u00edb\u011bh o fyzick\u00e9m odstran\u011bn\u00ed 300 milion\u016f z\u00e1znam\u016f v MySQL | ProHoster","description":"\u00davod Ahoj.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}