MySQL 5.7 内部临时表

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

本文研究了在没有写查询的情况下,InnoDB行插入时,因内部临时表的问题而发生性能尖刺的情形。

In this blog post, I investigate a case of spiking InnoDB Rows inserted in the absence of a write query, and find internal temporary tables to be the culprit.

事情发生在我研究一个客户的案例时,在”InnoDB行插入“指标图上,发现了从1k行每秒激增到6K行的尖刺,但却无法和其他活动或者现象连接起来,PMM监控图形上也有同样的反映。

Recently I was investigating an interesting case for a customer. We could see the regular spikes on a graph depicting “InnoDB rows inserted” metric (jumping from 1K/sec to 6K/sec), however we were not able to correlate those spikes with other activity. The innodb_row_inserted graph (picture from PMM demo) looked similar to this (but on a much larger scale):

aa137f3682664143ca71f2154de94fe57814a919

其他例如句柄和接口的图形都没有显示同样的尖刺,在无法开启general log的情况下,我们尝试检查了所有的日志,performance_schema,触发器,存储过程,预编译语句,甚至包括binlog后发现没有任何单个的写查询语句可以导致每秒插入飙升到6K行。

Other graphs (Com*, Handler) did not show any spikes like that. I’ve examined the logs (we were not able to enable general log or change the threshold of the slow log), performance_schema, triggers, stored procedures, prepared statements and even reviewed the binary logs. However, I was not able to find any single **\write* query which could have caused the spike to 6K rows inserted.

在最后才发现,行插入飙升一定和DML有关的这种想法是错误的,出乎意料的是,尖刺是由于SELECT查询导致的,但为何SELECT查询会导致大量的InnoDB行插入操作呢?

Finally, I figured out that I was focusing on the wrong queries. I was trying to correlate the spikes on the InnoDB Rows inserted graph to the DML queries (writes). However, the spike was caused by SELECT queries! But why would SELECT queries cause the massive InnoDB insert operation? How is this even possible?

原来是与磁盘临时表有关。在MySQL 5.7版本中,内部磁盘临时表的默认引擎是InnoDB引擎,这就意味着当SELECT操作需要在磁盘上创建临时表时(例如GROUP BY操作),就会使用到InnoDB引擎。

It turned out that this is related to temporary tables on disk. In MySQL 5.7 the default setting for internal_tmp_disk_storage_engine is set for InnoDB. That means that if the SELECT needs to create a temporary table on disk (e.g., for GROUP BY) it will use the InnoDB storage engine.

但这种尖刺就一定意味着性能的下降吗?Krunal Bauskar曾经写过一篇关于5.7 InnoDB原生表性能的文章,InnoDB的内部临时表的操作并不会记录在redo和undo中,一般情况下相比原本MyISAM引擎的临时表性能更好点,但是仍需注意一下几点:

Is that bad? Not necessarily. Krunal Bauskar published a blog post originally about the InnoDB Intrinsic Tables performance in MySQL 5.7. The InnoDB internal temporary tables are not redo/undo logged. So in general performance is better. However, here is what we need to watch out for:

1、更改MySQL存储临时表的位置,原本InnoDB临时表被存储在ibtmp1表空间中,可能遇到以下的问题:

Change of the place where MySQL stores temporary tables. InnoDB temporary tables are stored in ibtmp1 tablespace file. There are a number of challenges with that:

(1)ibtmp1文件默认保存在InnoDB的数据目录,原本MyISAM临时表被放在MySQL的tmp目录,如若像MyISAM一样把临时表文件存储在MySQL的tmp目录,需要更改为

innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend
AI 代码解读

Location of the ibtmp1 file. By default it is located inside the innodb datadir. Originally MyISAM temporary tables were stored in tmpdir. We can configure the size of the file, but the location is always relative to InnoDB datadir, so to move it to tmpdir we need something like this: innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend

(2)临时表空间和其他的表空间一样都不会自动缩小其占用容量,可能会发生临时表空间容量占满磁盘,MySQL挂掉的情况,可以通过控制其最大的容量来解决:

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G
AI 代码解读

Like other tablespaces it never shrinks back (though it is truncated on restart). The huge temporary table can fill the disk and hang MySQL (bug opened). One way to fix that is to set the maximum size of ibtmp1 file: innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G

(3)内部临时InnoDB表同样共享常规的InnoDB表的限制,如行或列的最大数量限制,超过最大值后,会返回Row size too large” or “Too many columns”的错误,遇到此种情况,可以将默认临时表引擎改回MyISAM

Like other InnoDB tables it has all the InnoDB limitations, i.e., InnoDB row or column limits. If it exceeds these, it will return “Row size too large” or “Too many columns” errors. The workaround is to set internal_tmp_disk_storage_engine to MYISAM.

2、当所有的临时表都改成InnoDB引擎后,会增加引擎的负载,影响到其他的查询。例如:当所有的表都放入buffer_pool中,且临时表都不是InnoDB引擎,那么不会对InnoDB的内存占用造成任何影响,但是临时表改成InnoDB引擎后,会和普通InnoDB表一样占用InnoDB_buffer_pool的空间,而且可能因为临时表空间占用过大挤出真正的热数据,让某些高频查询变慢

When all temp tables go to InnoDB, it may increase the total engine load as well as affect other queries. For example, if originally all datasets fit into buffer_pool and temporary tables were created outside of the InnoDB, it will not affect the* InnoDB* memory footprint. Now, if a huge temporary table is created as an InnoDB table it will use innodb_buffer_pool and may “evict” the existing pages so that other queries may perform slower.

Conclusion 结论

内部InnoDB临时表(可能仅仅因为是SELECT查询导致)被保存在InnoDB的ibtmp文件中,在大部分情况下,会加速临时表或者查询的速度,但是会影响到原本InnoDB内存的占用情况和原本临时表处理的逻辑,如果在某种情况确实需要规避的话,可以尝试将临时表的引擎改回MyISAM:

set global internal_tmp_disk_storage_engine=MYISAM
AI 代码解读

Beware of the new change in MySQL 5.7, the internal temporary tables (those that are created for selects when a temporary table is needed) are stored in InnoDB ibtmp file. In most cases this is faster. However, it can change the original behavior. If needed, you can switch the creation of internal temp tables back to MyISAM: set globalinternal_tmp_disk_storage_engine=MYISAM.

这个案例要求我们要对MySQL 5.7的特性要有所注意和了解。


原文发布时间为:2018-03-29

本文作者:张锐志

本文来自云栖社区合作伙伴“老叶茶馆”,了解相关信息可以关注“老叶茶馆”微信公众号

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
73529
分享
相关文章
轻松入门MySQL:优化复杂查询,使用临时表简化数据库查询流程(13)
轻松入门MySQL:优化复杂查询,使用临时表简化数据库查询流程(13)
270 0
解密MySQL中的临时表:探究临时表的神奇用途
解密MySQL中的临时表:探究临时表的神奇用途
777 3
mysql造数据占用临时表空间
【5月更文挑战第20天】MySQL在处理复杂查询时可能使用临时表,可能导致性能下降。临时表用于排序、分组和连接操作。常见问题包括内存限制、未优化的查询、数据类型不当和临时表清理。避免过度占用的策略包括优化查询、调整系统参数、优化数据类型和事务管理。使用并行查询、分区表和监控工具也能帮助管理临时表空间。通过智能问答工具如通义灵码,可实时续写SQL和获取优化建议。注意监控`Created_tmp_tables`和`Created_tmp_disk_tables`以了解临时表使用状况。
584 5
Mysql的临时表
MySQL中的临时表是一种特殊类型的表,它仅在当前数据库会话中存在,并在会话结束时自动被删除。临时表的作用是存储临时数据,通常用于复杂的查询、数据处理或临时存储计算结果。
136 0
认识MySQL数据库中的临时表
认识MySQL数据库中的临时表。
143 5
认识MySQL数据库中的临时表
认识MySQL数据库中的临时表。
85 4
MYSQL基础知识之【临时表】
MYSQL基础知识之【临时表】
89 0
MySQL的临时表以及视图与存储过程、触发器等功能概念详细解释说明以及它们的使用方法举例?
MySQL的临时表以及视图与存储过程、触发器等功能概念详细解释说明以及它们的使用方法举例?
MySQL的临时表--永远没有结束的故事
如果您曾经不得不处理与临时表相关的性能和/或磁盘空间问题,我打赌您最终会发现自己很困惑。根据临时表的类型、设置和所使用的MySQL版本,可能会有很多情况。
189 0
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等