资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/MYSQL的ibtmp1文件太大问题

MYSQL的ibtmp1文件太大问题

IT那活儿 / 1760人阅读
MYSQL的ibtmp1文件太大问题

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

认识ibtmp1

该参数是5.7的新特性。

针对临时表及相关对象引入新的“non-redo” undo log，存放于临时表空间。该类型的undo log非 redolog 因为临时表不需崩溃恢复、也就无需redo logs，但却需要 undo log用于回滚、MVCC等。

默认的临时表空间文件为ibtmp1，位于数据目录在每次服务器启动时被重新创建，可通过innodb_temp_data_file_path指定临时表空间。

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


常见的使用tmp临时表空间的场景

当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到：
1、UNION查询（MySQL 5.7起，执行UNION ALL不再产生临时表，除非需要额外排序）；
2、用到TEMPTABLE算法或者是UNION查询中的视图；
3、ORDER BY和GROUP BY的子句不一样时；
4、表连接中，ORDER BY的列不是驱动表中的；
5、DISTINCT查询并且加上ORDER BY时；
6、SQL中用到SQL_SMALL_RESULT修饰符的查询；
7、FROM中的子查询（派生表）；
8、子查询或者semi-join时创建的表；

9、评估多表UPDATE语句。


实例分析

1、问题现象

Mysql服务磁盘空间告警，经过排查发现ibtmp1文件非常大，已经超过TB级别，其他能清理的数据已经做了清理。
ll -h ibtmp1
-rw-r----- 1 mysql mysql 1.2T Aug 15 16:17 ibtmp1

2、问题分析

通过了解了ibtmp1是非压缩的innodb临时表的独立表空间，而且检查配置发现为ibtmp1:12M:autoextend，也就是支持无限扩大的，才导致了ibtmp1文件增加打了非常大的一个数值。

3、解决方案

重启数据库，释放临时表空间，同时为了避免临时表空间再次膨胀，可以将其设置一个最大的值。
--操作流程：
1）关闭数据库实例。
关闭后ibtmp1文件会自动清理。
2）修改my.cnf配置文件，限制tmp表空间的大小。
为了避免ibtmp1文件无止境的暴涨导致再次出现此情况,可以修改参数，限制其文件最大尺寸。
如果文件大小达到上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G # 12M代表文件初始大小，50G代表最大size
3）启动mysql服务，查一下是否生效。
show  variables like innodb_temp_data_file_path;
+----------------------------+-------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:50G |
+----------------------------+-------------------------------+


临时表使用的几点建议

1、设置innodb_temp_data_file_path选项，设定文件最大上限（innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M），超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。
2、检查INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO，找到最大的临时表对应的线程，kill之即可释放，但ibtmp1文件则不能释放（除非重启）。
3、择机重启实例，释放ibtmp1文件，和ibdata1不同，ibtmp1重启时会被重新初始化而ibdata1则不可以。
4、定期检查运行时长超过N秒（比如N=300）的SQL，考虑清理，避免垃圾SQL长时间运行影响业务。


本文作者：徐林

本文来源：IT那活儿（上海新炬王翦团队）

云服务器 GPU云服务器 mysql编码的问题 mysql索引性能的问题 jdbc的mysql安装问题 linux的缓存太大

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129633.html

相关文章

  • Docker Compose 1.18.0 之服务编排详解

    摘要：新建一个你能记住的目录，这个目录是应用镜像的上下文，该目录用于存放构建该镜像的资源在这个目录里面将会新建一个文件进入目录创建一个文件，将启动您的博客和一个单独的实例并挂载数据持久化到宿主机内容如下指定服务的镜像名称或镜像。 一个使用Docker容器的应用，通常由多个容器组成。使用Docker Compose，不再需要使用shell脚本来启动容器。在配置文件中，所有的容器通过servic...

    Seay 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<