案例 2：JDBC 与 MySQL 临时表空间的分析

作者：秦沛 胡呈清 1 背景 应用 JDBC 连接参数采用useCursorFetch=true，查询结果集存放在 mysqld 临时表空间中，导致ibtmp1 文件大小暴增到90 多G，耗尽服务器磁盘空间。为了限制临时表空间的大小，设置了： innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G 2 问题描述 在限制了临时表空间后，当应用仍按以前的方式访问时，ibtmp1 文件达到2G 后，程序一直等待直到超时 断开连接。 SHOW PROCESSLIST 显示程序的连接线程为sleep 状态，state 和info 信息为空。 这个对应 用开发来说不太友好，程序等待超时之后要分析原因也缺少提示信息。 3 问题分析过程 为了分析问题，我们进行了以下测试 测试环境：

1. mysql：5.7.16
2. java：1.8u162
3. jdbc 驱动：5.1.36
4. OS：Red Hat 6.4 3.1 手工模拟临时表超过最大限制的场景 模拟以下环境：
5. ibtmp1:12M:autoextend:max:30M
6. 将一张 500 万行的 sbtest 表的 k 字段索引删除 运行一条 group by 的查询，产生的临时表大小超过限制后，会直接报错： select sum(k) from sbtest1 group by k; ERROR 1114 (HY000): The table ‘/tmp/#sql_60f1_0’ is full 3.2 查驱动对MySQL 的设置 我们上一步看到，SQL 手工执行会返回错误，但是jdbc 不返回错误，导致连接一直 sleep，怀疑是 mysql 驱动做了特殊设置，驱动连接mysql，通过general_log 查看做了哪些设置。未发现做特殊设置。

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

1. 加参数 useCursorFetch=true 时，做同样的查询确实不会报错 这个参数是为了防止返回结果集过大而采用分段读取的方式。即程序下发一个sql 给mysql 后，会等 mysql 可以读结果的反馈，由于mysql 在执行sql 时，返回结果达到ibtmp 上限后报错，但没有关闭该 线程，该线程处理sleep 状态，程序得不到反馈，会一直等，没有报错。如果kill 这个线程，程序则会 报错。
2. 不加参数 useCursorFetch=true 时，做同样的查询则会报错 4 结论 正常情况下，sql 执行过程中临时表大小达到ibtmp 上限后会报错； 当JDBC 设置useCursorFetch=true，sql 执行过程中临时表大小达到ibtmp 上限后不会报错。 5 解决方案
3. 进一步了解到使用useCursorFetch=true 是为了防止查询结果集过大撑爆jvm；
4. 但是使用 useCursorFetch=true 又会导致普通查询也生成临时表，造成临时表空间过大的问题；
5. 临时表空间过大的解决方案是限制ibtmp1 的大小，然而useCursorFetch=true 又导致JDBC 不返回 错误。
6. 所以需要使用其它方法来达到相同的效果，且sql 报错后程序也要相应的报错。除了 useCursorFetch=true 这种段读取的方式外，还可以使用流读取的方式。流读取程序详见附件部分。 报错对比
7. 段读取方式，sql 报错后，程序不报错
8. 流读取方式，sql 报错后，程序会报错

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

1. 普通读取后，内存占用100M 多
2. 段读取后，内存占用60M 左右
3. 流读取后，内存占用60M 左右 6 知识补充点 MySQL 共享临时表空间知识点 MySQL 5.7 在temporary tablespace 上做了改进，已经实现将temporary tablespace 从ibdata（共享 表空间文件）中分离。并且可以重启重置大小，避免出现像以前ibdata 过大难以释放的问题。 其参数为：innodb_temp_data_file_path 6.1 表现 MySQL 启动时datadir 下会创建一个ibtmp1 文件，初始大小为12M，默认值下会无限扩展： 通常来说，查询导致的临时表（如group by）如果超出 tmp_table_size、max_heap_table_size 大小 限制则创建 innodb 磁盘临时表（MySQL5.7 默认临时表引擎为innodb），存放在共享临时表空间； 如果某个操作创建了一个大小为100 M 的临时表，则临时表空间数据文件会扩展到 100M 大小以满足临时 表的需要。当删除临时表时，释放的空间可以重新用于新的临时表，但 ibtmp1 文件保持扩展大小。 6.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) 6.3 回收方式 重启MySQL 才能回收

6.4 限制大小 为防止临时数据文件变得过大，可以配置该innodb_temp_data_file_path（需重启生效）选项以指定最 大文件大小，当数据文件达到最大大小时，查询将返回错误： innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G 6.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)) ; 7 附件 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&u seCursorFetch=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(); } } } }