MySQL Single table size

 

In earlier versions of MySQL 3.22, the single table size of MySQL is 4 GB. At that time, the storage engine of MySQL was also the ISAM storage engine. However, when the MyISAM storage engine appears, that is, starting from MySQL 3.23, the maximum limit for a single MySQL table has been extended to 64 Pb (as shown in the official document ). That is to say, from the current technical environment, the single table size limit of the MyISAM storage engine of the MySQL database is no longer determined by the MySQL database itself, it is determined by the file system on the OS of the host.

 

One of the most popular storage engines in MySQL, Innodb, has two data storage policies: Shared tablespace storage and exclusive tablespace storage.

When using the shared tablespace storage method, all data in Innodb is stored in a single tablespace, which can be composed of multiple files. A table can exist across multiple files, its size limit is not a limitation of the file size, but its own limitation. From the official Innodb documentation, we can see that the maximum tablespace limit is 64 TB. That is to say, the single table limit of Innodb is about 64 TB, of course, this size includes all indexes of the table and other related data.

When an exclusive tablespace is used to store Innodb tables, the data in each table is stored in a separate file. In this case, the single table is limited, the size of the file system is limited.

 

The following information is not necessarily accurate:

Operating system size limit

Win32 w/FAT/FAT32 2 GB/4 GB

Win32 w/NTFS 2 TB (larger)

Linux 2.2-Intel 32-bit 2 GB (LFS: 4 GB)

Linux 2.4 + 4 TB (ext3)

Solaris 9/10 16 TB

NetWare w/NSS filesystem 8 TB

MacOS X w/HFS + 2 TB

The following is the content in the MySQL document:

For Windows users, note that FAT and VFAT (FAT32) are not suitable for MySQL production. NTFS should be used.

 

By default, the maximum size of MyISAM Table created by MySQL is 4 GB. You can use the show table status statement or myisamchk-dv tbl_name to check the maximum size of the TABLE. See section 13.5.4 "SHOW Syntax ".

 

To use a MyISAM TABLE larger than 4 GB (and your operating system supports large files), you can use the create table statement that allows the AVG_ROW_LENGTH and MAX_ROWS options. After creating a TABLE, you can also use alter table to change these options to increase the maximum allowed TABLE capacity.