Implementation of a single MySQL database table exceeding 4G restrictions (1)

Guidance:The prompt "The table is full" appears when you reply to The Forum. Literally, The data table is full.Data Table memoryIt is insufficient because few developers encounter a single table that exceeds 4G. The following describes how to implement it.MySQL databaseA single table breaks through 4G restrictions.

Based on experience, The table is full prompts often occur in The following two situations:

1. The MAX_ROWS value is set in the table. In short, this error occurs if MAX_ROWS is set to 100 and the program tries to write 101st records.

2. The table is full. This is the focus of this article.

We believe that MySQL has a rule of location allocation when accessing tables. By default, this rule can address data within 4 GB. If the size exceeds this value, the database cannot locate the data and therefore cannot read or write the data. After experiments, this restriction can be completely broken.

In this example, the user's system environment is dual Athlon processor, SCSI hard drive 72 GB, 2 GB memory, the user's post table data size is 4294963640, close to 4 GB (the actual number of bytes for 4 GB is 4294967296 ).

First, log on to the SSH client and view the user's system information:

Reference content is as follows:
# Uname-

Linux zichen.com 2.4.20-8smp #1 SMP Thu Mar 13 16:43:01 EST 2003 i686 athlon i386 GNU/Linux
It turns out to be a Linux system. Based on the kernel version 2.4.20-8smp and the common system used in China, it is estimated that it should be the redhat 9 release package.

# Cat/etc/* release *

Red Hat Linux release 9 (Shrike)

This proves our conjecture about the system version.

Then let's take a look at what file system is used. Because this user is not a master, it is estimated that the system will be loaded with a carriage return. The default redhat 9 should be EXT3, but let's take a look:

Reference content is as follows:
# Parted

GNU Parted 1.6.3

Copyright (C) 1998,199 9, 2000,200 1, 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

MERCHANTABILITY or fitness for a particle PURPOSE. See the GNU General Public License 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.499 M.

(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
 

The proof is true. Then we looked at the related technical parameters of the EXT3 file system, and EXT3 evolved on the basis of EXT2. The maximum length of a single file supported by EXT2 is 2 GB, which is a very bad limit. One major improvement of EXT3 is to enlarge the limit to 2 TB, which makes it a little loose, at least not the limitation on the operating system.