reference manuals search engine

MySQL Reference Manual - 4.0.20

1.2.4 How Big MySQL Tables Can Be

MySQL Version 3.22 had a 4GB (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 MySQL databases now normally is 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:

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. Most current Linux distributions are based on kernel 2.4 and already include all the required LFS patches. However, the maximum available file size still depends on several factors, one of them being the filesystem 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.

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 command or with the myisamchk -dv tbl_name. See section 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. See section 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. See section 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 section myisampack.

• Another way to get around the operating system file limit for MyISAM data files is by using the RAID options. See section 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. See section MERGE tables.

This document was generated on July, 26 2004 using texi2html 1.70.