MySQL MyISAM 4GB限制解除

MySQL MyISAM 有 4GB 的限制, 如果使用量超出 4GB, 有何方法能解決.

因: MySQL 用 32bits pointer 來記錄資料位置, 所以只能定址到4GB的空間, 再多就會出現此錯誤.

解法: 將原本檔案大小限制 轉換成 rows 數目限制即可. 但是特別注意, 要做此轉換前, 一定要做備份, 並對 table 做 OPTIMIZE TABLE... 千萬千萬要做 OPTIMIZE TABLE.

實作如下:

  1. mysqldump -u root DB_NAME > DB_NAME.sql 備份
  2. mysql> optimize table TABLE_NAME; (註)
  3. mysql> alter table TABLE_NAME max_rows=210000000 avg_row_length = 1024;

若是要建立或還原時就設定要轉換, 可於 CREATE TABLE 語法中, 最後指定如下:

  • TYPE=MyISAM max_rows=4294967295 avg_row_length=50 ;

上述語法中 的 max_rows 和 avg_row_length 代表:

  • max_rows: 總筆數最大多少筆
  • avg_row_length: 平均每筆 rows 的大小

註: 若沒有下 OPTIMIZE TABLE 而直接做 ALTER TABLE 可能導致資料流失的慘劇, 所以務必要做, 不過 OPTIMIZE TABLE 依照 MyISAM 的做法是把資料整個撈出來, 再寫到另一個新的 data file 去, 所以如果資料量大, 需要一點時間.

做過 OPTIMIZE 後, 速度會有明顯的變快,
因為平常資料難免會有 UPDATE 或 DELETE 動作, 造成資料的不連續性, 此 OPTIMIZE 也就是將此問題解決, 也可以想成就是做磁碟重組吧.

MySQL Optimize 詳可參考 MySQL OPTIMIZE TABLE Syntax

作者: Tsung

對新奇的事物都很有興趣, 喜歡簡單的東西, 過簡單的生活.

在〈MySQL MyISAM 4GB限制解除〉中有 19 則留言

  1. HI~你好
    我常常搜尋到您的文章 特別是這一篇感到有興趣
    想請問說 我的database中 是否可以同時 存在轉換過的table 與未轉換的 ?若是可以 當我要建立關聯性時 會不會有所影響?
    另外 我有一個很納悶的問題 不知道大大是否 可以為我解答?
    小弟有使用phpmyadmin 做為mysql 基礎的管理 但是問題來了
    ------------------------------------------
    |二進制碼 - 不能編輯 (0 Bytes) |
    |[longblob] (最大容量: 102,400KB) |
    ------------------------------------------
    雖然我調整了longblob 看起來可放入102,400KB 但怎麼放都沒辦法超過1M
    小弟也寫了一個PHP做為用$_FILE丟進去 但問題還是一樣 都沒辦法超過1M
    不知道是否可以麻煩大大解答?

    回覆

  2. 1. 可以, 因為這只是設定 table 儲存的方式.
    2. 不會有影響 🙂
    3. 把 file 丟進 DB 這個, 我已經好幾年沒寫過了耶, 我會建議 file 不要丟 DB, 而是在 DB 裡面存 FILE 的 path 就好了, file 本身還是存在 file system 裡面, 這樣子 DB 的 loading 也不會太重, 速度也會比較快唷 🙂

    回覆

  3. 你好
    非常感謝你您的回答 小弟覺得很有幫助
    續問~
    在檔案管理上 檔案放在table 中會不會比較 容易造成 mysql 系統不穩定呢 ?

    回覆

  4. 我覺得不穩倒是比較不至於, 不過因為檔案越來越大, 讀取的時後自然速度會比較慢, 有可能因為這樣子造成 mysql 讀取速度慢後, 就卡很多 query, 造成的不穩是有可能的~ 🙂

    回覆

  5. 大大你好~好久沒來了這裡了
    這陣子我對mysql 又了解到不少東西
    最近小弟用mysql 做影音資料庫 把檔案都放入mysql中 最大還可以放到100M(當然還可以更大)
    但是我是用PHPMYADMIN 不知道大大是否有使用過這個東西?
    我的問題是 當檔案越來越大的時候 使用PHPMYADMIN就越來越難管理若要使用"瀏覽"這個功能變的要看運氣好不好
    才能進的去了 沽計檔案大約已經1G了
    後來我發現 PHPMYADMIN並不是很聰明 因為使用"瀏覽"功能 就是 SELECT from TABLE
    而我的檔案在裡面又肥又大　每次刪除檔案就變惡夢了
    不知道大大有沒有什麼好推薦的辦法？

    回覆

  6. 事實上, 通常建議的是, mysql 裡面存的只是檔名 或 檔名+路徑, 不要把檔案塞進 DB, 不然量大一點, DB 很容易就掛了.
    我會建議您採用這種方式耶.

    回覆

  7. 註: 若沒有下 OPTIMIZE TABLE 而直接做 ALTER TABLE 可能導致資料流失的慘劇, 所以務必要做,
    請問你是如何得知的呢？文檔沒有寫呀‥

    回覆

  11. 哎呀~對不起~
    >>>>>avg_row_length: 平均每筆 rows 的大小的單位是什麼呢？
    是ｋｂ還是ＭＢ呢？
    max_rows=210000000　＜＜＜這個還可以調整嗎？　這已經是兩億一千萬筆了．．．

    回覆

  13. 謝謝板大的回答~不過我在實驗中利用PHP上傳了 好幾個100MB的檔案進去 是可以存在MySQL中的
    只是在資料查詢時~就很難看了~當當當當!
    所以小弟才會有KB 與MB的疑問
    因為如果是MB的話 我自己也不太敢相信 這是真的
    雖然事實擺在眼前
    但我還是想要請問專家的意建~!!

    回覆

  18. 厄... 吃字可能是你有用到 左右括號(可能被認定有html語法, 而被濾掉).
    超過這個筆數, 就再把他設大點吧~ 不過通常超過這個筆數, 應該加機器了~ XD

    回覆

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。