Mysql单一表突破4G限制的实现方法
时间：2007-05-25 来源：szxsztszk
适用版本： 所有版本 \A/I7Zp^B|
所属类别： 数据库相关 6x '^i
关键字： MyISAM表 尺寸 限制 文件系统 fdEe)D!
近日，一位Discuz!老用户的论坛在发表回复时出现“The table is full”的提示，字面意义上是数据表已满的意思。因为很少有开发者遭遇单一表超过4G的情况，因此朋友间的讨论只能提供一些外围的信息。为解决此问题，我翻阅了很多资料，本文将以我此次问题的解决过程，介绍问题发生的原因及对策。 &,9/&h0
根据经验，The table is full提示往往出现在以下两种情况： [!|1S0F
1. 表中设置了MAX_ROWS值，简单的说，若MAX_ROWS设置为100，而程序试图写入第101条记录，会出现此错误。 PU8`!.`@
2. 表满。这种情况是本文讨论的重点。 +Gu2D7H):
F?^8N K
我们认为MySQL在存取表的时候，存在一种定位分配规律。这个规律在默认的情况下，可以寻址4G以内的数据。超过这个大小，数据库将不能对数据定位，因而也无法进行读写。经过实验，这个限制是完全可以被突破的。 WWLZS:_
本例中，用户的系统环境为双Athlon处理器、SCSI硬盘72G、2G内存，用户的帖子表数据尺寸为4294963640，接近4G(4G的实际字节数为4294967296)。 ] s4{LDT
JG6' -w
首先SSH登录后，查看用户的系统信息： ;k=dL0dc
z,V1KWZ
# uname -a x!Map3}?
Linux zichen.com 2.4.20-8smp #1 SMP Thu Mar 13 16:43:01 EST 2003 i686 athlon i386 GNU/Linux GV| Qv_G
1F44|+5i
证明是Linux系统，根据内核版本2.4.20-8smp，加上国内使用的常见系统，估计应该是redhat 9发行包。 |x(Weeb
[ynUk,99Y
# cat /etc/*release* aqb.\((%
Red Hat Linux release 9 (Shrike) "*1F} rT-
~+IvT^7/N
这也证明了我们对系统版本的猜想。 f7}my4V-
ca;_P+P6@9
然后看一下用的是什么文件系统。因为该用户并非高手，估计在装系统的时候就是一路回车下来，redhat 9默认的应该是EXT3，不过我们还是看一下： tf- {m71
nN 3nd
# parted cUZNFGgc!
GNU Parted 1.6.3 > U>c>I%
Copyright (C) 1998, 1999, 2000, 2001, 2002 Free Software Foundation, Inc. 3&og5]X
This program is free software, covered by the GNU General Public License. R 7.~f
C{-b|Q1
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of eq}>ADUn
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. ! Qho|6
t,]\%HX
Using /dev/sda fVP)o 2:
Information: The operating system thinks the geometry on /dev/sda is 8942/255/63. Therefore, cylinder 1024 ends at 8032.499M. vX/U} #=
(parted) print 6cX%ESx9
Disk geometry for /dev/sda: 0.000-70149.507 megabytes \GU~%*7KS:
Disk label type: msdos ~WQv!O; R
Minor Start End Type Filesystem Flags r(Bpww(B;
1 0.031 101.975 primary ext3 boot 1P_U\k7a
2 101.975 10103.378 primary linux-swap ]p.Mv7o2
Ev"HR^^Z
证明确实是这样子。随后我们翻阅了EXT3文件系统的相关技术参数，EXT3是在EXT2基础上演变而来。EXT2所支持最大单一文件长度是2G，这个是很蹩脚的一个限制。EXT3做的很大一个改善就是将这个限制放大到了2TB，由此稍松一口气，起码不是操作系统上的限制。 Q n %5G
*n!C:4w
经过朋友的开导，了解到单一文件大小有如下几个因素： 8^"6~ 0V
1. 文件系统的限制(如刚存所说EXT3的2TB限制) GH*4zk{
2. 某一程序进程所能存取的第一文件最大尺寸(例如apache在Linux EXT3下能存取的最大尺寸为2G，诸如日志) 8B {!n
"o J}?@
初步判断瓶颈就在上述其中第二项。随后找到myisamchk来显示一下表信息，证明了瓶颈就在MySQL本身的存取上。 Zi<Uy6Bw
VkQGp)P
# myisamchk -dv cdb_posts gQR"-Gm%p
'Jl^I-u}
结果就不贴了，其中有一项Max datafile length的值恰好就是4G。由此产生了瓶颈。 $z68vo3.E6
后来翻阅了N多资料，进行了N多尝试，也走了不少弯路，最终觉得还是官方文档比较可靠。比较老的文档里写道这是由于tmp_table_size的值造成的，也有提到用BIG-TABLES这个参数。事实证明这些都是歧途。大晚上的确实很累，这里只给出最终的解决方案吧，中间的就不罗嗦了。 8mFD
E][TVLNYu
进到mysql客户端。 k a4te<-n
# mysql -uroot -p fhX$uaS
Enter password: ****** f2bHN$85
Welcome to the MySQL monitor. Commands end with ; or \g. drlX!#JO:
Your MySQL connection id is 59411 to server version: 4.0.18-standard 2II~T6v
y?uJRIuB
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *;X;>u/V0F
iT=)?*:itP
mysql> use ****** 66[(@6M:
Database changed swmWBl
mysql> ALTER TABLE cdb_posts MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000; h-4Pnz]hG?
RpO>"
因为这个表非常大，执行时间在双Athlon的专业服务器上竟然花了30分钟！ ]8J:H,Yh
之后再通过myisamchk查看该表的信息： bM7=,in<hM
# myisamchk -dv cdb_posts ie;2#p#1
MyISAM file: cdb_posts Af .C]V"j
Record format: Packed kHd> L^2
Character set: latin1 (8) P}c~Gwy
File-version: 1 `V!!o]]
Creation time: 2004-08-30 22:19:48 qw5JdD-
Recover time: 2004-08-30 22:42:47 47b3yx9
Status: open,changed j>!QDG@_4
Auto increment key: 1 Last value: 1063143 z TW0|mJ
Data records: 619904 Deleted blocks: 5 xA~`xb-1I
Datafile parts: 619909 Deleted data: 323872 u bU\XQ;
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 4 $/lI$q Z
Datafile length: 4295287332 Keyfile length: 40421376 s V^Z5
Max datafile length: 281474976710654 Max keyfile length: 4398046510079 }vY qx
Recordlength: 149 Iu'Jzd1
iic 6CQc-
table description: oud4@"jz
Key Start Len Index Type Rec/key Root Blocksize 'gUUqh=
1 1 4 unique unsigned long 1 4535296 1024 e^&@x]z#a
2 5 2 multip. unsigned short 13776 12540928 1024 3f$Bz~X~
3 111 4 multip. unsigned long 1 18854912 1024 3rkL
4 28 3 multip. uint24 18 24546304 1024 ui;]pH
5 7 3 multip. uint24 7 32827392 1024 _hu#}Q4
111 4 unsigned long 1 7 kk P(
6 7 3 multip. uint24 7 40418304 1024 _ Hlxt
28 3 uint24 dR*E%xr)?
R6wXni*
令人振奋的事情发生了，该表的 Max datafile length: 281474976710654 Max keyfile length: 4398046510079，即最大数据尺寸(MYD文件)达到了2TB，最大索引尺寸(MYI)仍然为4G。 uQg6-GJ
由此默认的4G限制被突破了。关于其中的原理，其实很简单：假设你有一个日记本，上面有10页纸可以写东西，编排目录只需要1个字节(因为0～9就够了)。如果你把这本子又塞进两张纸，变成12页，1个字节的目录空间就无法寻址到后面的两页中，进而产生了错误。上面那个ALTER语句中的数值都是我为保证成功，取的比较大的值(因为ALTER一次实在是太慢了，没时间在那乱试验)，相当于告诉数据库，这个本子有1000000000页，每页平均有15000个字节。这样数据库便知道这是很大的一个本子，因此不遗余力的拿出了100页(假设说)做目录编排，这样这个新的目录就可以寻址到日记本的所有内容了。错误消失。 {q6G=qy6Q
QVsm=*N
惟一的缺点就是，目录占用的空间多了一些，但已经微乎其微了，做了这种改变其实4G的文件尺寸大小只增大了1M多，非常令人振奋。 1!W`~%x'
所属类别： 数据库相关 6x '^i
关键字： MyISAM表 尺寸 限制 文件系统 fdEe)D!
近日，一位Discuz!老用户的论坛在发表回复时出现“The table is full”的提示，字面意义上是数据表已满的意思。因为很少有开发者遭遇单一表超过4G的情况，因此朋友间的讨论只能提供一些外围的信息。为解决此问题，我翻阅了很多资料，本文将以我此次问题的解决过程，介绍问题发生的原因及对策。 &,9/&h0
根据经验，The table is full提示往往出现在以下两种情况： [!|1S0F
1. 表中设置了MAX_ROWS值，简单的说，若MAX_ROWS设置为100，而程序试图写入第101条记录，会出现此错误。 PU8`!.`@
2. 表满。这种情况是本文讨论的重点。 +Gu2D7H):
F?^8N K
我们认为MySQL在存取表的时候，存在一种定位分配规律。这个规律在默认的情况下，可以寻址4G以内的数据。超过这个大小，数据库将不能对数据定位，因而也无法进行读写。经过实验，这个限制是完全可以被突破的。 WWLZS:_
本例中，用户的系统环境为双Athlon处理器、SCSI硬盘72G、2G内存，用户的帖子表数据尺寸为4294963640，接近4G(4G的实际字节数为4294967296)。 ] s4{LDT
JG6' -w
首先SSH登录后，查看用户的系统信息： ;k=dL0dc
z,V1KWZ
# uname -a x!Map3}?
Linux zichen.com 2.4.20-8smp #1 SMP Thu Mar 13 16:43:01 EST 2003 i686 athlon i386 GNU/Linux GV| Qv_G
1F44|+5i
证明是Linux系统，根据内核版本2.4.20-8smp，加上国内使用的常见系统，估计应该是redhat 9发行包。 |x(Weeb
[ynUk,99Y
# cat /etc/*release* aqb.\((%
Red Hat Linux release 9 (Shrike) "*1F} rT-
~+IvT^7/N
这也证明了我们对系统版本的猜想。 f7}my4V-
ca;_P+P6@9
然后看一下用的是什么文件系统。因为该用户并非高手，估计在装系统的时候就是一路回车下来，redhat 9默认的应该是EXT3，不过我们还是看一下： tf- {m71
nN 3nd
# parted cUZNFGgc!
GNU Parted 1.6.3 > U>c>I%
Copyright (C) 1998, 1999, 2000, 2001, 2002 Free Software Foundation, Inc. 3&og5]X
This program is free software, covered by the GNU General Public License. R 7.~f
C{-b|Q1
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of eq}>ADUn
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. ! Qho|6
t,]\%HX
Using /dev/sda fVP)o 2:
Information: The operating system thinks the geometry on /dev/sda is 8942/255/63. Therefore, cylinder 1024 ends at 8032.499M. vX/U} #=
(parted) print 6cX%ESx9
Disk geometry for /dev/sda: 0.000-70149.507 megabytes \GU~%*7KS:
Disk label type: msdos ~WQv!O; R
Minor Start End Type Filesystem Flags r(Bpww(B;
1 0.031 101.975 primary ext3 boot 1P_U\k7a
2 101.975 10103.378 primary linux-swap ]p.Mv7o2
Ev"HR^^Z
证明确实是这样子。随后我们翻阅了EXT3文件系统的相关技术参数，EXT3是在EXT2基础上演变而来。EXT2所支持最大单一文件长度是2G，这个是很蹩脚的一个限制。EXT3做的很大一个改善就是将这个限制放大到了2TB，由此稍松一口气，起码不是操作系统上的限制。 Q n %5G
*n!C:4w
经过朋友的开导，了解到单一文件大小有如下几个因素： 8^"6~ 0V
1. 文件系统的限制(如刚存所说EXT3的2TB限制) GH*4zk{
2. 某一程序进程所能存取的第一文件最大尺寸(例如apache在Linux EXT3下能存取的最大尺寸为2G，诸如日志) 8B {!n
"o J}?@
初步判断瓶颈就在上述其中第二项。随后找到myisamchk来显示一下表信息，证明了瓶颈就在MySQL本身的存取上。 Zi<Uy6Bw
VkQGp)P
# myisamchk -dv cdb_posts gQR"-Gm%p
'Jl^I-u}
结果就不贴了，其中有一项Max datafile length的值恰好就是4G。由此产生了瓶颈。 $z68vo3.E6
后来翻阅了N多资料，进行了N多尝试，也走了不少弯路，最终觉得还是官方文档比较可靠。比较老的文档里写道这是由于tmp_table_size的值造成的，也有提到用BIG-TABLES这个参数。事实证明这些都是歧途。大晚上的确实很累，这里只给出最终的解决方案吧，中间的就不罗嗦了。 8mFD
E][TVLNYu
进到mysql客户端。 k a4te<-n
# mysql -uroot -p fhX$uaS
Enter password: ****** f2bHN$85
Welcome to the MySQL monitor. Commands end with ; or \g. drlX!#JO:
Your MySQL connection id is 59411 to server version: 4.0.18-standard 2II~T6v
y?uJRIuB
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *;X;>u/V0F
iT=)?*:itP
mysql> use ****** 66[(@6M:
Database changed swmWBl
mysql> ALTER TABLE cdb_posts MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000; h-4Pnz]hG?
RpO>"
因为这个表非常大，执行时间在双Athlon的专业服务器上竟然花了30分钟！ ]8J:H,Yh
之后再通过myisamchk查看该表的信息： bM7=,in<hM
# myisamchk -dv cdb_posts ie;2#p#1
MyISAM file: cdb_posts Af .C]V"j
Record format: Packed kHd> L^2
Character set: latin1 (8) P}c~Gwy
File-version: 1 `V!!o]]
Creation time: 2004-08-30 22:19:48 qw5JdD-
Recover time: 2004-08-30 22:42:47 47b3yx9
Status: open,changed j>!QDG@_4
Auto increment key: 1 Last value: 1063143 z TW0|mJ
Data records: 619904 Deleted blocks: 5 xA~`xb-1I
Datafile parts: 619909 Deleted data: 323872 u bU\XQ;
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 4 $/lI$q Z
Datafile length: 4295287332 Keyfile length: 40421376 s V^Z5
Max datafile length: 281474976710654 Max keyfile length: 4398046510079 }vY qx
Recordlength: 149 Iu'Jzd1
iic 6CQc-
table description: oud4@"jz
Key Start Len Index Type Rec/key Root Blocksize 'gUUqh=
1 1 4 unique unsigned long 1 4535296 1024 e^&@x]z#a
2 5 2 multip. unsigned short 13776 12540928 1024 3f$Bz~X~
3 111 4 multip. unsigned long 1 18854912 1024 3rkL
4 28 3 multip. uint24 18 24546304 1024 ui;]pH
5 7 3 multip. uint24 7 32827392 1024 _hu#}Q4
111 4 unsigned long 1 7 kk P(
6 7 3 multip. uint24 7 40418304 1024 _ Hlxt
28 3 uint24 dR*E%xr)?
R6wXni*
令人振奋的事情发生了，该表的 Max datafile length: 281474976710654 Max keyfile length: 4398046510079，即最大数据尺寸(MYD文件)达到了2TB，最大索引尺寸(MYI)仍然为4G。 uQg6-GJ
由此默认的4G限制被突破了。关于其中的原理，其实很简单：假设你有一个日记本，上面有10页纸可以写东西，编排目录只需要1个字节(因为0～9就够了)。如果你把这本子又塞进两张纸，变成12页，1个字节的目录空间就无法寻址到后面的两页中，进而产生了错误。上面那个ALTER语句中的数值都是我为保证成功，取的比较大的值(因为ALTER一次实在是太慢了，没时间在那乱试验)，相当于告诉数据库，这个本子有1000000000页，每页平均有15000个字节。这样数据库便知道这是很大的一个本子，因此不遗余力的拿出了100页(假设说)做目录编排，这样这个新的目录就可以寻址到日记本的所有内容了。错误消失。 {q6G=qy6Q
QVsm=*N
惟一的缺点就是，目录占用的空间多了一些，但已经微乎其微了，做了这种改变其实4G的文件尺寸大小只增大了1M多，非常令人振奋。 1!W`~%x'
相关阅读 更多 +
