{"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":"Jutt f\u00fc\u00fcsilisest kustutamisest 300 miljoni kirje osas 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 lood f\u00fc\u00fcsilisest kustutamisest 300 miljoni kirje osas MySQL-is.<\/p>\n

Olin sellest huvitatud, seega otsustasin koostada m\u00e4rkme (juhendi).<\/p>\n

Algus \u2014 Alert<\/h2>\n

\nPaketis serveril<\/a>, mida ma kasutan ja hooldan, on regulaarne protsess, mis kogub igal p\u00e4eval eelmise kuu andmeid MySQL-ist. <\/p>\n

Tavaliselt l\u00f5petatakse see protsess umbes 1 tunni jooksul, kuid seekord see ei l\u00f5petanud 7 v\u00f5i 8 tunni jooksul, ja h\u00e4ire ei lakanud \u2026 <\/a><\/noindex><\/p>\n

P\u00f5hjuse otsimine<\/h2>\n

\nProovisin protsessi k\u00e4ivitada uuesti, vaatasin logisid, kuid ei n\u00e4inud midagi t\u00f5sist.
\nP\u00e4ring indekseerus \u00f5igesti. Kuid kui m\u00f5tlesin, mis valesti l\u00e4heb, sain aru, et andmebaasi maht on \u00fcsna suur. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 miljonit kirjet. Tundub, et indekseerimine toimis \u00f5igesti, lihtsalt v\u00e4ga aeglaselt.<\/p>\n
N\u00f5utav andmekogumine kuus oli umbes 12 000 000 kirjet. Tundub, et select k\u00e4sk v\u00f5ttis palju aega ning tehing ei t\u00e4idetud pikka aega. <\/p>\n
DB<\/h2>\n
\nSisult on tabel, mis suureneb iga p\u00e4ev umbes 400 000 kirje v\u00f5rra. Andmebaas pidi koguma andmeid ainult viimase kuu jooksul, seega oli arvutus tehtud selle n\u00e4kku, et talub just seda andmemahtu, kuid kahjuks ei olnud rotate operatsioon aktiveeritud.<\/p>\n
See andmebaas ei olnud minu loodud. V\u00f5tsin selle \u00fcle teiselt arendajalt, seega j\u00e4i tunne, et see on tehniline v\u00f5lg. <\/p>\n
J\u00f5udis k\u00e4tte hetk, mil p\u00e4evaseid andmeid hakkas tekkima liiga palju ja see l\u00f5puks saavutas piiri. Eeldatakse, et t\u00f6\u00f6tades nii suure andmemahtuga peaks need jagama, kuid seda kahjuks ei tehtud.<\/p>\n
Ja siis astusin mina m\u00e4ngu.<\/p>\n
Parandus<\/h2>\n
\nOli ratsionaalsem v\u00e4hendada andmebaasi ennast ja l\u00fchendada selle t\u00f6\u00f6tlemise aega kui muuta kogu loogikat.<\/p>\n
Olukord peaks oluliselt muutuma, kui kustutada 300 miljonit kirjet, seega otsustasin nii teha\u2026 Oh, ma arvasin, et see kindlasti t\u00f6\u00f6tab.<\/p>\n
Tegevus 1<\/h2>\n
\nUsaldusv\u00e4\u00e4rse varukoopia valmistamine, alustasin l\u00f5puks p\u00e4ringute saatmist.<\/p>\n
\u300cP\u00e4ringu saatmine\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
Hmm... Ei mingit vastust. V\u00f5ib-olla protsess v\u00f5tab liiga kaua aega?\" \u2014 m\u00f5tlesin ma, kuid igaks juhuks vaatasin grafanas ja n\u00e4gin, et kettaruumi kasutus kasvas v\u00e4ga kiiresti. 
\n\"Ohtlik,\" \u2014 m\u00f5tlesin ma veel kord ja peatasin kohe p\u00e4ringu.<\/p>\n
Tegevus 2<\/h2>\n
\nKuna olin k\u00f5ik anal\u00fc\u00fcsinud, m\u00f5istsin, et andmete maht oli liiga suur, et k\u00f5ik \u00fchekorraga kustutada.<\/p>\n
Otsustasin kirjutada skripti, mis suudab kustutada umbes 1 000 000 kirjet ja k\u00e4ivitasin selle.<\/p>\n
\u201erakendan skripti\u201c<\/p>\n
\"N\u00fc\u00fcd kindlasti t\u00f6\u00f6tab,\" \u2014 m\u00f5tlesin ma.<\/p>\n
Tegevus 3<\/h2>\n
\nTeine meetod t\u00f6\u00f6tas, kuid osutus v\u00e4ga t\u00f6\u00f6mahukaks.
\nEt k\u00f5ik korralikult teha, ilma liigsete n\u00e4rvideta, kuluks umbes kaks n\u00e4dalat. Kuid see stsenaarium ei vastanud siiski teenuse n\u00f5uetele, seega pidin sellest loobuma.<\/p>\n
Seega otsustasin, et \"<\/p>\n
Kopeerime tabeli ja nimetame \u00fcmber.<\/h3>\n
\nEelmise sammu p\u00f5hjal m\u00f5istsin, et sellise suure andmemahtude kustutamine tekitab sama suure koormuse. Seega otsustasin luua uue tabeli algusest peale, kasutades insert'i, ja sinna viia andmed, mille plaanisin kustutada.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nKui teha uus tabel sama suur nagu eespool m\u00e4rgitud, peaks andmete t\u00f6\u00f6tlemise kiirus olema 1\/7 v\u00f5rra kiirem.<\/p>\n
P\u00e4rast tabeli loomist ja \u00fcmbernimetamise alustasin selle kasutamist peamise tabelina. N\u00fc\u00fcd, kui ma kustutan tabeli, kus on 300 miljonit kirjet, peaks k\u00f5ik olema korras.
\nSelgus, et truncate v\u00f5i drop tekitavad v\u00e4hem koormust kui delete, ja otsustasin seda meetodit kasutada.<\/p>\n
T\u00e4itmises<\/h3>\n
\n\u300cP\u00e4ringu saatmine\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\u300c\u044d\u043c…\uff1f\u300d<\/p>\n
Tegevus 4<\/h2>\n
\nArvasin, et eelmine idee t\u00f6\u00f6tab, kuid p\u00e4rast insert p\u00e4ringu saatmist tekkis hulk vigu. MySQL ei halasta.<\/p>\n
Olen juba nii v\u00e4sinud, et olen hakanud m\u00f5tlema, et ma ei taha enam sellega tegeleda.<\/p>\n
M\u00f5tlesin veidi ja sain aru, et v\u00f5ib-olla oli \u00fche korra jaoks liiga palju insert p\u00e4ringuid...
\nProovisin saata insert p\u00e4ringu andmemahtu, mille andmebaas peaks \u00fche p\u00e4eva jooksul t\u00f6\u00f6tlema. \u00d5nnestus!<\/p>\n
Ja p\u00e4rast seda j\u00e4tkame p\u00e4ringute saatmist sama andmemahu kohta. Kuna peab eemaldama kuu andmemahtu, korrame seda protseduuri umbes 35 korda.<\/p>\n
Tabeli \u00fcmbernimetamine <\/h3>\n
\nSiin oli \u00f5nn minu poolel: k\u00f5ik l\u00e4ks ladusalt.<\/p>\n
Alert kadusid<\/h3>\n
\nPartii t\u00f6\u00f6tlemise kiirus on kasvanud.<\/p>\n
Varem kestis see protsess umbes tund, n\u00fc\u00fcd kulub umbes 2 minutit. <\/p>\n
Kui olin veendunud, et k\u00f5ik probleemid on lahendatud, eemaldasin 300 miljonit kirjet. Kustutasin tabeli ja tundsin end taas s\u00fcndinuna.<\/p>\n
Kokkuv\u00f5te<\/h2>\n
\nM\u00f5istsin, et partii t\u00f6\u00f6tlemisel j\u00e4eti k\u00f5rvale p\u00f6\u00f6ramisprotsess, ja selles peitus peamine probleem. Selline arhitektuuri viga p\u00f5hjustab aega raiskamist. <\/p>\n
Kuidas te m\u00f5tlete andmete replikatsiooni koormusele, kustutades kirjeid andmebaasist? \u00c4rge koormake MySQL-i.<\/p>\n
Need, kes tunnevad andmebaase h\u00e4sti, ei puutu selliste probleemidega t\u00f5en\u00e4oliselt kokku. Ja \u00fclej\u00e4\u00e4nutele, loodan, et see artikkel oli kasulik.<\/p>\n
Ait\u00e4h lugemise eest!<\/p>\n
Oleksime v\u00e4ga r\u00f5\u00f5msad, kui r\u00e4\u00e4giksite meile, kas see artikkel meeldis teile, kas t\u00f5lge oli selge, 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 andme rea f\u00fc\u00fcsilisest kustutamisest MySQL-is | ProHoster","description":"Sissejuhatus Tere. Mina olen ningenMe, veebiarendaja. Nagu pealkiri \u00fctleb, on minu lugu 300 miljoni andme rea f\u00fc\u00fcsilisest kustutamisest MySQL-is. See teema huvitas mind, seega otsustasin koostada m\u00e4rkme (juhendi). Algus \u2014 Teade Pakett serveris, mida ma kasutan ja hooldan, on regulaarne protsess, mis korjab andmeid viimase kuu kohta kord p\u00e4evas.","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. \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","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"},"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}]}}