<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-buffer-pool-flushing">
      </a>
      17.8.3.5 Configuring Buffer Pool Flushing
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045163057584">
  </a>
  <a class="indexterm" name="idm46045163056512">
  </a>
  <p>
   <code class="literal">
    InnoDB
   </code>
   performs certain tasks in the
        background, including flushing of dirty pages from the buffer
        pool. Dirty pages are those that have been modified but are not
        yet written to the data files on disk.
  </p>
  <p>
   Buffer pool flushing is performed by page cleaner threads. The
        number of page cleaner threads is controlled by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_cleaners">
    <code class="literal">
     innodb_page_cleaners
    </code>
   </a>
   variable,
        which has a default value set to the same value as
   <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_instances">
    <code class="literal">
     innodb_buffer_pool_instances
    </code>
   </a>
   .
  </p>
  <p>
   Buffer pool flushing is initiated when the percentage of dirty
        pages reaches the low water mark value defined by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm">
    <code class="literal">
     innodb_max_dirty_pages_pct_lwm
    </code>
   </a>
   variable. The default low water mark is 10% of buffer pool
        pages. A
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm">
    <code class="literal">
     innodb_max_dirty_pages_pct_lwm
    </code>
   </a>
   value of 0 disables this early flushing behaviour.
  </p>
  <p>
   The purpose of the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm">
    <code class="literal">
     innodb_max_dirty_pages_pct_lwm
    </code>
   </a>
   threshold is to control the percentage dirty pages in the buffer
        pool and to prevent the amount of dirty pages from reaching the
        threshold defined by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">
    <code class="literal">
     innodb_max_dirty_pages_pct
    </code>
   </a>
   variable, which has a default value of 90.
   <code class="literal">
    InnoDB
   </code>
   aggressively flushes buffer pool pages
        if the percentage of dirty pages in the buffer pool reaches the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">
    <code class="literal">
     innodb_max_dirty_pages_pct
    </code>
   </a>
   threshold.
  </p>
  <p>
   When configuring
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm">
    <code class="literal">
     innodb_max_dirty_pages_pct_lwm
    </code>
   </a>
   ,
        the value should always be lower than the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">
    <code class="literal">
     innodb_max_dirty_pages_pct
    </code>
   </a>
   value.
  </p>
  <p>
   Additional variables permit fine-tuning of buffer pool flushing
        behavior:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_neighbors">
       <code class="literal">
        innodb_flush_neighbors
       </code>
      </a>
      variable defines whether flushing a page from the buffer
            pool also flushes other dirty pages in the same extent.
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The default setting of 0 disables
         <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_neighbors">
          <code class="literal">
           innodb_flush_neighbors
          </code>
         </a>
         .
                Dirty pages in the same extent are not flushed. This
                setting is recommended for non-rotational storage (SSD)
                devices where seek time is not a significant factor.
        </p>
       </li>
       <li class="listitem">
        <p>
         A setting of 1 flushes contiguous dirty pages in the
                same extent.
        </p>
       </li>
       <li class="listitem">
        <p>
         A setting of 2 flushes dirty pages in the same extent.
        </p>
       </li>
      </ul>
     </div>
     <p>
      When table data is stored on a traditional
      <a class="link" href="glossary.html#glos_hdd" title="HDD">
       HDD
      </a>
      storage device, flushing
            neighbor pages in one operation reduces I/O overhead
            (primarily for disk seek operations) compared to flushing
            individual pages at different times. For table data stored
            on
      <a class="link" href="glossary.html#glos_ssd" title="SSD">
       SSD
      </a>
      , seek time is not a
            significant factor and you can disable this setting to
            spread out write operations.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_lru_scan_depth">
       <code class="literal">
        innodb_lru_scan_depth
       </code>
      </a>
      variable specifies, per buffer pool instance, how far down
            the buffer pool LRU list the page cleaner thread scans
            looking for dirty pages to flush. This is a background
            operation performed by a page cleaner thread once per
            second.
     </p>
     <p>
      A setting smaller than the default is generally suitable for
            most workloads. A value that is significantly higher than
            necessary may impact performance. Only consider increasing
            the value if you have spare I/O capacity under a typical
            workload. Conversely, if a write-intensive workload
            saturates your I/O capacity, decrease the value, especially
            in the case of a large buffer pool.
     </p>
     <p>
      When tuning
      <a class="link" href="innodb-parameters.html#sysvar_innodb_lru_scan_depth">
       <code class="literal">
        innodb_lru_scan_depth
       </code>
      </a>
      ,
            start with a low value and configure the setting upward with
            the goal of rarely seeing zero free pages. Also, consider
            adjusting
      <a class="link" href="innodb-parameters.html#sysvar_innodb_lru_scan_depth">
       <code class="literal">
        innodb_lru_scan_depth
       </code>
      </a>
      when
            changing the number of buffer pool instances, since
      <a class="link" href="innodb-parameters.html#sysvar_innodb_lru_scan_depth">
       <code class="literal">
        innodb_lru_scan_depth
       </code>
      </a>
      *
      <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_instances">
       <code class="literal">
        innodb_buffer_pool_instances
       </code>
      </a>
      defines the amount of work performed by the page cleaner
            thread each second.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_neighbors">
    <code class="literal">
     innodb_flush_neighbors
    </code>
   </a>
   and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_lru_scan_depth">
    <code class="literal">
     innodb_lru_scan_depth
    </code>
   </a>
   variables
        are primarily intended for write-intensive workloads. With heavy
        DML activity, flushing can fall behind if it is not aggressive
        enough, or disk writes can saturate I/O capacity if flushing is
        too aggressive. The ideal settings depend on your workload, data
        access patterns, and storage configuration (for example, whether
        data is stored on HDD or SSD devices).
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-adaptive-flushing">
       </a>
       Adaptive Flushing
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     InnoDB
    </code>
    uses an adaptive flushing algorithm
          to dynamically adjust the rate of flushing based on the speed
          of redo log generation and the current rate of flushing. The
          intent is to smooth overall performance by ensuring that
          flushing activity keeps pace with the current workload.
          Automatically adjusting the flushing rate helps avoid sudden
          dips in throughput that can occur when bursts of I/O activity
          due to buffer pool flushing affects the I/O capacity available
          for ordinary read and write activity.
   </p>
   <p>
    Sharp checkpoints, which are typically associated with
          write-intensive workloads that generate a lot of redo entries,
          can cause a sudden change in throughput, for example. A sharp
          checkpoint occurs when
    <code class="literal">
     InnoDB
    </code>
    wants to
          reuse a portion of a log file. Before doing so, all dirty
          pages with redo entries in that portion of the log file must
          be flushed. If log files become full, a sharp checkpoint
          occurs, causing a temporary reduction in throughput. This
          scenario can occur even if
    <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">
     <code class="literal">
      innodb_max_dirty_pages_pct
     </code>
    </a>
    threshold is not reached.
   </p>
   <p>
    The adaptive flushing algorithm helps avoid such scenarios by
          tracking the number of dirty pages in the buffer pool and the
          rate at which redo log records are being generated. Based on
          this information, it decides how many dirty pages to flush
          from the buffer pool each second, which permits it to manage
          sudden changes in workload.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_flushing_lwm">
     <code class="literal">
      innodb_adaptive_flushing_lwm
     </code>
    </a>
    variable defines a low water mark for redo log capacity. When
          that threshold is crossed, adaptive flushing is enabled, even
          if the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_flushing">
     <code class="literal">
      innodb_adaptive_flushing
     </code>
    </a>
    variable is disabled.
   </p>
   <p>
    Internal benchmarking has shown that the algorithm not only
          maintains throughput over time, but can also improve overall
          throughput significantly. However, adaptive flushing can
          affect the I/O pattern of a workload significantly and may not
          be appropriate in all cases. It gives the most benefit when
          the redo log is in danger of filling up. If adaptive flushing
          is not appropriate to the characteristics of your workload,
          you can disable it. Adaptive flushing controlled by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_flushing">
     <code class="literal">
      innodb_adaptive_flushing
     </code>
    </a>
    variable, which is enabled by default.
   </p>
   <p>
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flushing_avg_loops">
     <code class="literal">
      innodb_flushing_avg_loops
     </code>
    </a>
    defines the number of iterations that
    <code class="literal">
     InnoDB
    </code>
    keeps the previously calculated
          snapshot of the flushing state, controlling how quickly
          adaptive flushing responds to foreground workload changes. A
          high
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flushing_avg_loops">
     <code class="literal">
      innodb_flushing_avg_loops
     </code>
    </a>
    value means that
    <code class="literal">
     InnoDB
    </code>
    keeps the
          previously calculated snapshot longer, so adaptive flushing
          responds more slowly. When setting a high value it is
          important to ensure that redo log utilization does not reach
          75% (the hardcoded limit at which asynchronous flushing
          starts), and that the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">
     <code class="literal">
      innodb_max_dirty_pages_pct
     </code>
    </a>
    threshold keeps the number of dirty pages to a level that is
          appropriate for the workload.
   </p>
   <p>
    Systems with consistent workloads, a large log file size
          (
    <a class="link" href="innodb-parameters.html#sysvar_innodb_log_file_size">
     <code class="literal">
      innodb_log_file_size
     </code>
    </a>
    ), and
          small spikes that do not reach 75% log space utilization
          should use a high
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flushing_avg_loops">
     <code class="literal">
      innodb_flushing_avg_loops
     </code>
    </a>
    value to keep flushing as smooth as possible. For systems with
          extreme load spikes or log files that do not provide a lot of
          space, a smaller value allows flushing to closely track
          workload changes, and helps to avoid reaching 75% log space
          utilization.
   </p>
   <p>
    Be aware that if flushing falls behind, the rate of buffer
          pool flushing can exceed the I/O capacity available to
    <code class="literal">
     InnoDB
    </code>
    , as defined by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    setting.
          The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    value defines an upper limit on I/O capacity in such
          situations, so that a spike in I/O activity does not consume
          the entire I/O capacity of the server.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    setting is applicable to all buffer pool instances. When dirty
          pages are flushed, I/O capacity is divided equally among
          buffer pool instances.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-limit-flushing-rate">
       </a>
       Limiting Buffer Flushing During Idle Periods
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_idle_flush_pct">
     <code class="literal">
      innodb_idle_flush_pct
     </code>
    </a>
    variable limits the rate of buffer pool flushing during idle
          periods, which are periods of time that database pages are not
          modified. Its value is interpreted as a percentage of
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    (which
          defines the number of I/O operations per second available to
    <code class="literal">
     InnoDB
    </code>
    ). The default value is 100, or 100
          percent of the value of
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    . To limit
          flushing during idle periods, set
    <code class="literal">
     innodb_idle_flush_pct
    </code>
    to less than 100.
   </p>
   <p>
    Limiting page flushing during idle periods can help extend the
          life of solid state storage devices. Side effects of limiting
          page flushing during idle periods may include a longer
          shutdown time following a lengthy idle period, and a longer
          recovery period should a server failure occur.
   </p>
  </div>
 </div>
 <br/>
</div>
