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:
Category:MySQL Server: InnoDB Plugin storage engine Severity:S5 (Performance)
Version:5.1.47 OS:Any
Assigned to: Assigned Account
Tags: innodb, performance
Triage: Triaged: D3 (Medium)

[25 Jun 2010 17:32] Mark Callaghan
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
[25 Jun 2010 17:36] Mark Callaghan
Details are in http://www.facebook.com/MySQLatFacebook#!/notes/mysqlfacebook/using-pmp-to-double-mysql-th...
[25 Jun 2010 17:42] Mark Callaghan
Peak QPS at 64 concurrent threads increased from ~34,000 to ~49,000 with this change
[26 Jun 2010 4:37] James Day
Isn't this a duplicate of bug #43161 ?
[26 Jun 2010 4:39] Mark Callaghan
Yes, but this has better details. Can you add a duplicate feature request filter for requests from me?
[28 Jun 2010 17:23] Mikhail Izioumtchenko
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.
[28 Jun 2010 23:08] Mikhail Izioumtchenko
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.
[28 Jun 2010 23:46] James Day
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.
[28 Jul 2010 14:54] Mikhail Izioumtchenko
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.
[27 Aug 2013 7:30] Laurynas Biveinis
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.

[10 Jan 2014 17:50] Daniel Price
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.
[28 Jan 2014 12:20] Naga Satyanarayana Bodapati
Thank you Laurynas Biveinis for the contribution
[3 Apr 2014 15:16] Laurynas Biveinis
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
  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