Administrator

From http://dev.mysql.com/doc/mysql/en/Table_size.html :

1.2.4 How Big MySQL Tables Can Be



MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 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 usually 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:

Operating System File-size Limit Linux-Intel 32-bit 2GB, much more when using LFS Linux-Alpha 8TB (?) Solaris 2.5.1 2GB (4GB possible with patch) Solaris 2.6 4GB (can be changed with flag) Solaris 2.7 Intel 4GB Solaris 2.7 UltraSPARC 512GB NetWare w/NSS filesystem 8TB
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 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 statement or with myisamchk -dv tbl_name. See section 14.5.3 SET and SHOW Syntax.

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 14.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 14.2.2 ALTER TABLE Syntax.

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.2 myisampack, the MySQL Compressed Read-only Table Generator.
• Another way to get around the operating system file limit for MyISAM data files is by using the RAID options. See section 14.2.5 CREATE TABLE Syntax.
• 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 15.2 The MERGE Storage Engine.

DISCUSS:

JsS

A table has 14lakhs of records.The order of retrival is not same as order of entry.
Means if we insert B5,B4,B3 then the retrival is B3,B4,B5.
When the table is having 9lakhs of records then it handles properly....
can any 1 suggest....

Azkaban

What's a lakhs?