(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
automatically generated byinfo2html