<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-temporary-tablespace">
      </a>
      17.6.3.5 Temporary Tablespaces
     </h4>
    </div>
   </div>
  </div>
  <p>
   <code class="literal">
    InnoDB
   </code>
   uses session temporary tablespaces and a
      global temporary tablespace.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-session-temporary-tablespaces">
       </a>
       Session Temporary Tablespaces
      </h5>
     </div>
    </div>
   </div>
   <p>
    Session temporary tablespaces store user-created temporary
        tables and internal temporary tables created by the optimizer
        when
    <code class="literal">
     InnoDB
    </code>
    is configured as the storage
        engine for on-disk internal temporary tables. On-disk internal
        temporary tables use the
    <code class="literal">
     InnoDB
    </code>
    storage
        engine.
   </p>
   <p>
    Session temporary tablespaces are allocated to a session from a
        pool of temporary tablespaces on the first request to create an
        on-disk temporary table. A maximum of two tablespaces is
        allocated to a session, one for user-created temporary tables
        and the other for internal temporary tables created by the
        optimizer. The temporary tablespaces allocated to a session are
        used for all on-disk temporary tables created by the session.
        When a session disconnects, its temporary tablespaces are
        truncated and released back to the pool. A pool of 10 temporary
        tablespaces is created when the server is started. The size of
        the pool never shrinks and tablespaces are added to the pool
        automatically as necessary. The pool of temporary tablespaces is
        removed on normal shutdown or on an aborted initialization.
        Session temporary tablespace files are five pages in size when
        created and have an
    <code class="filename">
     .ibt
    </code>
    file name
        extension.
   </p>
   <p>
    A range of 400 thousand space IDs is reserved for session
        temporary tablespaces. Because the pool of session temporary
        tablespaces is recreated each time the server is started, space
        IDs for session temporary tablespaces are not persisted when the
        server is shut down, and may be reused.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_tablespaces_dir">
     <code class="literal">
      innodb_temp_tablespaces_dir
     </code>
    </a>
    variable defines the location where session temporary
        tablespaces are created. The default location is the
    <code class="filename">
     #innodb_temp
    </code>
    directory in the data
        directory. Startup is refused if the pool of temporary
        tablespaces cannot be created.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa20046876"><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">$&gt; </span><span class="token command">cd</span> <em class="replaceable">BASEDIR</em>/data/<span class="token comment" spellcheck="true">#innodb_temp</span>
<span class="token prompt">$&gt; </span><span class="token command">ls</span>
temp_10<span class="token punctuation">.</span>ibt  temp_2<span class="token punctuation">.</span>ibt  temp_4<span class="token punctuation">.</span>ibt  temp_6<span class="token punctuation">.</span>ibt  temp_8<span class="token punctuation">.</span>ibt
temp_1<span class="token punctuation">.</span>ibt   temp_3<span class="token punctuation">.</span>ibt  temp_5<span class="token punctuation">.</span>ibt  temp_7<span class="token punctuation">.</span>ibt  temp_9<span class="token punctuation">.</span>ibt</code></pre>
   </div>
   <p>
    In statement based replication (SBR) mode, temporary tables
        created on a replica reside in a single session temporary
        tablespace that is truncated only when the MySQL server is shut
        down.
   </p>
   <p>
    The
    <a class="link" href="information-schema-innodb-session-temp-tablespaces-table.html" title="28.4.22 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table">
     <code class="literal">
      INNODB_SESSION_TEMP_TABLESPACES
     </code>
    </a>
    table provides metadata about session temporary tablespaces.
   </p>
   <p>
    The Information Schema
    <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
     <code class="literal">
      INNODB_TEMP_TABLE_INFO
     </code>
    </a>
    table
        provides metadata about user-created temporary tables that are
        active in an
    <code class="literal">
     InnoDB
    </code>
    instance.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-global-temporary-tablespace">
       </a>
       Global Temporary Tablespace
      </h5>
     </div>
    </div>
   </div>
   <p>
    The global temporary tablespace (
    <code class="filename">
     ibtmp1
    </code>
    )
        stores rollback segments for changes made to user-created
        temporary tables.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    variable defines the relative path, name, size, and attributes
        for global temporary tablespace data files. If no value is
        specified for
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    , the
        default behavior is to create a single auto-extending data file
        named
    <code class="filename">
     ibtmp1
    </code>
    in the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
     <code class="literal">
      innodb_data_home_dir
     </code>
    </a>
    directory.
        The initial file size is slightly larger than 12MB.
   </p>
   <p>
    The global temporary tablespace is removed on normal shutdown or
        on an aborted initialization, and recreated each time the server
        is started. The global temporary tablespace receives a
        dynamically generated space ID when it is created. Startup is
        refused if the global temporary tablespace cannot be created.
        The global temporary tablespace is not removed if the server
        halts unexpectedly. In this case, a database administrator can
        remove the global temporary tablespace manually or restart the
        MySQL server. Restarting the MySQL server removes and recreates
        the global temporary tablespace automatically.
   </p>
   <p>
    The global temporary tablespace cannot reside on a raw device.
   </p>
   <p>
    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
        provides metadata about the global temporary tablespace. Issue a
        query similar to this one to view global temporary tablespace
        metadata:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa72721283"><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> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES <span class="token keyword">WHERE</span> TABLESPACE_NAME<span class="token operator">=</span><span class="token string">'innodb_temporary'</span>\G</code></pre>
   </div>
   <p>
    By default, the global temporary tablespace data file is
        autoextending and increases in size as necessary.
   </p>
   <p>
    To determine if a global temporary tablespace data file is
        autoextending, check the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    setting:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85064666"><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_temp_data_file_path</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>
<span class="token output"><span class="token punctuation">|</span> @@innodb_temp_data_file_path <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>
<span class="token output"><span class="token punctuation">|</span> ibtmp1:12M:autoextend        <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></code></pre>
   </div>
   <p>
    To check the size of global temporary tablespace data files,
        examine 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 using a query similar
        to this one:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51098060"><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> FILE_NAME<span class="token punctuation">,</span> TABLESPACE_NAME<span class="token punctuation">,</span> <span class="token keyword">ENGINE</span><span class="token punctuation">,</span> <span class="token keyword">INITIAL_SIZE</span><span class="token punctuation">,</span> TOTAL_EXTENTS<span class="token operator">*</span><span class="token keyword">EXTENT_SIZE</span>
       <span class="token keyword">AS</span> TotalSizeBytes<span class="token punctuation">,</span> DATA_FREE<span class="token punctuation">,</span> MAXIMUM_SIZE <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES
       <span class="token keyword">WHERE</span> TABLESPACE_NAME <span class="token operator">=</span> <span class="token string">'innodb_temporary'</span>\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
      FILE_NAME<span class="token punctuation">:</span> ./ibtmp1
TABLESPACE_NAME<span class="token punctuation">:</span> innodb_temporary
         ENGINE<span class="token punctuation">:</span> InnoDB
   INITIAL_SIZE<span class="token punctuation">:</span> 12582912
 TotalSizeBytes<span class="token punctuation">:</span> 12582912
      DATA_FREE<span class="token punctuation">:</span> 6291456
   MAXIMUM_SIZE<span class="token punctuation">:</span> NULL</span></code></pre>
   </div>
   <p>
    <code class="literal">
     TotalSizeBytes
    </code>
    shows the current size of the
        global temporary tablespace data file. For information about
        other field values, see
    <a class="xref" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
     Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”
    </a>
    .
   </p>
   <p>
    Alternatively, check the global temporary tablespace data file
        size on your operating system. The global temporary tablespace
        data file is located in the directory defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    variable.
   </p>
   <p>
    To reclaim disk space occupied by a global temporary tablespace
        data file, restart the MySQL server. Restarting the server
        removes and recreates the global temporary tablespace data file
        according to the attributes defined by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    .
   </p>
   <p>
    To limit the size of the global temporary tablespace data file,
        configure
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    to
        specify a maximum file size. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa91408607"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">innodb_temp_data_file_path</span><span class="token attr-value"><span class="token punctuation">=</span>ibtmp1:12M:autoextend:max:500M</span></code></pre>
   </div>
   <p>
    Configuring
    <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
     <code class="literal">
      innodb_temp_data_file_path
     </code>
    </a>
    requires restarting the server.
   </p>
  </div>
 </div>
 <br/>
</div>
