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?
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:
As you can see, space is re-used, but never given back. That's the key point to your question.