<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-tablespace-autoextend-size">
      </a>
      17.6.3.9 Tablespace AUTOEXTEND_SIZE Configuration
     </h4>
    </div>
   </div>
  </div>
  <p>
   By default, when a file-per-table or general tablespace requires
      additional space, the tablespace is extended incrementally
      according to the following rules:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If the tablespace is less than an extent in size, it is
          extended one page at a time.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the tablespace is greater than 1 extent but smaller than 32
          extents in size, it is extended one extent at a time.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the tablespace is more than 32 extents in size, it is
          extended four extents at a time.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For information about extent size, see
   <a class="xref" href="innodb-file-space.html" title="17.11.2 File Space Management">
    Section 17.11.2, “File Space Management”
   </a>
   .
  </p>
  <p>
   The amount by which a file-per-table or general tablespace is
      extended is configurable by specifying the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   option. Configuring a larger
      extension size can help avoid fragmentation and facilitate
      ingestion of large amounts of data.
  </p>
  <p>
   To configure the extension size for a file-per-table tablespace,
      specify the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   size in a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80823585"><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">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">AUTOEXTEND_SIZE</span> <span class="token operator">=</span> 4M<span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">AUTOEXTEND_SIZE</span> <span class="token operator">=</span> 8M<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To configure the extension size for a general tablespace, specify
      the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   size in a
   <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    <code class="literal">
     CREATE TABLESPACE
    </code>
   </a>
   or
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2420417"><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">TABLESPACE</span> ts1 <span class="token keyword">AUTOEXTEND_SIZE</span> <span class="token operator">=</span> 4M<span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLESPACE</span> ts1 <span class="token keyword">AUTOEXTEND_SIZE</span> <span class="token operator">=</span> 8M<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>
    The
    <code class="literal">
     AUTOEXTEND_SIZE
    </code>
    option can also be used
        when creating an undo tablespace, but the extension behavior for
        undo tablespaces differs. For more information, see
    <a class="xref" href="innodb-undo-tablespaces.html" title="17.6.3.4 Undo Tablespaces">
     Section 17.6.3.4, “Undo Tablespaces”
    </a>
    .
   </p>
  </div>
  <p>
   The
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   setting must be a multiple
      of 4M. Specifying an
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   setting
      that is not a multiple of 4M returns an error.
  </p>
  <p>
   The
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   default setting is 0, which
      causes the tablespace to be extended according to the default
      behavior described above.
  </p>
  <p>
   The maximum allowed
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   is 4GB. The
      maximum tablespace size is described at
   <a class="xref" href="innodb-limits.html" title="17.21 InnoDB Limits">
    Section 17.21, “InnoDB Limits”
   </a>
   .
  </p>
  <p>
   The minimum
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   setting depends on
      the
   <code class="literal">
    InnoDB
   </code>
   page size, as shown in the following
      table:
  </p>
  <div class="informaltable">
   <table summary="The minimum AUTOEXTEND_SIZE for each InnoDB page size">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       InnoDB Page Size
      </th>
      <th>
       Minimum AUTOEXTEND_SIZE
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        4K
       </code>
      </td>
      <td>
       <code class="literal">
        4M
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        8K
       </code>
      </td>
      <td>
       <code class="literal">
        4M
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        16K
       </code>
      </td>
      <td>
       <code class="literal">
        4M
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        32K
       </code>
      </td>
      <td>
       <code class="literal">
        8M
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        64K
       </code>
      </td>
      <td>
       <code class="literal">
        16M
       </code>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   The default
   <code class="literal">
    InnoDB
   </code>
   page size is 16K (16384
      bytes). To determine the
   <code class="literal">
    InnoDB
   </code>
   page size for
      your MySQL instance, query the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   setting:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34077843"><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">@@GLOBAL.innodb_page_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>
<span class="token output"><span class="token punctuation">|</span> @@GLOBAL.innodb_page_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>
<span class="token output"><span class="token punctuation">|</span>                     16384 <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></code></pre>
  </div>
  <p>
   When the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   setting for a
      tablespace is altered, the first extension that occurs afterward
      increases the tablespace size to a multiple of the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   setting. Subsequent extensions
      are of the configured size.
  </p>
  <p>
   When a file-per-table or general tablespace is created with a
      non-zero
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   setting, the
      tablespace is initialized at the specified
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   size.
  </p>
  <p>
   <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
    <code class="literal">
     ALTER TABLESPACE
    </code>
   </a>
   cannot be used to
      configure the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   of a
      file-per-table tablespace.
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   must be used.
  </p>
  <p>
   For tables created in file-per-table tablespaces,
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   shows the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   option only when it is
      configured to a non-zero value.
  </p>
  <p>
   To determine the
   <code class="literal">
    AUTOEXTEND_SIZE
   </code>
   for any
   <code class="literal">
    InnoDB
   </code>
   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. For
      example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30664136"><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 keyword">NAME</span><span class="token punctuation">,</span> <span class="token keyword">AUTOEXTEND_SIZE</span> <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">'test/t1'</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>
<span class="token output"><span class="token punctuation">|</span> NAME    <span class="token punctuation">|</span> AUTOEXTEND_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>
<span class="token output"><span class="token punctuation">|</span> test/t1 <span class="token punctuation">|</span>         4194304 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> <span class="token keyword">AUTOEXTEND_SIZE</span> <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">'ts1'</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>
<span class="token output"><span class="token punctuation">|</span> NAME <span class="token punctuation">|</span> AUTOEXTEND_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>
<span class="token output"><span class="token punctuation">|</span> ts1  <span class="token punctuation">|</span>         4194304 <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></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    An
    <code class="literal">
     AUTOEXTEND_SIZE
    </code>
    of 0, which is the default
        setting, means that the tablespace is extended according to the
        default tablespace extension behavior described above.
   </p>
  </div>
 </div>
 <br/>
</div>
