Empfehlungen für MySQL-Funktionen in Aurora MySQL - Amazon Aurora
Verwenden von Multithread-Replikation in Aurora MySQLAufrufen von AWS Lambda-Funktionen mit Hilfe von nativen MySQL-FunktionenVermeiden von XA-Transaktionen mit Amazon Aurora MySQLAktivieren von Fremdschlüsseln während DML-AnweisungenKonfigurieren, wie oft der Protokollpuffer geleert wirdMinimieren und Beheben von Aurora-MySQL-Deadlocks

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Empfehlungen für MySQL-Funktionen in Aurora MySQL

Die folgenden Funktionen sind in Aurora MySQL für die MySQL-Kompatibilität verfügbar. Sie haben jedoch Probleme mit der Leistung, Skalierbarkeit, Stabilität oder Kompatibilität in der Aurora-Umgebung. Wir empfehlen daher, dass Sie bei der Verwendung dieser Funktionen bestimmte Richtlinien einhalten. Wir empfehlen zum Beispiel, bestimmte Funktionen nicht für Aurora-Produktionseinsätze zu verwenden.

Verwenden von Multithread-Replikation in Aurora MySQL

Bei der Multithread-Replikation für binäre Protokolle liest ein SQL-Thread Ereignisse aus dem Relay-Log und stellt sie in die Warteschlange, damit SQL-Worker-Threads angewendet werden können. Die SQL-Worker-Threads werden von einem Koordinator-Thread verwaltet. Die binären Protokollereignisse werden nach Möglichkeit parallel angewendet.

Die Multithread-Replikation wird in Aurora-MySQL-Version 3 und Aurora-MySQL-Version 2.12.1 und höher unterstützt.

Für Aurora-MySQL-Versionen unter 3.04 verwendet Aurora standardmäßig die Single-Thread-Replikation, wenn ein Aurora-MySQL-DB-Cluster als Read Replica für die Binärprotokollreplikation verwendet wird.

Frühere Versionen von Aurora-MySQL-Version 2 haben mehrere Probleme in Bezug auf die Multi-Thread-Replikation aus der MySQL Community Edition geerbt. Für diese Versionen empfehlen wir, die Multithread-Replikation in der Produktion nicht zu verwenden.

Wenn Sie die Multi-Thread-Replikation verwenden, empfehlen wir Ihnen, diese vorher gründlich zu testen.

Weitere Informationen zur Verwendung der Replikation in Amazon Aurora finden Sie unter Replikation mit Amazon Aurora. Weitere Informationen zur Multithread-Replikation in Aurora MySQL finden Sie unter Multithread-Replikation für binäre Protokolle.

Aufrufen von AWS Lambda-Funktionen mit Hilfe von nativen MySQL-Funktionen

Wir empfehlen, die nativen MySQL-Funktionen lambda_sync und lambda_async zu verwenden, um Lambda-Funktionen aufzurufen.

Wenn Sie die veraltete Prozedur mysql.lambda_async verwenden, empfehlen wir, dass Sie die Aufrufe an die Prozedur mysql.lambda_async in einer gespeicherten Prozedur übergeben. Sie können diese gespeicherte Prozedur aus verschiedenen Quellen aufrufen, wie z. B. Trigger oder Client-Code. Dieser Ansatz kann helfen, Probleme hinsichtlich Impedanz-Unstimmigkeiten zu vermeiden und macht es Ihren Datenbank-Programmieren einfacher Lambda-Funktionen aufzurufen.

Weitere Informationen über das Aufrufen von Lambda-Funktionen in Amazon Aurora finden Sie unter Aufrufen einer Lambda-Funktion aus einem Amazon Aurora MySQL-DB-Cluster.

Vermeiden von XA-Transaktionen mit Amazon Aurora MySQL

Wir empfehlen Ihnen, keine eXtended Architecture (XA)-Transaktionen mit Aurora MySQL zu verwenden, da diese lange Wiederherstellungszeiten verursachen können, wenn sich die XA im Status PREPARED befunden hat. Wenn Sie XA-Transaktionen mit Aurora MySQL verwenden müssen, befolgen Sie diese bewährten Verfahren:

  • Lassen Sie eine XA-Transaktion nicht im Status PREPARED offen.

  • Halten Sie XA-Transaktionen so klein wie möglich.

Weitere Informationen zur Verwendung von XA-Transaktionen mit MySQL finden Sie unter XA Transactions in der MySQL-Dokumentation.

Aktivieren von Fremdschlüsseln während DML-Anweisungen

Wir empfehlen Ihnen dringend, keine DDL-Anweisungen (Data Definition Language) auszuführen, wenn die Variable foreign_key_checks auf 0 (aus) gesetzt ist.

Wenn Sie Zeilen einfügen oder aktualisieren müssen, die eine vorübergehende Verletzung von Fremdschlüsseln bedingen, gehen Sie wie folgt vor:

  1. Setzen Sie foreign_key_checks auf 0.

  2. Nehmen Sie Ihre DML-Änderungen (Data Manipulation Language) vor.

  3. Stellen Sie sicher, dass Ihre durchgeführten Änderungen keine Fremdschlüsselbedingungen verletzen.

  4. Setzen Sie foreign_key_checks auf 1 (ein).

Darüber hinaus halten Sie die folgenden anderen bewährten Methoden für Fremdschlüsselbedingungen ein:

  • Stellen Sie sicher, dass Ihre Client-Anwendungen die Variable foreign_key_checks nicht auf 0 als Teil der Variablen init_connect setzen.

  • Wenn eine Wiederherstellung aus einer logischen Sicherung, wie beispielsweise mysqldump, fehlschlägt oder unvollständig ist, stellen Sie sicher, dass foreign_key_checks auf 1 gesetzt ist, bevor Sie innerhalb derselben Sitzung andere Operationen starten. Eine logische Sicherung setzt foreign_key_checks beim Start auf 0.

Konfigurieren, wie oft der Protokollpuffer geleert wird

In der MySQL Community Edition muss der InnoDB-Protokollpuffer in einen dauerhaften Speicher geleert werden, um Transaktionen dauerhaft zu machen. Verwenden Sie den Parameter innodb_flush_log_at_trx_commit, um zu konfigurieren, wie oft der Protokollpuffer geleert und auf die Festplatte übertragen wird.

Wenn Sie den Parameter innodb_flush_log_at_trx_commit auf den Standardwert 1 festlegen, wird der Protokollpuffer bei jedem Transaktions-Commit geleert. Diese Einstellung hilft, die Datenbank ACID-konform zu halten. Wir empfehlen, die Standardeinstellung 1 beizubehalten.

Wenn Sie innodb_flush_log_at_trx_commit in einen nicht standardmäßigen Wert ändern, kann dies dazu beitragen, die Latenz der Data Manipulation Language (DML) zu verringern, beeinträchtigt jedoch die Haltbarkeit der Protokolldatensätze. Durch diese mangelnde Haltbarkeit ist die Datenbank nicht ACID-konform. Ihre Datenbanken sollten ACID-konform sein, um das Risiko von Datenverlusten bei einem Serverneustart zu vermeiden. Weitere Informationen zu diesem Parameter finden Sie unter innodb_flush_log_at_trx_commit in der MySQL-Dokumentation.

In Aurora MySQL wird die Redo-Protokollverarbeitung auf die Speicherschicht verlagert, sodass auf der DB-Instance kein Leeren in Protokolldateien erfolgt. Wenn ein Schreibvorgang ausgeführt wird, werden Redo-Protokolle von der Writer-DB-Instance direkt an das Aurora-Cluster-Volume gesendet. Die einzigen Schreibvorgänge, die das Netzwerk passieren, sind Redo-Protokolldatensätze. Auf der Datenbankebene werden grundsätzlich keine Seiten geschrieben.

Standardmäßig wartet jeder Thread, der einen Commit für eine Transaktion ausführt, auf die Bestätigung vom Aurora-Cluster-Volume. Diese Bestätigung gibt an, dass dieser Datensatz und alle vorherigen Redo-Protokolldatensätze geschrieben wurden und das Quorum erreicht haben. Dadurch, dass die Protokolldatensätze beibehalten und das Quorum erreicht wird, ist die Transaktion dauerhaft gemacht worden, sei es durch Autocommit oder explizites Commit. Weitere Informationen zur Aurora-Speicherarchitektur finden Sie unter Amazon Aurora storage demystified (Amazon-Aurora-Speicher entmystifiziert).

Aurora MySQL leert keine Protokolle in Datendateien, wie dies bei der MySQL Community Edition der Fall ist. Sie können den Parameter innodb_flush_log_at_trx_commit jedoch verwenden, um Haltbarkeitsbeschränkungen beim Schreiben von Redo-Protokolldatensätzen auf das Aurora-Clustervolume zu lockern.

Für Aurora-MySQL-Version 2:

  • innodb_flush_log_at_trx_commit = 0 oder 2 – Die Datenbank wartet nicht auf die Bestätigung, dass die Redo-Protokolldatensätze auf das Aurora-Cluster-Volume geschrieben wurden.

  • innodb_flush_log_at_trx_commit = 1 – Die Datenbank wartet auf die Bestätigung, dass die Redo-Protokolldatensätze auf das Aurora-Cluster-Volume geschrieben wurden.

Für Aurora-MySQL-Version 3:

  • innodb_flush_log_at_trx_commit = 0 – Die Datenbank wartet nicht auf die Bestätigung, dass die Redo-Protokolldatensätze auf das Aurora-Cluster-Volume geschrieben wurden.

  • innodb_flush_log_at_trx_commit = 1 oder 2 – Die Datenbank wartet auf die Bestätigung, dass die Redo-Protokolldatensätze auf das Aurora-Cluster-Volume geschrieben wurden.

Um in Aurora-MySQL-Version 3 dasselbe nicht standardmäßige Verhalten zu erzielen, das Sie mit dem Wert 0 oder 2 in Aurora-MySQL-Version 2 erzielen würden, setzen Sie den Parameter daher auf 0.

Diese Einstellungen können zwar die DML-Latenz für den Client verringern, sie können im Falle eines Failovers oder Neustarts aber auch zu Datenverlust führen. Daher empfehlen wir, für den Parameter innodb_flush_log_at_trx_commit den Standardwert 1 beizubehalten.

Während Datenverlust sowohl bei der MySQL Community Edition als auch bei Aurora MySQL auftreten kann, unterscheidet sich das Verhalten in jeder Datenbank aufgrund ihrer unterschiedlichen Architekturen. Diese Unterschiede in der Architektur können zu unterschiedlich starkem Datenverlust führen. damit sichergestellt wird, dass Ihre Datenbank ACID-konform ist, legen Sie innodb_flush_log_at_trx_commit immer auf den Wert 1 fest.

Anmerkung

Bevor Sie innodb_flush_log_at_trx_commit in Aurora-MySQL-Version 3 in einen anderen Wert als 1 ändern können, müssen Sie zuerst den Wert von innodb_trx_commit_allow_data_loss auf 1 ändern. Auf diese Weise erkennen Sie das Risiko eines Datenverlusts an.

Minimieren und Beheben von Aurora-MySQL-Deadlocks

Bei Benutzern, die Workloads ausführen, bei denen regelmäßig Einschränkungen für eindeutige sekundäre Indizes oder Fremdschlüssel verletzt werden, kann es bei der gleichzeitigen Änderung von Datensätzen auf derselben Datenseite zu erhöhten Deadlocks und Wartezeitüberschreitungen bei Sperren kommen. Diese Deadlocks und Zeitüberschreitungen sind auf einen Bugfix der MySQL Community Edition zurückzuführen.

Dieser Bugfix ist in den MySQL-Community-Edition-Versionen 5.7.26 und höher enthalten und wurde in die Aurora-MySQL-Versionen 2.10.3 und höher zurückportiert. Der Bugfix ist notwendig, um die Serialisierbarkeit zu erzwingen, indem zusätzliche Sperren für diese Arten von Data Manipulation Language (DML)-Operationen für Änderungen an Datensätzen in einer InnoDB-Tabelle implementiert werden. Dieses Problem wurde im Rahmen einer Untersuchung von Deadlock-Problemen aufgedeckt, die durch einen früheren Bugfix der MySQL Community Edition verursacht wurden.

Mit dem Bugfix wurde die interne Behandlung für das teilweise Rollback eines Tupel- (Zeilen-)Updates in der InnoDB-Speicher-Engine geändert. Operationen, die zu Einschränkungsverstößen bei Fremdschlüsseln oder eindeutigen Sekundärindizes führen, verursachen ein partielles Rollback. Dies beinhaltet, ist aber nicht beschränkt auf gleichzeitige INSERT...ON DUPLICATE KEY UPDATE-, REPLACE INTO,- und INSERT IGNORE-Anweisungen (upserts).

In diesem Zusammenhang bezieht sich partielles Rollback nicht auf das Rollback von Transaktionen auf Anwendungsebene, sondern auf ein internes InnoDB-Rollback von Änderungen an einem gruppierten Index, wenn ein Einschränkungsverstoß auftritt. Beispielsweise wird während einer Upsert-Operation ein doppelter Schlüsselwert gefunden.

In einem normalen Einfügevorgang erstellt InnoDB automatisch gruppierte und sekundäre Indexeinträge für jeden Index. Wenn InnoDB während einer Upsert-Operation einen doppelten Wert in einem eindeutigen sekundären Index erkennt, muss der eingefügte Eintrag im gruppierten Index rückgängig gemacht werden (partielles Rollback), und die Aktualisierung muss dann auf die vorhandene doppelte Zeile angewendet werden. Während dieses internen partiellen Rollback-Schritts muss InnoDB jeden Datensatz sperren, der als Teil des Vorgangs angezeigt wird. Der Bugfix gewährleistet die Serialisierbarkeit von Transaktionen, indem nach dem partiellen Rollback eine zusätzliche Sperrung eingeführt wird.

Minimieren von InnoDB-Deadlocks

Sie können die folgenden Ansätze verwenden, um die Häufigkeit von Deadlocks in Ihrer Datenbank-Instance zu reduzieren. Weitere Beispiele finden Sie in der MySQL-Dokumentation.

  1. Um die Wahrscheinlichkeit von Deadlocks zu verringern, sollten Sie für Transaktionen sofort einen Commit ausführen, nachdem Sie die entsprechenden Änderungen vorgenommen haben. Teilen Sie dazu große Transaktionen (mehrere Zeilenaktualisierungen zwischen Commits) in kleinere Transaktionen auf. Wenn Sie Zeilen stapelweise einfügen, versuchen Sie, die Größe der Stapeleinfügungen zu reduzieren, insbesondere wenn Sie die zuvor genannten Upsert-Operationen verwenden.

    Um die Anzahl möglicher partieller Rollbacks zu reduzieren, können Sie einige der folgenden Ansätze ausprobieren:

    1. Ersetzen Sie Batch-Einfügeoperationen durch das Einfügen einer Zeile nach der anderen. Dadurch kann die Zeit reduziert werden, in der Sperren aufgrund von Transaktionen, die möglicherweise zu Konflikten führen, aufrechterhalten bleiben.

    2. Anstatt REPLACE INTO zu verwenden, schreiben Sie die SQL-Anweisung in eine Transaktion mit mehreren Anweisungen um, z. B. die folgende:

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. Anstatt INSERT...ON DUPLICATE KEY UPDATE zu verwenden, schreiben Sie die SQL-Anweisung in eine Transaktion mit mehreren Anweisungen um, z. B. die folgende:

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. Vermeiden Sie lang dauernde Transaktionen, ob aktiv oder inaktiv, die Sperren möglicherweise aufrechterhalten. Dazu gehören interaktive MySQL-Client-Sitzungen, die möglicherweise über einen längeren Zeitraum geöffnet sind, wenn für eine Transaktion kein Commit ausgeführt wird. Bei der Optimierung von Transaktionsgrößen oder Batch-Größen können die Auswirkungen in Abhängigkeit von einer Reihe von Faktoren wie Parallelität, Anzahl der Duplikate und Tabellenstruktur variieren. Alle Änderungen sollten auf der Grundlage Ihrer Workload implementiert und getestet werden.

  3. In einigen Situationen können Deadlocks auftreten, wenn zwei Transaktionen versuchen, auf dieselben Datensätze, entweder in einer oder mehreren Tabellen, in unterschiedlicher Reihenfolge zuzugreifen. Um dies zu verhindern, können Sie die Transaktionen so ändern, dass sie in derselben Reihenfolge auf die Daten zugreifen, wodurch der Zugriff serialisiert wird. Erstellen Sie beispielsweise eine Warteschlange mit Transaktionen, die abgeschlossen werden sollen. Dieser Ansatz kann dazu beitragen, Deadlocks zu vermeiden, wenn mehrere Transaktionen gleichzeitig stattfinden.

  4. Durch Hinzufügen sorgfältig ausgewählter Indizes zu Ihren Tabellen lässt sich die Selektivität verbessern und die Notwendigkeit, auf Zeilen zuzugreifen, reduzieren, was zu weniger Sperren führt.

  5. Wenn Sie auf eine Lückensperre stoßen, können Sie die Transaktionsisolationsstufe für die Sitzung oder Transaktion in READ COMMITTED ändern, um dies zu verhindern. Weitere Informationen zu InnoDB-Isolationsstufen und ihrem Verhalten finden Sie unter Transaktionsisolationsstufen in der MySQL-Dokumentation.

Anmerkung

Sie können zwar Vorkehrungen treffen, um die Wahrscheinlichkeit von Deadlocks zu verringern, Deadlocks sind jedoch ein erwartetes Datenbankverhalten und können dennoch auftreten. Anwendungen sollten über die erforderliche Logik zum Umgang mit Deadlocks verfügen, wenn diese auftreten. Implementieren Sie beispielsweise die Wiederholungs- und Back-Off-Logik in der Anwendung. Es ist am besten, die Ursache des Problems zu beheben. Wenn jedoch ein Deadlock auftritt, hat die Anwendung die Möglichkeit, zu warten und es erneut zu versuchen.

Überwachen von InnoDB-Deadlocks

Deadlocks können in MySQL auftreten, wenn Anwendungstransaktionen versuchen, Sperren auf Tabellen- und Zeilenebene so zu umgehen, dass zirkuläres Warten entsteht. Ein gelegentlicher InnoDB-Deadlock ist nicht unbedingt ein Problem, da die InnoDB-Speicher-Engine den Zustand sofort erkennt und für eine der Transaktionen automatisch ein Rollback durchführt. Wenn Sie häufig auf Deadlocks stoßen, empfehlen wir, Ihre Anwendung zu überprüfen und zu ändern, um Leistungsprobleme zu verringern und Deadlocks zu vermeiden. Wenn die Deadlock-Erkennung aktiviert ist (Standard), erkennt InnoDB automatisch Transaktions-Deadlocks und führt ein Rollback für eine oder mehrere Transaktionen durch, um den Deadlock zu durchbrechen. InnoDB versucht, kleine Transaktionen für das Rollback auszuwählen, wobei die Größe einer Transaktion durch die Anzahl der eingefügten, aktualisierten oder gelöschten Zeilen bestimmt wird.

  • SHOW ENGINE-Anweisung – Die SHOW ENGINE INNODB STATUS \G-Anweisung enthält Details zum letzten Deadlock, der seit dem letzten Neustart in der Datenbank aufgetreten ist.

  • MySQL-Fehlerprotokoll – Wenn Sie häufig auf Deadlocks stoßen, bei denen die Ausgabe der SHOW ENGINE-Anweisung unangemessen ist, können Sie den DB-Cluster-Parameter innodb_print_all_deadlocks aktivieren.

    Wenn dieser Parameter aktiviert ist, werden Informationen über alle Deadlocks in InnoDB-Benutzertransaktionen im Fehlerprotokoll von Aurora MySQL aufgezeichnet.

  • Amazon-CloudWatch-Metriken – Wir empfehlen außerdem, Deadlocks mithilfe der CloudWatch-Metrik Deadlocks proaktiv zu überwachen. Weitere Informationen finden Sie unter Metriken auf Instance-Ebene für Amazon Aurora.

  • Amazon CloudWatch Logs – Mit CloudWatch Logs können Sie Metriken anzeigen, Protokolldaten analysieren und Echtzeitalarme erstellen. Weitere Informationen finden Sie unter Überwachen von Fehlern in Amazon Aurora MySQL und Amazon RDS für MySQL mithilfe von Amazon CloudWatch und Senden von Benachrichtigungen mithilfe von Amazon SNS.

    Wenn CloudWatch Logs mit aktiviertem Parameter innodb_print_all_deadlocks verwendet wird, können Sie Alarme so konfigurieren, dass Sie benachrichtigt werden, wenn die Anzahl der Deadlocks einen bestimmten Schwellenwert überschreitet. Wenn Sie einen Schwellenwert definieren möchten, empfehlen wir Ihnen, Ihre Trends zu beobachten und einen Wert zu verwenden, der auf Ihrer normalen Workload basiert.

  • Performance Insights – Wenn Sie Performance Insights verwenden, können Sie die Metriken innodb_deadlocks und innodb_lock_wait_timeout überwachen. Weitere Informationen zu diesen Metriken, finden Sie unter Nicht-native Zähler für Aurora MySQL.