<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-purge-configuration">
      </a>
      17.8.9 Purge Configuration
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045162570640">
  </a>
  <a class="indexterm" name="idm46045162569568">
  </a>
  <a class="indexterm" name="idm46045162568496">
  </a>
  <p>
   <code class="literal">
    InnoDB
   </code>
   does not physically remove a row from
      the database immediately when you delete it with an SQL statement.
      A row and its index records are only physically removed when
   <code class="literal">
    InnoDB
   </code>
   discards the undo log record written for
      the deletion. This removal operation, which only occurs after the
      row is no longer required for multi-version concurrency control
      (MVCC) or rollback, is called a purge.
  </p>
  <p>
   Purge runs on a periodic schedule. It parses and processes undo
      log pages from the history list, which is a list of undo log pages
      for committed transactions that is maintained by the
   <code class="literal">
    InnoDB
   </code>
   transaction system. Purge frees the undo
      log pages from the history list after processing them.
  </p>
  <h4>
   <a name="idm46045162563776">
   </a>
   Configuring Purge Threads
  </h4>
  <p>
   Purge operations are performed in the background by one or more
      purge threads. The number of purge threads is controlled by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_threads">
    <code class="literal">
     innodb_purge_threads
    </code>
   </a>
   variable.
      The default value is 1 if the number of available logical
      processors is &lt;= 16, otherwise the default is 4.
  </p>
  <p>
   If DML action is concentrated on a single table, purge operations
      for the table are performed by a single purge thread, which can
      result in slowed purge operations, increased purge lag, and
      increased tablespace file size if the DML operations involve large
      object values. If the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   setting is
      exceeded, purge work is automatically redistributed among
      available purge threads. Too many active purge threads in this
      scenario can cause contention with user threads, so manage the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_threads">
    <code class="literal">
     innodb_purge_threads
    </code>
   </a>
   setting
      accordingly. The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   variable is
      set to 0 by default, which means that there is no maximum purge
      lag by default.
  </p>
  <p>
   If DML action is concentrated on few tables, keep the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_threads">
    <code class="literal">
     innodb_purge_threads
    </code>
   </a>
   setting low
      so that the threads do not contend with each other for access to
      the busy tables. If DML operations are spread across many tables,
      consider a higher
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_threads">
    <code class="literal">
     innodb_purge_threads
    </code>
   </a>
   setting. The
      maximum number of purge threads is 32.
  </p>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_threads">
    <code class="literal">
     innodb_purge_threads
    </code>
   </a>
   setting
      is the maximum number of purge threads permitted. The purge system
      automatically adjusts the number of purge threads that are used.
  </p>
  <h4>
   <a name="idm46045162551200">
   </a>
   Configuring Purge Batch Size
  </h4>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_batch_size">
    <code class="literal">
     innodb_purge_batch_size
    </code>
   </a>
   variable defines the number of undo log pages that purge parses
      and processes in one batch from the history list. The default
      value is 300. In a multithreaded purge configuration, the
      coordinator purge thread divides
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_batch_size">
    <code class="literal">
     innodb_purge_batch_size
    </code>
   </a>
   by
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_threads">
    <code class="literal">
     innodb_purge_threads
    </code>
   </a>
   and assigns
      that number of pages to each purge thread.
  </p>
  <p>
   The purge system also frees the undo log pages that are no longer
      required. It does so every 128 iterations through the undo logs.
      In addition to defining the number of undo log pages parsed and
      processed in a batch, the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_batch_size">
    <code class="literal">
     innodb_purge_batch_size
    </code>
   </a>
   variable
      defines the number of undo log pages that purge frees every 128
      iterations through the undo logs.
  </p>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_batch_size">
    <code class="literal">
     innodb_purge_batch_size
    </code>
   </a>
   variable is intended for advanced performance tuning and
      experimentation. Most users need not change
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_batch_size">
    <code class="literal">
     innodb_purge_batch_size
    </code>
   </a>
   from its
      default value.
  </p>
  <h4>
   <a name="idm46045162540928">
   </a>
   Configuring the Maximum Purge Lag
  </h4>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   variable
      defines the desired maximum purge lag. When the purge lag exceeds
      the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   threshold, a delay is imposed on
   <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 to allow time for
      purge operations to catch up. The default value is 0, which means
      there is no maximum purge lag and no delay.
  </p>
  <p>
   The
   <code class="literal">
    InnoDB
   </code>
   transaction system maintains a list
      of transactions that have index records delete-marked by
   <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. The length of
      the list is the purge lag.
  </p>
  <p>
   The purge lag delay is calculated by the following formula:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa96685147"><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">(purge_lag/innodb_max_purge_lag - 0.9995) * 10000</code></pre>
  </div>
  <p>
   The delay is calculated at the beginning of a purge batch.
  </p>
  <p>
   A typical
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   setting for a problematic workload might be 1000000 (1 million),
      assuming that transactions are small, only 100 bytes in size, and
      it is permissible to have 100MB of unpurged table rows.
  </p>
  <p>
   The purge lag is presented as the
   <code class="literal">
    History list
      length
   </code>
   value in the
   <code class="literal">
    TRANSACTIONS
   </code>
   section of
   <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
    <code class="literal">
     SHOW
      ENGINE INNODB STATUS
    </code>
   </a>
   output.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa10004520"><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-terminal"><span class="token prompt">mysql&gt;</span><span class="token command"> SHOW</span> ENGINE INNODB STATUS<span class="token punctuation">;</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token property">------------</span>
TRANSACTIONS
<span class="token property">------------</span>
Trx id counter 0 290328385
Purge done for trx's n<span class="token punctuation">:</span>o &lt; 0 290315608 undo n<span class="token punctuation">:</span>o &lt; 0 17
History list length 20</code></pre>
  </div>
  <p>
   The
   <code class="literal">
    History list length
   </code>
   is typically a low
      value, usually less than a few thousand, but a write-heavy
      workload or long running transactions can cause it to increase,
      even for transactions that are read only. The reason that a long
      running transaction can cause the
   <code class="literal">
    History list
      length
   </code>
   to increase is that under a consistent read
      transaction isolation level such as
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   , a transaction
      must return the same result as when the read view for that
      transaction was created. Consequently, the
   <code class="literal">
    InnoDB
   </code>
   multi-version concurrency control (MVCC)
      system must keep a copy of the data in the undo log until all
      transactions that depend on that data have completed. The
      following are examples of long running transactions that could
      cause the
   <code class="literal">
    History list length
   </code>
   to increase:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      operation that uses the
      <a class="link" href="mysqldump.html#option_mysqldump_single-transaction">
       <code class="option">
        --single-transaction
       </code>
      </a>
      option
          while there is a significant amount of concurrent DML.
     </p>
    </li>
    <li class="listitem">
     <p>
      Running a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      query after
          disabling
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      , and
          forgetting to issue an explicit
      <code class="literal">
       COMMIT
      </code>
      or
      <code class="literal">
       ROLLBACK
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   To prevent excessive delays in extreme situations where the purge
      lag becomes huge, you can limit the delay by setting the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag_delay">
    <code class="literal">
     innodb_max_purge_lag_delay
    </code>
   </a>
   variable. The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag_delay">
    <code class="literal">
     innodb_max_purge_lag_delay
    </code>
   </a>
   variable specifies the maximum delay in microseconds for the delay
      imposed when the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   threshold is
      exceeded. The specified
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag_delay">
    <code class="literal">
     innodb_max_purge_lag_delay
    </code>
   </a>
   value
      is an upper limit on the delay period calculated by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_max_purge_lag">
    <code class="literal">
     innodb_max_purge_lag
    </code>
   </a>
   formula.
  </p>
  <h4>
   <a name="idm46045162500736">
   </a>
   Purge and Undo Tablespace Truncation
  </h4>
  <p>
   The purge system is also responsible for truncating undo
      tablespaces. You can configure the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_rseg_truncate_frequency">
    <code class="literal">
     innodb_purge_rseg_truncate_frequency
    </code>
   </a>
   variable to control the frequency with which the purge system
      looks for undo tablespaces to truncate. For more information, see
   <a class="xref" href="innodb-undo-tablespaces.html#truncate-undo-tablespace" title="Truncating Undo Tablespaces">
    Truncating Undo Tablespaces
   </a>
   .
  </p>
 </div>
 <br/>
</div>
