GreatSQL社区

搜索

chongzh

MySQL 8.0.25 Bug 报"MY-013132 The table ...is full"

chongzh 已有 205 次阅读2024-2-5 09:24 |个人分类:MySQL Bug|系统分类:运维实战

  1. 问题现象描述

环境概述:MySQL 8.0.25;

问题简述:2024/1/31 凌晨3点 应用批量报 The table  ’/data_tmp/mysql_xx….’ is full 。

表的大小9GB,报错SQL语句如下:

SQL执行计划

2.问题处理过程

业务跑批失败的SQL语句,应用手工重新执行。

3. 问题分析

3.1 数据库错误日志:

数据库mysql.err日志发现有相应的报错信息:

错误信息是:tpmdir目录下的临时表 xxx is full

3.2 数据库关于临时表的相关配置信息:

3.3 SQL调用情况

4.问题原因

猜想1:tpmdir目录下临时表xxx is full ,是OS的磁盘空间满了吗?(排除)

tmpdir=/data_tmp 在sql报错期间 可用磁盘充足,故排除。

   猜想2:innodb_temp_data_file_path 临时表限额max超了吗?(排除)

 原因同上,如果限额max超了 MySQL error log 为 [ERROR] [MY-012639],[ERROR] [MY-012640],非 [ERROR] [MY-013132] ,故排除。

猜想3:碰到MySQL 临时表bug了吗?(已复现)

MySQL bug https://bugs.mysql.com/bug.php?id=99100

每个环境的内存消耗可能是不同的,触发条件非常严格,一个temptable内存溢出必须发生在table()->file->ha_update_row().

5. BUG复现

<code style="text-align: left;">会话1:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> show variables like '%temp%';
+-----------------------------+-------------------------------+
| Variable_name               | Value                         |
+-----------------------------+-------------------------------+
| avoid_temporal_upgrade      | OFF                           |
| innodb_temp_data_file_path  | ibtmp1:12M:autoextend:max:20M |
| innodb_temp_tablespaces_dir | ./#innodb_temp/               |
| show_old_temporals          | OFF                           |
| temptable_max_mmap          | 2097152                       |
| temptable_max_ram           | 2097152                       |
| temptable_use_mmap          | ON                            |
+-----------------------------+-------------------------------+
7 rows in set (0.01 sec)

mysql> show create table tt.sbtest1 \G;
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` varchar(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)

mysql> select count(*) from tt.sbtest1;
+----------+
| count(*) |
+----------+
|     5317 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) cnt, pad, c from tt.sbtest1 group by pad order by cnt desc  limit 1,2;
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| cnt | pad                                                         | c                                                                                                                       |
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
|   1 | 74781290517-41121402981-50604677924-34464478849-89102349959 | 42909700340-70078987867-62357124096-35495169193-85675377266-14643719347-30417020186-80900182681-50382374444-66260611196 |
|   1 | 05718751935-89098378275-78210260499-98875938393-07869813478 | 38843067987-81616962226-70977297724-90679933528-19965056701-17197660199-96971885251-66400408314-33753356648-14231252167 |
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

mysql> explain select count(*) cnt, pad, c from tt.sbtest1 group by pad order by cnt desc  limit 1,2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5317 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

会话2:
mysql> select count(*) cnt, pad, c from tt.sbtest1 group by pad order by cnt desc  limit 1,2;
ERROR 1114 (HY000): The table '/data/mysql/mysql3301/tmp/#sql5ac1_a_1' is full

MySQL错误日志
[root@test_greatsql_01 ~]# tail -f /data/mysql/mysql3301/logs/mysql.err
2024-02-04T16:41:50.401950+08:00 10 [ERROR] [MY-013132] [Server] The table '/data/mysql/mysql3301/tmp/#sql5ac1_a_2' is full!
  • 6.问题总结

该SQL语句中含 group by,select count(*),会使MySQL数据库创建临时表。当全局临时表大小超过 temptable_max_mmp值,则使用innodb临时表从disk上分配空间,并将内存中的临时表迁移到disk上。该过程在MySQL8.0.25触发bug https://bugs.mysql.com/bug.php?id=99100,该BUG 在 8.0.27 被 Fixed。

7.优化建议

1> 升级MySQL版本,该BUG Fixed in 8.0.27;

2> 对该SQL进行优化,减少临时表使用大小;

3> 减少该SQL并发,错峰执行;

4> 当SQL执行报错 xxx is full,重跑任务;

5> 调整 internal_tmp_mem_storage_engine=MEMORY,但可能在特殊场景会影响其他SQL的临时表的使用;​



评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 立即注册

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-3-2 23:46 , Processed in 0.013116 second(s), 8 queries , Redis On.
返回顶部