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?

Cleanlimbed

Answer 1

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.

Patroon

Seb, can you please given any official link for this? – Duren

@GautamSomani This is a 6 years old answer, I don't have links at hand but I could find this: dev.mysql.com/doc/refman/5.1/en/optimize-table.html: Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly. I'm sure if you google you'll be able to find more official info about this. – Patroon

Answer 2

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.

Evidential

There are solutions to this, with newer InnoDB versions and the right configuration (innodb_file_per_table): dev.mysql.com/doc/innodb-plugin/1.0/en/… dev.mysql.com/doc/refman/5.5/en/… – Berm

Answer 3

I have a suggestion, because there are so many unknowns. Chief among them being version of sql that you are using.

Knowing how much over head would be required to track space used by small entries that are deleted and then SQL looking for some new entry that would use some or all of that space, it seems to me some optimization code might deem the space should just become dead space and not re-used. if entry to identify the deleted space takes more temporary space than is recovered from the deleted area, there might be a point where deletes are done, but that space is left without any links to it, which would result in Database growing in size.

I'd recommend creating a temporary database, and run some test code over the weekend that reproduced what you are doing which is deleting an entry or many, adding new entries that might/might not be exactly the same size, and let it run over the weekend.

Check database size the next day.

This way you know 100% what will happen.

Seismograph

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Cubby