MySQL 你所不知的table is full那些事

當(dāng)我們要寫入新數(shù)據(jù)而發(fā)生“The table is full”告警錯(cuò)誤時(shí)，先不要著急，按照下面的思路來(lái)逐步分析即可：
1、查看操作系統(tǒng)以及MySQL的錯(cuò)誤日志文件
確認(rèn)操作系統(tǒng)的文件系統(tǒng)沒(méi)有報(bào)錯(cuò)，并且MySQL的錯(cuò)誤日志文件中是否有一些最直觀的可見(jiàn)的錯(cuò)誤提示。
有可能是數(shù)據(jù)庫(kù)文件超過(guò)操作系統(tǒng)層的文件大小限制，比如fat/fat32以及低版本的Linux，文件最大不可以大于2G（最大擴(kuò)展到4G），這就需要轉(zhuǎn)換fat32為NTFS，或升級(jí)Linux版本。

2、確認(rèn)磁盤空間沒(méi)有滿
執(zhí)行 df -h 查看剩余磁盤空間，如果發(fā)現(xiàn)磁盤空間確實(shí)已經(jīng)用完，則盡快刪除不需要的文件。

如果通過(guò) du 計(jì)算各個(gè)目錄的總和卻發(fā)現(xiàn)根本不會(huì)用完磁盤空間時(shí)，就需要注意了，可能是某個(gè)被刪除的文件還沒(méi)完全釋放，導(dǎo)致 df 看起來(lái)已經(jīng)用完，但 du 卻又統(tǒng)計(jì)不到。
這時(shí)候可以執(zhí)行 lsof | grep -i deleted 找到被刪除的大文件，將其對(duì)應(yīng)的進(jìn)程殺掉，釋放該文件描述符。

如果該進(jìn)程不能被殺掉，例如是 mysqld 進(jìn)程在占用的話，可以在 MySQL 里找到是哪個(gè)內(nèi)部線程在用，停止該線程即可。

曾經(jīng)發(fā)生過(guò)這樣一個(gè)例子：
用vim打開(kāi)MySQL的slow query log，退出時(shí)選擇了 “wq” 指令，也就是保存退出，結(jié)果悲劇發(fā)生了。
因?yàn)樵谄浯蜷_(kāi)的那段時(shí)間內(nèi)，slow query log有新日志產(chǎn)生，會(huì)持續(xù)寫入，但他退出時(shí)采用保存退出的方式，變成了一個(gè)“新”文件（或者說(shuō)新文件句柄 file handler），這個(gè)“新”文件無(wú)法被mysqld進(jìn)程識(shí)別，
mysqld進(jìn)程依舊將slow query log寫入到原來(lái)它打開(kāi)的那個(gè)文件（或者說(shuō)文件句柄）里，該日志文件在持續(xù)增長(zhǎng)，但手工保存退出的文件卻再也不增長(zhǎng)了，直接查看文件看不出任何異常。
這時(shí)候只能用 lsof -p pidof mysqld 才能看到該文件。
解決方法很簡(jiǎn)單，將原來(lái)的文件備份一下，執(zhí)行下面的指令：

FLUSH SLOW LOGS;

備注：MySQL 5.5開(kāi)始才支持 BINARY/ENGINE/ERROR/GENERAL/RELAY/SLOW 等關(guān)鍵字，之前的版本只能刷新全部日志。

3、確認(rèn)數(shù)據(jù)表狀態(tài)

• 如果是MyISAM引擎

默認(rèn)配置下，MyISAM引擎最大可支持256TB（myisam_data_pointer_size = 6，256^6 = 256TB），除非操作系統(tǒng)層有限制。
在MySQL5.0中，MyISAM引擎行記錄默認(rèn)是動(dòng)態(tài)長(zhǎng)度，單表最大可達(dá)256TB，MyISAM行指針（myisam_data_pointer_size）長(zhǎng)度為6字節(jié)。
在這之前，MyISAM行指針默認(rèn)長(zhǎng)度為4字節(jié)，只支持4GB的數(shù)據(jù)。改行指針最大值可設(shè)為8字節(jié)。
在行指針設(shè)置較小不夠用的時(shí)候，為提高M(jìn)yISAM表最大容量，可以修改表定義設(shè)定MAX_ROWS的值：

ALTER TABLE `xx` ENGINE=MyISAM MAX_ROWS=_nn_

備注：表定義中，AVG_ROW_LENGTH 屬性定義的是 BLOB/TEXT 字段類型的最大長(zhǎng)度。

• 如果是InnoDB引擎

ibdata共享表空間最后一個(gè)文件沒(méi)有設(shè)置成自增長(zhǎng)，或者超過(guò)32位系統(tǒng)的單文件大小限制。
解決方法：
1、ibdata的最后一個(gè)文件（非最后一個(gè)文件無(wú)法設(shè)置為自動(dòng)增長(zhǎng)）設(shè)置成自動(dòng)增長(zhǎng)；
2、檢查操作系統(tǒng)，遷移到64位操作系統(tǒng)下；
3、轉(zhuǎn)成獨(dú)立表空間；
4、刪除歷史數(shù)據(jù)，重整表空間；

• 如果是MEMORY引擎

1、適當(dāng)提高max_heap_table_size設(shè)置（注意該值是會(huì)話級(jí)別，不要設(shè)置過(guò)大，例如1GB，一般不建議超過(guò)256MB）；
2、執(zhí)行ALTER TABLE t_mem ENGINE=MEMORY; 重整表空間，否則無(wú)法寫入新數(shù)據(jù)；
3、刪除部分歷史數(shù)據(jù)或者直接清空，重整表空間；
4、設(shè)置 big_tables = 1，將所有臨時(shí)表存儲(chǔ)在磁盤，而非內(nèi)存中，缺點(diǎn)是如果某個(gè)SQL執(zhí)行時(shí)需要用到臨時(shí)表，則性能會(huì)差很多；

順便說(shuō)下，如果數(shù)據(jù)表有一列自增INT做主鍵，但是該ID值達(dá)到了INT最大值的話，MyISAM、MEMORY、InnoDB三種引擎的告警信息是不一樣的。
InnoDB引擎的告警信息類似這樣：
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

而MyISAM和MEMORY引擎則都是這樣：
ERROR 1062 (23000): Duplicate entry ‘4294967295’ for key ‘PRIMARY’

參考

MySQL手冊(cè)：B.5.2.12 The table is full