maria-discuss team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

If your table uses auto-increment field as primary key, then
possibility to reuse the space depends on how many rows you delete.
When a page in the clustered index contains too few rows, InnoDB will
merge it with an adjacent page, and then this space will become
available for reuse by a new page in the tail of the index.

Yes, "table bloat" will reduce performance of reads, because InnoDB
buffer pool is based on pages, so the fewer active rows in the pages,
the fewer active data fits into the buffer pool and InnoDB needs to
read from disk more often. But at the same time if you do a lot of
inserts/updates in the middle of the index, then lack of "table bloat"
will reduce performance of those operations, because InnoDB will have
to split pages to do them.

On Fri, Jul 27, 2018 at 4:01 PM Arlindo Neto <neto.acs@xxxxxxxxx> wrote:
>
> 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