数据表空间满解决方法

damofeixue

今天往一个表里添加3000多万条记录时，眼看着就要完成了，突然报了个错：
The table ‘tbl_name’ is full
第一反应是记录数有限制，查了下：32bit的机子，limit on number of rows 是4个billion。
显然没达到这个数，su到root查了下该表所占的空间：4GB
这么整的一个数，应该就是它了。
继续查，在mysql5.0的manual页里：
The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits
这下糊涂了，mysql数据目录所在的分区(ext3)的文件大小显然不可能限制在4GB，而且manual里面也提到：
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
算了，继续往下看，提到了出现full-table错误的各种可能原因，有这么一段：
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).
原来如此，create table是默认是4G的，用show table status看下我这个表：
mysql> show table status like 'tbl_name'\G
*************************** 1. row ***************************
           Name: tbl_name
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
...(略)...
Max_data_length: 4294967295
...(略)...
就是那个Max_data_length搞的鬼。
接下来
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
就可以了
关于那个AVG_ROW_LENGTH，manual里面这么写的：
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.
我这里没有 BLOB 和 TEXT 类型的列，就不写它了。
再show table status下，发现 max_data_length 已经变成1099511627775，够用了
最后，至于这个 4GB 的 limit 怎么来的，
Overcoming MySQL's 4GB Limit
这篇里有讲到：
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.
以后遇到创建可能会比较大的表时，可以直接把 MAX_ROWS 和 AVG_ROW_LENGTH 写进 create option
参考文献：
MySQL 5.0 Reference Manual
Overcoming MySQL's 4GB Limit


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/21651/showart_1920836.html