MYSQL 的临时表崩了

2023年 12月 1日 64.6k 0

唯品会崩了,语雀崩; 语雀崩了,阿里云崩,阿里云崩了,阿里崩; 阿里崩了,滴滴崩! 

在MySQL运行中日志报错,/DB/mysql8.0.34/sharkdb/data3038/tmp/#sql_18cdb_24' is full" 。

一、可能有下面几种情况:
1、在SQL中执行group by、order by、distinct、union、多表update、子查询、多表JOIN等情况下,可能需要生成内部临时表,当内部临时表超过tmp-table-size时,就会产生磁盘临时表。
2、接上,若查询包含BLOB、TEXT类型字段时,MySQL会直接使用磁盘临时表。
3、云数据库购买的磁盘空间,是包括数据库文件、日志文件(binlog、relay log、error log等)、临时文件&临时表(关注 Created_tmp_disk_tables、Created_tmp_tables、Binlog_cache_disk_use、Binlog_stmt_cache_disk_use等指标)所消耗占用的磁盘空间。
4、发生table...is full报错,说明可能生成磁盘临时表太多,超过云数据库购买的空间限制。

二、解决办法可以有:
1、关注slow query log,或者查看processlist,及时发现需要用到临时文件、临时表的SQL,尽快优化。
2、调高 tmp_table_size 参数值来调高内存临时表的上限。
3、调高参数loose_rds_max_tmp_disk_space值,可设置为当前空闲空间的80%(阿里云RDS专属参数)。
4、优化表DDL设计,尽量避免使用BLOB、TEXT类型字段,并且在SQL中减少对这些大字段的访问。
5、优化查询逻辑,避免使用UNION或者需要中间数据集的子查询等SQL。

一般来说在MySQL在运行中有很多的cache来支持相关的语句执行的工作,临时表在MySQL 中有重要的作用,如

tmp_table_size
max_heap_table_size
max_tmp_tables

这些参数制定在内存中存储的临时表的最大的大小,

tmp_table_size 直接制定在内存中存储的临时表的最大值, max_heap_table_size 指定在heap存储引擎的最大临时表的大小,max_tmp_tables 用来指定一个线程可以创建的最大的临时表的数量。
而上面这些参数,在设置不足的情况下,就可能发生上面的问题,尤其在MySQL中执行一些大SQL 和 过度使用MySQL 将其当做OLAP的应用场景使用的情况下,会容易发生上面的错误。

一般简单的情况下,可以通过调高上面的参数来,临时解决问题。一般来说在8  -16MB,不建议MySQL数据库超过这个值,

基本触发tmp_table_size 产生的情况为语句中有group by ,order by 等语句导致数据需要进行收集后的排序导致的,使用tmp_table.

tmp_table_size = 8 * 1024 * 1024;

另外再进行语句操作中sort_buffer_size 是另一个控制语句中数据排序操作的缓冲区的设置,设置较大的缓冲区有助于排序的操作。当然需要注意在内存不足的时候,这些设置会导致操作在磁盘上进行。

[mysqld]
tmp_table_size = 16M
sort_buffer_size = 2M

这里在调整了tmp_table_size 参数后,将原有数据库的参数调整为现有参数的3倍后,问题解决,同时也调整了一些其他的参数。

这里对这些参数进行一个回顾和在学习
1 tmp_table_size :参数设置主要负责临时表在内存中最大的写入的大小,与这个参数有关的MySQL的动作有 创建临时表,如 create temporary table , 或MySQL 根据数据处理中的需求,自助创建临时表,

   这个参数在MySQL 8.028 开始有了改变,在tmp_table_size 达到使用的限制的时候,MySQL会自动将内存中的内部临时表转换为innodb磁盘上的内部临时表,这个参数限制内存的使用,主要的目的是防止个别查询过多消耗temptable的资源。

2 sort_buffer_size :sort_buffer_size 主要是在针对SQL运算中的无法通过查询优化,或索引进行改进的情况下,数据库针对数据排序进行的另一种优化的方式。

3  max_heap_table_size : 在使用heap内存数据存储引擎的时候,会对创建的临时表进行中间结果的存储,这里heap的存储部分会通过max_heap_table_size来进行限制,    这里产生临时表的过程主要是ORDER BY ,GROUP BY 等语句会产生相关的需求,另外再表连接中也可能会使用,另外还有没有索引的情况下,也有可能会使用。

4 innodb_disable_sort_file_cache: 这个参数是针对排序文件是否使用文件来进行限制,如果设置为 1 将禁止使用缓存文件,这里就只能使用缓存而当缓存不足,就无法创建磁盘文件,所以产生上面的错误也有可能是这个参数被设置为1 。

所以在遇到上面的错误的情况下,可以针对这四个参数进行检查,将相关的设置进行变动,常识解决遇到SQL 执行报错临时文件无法生成的问题。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论