GNU Info

(mysql.info)Table size

---

Next: Year 2000 compliance Prev: Stability Up: MySQL and MySQL AB

---

Enter node , (file) or (file)node

How Big Can MySQL Tables Be?
----------------------------

MySQL Version 3.22 has a 4G limit on table size. With the new `MyISAM'
in MySQL Version 3.23 the maximum table size is pushed up to 8 million
terabytes (2 ^ 63 bytes).

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

*Operating System*                   *File Size Limit*
Linux-Intel 32 bit                   2G, 4G or more, depends on Linux
                                     version
Linux-Alpha                          8T (?)
Solaris 2.5.1                        2G (possible 4G with patch)
Solaris 2.6                          4G
Solaris 2.7 Intel                    4G
Solaris 2.7 ULTRA-SPARC              8T (?)

On Linux 2.2 you can get bigger tables than 2G by using the LFS patch
for the ext2 file system.  On Linux 2.4 there exists also patches for
ReiserFS to get support for big files.

This means that the table size for MySQL is normally limited by the
operating system.

By default, MySQL tables have a maximum size of about 4G.  You can
check the maximum table size for a table with the `SHOW TABLE STATUS'
command or with the `myisamchk -dv table_name'.  Note: SHOW.

If you need bigger tables than 4G (and your operating system supports
this), you should set the `AVG_ROW_LENGTH' and `MAX_ROWS' parameter
when you create your table.  Note: CREATE TABLE.  You can also set
these later with `ALTER TABLE'. Note: ALTER TABLE.

If your big table is going to be read-only, you could use `myisampack'
to merge and compress many tables to one.  `myisampack' usually
compresses a table by at least 50%, so you can have, in effect, much
bigger tables.  Note: `myisampack'.

You can go around the operating system file limit for `MyISAM' data
files by using the `RAID' option. Note: CREATE TABLE.

Another solution can be the included MERGE library, which allows you to
handle a collection of identical tables as one. *Note MERGE tables:
MERGE.