MySQL數據庫單一表突破4G限制的實現方法

問題:在論壇發表回複時出現“The table is full”的提示,字面意義上是數據表已滿的意思。因爲很少有開發者遭遇單一表超過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 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

證明確實是這樣子。隨後我們翻閱了EXT3文件系統的相關技術參數,EXT3是在EXT2基礎上演變而來。EXT2所支持最大單一文件長度是2G,這個是很蹩腳的一個限制。EXT3做的很大一個改善就是將這個限制放大到了2TB,由此稍松一口氣,起碼不是操作系統上的限制。

經過朋友的開導,了解到單一文件大小有如下幾個因素:

1. 文件系統的限制(如剛存所說EXT3的2TB限制)

2. 某一程序進程所能存取的第一文件最大尺寸(例如apache在Linux EXT3下能存取的最大尺寸爲2G,諸如日志)

初步判斷瓶頸就在上述其中第二項。隨後找到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 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

令人振奮的事情發生了,該表的 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多,非常令人振奮。

MySQL實現表中取出隨機數據
  以前在群裏討論過這個問題,比較的有意思.mysql的語法真好玩.他們原來都想用PHP的實現隨機,但取出多條好像要進行兩次以上查詢.翻了手冊,找到了下面這個語句,可以完成任務了。  SELECT * FROM table_name ORDER ...查看完整版>>MySQL實現表中取出隨機數據
 
如何實現MySQL表數據隨機讀取
以前在群裏討論過這個問題,比較的有意思.mysql的語法真好玩.他們原來都想用PHP的實現隨機,但取出多條好像要進行兩次以上查詢.翻了手冊,找到了下面這個語句,可以完成任務了。SELECT * FROM table_name ORDER BY rand(...查看完整版>>如何實現MySQL表數據隨機讀取
 
MySQL實現表中取出隨機數據
  以前在群裏討論過這個問題,比較的有意思.mysql的語法真好玩.他們原來都想用PHP的實現隨機,但取出多條好像要進行兩次以上查詢.翻了手冊,找到了下面這個語句,可以完成任務了。    SELECT * FROM table_name OR...查看完整版>>MySQL實現表中取出隨機數據
 
最簡便的MySql數據庫備份方法-MySQL
  使用MYSQL進行數據庫備份,又很正規的數據庫備份方法,同其他的數據庫服務器有相同的概念,但有沒有想過,MySQL會有更簡捷的使用文件目錄的備份方法,而且又快有好。     一、數據備份捷徑   因爲這個方法...查看完整版>>最簡便的MySql數據庫備份方法-MySQL
 
MySQL數據庫只監聽某個特定地址的方法
問:怎樣才能指定MySQL只監聽某個特定地址? 答:比較常見的辦法是,在my.cnf之mysqld節,添加bind-address=127.0.0.1。但是也有人按照下面的辦法來做。 爲了數據的安全,可以考慮讓MySQL只守候在127.0.0.1上,這樣從...查看完整版>>MySQL數據庫只監聽某個特定地址的方法
 
詳細講解MySQL數據庫雙機熱備的配置方法
MySQL數據庫雙機熱備的配置方法: ◆1.MySQL數據庫沒有增量備份的機制,當數據量太大的時候備份是一個很大的問題。還好MySQL數據庫提供了一種主從備份的機制,其實就是把主數據庫的所有的數據同時寫到備份數據庫中。實...查看完整版>>詳細講解MySQL數據庫雙機熱備的配置方法
 
講解查看MySQL數據庫錯誤碼的三個方法
查看MySQL數據庫錯誤碼(error code)的三個方法: ◆1. MySQL Reference Manual有Appendix B. Error Codes and Messages ◆2. 查看頁面:http://dev.mysql.com/doc/refman/5.0/en/error-handling.html ◆3. MySQL的...查看完整版>>講解查看MySQL數據庫錯誤碼的三個方法
 
連接MYSQL數據庫的方法及示例
連接mysql數據庫的方法及示例方法一:使用MYSQL推出的MySQL Connector/Net is an ADO.NET driver for MySQL該組件爲MYSQL爲ADO.NET訪問MYSQL數據庫設計的.NET訪問組件。安裝完成該組件後,引用命名空間MySql.Data.My...查看完整版>>連接MYSQL數據庫的方法及示例
 
用最簡便的方法備份MySql數據庫資料
     使用MYSQL進行數據庫備份,有很正規的數據庫備份方法,同其他的數據庫服務器有相同的概念,但有沒有想過,MySQL會有更簡捷的使用文件目錄的備份方法,而且又快有好。  一、數據備份捷徑  因爲這個方法沒...查看完整版>>用最簡便的方法備份MySql數據庫資料
 
 
回到王朝網路首頁