(mysql.info) table-size

Info Catalog

(mysql.info) stability

(mysql.info) what-is

(mysql.info) year-2000-compliance

 
 1.4.4 How Large MySQL Tables Can Be
 -----------------------------------
 
 MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the
 `MyISAM' storage engine in MySQL 3.23, the maximum table size was
 increased to 65536 terabytes (2567 - 1 bytes). With this larger allowed
 table size, the maximum effective table size for MySQL databases is
 usually determined by operating system constraints on file sizes, not
 by MySQL internal limits.
 
 The `InnoDB' storage engine maintains `InnoDB' tables within a
 tablespace that can be created from several files. This allows a table
 to exceed the maximum individual file size. The tablespace can include
 raw disk partitions, which allows extremely large tables. The maximum
 tablespace size is 64TB.
 
 The following table lists some examples of operating system file-size
 limits. This is only a rough guide and is not intended to be
 definitive. For the most up-to-date information, be sure to check the
 documentation specific to your operating system.
 
 *Operating System*     *File-size Limit*
 Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
 Linux 2.4+             (using ext3 filesystem) 4TB
 Solaris 9/10           16TB
 NetWare w/NSS          8TB
 filesystem             
 Win32 w/ FAT/FAT32     2GB/4GB
 Win32 w/ NTFS          2TB (possibly larger)
 MacOS X w/ HFS+        2TB
 
 On Linux 2.2, you can get `MyISAM' tables larger than 2GB in size by
 using the Large File Support (LFS) patch for the ext2 filesystem. On
 Linux 2.4, patches also exist for ReiserFS to get support for big files
 (up to 2TB). Most current Linux distributions are based on kernel 2.4
 and include all the required LFS patches. With JFS and XFS, petabyte
 and larger files are possible on Linux. However, the maximum available
 file size still depends on several factors, one of them being the
 filesystem used to store MySQL tables.
 
 For a detailed overview about LFS in Linux, have a look at Andreas
 Jaeger's `Large File Support in Linux' page at
 `http://www.suse.de/~aj/linux_lfs.html'.
 
 Windows users please note: FAT and VFAT (FAT32) are _not_ considered
 suitable for production use with MySQL. Use NTFS instead.
 
 By default, MySQL creates `MyISAM' tables with an internal structure
 that allows a maximum size of about 4GB. You can check the maximum
 table size for a `MyISAM' table with the `SHOW TABLE STATUS' statement
 or with `myisamchk -dv TBL_NAME'. See  show.
 
 If you need a `MyISAM' table that is larger than 4GB and your operating
 system supports large files, the `CREATE TABLE' statement supports
 `AVG_ROW_LENGTH' and `MAX_ROWS' options. See  create-table. You
 can also change these options with `ALTER TABLE' to increase a table's
 maximum allowable size after the table has been created.  See 
 alter-table.
 
 Other ways to work around file-size limits for `MyISAM' tables are as
 follows:
 
    * If your large table is read-only, you can use `myisampack' to
      compress it.  `myisampack' usually compresses a table by at least
      50%, so you can have, in effect, much bigger tables.  `myisampack'
      also can merge multiple tables into a single table. See 
      myisampack.
 
    * MySQL includes a `MERGE' library that allows you to handle a
      collection of `MyISAM' tables that have identical structure as a
      single `MERGE' table. See  merge-storage-engine.
 

Info Catalog

(mysql.info) stability

(mysql.info) what-is

(mysql.info) year-2000-compliance