<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="table-size-limit">
      </a>
      10.4.6 Limits on Table Size
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226460592">
  </a>
  <a class="indexterm" name="idm46045226459104">
  </a>
  <a class="indexterm" name="idm46045226457616">
  </a>
  <a class="indexterm" name="idm46045226456128">
  </a>
  <a class="indexterm" name="idm46045226455056">
  </a>
  <a class="indexterm" name="idm46045226453568">
  </a>
  <a class="indexterm" name="idm46045226452080">
  </a>
  <p>
   The effective maximum table size for MySQL databases is usually
        determined by operating system constraints on file sizes, not by
        MySQL internal limits. For up-to-date information operating
        system file size limits, refer to the documentation specific to
        your operating system.
  </p>
  <p>
   Windows users, please note that FAT and VFAT (FAT32) are
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   considered suitable for production use
        with MySQL. Use NTFS instead.
  </p>
  <p>
   If you encounter a full-table error, there are several reasons
        why it might have occurred:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The disk might be full.
     </p>
    </li>
    <li class="listitem">
     <p>
      You are using
      <code class="literal">
       InnoDB
      </code>
      tables and have run
            out of room in an
      <code class="literal">
       InnoDB
      </code>
      tablespace file.
            The maximum tablespace size is also the maximum size for a
            table. For tablespace size limits, see
      <a class="xref" href="innodb-limits.html" title="17.21 InnoDB Limits">
       Section 17.21, “InnoDB Limits”
      </a>
      .
     </p>
     <p>
      Generally, partitioning of tables into multiple tablespace
            files is recommended for tables larger than 1TB in size.
     </p>
    </li>
    <li class="listitem">
     <p>
      You have hit an operating system file size limit. For
            example, you are using
      <code class="literal">
       MyISAM
      </code>
      tables on
            an operating system that supports files only up to 2GB in
            size and you have hit this limit for the data file or index
            file.
     </p>
    </li>
    <li class="listitem">
     <p>
      You are using a
      <code class="literal">
       MyISAM
      </code>
      table and the
            space required for the table exceeds what is permitted by
            the internal pointer size.
      <code class="literal">
       MyISAM
      </code>
      permits
            data and index files to grow up to 256TB by default, but
            this limit can be changed up to the maximum permissible size
            of 65,536TB (256
      <sup>
       7
      </sup>
      − 1
            bytes).
     </p>
     <p>
      If you need a
      <code class="literal">
       MyISAM
      </code>
      table that is larger
            than the default limit and your operating system supports
            large files, the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement supports
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
      and
      <code class="literal">
       MAX_ROWS
      </code>
      options. See
      <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       Section 15.1.20, “CREATE TABLE Statement”
      </a>
      . The server uses these
            options to determine how large a table to permit.
     </p>
     <p>
      If the pointer size is too small for an existing table, you
            can change the options with
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
            TABLE
       </code>
      </a>
      to increase a table's maximum
            permissible size. See
      <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       Section 15.1.9, “ALTER TABLE Statement”
      </a>
      .
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75659519"><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">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">MAX_ROWS</span><span class="token operator">=</span><span class="token number">1000000000</span> <span class="token keyword">AVG_ROW_LENGTH</span><span class="token operator">=</span><em class="replaceable">nnn</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You have to specify
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
      only
            for tables with
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns; in this case,
            MySQL cannot optimize the space required based only on the
            number of rows.
     </p>
     <p>
      To change the default size limit for
      <code class="literal">
       MyISAM
      </code>
      tables, set the
      <a class="link" href="server-system-variables.html#sysvar_myisam_data_pointer_size">
       <code class="literal">
        myisam_data_pointer_size
       </code>
      </a>
      ,
            which sets the number of bytes used for internal row
            pointers. The value is used to set the pointer size for new
            tables if you do not specify the
      <code class="literal">
       MAX_ROWS
      </code>
      option. The value of
      <a class="link" href="server-system-variables.html#sysvar_myisam_data_pointer_size">
       <code class="literal">
        myisam_data_pointer_size
       </code>
      </a>
      can be from 2 to 7. For example, for tables that use the
            dynamic storage format, a value of 4 permits tables up to
            4GB; a value of 6 permits tables up to 256TB. Tables that
            use the fixed storage format have a larger maximum data
            length. For storage format characteristics, see
      <a class="xref" href="myisam-table-formats.html" title="18.2.3 MyISAM Table Storage Formats">
       Section 18.2.3, “MyISAM Table Storage Formats”
      </a>
      .
     </p>
     <p>
      You can check the maximum data and index sizes by using this
            statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa22105252"><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">SHOW</span> <span class="token keyword">TABLE</span> <span class="token keyword">STATUS</span> <span class="token keyword">FROM</span> <em class="replaceable">db_name</em> <span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">tbl_name</em>'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You also can use
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -dv
            /path/to/table-index-file
        </strong>
       </span>
      </a>
      . See
      <a class="xref" href="show.html" title="15.7.7 SHOW Statements">
       Section 15.7.7, “SHOW Statements”
      </a>
      , or
      <a class="xref" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”
      </a>
      .
     </p>
     <p>
      Other ways to work around file-size limits for
      <code class="literal">
       MyISAM
      </code>
      tables are as follows:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         If your large table is read only, you can use
         <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
          <span class="command">
           <strong>
            myisampack
           </strong>
          </span>
         </a>
         to compress it.
         <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
          <span class="command">
           <strong>
            myisampack
           </strong>
          </span>
         </a>
         usually compresses a table
                by at least 50%, so you can have, in effect, much bigger
                tables.
         <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
          <span class="command">
           <strong>
            myisampack
           </strong>
          </span>
         </a>
         also can merge
                multiple tables into a single table. See
         <a class="xref" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
          Section 6.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         MySQL includes a
         <code class="literal">
          MERGE
         </code>
         library that
                enables you to handle a collection of
         <code class="literal">
          MyISAM
         </code>
         tables that have identical
                structure as a single
         <code class="literal">
          MERGE
         </code>
         table.
                See
         <a class="xref" href="merge-storage-engine.html" title="18.7 The MERGE Storage Engine">
          Section 18.7, “The MERGE Storage Engine”
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      You are using the
      <code class="literal">
       MEMORY
      </code>
      (
      <code class="literal">
       HEAP
      </code>
      ) storage engine; in this case you
            need to increase the value of the
      <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
       <code class="literal">
        max_heap_table_size
       </code>
      </a>
      system
            variable. See
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
