MySQL Bugs: #107231: for rollback transactions, it is better to set del

Bug #107231	for rollback transactions, it is better to set del_marks to false
Submitted:	6 May 2022 14:27	Modified:	11 May 2022 2:51
Reporter:	alex xing (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
for rollback transactions, it is better to set del_marks to false.
In thus case: when purge  purge_sys->rseg->last_del_marks is also false, and no need to execute trx_undo_get_first_rec.

How to repeat:
just read the code

Suggested fix:
just as the below patch

a simple patch to describe the optimization (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: optimize.patch (text/plain), 538 bytes.

Hi Mr. xing,

Thank you Mr. xing.

Can you explain your idea a bit further. What would be the gain and how big a gain if setting del_marks to false when doing rollback.

Also, please check whether 8.0.29 has any changes in the rollback code, that might impact your patch.

I looked at the 8.0.29 code and found this optimization to be equally useful
 Logic before optimization:
 Transaction deletes a record, innodb would set trx->rsegs->m_redo->update_undo->del_marks to true.
 If transaction eventually rollback,  when maintain UNDO LOG HISTORY LIST(trx_purge_add_update_undo_to_history) would set rseg->last_del_marks=true.
 when purge(trx_purge_read_undo_rec)  the purge_sys->rseg->last_del_marks is also true, and will try to get first_rec(trx_undo_get_first_rec), which need get undo page and read record.
 optimization idea:
 For the Transactions that have been rollback, it is clear that there is no need to clear delete marked record, and the undo of this transaction has no data.
 So , if we set  trx->rsegs->m_redo->update_undo->del_marks  to false when rollback, during the period of purge, no need to  execute trx_undo_get_first_rec, just skip it.

Benefits of optimization：Reduce a page IO  during the period of purge

Hi,

After your latest analysis, what would be the patch that would improve performance and would there be any negative consequences ????

Benefits of optimization: purge_sys->rseg->last_del_marks is also false during the period of purge, and no need to execute trx_undo_get_first_rec, which  reduce a page IO.

pseudo code
trx_purge_read_undo_rec
{

    if (purge_sys->rseg->last_del_marks) {
         undo_rec = trx_undo_get_first_rec
    }else{
        //come here
        ...
    }
}

Hi Mr. xing,

How would you propose to fetch first record after the undo operation. That operation might change what is first record.

The native logic is to perform trx_undo_get_first_rec. Since undo TRX_UNDO_PAGE_FREE is modified, the return value of trx_undo_get_first_rec must be NULL  
and I thought it was an invalid operation. I don't think it's necessary to  get a delete mark for rollbacked trx.

New patch that considers partially rollback transactions (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: optimize.patch (text/plain), 548 bytes.

Hi,

Your patch might affect MVCC operation of the InnoDB storage engine. Particularly in READ_UNCOMMITTED isolation level.

Your feedback would be welcome.

Why does READ UNCOMMITTED Isolation Level affect,Can you give me an example

Hi Mr. xing,

You can find several examples in our Reference Manual.

Although in rollback, that transaction is not finished yet, hence the non-committed changes should be visible to other session in that mode.

More important that that, your patch does not even compile. Even if it does compile, it does not pass memory checks. In some cases your (corrected) patch even crashes the server.

Hence, we can not accept your patch. We are willing to consider all valid patches , under the condition that reporter is able to provide a test case which demonstrates the performance gain and a patch which doesn't crash on it.

Not a bug.

new patch , set del_marks in trx_rollback_finish

Attachment: optimize.patch (text/plain), 613 bytes.

Since the transaction has been rolled back at this point, there is no impact on READ_UNCOMMITTED isolation level.and the code compile ok based on 8.0.19
and why does not pass memory checks ,memory leak ?

And from the code 8.0.19 , only purge sys delay on del_marks , No MVCC-related dependencies were found