MySQL Bugs: #67476: Innodb_buffer_pool_read_ahead

Bug #67476	Innodb_buffer_pool_read_ahead_evicted is inaccurate
Submitted:	5 Nov 2012 18:49	Modified:	12 Jan 2013 2:07
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5,5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	access time, buffer pool, Contribution, innodb_buffer_pool_read_ahead_evicted, LRU

Description:
If a page being read into the buffer pool is made "young" (moved to the head of
the LRU), its time of first access it not properly recorded (that is, it remains
with a value of zero). When the page eventually gets evicted, the page is
counted as a read-ahead page that was evicted without having been accessed by
queries. This happens because the code assumes that pages with no access time
(value zero) must have been read due to read ahead.

How to repeat:
Run a workload where a page being read might be moved to the head of the LRU.
Compare the values of Innodb_buffer_pool_read_ahead (number of pages read by
read-ahead) and Innodb_buffer_pool_read_ahead_evicted (number of pages read by
read-ahead that were evicted). For exemple:

| Innodb_buffer_pool_read_ahead               | 18174            |
| Innodb_buffer_pool_read_ahead_evicted       | 61704157         |

Fix for Bug#67476 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Bug#67476-Innodb_buffer_pool_read_ahead_evicted-is-i.patch (application/octet-stream, text), 1.68 KiB.

Thank you for the report.

Verified as described.

To verify I was lazy to create proper test case, so I modified source as follow:

=== modified file 'storage/innobase/include/buf0buf.ic'
--- storage/innobase/include/buf0buf.ic	2012-07-25 08:23:01 +0000
+++ storage/innobase/include/buf0buf.ic	2012-11-10 11:47:18 +0000
@@ -169,6 +169,7 @@
 	const buf_page_t*	bpage)	/*!< in: block to make younger */
 {
 	buf_pool_t*		buf_pool = buf_pool_from_bpage(bpage);
+return 1;
 
 	if (UNIV_UNLIKELY(buf_pool->freed_page_clock == 0)) {
 		/* If eviction has not started yet, do not update the

The run sysbench: $ sysbench --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=13000 --test=oltp --oltp-read-only=1 --oltp-table-size=1000000 --num-threads=8 prepare

After it I got this:

mysql> show global status like 'Innodb_buffer_pool_read_ahead%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 818   |
+---------------------------------------+-------+
3 rows in set (0.00 sec)

Davi,

Thanks for pointing this out. The bug is real but I am going to solve it in a different way. I think ::access_time flag need only be protected by block->mutex and should not be clubbed together with LRU manipulation. I'll write a patch for this.

Added to changelog for 5.1.68, 5.5.30, 5.6.10: 

The status variable Innodb_buffer_pool_read_ahead_evicted could show
an inaccurate value, higher than expected, because some pages in the
buffer pool were incorrectly considered as being brought in by
read-ahead requests.