maria-discuss team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

It's actually a little different: when you issue DELETE, InnoDB marks
the rows as deleted and that's it. The space occupied by the deleted
rows will be reused later whenever new rows are inserted or when other
rows grow in size. There are no threads doing compaction, and InnoDB
won't release disk space to OS even if you delete all rows from the
table (the space will be released though if you execute TRUNCATE
TABLE). So if you deleted a lot of rows from the table and expect that
there will be no similar-sized additions to the table any time soon,
then you'll need to execute OPTIMIZE TABLE to release the disk space
occupied by the table. If your table sees roughly the same amount of
deletes and inserts (or it sees more inserts than deletes), then don't
bother, InnoDB will eventually reuse the space occupied by the deleted
rows.

Pavel

On Fri, Jul 27, 2018 at 12:07 PM Arlindo Neto <neto.acs@xxxxxxxxx> wrote:
>
> Hi everyone.
>
> I have some questions regarding InnoDB deletion process.
>
> As far as I know, when we issue a DELETE command to a row stored in a InnoDB table, the row contents are stored in the UNDO Log, and the contents of the row are only deleted when the last transaction after the DELETE transaction has finished. But what actually happens to the row that was deleted? Is any disk space ever released to the OS, or are the contents simply erased? Defragmenting a table is only done through OPTIMIZE TABLE, or are there any automatic threads responsible for rearranging the rows? And if the are no such threads, how do we maintain tables which suffer from many deletions?
>
> I hope I made myself clear. Thanks in advance,
>
>     Arlindo Neto.
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp