问题描述
现场同事反馈应用日志中有如下报错The table '/tmp/#sql2b742_48637_738' is full，已影响功能查询。
......
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [ SELECT group_level_2, group_level_1, group_level_3, org_code,binary_index,SUM(col_7) as col_7,SUM(col_10) as col_10,SUM(col_9) as col_9,SUM(col_12) as col_12,SUM(col_5) as col_5,SUM(col_6) as col_6,SUM(col_1) as col_1,SUM(col_11) as col_11,SUM(col_0) as col_0,SUM(col_13) as col_13,SUM(col_2) as col_2,SUM(col_4) as col_4,SUM(col_3) as col_3,SUM(col_8) as col_8 FROM ( SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11, ROUND(SUM( alias_BJ611_10.uxsino_c20) , 2) AS col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6, ROUND(SUM( alias_BJ611_10.uxsino_c01_1) , 2) AS col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13, ROUND(SUM( alias_BJ611_10.uxsino_c01_2) , 2) AS col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4, ROUND(SUM( alias_BJ611_10.uxsino_c11_2) , 2) AS col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2, ROUND(SUM( alias_BJ611_10.uxsino_c05_1) , 2) AS col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12, ROUND(SUM( alias_BJ611_10.uxsino_c05_2) , 2) AS col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5, ROUND(SUM( alias_BJ611_10.uxsino_c06_1) , 2) AS col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , ROUND(SUM( alias_BJ611_10.uxsino_c06_2) , 2) AS col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3, ROUND(SUM( alias_BJ611_10.uxsino_c07_1) , 2) AS col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10, ROUND(SUM( alias_BJ611_10.uxsino_c07_2) , 2) AS col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7, ROUND(SUM( alias_BJ611_10.uxsino_c09_1) , 2) AS col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1, ROUND(SUM( alias_BJ611_10.uxsino_c09_2) , 2) AS col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9, ROUND(SUM( alias_BJ611_10.uxsino_c08_1) , 2) AS col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0,0 as col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 UNION ALL SELECT ( LEFT(alias_BJ611_10.org_code, 4) ) AS group_level_2 , ( LEFT(alias_BJ611_10.org_code, 2) ) AS group_level_1 , ( LEFT(alias_BJ611_10.org_code, 6) ) AS group_level_3 , LEFT(alias_BJ611_10.org_code, 6) AS org_code , 111 AS binary_index , 0 as col_7,0 as col_10,0 as col_9,0 as col_12,0 as col_5,0 as col_6,0 as col_1,0 as col_11,0 as col_0, ROUND(SUM( alias_BJ611_10.uxsino_cbj01_1) , 2) AS col_13,0 as col_2,0 as col_4,0 as col_3,0 as col_8 FROM report_bj611_10_2023 AS alias_BJ611_10 WHERE 1 = 1 AND alias_BJ611_10.report_period = '2023' AND alias_BJ611_10.object_status = 1 AND alias_BJ611_10.report_status = 2 AND alias_BJ611_10.org_code like '11%' GROUP BY group_level_2,group_level_1,group_level_3 ) AS sub WHERE 1 = 1 AND sub.group_level_2 IS NOT NULL AND sub.group_level_1 IS NOT NULL AND sub.group_level_3 IS NOT NULL GROUP BY group_level_2, group_level_1, group_level_3, org_code,binary_index]; SQL state [HY000]; error code [1114]; The table '/tmp/#sql2b742_48637_738' is full; nested exception is java.sql.SQLException: The table '/tmp/#sql2b742_48637_738' is full
......导致报错的原因
导致报错的原因有：
- 磁盘空间满
- 临时表内存换成参数小
- sql中的分组排序导致内存缓存空间占满
分析过程
查数据库版本
[root@master mysql]# mysql -V
mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)判断磁盘空间是否满
保持有 20%的存储空间，发现磁盘空间还没满，使用率仅9%，排除磁盘空间不足导致临时文件写入失败报错。
[root@master mysql]# df -h
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/nlas-root 79G 4.0G 75G 6% /
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 1.1G 31G 4% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/vda1 1014M 162M 853M 16% /boot
/dev/mapper/nlas-home 19G 33M 19G 1% /home
tmpfs 6.3G 0 6.3G 0% /run/user/0
/dev/mapper/vg0-lv0 591G 49G 513G 9% /data
192.167.52.108:/192.158.178.66 2.0T 932G 1.1T 47% /nfs查看内存使用情况
内存还有剩余
[root@master mysql]# free -g
total used free shared buff/cache available
Mem: 62 35 0 1 26 25
Swap: 1 0 1查看临时表内存参数设置
max_heap_table_size
max_heap_table_size 参数会影响到临时表的内存缓存大小 。
max_heap_table_size 是MEMORY内存引擎的表大小 ， 因为临时表也是属于内存表所以也会受此参数的限制 所以如果要增加 tmp_table_size 的大小 也需要同时增加 max_heap_table_size 的大小。
mysql> show variables like '%max_heap_table_size%';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| max_heap_table_size | 2147483648 |
+---------------------+------------+
2 rows in set (0.01 sec)
默认值16777216 单位字节即16M，当前已设置成 2147483648/1024/1024/1024=2Gtmp_table_size
在MySQL 8.0.28中， tmp_table_size 定义了TempTable存储引擎创建的任何内存内部临时表的最大大小，每个线程都要分配。当达到 tmp_table_size 限制时，MySQL会自动将内存内部临时表转换为 InnoDB 磁盘内部临时表。默认的 tmp_table_size 设置为16777216字节（16 MiB）。
tmp_table_size 限制旨在防止单个查询消耗过多的全局TempTable资源，这可能会影响需要TempTable资源的并发查询的性能。全局TempTable资源由 temptable_max_ram 和 temptable_max_mmap 设置控制。
如果 tmp_table_size 限制小于 temptable_max_ram 限制，则内存中的临时表不可能包含比 tmp_table_size 限制所允许的更多的数据。如果 tmp_table_size 限制大于 temptable_max_ram 和 temptable_max_mmap 限制之和，则内存中的临时表不可能包含大于 temptable_max_ram 和 temptable_max_mmap 限制之和的内容。
此参数不适用用户创建的MEMORY表。
实际限制由tmp_table_size和max_heap_table_size的值中较小的一个确定，如果内存中的临时表超出限制，MySQL自动将其转换为磁盘上的MyISAM表。如果要执行许多 GROUP BY查询，可以增加tmp_table_size的值（或如有必要，也可以使用max_heap_table_size）。
执行计划中Extra字段包含有“Using temporary” 时会产生临时表。
补充：临时表介绍
MySQL中临时表主要有两类，包括外部临时表和内部临时表。
外部临时表
外部临时表是通过语句create temporary table...创建的临时表，临时表只在本会话有效，会话断开后，临时表数据会自动清理。
内部临时表
内部临时表主要有两类，一类是information_schema中临时表，另一类是会话执行查询时，如果执行计划中包含有“Using temporary”时，会产生临时表。
内部临时表与外部临时表的区别
内部临时表与外部临时表的一个区别在于，看不到内部临时表的表结构定义文件frm。而外部临时表的表定义文件frm，一般是以#sql{进程id}_{线程id}_序列号组成，因此不同会话可以创建同名的临时表。
临时表与普通表的区别
临时表与普通表的主要区别在于是否在实例，会话，或语句结束后，自动清理数据。比如，内部临时表，在一个查询中，如果要存储中间结果集，而查询结束后，临时表就会自动回收，不会影响用户表结构和数据。另外就是，不同会话的临时表可以重名，所有多个会话执行查询时，如果要使用临时表，不会有重名的担忧。5.7引入了临时表空间后，所有临时表都存储在临时表空间(非压缩)中，临时表空间的数据可以复用。临时表并非只支持Innodb引擎，还支持myisam引擎，memory引擎等。因此，临时表看不到实体(idb文件)，但其实不一定是内存表，也可能存储在临时表空间中。
临时表既可以innodb引擎表，也可以是memory引擎表。这里所谓的内存表，是说memory引擎表，通过建表语句create table ...engine=memory，数据全部在内存，表结构通过frm管理，同样的内部的memory引擎表，也是看不到frm文件中，甚至看不到information_schema在磁盘上的目录。在MySQL内部，information_schema里面的临时表就包含两类：innodb引擎的临时表和memory引擎的临时表。比如TABLES表属于memory临时表，而columns,processlist,属于innodb引擎临时表。内存表所有数据都在内存中，在内存中数据结构是一个数组(堆表)，所有数据操作都在内存中完成，对于小数据量场景，速度比较快(不涉及物理IO操作)。但内存毕竟是有限的资源，因此，如果数据量比较大，则不适合用内存表，而是选择用磁盘临时表(innodb引擎)，这种临时表采用B+树存储结构(innodb引擎)，innodb的bufferpool资源是共享的，临时表的数据可能会对bufferpool的热数据有一定的影响，另外，操作可能涉及到物理IO。memory引擎表实际上也是可以创建索引的，包括Btree索引和Hash索引，所以查询速度很快，主要缺陷是内存资源有限。
temptable_max_ram
temptable_max_ram ：根据您的配置，定义 TempTable 存储引擎在开始从内存映射文件分配空间之前或MySQL开始使用 InnoDB 磁盘内部临时表之前可以使用的最大RAM量。默认的 temptable_max_ram 设置为1073741824字节（1GiB）。
mysql> show variables like '%temptable_max_ram%';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| temptable_max_ram | 1073741824 |
+---------------------+------------+
2 rows in set (0.01 sec)temptable_max_mmap
temptable_max_mmap ：在MySQL 8.0.23中引入。定义在MySQL开始使用 InnoDB 磁盘内部临时表之前，TempTable存储引擎允许从内存映射文件分配的最大内存量。默认设置为1073741824字节（1GiB）。该限制旨在解决内存映射文件在临时目录中使用过多空间的风险（ tmpdir ）。 temptable_max_mmap=0 设置禁止从内存映射文件中分配，有效地禁止使用它们，而不管 temptable_use_mmap 设置如何。
mysql> show variables like '%temptable_max_mmap%';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| temptable_max_mmap | 1073741824 |
+---------------------+------------+
2 rows in set (0.01 sec)查看设置
tmp_table_size参数：
默认值 16777216 即16M
最小值 1
最大值 18446744073709551615
单位字节
mysql> show variables like '%tmp_table_size%';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| tmp_table_size | 2147483648 |
+---------------------+------------+
2 rows in set (0.01 sec)
默认值16777216 单位字节即16M，当前已设置成 2147483648/1024/1024/1024=2Gtmp_table_size临时表的内存缓存大小。
临时表是指sql执行时生成临时数据表。
--查看 tmp_table_size
show global variables like 'tmp_table_size';
--设置 tmp_table_size
set global tmp_table_size= 2048; （立即生效重启后失效）
--设置 tmp_table_size 永久生效
MySQL 配置文件 my.cnf 中 mysqld 下添加 tmp_table_size
[mysqld]
tmp_table_size = 2048
service mysqld stop
service mysqld start分析是否需要增加 tmp_table_size
可以通过 Created_tmp_disk_tables 和 Created_tmp_tables 状态来分析是否需要增加 tmp_table_size
查看状态
show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_tables';
Created_tmp_disk_tables : 磁盘临时表的数量
Created_tmp_tables : 内存临时表的数量判断依据：Created_tmp_disk_tables/Created_tmp_tables<5%
参考链接：https://blog.csdn.net/snake1900/article/details/99934494
查看配置文件
--查进程
[root@master mysql]# ps -ef | grep mysql
root 179480 1 0 2023 ? 00:00:00 /bin/sh /data/mysql/bin/mysqld_safe --datadir=/data/mysql/datanew --pid-file=/data/mysq/run/mysqld/mysqld.pid
mysql 179851 179480 19 2023 ? 26-06:04:11 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/mysql/datanew --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/mysqld.log --open-files-limit=65535 --pid-file=/data/mysql/run/mysqld/mysqld.pid --port=3306
root 207101 206071 59 10:05 ? 00:00:07 mysqldump -h192.158.178.81 -u root -P3306 -px xxxxx --skip-lock-tables --add-locks=false --hex-blob --complete-insert -e --quick --skip-tz-utc --no-create-info --skip-comments --databases slave_institution_dev --tables report_bj611_10_2023bak20240204
root 207132 243304 0 10:06 pts/1 00:00:00 grep --color=auto mysql
--查配置文件
[root@master mysql]# cat /etc/my.cnf
[client]
default-character-set = utf8
port=3306
[mysqld]
port=3306
character-set-server=utf8
default-time_zone = '+8:00'
basedir=/data/mysql
datadir=/data/mysql/datanew
lower_case_table_names = 1
max_connections=2000
default-storage-engine=INNODB
#symbolic-links=0
skip-symbolic-links=0
log-error=/data/mysql/log/mysqld.log
pid-file=/data/mysql/run/mysqld/mysqld.pid
expire_logs_days=30
#binlog_expire_logs_seconds=0
max_binlog_size=1G
log_bin=/data/mysql/datanew/master-bin
binlog-format=ROW
server_id=1
user=mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#innodb_buffer_pool_size = 10240M
innodb_buffer_pool_size = 30720M
open_files_limit=65535
slow_query_log=1
long_query_time=5
slow_query_log_file=/data/mysql/mysql-slow.log解决办法
永久性更改临时表相关参数
全局更改
由于是生产环境，先进行全局更改，待申请到重启窗口后按照永久更改的方式修改配置文件后进行重启。
SET GLOBAL tmp_table_size= 256*1024*1024;
SET GLOBAL max_heap_table_size =512*1024*1024;
SET GLOBAL temptable_max_ram=2*1024*1024*1024;
SET GLOBAL temptable_max_mmap=2*1024 1024*1024;永久更改
MySQL 配置文件 my.cnf 中 mysqld 下添加如下参数
vi my.cnf
[mysqld]
tmp_table_size = 256M
max_heap_table_size=512M
temptable_max_ram=2G
temptable_max_mmap=2G
--重启mysql服务
service mysqld stop
service mysqld startsql语句优化
找到问题SQL语句 优化
参考链接：https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
