Allen 2023-07-28 0 884 0 0 0 0
Mysql,网站运营,Mysql里的ibtmp1文件太大,导致磁盘空间被占满 ( www/server/data/ibtmp1 )

早上查看磁盘的时候发现磁盘空间100%

排查文件,原来是www/server/data/ibtmp1,已经21G多了。此文件是用于数据库临时表空间


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文件大小

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE/1024/1024

AS TotalSize_MB, DATA_FREE/1024/1024 as FreeSize_MB, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES

WHERE TABLESPACE_NAME = 'innodb_temporary';

解决办法

1.需在配置文件:/etc/my.cnf中加入限制

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

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

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


参考:

一、临时表空间

MySQL 5.7在临时表空间上做了改进,已经实现将临时表空间从 ibdata(共享表空间文件)中分离。并且可以重启重置大小,避免出现像以前 ibdata 过大难以释放的问题。

1、表现

MySQL启动时 datadir 下会创建一个 ibtmp1 文件,默认配置为 ibtmp1:12M:autoextend,即初始大小为 12M,默认值下会无限扩展。

通常来说,查询导致的临时表(如group by)如果超出 tmp_table_size、max_heap_table_size 大小限制则创建 innodb 磁盘临时表(MySQL5.7默认临时表引擎为 innodb),存放在共享临时表空间。

如果某个操作创建了一个大小为100M的临时表,则临时表空间数据文件会扩展到100M大小以满足临时表的需要。当删除临时表时,释放的空间可以重新用于新的临时表,但 ibtmp1 文件保持扩展大小。

2、临时表空间与 tmpdir 对比

共享临时表空间用于存储非压缩InnoDB临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等数据;

tmpdir 用于存放指定临时文件(temporary files)和临时表(temporary tables),与共享临时表空间不同的是,tmpdir存储的是压缩的InnoDB临时表。

二、什么情况下会用到临时表?

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当 explain 查看执行计划结果的 extra 列中,如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到:

1、insert into table1  select ...  from table2 。

2、group by 无索引字段或 group by 和 order by 的字段不一样。

3、distinct 的值和 group by 的值不一样,无法利用稀疏索引。

三、怎么解决 ibtmp1 文件空间占用的问题?

1、万能的重启大法,找个合适的时间,切换数据库,重启老的主库。

因为临时表空间使用动态的表空间ID,因此每次重启时都会变化(每次重启时,都会重新初始化临时表空间文件)。

2、通过配置 innodb_temp_data_file_path 控制 ibtmp1 文件的最大值,避免表空间大小无限增加。

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

12M是文件的初始大小,10G是文件的最大值,超过最大值则系统会提示报错

ERROR 1114 (HY000): The table '/data/msb_5_7_31/tmp#sql_xxxxx_0' is full

来源:https://www.modb.pro/db/621529


Tag: Mysql 网站运营
我也要发一个   ·   返回首页   ·   返回[网站]   ·   前一个   ·   下一个
欢迎评论
未登录,
请先 [ 注册 ] or [ 登录 ]
(一分钟即可完成注册!)
返回首页     ·   返回[网站]   ·   返回顶部