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

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

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

背景

臨時(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)行了以下測(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í)表超過最大限制的場(chǎng)景

模擬以下環(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.測(cè)試 JDBC 連接

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

發(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。

可通過如下語句測(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=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é)

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

相關(guān)文章

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

    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-05
  • mysql建庫時(shí)提示Specified key was too long max key length is 1000 bytes的問題的解決方法

    mysql建庫時(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 查看事務(wù)和鎖情況的常用語句分享

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

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

    解決MySQL Workbench gnome-keyring-daemon錯(cuò)誤的方法分享

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

    MySQL無法啟動(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-01
  • MySQL服務(wù)器默認(rèn)安裝之后調(diào)節(jié)性能的方法

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

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

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

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

    MySQL外鍵使用及說明詳解

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

    javascript身份證驗(yàn)證代碼

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

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

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

最新評(píng)論