[MySQL FAQ]系列 — 你所不知的table is full那些事

[MySQL FAQ]系列 — 你所不知的table is full那些事

http://imysql.com/2014/08/21/mysql-faq-howto-deal-with-table-full.shtml

 

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

1、查看操作系统以及MySQL的错误日志文件 确认操作系统的文件系统没有报错，并且MySQL的错误日志文件中是否有一些最直观的可见的错误提示。

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

 

2、确认磁盘空间没有满 执行 df -h 查看剩余磁盘空间，如果发现磁盘空间确实已经用完，则尽快删除不需要的文件。

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

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

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

曾经发生过这样一个例子： 用vim打开MySQL的slow query log，退出时选择了 “wq” 指令，也就是保存退出，结果悲剧发生了。

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

mysqld进程依旧将slow query log写入到原来它打开的那个文件（或者说文件句柄）里，该日志文件在持续增长，但手工保存退出的文件却再也不增长了，直接查看文件看不出任何异常。 这时候只能用 lsof -p `pidof mysqld` 才能看到该文件。 解决方法很简单，将原来的文件备份一下，执行下面的指令：

FLUSH SLOW LOGS;

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

https://dev.mysql.com/doc/refman/5.6/en/flush.html
flush [log_type] LOGS

With no log_type option, FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

FLUSH LOGS has no effect on tables used for the general query log or for the slow query log .对慢查询日志表和一般日志表无效
It also has no effect on tables used for the binary or relay log ，对从库的masterinfo表和relayloginfo表无效

With a log_type option, only the specified log type is flushed. These log_type options are permitted:
BINARY closes and reopens the binary log files.
ENGINE closes and reopens any flushable logs for installed storage engines. This causes InnoDB to flush its logs to disk.
ERROR closes and reopens the error log file.
GENERAL closes and reopens the general query log file
RELAY closes and reopens the relay log files.
SLOW closes and reopens the slow query log file.

 

测试结果看只会刷新 binlog

 

3、确认数据表状态

• 如果是MyISAM引擎

默认配置下，MyISAM引擎最大可支持256TB（myisam_data_pointer_size = 6，256^6 = 256TB），除非操作系统层有限制。 在MySQL5.0中，MyISAM引擎行记录默认是动态长度，

单表最大可达256TB，MyISAM行指针（myisam_data_pointer_size）长度为6字节。 在这之前，MyISAM行指针默认长度为4字节，只支持4GB的数据。改行指针最大值可设为8字节。

在行指针设置较小不够用的时候，为提高MyISAM表最大容量，可以修改表定义设定MAX_ROWS的值：

ALTER TABLE `xx` ENGINE=MyISAM MAX_ROWS=nn

备注：表定义中，AVG_ROW_LENGTH 属性定义的是 BLOB/TEXT 字段类型的最大长度。

 

• 如果是InnoDB引擎

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

解决方法：

1、ibdata*的最后一个文件（非最后一个文件无法设置为自动增长）设置成自动增长；

2、检查操作系统，迁移到64位操作系统下；

3、转成独立表空间；

4、删除历史数据，重整表空间；

 

• 如果是MEMORY引擎

1、适当提高max_heap_table_size设置（注意该值是会话级别，不要设置过大，例如1GB，一般不建议超过256MB）；

2、执行ALTER TABLE t_mem ENGINE=MEMORY; 重整表空间，否则无法写入新数据；

3、删除部分历史数据或者直接清空，重整表空间；

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

顺便说下，如果数据表有一列自增INT做主键，但是该ID值达到了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 5.5开始才支持 BINARY/ENGINE/ERROR/GENERAL/RELAY/SLOW 等关键字，之前的版本只能刷新全部日志。

https://dev.mysql.com/doc/refman/5.6/en/flush.html
flush [log_type] LOGS

flush engine logs
强制将innodb的redo刷新到磁盘

flush binary logs
强制将binlog刷新到磁盘，滚动生成一个新binlog

flush error logs
强制将错误日志刷新到磁盘，滚动生成一个新错误日志

flush general logs
强制将general log刷新到磁盘，滚动生成一个新general log

flush relay logs
强制将relay  log刷新到磁盘，滚动生成一个新relay log

flush slow logs
强制将慢日志刷新到磁盘，滚动生成一个新慢日志