MySQL Version 3.22 had a 4 GB (4 gigabyte) limit on table size. With the
MyISAM storage engine in
MySQL Version 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
databases now normally is determined by operating system constraints
on file sizes, not by MySQL internal limits.
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 64 TB.
The following table lists some examples of operating system file-size limits:
|Operating System||File-Size Limit|
|Linux-Intel 32-bit||2 GB, much more when using LFS|
|Linux-Alpha||8 TB (?)|
|Solaris 2.5.1||2 GB (4GB possible with patch)|
|Solaris 2.6||4 GB (can be changed with flag)|
|Solaris 2.7 Intel||4 GB|
|Solaris 2.7 UltraSPARC||512 GB|
On Linux 2.2, you can get
MyISAM tables larger than 2 GB in size by
using the LFS patch for the ext2 filesystem. On Linux 2.4, patches also
exist for ReiserFS to get support for big files. Most current Linux
distributions are based on kernel 2.4 and already include all the required
Large File Support (LFS) patches. However, the maximum available file size
still depends on several factors, one of them being the file system used to
store MySQL tables.
For a very 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.
MyISAM tables with an internal
structure that allows a maximum size of about 4 GB. You can
check the maximum table size for a table with the
SHOW TABLE STATUS
command or with the
myisamchk -dv table_name.
See section 13.5.3
If you need a
MyISQM table that will be larger than 4 GB in size (and your
operating system supports large files), the
CREATE TABLE statement
See section 13.2.5
CREATE TABLE Syntax.
You can also change these options with
ALTER TABLE after the table has
been created, to increase the table's maximum allowable size.
See section 13.2.2
ALTER TABLE Syntax.
Other ways to work around file-size limits for
MyISAM tables are as
myisampackto compress it.
myisampackusually compresses a table by at least 50%, so you can have, in effect, much bigger tables.
myisampackalso can merge multiple tables into a single table. See section 8.11
myisampack, The MySQL Compressed Read-only Table Generator.
MyISAMdatafiles is by using the
RAIDoptions. See section 13.2.5
MERGElibrary that allows you to handle a collection of
MyISAMtables that have identical structure as a single
MERGEtable. See section 14.2