maria-discuss team mailing list archive

[Arlindo Neto <neto.acs@xxxxxxxxx>]

Nice. Thanks Pavel.

A few more questions though: Can InnoDB reuse this space by adding new rows
with AUTO_INCREMENT primary key when our table use clustered indexes? As
new rows are added, wouldn't the table structure
organize the data based on the primary key, preventing the disk space
containing the old row from being written again? Also, when talking about
large tables, could this "table bloat" impact performance in, for instance,
sequential scans?

Thanks again,
    Arlindo Neto.

Em sex, 27 de jul de 2018 às 18:35, Pavel Ivanov <pivanof@xxxxxxxxxx>
escreveu:

> 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
>