CODEWITHSUNDEEP
mysqlinnodb
AS7K
AS7K

Reputation: 457

Does a MySQL DELETE physically reorder an InnoDB table?

Does a DELETE statement on an InnoDB table in MySQL cause the table to be physically reordered? I have a huge (200 million) row table that I want to delete certain records from. When I delete from it based on a WHERE condition it takes something like 90 minutes but only affects a few hundred thousand rows.

According to this stackoverflow answer, that's because deleting rows causes the table to be physically reordered on the disk: How can I improve DELETE FROM performance on large InnoDB tables?

I used the solution they provided and it worked: Make a new table and insert only the rows you want to keep, then drop the original. BUT! The DBA ran the same straight DELETE on a copy of the database and it worked in 5 minutes. Even with caching I'm surprised.

I can't find any documentation from Oracle or otherwise proving the physical reordering is happening. Does anyone know where I can find documentation stating this is the case? Or some way to prove if this is the cause?

Upvotes: 1

Views: 1382

Answers (1)

Rick James
Rick James

Reputation: 142453

How did you find the rows to delete? Is there an index? If you need to scan the whole table, that takes a lot for 200M rows.

(Assuming InnoDB, ...)

For each row to delete, it does these:

  • Queue up changes to the indexes.
  • Generate undo information, that will need to be cleaned out later.
  • Remove the record from the block where it lives.
  • If a block becomes 'small', coalesce with an adjacent block.

Other comments:

  • If the table is bigger than the buffer_pool, you are likely to incur a lot of I/O.
  • If the rows are randomly arranged, there could be I/O to get to the desired rows.
  • If the key is a UUID, you are very likely to need I/O.
  • If the amount of undo stuff is 'too big', the process slows down be cause of having to work harder to save it.

Another aspect -- if there are SELECTs against the table at the same time, there is some interaction that slows things down.

No, the table is not rebuilt, only little parts may be rearranged.

A possible improvement is to walk through the rows to delete, doing no more than 1000 at a time. COMMIT each change before moving on to the next chunk. More details, and more suggestions.

Upvotes: 3

Related Questions