The realization method of breaking 4G limit by single table of MySQL database

Recently, a discuz! old user of the Forum in the reply to appear "The table is full" hint, the literal meaning is that the data table is filled. Because few developers encounter a single table over 4G, the discussion between friends can only provide some peripheral information. In order to solve this problem, I have read a lot of information, this article will be my solution to the problem, introduce the causes of the problem and countermeasures.

Based on experience, the table is a full hint in the following two situations:

1. The Max_Rows value is set in the table, which simply says that if the max_rows is set to 100 and the program attempts to write to the 101th record, this error occurs.

2. Table full. This situation is the focus of the discussion in this paper.

We think that MySQL in access to the table, there is a pattern of positioning allocation. This rule, by default, can address data within 4G. Beyond this size, the database will not be able to locate the data and therefore cannot read or write. Through experiments, this limitation can be completely broken.

In this case, the user's system environment is a dual Athlon processor, SCSI hard drive 72G, 2G memory, user's post table data size is 4294963640, nearly 4G (the actual byte number of 4G is 4294967296).

First, after SSH login, view the user's system information:

# uname-a

Linux zichen.com 2.4.20-8smp #1 SMP Thu 16:43:01 EST 2003 i686 Athlon i386

The proof is Linux system, according to kernel version 2.4.20-8smp, plus the common system that uses in the country, estimate should be Redhat 9 release package.

# cat/etc/*release*

Red Hat Linux Release 9 (shrike)

This also proves our conjecture about the system version.

Then look at what file system is used. Because the user is not a master, it is estimated that in the installation system is all the way to return to the Redhat 9 the default should be EXT3, but we still look at:

# parted

GNU Parted 1.6.3

Copyright (C) 1998, 1999, Watts, 2001, 2002 Free Software Foundation, Inc.

This are free software and covered by the GNU general public License.

This are distributed in the hope that it'll be useful and but without any WARRANTY; Without even the implied warranty of

merchantability or FITNESS for A particular purpose. The GNU general public License is for more details.

Using/dev/sda

Information:the operating system thinks the geometry On/dev/sda is 8942/255/63. Therefore, cylinder 1024 ends at 8032.499M.

(parted) Print

Disk Geometry for/dev/sda:0.000-70149.507 Megabytes

Disk label Type:msdos

Minor Start End Type filesystem Flags

1 0.031 101.975 primary ext3 Boot

2 101.975 10103.378 Primary Linux-swap

proved to be the way it really is. Then we read the EXT3 file system related technical parameters, EXT3 is based on the EXT2 evolved. The maximum single file length supported by EXT2 is 2G, which is a very lame limitation. A big improvement for EXT3 is that the limit is magnified to 2TB, which is a little relieved, at least not on operating system limits.