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

對新奇的事物都很有興趣, 喜歡簡單的東西, 過簡單的生活.
本篇發表於 My_Note-App-Setting。將永久鏈結加入書籤。

MySQL MyISAM 4GB限制解除 有 19 則回應

  1. 蕭小豪 說道:

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

  2. Tsung 說道:

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

  3. 蕭小豪 說道:

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

  4. Tsung 說道:

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

  5. 蕭小豪 說道:

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

  6. Tsung 說道:

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

  7. howa 說道:

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

  8. 蕭小豪 說道:

    你好我又來了 ~
    >>>>>avg_row_length: 平均每筆 rows 的大小

  9. Tsung 說道:

    @.@a... 不懂您要說什麼耶~

  10. 蕭小豪 說道:

    哎呀~對不起~
    >>>>>avg_row_length: 平均每筆 rows 的大小的單位是什麼呢?
    是kb還是MB呢?
    max_rows=210000000 <<<這個還可以調整嗎? 這已經是兩億一千萬筆了...

  11. Tsung 說道:

    雖然找不到大小單位, 但我想應該是 kb, 不太可能是 MB.
    http://dev.mysql.com/doc/refman/5.1/en/full-table.html
    至於 max_rows 是可以調整的, MySQL 3.22 之後版本的 MyISAM Engine 可以達到 65536 TB.

  12. 蕭小豪 說道:

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

  13. Tsung 說道:

    嗯嗯 我們是不會把檔案直接存進 db... XD
    而且這個就不是容量上的差異, 而是筆數上的差異了~~

  14. 蕭小豪 說道:

    我又來了 ~
    看到板大的回答 感到很榮幸 (不知道為什麼)
    問題來囉 >>>>max_rows=210000000

  15. 蕭小豪 說道:

    奇怪 好像字被吃了 說
    問題來囉 >>>>max_rows=210000000

  16. 蕭小豪 說道:

    超過這個筆數 會發生什麼事嗎?

  17. Tsung 說道:

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

  18. 蕭小豪 說道:

    喔~原來~
    真是太感謝了~

發表迴響

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