Innodb: does the space occupied by deleted rows get re-used?

Question

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?

Thank you.

Answer 1

This could hint at you should do:

From http://tag1consulting.com/InnoDB_Performance_Tuning:

Don't empty a table with DELETE FROM or TRUNCATE

Emptying a large table using DELETE FROM or TRUNCATE is slow on InnoDB. This is because for both operations InnoDB has to process each row of the table, and due to its transactional design it first writes each delete action to the transaction log then applies it to the actual table. For better performance if not limited by foreign keys, use DROP TABLE followed by CREATE TABLE to empty a large table.

Answer 2

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.

Answer 3

in innodb, there is no practical way of freeing up the space.

• use per table ibdata file, that will enable you to delete record copy the data to a new table and delete old table, thus recovering records.
• use mysqldump and whole lots of receipe to clean up the whole server. Check following:
  http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

All of these methods become impractical when you are using huge tables(in my case they are more than 250GB) and you must keep them deleting records to better performance.

You will have to seriously think, whether you have enough space on your harddisk to perform one of the above function (in my case I do not think 1TB is enough for all these actions)

with Innotab table (and mysql itself) the option are fairly limited if have serious database size.