<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-change-buffer">
      </a>
      17.5.2 Change Buffer
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045167013264">
  </a>
  <a class="indexterm" name="idm46045167011776">
  </a>
  <p>
   The change buffer is a special data structure that caches changes to
   <a class="link" href="glossary.html#glos_secondary_index" title="secondary index">
    secondary index
   </a>
   pages
    when those pages are not in the
   <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
    buffer pool
   </a>
   . The buffered
    changes, which may result from
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   , or
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   operations (DML), are merged
    later when the pages are loaded into the buffer pool by other read
    operations.
  </p>
  <div class="figure">
   <a name="innodb-change-buffer-diagram">
   </a>
   <p class="title">
    <b>
     Figure 17.3 Change Buffer
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="Content is described in the surrounding text." src="images/innodb-change-buffer.png" style="width: 100%; max-width: 581px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   Unlike
   <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
    clustered
    indexes
   </a>
   , secondary indexes are usually nonunique, and inserts
    into secondary indexes happen in a relatively random order.
    Similarly, deletes and updates may affect secondary index pages that
    are not adjacently located in an index tree. Merging cached changes
    at a later time, when affected pages are read into the buffer pool
    by other operations, avoids substantial random access I/O that would
    be required to read secondary index pages into the buffer pool from
    disk.
  </p>
  <p>
   Periodically, the purge operation that runs when the system is
    mostly idle, or during a slow shutdown, writes the updated index
    pages to disk. The purge operation can write disk blocks for a
    series of index values more efficiently than if each value were
    written to disk immediately.
  </p>
  <p>
   Change buffer merging may take several hours when there are many
    affected rows and numerous secondary indexes to update. During this
    time, disk I/O is increased, which can cause a significant slowdown
    for disk-bound queries. Change buffer merging may also continue to
    occur after a transaction is committed, and even after a server
    shutdown and restart (see
   <a class="xref" href="forcing-innodb-recovery.html" title="17.20.3 Forcing InnoDB Recovery">
    Section 17.20.3, “Forcing InnoDB Recovery”
   </a>
   for more information).
  </p>
  <p>
   In memory, the change buffer occupies part of the buffer pool. On
    disk, the change buffer is part of the system tablespace, where
    index changes are buffered when the database server is shut down.
  </p>
  <p>
   The type of data cached in the change buffer is governed by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
    <code class="literal">
     innodb_change_buffering
    </code>
   </a>
   variable.
    For more information, see
   <a class="xref" href="innodb-change-buffer.html#innodb-change-buffer-configuration" title="Configuring Change Buffering">
    Configuring Change Buffering
   </a>
   . You can also
    configure the maximum change buffer size. For more information, see
   <a class="xref" href="innodb-change-buffer.html#innodb-change-buffer-maximum-size" title="Configuring the Change Buffer Maximum Size">
    Configuring the Change Buffer Maximum Size
   </a>
   .
  </p>
  <p>
   Change buffering is not supported for a secondary index if the index
    contains a descending index column or if the primary key includes a
    descending index column.
  </p>
  <p>
   For answers to frequently asked questions about the change buffer,
    see
   <a class="xref" href="faqs-innodb-change-buffer.html" title="A.16 MySQL 8.4 FAQ: InnoDB Change Buffer">
    Section A.16, “MySQL 8.4 FAQ: InnoDB Change Buffer”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-change-buffer-configuration">
       </a>
       Configuring Change Buffering
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166986640">
   </a>
   <a class="indexterm" name="idm46045166985568">
   </a>
   <a class="indexterm" name="idm46045166984080">
   </a>
   <p>
    When
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    ,
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    , and
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    operations are performed on
      a table, the values of indexed columns (particularly the values of
      secondary keys) are often in an unsorted order, requiring
      substantial I/O to bring secondary indexes up to date. The
    <a class="link" href="glossary.html#glos_change_buffer" title="change buffer">
     change buffer
    </a>
    caches
      changes to secondary index entries when the relevant
    <a class="link" href="glossary.html#glos_page" title="page">
     page
    </a>
    is not in the
    <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
     buffer pool
    </a>
    , thus avoiding
      expensive I/O operations by not immediately reading in the page
      from disk. The buffered changes are merged when the page is loaded
      into the buffer pool, and the updated page is later flushed to
      disk. The
    <code class="literal">
     InnoDB
    </code>
    main thread merges buffered
      changes when the server is nearly idle, and during a
    <a class="link" href="glossary.html#glos_slow_shutdown" title="slow shutdown">
     slow shutdown
    </a>
    .
   </p>
   <p>
    Because it can result in fewer disk reads and writes, change
      buffering is most valuable for workloads that are I/O-bound; for
      example, applications with a high volume of DML operations such as
      bulk inserts benefit from change buffering.
   </p>
   <p>
    However, the change buffer occupies a part of the buffer pool,
      reducing the memory available to cache data pages. If the working
      set almost fits in the buffer pool, or if your tables have
      relatively few secondary indexes, it may be useful to disable
      change buffering. If the working data set fits entirely within the
      buffer pool, change buffering does not impose extra overhead,
      because it only applies to pages that are not in the buffer pool.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
     <code class="literal">
      innodb_change_buffering
     </code>
    </a>
    variable controls the extent to which
    <code class="literal">
     InnoDB
    </code>
    performs change buffering. You can enable or disable buffering for
      inserts, delete operations (when index records are initially
      marked for deletion) and purge operations (when index records are
      physically deleted). An update operation is a combination of an
      insert and a delete. The default
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
     <code class="literal">
      innodb_change_buffering
     </code>
    </a>
    value is
    <code class="literal">
     none
    </code>
    , and permitted values are described within
      the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
     <code class="literal">
      innodb_change_buffering
     </code>
    </a>
    documentation.
   </p>
   <p>
    You can set the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
     <code class="literal">
      innodb_change_buffering
     </code>
    </a>
    variable
      in the MySQL option file (
    <code class="literal">
     my.cnf
    </code>
    or
    <code class="literal">
     my.ini
    </code>
    ) 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>
    . Changing the setting
      affects the buffering of new operations; the merging of existing
      buffered entries is not affected.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-change-buffer-maximum-size">
       </a>
       Configuring the Change Buffer Maximum Size
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
     <code class="literal">
      innodb_change_buffer_max_size
     </code>
    </a>
    variable permits configuring the maximum size of the change buffer
      as a percentage of the total size of the buffer pool. 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>
    Consider increasing
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
     <code class="literal">
      innodb_change_buffer_max_size
     </code>
    </a>
    on
      a MySQL server with heavy insert, update, and delete activity,
      where change buffer merging does not keep pace with new change
      buffer entries, causing the change buffer to reach its maximum
      size limit.
   </p>
   <p>
    Consider decreasing
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
     <code class="literal">
      innodb_change_buffer_max_size
     </code>
    </a>
    on
      a MySQL server with static data used for reporting, or if the
      change buffer consumes too much of the memory space shared with
      the buffer pool, causing pages to age out of the buffer pool
      sooner than desired.
   </p>
   <p>
    Test different settings with a representative workload to
      determine an optimal configuration. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffer_max_size">
     <code class="literal">
      innodb_change_buffer_max_size
     </code>
    </a>
    variable is dynamic, which permits modifying the setting without
      restarting the server.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-change-buffer-monitoring">
       </a>
       Monitoring the Change Buffer
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045166949168">
   </a>
   <a class="indexterm" name="idm46045166947680">
   </a>
   <p>
    The following options are available for change buffer monitoring:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        InnoDB
       </code>
       Standard Monitor output includes
          change buffer status information. To view monitor data, issue
          the
       <code class="literal">
        SHOW ENGINE INNODB STATUS
       </code>
       statement.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39265303"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">ENGINE</span> INNODB <span class="token keyword">STATUS</span>\G</code></pre>
      </div>
      <p>
       Change buffer status information is located under the
       <code class="literal">
        INSERT BUFFER AND ADAPTIVE HASH INDEX
       </code>
       heading and appears similar to the following:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81429649"><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-sql"><span class="token comment" spellcheck="true">-------------------------------------</span>
<span class="token keyword">INSERT</span> BUFFER <span class="token operator">AND</span> ADAPTIVE <span class="token keyword">HASH</span> <span class="token keyword">INDEX</span>
<span class="token comment" spellcheck="true">-------------------------------------</span>
Ibuf: size <span class="token number">1</span><span class="token punctuation">,</span> free <span class="token keyword">list</span> len <span class="token number">0</span><span class="token punctuation">,</span> seg size <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">0</span> merges
merged operations:
 <span class="token keyword">insert</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token keyword">delete</span> mark <span class="token number">0</span><span class="token punctuation">,</span> <span class="token keyword">delete</span> <span class="token number">0</span>
discarded operations:
 <span class="token keyword">insert</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token keyword">delete</span> mark <span class="token number">0</span><span class="token punctuation">,</span> <span class="token keyword">delete</span> <span class="token number">0</span>
<span class="token keyword">Hash</span> <span class="token keyword">table</span> size <span class="token number">4425293</span><span class="token punctuation">,</span> used cells <span class="token number">32</span><span class="token punctuation">,</span> node heap has <span class="token number">1</span> <span class="token function">buffer</span><span class="token punctuation">(</span>s<span class="token punctuation">)</span>
<span class="token number">13577.57</span> <span class="token keyword">hash</span> searches<span class="token operator">/</span>s<span class="token punctuation">,</span> <span class="token number">202.47</span> non<span class="token operator">-</span><span class="token keyword">hash</span> searches<span class="token operator">/</span>s</code></pre>
      </div>
      <p>
       For more information, see
       <a class="xref" href="innodb-standard-monitor.html" title="17.17.3 InnoDB Standard Monitor and Lock Monitor Output">
        Section 17.17.3, “InnoDB Standard Monitor and Lock Monitor Output”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The Information Schema
       <a class="link" href="information-schema-innodb-metrics-table.html" title="28.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table">
        <code class="literal">
         INNODB_METRICS
        </code>
       </a>
       table provides
          most of the data points found in
       <code class="literal">
        InnoDB
       </code>
       Standard Monitor output plus other data points. To view change
          buffer metrics and a description of each, issue the following
          query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa40790151"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> <span class="token keyword">COMMENT</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_METRICS <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'%ibuf%'</span>\G</code></pre>
      </div>
      <p>
       See
       <a class="xref" href="innodb-information-schema-metrics-table.html" title="17.15.6 InnoDB INFORMATION_SCHEMA Metrics Table">
        Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The Information Schema
       <a class="link" href="information-schema-innodb-buffer-page-table.html" title="28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table">
        <code class="literal">
         INNODB_BUFFER_PAGE
        </code>
       </a>
       table provides
          metadata about each page in the buffer pool, including change
          buffer index and change buffer bitmap pages. Change buffer
          pages are identified by
       <code class="literal">
        PAGE_TYPE
       </code>
       .
       <code class="literal">
        IBUF_INDEX
       </code>
       is the page type for change
          buffer index pages, and
       <code class="literal">
        IBUF_BITMAP
       </code>
       is the
          page type for change buffer bitmap pages.
      </p>
      <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Warning
       </div>
       <p>
        Querying the
        <a class="link" href="information-schema-innodb-buffer-page-table.html" title="28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table">
         <code class="literal">
          INNODB_BUFFER_PAGE
         </code>
        </a>
        table can introduce significant performance overhead. To
            avoid impacting performance, reproduce the issue you want to
            investigate on a test instance and run your queries on the
            test instance.
       </p>
      </div>
      <p>
       For example, you can query the
       <a class="link" href="information-schema-innodb-buffer-page-table.html" title="28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table">
        <code class="literal">
         INNODB_BUFFER_PAGE
        </code>
       </a>
       table to
          determine the approximate number of
       <code class="literal">
        IBUF_INDEX
       </code>
       and
       <code class="literal">
        IBUF_BITMAP
       </code>
       pages as a percentage of total
          buffer pool pages.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49071310"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_BUFFER_PAGE
       <span class="token keyword">WHERE</span> PAGE_TYPE <span class="token operator">LIKE</span> <span class="token string">'IBUF%'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> change_buffer_pages<span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_BUFFER_PAGE<span class="token punctuation">)</span> <span class="token keyword">AS</span> total_pages<span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">(</span><span class="token punctuation">(</span>change_buffer_pages<span class="token operator">/</span>total_pages<span class="token punctuation">)</span><span class="token operator">*</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
       <span class="token keyword">AS</span> change_buffer_page_percentage<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> change_buffer_pages <span class="token punctuation">|</span> total_pages <span class="token punctuation">|</span> change_buffer_page_percentage <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                  25 <span class="token punctuation">|</span>        8192 <span class="token punctuation">|</span>                        0.3052 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
      </div>
      <p>
       For information about other data provided by the
       <a class="link" href="information-schema-innodb-buffer-page-table.html" title="28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table">
        <code class="literal">
         INNODB_BUFFER_PAGE
        </code>
       </a>
       table, see
       <a class="xref" href="information-schema-innodb-buffer-page-table.html" title="28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table">
        Section 28.4.2, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”
       </a>
       .
          For related usage information, see
       <a class="xref" href="innodb-information-schema-buffer-pool-tables.html" title="17.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables">
        Section 17.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="performance-schema.html" title="Chapter 29 MySQL Performance Schema">
        Performance Schema
       </a>
       provides change buffer mutex wait instrumentation for advanced
          performance monitoring. To view change buffer instrumentation,
          issue the following query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74676904"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>setup_instruments
       <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'%wait/synch/mutex/innodb/ibuf%'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NAME                                                  <span class="token punctuation">|</span> ENABLED <span class="token punctuation">|</span> TIMED <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> wait/synch/mutex/innodb/ibuf_bitmap_mutex             <span class="token punctuation">|</span> YES     <span class="token punctuation">|</span> YES   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> wait/synch/mutex/innodb/ibuf_mutex                    <span class="token punctuation">|</span> YES     <span class="token punctuation">|</span> YES   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex <span class="token punctuation">|</span> YES     <span class="token punctuation">|</span> YES   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
      </div>
      <p>
       For information about monitoring
       <code class="literal">
        InnoDB
       </code>
       mutex waits, see
       <a class="xref" href="monitor-innodb-mutex-waits-performance-schema.html" title="17.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema">
        Section 17.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
