mysql 后缀 deleted,MySQL · 特性分析 · (deleted) 临时空间

1. 简介

在运行 MySQL 的服务器上，偶尔出现 du 和 df 统计空间大小差别很大的情况，原因之一是 MySQL 的临时空间过大

在Linux或者Unix系统中，通过rm或者文件管理器删除文件将会从文件系统的目录结构上解除链接(unlink)，然而如果文件是被打开的(有一个进程正在使用)，那么进程将仍然可以读取该文件，磁盘空间也一直被占用，这样就会导致我们明明删除了文件，但是磁盘空间却未被释放

这些已经删除，但空间未释放的文件，可以通过 lsof 看到，末尾标明 (deleted) 就是这种文件

如下图

MySQL 服务器中，已删除未释放空间的文件主要存在于 tmp 目录，执行以下命令可以查看实例的 tmp 目录

show variables like 'tmpdir'

如果 deleted 文件过大，会对实例的性能产生负面影响，因为写文件的磁盘IO操作远慢于内存操作，所以要尽量避免使用临时文件

如果 deleted 文件太多导致磁盘空间满，会造成 MySQL crash，这是后表现为 df -h 看到磁盘 100%，但是 du -h 看到磁盘 90% 或者更少，这时候重启占用文件句柄的进程可以快速解决问题

2.产生临时空间的原因

MySQL产生的临时文件主要有以下几类：

查询产生的临时表文件

MySQL 执行带有 order by，group by 的复制查询时，经常需要建立一个或两个临时表

临时表所需的最大空间取决于以下公式

(length of what is sorted + sizeof(row pointer))

* number of matched rows

* 2

临时表较小时，可以存放在内存中，较大时则会存在在磁盘中，这取决于两个参数 tmp_table_size 和 max_heap_table_size

tmp_table_size 和 max_heap_table_size 较小的一个决定了内存临时表的上线，临时表小于上限则为内存表，大于上限为磁盘表

执行以下命令可以查看创建内存临时表和磁盘临时表的数量

mysql> show status like '%tmp%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 1 |

| Created_tmp_files | 10 |

| Created_tmp_tables | 1 |

+-------------------------+-------+

原则上讲，临时表的数量越少越好，磁盘临时表的数量应该远远少于内存临时表的

正常情况下 Created_tmp_disk_tables/Created_tmp_tables < 25%

如果磁盘临时表过多，可能需要优化 sql

alter table 也会产生临时表，由于alter table执行频率较低，不容易引起空间问题

binlog cache 文件

事务产生的 binlog 先缓存在 binlog cache 中，事务提交后再刷到磁盘

MySQL 为每个连接分配一个 binlog cache，cache 大小由参数 binlog_cache_size 决定

如果事务使用的 binlog_cache 超过 binlog_cache_size，则在 tmpdir 下建立临时文件缓存 binlog，binlog_cache 临时文件以 ML 开头

以下两个status展示了 binlog_cache 和磁盘binlog_cache 的使用量

mysql> show status like 'binlog_cache%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 6 |

| Binlog_cache_use | 19 |

+-----------------------+-------+

我们先建一张表，插入很多条记录

mysql> show create table t;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+

| t | CREATE TABLE `t` (

`i` int(11) DEFAULT NULL,

`c` char(20) DEFAULT NULL,

`c2` char(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select count(*) from t;

+----------+

| count(*) |

+----------+

| 65536 |

+----------+

1 row in set (28.08 sec)

插入一条记录，我们看到 Binlog_cache_use 加了1，Binlog_cache_disk_use 没变

mysql> insert into t values(1,'a','a');

Query OK, 1 row affected (0.01 sec)

mysql> show status like 'binlog_cache%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 6 |

| Binlog_cache_use | 20 |

+-----------------------+-------+

2 rows in set (0.37 sec)

插入大量记录，可以看到 Binlog_cache_disk_use 和 Binlog_cache_use 各加一

mysql> insert into t select * from t;

Query OK, 65537 rows affected (1 min 26.58 sec)

Records: 65537 Duplicates: 0 Warnings: 0

mysql> show status like 'binlog_cache%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 7 |

| Binlog_cache_use | 21 |

+-----------------------+-------+

2 rows in set (0.35 sec)

如下图，临时目录下 MLjogklw (deleted) 就是 binlog cache 文件

换一个连接再次执行大量插入

mysql> insert into t select * from t limit 65536;

Query OK, 65536 rows affected (2 min 11.09 sec)

Records: 65536 Duplicates: 0 Warnings: 0

如下图，一共有两个 binlog cache文件

连接断开后，binlog cache释放，空间会自动回收

innodb临时文件

innodb 启动时也需要建立一些临时文件，innodb 临时文件以 ib 开头

srv_monitor_file

srv_dict_tmpfile

srv_misc_tmpfile

dict_foreign_err_file

lock_latest_err_file

主要保存一些监控、字典、错误日志和无法归类的信息，这部分文件占用空间较少，无须特殊关注

3.如何避免临时空间过大

针对查询产生的临时文件，应该避免频繁使用 order by、group by 操作，即使需要排序也应该尽量减少排序行数

出于提高性能的考虑，如果确实经常需要对记录排序，可以适当调大 tmp_table_size 和 max_heap_table_size

但是为了减少磁盘使用而调高 tmp_table_size 和 max_heap_table_size 并不明智，因为内存资源远比磁盘资源宝贵，不能为了节省磁盘空间而增加内存消耗

针对 binlog cache，应该少执行大事务，尤其应该减少在多个连接同时执行大事务

如果大事务比较多，可以适当调大 binlog_cache_size，但是同样不应该为了节省磁盘调整这个参数

因为连接断开后 binlog_cache 空间会释放，使用短连接执行大事务可以有效降低临时空间开销