当前位置:Gxlcms > mysql > 关于JDBC与MySQL临时表空间的深入解析

关于JDBC与MySQL临时表空间的深入解析

时间:2021-07-01 10:21:17 帮助过:38人阅读

背景

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,相信大家在开发中经常会遇到相关的需求,下面本文将给大家详细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();
   }
  }
 }
}

总结

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

您可能感兴趣的文章:

  • MySQL中临时表的基本创建与使用教程
  • MySQL中关于临时表的一些基本使用方法
  • MySQL两种临时表的用法详解
  • MySQL使用临时表加速查询的方法
  • 解析Mysql临时表及特点
  • 浅谈MySQL临时表与派生表
  • 深度解析MySQL 5.7之临时表空间
  • mysql复制中临时表的运用技巧
  • MySQL中Update、select联用操作单表、多表,及视图与临时表的区别
  • MySQL问答系列之什么情况下会用到临时表
  • MySQL 5.7临时表空间如何玩才能不掉坑里详解
  • mysql临时表用法分析【查询结果可存在临时表中】

人气教程排行