<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-system-tablespace">
      </a>
      17.6.3.1 The System Tablespace
     </h4>
    </div>
   </div>
  </div>
  <p>
   The system tablespace is the storage area for the change buffer.
      It may also contain table and index data if tables are created in
      the system tablespace rather than file-per-table or general
      tablespaces.
  </p>
  <p>
   The system tablespace can have one or more data files. By default,
      a single system tablespace data file, named
   <code class="filename">
    ibdata1
   </code>
   , is created in the data directory.
      The size and number of system tablespace data files is defined by
      the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
    <code class="literal">
     innodb_data_file_path
    </code>
   </a>
   startup
      option. For configuration information, see
   <a class="xref" href="innodb-init-startup-configuration.html#innodb-startup-data-file-configuration" title="System Tablespace Data File Configuration">
    System Tablespace Data File Configuration
   </a>
   .
  </p>
  <p>
   Additional information about the system tablespace is provided
      under the following topics in the section:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-system-tablespace.html#innodb-resize-system-tablespace" title="Resizing the System Tablespace">
       Resizing the System Tablespace
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-system-tablespace.html#innodb-raw-devices" title="Using Raw Disk Partitions for the System Tablespace">
       Using Raw Disk Partitions for the System Tablespace
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-resize-system-tablespace">
       </a>
       Resizing the System Tablespace
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045165573792">
   </a>
   <p>
    This section describes how to increase or decrease the size of
        the system tablespace.
   </p>
   <h6>
    <a name="idm46045165571824">
    </a>
    Increasing the Size of the System Tablespace
   </h6>
   <p>
    The easiest way to increase the size of the system tablespace is
        to configure it to be auto-extending. To do so, specify the
    <code class="literal">
     autoextend
    </code>
    attribute for the last data file
        in the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
     <code class="literal">
      innodb_data_file_path
     </code>
    </a>
    setting, and restart the server. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-ini"><div class="docs-select-all right" id="sa61591659"><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 constant">innodb_data_file_path</span><span class="token attr-value"><span class="token punctuation">=</span>ibdata1:10M:autoextend</span></code></pre>
   </div>
   <p>
    When the
    <code class="literal">
     autoextend
    </code>
    attribute is specified,
        the data file automatically increases in size by 8MB increments
        as space is required. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_autoextend_increment">
     <code class="literal">
      innodb_autoextend_increment
     </code>
    </a>
    variable controls the increment size.
   </p>
   <p>
    You can also increase system tablespace size by adding another
        data file. To do so:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Stop the MySQL server.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the last data file in the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
        <code class="literal">
         innodb_data_file_path
        </code>
       </a>
       setting is defined with the
       <code class="literal">
        autoextend
       </code>
       attribute, remove it, and modify the size attribute to
            reflect the current data file size. To determine the
            appropriate data file size to specify, check your file
            system for the file size, and round that value down to the
            closest MB value, where a MB is equal to 1024 x 1024 bytes.
      </p>
     </li>
     <li class="listitem">
      <p>
       Append a new data file to the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
        <code class="literal">
         innodb_data_file_path
        </code>
       </a>
       setting, optionally specifying the
       <code class="literal">
        autoextend
       </code>
       attribute. The
       <code class="literal">
        autoextend
       </code>
       attribute can be specified
            only for the last data file in the
       <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
        <code class="literal">
         innodb_data_file_path
        </code>
       </a>
       setting.
      </p>
     </li>
     <li class="listitem">
      <p>
       Start the MySQL server.
      </p>
     </li>
    </ol>
   </div>
   <p>
    For example, this tablespace has one auto-extending data file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa52578199"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend</code></pre>
   </div>
   <p>
    Suppose that the data file has grown to 988MB over time. This is
        the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
     <code class="literal">
      innodb_data_file_path
     </code>
    </a>
    setting after modifying the size attribute to reflect the
        current data file size, and after specifying a new 50MB
        auto-extending data file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa29293315"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend</code></pre>
   </div>
   <p>
    When adding a new data file, do not specify an existing file
        name.
    <code class="literal">
     InnoDB
    </code>
    creates and initializes the new
        data file when you start the server.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     You cannot increase the size of an existing system tablespace
          data file by changing its size attribute. For example,
          changing the
     <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
      <code class="literal">
       innodb_data_file_path
      </code>
     </a>
     setting
          from
     <code class="literal">
      ibdata1:10M:autoextend
     </code>
     to
     <code class="literal">
      ibdata1:12M:autoextend
     </code>
     produces the
          following error when starting the server:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa84131290"><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 property"><span class="token punctuation">[</span>ERROR<span class="token punctuation">]</span></span> <span class="token punctuation">[</span>MY-012263<span class="token punctuation">]</span> <span class="token punctuation">[</span>InnoDB<span class="token punctuation">]</span> The Auto-extending innodb_system
data file <span class="token atrule">'./ibdata1'</span> is of a different size 640 pages <span class="token punctuation">(</span>rounded down to MB<span class="token punctuation">)</span> than
specified in the <span class="token punctuation">.</span>cnf file<span class="token punctuation">:</span> initial 768 pages<span class="token punctuation">,</span> max 0 <span class="token punctuation">(</span>relevant if non-zero<span class="token punctuation">)</span> pages!</code></pre>
    </div>
    <p>
     The error indicates that the existing data file size
          (expressed in
     <code class="literal">
      InnoDB
     </code>
     pages) is different
          from the data file size specified in the configuration file.
          If you encounter this error, restore the previous
     <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
      <code class="literal">
       innodb_data_file_path
      </code>
     </a>
     setting, and refer to the system tablespace resizing
          instructions.
    </p>
   </div>
   <h6>
    <a name="idm46045165540320">
    </a>
    Decreasing the Size of the InnoDB System Tablespace
   </h6>
   <p>
    Decreasing the size of an existing system tablespace is not
        supported. The only option to achieve a smaller system
        tablespace is to restore your data from a backup to a new MySQL
        instance created with the desired system tablespace size
        configuration.
   </p>
   <p>
    For information about creating backups, see
    <a class="xref" href="innodb-backup.html" title="17.18.1 InnoDB Backup">
     Section 17.18.1, “InnoDB Backup”
    </a>
    .
   </p>
   <p>
    For information about configuring data files for a new system
        tablespace. See
    <a class="xref" href="innodb-init-startup-configuration.html#innodb-startup-data-file-configuration" title="System Tablespace Data File Configuration">
     System Tablespace Data File Configuration
    </a>
    .
   </p>
   <p>
    To avoid a large system tablespace, consider using
        file-per-table tablespaces or general tablespaces for your data.
        File-per-table tablespaces are the default tablespace type and
        are used implicitly when creating an
    <code class="literal">
     InnoDB
    </code>
    table. Unlike the system tablespace, file-per-table tablespaces
        return disk space to the operating system when they are
        truncated or dropped. For more information, see
    <a class="xref" href="innodb-file-per-table-tablespaces.html" title="17.6.3.2 File-Per-Table Tablespaces">
     Section 17.6.3.2, “File-Per-Table Tablespaces”
    </a>
    . General
        tablespaces are multi-table tablespaces that can also be used as
        an alternative to the system tablespace. See
    <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
     Section 17.6.3.3, “General Tablespaces”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-raw-devices">
       </a>
       Using Raw Disk Partitions for the System Tablespace
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045165532208">
   </a>
   <a class="indexterm" name="idm46045165530752">
   </a>
   <p>
    Raw disk partitions can be used as system tablespace data files.
        This technique enables nonbuffered I/O on Windows and some Linux
        and Unix systems without file system overhead. Perform tests
        with and without raw partitions to verify whether they improve
        performance on your system.
   </p>
   <p>
    When using a raw disk partition, ensure that the user ID that
        runs the MySQL server has read and write privileges for that
        partition. For example, if running the server as the
    <code class="literal">
     mysql
    </code>
    user, the partition must be readable
        and writeable by
    <code class="literal">
     mysql
    </code>
    . If running the server
        with the
    <a class="link" href="server-options.html#option_mysqld_memlock">
     <code class="option">
      --memlock
     </code>
    </a>
    option, the
        server must be run as
    <code class="literal">
     root
    </code>
    , so the partition
        must be readable and writeable by
    <code class="literal">
     root
    </code>
    .
   </p>
   <p>
    The procedures described below involve option file modification.
        For additional information, see
    <a class="xref" href="option-files.html" title="6.2.2.2 Using Option Files">
     Section 6.2.2.2, “Using Option Files”
    </a>
    .
   </p>
   <h6>
    <a name="idm46045165523152">
    </a>
    Allocating a Raw Disk Partition on Linux and Unix Systems
   </h6>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       To use a raw device for a new server instance, first prepare
            the configuration file by setting
       <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
        <code class="literal">
         innodb_data_file_path
        </code>
       </a>
       with
            the
       <code class="literal">
        raw
       </code>
       keyword. For example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa91974884"><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_data_home_dir</span><span class="token attr-value"><span class="token punctuation">=</span></span>
<span class="token constant">innodb_data_file_path</span><span class="token attr-value"><span class="token punctuation">=</span>/dev/hdd1:3Graw;/dev/hdd2:2Graw</span></code></pre>
      </div>
      <p>
       The partition must be at least as large as the size that you
            specify. Note that 1MB in
       <code class="literal">
        InnoDB
       </code>
       is 1024
            × 1024 bytes, whereas 1MB in disk specifications
            usually means 1,000,000 bytes.
      </p>
     </li>
     <li class="listitem">
      <p>
       Then initialize the server for the first time by using
       <a class="link" href="server-options.html#option_mysqld_initialize">
        <code class="option">
         --initialize
        </code>
       </a>
       or
       <a class="link" href="server-options.html#option_mysqld_initialize-insecure">
        <code class="option">
         --initialize-insecure
        </code>
       </a>
       . InnoDB
            notices the
       <code class="literal">
        raw
       </code>
       keyword and initializes
            the new partition, and then it stops the server.
      </p>
     </li>
     <li class="listitem">
      <p>
       Now restart the server.
       <code class="literal">
        InnoDB
       </code>
       now
            permits changes to be made.
      </p>
     </li>
    </ol>
   </div>
   <h6>
    <a name="idm46045165510896">
    </a>
    Allocating a Raw Disk Partition on Windows
   </h6>
   <p>
    On Windows systems, the same steps and accompanying guidelines
        described for Linux and Unix systems apply except that the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_data_file_path">
     <code class="literal">
      innodb_data_file_path
     </code>
    </a>
    setting
        differs slightly on Windows. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa29227001"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Graw</code></pre>
   </div>
   <p>
    The
    <code class="filename">
     //./
    </code>
    corresponds to the Windows syntax
        of
    <code class="filename">
     \\.\
    </code>
    for accessing physical drives. In
        the example above,
    <code class="literal">
     D:
    </code>
    is the drive letter of
        the partition.
   </p>
  </div>
 </div>
 <br/>
</div>
