首页 > 问答 > 关键词  > mysql最新资讯  > 正文

MYSQL的ibtmp1文件太大怎么处理

2020-07-14 14:46 · 稿源:数据库干货铺

1. 啥情况呀

测试环境机器磁盘空间不足的告警打破了下午的沉寂,一群人开始忙活着删数据。但是,不久前刚清理了一波数据,测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据,于是,我悄悄的进入数据目录下,发现一个不寻常的点,ibtmp1 文件有192G

ll-hibtmp1
-rw-r-----1mysqlmysql192GAug1216:20ibtmp1

2. 怎么处理

2.1 简单说明

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

2.2 解决办法

a) 找个空闲时间关闭数据

#设置innodb_fast_shutdown参数SETGLOBALinnodb_fast_shutdown=0;#此步骤可以省略
#关闭数据库实例shutdown;#因本实例为MySQL5.7可以直接在SQL命令行中shutdown关闭

关闭后ibtmp1 文件会自动清理

b) 修改my.cnf配置文件

为了避免ibtmp1 文件无止境的暴涨导致再次出现此情况,可以修改参数,限制其文件最大尺寸。

如果文件大小达到上限时,需要生成临时表的SQL无法被执行(一般这种SQL效率也比较低,可借此机会进行优化)

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G#12M代表文件初始大小,5G代表最大size

c) 启动mysql服务

启动数据库后可以查一下是否生效

mysql>showvariableslike'innodb_temp_data_file_path';+----------------------------+-------------------------------+|Variable_name|Value|+----------------------------+-------------------------------+|innodb_temp_data_file_path|ibtmp1:12M:autoextend:max:5G|+----------------------------+-------------------------------+1rowinset(0.01sec)

3. 什么情况下会用到临时表

当EXPLAIN 查看执行计划结果的 Extra 列中,如果包含 Using Temporary 就表示会用到临时表,例如如下几种常见的情况通常就会用到:

a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时

/**先看一下表结构*/mysql>showcreatetabletest_tmp1\G***************************1.row***************************
Table:test_tmp1CreateTable:CREATETABLE`test_tmp1`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(50)DEFAULTNULL,
`col2`varchar(25)DEFAULTNULL,PRIMARYKEY(`id`),KEY`name`(`name`)
)ENGINE=InnoDBAUTO_INCREMENT=16DEFAULTCHARSET=utf81rowinset(0.00sec)/**groupby无索引字段*/mysql>explainselect*fromtest_tmp1groupbycol2;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|test_tmp1|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary;Usingfilesort|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+/**groupby与orderby字段不一致时,及时groupby和orderby字段有索引也会使用*/mysql>explainselectnamefromtest_tmp1groupbynameorderbyiddesc;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+|1|SIMPLE|test_tmp1|NULL|range|name|name|153|NULL|3|100.00|Usingindexforgroup-by;Usingtemporary;Usingfilesort|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+1rowinset,1warning(0.02sec)

b) order by 与distinct 共用,其中distinct与order by里的字段不一致(主键字段除外)

/**例子中有无索引时会存在,如果2个字段都有索引会如何*/mysql>altertabletest_tmp1addkeycol2(col2);
QueryOK,0rowsaffected(1.07sec)
Records:0Duplicates:0Warnings:0/**结果如下,其实该写法与groupby+orderby一样*/mysql>explainselectdistinctcol2fromtest_tmp1orderbyname;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|test_tmp1|NULL|index|col2|col2|78|NULL|8|100.00|Usingtemporary;Usingfilesort|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+1rowinset,1warning(0.00sec)

c) UNION查询(MySQL5. 7 后union all已不使用临时表)

/**先测一下unionall的情况*/mysql>explainselectnamefromtest_tmp1unionallselectnamefromtest_tmp1whereid<10;+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|1|PRIMARY|test_tmp1|NULL|index|NULL|name|153|NULL|8|100.00|Usingindex||2|UNION|test_tmp1|NULL|range|PRIMARY|PRIMARY|4|NULL|8|100.00|Usingwhere|+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+2rowsinset,1warning(0.01sec)/**再看一下union作为对比,发现出现了使用临时表的情况*/mysql>explainselectnamefromtest_tmp1unionselectnamefromtest_tmp1whereid<10;+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|1|PRIMARY|test_tmp1|NULL|index|NULL|name|153|NULL|8|100.00|Usingindex||2|UNION|test_tmp1|NULL|range|PRIMARY|PRIMARY|4|NULL|8|100.00|Usingwhere||NULL|UNIONRESULT|<union1,2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+3rowsinset,1warning(0.00sec)

d) insert into select ...from ...

/**简单看一下本表的数据重复插入的情况*/mysql>explaininsertintotest_tmp1(name,col2)selectname,col2fromtest_tmp1;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|INSERT|test_tmp1|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|NULL||1|SIMPLE|test_tmp1|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+2rowsinset(0.00sec)

小结: 上面列举的是最常见的使用临时表的情况,其中基本都是引起慢查询的因素,因此,如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。

4. 和临时表空间相关的参数有哪些

各参数之间相互影响,其中直接影响临时表空间的参数如要有如下几个

innodb_temp_data_file_path
tmp_table_size
max_heap_table_size
default_tmp_storage_engine
internal_tmp_disk_storage_engine

5. 下面来模拟一个ibtmp1 文件快速膨胀的例子

5.1 调整参数值

上面列出了主要的参数,那么先调整一下参数,以便于模拟

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

调整后重启数据库

5.2 造一批数据

/**造一张表或者从其他表复制一批数据,为了方便模拟,可以不创建主键及索引*/mysql>createtabletest_tmp3select*fromdb1.tbname;
QueryOK,15948372rowsaffected(2min27.24sec)
Records:15948372Duplicates:0Warnings:0

此时查看一下ibtmp1 文件的大小

ll-hibtmp1
-rw-r-----1mysqlmysql12MAug1516:06ibtmp1/**此时是默认的初始大小*/

5.2 使用insert into ... select * from ...的方式插入

/**此方式将会使用临时表空间,且tmp_table_size参数已调小为16M,本表当前有2G多,所以会使用临时表空间*/mysql>insertintotest_tmp3select*fromtest_tmp3;
QueryOK,15948372rowsaffected(2min7.40sec)
Records:15948372Duplicates:0Warnings:0

此时 查看一下ibtmp1 文件的大小

ll-hibtmp1
-rw-r-----1mysqlmysql2.8GAug1516:17ibtmp1/**此时已使用了2.8G*/

此时该表的size如下

ll-hbak_db/test_tmp3*/**结果中已有5.8G*/-rw-r-----1mysqlmysql8.9KAug1516:04bak_db/test_tmp3.frm-rw-r-----1mysqlmysql5.8GAug1516:16bak_db/test_tmp3.ibd

5.3 继续测试,看看会发生什么

因为ibtmp1 当前设置的最大值为5G,继续复制一个5.8G的数据,会不会异常,如果异常有什么表现?

/**继续插入时因临时表空间大小有限制,超过5G后将异常,信息如下*/mysql>insertintotest_tmp3select*fromtest_tmp3;
ERROR1114(HY000):Thetable'/app/data/mysql3306/tmp/#sql_32469_0'isfull

此时 查看一下ibtmp1 文件的大小

ll-hibtmp1
-rw-r-----1mysqlmysql5.0GAug1516:17ibtmp1/**此时已使用了5.0G,已达到上限*/

数据库日志里也会记录本次异常

2019-08-15T08:23:47.016495Z3[ERROR]/usr/local/mysql5.7/bin/mysqld:Thetable'/app/data/mysql3306/tmp/#sql_32469_0'isfull

以上测试实例因不同的版本可能会有差异,建议大家亲自测试一下。

本文转载自微信公众号【数据库干货铺】。

举报

  • 相关推荐
  • 大家在看
  • Prime Intellect:AI开发规模化的民主化平台

    Prime Intellect是一个致力于AI开发规模化民主化的平台,提供全球计算资源的发现、模型训练以及共同拥有智能创新的能力。它通过分布式训练跨集群,使得用户能够训练最前沿的模型,并且共同拥有由此产生的开放AI创新成果,包括语言模型和科学突破。

  • Zed:高性能、多人协作代码编辑器

    Zed是由Atom和Tree-sitter的创造者开发的高性能、多人协作代码编辑器,开源且集成了AI代码生成功能。它利用多核心CPU和GPU,实现即时启动、快速文件加载和响应键盘输入。Zed支持GitHub Copilot,并通过内置助手面板与模型进行对话式交互,以生成或重构代码。

  • AuraFlow:开源的基于流的文本到图像生成模型

    AuraFlow v0.1是一个完全开源的、基于流的文本到图像生成模型,它在GenEval上达到了最先进的结果。目前模型处于beta阶段,正在不断改进中,社区反馈至关重要。感谢两位工程师@cloneofsimo和@isidentical将此项目变为现实,以及为该项目奠定基础的研究人员。

  • 墨狐AI:短篇小说写作助手

    墨狐AI是一个专为短篇小说创作者设计的在线写作助手,它通过提供创意大纲、续写故事、生成剧情树和剧本创作等功能,帮助作者激发灵感,提高写作效率。产品背景信息显示,墨狐AI旨在解决创作者在创作过程中遇到的难题,如灵感枯竭或故事发展困难。目前,墨狐AI的定位是辅助工具,详情可访问官网查看更多介绍。

  • LLaVA-NeXT:大型多模态模型,处理多图像、视频和3D数据。

    LLaVA-NeXT是一个大型多模态模型,它通过统一的交错数据格式处理多图像、视频、3D和单图像数据,展示了在不同视觉数据模态上的联合训练能力。该模型在多图像基准测试中取得了领先的结果,并在不同场景中通过适当的数据混合提高了之前单独任务的性能或保持了性能。

  • 老鱼简历:在线制作简历,简单高效。

    老鱼简历是一个在线简历制作平台,提供多种简历模板,支持AI生成简历,帮助用户快速制作出专业且个性化的简历。用户可以根据自己的需求选择不同的模板,并通过简单的在线编辑完成简历的制作,支持下载为PDF或PNG格式,满足不同场景的求职需求。

  • Enchanted:与私有自托管语言模型对话的iOS/macOS应用

    Enchanted是一个开源的、兼容Ollama的macOS/iOS/visionOS应用,它允许用户与私有自托管的语言模型如Llama 2、Mistral、Vicuna等进行对话。它基本上是一个连接到私有模型的ChatGPT应用界面。Enchanted的目标是提供一个产品,允许在iOS生态系统(macOS、iOS、Watch、Vision Pro)的所有设备上提供无过滤、安全、私密和多模态的体验。

  • Logo Galleria:在线AI Logo制作,快速生成个性化标志。

    Logo Galleria是一个在线AI Logo制作平台,利用人工智能技术帮助用户快速生成个性化的标志设计。它通过用户输入的行业、风格等参数,提供定制化的标志设计方案,满足不同用户的设计需求。该平台的主要优点是操作简便、设计效率高,可广泛应用于品牌建设、产品包装等场景。

  • Afforai.com:AI驱动的参考文献管理助手

    Afforai是一个AI驱动的参考文献管理助手,旨在帮助研究人员管理、注释、引用论文,并以AI的可靠性进行文献综述。它提供了一个全新的研究材料存储方式,使用户能够专注于真正重要的事情。Afforai支持多种文档格式,包括DOI、URL、PDF等,并具有多种搜索模式,可以连接数百篇论文进行总结、比较和翻译。此外,Afforai还提供数据引用,使用户能够方便地核实信息来源,确保研究的可靠性。

  • Rodel Agent:集成聊天、文本转图像、文本转语音和机器翻译的桌面应用

    Rodel Agent 是一款集成了聊天、文本到图像、文本到语音以及机器翻译功能的Windows桌面应用程序。它支持当前主流的AI服务,为用户提供了卓越的桌面AI体验。该产品的主要优点包括强大的集成功能、用户友好的界面以及对主流AI服务的支持,能够显著提高用户的工作效率和创造力。

  • DictionaryByGPT4:一本由GPT4生成的英语单词书,覆盖8000+单词

    DictionaryByGPT4是一个由GPT4模型生成的英语单词学习工具,它通过分析超过8000个单词,为每个单词提供词义、例句、词根词缀、变形、文化背景、记忆技巧和小故事等全方位信息,帮助用户深入理解单词的来源、使用场景以及记忆方法。该产品特别适合需要提升英语词汇量和理解力的学习者。

  • gpt-frontend-code-gen:前端页面生成神器,提升开发效率

    gpt-frontend-code-gen 是一个基于 React 和 Vite 构建的前端项目,结合 Koa 后端服务,实现前端页面生成并预览的功能。它使用 GPT-4 模型,支持 Chakra UI 和 ShadcnUI 组件生成,允许开发者通过对话形式持续迭代和修改页面,直到达到满意的效果。

  • OpenDiLoCo:开源实现分布式低通信AI模型训练

    OpenDiLoCo是一个开源框架,用于实现和扩展DeepMind的分布式低通信(DiLoCo)方法,支持全球分布式AI模型训练。它通过提供可扩展的、去中心化的框架,使得在资源分散的地区也能高效地进行AI模型的训练,这对于推动AI技术的普及和创新具有重要意义。

  • SmartCrawl:将任何网站转化为AI驱动的API。

    SmartCrawl是一个创新的在线工具,它允许用户将任何网站转化为API,通过AI技术实现数据的自动化抓取和处理。这项技术对于需要从网站获取数据的开发者和企业来说非常重要,因为它简化了数据集成的过程,提高了效率。产品目前处于Beta测试阶段,用户可以通过加入等待名单来获取试用机会。

  • TF-ID:学术文献中表格和图表的识别工具

    TF-ID是一个由Yifei Hu创建的用于从学术论文中提取表格和图表的对象检测模型系列。这些模型基于microsoft/Florence-2检查点进行微调,提供带或不带标题文本的版本,旨在提高学术文献信息的可访问性和处理效率。

  • FlashAttention:快速且内存高效的精确注意力机制

    FlashAttention是一个开源的注意力机制库,专为深度学习中的Transformer模型设计,以提高计算效率和内存使用效率。它通过IO感知的方法优化了注意力计算,减少了内存占用,同时保持了精确的计算结果。FlashAttention-2进一步改进了并行性和工作分配,而FlashAttention-3针对Hopper GPU进行了优化,支持FP16和BF16数据类型。

  • aTrain:一款用于离线语音转录的GUI工具

    aTrain是由格拉茨大学商业分析与数据科学中心的研究人员开发,并由格拉茨知识中心的研究人员测试的一款离线语音转录工具。它利用最新的机器学习模型,无需上传任何数据即可自动转录语音录音。aTrain在《行为与实验金融学杂志》上发表的论文中被介绍,如果用于研究,请引用该论文。它支持Windows 10和11系统,用户可以通过Microsoft应用商店或BANDAS中心网站下载安装。对于Linux系统,提供了Wiki上的安装指南。aTrain的主要优点包括无需上传数据的隐私保护、高质量的转录质量、以及在本地计算机上的快速处理速度。

  • Graphcore:AI加速器,推动人工智能的突破

    Graphcore是一家专注于人工智能硬件加速器的公司,其产品主要面向需要高性能计算的人工智能领域。Graphcore的IPU(智能处理单元)技术为机器学习、深度学习等AI应用提供了强大的计算支持。公司的产品包括云端IPU、数据中心IPU以及Bow IPU处理器等,这些产品通过Poplar® Software进行优化,能够显著提升AI模型的训练和推理速度。Graphcore的产品和技术在金融、生物技术、科研等多个行业都有应用,帮助企业和研究机构加速AI项目的实验过程,提高效率。

  • SandTech:企业级AI和数据解决方案提供商

    Sand Technologies提供企业级AI和数据解决方案,帮助公司解决实际商业问题并实现有意义的结果。公司已在该领域深耕十年,开发定制AI算法和模型,构建在可大规模管理数据的平台和基础设施上。

  • Melodisco.so:AI 音乐播放器智能推荐,发现你的音乐新世界

    Melodisco是一个基于人工智能技术的音乐播放器,通过智能推荐算法,为用户提供个性化的音乐播放体验。它能够根据用户的喜好和听歌习惯,推荐适合的音乐,帮助用户发现新的音乐风格和艺术家。产品背景信息显示,Melodisco致力于为用户提供一个全新的音乐探索平台,无论是音乐爱好者还是寻找灵感的创作者,都能在这里找到适合自己的音乐。目前产品提供免费试用,具体价格和定位信息未在页面上明确展示。

今日大家都在搜的词:

热文

  • 3 天
  • 7天