關(guān)于JDBC與MySQL臨時(shí)表空間的深入解析
背景
臨時(shí)表空間用來管理數(shù)據(jù)庫排序操作以及用于存儲(chǔ)臨時(shí)表、中間排序結(jié)果等臨時(shí)對(duì)象,相信大家在開發(fā)中經(jīng)常會(huì)遇到相關(guān)的需求,下面本文將給大家詳細(xì)JDBC與MySQL臨時(shí)表空間的相關(guān)內(nèi)容,分享出來供大家參考學(xué)習(xí),下面話不多說了,來一起看看詳細(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
問題描述
在限制了臨時(shí)表空間后,當(dāng)應(yīng)用仍按以前的方式訪問時(shí),ibtmp1文件達(dá)到2G后,程序一直等待直到超時(shí)斷開連接。 SHOW PROCESSLIST顯示程序的連接線程為sleep狀態(tài),state和info信息為空。 這個(gè)對(duì)應(yīng)用開發(fā)來說不太友好,程序等待超時(shí)之后要分析原因也缺少提示信息。
問題分析過程
為了分析問題,我們進(jìn)行了以下測試
測試環(huán)境:
mysql:5.7.16
java:1.8u162
jdbc 驅(qū)動(dòng):5.1.36
OS:Red Hat 6.4
1.手工模擬臨時(shí)表超過最大限制的場景
模擬以下環(huán)境:
ibtmp1:12M:autoextend:max:30M
將一張 500萬行的 sbtest 表的 k 字段索引刪除
運(yùn)行一條 group by 的查詢,產(chǎn)生的臨時(shí)表大小超過限制后,會(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,通過 general_log 查看做了哪些設(shè)置。未發(fā)現(xiàn)做特殊設(shè)置。
3.測試 JDBC 連接
問題的背景中有對(duì)JDBC做特殊配置:useCursorFetch=true,不知道是否與隱藏報(bào)錯(cuò)有關(guān),接下來進(jìn)行測試:
發(fā)現(xiàn)以下現(xiàn)象:
·加參數(shù) useCursorFetch=true時(shí),做同樣的查詢確實(shí)不會(huì)報(bào)錯(cuò)
這個(gè)參數(shù)是為了防止返回結(jié)果集過大而采用分段讀取的方式。即程序下發(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í)行過程中臨時(shí)表大小達(dá)到 ibtmp 上限后會(huì)報(bào)錯(cuò);
2.當(dāng)JDBC設(shè)置 useCursorFetch=true
,sql 執(zhí)行過程中臨時(shí)表大小達(dá)到 ibtmp 上限后不會(huì)報(bào)錯(cuò)。
解決方案
進(jìn)一步了解到使用 useCursorFetch=true
是為了防止查詢結(jié)果集過大撐爆 jvm;
但是使用 useCursorFetch=true
又會(huì)導(dǎo)致普通查詢也生成臨時(shí)表,造成臨時(shí)表空間過大的問題;
臨時(shí)表空間過大的解決方案是限制 ibtmp1 的大小,然而 useCursorFetch=true
又導(dǎo)致JDBC不返回錯(cuò)誤。
所以需要使用其它方法來達(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 過大難以釋放的問題。
其參數(shù)為:innodb_temp_data_file_path
1.表現(xiàn)
MySQL啟動(dòng)時(shí) datadir 下會(huì)創(chuàng)建一個(gè) ibtmp1 文件,初始大小為 12M,默認(rèn)值下會(huì)無限擴(kuò)展:
通常來說,查詢導(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ù)文件變得過大,可以配置該 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。
可通過如下語句測試:
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(); } } } }
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問大家可以留言交流,謝謝大家對(duì)腳本之家的支持。
- Mysql臨時(shí)表及分區(qū)表區(qū)別詳解
- Mysql臨時(shí)表原理及創(chuàng)建方法解析
- mysql臨時(shí)表用法分析【查詢結(jié)果可存在臨時(shí)表中】
- MySQL 5.7臨時(shí)表空間如何玩才能不掉坑里詳解
- MySQL問答系列之什么情況下會(huì)用到臨時(shí)表
- MySQL臨時(shí)表的簡單用法介紹
- MySQL中Update、select聯(lián)用操作單表、多表,及視圖與臨時(shí)表的區(qū)別
- MySQL兩種臨時(shí)表的用法詳解
- 淺談MySQL臨時(shí)表與派生表
- MySQL中臨時(shí)表的基本創(chuàng)建與使用教程
- MySQL中關(guān)于臨時(shí)表的一些基本使用方法
- MySQL使用臨時(shí)表加速查詢的方法
- MySQL中臨時(shí)表的使用示例
相關(guān)文章
MySQL數(shù)據(jù)庫數(shù)據(jù)塊大小及配置方法
MySQL作為一種流行的關(guān)系數(shù)據(jù)庫管理系統(tǒng),在處理大規(guī)模數(shù)據(jù)存儲(chǔ)和查詢時(shí),數(shù)據(jù)塊(data block)大小是一個(gè)至關(guān)重要的因素,本文將詳細(xì)探討MySQL數(shù)據(jù)庫的數(shù)據(jù)塊大小,結(jié)合實(shí)際例子說明其重要性和配置方法,感興趣的朋友跟隨小編一起看看吧2024-05-05mysql建庫時(shí)提示Specified key was too long max key length is 1000
本文將詳細(xì)提供mysql建庫時(shí)提示Specified key was too long max key length is 1000 bytes的問題的解決方法,有需求的朋友可以參考2012-11-11解決MySQL Workbench gnome-keyring-daemon錯(cuò)誤的方法分享
這篇文章主要介紹了解決MySQL Workbench gnome-keyring-daemon錯(cuò)誤的方法,需要的朋友可以參考下2014-08-08MySQL無法啟動(dòng)1067錯(cuò)誤的又一種解決方法(機(jī)房斷電)
今早在對(duì)一張table 創(chuàng)建primay key過程中發(fā)生了斷電,當(dāng)電腦再次啟動(dòng)時(shí)候,發(fā)現(xiàn)mysql 服務(wù)無法啟動(dòng),使用 net start 提示 1067錯(cuò)誤,折騰了2個(gè)小時(shí)無法解決,后來只能通過手工刪除數(shù)據(jù)文件,日志文件,再啟動(dòng)服務(wù),然后導(dǎo)入數(shù)據(jù)來完成2013-01-01MySQL服務(wù)器默認(rèn)安裝之后調(diào)節(jié)性能的方法
在面試MySQL DBA或者那些打算做MySQL性能優(yōu)化的人時(shí),我最喜歡問題是:MySQL服務(wù)器按照默認(rèn)設(shè)置安裝完之后,應(yīng)該做哪些方面的調(diào)節(jié)呢?2011-05-05Mysql使用存儲(chǔ)過程快速添加百萬數(shù)據(jù)的示例代碼
這篇文章主要介紹了Mysql使用存儲(chǔ)過程快速添加百萬數(shù)據(jù),本文通過實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-08-08