Hi Arlindo, 2018-07-27 22:06 GMT+03:00 Arlindo Neto <neto.acs@gmail.com>:
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.
That is true. History will be preserved in undo logs and index trees, so that any active read views associated with transactions that started before the transaction (which made changes) committed, will have a consistent read view, as if the DELETE did not take place. So, DELETE is a special form of UPDATE, which will only update the delete-mark flag in the index records. The oldest active transaction read view is also called the "purge view". Anything older than that may be purged, but purge might also be lagging a little bit behind. The only case when records can actually be deleted outside purge is transaction rollback. A "fresh insert" can be rolled back by immediately deleting the index records. But, an insert may also be performed by updating a delete-marked purgeable record. In that case, the rollback will have to do a purge-like check: delete the record if it is not visible in any purge view. (This is a bit tricky for secondary indexes, because secondary index records do not contain a per-record transaction identifier.) I explained some of this in the "Deep Dive" talk at M18: https://vimeo.com/258533150 https://docs.google.com/presentation/d/1bP3yh57B58yfGDd-34TZ5MeiacIdaseaTLpd...
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?
InnoDB keeps a PAGE_FREE stack of purged records. An insert would allocate space from the top of that stack unless deleted record was smaller than the one that is about to be inserted. It would fall back to allocating new space from the "page heap". If the page would get too full, and if the record would fit after removing the garbage from the page, then there would be a btr_page_reorganize() operation that would rebuild the page, followed by an insert. If the insert does not fit, then the page would be split. Page merges will typically be done lazily. MySQL 5.7 (and MariaDB 10.2) introduced the table comment keyword MERGE_THRESHOLD for controlling it.
Defragmenting a table is only done through OPTIMIZE TABLE, or are there any automatic threads responsible for rearranging the rows?
In MariaDB 10.1 and later, there is an option. After innodb_defragment=ON, OPTIMIZE TABLE would not rebuild the table, but instead invoke a defragmentation operation. This would not shrink the data file, however. By default, OPTIMIZE TABLE does rebuild the table. It is supported as an online operation, but the log of concurrent DML operations would increase the storage requirements
And if the are no such threads, how do we maintain tables which suffer from many deletions?
Generally, it could be good to avoid mass deletions with InnoDB when possible. Alternatives could be to partition the table in a suitable way if possible, and then use DROP PARTITION for the mass deletions. Or, you could copy the "surviving" records to a new table and then drop the old one. I hope that this helps. Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation