Bug #70561 Why does LRU neighbor flush skip 'non-old' pages?
Submitted: 9 Oct 2013 12:40 Modified: 14 Oct 2013 12:09
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[9 Oct 2013 12:40] Laurynas Biveinis
Description:
static
bool
buf_flush_check_neighbor(
...
	/* We avoid flushing 'non-old' blocks in an LRU flush,
	because the flushed blocks are soon freed */
...
	if (... buf_page_is_old(bpage)) {
...
}

static
ulint
buf_flush_try_neighbors(
... 
calls to buf_flush_check_neighbor()
...
		/* We avoid flushing 'non-old' blocks in an LRU flush,
		because the flushed blocks are soon freed */

		if (... || buf_page_is_old(bpage)) {
...
}

First, is the comment "because the flushed blocks are soon freed" correct for 5.6? If I understand correctly, LRU flushes do not move blocks around in the LRU list, they stay in their places, just become clean.

Second, if the LRU flushes do not move blocks around on the LRU list, then would removing this check potentially improve --innodb-flush-neighbors=1 (i.e. contiguous neighbor flush) performance on HDDs by making it possible to form larger contiguous blocks that include these 'non-old' pages? As well as simplify code in general.

How to repeat:
Code analysis. We haven't gotten around to measuring this yet.

Suggested fix:
If the above the correct, then these ifs in these two functions are redundant and might be removed.
[9 Oct 2013 15:45] Sinisa Milivojevic
Analysis is in the process, but so far it seems that the above mentioned option could be optimized further for the non-zero values of the option.
[12 Oct 2013 0:02] Sunny Bains
The justification for this behaviour:

"
For modification intensive workload, the last modified LSN should  progress  effectively by flushing dirty blocks. 

The current area-based neighbor flushing is not affective for both HDD and  SSD. 

If there is no advantage for write-IO throughput by using the neighbor_flush, we  should not use it. 
Because only flushing oldest dirty blocks progresses the last modified lsn at  the time. 

HDD has sequential advantage for write-IO throughput when the blocks are  contiguous. In addition InnoDB conjugates the contiguous IO requests in 1 IO.

So, the best setting for HDD should be contiguous-only neighbor_flush. 

* SSD doesn't have any IO throughput advantage from neighbor_flush, should concentrate to flush oldests. (disable neighbor_flush is the best) 

How To Repeat: 
------------- 
modification intensive benchmark with HDD-RAID, HDD. 

Suggested Fix: 
------------- 
change the neighbor_flush behavior for HDD or HDD-RAID 
"
[14 Oct 2013 12:09] Sinisa Milivojevic
As the change required for further optimization is dependent on media used, I am verifying this report as a feature request.