mysql innodb临时表btmp1文件太大

某日生产环境(数据库实例)告警,磁盘使用率过高!

检查发现是由于mysql的data目录的ibtmp1文件太大,达到了30GB

一、ibtmp1文件是干嘛的?

就是用来存放临时表查询时的数据。

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

查询语句会先查询temp_table_size(内存分配)的量,当临时存储的量超过这个参数限制时,就会在iptmp1中申请占用空间。
select order group by GROUP BY 无索引字段或group by + order by 的子句字段不一样时。
select (select) 子查询
insert into select … from … 表数据复制
select union select 联合语句

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

三、解决办法
1、去检查sql!

通过慢查询日志找到慢sql(包含子查询的sql着重关注),要确保子查询内的结果集不要太大(返回太多行),可以通过子查询的where条件缩减结果集。

或是通过show processlist查询的,如图:

mysql innodb临时表btmp1文件太大

我这里正是因为sql太慢,和子查询的结果集太大,导致了mysql线程卡死。通过优化后,iptmp1文件大小正常了。

2、my.cnf配置临时文件大小限制

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

不推荐,因为一旦临时文件增长到500M后, 再进行需要临时表的sql查询(例如子查询),是会报错的!

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/10602

(0)
凯影的头像凯影
上一篇 2023年12月15日 下午3:29
下一篇 2023年12月18日 下午3:06

相关推荐

  • 1558 -Column count ot mysql.proc 1s wrong.Expected 20,found 16.Created with MySQL50096, now running 50525. Please use mysql_upgrade to fix this error.

    我在MySQL版本5.5上面创建存储过程的时候就出现了上面的错误。甚至使用show procedure status 查看存储过程都会报上面的错误。 注意操作前先备份!!! 错误原因:是由于你曾经升级过MySQL,或用不同的MySQL版本进行备份迁移恢复。升级和迁移完后未使用mysql_upgrade升级数据结构造成的。 mysql.proc:是MySQL的…

    2023年12月21日
    19600
  • MySQL InnoDB调试死锁

    前提 1、在RR隔离级别下。2、查看间隙锁是否关闭区间锁(间隙锁,临键锁)是InnoDB特有施加在索引记录区间的锁,MySQL5.6可以手动关闭区间锁,它由innodb_locks_unsafe_for_binlog参数控制: show global variables like “innodb_locks%”; 3、show glo…

    2023年10月23日
    24600
  • Sql Server2005 如何导出数据库数据

    Sql Server2005数据库数据导出两种方式 第一种,数据库数据直接备份,右键点击需要备份的库,打开任务,打开备份,然后记一下数据库备份文件位置,直接点击确定即可成功,注意修改文件路径可能会提示文件无权限 第二种方式 打开本地服务,停止Sql server服务,然后去复制需要的ldf与mdf文件

    2022年12月16日
    39300
  • 关于mysql now()显示的时间不正确

    如果MySQL中的NOW()函数返回的时间不正确,可能有以下几个原因: 时区设置不正确。请使用SELECT @@global.time_zone;命令检查MySQL服务器的全局时区设置,并确保它与你所在地区的时区匹配。如果时区设置不正确,请使用SET GLOBAL time_zone = ‘timezone’;命令进行更正。 系统时间…

    2023年8月22日
    44600
  • SqlServer查看默认编码格式

    先使用系统函数SERVERPROPERTY查询当前数据库的编码名称,再使用编码名称通过系统函数COLLATIONPROPERTY查询编码编号,再进行比对。示例如下: 查询比对如下结果:

    2023年2月18日
    35000

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信