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.
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 https://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
Section 13.5.4, “SHOW Syntax”.
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 Section 13.1.5, “CREATE TABLE Syntax”. You can also change
these options with ALTER TABLE to increase a
table's maximum allowable size after the table has been created.
See Section 13.1.2, “ALTER TABLE Syntax”.
Other ways to work around file-size limits for
MyISAM tables are as follows:
