欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間:2018年09月12日 10:11:29   作者:愛可生云數(shù)據(jù)庫  
這篇文章主要給大家介紹了關(guān)于JDBC與MySQL臨時表空間的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

背景

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

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

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

問題描述

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

問題分析過程

為了分析問題,我們進(jìn)行了以下測試

測試環(huán)境:

mysql:5.7.16

java:1.8u162

jdbc 驅(qū)動:5.1.36

OS:Red Hat 6.4

1.手工模擬臨時表超過最大限制的場景

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

ibtmp1:12M:autoextend:max:30M

將一張 500萬行的 sbtest 表的 k 字段索引刪除

運行一條 group by 的查詢,產(chǎn)生的臨時表大小超過限制后,會直接報錯:

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

2.檢查驅(qū)動對 mysql 的設(shè)置

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

3.測試 JDBC 連接

問題的背景中有對JDBC做特殊配置:useCursorFetch=true,不知道是否與隱藏報錯有關(guān),接下來進(jìn)行測試:

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

·加參數(shù) useCursorFetch=true時,做同樣的查詢確實不會報錯

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

·不加參數(shù) useCursorFetch=true時,做同樣的查詢則會報錯

結(jié)論

1.正常情況下,sql 執(zhí)行過程中臨時表大小達(dá)到 ibtmp 上限后會報錯;

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

解決方案

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

但是使用 useCursorFetch=true 又會導(dǎo)致普通查詢也生成臨時表,造成臨時表空間過大的問題;

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

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

·報錯對比

·段讀取方式,sql 報錯后,程序不報錯

·流讀取方式,sql 報錯后,程序會報錯

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

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

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

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

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

補充知識點

MySQL共享臨時表空間知識點

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

1.表現(xiàn)

MySQL啟動時 datadir 下會創(chuàng)建一個 ibtmp1 文件,初始大小為 12M,默認(rèn)值下會無限擴展:

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

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

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

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

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

5. 臨時表空間與 tmpdir 對比

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

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

總結(jié)

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

相關(guān)文章

  • MySQL數(shù)據(jù)庫數(shù)據(jù)塊大小及配置方法

    MySQL數(shù)據(jù)庫數(shù)據(jù)塊大小及配置方法

    MySQL作為一種流行的關(guān)系數(shù)據(jù)庫管理系統(tǒng),在處理大規(guī)模數(shù)據(jù)存儲和查詢時,數(shù)據(jù)塊(data block)大小是一個至關(guān)重要的因素,本文將詳細(xì)探討MySQL數(shù)據(jù)庫的數(shù)據(jù)塊大小,結(jié)合實際例子說明其重要性和配置方法,感興趣的朋友跟隨小編一起看看吧
    2024-05-05
  • mysql建庫時提示Specified key was too long max key length is 1000 bytes的問題的解決方法

    mysql建庫時提示Specified key was too long max key length is 1000

    本文將詳細(xì)提供mysql建庫時提示Specified key was too long max key length is 1000 bytes的問題的解決方法,有需求的朋友可以參考
    2012-11-11
  • MySQL 查看事務(wù)和鎖情況的常用語句分享

    MySQL 查看事務(wù)和鎖情況的常用語句分享

    這篇文章主要介紹了MySQL 查看事務(wù)和鎖情況的常用語句分享,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • 解決MySQL Workbench gnome-keyring-daemon錯誤的方法分享

    解決MySQL Workbench gnome-keyring-daemon錯誤的方法分享

    這篇文章主要介紹了解決MySQL Workbench gnome-keyring-daemon錯誤的方法,需要的朋友可以參考下
    2014-08-08
  • MySQL無法啟動1067錯誤的又一種解決方法(機房斷電)

    MySQL無法啟動1067錯誤的又一種解決方法(機房斷電)

    今早在對一張table 創(chuàng)建primay key過程中發(fā)生了斷電,當(dāng)電腦再次啟動時候,發(fā)現(xiàn)mysql 服務(wù)無法啟動,使用 net start 提示 1067錯誤,折騰了2個小時無法解決,后來只能通過手工刪除數(shù)據(jù)文件,日志文件,再啟動服務(wù),然后導(dǎo)入數(shù)據(jù)來完成
    2013-01-01
  • MySQL服務(wù)器默認(rèn)安裝之后調(diào)節(jié)性能的方法

    MySQL服務(wù)器默認(rèn)安裝之后調(diào)節(jié)性能的方法

    在面試MySQL DBA或者那些打算做MySQL性能優(yōu)化的人時,我最喜歡問題是:MySQL服務(wù)器按照默認(rèn)設(shè)置安裝完之后,應(yīng)該做哪些方面的調(diào)節(jié)呢?
    2011-05-05
  • MySql無法連接本地地址localhost問題

    MySql無法連接本地地址localhost問題

    這篇文章主要介紹了MySql無法連接本地地址localhost問題,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2023-06-06
  • MySQL外鍵使用及說明詳解

    MySQL外鍵使用及說明詳解

    MySQL通過外鍵約束來保證表與表之間的數(shù)據(jù)的完整性和準(zhǔn)確性。這篇文章還通過外鍵的使用條件和外鍵的好處定義語法方面介紹了mysql外鍵使用及說明,非常不錯,具有參考借鑒價值,需要的朋友一起看下吧
    2016-08-08
  • javascript身份證驗證代碼

    javascript身份證驗證代碼

    對于客戶端驗證用戶輸入的身份證是否符合格式的代碼,需要的朋友可以參考下。
    2010-11-11
  • Mysql使用存儲過程快速添加百萬數(shù)據(jù)的示例代碼

    Mysql使用存儲過程快速添加百萬數(shù)據(jù)的示例代碼

    這篇文章主要介紹了Mysql使用存儲過程快速添加百萬數(shù)據(jù),本文通過實例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2021-08-08

最新評論