Bug #104779 no need to own sx-latch during the period of page flusing for BUF_FLUSH_LRU

• Submitted: 31 Aug 2021 9:38
• Modified: 31 Aug 2021 10:01
• Reporter: alex xing (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 8.0.19
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[31 Aug 2021 9:38] alex xing

Description:
no need to own sx-latch during the period of page flusing for BUF_FLUSH_LRU if Double Write is enabled.
After copy block->frame to double write buffer, the page's sx-latch  can be released, and we can flush  the Double Write buffer to user space without sx-latch.
In buf_page_io_complete, if the block->frame is already being modified or is being modified, there is no need to maintian lru or flush list. if the block->frame is not modified ,the execution logic is the same as before

The benefits of this optimization:
1.Reduce the lock occupancy time.The performance improvement is especially significant for scenarios with poor disk performance
2.Reduce the overhead of maintaining LRU and flush list. Before optimization, if user modify a page that is being flushed. First need remove the page from  Flush list and LRU list in the buf_page_io_complete first. And then after flusing and and modification.  Page needs to be moved back to flush List again. After optimization, do nothing.

How to repeat:
read the code

Suggested fix:
After copy block->frame to double write buffer, the page's sx-latch  can be released, and we can flush  the Double Write buffer to user space without sx-latch.

Along those lines, I made a simple patch in 8.0.19.  which I believe works for the latest code as well

[31 Aug 2021 9:39] alex xing

a simple patch based on  8.0.19.  The same may be true for the latest version of the code.

(*) 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), 10.50 KiB.

[31 Aug 2021 10:01] MySQL Verification Team

Hello Alex Xing,

Thank you for the report and contribution.

regards,
Umesh