mysql+排序+tmp满,【杭州腾科分享】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监控,故障恢复.