背景

应用 JDBC 连接参数采用 useCursorFetch=true，查询结果集存放在 mysqld 临时表空间中，导致ibtmp1 文件大小暴增到90多G，耗尽服务器磁盘空间。为了限制临时表空间的大小，设置了：

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G

问题描述

在限制了临时表空间后，当应用仍按以前的方式访问时，ibtmp1文件达到2G后，程序一直等待直到超时断开连接。 SHOW PROCESSLIST显示程序的连接线程为sleep状态，state和info信息为空。 这个对应用开发来说不太友好，程序等待超时之后要分析原因也缺少提示信息。

问题分析过程

为了分析问题，我们进行了以下测试

测试环境：

  • mysql：5.7.16
  • java：1.8u162
  • jdbc 驱动：5.1.36
  • OS：Red Hat 6.4

1. 手工模拟临时表超过最大限制的场景

模拟以下环境：

  • ibtmp1:12M:autoextend:max:30M
  • 将一张 500万行的 sbtest 表的 k 字段索引删除

运行一条 group by 的查询，产生的临时表大小超过限制后，会直接报错：

select sum(k) from sbtest1 group by k; ERROR 1114 (HY000): The table '/tmp/#sql_60f1_0' is full

2. 检查驱动对 MySQL 的设置

我们上一步看到，SQL手工执行会返回错误，但是 jdbc 不返回错误，导致连接一直 sleep，怀疑是 mysql 驱动做了特殊设置，驱动连接 mysql，通过 general_log 查看做了哪些设置。未发现做特殊设置。

3. 测试 JDBC 连接

问题的背景中有对JDBC做特殊配置：useCursorFetch=true，不知道是否与隐藏报错有关，接下来进行测试：




怎么读取mysql临时表 mysql查看临时表空间大小_sql 临时表


发现以下现象：

  • 加参数 useCursorFetch=true时，做同样的查询确实不会报错
  • 这个参数是为了防止返回结果集过大而采用分段读取的方式。即程序下发一个 sql 给 mysql 后，会等 mysql 可以读结果的反馈，由于 mysql 在执行sql时，返回结果达到 ibtmp 上限后报错，但没有关闭该线程，该线程处理 sleep 状态，程序得不到反馈，会一直等，没有报错。如果 kill 这个线程，程序则会报错。
  • 不加参数 useCursorFetch=true时，做同样的查询则会报错


怎么读取mysql临时表 mysql查看临时表空间大小_table表头上下固定左右不固定_02


结论

  1. 正常情况下，sql 执行过程中临时表大小达到 ibtmp 上限后会报错；
  2. 当JDBC设置 useCursorFetch=true，sql 执行过程中临时表大小达到 ibtmp 上限后不会报错。

解决方案

  • 进一步了解到使用 useCursorFetch=true 是为了防止查询结果集过大撑爆 jvm；
  • 但是使用 useCursorFetch=true 又会导致普通查询也生成临时表，造成临时表空间过大的问题；
  • 临时表空间过大的解决方案是限制 ibtmp1 的大小，然而 useCursorFetch=true 又导致JDBC不返回错误。
  • 所以需要使用其它方法来达到相同的效果，且 sql 报错后程序也要相应的报错。除了 useCursorFetch=true 这种段读取的方式外，还可以使用流读取的方式。流读取程序详见附件部分。
  • 报错对比
  • 段读取方式，sql 报错后，程序不报错
  • 流读取方式，sql 报错后，程序会报错
  • 内存占用对比

这里对比了普通读取、段读取、流读取三种方式，初始内存占用 28M 左右：

  • 普通读取后，内存占用 100M 多
  • 段读取后，内存占用 60M 左右
  • 流读取后，内存占用 60M 左右

补充知识点

MySQL共享临时表空间知识点

MySQL 5.7在 temporary tablespace上做了改进，已经实现将 temporary tablespace 从 ibdata(共享表空间文件)中分离。并且可以重启重置大小，避免出现像以前 ibdata 过大难以释放的问题。

其参数为：innodb_temp_data_file_path

1. 表现

MySQL启动时 datadir 下会创建一个 ibtmp1 文件，初始大小为 12M，默认值下会无限扩展：

通常来说，查询导致的临时表(如group by)如果超出 tmp_table_size、max_heap_table_size 大小限制则创建 innodb 磁盘临时表(MySQL5.7默认临时表引擎为 innodb)，存放在共享临时表空间；

如果某个操作创建了一个大小为100 M的临时表，则临时表空间数据文件会扩展到 100M大小以满足临时表的需要。当删除临时表时，释放的空间可以重新用于新的临时表，但 ibtmp1 文件保持扩展大小。

2. 查询视图

可查询共享临时表空间的使用情况：

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE,MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'G*************************** 1. row *************************** FILE_NAME: /data/mysql5722/data/ibtmp1TABLESPACE_NAME: innodb_temporary ENGINE: InnoDB INITIAL_SIZE: 12582912 TotalSizeBytes: 31457280 DATA_FREE: 27262976 MAXIMUM_SIZE: 314572801 row in set (0.00 sec)

3. 回收方式

重启 MySQL 才能回收

4. 限制大小

为防止临时数据文件变得过大，可以配置该 innodb_temp_data_file_path (需重启生效)选项以指定最大文件大小，当数据文件达到最大大小时，查询将返回错误：

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G

5. 临时表空间与 tmpdir 对比

共享临时表空间用于存储非压缩InnoDB临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等数据；

tmpdir 用于存放指定临时文件(temporary files)和临时表(temporary tables)，与共享临时表空间不同的是，tmpdir存储的是compressed InnoDB temporary tables。

可通过如下语句测试：

CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED;CREATE TEMPORARY TABLE uncompress_table (id int, name char(255)) ;

附件

SimpleExample.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;import java.util.concurrent.CountDownLatch;import java.util.concurrent.atomic.AtomicLong;public class SimpleExample { public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Properties props = new Properties(); props.setProperty("user