Warum MySQL große Transaktionen vermeiden sollte und wie man sie löst

Was ist eine große Sache?

Transaktionen, die über einen langen Zeitraum ausgeführt werden und für die über einen langen Zeitraum keine Festschreibung erfolgt, können als große Transaktionen bezeichnet werden.

Die Ursachen des Großkapitals

• Es müssen mehr Daten verarbeitet werden
• Viele Sperrkonflikte
• Es gibt andere zeitaufwändige Operationen in der Transaktion, die nichts mit der Datenbank zu tun haben.
• . . .
  

Die Wirkung großer Ereignisse

• Bei gleichzeitigen Verbindungen ist der Datenbankverbindungspool anfällig für Überlastungen.
• Sperren zu vieler Daten, was zu vielen Blockierungs- und Sperrzeitüberschreitungen führt
• Lange Ausführungszeit, die zu Master-Slave-Verzögerungen führen kann
• Rollback dauert lange
• Undo-Log-Erweiterung
• . . .
  

So fragen Sie große Transaktionen ab

**Hinweis**: Die SQL-Operationen in diesem Artikel basieren auf MySQL Version 5.7

Nehmen wir als Beispiel eine Abfrageausführungszeit von mehr als 10 Sekunden:

wähle \* aus information\_schema.innodb\_trx, wobei TIME\_TO\_SEC(timediff(now(),trx\_started))>10

So vermeiden Sie große Transaktionen

Allgemeine Lösung

• Vermeiden Sie die gleichzeitige Verarbeitung zu vieler Daten in einer Transaktion
• Vermeiden Sie bei einer Transaktion unnötige Rückfragen
• Vermeiden Sie während einer Transaktion die Durchführung zu vieler zeitaufwändiger Vorgänge, die zu einem Transaktionstimeout führen können. Versuchen Sie, einige Nicht-DB-Operationen, wie z. B. RPC-Aufrufe und Nachrichtenwarteschlangenoperationen, außerhalb von Transaktionen zu platzieren.
  

Lösung basierend auf mysql5.7

• Bei einer InnoDB-Transaktion werden Zeilensperren nur bei Bedarf hinzugefügt, aber nicht sofort freigegeben, wenn sie nicht mehr benötigt werden. Stattdessen werden sie erst freigegeben, wenn die Transaktion endet. **Wenn Sie in Ihrer Transaktion mehrere Zeilen sperren müssen, setzen Sie die Sperren, die am wahrscheinlichsten Sperrkonflikte verursachen und die Parallelität beeinträchtigen, so weit nach hinten wie möglich.**
• Verwenden Sie den Befehl SETMAX_EXECUTION_TIME, um die maximale Abfragezeit für jede Anweisung zu steuern und zu verhindern, dass eine einzelne Anweisung versehentlich zu lange abfragt.
• Überwachen Sie die Tabelle information_schema.Innodb_trx, legen Sie einen Schwellenwert für lange Transaktionen fest und geben Sie einen Alarm aus oder beenden Sie die Transaktion, wenn dieser überschritten wird.
• Während der Testphase der Geschäftsfunktionen müssen alle allgemeinen Protokolle ausgegeben und das Protokollverhalten analysiert werden, um Probleme im Voraus zu erkennen.
• Legen Sie den Wert innodb_undo_tablespaces fest, um das Undo-Protokoll in einen separaten Tablespace zu trennen. Wenn das Rollback-Segment aufgrund einer großen Transaktion zu groß wird, lässt es sich nach der Einrichtung leichter bereinigen.
  

Anhang: Abfragetransaktionsbezogene Anweisungen

**Hinweis**: SQL-Anweisungen basieren auf MySQL Version 5.7

# Abfrage aller laufenden Transaktionen und deren Laufzeit select t.\*,to\_seconds(now())-to\_seconds(t.trx\_started) idle\_time from INFORMATION\_SCHEMA.INNODB\_TRX t



# Transaktionsdetails abfragen und SQL ausführen

Wählen Sie jetzt (), (UNIX\_TIMESTAMP (jetzt ()) - UNIX\_TIMESTAMP (a.trx\_started)) diff\_sec, b.id, b.user, b.host, b.db, d.SQL\_TEXT aus information\_schema.innodb\_trx ein innerer Join information\_schema.PROCESSLIST b

auf a.TRX\_MYSQL\_THREAD\_ID=b.id und b.command = 'Sleep'

Innerer Join Performance\_Schema.Threads c ON b.id = c.PROCESSLIST\_ID

innerer Join Performance\_Schema.Events\_Statements\_current d ON d.THREAD\_ID = c.THREAD\_ID;



# Abfrage aller historischen SQL-Datensätze, die von der Transaktion SELECT ausgeführt wurden

 ps.id 'PROZESS-ID',

 ps.BENUTZER,

 ps.HOST,

 esh.EVENT_ID,

 trx.trx_started,

 esh.event\_name 'EREIGNISNAME',

 esh.sql\_text 'SQL',

 ps.Zeit 

AUS

 PERFORMANCE\_SCHEMA.events\_statements\_history esh

 JOIN PERFORMANCE\_SCHEMA.threads th ON esh.thread\_id = th.thread\_id

 JOIN information\_schema.PROCESSLIST ps ON ps.id = th.processlist\_id

 LEFT JOIN Informationen\_schema.innodb\_trx trx ON trx.trx\_mysql\_thread\_id = ps.id 

WO

 trx.trx_id IST NICHT NULL 

 UND ps.USER != 'SYSTEM\_USER' 

BESTELLEN BIS

 esh.EVENT_ID;

 

 # Einfache Abfragetransaktionssperre select \* from sys.innodb\_lock\_waits

 

 #Transaktionssperrdetails abfragenSELECT

 temporär.*,

 c.SQL\_Textblockierung\_sql\_text,

 p.HOST blockiert\_host 

AUS

 (

 WÄHLEN

 r.trx_state beobachtet_trx_state,

 r.trx_id warte_trx_id,

 r.trx\_mysql\_thread\_Id wartender\_Thread,

 r.trx_query warte_abfrage,

 b.trx_state Blockierender_trx_state,

 b.trx_id blockierende_trx_id,

 b.trx\_mysql\_thread\_id blockierender\_Thread,

 b.trx_query blockierende_Abfrage 

 AUS

 information\_schema.innodb\_lock\_waits w

 INNER JOIN information\_schema.innodb\_trx b ON b.trx\_id = w.blocking\_trx\_id

 INNER JOIN information\_schema.innodb\_trx r ON r.trx\_id = w.requesting\_trx\_id 

 ) tmp,

 information\_schema.PROCESSLIST p,

 PERFORMANCE\_SCHEMA.events\_statements\_current c,

 PERFORMANCE\_SCHEMA.threads t 

WO

 tmp.blocking_thread = p.id 

 UND t.thread_id = c.THREAD_ID 

 UND t.PROCESSLIST_ID = p.id

Oben finden Sie Einzelheiten dazu, wie Sie große Transaktionen in MySQL vermeiden und wie Sie große Transaktionen lösen können. Weitere Informationen zu großen MySQL-Transaktionen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!