mysql 磁盘空间100%

MySQL大事务可能会导致过多的占用临时文件，导致磁盘空间撑满的问题

本例说明下binlog cache产生的临时文件

案例复现

调小binlog_cache_size，让DML使用临时文件

使用存储过程模拟大事务

创建表 create table t1( id int AUTO_INCREMENT, name varchar(11) not null, addr varchar(11) not null, PRIMARY KEY ( id ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建存储过程

创建表
create table t1(
id int AUTO_INCREMENT,
name varchar(11) not null,
addr varchar(11) not null,
 PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建存储过程
show create procedure insert_data;
drop procedure if exists insert_data;
delimiter $$
create procedure insert_data()
begin
    declare n int default 1;
    declare MAX int default 50000000;
    while n < MAX do
        insert into t1 values (null,'cjr','成都');
        set n = n + 1;
    end while;
end
$$
delimiter ;

执行存储过程（大事务）
call insert_data();

mysql进程号 25319

lsof -p 25319 |grep deleted 查看binlog cache临时文件

产生大量临时文件

也可通过select * from performance_schema.file_instances ;查看临时文件的路径和名字

可以看到此时产生大量临时文件，会占用到

使用fallocate产生一个大文件，模拟磁盘即将撑满

磁盘满了

此时报错

binlog cache相关概念

binlog cache：它是用于缓存binlog event的内存，大小由binlog_cache_size控制

binlog cache 临时文件：是一个临时磁盘文件，存储由于binlog cache不足溢出的binlog event，该文件名字由"ML"打头，由参数max_binlog_cache_size控制该文件大小

binlog file：代表binglog 文件，由max_binlog_size指定大小。

binlog event：代表binlog中的记录，如MAP_EVENT/QUERY EVENT/XID EVENT/WRITE EVENT

Binlog_cache_disk_use统计值用于表示使用了多少次临时物理文件存储event

整个事务的event在commit的时候才会真正写入到binary log，在此之前这些Event都存放在另外一个地方我们可以统称为binlog cache 。

事务binlog event写入流程

binlog cache和binlog临时文件都是在事务运行过程中写入，一旦事务提交，binlog cache和binlog临时文件都会释放掉。而且如果事务中包含多个DML语句，他们共享binlog cache和binlog 临时文件。整个binlog写入流程如下：

1. 事务开启

2. 执行dml语句，在dml语句第一次执行的时候会分配内存空间binlog cache

3. 执行dml语句期间生成的event不断写入到binlog cache

4. 如果binlog cache的空间已经满了，则将binlog cache的数据写入到binlog临时文件，同时清空binlog cache。如果binlog临时文件的大小大于了max_binlog_cache_size的设置则抛错ERROR 1197

5. 事务提交，整个binlog cache和binlog临时文件数据全部写入到binlog file中，同时释放binlog cache和binlog临时文件。但是注意此时binlog cache的内存空间会被保留以供THD上的下一个事务使用，但是binlog临时文件被截断为0，保留文件描述符。其实也就是IO_CACHE(参考后文)保留，并且保留IO_CACHE中的分配的内存空间，和物理文件描述符

6. 客户端断开连接，这个过程会释放IO_CACHE同时释放其持有的binlog cache内存空间以及持有的binlog 临时文件。