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 |
Solaris 9/10 | 16TB |
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
val
SQL_BIG_TABLES
before you issue the
problematic query. See 項12.5.3. 「SET
構文」.
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
MyISAM
table.
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.
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
項13.5.7. 「InnoDB
データとログ
ファイルの追加と削除」.
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 256GB by default, 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 AVG_ROW_LENGTH
and
MAX_ROWS
options. See
項12.1.8. 「CREATE TABLE
構文」. 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 項12.1.2. 「ALTER TABLE
構文」.
ALTER TABLEtbl_name
MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn
;
You have to specify AVG_ROW_LENGTH
only
for tables with BLOB
or
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 FROMdb_name
LIKE 'tbl_name
';
You also can use myisamchk -dv
/path/to/table-index-file. See
項12.5.4. 「SHOW
構文」, or 項7.4. 「myisamchk — MyISAM テーブル メンテナンス ユーティリティ」.
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 項7.6. 「myisampack — 圧縮された、読み取り専用MyISAM テーブルを作成する。」.
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 項13.6. 「MERGE
ストレージエンジン」.
You are using the NDB
storage engine,
in which case you need to increase the values for the
DataMemory
and
IndexMemory
configuration parameters in
your config.ini
file. See
項14.4.5.1. 「データノードの設定パラメータ」.
You are using the MEMORY
(HEAP
) storage engine; in this case you
need to increase the value of the
max_heap_table_size
system variable.
See 項4.2.3. 「システム変数」.