分类: LINUX
2009-05-07 09:01:14
今天往一个表里添加3000多万条记录时,眼看着就要完成了,突然报了个错:
The table ‘tbl_name’ is full
The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits
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
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).
mysql> show table status like 'tbl_name'\G
*************************** 1. row ***************************
Name: tbl_name
Engine: MyISAM
Version: 9
Row_format: Dynamic
...(略)...
Max_data_length: 4294967295
...(略)...
ALTER TABLE tbl_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.
In a MyISAM table with dynamic (variable length) rows, the index file for the table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only 4GB of space.