mysql表ful,你所不知的tableisfull那些事

当我们要写入新数据而发生“The table is full”告警错误时&＃xff0c;先不要着急&＃xff0c;按照下面的思路来逐步分析即可&＃xff1a;

1、查看操作系统以及MySQL的错误日志文件

确认操作系统的文件系统没有报错&＃xff0c;并且MySQL的错误日志文件中是否有一些最直观的可见的错误提示。

有可能是数据库文件超过操作系统层的文件大小限制&＃xff0c;比如fat/fat32以及低版本的Linux&＃xff0c;文件最大不可以大于2G(最大扩展到4G)&＃xff0c;这就需要转换fat32为NTFS&＃xff0c;或升级Linux版本。

2、确认磁盘空间没有满

执行 df -h 查看剩余磁盘空间&＃xff0c;如果发现磁盘空间确实已经用完&＃xff0c;则尽快删除不需要的文件。

如果通过 du 计算各个目录的总和却发现根本不会用完磁盘空间时&＃xff0c;就需要注意了&＃xff0c;可能是某个被删除的文件还没完全释放&＃xff0c;导致 df 看起来已经用完&＃xff0c;但 du 却又统计不到。

这时候可以执行 lsof | grep -i deleted 找到被删除的大文件&＃xff0c;将其对应的进程杀掉&＃xff0c;释放该文件描述符。

如果该进程不能被杀掉&＃xff0c;例如是 mysqld 进程在占用的话&＃xff0c;可以在 MySQL 里找到是哪个内部线程在用&＃xff0c;停止该线程即可。

曾经发生过这样一个例子&＃xff1a;

用vim打开MySQL的slow query log&＃xff0c;退出时选择了 “wq” 指令&＃xff0c;也就是保存退出&＃xff0c;结果悲剧发生了。

因为在其打开的那段时间内&＃xff0c;slow query log有新日志产生&＃xff0c;会持续写入&＃xff0c;但他退出时采用保存退出的方式&＃xff0c;变成了一个“新”文件(或者说新文件句柄 file handler)&＃xff0c;这个“新”文件无法被mysqld进程识别&＃xff0c;

mysqld进程依旧将slow query log写入到原来它打开的那个文件(或者说文件句柄)里&＃xff0c;该日志文件在持续增长&＃xff0c;但手工保存退出的文件却再也不增长了&＃xff0c;直接查看文件看不出任何异常。

这时候只能用 lsof -p &＃96;pidof mysqld&＃96; 才能看到该文件。

解决方法很简单&＃xff0c;将原来的文件备份一下&＃xff0c;执行下面的指令&＃xff1a;

FLUSH SLOW LOGS;

备注&＃xff1a;MySQL 5.5开始才支持 BINARY/ENGINE/ERROR/GENERAL/RELAY/SLOW 等关键字&＃xff0c;之前的版本只能刷新全部日志。

3、确认数据表状态

如果是MyISAM引擎

默认配置下&＃xff0c;MyISAM引擎最大可支持256TB(myisam_data_pointer_size &＃61; 6&＃xff0c;256^6 &＃61; 256TB)&＃xff0c;除非操作系统层有限制。

在MySQL5.0中&＃xff0c;MyISAM引擎行记录默认是动态长度&＃xff0c;单表最大可达256TB&＃xff0c;MyISAM行指针(myisam_data_pointer_size)长度为6字节。

在这之前&＃xff0c;MyISAM行指针默认长度为4字节&＃xff0c;只支持4GB的数据。改行指针最大值可设为8字节。

在行指针设置较小不够用的时候&＃xff0c;为提高MyISAM表最大容量&＃xff0c;可以修改表定义设定MAX_ROWS的值&＃xff1a;ALTER TABLE &＃96;xx&＃96; ENGINE&＃61;MyISAM MAX_ROWS&＃61;nn

备注&＃xff1a;表定义中&＃xff0c;AVG_ROW_LENGTH 属性定义的是 BLOB/TEXT 字段类型的最大长度。

如果是InnoDB引擎

ibdata*共享表空间最后一个文件没有设置成自增长&＃xff0c;或者超过32位系统的单文件大小限制。

解决方法&＃xff1a;

1、ibdata*的最后一个文件(非最后一个文件无法设置为自动增长)设置成自动增长&＃xff1b;

2、检查操作系统&＃xff0c;迁移到64位操作系统下&＃xff1b;

3、转成独立表空间&＃xff1b;

4、删除历史数据&＃xff0c;重整表空间&＃xff1b;

如果是MEMORY引擎

1、适当提高max_heap_table_size设置(注意该值是会话级别&＃xff0c;不要设置过大&＃xff0c;例如1GB&＃xff0c;一般不建议超过256MB)&＃xff1b;

2、执行ALTER TABLE t_mem ENGINE&＃61;MEMORY; 重整表空间&＃xff0c;否则无法写入新数据&＃xff1b;

3、删除部分历史数据或者直接清空&＃xff0c;重整表空间&＃xff1b;

4、设置 big_tables &＃61; 1&＃xff0c;将所有临时表存储在磁盘&＃xff0c;而非内存中&＃xff0c;缺点是如果某个SQL执行时需要用到临时表&＃xff0c;则性能会差很多&＃xff1b;

顺便说下&＃xff0c;如果数据表有一列自增INT做主键&＃xff0c;但是该ID值达到了INT最大值的话&＃xff0c;MyISAM、MEMORY、InnoDB三种引擎的告警信息是不一样的。

InnoDB引擎的告警信息类似这样&＃xff1a;

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

而MyISAM和MEMORY引擎则都是这样&＃xff1a;

ERROR 1062 (23000): Duplicate entry ’4294967295′ for key ‘PRIMARY’

参考

觉得文章有用&＃xff1f;立即&＃xff1a;

和朋友一起 共学习 共进步&＃xff01;

猜想失败&＃xff0c;您看看下面的文章有用吗&＃xff1f;