The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space needed for your rollback segment. 

In the InnoDB  multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. InnoDB  only physically removes the corresponding row and its index records when it discards the update undo log record written for the deletion. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion. 

If you insert and delete rows in smallish batches at about the same rate in the table, the purge thread can start to lag behind and the table can grow bigger and bigger because of all the “dead”  rows, making everything disk-bound and very slow. In such cases, throttle new row operations, and allocate more resources to the purge thread by tuning the innodb_max_purge_lag  system variable. For more information, see Section 15.8.9, “Purge Configuration” . 

## Multi-Versioning and Secondary Indexes 

InnoDB  multiversion concurrency control (MVCC) treats secondary indexes differently than clustered indexes. Records in a clustered index are updated in-place, and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed. Unlike clustered index records, secondary index records do not contain hidden system columns nor are they updated in-place. 

When a secondary index column is updated, old secondary index records are delete-marked, new records are inserted, and delete-marked records are eventually purged. When a secondary index record is delete- marked or the secondary index page is updated by a newer transaction, InnoDB  looks up the database record in the clustered index. In the clustered index, the record’s DB_TRX_ID  is checked, and the correct version of the record is retrieved from the undo log if the record was modified after the reading transaction was initiated. 

If a secondary index record is marked for deletion or the secondary index page is updated by a newer transaction, the covering index technique is not used. Instead of returning values from the index structure, InnoDB  looks up the record in the clustered index. 

However, if the index condition pushdown (ICP) optimization is enabled, and parts of the WHERE  condition can be evaluated using only fields from the index, the MySQL server still pushes this part of the WHERE condition down to the storage engine where it is evaluated using the index. If no matching records are found, the clustered index lookup is avoided. If matching records are found, even among delete-marked records, InnoDB  looks up the record in the clustered index. 

## 15.4 InnoDB Architecture 

The following diagram shows in-memory and on-disk structures that comprise the InnoDB  storage engine architecture. For information about each structure, see Section 15.5, “InnoDB In-Memory Structures” , and Section 15.6, “InnoDB On-Disk Structures” . 

