<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-performance-read_ahead">
      </a>
      17.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045163099808">
  </a>
  <a class="indexterm" name="idm46045163098736">
  </a>
  <a class="indexterm" name="idm46045163097664">
  </a>
  <a class="indexterm" name="idm46045163096624">
  </a>
  <a class="indexterm" name="idm46045163095136">
  </a>
  <p>
   A
   <a class="link" href="glossary.html#glos_read_ahead" title="read-ahead">
    read-ahead
   </a>
   request is
        an I/O request to prefetch multiple pages in the
   <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
    buffer pool
   </a>
   asynchronously, in anticipation of impending need for these
        pages. The requests bring in all the pages in one
   <a class="link" href="glossary.html#glos_extent" title="extent">
    extent
   </a>
   .
   <code class="literal">
    InnoDB
   </code>
   uses two read-ahead algorithms to
        improve I/O performance:
  </p>
  <p>
   <span class="strong">
    <strong>
     Linear
    </strong>
   </span>
   read-ahead is a
        technique that predicts what pages might be needed soon based on
        pages in the buffer pool being accessed sequentially. You
        control when
   <code class="literal">
    InnoDB
   </code>
   performs a read-ahead
        operation by adjusting the number of sequential page accesses
        required to trigger an asynchronous read request, using the
        configuration parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_ahead_threshold">
    <code class="literal">
     innodb_read_ahead_threshold
    </code>
   </a>
   .
        Before this parameter was added,
   <code class="literal">
    InnoDB
   </code>
   would
        only calculate whether to issue an asynchronous prefetch request
        for the entire next extent when it read the last page of the
        current extent.
  </p>
  <p>
   The configuration parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_ahead_threshold">
    <code class="literal">
     innodb_read_ahead_threshold
    </code>
   </a>
   controls how sensitive
   <code class="literal">
    InnoDB
   </code>
   is in detecting
        patterns of sequential page access. If the number of pages read
        sequentially from an extent is greater than or equal to
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_ahead_threshold">
    <code class="literal">
     innodb_read_ahead_threshold
    </code>
   </a>
   ,
   <code class="literal">
    InnoDB
   </code>
   initiates an asynchronous read-ahead
        operation of the entire following extent.
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_ahead_threshold">
    <code class="literal">
     innodb_read_ahead_threshold
    </code>
   </a>
   can
        be set to any value from 0-64. The default value is 56. The
        higher the value, the more strict the access pattern check. For
        example, if you set the value to 48,
   <code class="literal">
    InnoDB
   </code>
   triggers a linear read-ahead request only when 48 pages in the
        current extent have been accessed sequentially. If the value is
        8,
   <code class="literal">
    InnoDB
   </code>
   triggers an asynchronous read-ahead
        even if as few as 8 pages in the extent are accessed
        sequentially. You can set the value of this parameter in the
        MySQL
   <a class="link" href="glossary.html#glos_configuration_file" title="configuration file">
    configuration
        file
   </a>
   , or change it dynamically with the
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
        GLOBAL
    </code>
   </a>
   statement, which requires privileges sufficient
        to set global system variables. See
   <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
    Section 7.1.9.1, “System Variable Privileges”
   </a>
   .
  </p>
  <p>
   <span class="strong">
    <strong>
     Random
    </strong>
   </span>
   read-ahead is a
        technique that predicts when pages might be needed soon based on
        pages already in the buffer pool, regardless of the order in
        which those pages were read. If 13 consecutive pages from the
        same extent are found in the buffer pool,
   <code class="literal">
    InnoDB
   </code>
   asynchronously issues a request to
        prefetch the remaining pages of the extent. To enable this
        feature, set the configuration variable
   <a class="link" href="innodb-parameters.html#sysvar_innodb_random_read_ahead">
    <code class="literal">
     innodb_random_read_ahead
    </code>
   </a>
   to
   <code class="literal">
    ON
   </code>
   .
  </p>
  <p>
   The
   <code class="literal">
    SHOW ENGINE INNODB STATUS
   </code>
   statement
        displays statistics to help you evaluate the effectiveness of
        the read-ahead algorithm. Statistics include counter information
        for the following global status variables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead">
       <code class="literal">
        Innodb_buffer_pool_read_ahead
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead_evicted">
       <code class="literal">
        Innodb_buffer_pool_read_ahead_evicted
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead_rnd">
       <code class="literal">
        Innodb_buffer_pool_read_ahead_rnd
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   This information can be useful when fine-tuning the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_random_read_ahead">
    <code class="literal">
     innodb_random_read_ahead
    </code>
   </a>
   setting.
  </p>
  <p>
   For more information about I/O performance, see
   <a class="xref" href="optimizing-innodb-diskio.html" title="10.5.8 Optimizing InnoDB Disk I/O">
    Section 10.5.8, “Optimizing InnoDB Disk I/O”
   </a>
   and
   <a class="xref" href="disk-issues.html" title="10.12.1 Optimizing Disk I/O">
    Section 10.12.1, “Optimizing Disk I/O”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
