Detaillierte Analyse des temporären JDBC- und MySQL-Tablespace

Hintergrund

Temporäre Tablespaces werden verwendet, um Datenbanksortiervorgänge zu verwalten und temporäre Objekte wie temporäre Tabellen und Zwischensortierergebnisse zu speichern. Ich glaube, dass Sie bei der Entwicklung häufig auf entsprechende Anforderungen stoßen werden. Der folgende Artikel gibt Ihnen detaillierte Informationen zu temporären JDBC- und MySQL-Tablespaces und stellt sie Ihnen zu Referenz- und Studienzwecken zur Verfügung. Werfen wir einen Blick auf die ausführliche Einführung.

Der JDBC-Verbindungsparameter useCursorFetch=true wird verwendet und der Abfrageergebnissatz wird im temporären MySQL-Tabellenbereich gespeichert, wodurch die ibtmp1-Dateigröße auf über 90 GB ansteigt und der Festplattenspeicher des Servers erschöpft wird. Um die Größe des temporären Tablespaces zu begrenzen, legen Sie Folgendes fest:

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

Problembeschreibung

Wenn die Anwendung nach der Begrenzung des temporären Tabellenbereichs immer noch auf die vorherige Weise darauf zugreift und die Datei ibtmp1 2 G erreicht, wartet das Programm, bis das Zeitlimit überschritten wird, und trennt die Verbindung. SHOW PROCESSLIST zeigt an, dass sich der Verbindungsthread des Programms im Ruhezustand befindet und die Status- und Infoinformationen leer sind. Dies ist für die Anwendungsentwicklung nicht sehr benutzerfreundlich. Es gibt keine sofortigen Informationen bei der Analyse der Ursache, nachdem das Programm auf das Timeout gewartet hat.

Problemanalyseprozess

Um das Problem zu analysieren, haben wir die folgenden Tests durchgeführt

Testumgebung:

mysql:5.7.16

java:1.8u162

JDBC-Treiber: 5.1.36

Betriebssystem: Red Hat 6.4

1. Simulieren Sie manuell das Szenario, in dem die temporäre Tabelle das maximale Limit überschreitet

Simulieren Sie die folgende Umgebung:

ibtmp1:12M:autoextend:max:30M

Löschen Sie den k-Feldindex der sbtest-Tabelle mit 5 Millionen Zeilen

Wenn beim Ausführen einer Group-By-Abfrage die Größe der generierten temporären Tabelle den Grenzwert überschreitet, wird direkt ein Fehler gemeldet:

Wähle Summe(k) aus sbtest1-Gruppe nach k;
FEHLER 1114 (HY000): Die Tabelle '/tmp/#sql_60f1_0' ist voll

2. Überprüfen Sie die Treibereinstellungen für MySQL

Im vorherigen Schritt haben wir gesehen, dass die manuelle Ausführung von SQL einen Fehler zurückgibt, JDBC jedoch keinen Fehler zurückgibt, wodurch die Verbindung ständig inaktiv ist. Es wird vermutet, dass der MySQL-Treiber spezielle Einstellungen vorgenommen hat. Der Treiber stellt eine Verbindung zu MySQL her und überprüft über general_log, welche Einstellungen vorgenommen wurden. Es wurden keine besonderen Einstellungen gefunden.

3. Testen Sie die JDBC-Verbindung

Im Hintergrund des Problems gibt es eine spezielle Konfiguration für JDBC: useCursorFetch=true. Ich weiß nicht, ob sie mit dem Verbergen des Fehlers zusammenhängt. Als nächstes testen wir sie:

Dabei konnten folgende Phänomene festgestellt werden:

Wenn Sie den Parameter useCursorFetch=true hinzufügen, wird bei derselben Abfrage kein Fehler gemeldet.

Dieser Parameter wird verwendet, um zu verhindern, dass der zurückgegebene Ergebnissatz zu groß wird, und um segmentiertes Lesen zu verwenden. Das heißt, nachdem das Programm eine SQL-Anweisung an MySQL gesendet hat, wartet es auf eine Rückmeldung, dass MySQL das Ergebnis lesen kann. Denn MySQL meldet einen Fehler, nachdem das zurückgegebene Ergebnis bei der Ausführung der SQL-Anweisung die Obergrenze von ibtmp erreicht hat, schließt den Thread jedoch nicht. Der Thread verarbeitet den Ruhezustand, und das Programm kann keine Rückmeldung erhalten und wartet weiter, ohne einen Fehler zu melden. Wenn Sie diesen Thread beenden, meldet das Programm einen Fehler.

Ohne den Parameter useCursorFetch=true führt die gleiche Abfrage zu einem Fehler

abschließend

1. Unter normalen Umständen wird ein Fehler gemeldet, wenn die Größe der temporären Tabelle während der SQL-Ausführung die Obergrenze von ibtmp erreicht.

2. Wenn JDBC useCursorFetch=true festlegt, wird kein Fehler gemeldet, wenn die Größe der temporären Tabelle während der SQL-Ausführung die obere ibtmp-Grenze erreicht.

Lösung

Ich habe außerdem gelernt, dass die Verwendung useCursorFetch=true dazu dient, zu verhindern, dass der Abfrageergebnissatz zu groß wird und die JVM überlastet.

Die Verwendung von useCursorFetch=true führt jedoch dazu, dass normale Abfragen temporäre Tabellen generieren, wodurch der temporäre Tabellenspeicher zu groß wird.

Die Lösung für das Problem eines zu großen temporären Tablespace besteht darin, die Größe von ibtmp1 zu begrenzen. useCursorFetch=true führt jedoch dazu, dass JDBC keinen Fehler zurückgibt.

Um den gleichen Effekt zu erzielen, müssen daher andere Methoden verwendet werden und das Programm muss nach der SQL-Fehlermeldung auch entsprechend einen Fehler melden. Zusätzlich zur Segment-Lesemethode von useCursorFetch=true können Sie auch die Stream-Lesemethode verwenden. Das Verfahren zum Lesen des Streams wird im Anhang ausführlich beschrieben.

Fehlervergleich

Segmentlesemodus: Nachdem SQL einen Fehler gemeldet hat, meldet das Programm keinen Fehler

· Im Stream-Lesemodus meldet das Programm einen Fehler, nachdem SQL einen Fehler gemeldet hat

Vergleich der Speichernutzung

Hier vergleichen wir die drei Methoden des normalen Lesens, des Segmentlesens und des Streamlesens. Der anfängliche Speicherverbrauch beträgt etwa 28 MB:

Nach dem normalen Lesen belegt der Speicher mehr als 100 MB

Nach dem Lesen des Segments belegt der Speicher etwa 60 MB

Nachdem der Stream gelesen wurde, belegt der Speicher etwa 60 MB

Zusätzliche Wissenspunkte

Gemeinsam genutzte MySQL-Wissenspunkte für temporäre Tablespaces

MySQL 5.7 hat Verbesserungen am temporären Tablespace vorgenommen und diesen von ibdata (gemeinsam genutzte Tablespace-Dateien) getrennt. Und Sie können neu starten und die Größe zurücksetzen, um das Problem zu vermeiden, dass Ibdata wie zuvor zu groß zum Freigeben sind.
Seine Parameter sind: innodb_temp_data_file_path

1. Leistung

Beim Start von MySQL wird unter datadir eine ibtmp1-Datei mit einer Anfangsgröße von 12 MB erstellt. Unter dem Standardwert wird sie unendlich erweitert:

Wenn die durch die Abfrage (z. B. Gruppieren nach) verursachte temporäre Tabelle die Größenbeschränkung von tmp_table_size und max_heap_table_size überschreitet, wird im Allgemeinen eine temporäre InnoDB-Festplattentabelle erstellt (die Standard-Engine für temporäre Tabellen von MySQL 5.7 ist InnoDB) und im gemeinsam genutzten temporären Tabellenbereich gespeichert.

Wenn durch einen Vorgang eine temporäre Tabelle mit einer Größe von 100 MB erstellt wird, wird die Datendatei des temporären Tablespace auf 100 MB erweitert, um den Anforderungen der temporären Tabelle gerecht zu werden. Wenn eine temporäre Tabelle gelöscht wird, kann der freigegebene Speicherplatz für eine neue temporäre Tabelle wiederverwendet werden, die ibtmp1-Datei bleibt jedoch erweitert.

2. Abfrageansicht

Sie können die Nutzung des gemeinsam genutzten temporären Tablespaces abfragen:

Wählen Sie Dateiname, Tabellenbereichsname, Engine, Anfangsgröße, Gesamtgröße * Größengröße als GesamtgrößeBytes, freie Daten, maximale Größe aus Informationsschema.Dateien, wobei Tabellenbereichsname = 'innodb_temporary'\G ist.
*************************** 1. Reihe ***************************
    DATEINAME: /data/mysql5722/data/ibtmp1
TABLESPACE_NAME: innodb_temporary
      Motor: InnoDB
    INITIALGRÖSSE: 12582912
   GesamtgrößeBytes: 31457280
    DATEN_FREI: 27262976
  MAXIMALE GRÖSSE: 31457280
1 Zeile im Satz (0,00 Sek.)

3. Recyclingmethoden

Starten Sie MySQL zur Wiederherstellung neu

4. Größe begrenzen

Um zu verhindern, dass temporäre Datendateien zu groß werden, können Sie die Option innodb_temp_data_file_path (erfordert Neustart, um wirksam zu werden) konfigurieren, um eine maximale Dateigröße anzugeben. Wenn die Datendatei die maximale Größe erreicht, geben Abfragen einen Fehler zurück:

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

5. Temporärer Tablespace vs. tmpdir

Der gemeinsam genutzte temporäre Tabellenbereich wird zum Speichern von Daten wie nicht komprimierten temporären InnoDB-Tabellen, zugehörigen Objekten und Rollback-Segmenten verwendet.

tmpdir wird zum Speichern bestimmter temporärer Dateien und temporärer Tabellen verwendet. Im Gegensatz zum gemeinsam genutzten temporären Tablespace speichert tmpdir komprimierte temporäre InnoDB-Tabellen.

Dies kann mit der folgenden Anweisung getestet werden:

Temporäre Tabelle erstellen compress_table (ID int, Name char(255)) ROW_FORMAT=KOMPRIMIERT;
Temporäre Tabelle erstellen: uncompress_table (ID int, Name char(255));

Anhang

EinfachesBeispiel.java

importiere java.sql.Connection;
importiere java.sql.DriverManager;
importiere java.sql.PreparedStatement;
importiere java.sql.ResultSet;
importiere java.sql.SQLException;
importiere java.sql.Statement;
importiere java.util.Properties;
importiere java.util.concurrent.CountDownLatch;
importiere java.util.concurrent.atomic.AtomicLong;
öffentliche Klasse SimpleExample {
 öffentliche statische void main(String[] args) wirft Exception {
  Klasse.fürName("com.mysql.jdbc.Driver");
  Eigenschaften-Eigenschaften = neue Eigenschaften();
  props.setProperty("Benutzer", "root");
  props.setProperty("Passwort", "root");
  SimpleExample-Engine = neues SimpleExample();
// engine.execute(Eigenschaften,"jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false");
  engine.execute(Eigenschaften,"jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false&useCursorFetch=true");
 }
 endgültige AtomicLong tmAl = neues AtomicLong();
 endgültiger String-Tabellenname = "Test";
 public void execute(Eigenschaften Eigenschaften,String URL) {
  CountDownLatch cdl = neuer CountDownLatch(1);
  langer Start = System.currentTimeMillis();
  für (int i = 0; i < 1; i++) {
   TestThread insertThread = neuer TestThread(Requisiten, CDL, URL);
   Thread t = neuer Thread(Thread einfügen);
   t.start();
   System.out.println("Teststart");
  }
  versuchen {
   cdl.warten();
   langes Ende = System.currentTimeMillis();
   System.out.println("Testende, Gesamtkosten:" + (Ende-Start) + "ms");
  } Fang (Ausnahme e) {
  }
 }
 
 Klasse TestThread implementiert Runnable {
  Eigenschaften Requisiten;
  privater CountDownLatch countDownLatch;
  String-URL;
  öffentlicher TestThread(Eigenschafteneigenschaften, CountDownLatch cdl, String-URL) {
   this.props = Requisiten;
   dies.countDownLatch = cdl;
   diese.url = URL;
  }
  öffentliche Leere ausführen() {
   Verbindung Verbindung = null;
   Vorbereitete Anweisung ps = null;
   Anweisung st = null;
   langer Start = System.currentTimeMillis();
   versuchen {
    Verbindung = DriverManager.getConnection(url,props);
    Verbindung.setAutoCommit(false);
    st = Verbindung.createStatement();
     
    //st.setFetchSize(500);
    st.setFetchSize(Integer.MIN_VALUE); //Einfach hier ändern ResultSet rstmp;
     
    st.executeQuery("wähle Summe(k) aus sbtest1-Gruppe nach k aus");
    rstmp = st.getResultSet();
    während(rstmp.next()){
      
    }
   } Fang (Ausnahme e) {
    System.out.println(System.currentTimeMillis() - starten);
    System.out.println(neues java.util.Date().toString());
    e.printStackTrace();
   Endlich
    wenn (ps != null)
     versuchen {
      ps.schließen();
     } Fang (SQLException e1) {
      e1.printStackTrace();
     }
    wenn (Verbindung != null)
     versuchen {
      Verbindung.schließen();
     } Fang (SQLException e1) {
      e1.printStackTrace();
     }
    dies.countDownLatch.countDown();
   }
  }
 }
}

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.