{"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\/en\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","title":{"rendered":"The Story of Physically Deleting 300 Million Records in MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Introduction<\/h2>\n

\nHello. I am ningenMe, a web developer.<\/p>\n

As the title states, my story is about the physical deletion of 300 million records in MySQL.<\/p>\n

I became interested in this, so I decided to create a memo (instruction).<\/p>\n

Beginning \u2014 Alert<\/h2>\n

\nIn the batch server<\/a>, which I use and maintain, there is a regular process that collects data from MySQL for the last month once a day. <\/p>\n

Usually this process takes about 1 hour to finish, but this time it didn\u2019t complete for 7 or 8 hours, and the alert kept popping up... <\/a><\/noindex><\/p>\n

Finding the reason<\/h2>\n

\nI tried restarting the process, looking at the logs, but I didn\u2019t see anything unusual.
\nThe query was indexed correctly. But when I thought about what was going wrong, I realized that the size of the database was quite large. <\/p>\n

hoge_table | 350'000'000 |<\/code><\/pre>\n
\n350 million records. It seems indexing was working correctly, just very slowly.<\/p>\n
The required data collection for the month was about 12,000,000 records. It looks like the select command took a long time, and the transaction wasn\u2019t executing for a long time. <\/p>\n
DB<\/h2>\n
\nEssentially, this is a table that enlarges by about 400,000 records every day. The database was supposed to gather data only for the last month, so the expectation was that it would handle this amount of data, but unfortunately, the rotate operation was not enabled.<\/p>\n
This database wasn\u2019t designed by me. I inherited it from another developer, so there\u2019s a sense of technical debt left. <\/p>\n
The moment came when the volume of data being inserted daily grew large and finally reached its limit. It\u2019s supposed to be that when dealing with such a large amount of data, they should be partitioned, but unfortunately, that was not done.<\/p>\n
And that\u2019s when I came into the picture.<\/p>\n
Correction<\/h2>\n
\nIt made more sense to reduce the database itself and shorten the processing time than to change the logic itself.<\/p>\n
The situation should change significantly if I delete 300 million records, so I decided to do just that... Sigh, I thought this would definitely work.<\/p>\n
Action 1<\/h2>\n
\nHaving prepared a reliable backup, I finally started sending the queries.<\/p>\n
\u300cSending the query\u300d<\/p>\n
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u201c\u2026\u201d<\/p>\n
\u201c\u2026\u201d<\/p>\n
\u201cHmm... No response. Maybe the process takes too long?\u201d \u2014 I thought, but just to be sure, I glanced at grafana and saw that the disk load was rising very quickly. 
\n\u201cRisky\u201d \u2014 I thought once more and immediately stopped the query.<\/p>\n
Action 2<\/h2>\n
\nAfter analyzing everything, I realized that the volume of data was too large to delete all at once.<\/p>\n
I decided to write a script that could delete around 1,000,000 records and I launched it.<\/p>\n
\u300cI will implement the script\u300d<\/p>\n
\u201cNow it will definitely work,\u201d I thought.<\/p>\n
Action 3<\/h2>\n
\nThe second method worked, but it turned out to be very labor-intensive.
\nTo do everything carefully, without unnecessary stress, it would take about two weeks. However, this scenario did not meet the service requirements, so I had to abandon it.<\/p>\n
So, here's what I decided to do:<\/p>\n
We copy the table and rename it<\/h3>\n
\nFrom the previous step, I realized that deleting such a large volume of data creates a similarly large load. Therefore, I decided to create a new table from scratch using insert and move the data that I planned to delete into it.<\/p>\n
| hoge_table     | 350,000,000|\n| tmp_hoge_table |  50,000,000|<\/code><\/pre>\n
\nIf we create a new table the same size as indicated above, the data processing speed should also become 1\/7 faster.<\/p>\n
After creating the table and renaming it, I started using it as the master table. Now, if I delete a table with 300 million records, everything should be fine.
\nI learned that truncate or drop creates less load than delete, so I decided to use this method.<\/p>\n
Execution<\/h3>\n
\n\u300cSending the query\u300d<\/p>\n
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';<\/code><\/pre>\n
\n\u201c\u2026\u201d
\n\u201c\u2026\u201d
\n\u201cem\u2026?\u201d<\/p>\n
Action 4<\/h2>\n
\nI thought the previous idea would work, but after sending the insert request, a multiple error occurred. MySQL shows no mercy.<\/p>\n
I was so exhausted that I began to think that I no longer wanted to deal with this.<\/p>\n
I sat down and thought, realizing that maybe there were too many insert requests for just one time...
\nI tried to send an insert request for the volume of data that the database should process in one day. It worked!<\/p>\n
After that, we continue to send requests for the same volume of data. Since we need to remove a month's worth of data, we repeat this operation about 35 times.<\/p>\n
Renaming the table <\/h3>\n
\nHere, luck was on my side: everything went smoothly.<\/p>\n
Alerts disappeared<\/h3>\n
\nThe batch processing speed increased.<\/p>\n
Previously, this process took about an hour; now it takes approximately 2 minutes. <\/p>\n
After I made sure that all the issues were resolved, I dropped 300 million records. I deleted the table and felt reborn.<\/p>\n
Summarization<\/h2>\n
\nI realized that the rotate processing was missed during batch processing, and that was the main issue. Such an architectural mistake leads to a complete waste of time. <\/p>\n
Are you considering the load when replicating data by deleting records from the database? Let\u2019s not overload MySQL.<\/p>\n
Those who have a good understanding of databases will not encounter such a problem. I hope this article was helpful for others.<\/p>\n
Thank you for reading!<\/p>\n
We would be very pleased if you could tell us whether you liked this article, if the translation was clear, and if it was useful to you.<\/i>
\n
Source: 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\udd47The story of physically deleting 300 million records in MySQL | ProHoster","description":"Introduction Hello.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/istoriya-o-fizicheskom-udalenii-300-millionov-zapisej-v-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","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\/en\/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\/en\/wp-json\/wp\/v2\/posts\/95518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=95518"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/95518\/revisions"}],"predecessor-version":[{"id":159882,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/95518\/revisions\/159882"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=95518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=95518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=95518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}