<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-undo-tablespaces">
      </a>
      17.6.3.4 Undo Tablespaces
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045165289840">
  </a>
  <a class="indexterm" name="idm46045165288832">
  </a>
  <a class="indexterm" name="idm46045165287824">
  </a>
  <p>
   Undo tablespaces contain undo logs, which are collections of
      records containing information about how to undo the latest change
      by a transaction to a clustered index record.
  </p>
  <p>
   Undo tablespaces are described under the following topics in this
      section:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb-default-undo-tablespaces" title="Default Undo Tablespaces">
       Default Undo Tablespaces
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb--undo-tablespace-size" title="Undo Tablespace Size">
       Undo Tablespace Size
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb-add-undo-tablespaces" title="Adding Undo Tablespaces">
       Adding Undo Tablespaces
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb-drop-undo-tablespaces" title="Dropping Undo Tablespaces">
       Dropping Undo Tablespaces
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb-move-undo-tablespaces" title="Moving Undo Tablespaces">
       Moving Undo Tablespaces
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb-undo-tablespace-rollback-segments" title="Configuring the Number of Rollback Segments">
       Configuring the Number of Rollback Segments
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#truncate-undo-tablespace" title="Truncating Undo Tablespaces">
       Truncating Undo Tablespaces
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-undo-tablespaces.html#innodb-undo-tablespace-status-variables" title="Undo Tablespace Status Variables">
       Undo Tablespace Status Variables
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-default-undo-tablespaces">
       </a>
       Default Undo Tablespaces
      </h5>
     </div>
    </div>
   </div>
   <p>
    Two default undo tablespaces are created when the MySQL instance
        is initialized. Default undo tablespaces are created at
        initialization time to provide a location for rollback segments
        that must exist before SQL statements can be accepted. A minimum
        of two undo tablespaces is required to support automated
        truncation of undo tablespaces. See
    <a class="xref" href="innodb-undo-tablespaces.html#truncate-undo-tablespace" title="Truncating Undo Tablespaces">
     Truncating Undo Tablespaces
    </a>
    .
   </p>
   <p>
    Default undo tablespaces are created in the location defined by
        the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable. If the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable
        is undefined, default undo tablespaces are created in the data
        directory. Default undo tablespace data files are named
    <code class="filename">
     undo_001
    </code>
    and
    <code class="filename">
     undo_002
    </code>
    .
        The corresponding undo tablespace names defined in the data
        dictionary are
    <code class="literal">
     innodb_undo_001
    </code>
    and
    <code class="literal">
     innodb_undo_002
    </code>
    .
   </p>
   <p>
    Additional undo tablespaces can be created at runtime using SQL
        statements. See
    <a class="xref" href="innodb-undo-tablespaces.html#innodb-add-undo-tablespaces" title="Adding Undo Tablespaces">
     Adding Undo Tablespaces
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb--undo-tablespace-size">
       </a>
       Undo Tablespace Size
      </h5>
     </div>
    </div>
   </div>
   <p>
    The initial undo tablespace size is normally 16MiB. The initial
        size may differ when a new undo tablespace is created by a
        truncate operation. In this case, if the file extension size is
        larger than 16MB, and the previous file extension occurred
        within the last second, the new undo tablespace is created at a
        quarter of the size defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_max_undo_log_size">
     <code class="literal">
      innodb_max_undo_log_size
     </code>
    </a>
    variable.
   </p>
   <p>
    An undo tablespace is extended by a minimum of 16MB. To handle
        aggressive growth, the file extension size is doubled if the
        previous file extension happened less than 0.1 seconds earlier.
        Doubling of the extension size can occur multiple times to a
        maximum of 256MB. If the previous file extension occurred more
        than 0.1 seconds earlier, the extension size is reduced by half,
        which can also occur multiple times, to a minimum of 16MB. If
        the
    <code class="literal">
     AUTOEXTEND_SIZE
    </code>
    option is defined for an
        undo tablespace, it is extended by the greater of the
    <code class="literal">
     AUTOEXTEND_SIZE
    </code>
    setting and the extension
        size determined by the logic described above. For information
        about the
    <code class="literal">
     AUTOEXTEND_SIZE
    </code>
    option, see
    <a class="xref" href="innodb-tablespace-autoextend-size.html" title="17.6.3.9 Tablespace AUTOEXTEND_SIZE Configuration">
     Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-add-undo-tablespaces">
       </a>
       Adding Undo Tablespaces
      </h5>
     </div>
    </div>
   </div>
   <p>
    Because undo logs can become large during long-running
        transactions, creating additional undo tablespaces can help
        prevent individual undo tablespaces from becoming too large.
        Additional undo tablespaces can be created at runtime using
    <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
     <code class="literal">
      CREATE UNDO
        TABLESPACE
     </code>
    </a>
    syntax.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77258177"><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 keyword">CREATE</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'<em class="replaceable">file_name</em>.ibu'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The undo tablespace file name must have an
    <code class="filename">
     .ibu
    </code>
    extension. It is not permitted to
        specify a relative path when defining the undo tablespace file
        name. A fully qualified path is permitted, but the path must be
        known to
    <code class="literal">
     InnoDB
    </code>
    . Known paths are those
        defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variable.
        Unique undo tablespace file names are recommended to avoid
        potential file name conflicts when moving or cloning data.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     In a replication environment, the source and each replica must
          have its own undo tablespace file directory. Replicating the
          creation of an undo tablespace file to a common directory
          would cause a file name conflict.
    </p>
   </div>
   <p>
    At startup, directories defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variable are
        scanned for undo tablespace files. (The scan also traverses
        subdirectories.) Directories defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
     <code class="literal">
      innodb_data_home_dir
     </code>
    </a>
    ,
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    , and
    <a class="link" href="server-system-variables.html#sysvar_datadir">
     <code class="literal">
      datadir
     </code>
    </a>
    variables are
        automatically appended to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    value
        regardless of whether the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variable is
        defined explicitly. An undo tablespace can therefore reside in
        paths defined by any of those variables.
   </p>
   <p>
    If the undo tablespace file name does not include a path, the
        undo tablespace is created in the directory defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable.
        If that variable is undefined, the undo tablespace is created in
        the data directory.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     The
     <code class="literal">
      InnoDB
     </code>
     recovery process requires that
          undo tablespace files reside in known directories. Undo
          tablespace files must be discovered and opened before redo
          recovery and before other data files are opened to permit
          uncommitted transactions and data dictionary changes to be
          rolled back. An undo tablespace not found before recovery
          cannot be used, which can lead to database inconsistencies. An
          error message is reported at startup if an undo tablespace
          known to the data dictionary is not found. The known directory
          requirement also supports undo tablespace portability. See
     <a class="xref" href="innodb-undo-tablespaces.html#innodb-move-undo-tablespaces" title="Moving Undo Tablespaces">
      Moving Undo Tablespaces
     </a>
     .
    </p>
   </div>
   <p>
    To create undo tablespaces in a path relative to the data
        directory, set the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable
        to the relative path, and specify the file name only when
        creating an undo tablespace.
   </p>
   <p>
    To view undo tablespace names and paths, query
    <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
     <code class="literal">
      INFORMATION_SCHEMA.FILES
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23043024"><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 keyword">SELECT</span> TABLESPACE_NAME<span class="token punctuation">,</span> FILE_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES
  <span class="token keyword">WHERE</span> FILE_TYPE <span class="token operator">LIKE</span> <span class="token string">'UNDO LOG'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    A MySQL instance supports up to 127 undo tablespaces including
        the two default undo tablespaces created when the MySQL instance
        is initialized.
   </p>
   <p>
    Undo tablespaces can be dropped using
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      DROP UNDO
        TABALESPACE
     </code>
    </a>
    syntax. See
    <a class="xref" href="innodb-undo-tablespaces.html#innodb-drop-undo-tablespaces" title="Dropping Undo Tablespaces">
     Dropping Undo Tablespaces
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-drop-undo-tablespaces">
       </a>
       Dropping Undo Tablespaces
      </h5>
     </div>
    </div>
   </div>
   <p>
    Undo tablespaces created using
    <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
     <code class="literal">
      CREATE UNDO
        TABLESPACE
     </code>
    </a>
    syntax can be dropped at runtime using
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      DROP UNDO
        TABALESPACE
     </code>
    </a>
    syntax.
   </p>
   <p>
    An undo tablespace must be empty before it can be dropped. To
        empty an undo tablespace, the undo tablespace must first be
        marked as inactive using
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      ALTER UNDO
        TABLESPACE
     </code>
    </a>
    syntax so that the tablespace is no longer
        used for assigning rollback segments to new transactions.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa82601252"><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 keyword">ALTER</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">SET</span> <span class="token keyword">INACTIVE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    After an undo tablespace is marked as inactive, transactions
        currently using rollback segments in the undo tablespace are
        permitted to finish, as are any transactions started before
        those transactions are completed. After transactions are
        completed, the purge system frees the rollback segments in the
        undo tablespace, and the undo tablespace is truncated to its
        initial size. (The same process is used when truncating undo
        tablespaces. See
    <a class="xref" href="innodb-undo-tablespaces.html#truncate-undo-tablespace" title="Truncating Undo Tablespaces">
     Truncating Undo Tablespaces
    </a>
    .)
        Once the undo tablespace is empty, it can be dropped.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19260691"><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 keyword">DROP</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em><span class="token punctuation">;</span></code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Alternatively, the undo tablespace can be left in an empty
          state and reactivated later, if needed, by issuing an
     <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
      <code class="literal">
       ALTER UNDO
          TABLESPACE
       <em class="replaceable">
        <code>
         tablespace_name
        </code>
       </em>
       SET
          ACTIVE
      </code>
     </a>
     statement.
    </p>
   </div>
   <p>
    The state of an undo tablespace can be monitored by querying the
        Information Schema
    <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
     <code class="literal">
      INNODB_TABLESPACES
     </code>
    </a>
    table.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50053721"><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 keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> STATE <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLESPACES
  <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">tablespace_name</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    An
    <code class="literal">
     inactive
    </code>
    state indicates that rollback
        segments in an undo tablespace are no longer used by new
        transactions. An
    <code class="literal">
     empty
    </code>
    state indicates that
        an undo tablespace is empty and ready to be dropped, or ready to
        be made active again using an
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      ALTER UNDO
        TABLESPACE
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
      SET
        ACTIVE
     </code>
    </a>
    statement. Attempting to drop an undo
        tablespace that is not empty returns an error.
   </p>
   <p>
    The default undo tablespaces (
    <code class="literal">
     innodb_undo_001
    </code>
    and
    <code class="literal">
     innodb_undo_002
    </code>
    ) created when the MySQL
        instance is initialized cannot be dropped. They can, however, be
        made inactive using an
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      ALTER UNDO
        TABLESPACE
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
      SET
        INACTIVE
     </code>
    </a>
    statement. Before a default undo tablespace
        can be made inactive, there must be an undo tablespace to take
        its place. A minimum of two active undo tablespaces are required
        at all times to support automated truncation of undo
        tablespaces.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-move-undo-tablespaces">
       </a>
       Moving Undo Tablespaces
      </h5>
     </div>
    </div>
   </div>
   <p>
    Undo tablespaces created with
    <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
     <code class="literal">
      CREATE UNDO
        TABLESPACE
     </code>
    </a>
    syntax can be moved while the server is
        offline to any known directory. Known directories are those
        defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variable.
        Directories defined by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
     <code class="literal">
      innodb_data_home_dir
     </code>
    </a>
    ,
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    , and
    <a class="link" href="server-system-variables.html#sysvar_datadir">
     <code class="literal">
      datadir
     </code>
    </a>
    are automatically
        appended to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    value
        regardless of whether the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    variable is
        defined explicitly. Those directories and their subdirectories
        are scanned at startup for undo tablespaces files. An undo
        tablespace file moved to any of those directories is discovered
        at startup and assumed to be the undo tablespace that was moved.
   </p>
   <p>
    The default undo tablespaces (
    <code class="literal">
     innodb_undo_001
    </code>
    and
    <code class="literal">
     innodb_undo_002
    </code>
    ) created when the MySQL
        instance is initialized must reside in the directory defined by
        the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable. If the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable
        is undefined, default undo tablespaces reside in the data
        directory. If default undo tablespaces are moved while the
        server is offline, the server must be started with the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
     <code class="literal">
      innodb_undo_directory
     </code>
    </a>
    variable
        configured to the new directory.
   </p>
   <p>
    The I/O patterns for undo logs make undo tablespaces good
        candidates for
    <a class="link" href="glossary.html#glos_ssd" title="SSD">
     SSD
    </a>
    storage.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-undo-tablespace-rollback-segments">
       </a>
       Configuring the Number of Rollback Segments
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_rollback_segments">
     <code class="literal">
      innodb_rollback_segments
     </code>
    </a>
    variable defines the number of
    <a class="link" href="glossary.html#glos_rollback_segment" title="rollback segment">
     rollback segments
    </a>
    allocated to each undo tablespace and to the global temporary
        tablespace. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_rollback_segments">
     <code class="literal">
      innodb_rollback_segments
     </code>
    </a>
    variable can be configured at startup or while the server is
        running.
   </p>
   <p>
    The default setting for
    <a class="link" href="innodb-parameters.html#sysvar_innodb_rollback_segments">
     <code class="literal">
      innodb_rollback_segments
     </code>
    </a>
    is
        128, which is also the maximum value. For information about the
        number of transactions that a rollback segment supports, see
    <a class="xref" href="innodb-undo-logs.html" title="17.6.6 Undo Logs">
     Section 17.6.6, “Undo Logs”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="truncate-undo-tablespace">
       </a>
       Truncating Undo Tablespaces
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045165195392">
   </a>
   <a class="indexterm" name="idm46045165194384">
   </a>
   <a class="indexterm" name="idm46045165193376">
   </a>
   <p>
    There are two methods of truncating undo tablespaces, which can
        be used individually or in combination to manage undo tablespace
        size. One method is automated, enabled using configuration
        variables. The other method is manual, performed using SQL
        statements.
   </p>
   <p>
    The automated method does not require monitoring undo tablespace
        size and, once enabled, it performs deactivation, truncation,
        and reactivation of undo tablespaces without manual
        intervention. The manual truncation method may be preferable if
        you want to control when undo tablespaces are taken offline for
        truncation. For example, you may want to avoid truncating undo
        tablespaces during peak workload times.
   </p>
   <h6>
    <a name="idm46045165190784">
    </a>
    Automated Truncation
   </h6>
   <p>
    Automated truncation of undo tablespaces requires a minimum of
        two active undo tablespaces, which ensures that one undo
        tablespace remains active while the other is taken offline to be
        truncated. By default, two undo tablespaces are created when the
        MySQL instance is initialized.
   </p>
   <p>
    To have undo tablespaces automatically truncated, enable the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_log_truncate">
     <code class="literal">
      innodb_undo_log_truncate
     </code>
    </a>
    variable. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa6565221"><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">SET</span> <span class="token keyword">GLOBAL</span> innodb_undo_log_truncate<span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    When the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_log_truncate">
     <code class="literal">
      innodb_undo_log_truncate
     </code>
    </a>
    variable is enabled, undo tablespaces that exceed the size limit
        defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_max_undo_log_size">
     <code class="literal">
      innodb_max_undo_log_size
     </code>
    </a>
    variable are subject to truncation. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_max_undo_log_size">
     <code class="literal">
      innodb_max_undo_log_size
     </code>
    </a>
    variable is dynamic and has a default value of 1073741824 bytes
        (1024 MiB).
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa77446298"><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 variable">@@innodb_max_undo_log_size</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>
<span class="token output"><span class="token punctuation">|</span> @@innodb_max_undo_log_size <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>
<span class="token output"><span class="token punctuation">|</span>                 1073741824 <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></code></pre>
   </div>
   <p>
    When the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_log_truncate">
     <code class="literal">
      innodb_undo_log_truncate
     </code>
    </a>
    variable is enabled:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Default and user-defined undo tablespaces that exceed the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_max_undo_log_size">
        <code class="literal">
         innodb_max_undo_log_size
        </code>
       </a>
       setting are marked for truncation. Selection of an undo
            tablespace for truncation is performed in a circular fashion
            to avoid truncating the same undo tablespace each time.
      </p>
     </li>
     <li class="listitem">
      <p>
       Rollback segments residing in the selected undo tablespace
            are made inactive so that they are not assigned to new
            transactions. Existing transactions that are currently using
            rollback segments are permitted to finish.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="glossary.html#glos_purge" title="purge">
        purge
       </a>
       system empties
            rollback segments by freeing undo logs that are no longer in
            use.
      </p>
     </li>
     <li class="listitem">
      <p>
       After all rollback segments in the undo tablespace are
            freed, the truncate operation runs and truncates the undo
            tablespace to its initial size.
      </p>
      <p>
       The size of an undo tablespace after a truncate operation
            may be larger than the initial size due to immediate use
            following the completion of the operation.
      </p>
      <p>
       The
       <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
        <code class="literal">
         innodb_undo_directory
        </code>
       </a>
       variable defines the location of default undo tablespace
            files. If the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
        <code class="literal">
         innodb_undo_directory
        </code>
       </a>
       variable is undefined, default undo tablespaces reside in
            the data directory. The location of all undo tablespace
            files including user-defined undo tablespaces created using
       <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
        <code class="literal">
         CREATE
            UNDO TABLESPACE
        </code>
       </a>
       syntax can be determined by
            querying the Information Schema
       <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
        <code class="literal">
         FILES
        </code>
       </a>
       table:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa491725"><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 keyword">SELECT</span> TABLESPACE_NAME<span class="token punctuation">,</span> FILE_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES <span class="token keyword">WHERE</span> FILE_TYPE <span class="token operator">LIKE</span> <span class="token string">'UNDO LOG'</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Rollback segments are reactivated so that they can be
            assigned to new transactions.
      </p>
     </li>
    </ol>
   </div>
   <h6>
    <a name="idm46045165165936">
    </a>
    Manual Truncation
   </h6>
   <p>
    Manual truncation of undo tablespaces requires a minimum of
        three active undo tablespaces. Two active undo tablespaces are
        required at all times to support the possibility that automated
        truncation is enabled. A minimum of three undo tablespaces
        satisfies this requirement while permitting an undo tablespace
        to be taken offline manually.
   </p>
   <p>
    To manually initiate truncation of an undo tablespace,
        deactivate the undo tablespace by issuing the following
        statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90950635"><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 keyword">ALTER</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">SET</span> <span class="token keyword">INACTIVE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    After the undo tablespace is marked as inactive, transactions
        currently using rollback segments in the undo tablespace are
        permitted to finish, as are any transactions started before
        those transactions are completed. After transactions are
        completed, the purge system frees the rollback segments in the
        undo tablespace, the undo tablespace is truncated to its initial
        size, and the undo tablespace state changes from
    <code class="literal">
     inactive
    </code>
    to
    <code class="literal">
     empty
    </code>
    .
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     When an
     <code class="literal">
      ALTER UNDO TABLESPACE
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
      SET
          INACTIVE
     </code>
     statement deactivates an undo tablespace,
          the purge thread looks for that undo tablespace at the next
          opportunity. Once the undo tablespace is found and marked for
          truncation, the purge thread returns with increased frequency
          to quickly empty and truncate the undo tablespace.
    </p>
   </div>
   <p>
    To check the state of an undo tablespace, query the Information
        Schema
    <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
     <code class="literal">
      INNODB_TABLESPACES
     </code>
    </a>
    table.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17428241"><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 keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> STATE <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLESPACES
  <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">tablespace_name</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Once the undo tablespace is in an
    <code class="literal">
     empty
    </code>
    state, it can be reactivated by issuing the following statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8293818"><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 keyword">ALTER</span> <span class="token keyword">UNDO</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">SET</span> <span class="token keyword">ACTIVE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    An undo tablespace in an
    <code class="literal">
     empty
    </code>
    state can also
        be dropped. See
    <a class="xref" href="innodb-undo-tablespaces.html#innodb-drop-undo-tablespaces" title="Dropping Undo Tablespaces">
     Dropping Undo Tablespaces
    </a>
    .
   </p>
   <h6>
    <a name="idm46045165151776">
    </a>
    Expediting Automated Truncation of Undo Tablespaces
   </h6>
   <p>
    The purge thread is responsible for emptying and truncating undo
        tablespaces. By default, the purge thread looks for undo
        tablespaces to truncate once every 128 times that purge is
        invoked. The frequency with which the purge thread looks for
        undo tablespaces to truncate is controlled by 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, which has a default setting of 128.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3833177"><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 variable">@@innodb_purge_rseg_truncate_frequency</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>
<span class="token output"><span class="token punctuation">|</span> @@innodb_purge_rseg_truncate_frequency <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>
<span class="token output"><span class="token punctuation">|</span>                                    128 <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></code></pre>
   </div>
   <p>
    To increase the frequency, decrease the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_rseg_truncate_frequency">
     <code class="literal">
      innodb_purge_rseg_truncate_frequency
     </code>
    </a>
    setting. For example, to have the purge thread look for undo
        tablespaces once every 32 times that purge is invoked, set
    <a class="link" href="innodb-parameters.html#sysvar_innodb_purge_rseg_truncate_frequency">
     <code class="literal">
      innodb_purge_rseg_truncate_frequency
     </code>
    </a>
    to 32.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85578308"><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">SET</span> <span class="token keyword">GLOBAL</span> innodb_purge_rseg_truncate_frequency<span class="token operator">=</span><span class="token number">32</span><span class="token punctuation">;</span></code></pre>
   </div>
   <h6>
    <a name="idm46045165143520">
    </a>
    Performance Impact of Truncating Undo Tablespace Files
   </h6>
   <p>
    When an undo tablespace is truncated, the rollback segments in
        the undo tablespace are deactivated. The active rollback
        segments in other undo tablespaces assume responsibility for the
        entire system load, which may result in a slight performance
        degradation. The extent to which performance is affected depends
        on a number of factors:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Number of undo tablespaces
      </p>
     </li>
     <li class="listitem">
      <p>
       Number of undo logs
      </p>
     </li>
     <li class="listitem">
      <p>
       Undo tablespace size
      </p>
     </li>
     <li class="listitem">
      <p>
       Speed of the I/O subsystem
      </p>
     </li>
     <li class="listitem">
      <p>
       Existing long running transactions
      </p>
     </li>
     <li class="listitem">
      <p>
       System load
      </p>
     </li>
    </ul>
   </div>
   <p>
    The easiest way to avoid the potential performance impact is to
        increase the number of undo tablespaces.
   </p>
   <h6>
    <a name="idm46045165136512">
    </a>
    Monitoring Undo Tablespace Truncation
   </h6>
   <p>
    <code class="literal">
     undo
    </code>
    and
    <code class="literal">
     purge
    </code>
    subsystem
        counters are provided for monitoring background activities
        associated with undo log truncation. For counter names and
        descriptions, query 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.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19454764"><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 keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> SUBSYSTEM<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">'%truncate%'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For information about enabling counters and querying counter
        data, 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>
   <h6>
    <a name="idm46045165131072">
    </a>
    Undo Tablespace Truncation Limit
   </h6>
   <p>
    The number of truncate operations on the same undo tablespace
        between checkpoints is limited to 64. The limit prevents
        potential issues caused by an excessive number of undo
        tablespace truncate operations, which can occur if
    <a class="link" href="innodb-parameters.html#sysvar_innodb_max_undo_log_size">
     <code class="literal">
      innodb_max_undo_log_size
     </code>
    </a>
    is set
        too low on a busy system, for example. If the limit is exceeded,
        an undo tablespace can still be made inactive, but it is not
        truncated until after the next checkpoint. In MySQL
        8.4 the limit is 50000.
   </p>
   <h6>
    <a name="idm46045165128512">
    </a>
    Undo Tablespace Truncation Recovery
   </h6>
   <p>
    An undo tablespace truncate operation creates a temporary
    <code class="filename">
     undo_
     <em class="replaceable">
      <code>
       space_number
      </code>
     </em>
     _trunc.log
    </code>
    file in the server log directory. That log directory is defined
        by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_log_group_home_dir">
     <code class="literal">
      innodb_log_group_home_dir
     </code>
    </a>
    .
        If a system failure occurs during the truncate operation, the
        temporary log file permits the startup process to identify undo
        tablespaces that were being truncated and to continue the
        operation.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-undo-tablespace-status-variables">
       </a>
       Undo Tablespace Status Variables
      </h5>
     </div>
    </div>
   </div>
   <p>
    The following status variables permit tracking the total number
        of undo tablespaces, implicit
        (
    <code class="literal">
     InnoDB
    </code>
    -created) undo tablespaces, explicit
        (user-created) undo tablespaces, and the number of active undo
        tablespaces:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa421655"><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">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'Innodb_undo_tablespaces%'</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>
<span class="token output"><span class="token punctuation">|</span> Variable_name                    <span class="token punctuation">|</span> Value <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>
<span class="token output"><span class="token punctuation">|</span> Innodb_undo_tablespaces_total    <span class="token punctuation">|</span> 2     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Innodb_undo_tablespaces_implicit <span class="token punctuation">|</span> 2     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Innodb_undo_tablespaces_explicit <span class="token punctuation">|</span> 0     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Innodb_undo_tablespaces_active   <span class="token punctuation">|</span> 2     <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></code></pre>
   </div>
   <p>
    For status variable descriptions, see
    <a class="xref" href="server-status-variables.html" title="7.1.10 Server Status Variables">
     Section 7.1.10, “Server Status Variables”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
