MySQL Bugs: #103856: Speed up row_purge_poss_sec for non-delete marked sec index record

Bug #103856	Speed up row_purge_poss_sec for non-delete marked sec index record
Submitted:	31 May 2021 3:11	Modified:	7 Jun 2021 12:07
Reporter:	Kang Wang	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.6 and above	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	row_purge_poss_sec

Description:
I notice that row_purge_poss_sec will check cluster index record and all its previous versions until current purge view, to decide whether it is possible to remove a secondary index entry. 

My question is why not just return false when the secondary index entry is non-delete marked. Obviously, it should not be removed. 

Although the following check in row_vers_old_has_index_entry will come out with the same conclusion. But row_vers_old_has_index_entry may be very time-consuming, especially when the the version list is long, and each version may take up one single page, which may cause an IO operation on IO-bound scenario. 

How to repeat:
no repeat

Suggested fix:
row_purge_poss_sec just return false as while as the secondary index entry is non-delete marked.

Hi Mr. Wang,

Thank you for your bug report.

We understand completely your performance improvement proposal.

However, it is not possible to implement your idea, due to the MVCC nature of the InnoDB Storage Engine, as well as due to the online DDLs that have been introduced.

Hence, entire history has to be analysed and not just a single flag.

If you have any other ideas, let us know ......

Would you please give more detail abort why not just see the delete mark on sec index?

In other words, what will happened if we do like this?

Would you please give more detail abort why not just see the delete mark on sec index?

In other words, what will happened if we do like this?

Hi,

The answer is quite simple.

There would be negative repercussions in several isolation levels.