宝塔面板MySQL 5.7 导致磁盘IO暴增 临时表空间文件ibtmp1暴增原因及解决方法

心得4个月前发布 xieniao
106 0 0

1. MySQL临时表空间ibtmp1介绍

关于临时表、临时表空间ibtmp1的概念参考官方解释,临时表一种是用户通过create temporary table创建的显式临时表,另一种是复杂SQL执行时临时创建的隐式辅助表,当临时表需要存储的数据超过了tmp_table_size或max-heap-table-size中的较大值,那么临时表数据将会存储到磁盘,在MySQL 5.7中就是基于ibtmp1文件的临时表空间中。显式临时表数据和undo存于ibtmp1时,用户断开连接虽然释放了临时表,但实际的使用空间并不会释放,只有重启数据库才能真正释放这部分空间(这部分的功能bug在MySQL 8.0中得到解决)。另外,关于临时表的具体占用情况可以通过INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO查看。
MySQL 5.7中临时表空间通过innodb_temp_data_file_path参数控制,可以在配置文件my.cnf中根据需要设置临时表空间的路径、名称、大小,默认是ibtmp1:12M:autoextend,默认初始大小为12M,自增无上限,每次数据库重启之后会被删除重建。

 

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

临时表释放后,空间会释放,但是磁盘空间不会释放,空闲空间可以被复用。释放磁盘空间只能重启。

 

ibtmp1增长的原因

ibtmp1增长主要与SQL有关,尤其是大量的分组聚合,排序,join查询SQL.通常如下情况会造成iptmp1上涨:

1.查询语句会先查询temp_table_size(内存分配)的量,当临时存储的量超过这个参数限制时,就会在iptmp1中申请占用空间。

2.select order group by GROUP BY 无索引字段或group by + order by 的子句字段不一样时。

3.select (select) 子查询

4.insert into select … from … 表数据复制

5.select union select 联合语句

 

临时表空间相关的参数:tmp_table_size ;max_heap_table_size ;innodb_temp_data_file_path

#查看ibtmp1文件大小

 

 

解决办法

1.在 MYSQL配置文件限制ibtmp1文件大小:

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

 

2.优化SQL,避免使用临时表。

3.重启mysql实例释放ibtmp1文件

© 版权声明

相关文章

暂无评论

您必须登录才能参与评论!
立即登录
暂无评论...