MySQL database keeps the information about old versions of changed rows and supports transactional features such as concurrency and rollback. A rollback segment is an InnoDB storage area that contains the undo log. InnoDB can respond to queries for multiple versions of the same row when those queries are part of transactions that started at different times. It uses part of the undo log, the update undo buffer, to build earlier versions of database rows.
Three hidden fields exist on every row of user data managed by InnoDB:
- DB_TRX_ID: Six-byte field that indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted.
- DB_ROLL_PTR: Seven-byte field called the roll pointer. It points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.
- DB_ROW_ID: Six-byte field that contains a row ID that automatically increments as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.
If you insert and delete rows in small batches at the same rate in the table, the purge thread can 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 a case, throttle new row operations and allocate more resources to the purge thread by tuning the innodb_max_purge_lag system variable.
MySQL database physically removes a row (and its indexes) only when it discards the update undo log record written for the deletion. This is a fast purge operation. The purge thread might need more resources because it can get slow and take disk space.