maria-discuss team mailing list archive

[Marko Mäkelä <marko.makela@xxxxxxxxxxx>]

Hi Arlindo,

2018-07-27 22:06 GMT+03:00 Arlindo Neto <neto.acs@xxxxxxxxx>:
> 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_Ov5NI8/

> 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