{"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\/et\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"Lugu 300 miljoni kirje f\u00fc\u00fcsilisest kustutamisest MySQL-is","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Sissejuhatus<\/h2>\n

\nTere. Mina olen ningenMe, veebi arendaja.<\/p>\n

Nagu pealkirjas \u00f6eldud, on minu lugu 300 miljoni kirje f\u00fc\u00fcsilisest kustutamisest MySQL-is.<\/p>\n

See huvitab mind, seet\u00f5ttu otsustasin koostada meelespea (juhendi).<\/p>\n

Algus \u2014 Alarm<\/h2>\n

\nKogumisprotsessis, mida ma kasutan ja hooldan, on regulaarne protsess, mis korjab MySQL-ist andmed viimase kuu kohta \u00fcks kord p\u00e4evas. serveris<\/a>Tavaliselt l\u00f5petatakse see protsess umbes 1 tunni jooksul, kuid seekord ei l\u00f5petanud see 7 v\u00f5i 8 tunni jooksul ja alarm ei lakka ilmumast\u2026 <\/p>\n

P\u00f5hjuse otsimine <\/a><\/noindex><\/p>\n

Proovisin protsessi taask\u00e4ivitada, vaadata logisid, kuid midagi hullemat ma ei n\u00e4inud.<\/h2>\n

\nP\u00e4ring indekseeriti \u00f5igesti. Kuid kui ma m\u00f5tlema hakkasin, mis valesti k\u00e4ib, sain ma aru, et andmebaasi suurus on \u00fcsna suur.
\nhoge_table | 350'000'000 | <\/p>\n

350 miljonit kirjet. Tundub, et indekseerimine toimis \u00f5igesti, lihtsalt v\u00e4ga aeglaselt.<\/code><\/pre>\n
\nKogutud andmed kuu kohta olid umbes 12 000 000 kirjet. Tundub, et select-k\u00e4sk v\u00f5ttis palju aega ja tehing ei t\u00e4itunud pikka aega.<\/p>\n
DB <\/p>\n
P\u00f5him\u00f5tteliselt on see tabel, mis kasvab iga p\u00e4ev umbes 400 000 kirje v\u00f5rra. Andmebaas pidi koguma andmeid ainult viimase kuu kohta, seega arvati, et see talub just selle mahu andmeid, kuid kahjuks ei olnud rotate operatsioon sisse l\u00fclitatud.<\/h2>\n
\nSee andmebaas ei olnud minu loodud. V\u00f5tsin selle \u00fcle teiselt arendajalt, seet\u00f5ttu on j\u00e4\u00e4nud tunne, et see on tehniline v\u00f5lg.<\/p>\n
Kohale j\u00f5udis hetk, mil igap\u00e4evaselt sisestatav andmemahu suurenemine saavutas l\u00f5puks piiri. Eeldatakse, et t\u00f6\u00f6tades sellise suure andmemahuga, tuleks neid jagada, kuid kahjuks seda ei tehtud. <\/p>\n
Ja siis astusin ma m\u00e4ngu.<\/p>\n
Parandus<\/p>\n
Mugav oli andmebaasi mahtu v\u00e4hendada ja t\u00f6\u00f6tlusaega l\u00fchendada, kui muuta kogu loogikat.<\/h2>\n
\nKuna olukord peaks paranema oluliselt, kui kustutada 300 miljonit kirjet, otsustasin seda teha\u2026 Ah, ma arvasin, et see t\u00f5esti toimib.<\/p>\n
Tegevus 1<\/p>\n
Usaldusv\u00e4\u00e4rse varukoopia teinud, hakkasin l\u00f5puks p\u00e4ringut saatma.<\/h2>\n
\n\u300cP\u00e4ringu saatmine\u300d<\/p>\n
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';<\/p>\n
KUSTUTA FROM hoge_table KUS create_time <= 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u300c\u2026\u300d<\/p>\n
\u300c\u2026\u300d<\/p>\n
\u201eHmm... Pole pole puudub. Kas protsess v\u00f5tab palju aega?\u201d \u2014 m\u00f5tlesin ma, kuid igaks juhuks vaatasin grafanasse ja n\u00e4gin, et ketta koormus suurenes v\u00e4ga kiiresti. 
\n\u201eOhtlik\u201d \u2014 m\u00f5tlesin ma veel kord ja peatasin kohe p\u00e4ringu.<\/p>\n
Tegevus 2<\/h2>\n
\nK\u00f5ike anal\u00fc\u00fcsides sain aru, et andmehulka oli liiga suur, et k\u00f5ike \u00fchel korral kustutada.<\/p>\n
Otsustasin kirjutada skripti, mis suudab kustutada umbes 1 000 000 kirjet ja k\u00e4ivitasin selle.<\/p>\n
\u201erakendan skripti\u201d<\/p>\n
\u201eN\u00fc\u00fcd peaks kindlasti toimima,\u201d \u2014 m\u00f5tlesin ma.<\/p>\n
Tegevus 3<\/h2>\n
\nTeine meetod toimis, kuid osutus k\u00fcllalt t\u00f6\u00f6mahukaks.
\nKui k\u00f5ik teha korralikult, ilma liigsete n\u00e4rvideta, kuluks umbes kaks n\u00e4dalat. Kuid see stsenaarium ei vastanud teenuse n\u00f5uetele, seega tuli sellest loobuda.<\/p>\n
Seet\u00f5ttu otsustasin:<\/p>\n
Kopeerime tabeli ja nimetame \u00fcmber.<\/h3>\n
\nEelmise sammu p\u00f5hjal sain aru, et sellise suure andmemahtu kustutamine tekitab sama suure koormuse. Seega otsustasin luua uue tabeli nullist insert'i abil ja kanda sinna andmed, mille kavatsesin kustutada.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nKui luua uus tabel, mis on sama suur kui eespool n\u00e4idatud, siis peaks andmete t\u00f6\u00f6tlemise kiirus olema 1\/7 kiiremini.<\/p>\n
Loomiseks ja \u00fcmbernimetamiseks tabeli, hakkasin seda kasutama peamise tabelina. N\u00fc\u00fcd, kui kustutan 300 miljoni kirje tabeli, peaks k\u00f5ik olema korras.
\nSelgus, et truncate v\u00f5i drop tekitavad v\u00e4iksemat koormust kui delete, ja otsustasin seda meetodit kasutada.<\/p>\n
Tegevus<\/h3>\n
\nDELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';<\/p>\n
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u300c\u2026\u300d
\n\u300c\u2026\u300d
\n\u300cem\u2026\uff1f\u300d<\/p>\n
Tegevus 4<\/h2>\n
\nM\u00f5tlesin, et eelmine idee toimib, kuid p\u00e4rast insert p\u00e4ringu saatmist l\u00e4ks palju viga. MySQL ei hooli.<\/p>\n
Olin juba nii v\u00e4sinud, et hakkasin m\u00f5tlema, et ei taha enam sellega tegeleda.<\/p>\n
M\u00f5tlesin veidi ja sain aru, et v\u00f5ib-olla oli \u00fche korra jaoks too palju insert p\u00e4ringuid...
\nKatsusin saata insert p\u00e4ringu andmemahtudele, mida andmebaas peab 1 p\u00e4evaga t\u00f6\u00f6tlema. \u00d5nnestus!<\/p>\n
Ja p\u00e4rast seda j\u00e4tkame samade andmemahtude p\u00e4ringute saatmist. Kuna peame eemaldama kuu andmemahtu, korrame seda operatsiooni umbes 35 korda.<\/p>\n
Tabeli \u00fcmbernimetamine <\/h3>\n
\nSiin oli \u00f5nn minu poolel: k\u00f5ik l\u00e4ks sujuvalt.<\/p>\n
H\u00e4ired kadusid<\/h3>\n
\nPartii t\u00f6\u00f6tlemise kiirus suurenes.<\/p>\n
Varasemalt v\u00f5ttis see protsess umbes tund aega, n\u00fc\u00fcd kulub umbes 2 minutit. <\/p>\n
P\u00e4rast seda, kui ma veendusin, et k\u00f5ik probleemid on lahendatud, kustutasin 300 miljonit kirjet. Kustutasin tabeli ja tundsin end nagu uuesti s\u00fcndinud.<\/p>\n
Kokkuv\u00f5te<\/h2>\n
\nSain aru, et partii t\u00f6\u00f6tlemise k\u00e4igus j\u00e4i v\u00e4lja p\u00f6\u00f6ramise t\u00f6\u00f6tlemine ja see oli peamine probleem. Selline arhitektuuri viga toob kaasa ajakulu. <\/p>\n
Kas m\u00f5tlete koormusele andmete replikatsiooni ajal, kustutades kirjeid andmebaasist? \u00c4rgem koormake MySQL-i \u00fcle.<\/p>\n
Need, kes tunnevad andmebaase h\u00e4sti, ei puutu sellise probleemiga t\u00f5en\u00e4oliselt kokku. Loodetavasti oli see artikkel teistele kasulik.<\/p>\n
Ait\u00e4h lugemise eest!<\/p>\n
Oleksime v\u00e4ga t\u00e4nulikud, kui r\u00e4\u00e4giksite meile, kas see artikkel meeldis, kas t\u00f5lge oli arusaadav ja kas see oli teile kasulik?<\/i>
\n
Allikas: 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\udd47Lugu 300 miljoni kirje f\u00fc\u00fcsilisest eemaldamisest MySQL-is | ProHoster","description":"Sissejuhatus Tere.","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}