Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how the MySQL database single table breaks through the 4G limit. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Question: a "The table is full" prompt appears when posting a reply, which literally means that the data table is full. Because few developers encounter situations where a single table is more than 4G, discussions among friends can only provide some peripheral information. In order to solve this problem, I have read a lot of materials. This article will introduce the causes and countermeasures of the problem with the process of solving this problem.

As a rule of thumb, The table is full prompts often occur in the following two situations:

1. The MAX_ Rows value is set in the table. To put it simply, this error occurs if the MAX_ROWS is set to 100 and the program attempts to write to the 101 record.

two。 The watch is full. This situation is the focus of this article.

We think that when MySQL accesses the table, there is a law of location and allocation. By default, this rule 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.

The user's system environment is dual Athlon processors, SCSI hard disk 72G, 2G memory, and the user's post table data size is 4294963640, which is close to 4G (the actual number of 4G bytes is 4294967296).

First, after logging in with SSH, view the user's system information:

# uname-a

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

It is proved to be a Linux system. According to the kernel version 2.4.20-8smp, coupled with the common systems used in China, it is estimated that it should be a redhat 9 distribution.

# cat / etc/*release*

Red Hat Linux release 9 (Shrike)

This also proves our conjecture about the system version.

Then take a look at what file system is being used. Because the user is not an expert, it is estimated that he will enter the car all the way down when installing the system. The default value of redhat 9 should be EXT3, but let's take a look:

# 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 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: ms

Minor Start End Type Filesystem Flags

1 0.031 101.975 primary ext3 boot

2 101.975 10103.378 primary linux-swap

To prove that this is the case. Then we flipped through the relevant technical parameters of the EXT3 file system. EXT3 evolved on the basis of EXT2. The maximum single file length supported by EXT2 is 2G, which is a poor limit. One of the big improvements made by EXT3 is to enlarge this limit to 2TB, which breathes a sigh of relief, at least not on the operating system.

Through the guidance of my friends, I learned that the size of a single file has the following factors:

1. File system restrictions (such as 2TB restrictions for EXT3 just saved)

two。 The maximum size of the first file that can be accessed by a program process (for example, the maximum size that apache can access under Linux EXT3 is 2G, such as logs)

The bottleneck of preliminary judgment lies in the second of the above. Then find myisamchk to display the table information, which proves that the bottleneck lies in the access of MySQL itself.

# myisamchk-dv cdb_posts

As a result, it will not be posted, and one of the values of Max datafile length happens to be 4G. This creates a bottleneck.

Later, I flipped through more than N materials, made many attempts, and took a lot of detours, and finally felt that the official documents were more reliable. Older documents say that this is due to the value of tmp_table_size, and that the parameter BIG-TABLES is also mentioned. Facts have proved that these are all misguided. The big night is really tired, here is only the final solution, the middle will not be wordy.

Go to the mysql client.

# 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

Because the table is so large, it took 30 minutes to execute on a professional server with dual Athlon!

Then view the information of the table through myisamchk:

# myisamchk-dv cdb_posts

MyISAM file: cdb_posts

Record format: Packed

Character set: 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 pointer (bytes): 6 Keyfile pointer (bytes): 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 short 13776 12540928 1024

3 111 4 multip. Unsigned long 1 18854912 1024

4 28 3 multip. Uint24 18 24546304 1024

5 7 3 multip. Uint24 7 32827392 1024

111 4 unsigned long 1

6 7 3 multip. Uint24 7 40418304 1024

28 3 uint24

An exciting thing happened: the Max datafile length of the table: 281474976710654 Max keyfile length: 4398046510079, that is, the maximum big data size (MYD file) reached 2TB, and the maximum index size (MYI) was still 4G.

As a result, the default 4G limit is broken. About how it works, it's simple: suppose you have a diary with 10 pages to write about, and cataloging takes only 1 byte (because 0nine is enough). If you cram the book into two more sheets of paper and turn it into 12 pages, 1 byte of directory space cannot be addressed to the next two pages, resulting in an error. The values in the above ALTER statement are the larger values I took to ensure success (because ALTER is too slow to experiment there at once), which is equivalent to telling me that this book has 1000000000 pages, with an average of 15000 bytes per page. In this way, the database knows that this is a big book, so it spares no effort to set out 100 pages (hypothetically) for cataloging so that the new directory can address all the contents of the diary. The error disappears.

The only disadvantage is that the directory takes up a little more space, but it is already very small, and with this change, the file size of 4G has only increased by more than 1m, which is very exciting.

The above is the realization method of breaking the 4G limit of a single table in MySQL database shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.