At least since a change in MySQL 5.5.33, InnoDB appears to be invoking buf_flush_note_modification() on pages that were exclusively latched but not modified in a mini-transaction. I encountered this problem while debugging
MDEV-22097 (which appears to be a separate problem).
The unnecessary page writes should at least be hurting performance.
Initially, I was suspecting that the writes might break crash recovery as well, but it looks like that it is not the case. When a page is added to the flush list and eventually written out, the FIL_PAGE_LSN field will be updated to the commit LSN of the mini-transaction (to the value of log_sys.lsn at the end of the mini-transaction).
- Let us assume that the latest real modification to the page was at LSN 10000.
- Then, a mini-transaction (LSN 15000) X-latches but does not modify the page.
- The page will be scheduled to be flushed with FIL_PAGE_LSN 15000. (Earlier, it might have been written with FIL_PAGE_LSN 10000 already.)
- The server is killed.
- Recovery will scan logs for the page since the latest checkpoint C.
- If C>=10000, there will be no log to apply for the page.
- If C<10000, we will find some records for the page, and we may have to read the page (but could also skip it thanks to
- If we read the page, then, if FIL_PAGE_LSN is at least 10000, we will not apply any log records. Only if neither the ‘extra’ page flush nor the necessary page flush took place, we will apply the log records.
Here is a work-in-progress patch to expose the problem on 10.5:
We might want to edit the slot->type when the X-latched or SX-latched page is actually modified for the first time, so that ReleaseBlocks will be able to skip the unnecessary call. Instead of traversing the mtr_t::m_memo, it might be more efficient to use a flag in buf_block_t for this purpose.