数据库内核月报－ 2018/04 - MySQL · 特性分析 · (deleted) 临时空间 - 《数据库内核月报》

1. 简介
2.产生临时空间的原因
3.如何避免临时空间过大

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