mysql 临时表空间大小_JDBC与MySQL临时表空间的分析

文章来源：爱可生云数据库

作者：秦沛、胡呈清

背景

应用 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，不知道是否与隐藏报错有关，接下来进行测试：

发现以下现象：

加参数 useCursorFetch=true时，做同样的查询确实不会报错

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

不加参数 useCursorFetch=true时，做同样的查询则会报错

结论正常情况下，sql 执行过程中临时表大小达到 ibtmp 上限后会报错；

当JDBC设置 useCursorFetch=true，sql 执行过程中临时表大小达到 ibtmp