Description:
During InnoDB purge, removing a delete-marked secondary index entry calls row_purge_poss_sec(), which in turn may call row_vers_old_has_index_entry().
row_vers_old_has_index_entry() walks the corresponding clustered index record version chain through undo records, and checks whether any version newer than the purge view still needs the secondary index entry.
For a table with multiple ordinary secondary indexes, purge currently performs this check independently for each secondary index entry of the same clustered record. For example, if a table has 5 secondary indexes and one delete-marked clustered record is being purged, InnoDB may call row_purge_poss_sec() 5 times. Each call can reposition to the same clustered record and walk the same clustered version chain again.
This duplicates the expensive part of the work: fetching and materializing previous clustered versions from undo. The same clustered version chain could be scanned once, while answering whether each of the 5 secondary index entries is still needed.
The current behavior is correct, but it can be unnecessarily expensive when:
- the table has multiple secondary indexes,
- history list length is large,
- the clustered version chain is long,
- purge needs to remove many delete-marked secondary entries.
The cost is roughly proportional to:
number_of_secondary_indexes * clustered_version_chain_length
for the version-chain traversal part. This could be reduced by batching the secondary-index checks for the same clustered record, so that the clustered version chain is walked once and the result is used for all candidate secondary index entries.
This proposal does not depend on PAGE_MAX_TRX_ID or any page-level heuristic. It would preserve the existing correctness model of row_vers_old_has_index_entry(), but aggregate multiple checks that currently scan the same clustered version chain repeatedly.
How to repeat:
1. Create a table with several ordinary secondary indexes, for example 5 or more:
CREATE TABLE t (
id BIGINT PRIMARY KEY,
c1 BIGINT,
c2 BIGINT,
c3 BIGINT,
c4 BIGINT,
c5 BIGINT,
pad VARCHAR(100),
KEY k1(c1, id),
KEY k2(c2, id),
KEY k3(c3, id),
KEY k4(c4, id),
KEY k5(c5, id)
) ENGINE=InnoDB;
2. Insert a reasonably large number of rows.
3. Keep an old read view open in one session to prevent purge from advancing:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM t;
4. In another session, repeatedly update or delete rows so that many undo records and delete-marked secondary index entries are created. For example, repeatedly update all indexed columns or delete rows from the table.
5. Release the old read view so purge can start catching up:
COMMIT;
6. Profile the mysqld process while purge is running, for example with perf:
perf record -g -p <mysqld_pid> -- sleep 60
perf report --children
7. Observe that row_purge_poss_sec() and row_vers_old_has_index_entry() can consume significant CPU. For a table with N secondary indexes, the same clustered record version chain can be walked up to N times, once per secondary index entry.
A similar workload can also be produced with sysbench oltp_update_index by adding multiple secondary indexes and holding a long read view to accumulate a long history list before allowing purge to run.
Suggested fix:
Batch the secondary-index purge checks for the same clustered record.
Instead of calling row_purge_poss_sec() independently for each secondary index entry, collect the candidate secondary index entries generated from the same purge node / clustered record, then scan the clustered version chain once.
During the single scan:
1. Reposition to the clustered record once.
2. Walk the clustered version chain once through trx_undo_prev_version_build().
3. For each visible/not-delete-marked clustered version that is still newer than the purge view, build the corresponding secondary index entries for the candidate indexes.
4. Mark each candidate secondary entry as still needed if any clustered version maps to the same secondary entry.
5. After the scan, physically remove only the candidate secondary entries that were not marked as needed.
Conceptually, replace repeated checks like:
for each secondary index:
row_purge_poss_sec()
-> row_vers_old_has_index_entry()
-> walk the same clustered version chain
with a batched check like:
collect candidate secondary entries for this clustered record
walk the clustered version chain once
answer all candidate secondary entries from that one walk
This keeps the same correctness criterion as the existing implementation:
A secondary index entry can be removed only if it does not refer to any not-delete-marked clustered record version whose DB_TRX_ID is newer than the purge view.
The optimization only removes duplicate clustered-version-chain traversal. It does not rely on PAGE_MAX_TRX_ID, and therefore avoids the maintenance/upper-bound concerns discussed in page-level max-trx-id based fast paths.
This should reduce purge CPU cost for tables with multiple secondary indexes and long version chains, especially when row_vers_old_has_index_entry() is hot in profiles.
© 2026, Oracle Corporation and/or its affiliates
Description: During InnoDB purge, removing a delete-marked secondary index entry calls row_purge_poss_sec(), which in turn may call row_vers_old_has_index_entry(). row_vers_old_has_index_entry() walks the corresponding clustered index record version chain through undo records, and checks whether any version newer than the purge view still needs the secondary index entry. For a table with multiple ordinary secondary indexes, purge currently performs this check independently for each secondary index entry of the same clustered record. For example, if a table has 5 secondary indexes and one delete-marked clustered record is being purged, InnoDB may call row_purge_poss_sec() 5 times. Each call can reposition to the same clustered record and walk the same clustered version chain again. This duplicates the expensive part of the work: fetching and materializing previous clustered versions from undo. The same clustered version chain could be scanned once, while answering whether each of the 5 secondary index entries is still needed. The current behavior is correct, but it can be unnecessarily expensive when: - the table has multiple secondary indexes, - history list length is large, - the clustered version chain is long, - purge needs to remove many delete-marked secondary entries. The cost is roughly proportional to: number_of_secondary_indexes * clustered_version_chain_length for the version-chain traversal part. This could be reduced by batching the secondary-index checks for the same clustered record, so that the clustered version chain is walked once and the result is used for all candidate secondary index entries. This proposal does not depend on PAGE_MAX_TRX_ID or any page-level heuristic. It would preserve the existing correctness model of row_vers_old_has_index_entry(), but aggregate multiple checks that currently scan the same clustered version chain repeatedly. How to repeat: 1. Create a table with several ordinary secondary indexes, for example 5 or more: CREATE TABLE t ( id BIGINT PRIMARY KEY, c1 BIGINT, c2 BIGINT, c3 BIGINT, c4 BIGINT, c5 BIGINT, pad VARCHAR(100), KEY k1(c1, id), KEY k2(c2, id), KEY k3(c3, id), KEY k4(c4, id), KEY k5(c5, id) ) ENGINE=InnoDB; 2. Insert a reasonably large number of rows. 3. Keep an old read view open in one session to prevent purge from advancing: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT COUNT(*) FROM t; 4. In another session, repeatedly update or delete rows so that many undo records and delete-marked secondary index entries are created. For example, repeatedly update all indexed columns or delete rows from the table. 5. Release the old read view so purge can start catching up: COMMIT; 6. Profile the mysqld process while purge is running, for example with perf: perf record -g -p <mysqld_pid> -- sleep 60 perf report --children 7. Observe that row_purge_poss_sec() and row_vers_old_has_index_entry() can consume significant CPU. For a table with N secondary indexes, the same clustered record version chain can be walked up to N times, once per secondary index entry. A similar workload can also be produced with sysbench oltp_update_index by adding multiple secondary indexes and holding a long read view to accumulate a long history list before allowing purge to run. Suggested fix: Batch the secondary-index purge checks for the same clustered record. Instead of calling row_purge_poss_sec() independently for each secondary index entry, collect the candidate secondary index entries generated from the same purge node / clustered record, then scan the clustered version chain once. During the single scan: 1. Reposition to the clustered record once. 2. Walk the clustered version chain once through trx_undo_prev_version_build(). 3. For each visible/not-delete-marked clustered version that is still newer than the purge view, build the corresponding secondary index entries for the candidate indexes. 4. Mark each candidate secondary entry as still needed if any clustered version maps to the same secondary entry. 5. After the scan, physically remove only the candidate secondary entries that were not marked as needed. Conceptually, replace repeated checks like: for each secondary index: row_purge_poss_sec() -> row_vers_old_has_index_entry() -> walk the same clustered version chain with a batched check like: collect candidate secondary entries for this clustered record walk the clustered version chain once answer all candidate secondary entries from that one walk This keeps the same correctness criterion as the existing implementation: A secondary index entry can be removed only if it does not refer to any not-delete-marked clustered record version whose DB_TRX_ID is newer than the purge view. The optimization only removes duplicate clustered-version-chain traversal. It does not rely on PAGE_MAX_TRX_ID, and therefore avoids the maintenance/upper-bound concerns discussed in page-level max-trx-id based fast paths. This should reduce purge CPU cost for tables with multiple secondary indexes and long version chains, especially when row_vers_old_has_index_entry() is hot in profiles.