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

相关推荐

  • Oracle报错:ORA-00257 错误处理

    一、错误描述 使用plsql develop工具登录数据库时,有如下报错: ORA-00257:archiver error. Connect internal only. unitl freed. 二、错误原因 archive log 日志已满 三、处理方法 1.用sys用户登录 2.查看archivlog所在位置 3.VALUE为空时,可用archive…

    2023年3月25日
    17400
  • 手把手教你在Centos7.6环境下安装Redis(含详细图文)

    1.Linux安装redis 下载: wget http://download.redis.io/releases/redis-2.8.17.tar.gz 解压源码包 tar xzf redis-2.8.17.tar.gz 解压完成后的目录 redis-2.8.17 安装 执行完make命令后,在redis-2.8.17 的 src目录下会出现编译后的 re…

    2022年6月14日
    49100
  • 解决Redis、MySQL缓存双写不一致问题

    redis、mysql双写缓存不一致: 但是在更新缓存方面,对于更新完数据库,是更新缓存呢,还是删除缓存。又或者是先删除缓存,再更新数据库,其实大家存在很大的争议。目前没有一篇全面的博客,对这几种方案进行解析。 正文 给缓存数据设置过期时间 先做一个说明,从理论上来说,给缓存设置过期时间,是保证最终一致性的解决方案。这种方案下,我们可以对存入缓存的数据设置过…

    2022年8月21日
    48500
  • 无法作为数据库主体执行,因为主体 “dbo“ 不存在、无法模拟这种类型的主体,或您没有所需的权限

    问题描述: 最近正在测试数据库复制,想删除发布服务器的发布数据库时,突然提示这个:无法作为数据库主体执行,因为主体 “dbo” 不存在、无法模拟这种类型的主体,或您没有所需的权限。已将数据库上下文更改为 “AdventureWorks2008R2”。 (Microsoft SQL Server,错误: 15517) 原因分析: 用附件文件和组的方法的还原数据…

    2023年2月18日
    27700
  • JetBrains产品安装破解分享–DataGrip

    第一步: 下载最新的 Datagrip 2022.3.1 版本安装包 我们先从 Datagrip 官网下载 Datagrip 2022.3.1 版本的安装包,下载链接如下: 第二步:卸载老版本 Datagrip 卸载时删除本地数据 第三步: 开始安装 DataGrip 2022.3.1 版本 安装路径可更改 创建桌面快捷方式 安装完成后勾选 Run Data…

    2023年3月15日
    42900

发表回复

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

在线咨询: QQ交谈

邮件:712342017@qq.com

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

关注微信