背景
臨時表空間用來管理數據庫排序操作以及用于存儲臨時表、中間排序結果等臨時對象，相信大家在開發中經常會遇到相關的需求，下面本文將給大家詳細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=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();
}
}
}
}
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- Mysql臨時表及分區表區別詳解
- Mysql臨時表原理及創建方法解析
- mysql臨時表用法分析【查詢結果可存在臨時表中】
- MySQL 5.7臨時表空間如何玩才能不掉坑里詳解
- MySQL問答系列之什么情況下會用到臨時表
- MySQL臨時表的簡單用法介紹
- MySQL中Update、select聯用操作單表、多表，及視圖與臨時表的區別
- MySQL兩種臨時表的用法詳解
- 淺談MySQL臨時表與派生表
- MySQL中臨時表的基本創建與使用教程
- MySQL中關于臨時表的一些基本使用方法
- MySQL使用臨時表加速查詢的方法
- MySQL中臨時表的使用示例
