Introduction
Hello. I am ningenMe, a web developer.
As the title states, my story is about the physical deletion of 300 million records in MySQL.
I became interested in this, so I decided to create a memo (instruction).
Beginning — Alert
In the batch server, which I use and maintain, there is a regular process that collects data from MySQL for the last month once a day.
Usually this process takes about 1 hour to finish, but this time it didn’t complete for 7 or 8 hours, and the alert kept popping up...
Finding the reason
I tried restarting the process, looking at the logs, but I didn’t see anything unusual.
The query was indexed correctly. But when I thought about what was going wrong, I realized that the size of the database was quite large.
hoge_table | 350'000'000 |350 million records. It seems indexing was working correctly, just very slowly.
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’t executing for a long time.
DB
Essentially, 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.
This database wasn’t designed by me. I inherited it from another developer, so there’s a sense of technical debt left.
The moment came when the volume of data being inserted daily grew large and finally reached its limit. It’s supposed to be that when dealing with such a large amount of data, they should be partitioned, but unfortunately, that was not done.
And that’s when I came into the picture.
Correction
It made more sense to reduce the database itself and shorten the processing time than to change the logic itself.
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.
Action 1
Having prepared a reliable backup, I finally started sending the queries.
「Sending the query」
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';「…」
「…」
“Hmm... No response. Maybe the process takes too long?” — I thought, but just to be sure, I glanced at grafana and saw that the disk load was rising very quickly.
“Risky” — I thought once more and immediately stopped the query.
Action 2
After analyzing everything, I realized that the volume of data was too large to delete all at once.
I decided to write a script that could delete around 1,000,000 records and I launched it.
「I will implement the script」
“Now it will definitely work,” I thought.
Action 3
The second method worked, but it turned out to be very labor-intensive.
To 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.
So, here's what I decided to do:
We copy the table and rename it
From 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.
| hoge_table | 350,000,000|
| tmp_hoge_table | 50,000,000|If we create a new table the same size as indicated above, the data processing speed should also become 1/7 faster.
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.
I learned that truncate or drop creates less load than delete, so I decided to use this method.
Execution
「Sending the query」
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS'; 「…」
「…」
「Um…？」
Action 4
I thought the previous idea would work, but after sending the insert request, a multiple error occurred. MySQL shows no mercy.
I was so exhausted that I began to think that I no longer wanted to deal with this.
I sat down and thought, realizing that maybe there were too many insert requests for just one time...
I tried to send an insert request for the volume of data that the database should process in one day. It worked!
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.
Renaming the table
Here, luck was on my side: everything went smoothly.
Alerts disappeared
The batch processing speed increased.
Previously, this process took about an hour; now it takes approximately 2 minutes.
After I made sure that all the issues were resolved, I dropped 300 million records. I deleted the table and felt reborn.
Summarization
I 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.
Are you considering the load when replicating data by deleting records from the database? Let’s not overload MySQL.
Those who have a good understanding of databases will not encounter such a problem. I hope this article was helpful for others.
Thank you for reading!
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.
Source: habr.com
