(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