MySQL Bugs: #54814: make BUF_READ_AHEAD

Bug #54814	make BUF_READ_AHEAD_AREA a constant
Submitted:	25 Jun 2010 17:32	Modified:	10 Jan 2014 17:51
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S5 (Performance)
Version:	5.1.47	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb, performance

Description:
BUF_READ_AHEAD_AREA calls ut_2_power_up. On workloads that do many IOPs this is called frequently and uses too much CPU. I think it should be hardwired to 64. This is safe as long as buf_pool->curr_size >= 1024 and I can live with that limit.

How to repeat:
To be described

Details are in http://www.facebook.com/MySQLatFacebook#!/notes/mysqlfacebook/using-pmp-to-double-mysql-th...

Peak QPS at 64 concurrent threads increased from ~34,000 to ~49,000 with this change

Isn't this a duplicate of bug #43161 ?

Yes, but this has better details. Can you add a duplicate feature request filter for requests from me?

we should remember to set minimum innodb_buffer_pool_size to 16m 
when doing this, and verify it is safe even there, if not set the minimum
to 32m or so. The lowest possible value of innodb_buffer_pool_size
is only used in stress testing, of course.

somewhat less efficient solution is to keep the expression but make the value a variable, computed once. Could be safer to include in 5.1. 
But making it constant 64 and increasing minimal buffer pool size a little
doesn't look unsafe, either.

Michael, seems OK to use 16 megabytes for the plugin, though strictly that breaks backwards compatibility in a production release and shouldn't be done. Doing it in the next major server release as well seems like an easy decision.

Doesn't seem OK to change it for the built in InnoDB in 5.1 for backwards compatibility, mainly with OEMs and low RAM setups. Things like packaged with Windows applications that have to work on machines with a few hundred megabytes of total RAM or point of sale terminals.

Mark, thanks for confirming. I expect that we'll close one of them, the earlier one seems best to close.

my current recalculation tells me that the buffer pool size has to be
<= 16M to trigger the readahead size <64 with the current formula.
I think I mentioned earlier that is was <16M, not <=16M.
If I'm right the default buffer pool should be 32M at least.
This simple calculation has to be verified as I share a trait that
people with a Math degree often are not very good at elementary school level math.
Regardless, there is additional comlication when multiple buffer pools are used.
With the current formula it is the granule size that drives the computation
and the min for that is 16M with 1G <= innodb_buffer_pool_size < 2G and 64 == innodb_buffer_pool_instances. An autoadjustment of innodb_buffer_pool_instances
to 32 in that case is a simple fix. However I'm not entirely convinced yet that 
the readahead area shouldn't depend mostly on innodb_buffer_pool_size,
especially when we use it more like the writeahead size in the flushing code.

Precomputed BUF_READ_AHEAD_AREA patch for 5.6. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug54814.patch (text/x-patch), 1.65 KiB.

Posted by developer:
 
Fixed as of 5.7.4, and here's the changelog entry:

"BUF_READ_AHEAD_AREA" would frequently call "ut_2_power_up" for workloads
with a high I/O rate. The calculation is now performed once and the result
is stored in the "buf_pool_t" structure. 

Thank you for the bug report.

Thank you Laurynas Biveinis for the contribution

5.7$ bzr log -r 7251
------------------------------------------------------------
revno: 7251
committer: Satya Bodapati <satya.bodapati@oracle.com>
branch nick: mysql-trunk
timestamp: Fri 2014-01-10 15:56:34 +0530
message:
  Bug#11762242 - MAKE BUF_READ_AHEAD_AREA A CONSTANT
  
  BUF_READ_AHEAD_AREA calls ut_2_power_up. On workloads that do many IOPs
  this is called frequently and uses too much CPU.
  
  Fix by calculating only once and store in buf_pool_t structure.
  
  Approved by Kevin. rb#4008