Does the space occupied by deleted rows get re-used?
I have read several times that after you delete a row in an InnoDB table in MySQL, its space is not reused, so if you make a lot of INSERTs into a table and then periodically DELETE some rows the table will use more and more space on disk, as if the rows were not deleted at all.
Recently I've been told though that the space occupied by deleted rows is re-used but only after some transactions are complete and even then - not fully. I am now confused.
Can someone please make sense of this to me? I need to do a lot of INSERTs into an InnoDB table and then every X minutes I need to DELETE records that are more than Y minutes old. Do I have a problem of ever-growing InnoDB table here, or is it paranoia?
Answer
It is paranoia :)
DB's don't grow in size unnecessarily, but for performance issues space is not freed either.
What you've heard most probably is that if you delete records that space is not given back to the Operating System. Instead, it's kept as an empty space for the DB to re-use afterwards.
This is because:
- DB needs to have some HD space to save its data; if it doesn't have any space, it reserves some empty space at first.
- When you insert a new row, a piece of that space is used.
- When you run out of free space, a new block is reserved, and so on.
- Now, when you delete some rows, in order to prevent reserving more and more blocks, its space is kept free but never given back to the Operating System, so you can use it again later without any need of reserving new blocks.
As you can see, space is re-used, but never given back. That's the key point to your question.