Mysql里的ibtmp1文件太大

﹏ヽ暗。殇╰゛Y 2024-03-26 00:15 7阅读 0赞

### 一、告警现象 ###

**收到Mysql实例主机磁盘空间不足告警**

实例硬盘使用率高于设定规则阈值上限（80%）

收到告警后，立即登陆相应的服务器：  
查看确认是磁盘空间已经快被占满了，检查发现是Mysql数据目录下的ibtmp1文件涨到了200多G。

### 二、告警处理 ###

**ibtmp1文件说明：**  
ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb\_temp\_data\_file\_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在默认情况下支持大文件的系统这个文件大小是可以无限增长的。

mysql> show variables like 'innodb_temp_data_file_path';
    +----------------------------+-----------------------+
    | Variable_name              | Value                 |
    +----------------------------+-----------------------+
    | innodb_temp_data_file_path | ibtmp1:12M:autoextend |
    +----------------------------+-----------------------+
    1 row in set (0.00 sec)

**处理步骤**

1）首先备份数据库，如果Mysql服务还能正常使用，可以用日常的备份机制做一次全备；如果Mysql服务已经异常了，可以考虑物理备份。
    
    2）为了避免以后再次出现ibtmp1文件暴涨，限制其大小，需在配置文件加入：
    innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
    
    3）与业务各方沟通好，重启Mysql实例（重启后ibtmp1文件会自动清理）。
    
    4）重启后，验证配置是否生效：
    mysql> show variables like 'innodb_temp_data_file_path';
    +----------------------------+------------------------------+
    | Variable_name              | Value                        |
    +----------------------------+------------------------------+
    | innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:5G |
    +----------------------------+------------------------------+
    1 row in set (0.01 sec)

如果没有重新启动mysql服务,**ibtmp1**一旦创建就无法通过任何方法收缩.

有两种方法可以处理它:

**注意事项**:在服务器启动时,您应该将此文件的大小限制为:

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

其中max 5G表示此文件大小限制为5GB.

**解决** :如果文件已经创建,则需要重新启动服务:

SET GLOBAL innodb_fast_shutdown = 0;
    Shutdown MySQL
    remove ibtmp1
    start MySQL.

这样配置以后，就不用担心ibtmp1文件会暴涨占满磁盘空间了。

### 三、总结 ###

**可能导致ibtmp1文件会暴涨的情况：**

1.用到临时表，当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary就表示会用到临时表。
    
    2.GROUP BY无索引字段或GROUP BY + ORDER BY的子句字段不一样时。
    
    3.order by与distinct共用，其中distinct与order by里的字段不一致（主键字段除外）。
    
    4.insert into table1 select xxx from table2。

PS：一般情况下最好避免使用临时表,其大多都是引起慢查询的因素，因此，如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。