關(guān)于JDBC與MySQL臨時(shí)表空間的深入解析

背景

臨時(shí)表空間用來(lái)管理數(shù)據(jù)庫(kù)排序操作以及用于存儲(chǔ)臨時(shí)表、中間排序結(jié)果等臨時(shí)對(duì)象，相信大家在開發(fā)中經(jīng)常會(huì)遇到相關(guān)的需求，下面本文將給大家詳細(xì)JDBC與MySQL臨時(shí)表空間的相關(guān)內(nèi)容，分享出來(lái)供大家參考學(xué)習(xí)，下面話不多說(shuō)了，來(lái)一起看看詳細(xì)的介紹吧

應(yīng)用 JDBC 連接參數(shù)采用 useCursorFetch=true，查詢結(jié)果集存放在 mysqld 臨時(shí)表空間中，導(dǎo)致ibtmp1 文件大小暴增到90多G，耗盡服務(wù)器磁盤空間。為了限制臨時(shí)表空間的大小，設(shè)置了：

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

問(wèn)題描述

在限制了臨時(shí)表空間后，當(dāng)應(yīng)用仍按以前的方式訪問(wèn)時(shí)，ibtmp1文件達(dá)到2G后，程序一直等待直到超時(shí)斷開連接。 SHOW PROCESSLIST顯示程序的連接線程為sleep狀態(tài)，state和info信息為空。 這個(gè)對(duì)應(yīng)用開發(fā)來(lái)說(shuō)不太友好，程序等待超時(shí)之后要分析原因也缺少提示信息。

問(wèn)題分析過(guò)程

為了分析問(wèn)題，我們進(jìn)行了以下測(cè)試

測(cè)試環(huán)境：

mysql：5.7.16

java：1.8u162

jdbc 驅(qū)動(dòng)：5.1.36

OS：Red Hat 6.4

1.手工模擬臨時(shí)表超過(guò)最大限制的場(chǎng)景

模擬以下環(huán)境：

ibtmp1:12M:autoextend:max:30M

將一張 500萬(wàn)行的 sbtest 表的 k 字段索引刪除

運(yùn)行一條 group by 的查詢，產(chǎn)生的臨時(shí)表大小超過(guò)限制后，會(huì)直接報(bào)錯(cuò)：

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

2.檢查驅(qū)動(dòng)對(duì) mysql 的設(shè)置

我們上一步看到，sql 手工執(zhí)行會(huì)返回錯(cuò)誤，但是 jdbc 不返回錯(cuò)誤，導(dǎo)致連接一直 sleep，懷疑是 mysql 驅(qū)動(dòng)做了特殊設(shè)置，驅(qū)動(dòng)連接 mysql，通過(guò) general_log 查看做了哪些設(shè)置。未發(fā)現(xiàn)做特殊設(shè)置。

3.測(cè)試 JDBC 連接

問(wèn)題的背景中有對(duì)JDBC做特殊配置：useCursorFetch=true，不知道是否與隱藏報(bào)錯(cuò)有關(guān)，接下來(lái)進(jìn)行測(cè)試：

發(fā)現(xiàn)以下現(xiàn)象：

·加參數(shù) useCursorFetch=true時(shí)，做同樣的查詢確實(shí)不會(huì)報(bào)錯(cuò)

這個(gè)參數(shù)是為了防止返回結(jié)果集過(guò)大而采用分段讀取的方式。即程序下發(fā)一個(gè) sql 給 mysql 后，會(huì)等 mysql 可以讀結(jié)果的反饋，由于 mysql 在執(zhí)行sql時(shí)，返回結(jié)果達(dá)到 ibtmp 上限后報(bào)錯(cuò)，但沒有關(guān)閉該線程，該線程處理 sleep 狀態(tài)，程序得不到反饋，會(huì)一直等，沒有報(bào)錯(cuò)。如果 kill 這個(gè)線程，程序則會(huì)報(bào)錯(cuò)。

·不加參數(shù) useCursorFetch=true時(shí)，做同樣的查詢則會(huì)報(bào)錯(cuò)

結(jié)論

1.正常情況下，sql 執(zhí)行過(guò)程中臨時(shí)表大小達(dá)到 ibtmp 上限后會(huì)報(bào)錯(cuò)；

2.當(dāng)JDBC設(shè)置 useCursorFetch=true，sql 執(zhí)行過(guò)程中臨時(shí)表大小達(dá)到 ibtmp 上限后不會(huì)報(bào)錯(cuò)。

解決方案

進(jìn)一步了解到使用 useCursorFetch=true 是為了防止查詢結(jié)果集過(guò)大撐爆 jvm；

但是使用 useCursorFetch=true 又會(huì)導(dǎo)致普通查詢也生成臨時(shí)表，造成臨時(shí)表空間過(guò)大的問(wèn)題；

臨時(shí)表空間過(guò)大的解決方案是限制 ibtmp1 的大小，然而 useCursorFetch=true 又導(dǎo)致JDBC不返回錯(cuò)誤。

所以需要使用其它方法來(lái)達(dá)到相同的效果，且 sql 報(bào)錯(cuò)后程序也要相應(yīng)的報(bào)錯(cuò)。除了 useCursorFetch=true 這種段讀取的方式外，還可以使用流讀取的方式。流讀取程序詳見附件部分。

·報(bào)錯(cuò)對(duì)比

·段讀取方式，sql 報(bào)錯(cuò)后，程序不報(bào)錯(cuò)

·流讀取方式，sql 報(bào)錯(cuò)后，程序會(huì)報(bào)錯(cuò)

·內(nèi)存占用對(duì)比

這里對(duì)比了普通讀取、段讀取、流讀取三種方式，初始內(nèi)存占用 28M 左右：

·普通讀取后，內(nèi)存占用 100M 多

·段讀取后，內(nèi)存占用 60M 左右

·流讀取后，內(nèi)存占用 60M 左右

補(bǔ)充知識(shí)點(diǎn)

MySQL共享臨時(shí)表空間知識(shí)點(diǎn)

MySQL 5.7在 temporary tablespace上做了改進(jìn)，已經(jīng)實(shí)現(xiàn)將 temporary tablespace 從 ibdata（共享表空間文件）中分離。并且可以重啟重置大小，避免出現(xiàn)像以前 ibdata 過(guò)大難以釋放的問(wèn)題。
其參數(shù)為：innodb_temp_data_file_path

1.表現(xiàn)

MySQL啟動(dòng)時(shí) datadir 下會(huì)創(chuàng)建一個(gè) ibtmp1 文件，初始大小為 12M，默認(rèn)值下會(huì)無(wú)限擴(kuò)展：

通常來(lái)說(shuō)，查詢導(dǎo)致的臨時(shí)表（如group by）如果超出 tmp_table_size、max_heap_table_size 大小限制則創(chuàng)建 innodb 磁盤臨時(shí)表（MySQL5.7默認(rèn)臨時(shí)表引擎為 innodb），存放在共享臨時(shí)表空間；

如果某個(gè)操作創(chuàng)建了一個(gè)大小為100 M的臨時(shí)表，則臨時(shí)表空間數(shù)據(jù)文件會(huì)擴(kuò)展到 100M大小以滿足臨時(shí)表的需要。當(dāng)刪除臨時(shí)表時(shí)，釋放的空間可以重新用于新的臨時(shí)表，但 ibtmp1 文件保持?jǐn)U展大小。

2.查詢視圖

可查詢共享臨時(shí)表空間的使用情況：

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.限制大小

為防止臨時(shí)數(shù)據(jù)文件變得過(guò)大，可以配置該 innodb_temp_data_file_path （需重啟生效）選項(xiàng)以指定最大文件大小，當(dāng)數(shù)據(jù)文件達(dá)到最大大小時(shí)，查詢將返回錯(cuò)誤：

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

5. 臨時(shí)表空間與 tmpdir 對(duì)比

共享臨時(shí)表空間用于存儲(chǔ)非壓縮InnoDB臨時(shí)表(non-compressed InnoDB temporary tables)、關(guān)系對(duì)象(related objects)、回滾段(rollback segment)等數(shù)據(jù)；

tmpdir 用于存放指定臨時(shí)文件(temporary files)和臨時(shí)表(temporary tables)，與共享臨時(shí)表空間不同的是，tmpdir存儲(chǔ)的是compressed InnoDB temporary tables。

可通過(guò)如下語(yǔ)句測(cè)試：

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=falseuseCursorFetch=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();
   }
  }
 }
}

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。