关于JDBC与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时，做同样的查询则会报错

结论

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/ibtmp1
tablespace_name: innodb_temporary
      engine: innodb
    initial_size: 12582912
   totalsizebytes: 31457280
    data_free: 27262976
  maximum_size: 31457280
1 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", "root");
  props.setproperty("password", "root");
  simpleexample engine = new simpleexample();
//  engine.execute(props,"jdbc:mysql://10.186.24.31:3336/hucq?usessl=false");
  engine.execute(props,"jdbc:mysql://10.186.24.31:3336/hucq?usessl=false&usecursorfetch=true");
 }
 final atomiclong tmal = new atomiclong();
 final string tablename="test";
 public void execute(properties props,string url) {
  countdownlatch cdl = new countdownlatch(1);
  long start = system.currenttimemillis();
  for (int i = 0; i < 1; i++) {
   testthread insertthread = new testthread(props,cdl, url);
   thread t = new thread(insertthread);
   t.start();
   system.out.println("test start");
  }
  try {
   cdl.await();
   long end = system.currenttimemillis();
   system.out.println("test end,total cost:" + (end-start) + "ms");
  } catch (exception e) {
  }
 }
 
 class testthread implements runnable {
  properties props;
  private countdownlatch countdownlatch;
  string url;
  public testthread(properties props,countdownlatch cdl,string url) {
   this.props = props;
   this.countdownlatch = cdl;
   this.url = url;
  }
  public void run() {
   connection connection = null;
   preparedstatement ps = null;
   statement st = null;
   long start = system.currenttimemillis();
   try {
    connection = drivermanager.getconnection(url,props);
    connection.setautocommit(false);
    st = connection.createstatement();
     
    //st.setfetchsize(500);
    st.setfetchsize(integer.min_value); //仅修改此处即可
     
    resultset rstmp;
     
    st.executequery("select sum(k) from sbtest1 group by k");
    rstmp = st.getresultset();
    while(rstmp.next()){
      
    }
   } catch (exception e) {
    system.out.println(system.currenttimemillis() - start);
    system.out.println(new java.util.date().tostring());
    e.printstacktrace();
   } finally {
    if (ps != null)
     try {
      ps.close();
     } catch (sqlexception e1) {
      e1.printstacktrace();
     }
    if (connection != null)
     try {
      connection.close();
     } catch (sqlexception e1) {
      e1.printstacktrace();
     }
    this.countdownlatch.countdown();
   }
  }
 }
}

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。