<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="faqs-innodb-change-buffer">
      </a>
      A.16 MySQL 8.4 FAQ: InnoDB Change Buffer
     </h2>
    </div>
   </div>
  </div>
  <div class="qandaset">
   <a name="idm46045054908128">
   </a>
   <dl>
    <dt>
     A.16.1.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-operations">
      What types of operations modify secondary indexes and result in
        change buffering?
     </a>
    </dt>
    <dt>
     A.16.2.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-benefits">
      What is the benefit of the InnoDB change
        buffer?
     </a>
    </dt>
    <dt>
     A.16.3.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-index-types">
      Does the change buffer support other types of indexes?
     </a>
    </dt>
    <dt>
     A.16.4.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-space-max-size">
      How much space does InnoDB use for the change
        buffer?
     </a>
    </dt>
    <dt>
     A.16.5.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-current-size">
      How do I determine the current size of the change buffer?
     </a>
    </dt>
    <dt>
     A.16.6.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-merging">
      When does change buffer merging occur?
     </a>
    </dt>
    <dt>
     A.16.7.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-flush-time">
      When is the change buffer flushed?
     </a>
    </dt>
    <dt>
     A.16.8.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-when-to-enable">
      When should the change buffer be used?
     </a>
    </dt>
    <dt>
     A.16.9.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-when-to-disable">
      When should the change buffer not be used?
     </a>
    </dt>
    <dt>
     A.16.10.
     <a href="faqs-innodb-change-buffer.html#faq-innodb-change-buffer-info">
      Where can I find additional information about the change buffer?
     </a>
    </dt>
   </dl>
   <table border="0" style="width: 100%;">
    <colgroup>
     <col align="left" width="1%"/>
     <col/>
    </colgroup>
    <tbody>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-operations">
       </a>
       <a name="idm46045054907376">
       </a>
       <p>
        <b>
         A.16.1.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        What types of operations modify secondary indexes and result in
        change buffering?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        <code class="literal">
         INSERT
        </code>
        ,
        <code class="literal">
         UPDATE
        </code>
        , and
        <code class="literal">
         DELETE
        </code>
        operations can modify secondary
        indexes. If an affected index page is not in the buffer pool,
        the changes can be buffered in the change buffer.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-benefits">
       </a>
       <a name="idm46045054903056">
       </a>
       <p>
        <b>
         A.16.2.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        What is the benefit of the
        <code class="literal">
         InnoDB
        </code>
        change
        buffer?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Buffering secondary index changes when secondary index pages are
        not in the buffer pool avoids expensive random access I/O
        operations that would be required to immediately read in
        affected index pages from disk. Buffered changes can be applied
        later, in batches, as pages are read into the buffer pool by
        other read operations.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-index-types">
       </a>
       <a name="idm46045054899952">
       </a>
       <p>
        <b>
         A.16.3.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Does the change buffer support other types of indexes?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        No. The change buffer only supports secondary indexes. Clustered
        indexes, full-text indexes, and spatial indexes are not
        supported. Full-text indexes have their own caching mechanism.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-space-max-size">
       </a>
       <a name="idm46045054897728">
       </a>
       <p>
        <b>
         A.16.4.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        How much space does
        <code class="literal">
         InnoDB
        </code>
        use for the change
        buffer?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Prior to the introduction of the
        <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
         <code class="literal">
          innodb_change_buffer_max_size
         </code>
        </a>
        configuration option in MySQL 5.6, the maximum size of the
        on-disk change buffer in the system tablespace was 1/3 of the
        <code class="literal">
         InnoDB
        </code>
        buffer pool size.
       </p>
       <p>
        In MySQL 5.6 and later, the
        <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
         <code class="literal">
          innodb_change_buffer_max_size
         </code>
        </a>
        configuration option defines the maximum size of the change
        buffer as a percentage of the total buffer pool size. By
        default,
        <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
         <code class="literal">
          innodb_change_buffer_max_size
         </code>
        </a>
        is set to 25. The maximum setting is 50.
       </p>
       <p>
        <code class="literal">
         InnoDB
        </code>
        does not buffer an operation if it
        would cause the on-disk change buffer to exceed the defined
        limit.
       </p>
       <p>
        Change buffer pages are not required to persist in the buffer
        pool and may be evicted by LRU operations.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-current-size">
       </a>
       <a name="idm46045054887920">
       </a>
       <p>
        <b>
         A.16.5.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        How do I determine the current size of the change buffer?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        The current size of the change buffer is reported by
        <code class="literal">
         SHOW ENGINE INNODB STATUS \G
        </code>
        , under the
        <code class="literal">
         INSERT BUFFER AND ADAPTIVE HASH INDEX
        </code>
        heading. For example:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting language-none"><div class="docs-select-all right" id="sa3305531"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges</code></pre>
       </div>
       <p>
        Relevant data points include:
       </p>
       <div class="itemizedlist">
        <ul class="itemizedlist" style="list-style-type: disc; ">
         <li class="listitem">
          <p>
           <code class="literal">
            size
           </code>
           : The number of pages used within the
            change buffer. Change buffer size is equal to
           <code class="literal">
            seg
            size - (1 + free list len)
           </code>
           . The
           <code class="literal">
            1
            +
           </code>
           value represents the change buffer header page.
          </p>
         </li>
         <li class="listitem">
          <p>
           <code class="literal">
            seg size
           </code>
           : The size of the change buffer,
            in pages.
          </p>
         </li>
        </ul>
       </div>
       <p>
        For information about monitoring change buffer status, see
        <a class="xref" href="innodb-change-buffer.html" title="17.5.2 Change Buffer">
         Section 17.5.2, “Change Buffer”
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-merging">
       </a>
       <a name="idm46045054876624">
       </a>
       <p>
        <b>
         A.16.6.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        When does change buffer merging occur?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <div class="itemizedlist">
        <ul class="itemizedlist" style="list-style-type: disc; ">
         <li class="listitem">
          <p>
           When a page is read into the buffer pool, buffered changes
            are merged upon completion of the read, before the page is
            made available.
          </p>
         </li>
         <li class="listitem">
          <p>
           Change buffer merging is performed as a background task. The
           <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
            <code class="literal">
             innodb_io_capacity
            </code>
           </a>
           parameter sets an upper limit on the I/O activity performed
            by
           <code class="literal">
            InnoDB
           </code>
           background tasks such as
            merging data from the change buffer.
          </p>
         </li>
         <li class="listitem">
          <p>
           A change buffer merge is performed during crash recovery.
            Changes are applied from the change buffer (in the system
            tablespace) to leaf pages of secondary indexes as index
            pages are read into the buffer pool.
          </p>
         </li>
         <li class="listitem">
          <p>
           The change buffer is fully durable and can survive a system
            crash. Upon restart, change buffer merge operations resume
            as part of normal operations.
          </p>
         </li>
         <li class="listitem">
          <p>
           A full merge of the change buffer can be forced as part of a
            slow server shutdown using
           <a class="link" href="innodb-parameters.html#sysvar_innodb_fast_shutdown">
            <code class="literal">
             --innodb-fast-shutdown=0
            </code>
           </a>
           .
          </p>
         </li>
        </ul>
       </div>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-flush-time">
       </a>
       <a name="idm46045054866464">
       </a>
       <p>
        <b>
         A.16.7.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        When is the change buffer flushed?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Updated pages are flushed by the same flushing mechanism that
        flushes the other pages that occupy the buffer pool.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-when-to-enable">
       </a>
       <a name="idm46045054864384">
       </a>
       <p>
        <b>
         A.16.8.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        When should the change buffer be used?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        The change buffer is a feature designed to reduce random I/O to
        secondary indexes as indexes grow larger and no longer fit in
        the
        <code class="literal">
         InnoDB
        </code>
        buffer pool. Generally, the change
        buffer should be used when the entire data set does not fit into
        the buffer pool, when there is substantial DML activity that
        modifies secondary index pages, or when there are lots of
        secondary indexes that are regularly changed by DML activity.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-when-to-disable">
       </a>
       <a name="idm46045054861152">
       </a>
       <p>
        <b>
         A.16.9.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        When should the change buffer not be used?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        You might consider disabling the change buffer if the entire
        data set fits within the
        <code class="literal">
         InnoDB
        </code>
        buffer pool,
        if you have relatively few secondary indexes, or if you are
        using solid-state storage, where random reads are about as fast
        as sequential reads. Before making configuration changes, it is
        recommended that you run tests using a representative workload
        to determine if disabling the change buffer provides any
        benefit.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-innodb-change-buffer-info">
       </a>
       <a name="idm46045054857968">
       </a>
       <p>
        <b>
         A.16.10.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Where can I find additional information about the change buffer?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        See
        <a class="xref" href="innodb-change-buffer.html" title="17.5.2 Change Buffer">
         Section 17.5.2, “Change Buffer”
        </a>
        .
       </p>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
 </div>
 <br/>
</div>
