1.2.4 How Big Can MySQL Tables Be?

MySQL Version 3.22 had a 4 GB (4 gigabyte) limit on table size. With the MyISAM table type in MySQL Version 3.23, the maximum table size was pushed up to 8 million terabytes (2 ^ 63 bytes).

Note, however, that operating systems have their own file-size limits. Here are some examples:

On Linux 2.2 you can get 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.

In effect, then, the table size for MySQL databases is normally limited by the operating system.

By default, MySQL tables have 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 4.5.7 SHOW Syntax.

If you need a table that will be larger than 4 GB in size (and your operating system supports this), set the AVG_ROW_LENGTH and MAX_ROWS parameters accordingly when you create your table. See section 6.5.3 CREATE TABLE Syntax. You can also set these parameters later, with ALTER TABLE. See section 6.5.4 ALTER TABLE Syntax.

If your big table is a read-only table, you could use myisampack to merge and compress many tables into one. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. See section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator.

You can get around the operating system file limit for MyISAM datafiles using the RAID option. See section 6.5.3 CREATE TABLE Syntax.

Another solution can be the included MERGE library, which allows you to handle a collection of identical tables as one. See section 7.2 MERGE Tables.

User Comments

Add your own comment.