源自:51cto.com

   很少有开发者遭遇单表超过4G情况因此朋友间讨论只能提供些外围信息但随着数据流不断总价,4G容量是早晚事儿本文将以此次问题解决过程介绍问题发生原因及对策

   根据经验The table is full提示往往出现在以下两种情况:

   1. 表中设置了MAX_ROWS值简单说若MAX_ROWS设置为100而试图写入第101条记录会出现此

   2. 表满这种情况是本文讨论重点

   我们认为MySQL在存取表时候存在种定位分配规律这个规律在默认情况下可以寻址4G以内数据超过这个大小数据库将不能对数据定位因而也无法进行读写经过实验这个限制是完全可以被突破
   本例中用户系统环境为双Athlon处理器、SCSI硬盘72G、2G内存用户帖子表数据尺寸为4294963640接近4G(4G实际字节数为4294967296)

   首先SSH登录后查看用户系统信息:

   # uname -a
   Linux zichen.com 2.4.20-8smp #1 SMP Thu Mar 13 16:43:01 EST 2003 i686 athlon i386 GNU/Linux

   证明是Linux系统根据内核版本2.4.20-8smp加上国内使用常见系统估计应该是redhat 9发行包

   # cat /etc/*release*
   Red Hat Linux release 9 (Shrike)

   这也证明了我们对系统版本猜想

   然后看下用是什么文件系统该用户并非高手估计在装系统时候就是路回车下来redhat 9默认应该是EXT3不过我们还是看下:

# parted GNU Parted 1.6.3 Copyright (C) 1998, 1999, 2000, 2001, 2002 Free Software Foundation, Inc. This program is free software, covered by the GNU General Public License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. Using /dev/sda Information: The operating system thinks the geometry _disibledevent=>技术参数EXT3是在EXT2基础上演变而来EXT2所支持最大单文件长度是2G这个是很蹩脚个限制EXT3做很大个改善就是将这个限制放大到了2TB由此稍松口气起码不是操作系统上限制

   经过朋友开导了解到单文件大小有如下几个原因:

   1. 文件系统限制(如刚存所说EXT32TB限制)

   2. 某进程所能存取第文件最大尺寸(例如apache在Linux EXT3下能存取最大尺寸为2G诸如日志)初步判断瓶颈就在上述其中第 2项随后找到myisamchk来显示下表信息证明了瓶颈就在MySQL本身存取上

   # myisamchk -dv cdb_posts

   结果就不贴了其中有项Max datafile length值恰好就是4G由此产生了瓶颈
后来翻阅了N多资料进行了N多尝试也走了不少弯路最终觉得还是官方文档比较可靠比较老文档里写道这是由于tmp_table_size值造成也有提到用BIG-TABLES这个参数事实证明这些都是歧途大晚上确实很累这里只给出最终解决方案吧中间就不罗嗦了

   进到mysql客户端

   # mysql -uroot -p
   Enter password: ******
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 59411 to server version: 4.0.18-standard

   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

   mysql> use ******
   Database changed
   mysql> ALTER TABLE cdb_posts MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

   这个表非常大执行时间在双Athlon专业服务器上竟然花了30分钟！
的后再通过myisamchk查看该表信息:

# myisamchk -dv cdb_posts
MyISAM file: cdb_posts
Record format: Packed
Character : latin1 (8)
File-version: 1
Creation time: 2004-08-30 22:19:48
Recover time: 2004-08-30 22:42:47
Status: open,changed
Auto increment key: 1 Last value: 1063143
Data records: 619904 Deleted blocks: 5
Datafile parts: 619909 Deleted data: 323872
Datafile poer (s): 6 Keyfile poer (s): 4
Datafile length: 4295287332 Keyfile length: 40421376
Max datafile length: 281474976710654 Max keyfile length: 4398046510079
Recordlength: 149

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 1 4 unique unsigned long 1 4535296 1024
2 5 2 multip. unsigned 13776 12540928 1024
3 111 4 multip. unsigned long 1 18854912 1024
4 28 3 multip. u24 18 24546304 1024
5 7 3 multip. u24 7 32827392 1024
111 4 unsigned long 1
6 7 3 multip. u24 7 40418304 1024
28 3 u24







   令人振奋事情发生了该表 Max datafile length: 281474976710654 Max keyfile length: 4398046510079即最大数据尺寸(MYD文件)达到了2TB最大索引尺寸(MYI)仍然为4G

   由此默认4G限制被突破了有关其中原理其实很简单:假设你有个日记本上面有10页纸可以写东西编排目录只需要1个字节(0～9就够了)如果你把这本子又塞进两张纸变成12页1个字节目录空间就无法寻址到后面两页中进而产生了上面那个ALTER语句中数值都是我为保证成功取比较大值(ALTER次实在是太慢了没时间在那乱试验)相当于告诉数据库这个本子有1000000000页每页平均有15000个字节这样数据库便知道这是很大个本子因此不遗余力拿出了100页(假设说)做目录编排这样这个新目录就可以寻址到日记本所有内容了消失

   惟缺点就是目录占用空间多了些但已经微乎其微了做了这种改变其实4G文件尺寸大小只增大了1M多非常令人振奋