MYSQL的ibtmp1文件太大问题及解决办法
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文件大小
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.限制ibtmp1文件大小:innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
2.优化SQL,避免使用临时表。
3.重启mysql实例释放ibtmp1文件
宝塔设置定时重启数据库方法
新建SHELL脚本,填入以下代码:service mysqld restart
针对临时表及相关对象引入新的“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临时表空间的场景
9、评估多表UPDATE语句。
实例分析
1、问题现象
ll -h ibtmp1-rw-r----- 1 mysql mysql 1.2T Aug 15 16:17 ibtmp1
2、问题分析
3、解决方案
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G # 12M代表文件初始大小,50G代表最大size
show variables like 'innodb_temp_data_file_path';+----------------------------+-------------------------------+| Variable_name | Value |+----------------------------+-------------------------------+| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:50G |+----------------------------+-------------------------------+
临时表使用的几点建议
本文作者:徐林
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。