{"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\/hr\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"Pri\u010da o fizi\u010dkom brisanju 300 milijuna zapisa u MySQL-u","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Uvod<\/h2>\n

\nBok. Ja sam ningenMe, web programer.<\/p>\n

Kako stoji u naslovu, moja pri\u010da je pri\u010da o fizi\u010dkom brisanju 300 milijuna zapisa u MySQL-u.<\/p>\n

Zanimao sam se za to, pa sam odlu\u010dio napraviti bilje\u0161ku (uputu).<\/p>\n

Po\u010detak \u2014 Alert<\/h2>\n

\nU paketu serveru<\/a>, koji koristim i odr\u017eavam, postoji redovni proces koji svakodnevno prikuplja podatke za posljednji mjesec iz MySQL-a. <\/p>\n

Obi\u010dno ovaj proces zavr\u0161i otprilike unutar 1 sata, ali ovaj put nije se zavr\u0161avao 7 ili 8 sati, a alert nije prestajao izlaziti\u2026 <\/a><\/noindex><\/p>\n

Istra\u017eivanje uzroka<\/h2>\n

\nPoku\u0161ao sam ponovno pokrenuti proces, pogledati logove, ali nisam vidio ni\u0161ta ozbiljno.
\nUpit je bio ispravno indeksiran. No, kada sam razmi\u0161ljao \u0161to ne uspijeva, shvatio sam da je veli\u010dina baze podataka prili\u010dno velika. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 milijuna zapisa. \u010cini se da je indeksacija radila ispravno, samo jako sporo.<\/p>\n
Zahtevani skup podataka za mjesec iznosio je oko 12 000 000 zapisa. Izgleda da je select naredba potrajala dugo vremena, a transakcija se nije izvr\u0161avala. <\/p>\n
DB<\/h2>\n
\nU su\u0161tini, to je tablica koja se svaki dan pove\u0107ava otprilike za 400 000 zapisa. Baza je trebala prikupljati podatke samo za posljednji mjesec, stoga je procjena bila da \u0107e mo\u0107i podnijeti upravo taj volumen podataka, ali, na\u017ealost, operacija rotacije nije bila uklju\u010dena.<\/p>\n
Ova baza podataka nije bila razvijena od strane mene. Primio sam je od drugog programera, pa je ostao dojam tehni\u010dkog duga. <\/p>\n
Do\u0161ao je trenutak kada je volumen podataka koji su se dnevno unosili postao velik i napokon dostigao limit. Pretpostavlja se da bi se s tako velikim volumenom podataka trebali dijeliti, ali to, na\u017ealost, nije napravljeno.<\/p>\n
I tu sam ja u\u0161ao na scenu.<\/p>\n
Ispravak<\/h2>\n
\nBilo je racionalnije smanjiti samu bazu podataka i skratiti vrijeme obrade, nego mijenjati samu logiku.<\/p>\n
Situacija bi se trebala zna\u010dajno promijeniti ako obri\u0161em 300 milijuna zapisa, pa sam odlu\u010dio tako i u\u010diniti\u2026 Ah, mislio sam da \u0107e to zasigurno upaliti.<\/p>\n
Akcija 1<\/h2>\n
\nNakon \u0161to sam pripremio pouzdanu sigurnosnu kopiju, kona\u010dno sam po\u010deo slati upite.<\/p>\n
\u300cSlanje upita\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
\u201cHmmm\u2026 Nema odgovora. Mo\u017eda proces traje dugo?\u201d \u2014 pomislio sam, ali iz predostro\u017enosti sam pogledao u grafanu i vidio da je optere\u0107enje diska brzo raslo. 
\n\"Jako opasno\" \u2014 pomislio sam jo\u0161 jednom i odmah zaustavio zahtjev.<\/p>\n
Akcija 2<\/h2>\n
\nAnaliziraju\u0107i sve, shvatio sam da je volumen podataka bio prevelik da bi se sve obrisalo odjednom.<\/p>\n
Odlu\u010dio sam napisati skriptu koja mo\u017ee obrisati oko 1 000 000 unosa i pokrenuo je.<\/p>\n
\"realiziram skriptu\"<\/p>\n
\"Sad sigurno radi\", \u2014 pomislio sam<\/p>\n
Akcija 3<\/h2>\n
\nDruga metoda je upalila, ali se pokazala vrlo zahtjevnom.
\nDa sve odradim uredno, bez dodatnog stresa, trebalo bi mi oko dva tjedna. No, ipak, ovaj scenarij nije ispunjavao zahtjeve usluge, pa sam se morao od njega odmaknuti.<\/p>\n
Stoga, ovo je \u0161to sam odlu\u010dio u\u010diniti:<\/p>\n
Kopiramo tablicu i preimenujemo je<\/h3>\n
\nIz prethodnog koraka shvatio sam da brisanje tako velikog volumena podataka stvara istu toliku optere\u0107enost. Zato sam odlu\u010dio stvoriti novu tablicu od nule pomo\u0107u insert i u nju premjestiti podatke koje sam planirao obrisati.<\/p>\n
| hoge_table     | 350'000'000|\n| tmp_hoge_table |  50'000'000|<\/code><\/pre>\n
\nAko napravim novu tablicu jednake veli\u010dine kao \u0161to je navedeno iznad, brzina obrade podataka tako\u0111er bi trebala biti 1\/7 br\u017ea.<\/p>\n
Kreirav\u0161i tablicu i preimenuju\u0107i je, po\u010deo sam je koristiti kao glavnu (master) tablicu. Sada, ako obri\u0161em tablicu s 300 milijuna unosa, sve bi trebalo biti u redu.
\nSaznao sam da truncate ili drop stvaraju manji teret od delete, pa sam odlu\u010dio koristiti ovaj na\u010din.<\/p>\n
Izvr\u0161enje<\/h3>\n
\n\u300cSlanje upita\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
Akcija 4<\/h2>\n
\nMislim da \u0107e prethodna ideja upaliti, ali nakon slanja insert zahtjeva pojavila se vi\u0161estruka gre\u0161ka. MySQL ne pra\u0161ta.<\/p>\n
Ve\u0107 sam bio toliko umoran da sam po\u010deo razmi\u0161ljati da vi\u0161e ne \u017eelim ovo raditi.<\/p>\n
Sjedio sam, razmi\u0161ljao i shvatio da je mo\u017eda za jedan put bilo previ\u0161e insert zahtjeva\u2026
\nPoku\u0161ao sam poslati insert zahtjev za koli\u010dinu podataka koju baza mora obraditi za 1 dan. Uspeo sam!<\/p>\n
Nakon toga nastavljamo slati zahtjeve za istu koli\u010dinu podataka. Kako trebamo obrisati mjese\u010dnu koli\u010dinu podataka, ponavljamo ovu operaciju otprilike 35 puta.<\/p>\n
Preimenovanje tablice <\/h3>\n
\nOvdje je sre\u0107a bila na mojoj strani: sve je pro\u0161lo glatko.<\/p>\n
Alert su nestali<\/h3>\n
\nBrzina obrade serije se pove\u0107ala.<\/p>\n
Ranije je ovaj proces trajao oko sat vremena, sada traje otprilike 2 minute. <\/p>\n
Nakon \u0161to sam se uvjerio da su svi problemi rije\u0161eni, izbrisao sam 300 milijuna zapisa. Obri\u0161ao sam tablicu i osje\u0107ao se kao da sam se ponovno rodio.<\/p>\n
Sa\u017eetak<\/h2>\n
\nShvatio sam da je prilikom obrade serija propu\u0161teno rotiranje, \u0161to je bila glavna problema. Takva gre\u0161ka u arhitekturi dovodi do bespotrebnog tro\u0161enja vremena. <\/p>\n
Razmi\u0161ljate li o optere\u0107enju prilikom replikacije podataka, kada bri\u0161ete zapise iz baze? Ne preoptere\u0107ujmo MySQL.<\/p>\n
Oni koji dobro razumiju baze podataka sigurno se ne\u0107e susresti s takvim problemom. A ostalima, nadam se, ovaj \u010dlanak bio od koristi.<\/p>\n
Hvala na \u010ditanju!<\/p>\n
Bit \u0107emo vrlo sretni ako nam ka\u017eete svi\u0111a li vam se ovaj \u010dlanak, je li prijevod jasan i je li vam bio koristan?<\/i>
\n
Izvor: 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\udd47Pri\u010da o fizi\u010dkom brisanju 300 milijuna zapisa u MySQL | ProHoster","description":"Uvod Pozdrav.","canonical_url":"https:\/\/prohoster.info\/hr\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"hr_HR","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\/hr\/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\/hr\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}