0

we have a project with a huge MySQL table (MySQL version 5.7.35-0 on Ubuntu 18.04.1 using InnoDB) table. By Huge I mean approximately 100M of rows 300GB.

I want to do some pruning there. This can be achieved by this SQL query:

DELETE FROM Commit WHERE current=0;

But this will lock up the database and I need to terminate the process by force. I have an index on column current. I can split up the query into chunks:

DELETE FROM Commit WHERE current=0 LIMIT 100;

If I am deleting 1 row it is done immediately if 100 it takes few milliseconds if 1000 it takes a second, if 10000 approximately a minute, and unproportionally more and more (not linearly).

Is there some smart way how to iterate this query again and again until there is no row with this condition left? And without locking up the server?

Or can I somehow modify the strategy of deleting to do it in one query?

Improve this question
7
  • Just out of curiousity, how long does it take to do CREATE TABLE new_commit AS SELECT * FROM commit WHERE current <> 0;?
    – user16577935
    Commented Aug 4, 2021 at 17:06
  • You can't do it, since deleting the record is going to lock the underlying table anyways.
    – Danyal Imran
    Commented Aug 4, 2021 at 17:46
  • @coyeb60297 I am now a bit locked to do this because for this we have no disk space. But recreation can be a great idea.
    – Pavol Hejný
    Commented Aug 4, 2021 at 18:03
  • Batching is one way of doing it. I.e. deleting x amount of records in a sequence and repeat that sequence every 3 seconds or so. Another way (which should be way faster, is to create a temporary table that holds all the records you wish to keep, truncate the original, then re-insert the records from the temporary table into the original).
    – Martin
    Commented Aug 4, 2021 at 18:13
  • Iteration until none left -> mysql_affected_rows aka ROW_COUNT() until it is 0.
    – danblack
    Commented Aug 5, 2021 at 2:46

1 Answer 1

Reset to default
0

If you are deleting most of the table (and you have enough spare disk space), copy the rows to keep into an new table and use RENAME TABLE.

Else, walk thru the PRIMARY KEY of the table 1K rows at a time, deleting whatever rows in that chunk need deleting.

More discussion of both techniques, plus others: http://mysql.rjweb.org/doc.php/deletebig

Yes, your first attempt led to a very long lock.

Yes, your second attempt took longer and longer. (The cure in the link above is to "remember where you left off".) Yes, non-linear, probably "quadratic" -- about 100M * 100M / 100, which equals a huge number. (Caveat: If the Optimizer actually uses the index, the situation may not be this dire.)

Do it in a single query -- No. Why does that matter? (Based on your answer to that question, I will explain why some alternative is "good enough".)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.