I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution.
What I did see is that after some executions the table starts to respond very slow and the delition no longer respond, for example:
- First itaration, 30k records to delete, time 35 seconds
- Second iteration, 126k records to delete, time 3.4 mins
- Third iteration, 350k records to delete, time 24 mins
Every iteration i place a commit every 10k record. So first iteration need to delete 30k records and commit every 10k records.
I see that every time i execute the procedure the table grow (and does not decrease, what i was expecting…)
I'm trying to find a good solution before this post, with no luck.
I cannot delete and re-create the table with less records, so i need to find a way to lower down the records on the table.
How i can do to delete all this records maintaining the performance? Free space, "well built indexes" etc
I can give you more details if needed
Best Answer
For a "large" delete (more than, say, half the table), do something like:
The
INSERT..SELECT
is slow; the other steps are fast. No need to "shrink" unless the original table was created withinnodb_file_per_table=OFF
, in which case the disk usage will increase with this process.More big-delete tips for more cases: http://mysql.rjweb.org/doc.php/deletebig