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@google.com> 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@gmail.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp