<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-performance-midpoint_insertion">
      </a>
      17.8.3.3 Making the Buffer Pool Scan Resistant
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045163150272">
  </a>
  <a class="indexterm" name="idm46045163149200">
  </a>
  <a class="indexterm" name="idm46045163148128">
  </a>
  <a class="indexterm" name="idm46045163147056">
  </a>
  <a class="indexterm" name="idm46045163145984">
  </a>
  <a class="indexterm" name="idm46045163144912">
  </a>
  <a class="indexterm" name="idm46045163143840">
  </a>
  <p>
   Rather than using a strict
   <a class="link" href="glossary.html#glos_lru" title="LRU">
    LRU
   </a>
   algorithm,
   <code class="literal">
    InnoDB
   </code>
   uses a technique to
        minimize the amount of data that is brought into the
   <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
    buffer pool
   </a>
   and never
        accessed again. The goal is to make sure that frequently
        accessed (
   <span class="quote">
    “
    <span class="quote">
     hot
    </span>
    ”
   </span>
   ) pages remain in the buffer pool,
        even as
   <a class="link" href="glossary.html#glos_read_ahead" title="read-ahead">
    read-ahead
   </a>
   and
   <a class="link" href="glossary.html#glos_full_table_scan" title="full table scan">
    full table scans
   </a>
   bring in new blocks that might or might not be accessed
        afterward.
  </p>
  <p>
   Newly read blocks are inserted into the middle of the LRU list.
        All newly read pages are inserted at a location that by default
        is
   <code class="literal">
    3/8
   </code>
   from the tail of the LRU list. The
        pages are moved to the front of the list (the most-recently used
        end) when they are accessed in the buffer pool for the first
        time. Thus, pages that are never accessed never make it to the
        front portion of the LRU list, and
   <span class="quote">
    “
    <span class="quote">
     age out
    </span>
    ”
   </span>
   sooner
        than with a strict LRU approach. This arrangement divides the
        LRU list into two segments, where the pages downstream of the
        insertion point are considered
   <span class="quote">
    “
    <span class="quote">
     old
    </span>
    ”
   </span>
   and are
        desirable victims for LRU eviction.
  </p>
  <p>
   For an explanation of the inner workings of the
   <code class="literal">
    InnoDB
   </code>
   buffer pool and specifics about the
        LRU algorithm, see
   <a class="xref" href="innodb-buffer-pool.html" title="17.5.1 Buffer Pool">
    Section 17.5.1, “Buffer Pool”
   </a>
   .
  </p>
  <p>
   You can control the insertion point in the LRU list and choose
        whether
   <code class="literal">
    InnoDB
   </code>
   applies the same optimization
        to blocks brought into the buffer pool by table or index scans.
        The configuration parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   controls
        the percentage of
   <span class="quote">
    “
    <span class="quote">
     old
    </span>
    ”
   </span>
   blocks in the LRU list. The
        default value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   is
   <code class="literal">
    37
   </code>
   , corresponding to the original fixed ratio
        of 3/8. The value range is
   <code class="literal">
    5
   </code>
   (new pages in
        the buffer pool age out very quickly) to
   <code class="literal">
    95
   </code>
   (only 5% of the buffer pool is reserved for hot pages, making
        the algorithm close to the familiar LRU strategy).
  </p>
  <p>
   The optimization that keeps the buffer pool from being churned
        by read-ahead can avoid similar problems due to table or index
        scans. In these scans, a data page is typically accessed a few
        times in quick succession and is never touched again. The
        configuration parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_time">
    <code class="literal">
     innodb_old_blocks_time
    </code>
   </a>
   specifies the time window (in milliseconds) after the first
        access to a page during which it can be accessed without being
        moved to the front (most-recently used end) of the LRU list. The
        default value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_time">
    <code class="literal">
     innodb_old_blocks_time
    </code>
   </a>
   is
   <code class="literal">
    1000
   </code>
   . Increasing this value makes more and
        more blocks likely to age out faster from the buffer pool.
  </p>
  <p>
   Both
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_time">
    <code class="literal">
     innodb_old_blocks_time
    </code>
   </a>
   can be
        specified in the MySQL option file (
   <code class="literal">
    my.cnf
   </code>
   or
   <code class="literal">
    my.ini
   </code>
   ) or changed at runtime 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. Changing the value at runtime
        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>
   To help you gauge the effect of setting these parameters, the
   <code class="literal">
    SHOW ENGINE INNODB STATUS
   </code>
   statement reports
        buffer pool statistics. For details, see
   <a class="xref" href="innodb-buffer-pool.html#innodb-buffer-pool-monitoring" title="Monitoring the Buffer Pool Using the InnoDB Standard Monitor">
    Monitoring the Buffer Pool Using the InnoDB Standard Monitor
   </a>
   .
  </p>
  <p>
   Because the effects of these parameters can vary widely based on
        your hardware configuration, your data, and the details of your
        workload, always benchmark to verify the effectiveness before
        changing these settings in any performance-critical or
        production environment.
  </p>
  <p>
   In mixed workloads where most of the activity is OLTP type with
        periodic batch reporting queries which result in large scans,
        setting the value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_time">
    <code class="literal">
     innodb_old_blocks_time
    </code>
   </a>
   during
        the batch runs can help keep the working set of the normal
        workload in the buffer pool.
  </p>
  <p>
   When scanning large tables that cannot fit entirely in the
        buffer pool, setting
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   to a
        small value keeps the data that is only read once from consuming
        a significant portion of the buffer pool. For example, setting
   <code class="literal">
    innodb_old_blocks_pct=5
   </code>
   restricts this data
        that is only read once to 5% of the buffer pool.
  </p>
  <p>
   When scanning small tables that do fit into memory, there is
        less overhead for moving pages around within the buffer pool, so
        you can leave
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   at its
        default value, or even higher, such as
   <code class="literal">
    innodb_old_blocks_pct=50
   </code>
   .
  </p>
  <p>
   The effect of the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_time">
    <code class="literal">
     innodb_old_blocks_time
    </code>
   </a>
   parameter is harder to predict than the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   parameter, is relatively small, and varies more with the
        workload. To arrive at an optimal value, conduct your own
        benchmarks if the performance improvement from adjusting
   <a class="link" href="innodb-parameters.html#sysvar_innodb_old_blocks_pct">
    <code class="literal">
     innodb_old_blocks_pct
    </code>
   </a>
   is not
        sufficient.
  </p>
 </div>
 <br/>
</div>
