<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-file-per-table-tablespaces">
      </a>
      17.6.3.2 File-Per-Table Tablespaces
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045165503168">
  </a>
  <a class="indexterm" name="idm46045165501664">
  </a>
  <p>
   A file-per-table tablespace contains data and indexes for a single
   <code class="literal">
    InnoDB
   </code>
   table, and is stored on the file system
      in a single data file.
  </p>
  <p>
   File-per-table tablespace characteristics 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-file-per-table-tablespaces.html#innodb-file-per-table-configuration" title="File-Per-Table Tablespace Configuration">
       File-Per-Table Tablespace Configuration
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-file-per-table-tablespaces.html#innodb-file-per-table-data-files" title="File-Per-Table Tablespace Data Files">
       File-Per-Table Tablespace Data Files
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-file-per-table-tablespaces.html#innodb-file-per-table-advantages" title="File-Per-Table Tablespace Advantages">
       File-Per-Table Tablespace Advantages
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-file-per-table-tablespaces.html#innodb-file-per-table-disadvantages" title="File-Per-Table Tablespace Disadvantages">
       File-Per-Table Tablespace Disadvantages
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-file-per-table-configuration">
       </a>
       File-Per-Table Tablespace Configuration
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     InnoDB
    </code>
    creates tables in file-per-table
        tablespaces by default. This behavior is controlled by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    variable.
        Disabling
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    causes
    <code class="literal">
     InnoDB
    </code>
    to create tables in the system
        tablespace.
   </p>
   <p>
    An
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="literal">
      innodb_file_per_table
     </code>
    </a>
    setting can be specified in an option file or configured at
        runtime using a
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
        GLOBAL
     </code>
    </a>
    statement. Changing the setting at runtime
        requires privileges sufficient to set global system variables.
        See
    <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
     Section 7.1.9.1, “System Variable Privileges”
    </a>
    .
   </p>
   <p>
    Option file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa89140588"><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_file_per_table</span><span class="token attr-value"><span class="token punctuation">=</span>ON</span></code></pre>
   </div>
   <p>
    Using
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
        GLOBAL
     </code>
    </a>
    at runtime:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10292868"><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_file_per_table<span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-file-per-table-data-files">
       </a>
       File-Per-Table Tablespace Data Files
      </h5>
     </div>
    </div>
   </div>
   <p>
    A file-per-table tablespace is created in an
    <code class="filename">
     .ibd
    </code>
    data file in a schema directory under
        the MySQL data directory. The
    <code class="filename">
     .ibd
    </code>
    file is
        named for the table
        (
    <code class="filename">
     <em class="replaceable">
      <code>
       table_name
      </code>
     </em>
     .ibd
    </code>
    ).
        For example, the data file for table
    <code class="literal">
     test.t1
    </code>
    is created in the
    <code class="filename">
     test
    </code>
    directory under the
        MySQL data directory:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34238097"><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">USE</span> test<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXIT</span><span class="token punctuation">;</span></code></pre>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa73359084"><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">path</em>/<em class="replaceable">to</em>/<em class="replaceable">mysql</em>/data/test
<span class="token prompt">$&gt; </span><span class="token command">ls</span>
t1<span class="token punctuation">.</span>ibd</code></pre>
   </div>
   <p>
    You can use the
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause of the
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statement to
        implicitly create a file-per-table tablespace data file outside
        of the data directory. For more information, see
    <a class="xref" href="innodb-create-table-external.html" title="17.6.1.2 Creating Tables Externally">
     Section 17.6.1.2, “Creating Tables Externally”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-file-per-table-advantages">
       </a>
       File-Per-Table Tablespace Advantages
      </h5>
     </div>
    </div>
   </div>
   <p>
    File-per-table tablespaces have the following advantages over
        shared tablespaces such as the system tablespace or general
        tablespaces.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Disk space is returned to the operating system after
            truncating or dropping a table created in a file-per-table
            tablespace. Truncating or dropping a table stored in a
            shared tablespace creates free space within the shared
            tablespace data file, which can only be used for
       <code class="literal">
        InnoDB
       </code>
       data. In other words, a shared
            tablespace data file does not shrink in size after a table
            is truncated or dropped.
      </p>
     </li>
     <li class="listitem">
      <p>
       A table-copying
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       operation on a table that resides in a shared tablespace can
            increase the amount of disk space occupied by the
            tablespace. Such operations may require as much additional
            space as the data in the table plus indexes. This space is
            not released back to the operating system as it is for
            file-per-table tablespaces.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
        <code class="literal">
         TRUNCATE TABLE
        </code>
       </a>
       performance is
            better when executed on tables that reside in file-per-table
            tablespaces.
      </p>
     </li>
     <li class="listitem">
      <p>
       File-per-table tablespace data files can be created on
            separate storage devices for I/O optimization, space
            management, or backup purposes. See
       <a class="xref" href="innodb-create-table-external.html" title="17.6.1.2 Creating Tables Externally">
        Section 17.6.1.2, “Creating Tables Externally”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       You can import a table that resides in file-per-table
            tablespace from another MySQL instance. See
       <a class="xref" href="innodb-table-import.html" title="17.6.1.3 Importing InnoDB Tables">
        Section 17.6.1.3, “Importing InnoDB Tables”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Tables created in file-per-table tablespaces support
            features associated with
       <code class="literal">
        DYNAMIC
       </code>
       and
       <code class="literal">
        COMPRESSED
       </code>
       row formats, which are not
            supported by the system tablespace. See
       <a class="xref" href="innodb-row-format.html" title="17.10 InnoDB Row Formats">
        Section 17.10, “InnoDB Row Formats”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Tables stored in individual tablespace data files can save
            time and improve chances for a successful recovery when data
            corruption occurs, when backups or binary logs are
            unavailable, or when the MySQL server instance cannot be
            restarted.
      </p>
     </li>
     <li class="listitem">
      <p>
       Tables created in file-per-table tablespaces can be backed
            up or restored quickly using MySQL Enterprise Backup, without interrupting the
            use of other
       <code class="literal">
        InnoDB
       </code>
       tables. This is
            beneficial for tables on varying backup schedules or that
            require backup less frequently. See
       <a class="ulink" href="/doc/mysql-enterprise-backup/8.4/en/partial.html" target="_top">
        Making a Partial Backup
       </a>
       for details.
      </p>
     </li>
     <li class="listitem">
      <p>
       File-per-table tablespaces permit monitoring table size on
            the file system by monitoring the size of the tablespace
            data file.
      </p>
     </li>
     <li class="listitem">
      <p>
       Common Linux file systems do not permit concurrent writes to
            a single file such as a shared tablespace data file when
       <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_method">
        <code class="literal">
         innodb_flush_method
        </code>
       </a>
       is set
            to
       <code class="literal">
        O_DIRECT
       </code>
       . As a result, there are
            possible performance improvements when using file-per-table
            tablespaces in conjunction with this setting.
      </p>
     </li>
     <li class="listitem">
      <p>
       Tables in a shared tablespace are limited in size by the
            64TB tablespace size limit. By comparison, each
            file-per-table tablespace has a 64TB size limit, which
            provides plenty of room for individual tables to grow in
            size.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-file-per-table-disadvantages">
       </a>
       File-Per-Table Tablespace Disadvantages
      </h5>
     </div>
    </div>
   </div>
   <p>
    File-per-table tablespaces have the following disadvantages
        compared to shared tablespaces such as the system tablespace or
        general tablespaces.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       With file-per-table tablespaces, each table may have unused
            space that can only be utilized by rows of the same table,
            which can lead to wasted space if not properly managed.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        fsync
       </code>
       operations are performed on
            multiple file-per-table data files instead of a single
            shared tablespace data file. Because
       <code class="literal">
        fsync
       </code>
       operations are per file, write
            operations for multiple tables cannot be combined, which can
            result in a higher total number of
       <code class="literal">
        fsync
       </code>
       operations.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
        <span class="command">
         <strong>
          mysqld
         </strong>
        </span>
       </a>
       must keep an open file handle for
            each file-per-table tablespace, which may impact performance
            if you have numerous tables in file-per-table tablespaces.
      </p>
     </li>
     <li class="listitem">
      <p>
       More file descriptors are required when each table has its
            own data file.
      </p>
     </li>
     <li class="listitem">
      <p>
       There is potential for more fragmentation, which can impede
       <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
        <code class="literal">
         DROP TABLE
        </code>
       </a>
       and table scan
            performance. However, if fragmentation is managed,
            file-per-table tablespaces can improve performance for these
            operations.
      </p>
     </li>
     <li class="listitem">
      <p>
       The buffer pool is scanned when dropping a table that
            resides in a file-per-table tablespace, which can take
            several seconds for large buffer pools. The scan is
            performed with a broad internal lock, which may delay other
            operations.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="innodb-parameters.html#sysvar_innodb_autoextend_increment">
        <code class="literal">
         innodb_autoextend_increment
        </code>
       </a>
       variable, which defines the increment size for extending the
            size of an auto-extending shared tablespace file when it
            becomes full, does not apply to file-per-table tablespace
            files, which are auto-extending regardless of the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_autoextend_increment">
        <code class="literal">
         innodb_autoextend_increment
        </code>
       </a>
       setting. Initial file-per-table tablespace extensions are by
            small amounts, after which extensions occur in increments of
            4MB.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
