案例分享-MySQL服务器/tmp目录被占满

描述:

MySQL服务器在每天的22点/tmp目录磁盘空间被占满,持续10分钟左右,然后自动恢复./tmp目录大小10G,平时可用空间8G左右.MySQL版本5.5

分析

1.在问题出现时,进入/tmp目录,ls –al查看具体文件.

2.在问题出现时,登录MySQL,执行show processlist查看是否存在异常SQL.

3.查看MySQL慢查询日志.

4.MySQL配置情况:

a)default_storage_engine         = InnoDB

b)transaction_isolation           = READ-COMMITTED

c)binlog_format                 = mixed

d)binlog_cache_size              = 32K

e)max_binlog_cache_size         = 18446744073709547520

f)tmpdir                       = /tmp

总结

1.InnoDB存储引擎,在READ-COMMITTED事务隔离级别的情况下(默认的级别是REPEATABLE-READ),普通的DELETE操作,在记录binlog的时候,会采用ROW模式.(暂时还不清楚原因,以后分析).

2.程序在每天的22点,有一个清理的定时任务.自动删除R表的数据,如下:

delete from R

where time < xxx;

将某天之前的数据清除.但是该表比较大,近50G.

3.MySQL参数

binlog_cache_size

max_binlog_cache_size

参考http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html

4.每次执行定时任务的时候,因为binlog记录的是ROW模式,再加上表的数据量比较大,binlog缓存一定会超过32K,结果就会在/tmp目录下生成临时文件(参考: When a thread that handles

the transaction starts, it allocates a buffer of binlog_cache_size to

buffer statements. If a statement is bigger than this, the thread opens a

temporary file to store the transaction. The temporary file is deleted when the

thread ends),MySQL默认配置,在64位系统情况下,binlog文件大小最大可以达到16EB.但是系统的/tmp目录是10G,所以事务执行一半,磁盘空间被占满,事务回滚.

5.事后查看R表的数据以及binlog记录,验证了第4步的推论.

解决

在没有新数据写入的前提下,把确定保留的数据先放到新表里面,然后删除旧表,再把新表重命名.

create table R_20130220 select * FROM R where  time >= xxx;

DROP TABLE R;

RENAME TABLE R_20130220 TO R;

隔天观察nagios和cacti监控,故障恢复.