DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Table size

Info Catalog (mysql.info.gz) Stability (mysql.info.gz) What-is (mysql.info.gz) Year 2000 compliance
 
 1.2.4 How Big 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 8 million terabytes (2 ^ 63 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        2GB (LFS: 4GB)
 32-bit                 
 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 table with the `SHOW TABLE STATUS' statement or with
 `myisamchk -dv TBL_NAME'.   `SHOW' SHOW.
 
 If you need a `MyISAM' table that will be larger than 4GB in size (and
 your operating system supports large files), the `CREATE TABLE'
 statement allows `AVG_ROW_LENGTH' and `MAX_ROWS' options.  
 `CREATE TABLE' CREATE TABLE.  You can also change these options with
 `ALTER TABLE' after the table has been created, to increase the table's
 maximum allowable size.   `ALTER TABLE' 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.  
      `myisampack' myisampack.
 
    * Another way to get around the operating system file limit for
      `MyISAM' data files is by using the `RAID' options.   `CREATE
      TABLE' CREATE TABLE.
 
    * MySQL includes a `MERGE' library that allows you to handle a
      collection of `MyISAM' tables that have identical structure as a
      single `MERGE' table.   `MERGE' storage engine MERGE storage
      engine.
 
 
Info Catalog (mysql.info.gz) Stability (mysql.info.gz) What-is (mysql.info.gz) Year 2000 compliance
automatically generated byinfo2html