DEVELOPER ZONE ::
Login / Register
The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. 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|
|Win32 w/ FAT/FAT32||2GB/4GB|
|Win32 w/ NTFS||2TB (possibly larger)|
|Linux 2.2-Intel 32-bit||2GB (LFS: 4GB)|
|Linux 2.4+||(using ext3 filesystem) 4TB|
|MacOS X w/ HFS+||2TB|
|NetWare w/NSS filesystem||8TB|
Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.
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. Most current Linux distributions are based on kernel 2.4 or higher and include all the required LFS patches. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). With JFS and XFS, petabyte and larger files are possible on Linux.
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.
If you do encounter a full-table error, there are several reasons why it might have occurred:
You are using a MySQL server older than 3.23 and an in-memory temporary table becomes larger than
tmp_table_size bytes. To avoid this problem, you can use the
--tmp_table_size= option to make mysqld increase the temporary table size or use the SQL option
SQL_BIG_TABLES before you issue the problematic query. See Section 13.5.3, “
Under Windows you may get an error
OS error code 22: Invalid argument if you are using NTFS file system compression and the size od your data or temporary table files exceesds 30GB. This is due to a limitation in the NTFS file system that it is unable to compress files of this size. See http://msdn2.microsoft.com/en-us/library/aa364219.aspx.
You can also start mysqld with the
--big-tables option. This is exactly the same as using
SQL_BIG_TABLES for all queries.
As of MySQL 3.23, this problem should not occur. If an in-memory temporary table becomes larger than
tmp_table_size, the server automatically converts it to a disk-based
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.
If you are using
InnoDB tables and run out of room in the
InnoDB tablespace. In this case, the solution is to extend the
InnoDB tablespace. See Section 14.2.7, “Adding and Removing
InnoDB Data and Log Files”.
You are using
MyISAM tables on an operating system that supports files only up to 2GB in size and you have hit this limit for the data file or index file.
You are using a
MyISAM table and the space required for the table exceeds what is allowed by the internal pointer size.
MyISAM creates tables to allow up to 4GB by default (256TB as of MySQL 5.0.6), but this limit can be changed up to the maximum allowable size of 65,536TB (2567 – 1 bytes).
If you need a
MyISAM table that is larger than the default limit and your operating system supports large files, the
CREATE TABLE statement supports
MAX_ROWS options. See Section 13.1.5, “
CREATE TABLE Syntax”. The server uses these options to determine how large a table to allow.
If the pointer size is too small for an existing table, you can change the options with
ALTER TABLE to increase a table's maximum allowable size. See Section 13.1.2, “
ALTER TABLE Syntax”.
You have to specify
AVG_ROW_LENGTH only for tables with
TEXT columns; in this case, MySQL can't optimize the space required based only on the number of rows.
To change the default size limit for
MyISAM tables, set the
myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the
MAX_ROWS option. The value of
myisam_data_pointer_size can be from 2 to 7. A value of 4 allows tables up to 4GB; a value of 6 allows tables up to 256TB.
You can check the maximum data and index sizes by using this statement:
SHOW TABLE STATUS FROM
You also can use myisamchk -dv /path/to/table-index-file. See Section 13.5.4, “
SHOW Syntax”, or Section 8.5, “myisamchk — MyISAM Table-Maintenance Utility”.
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 8.7, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
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 14.3, “The
MERGE Storage Engine”.
You are using the
NDB storage engine, in which case you need to increase the values for the
IndexMemory configuration parameters in your
config.ini file. See Section 22.214.171.124, “Data Node Configuration Parameters”.
You are using the
HEAP) storage engine; in this case you need to increase the value of the
max_heap_table_size system variable. See Section 5.2.3, “System Variables”.