Empfehlungen für Meine SQL Funktionen in Aurora My SQL - Amazon Aurora
Verwenden der Multithread-Replikation in Aurora My SQLAufrufen AWS Lambda Funktionen, die native SQL My-Funktionen verwendenVermeidung von XA-Transaktionen mit Amazon Aurora My SQLFremdschlüssel bei DML Kontoauszügen aktiviert lassenKonfigurieren, wie oft der Protokollpuffer geleert wirdMinimierung und Behebung von Aurora My-Deadlocks SQL

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 Meine SQL Funktionen in Aurora My SQL

Die folgenden Funktionen sind in der SQL Kompatibilität mit Aurora My SQL for My 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 der Multithread-Replikation in Aurora My SQL

Bei der binären Protokollreplikation mit mehreren SQL Threads liest ein Thread Ereignisse aus dem Relay-Log und stellt sie in eine Warteschlange, damit SQL Worker-Threads sie anwenden 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 My SQL Version 3 und in Aurora My SQL Version 2.12.1 und höher unterstützt.

Für Aurora SQL My-Versionen unter 3.04 verwendet Aurora standardmäßig die Single-Thread-Replikation, wenn ein Aurora My SQL DB-Cluster als Read Replica für die binäre Protokollreplikation verwendet wird.

Frühere Versionen von Aurora My SQL Version 2 haben mehrere Probleme mit der Multithread-Replikation aus My SQL Community Edition übernommen. Für diese Versionen empfehlen wir, die Multithread-Replikation in der Produktion nicht zu verwenden.

Wenn Sie die Multithread-Replikation verwenden, empfehlen wir, sie 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 My finden Sie SQL unterReplikation von Binärprotokollen mit mehreren Threads.

Aufrufen AWS Lambda Funktionen, die native SQL My-Funktionen verwenden

Wir empfehlen, die nativen SQL My-Funktionen lambda_async zu verwenden lambda_sync und 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 My SQL DB-Cluster.

Vermeidung von XA-Transaktionen mit Amazon Aurora My SQL

Wir empfehlen, keine eXtended Architecture (XA) -Transaktionen mit Aurora My zu verwendenSQL, da sie zu langen Wiederherstellungszeiten führen können, wenn sich die XA im PREPARED Status befindet. Wenn Sie XA-Transaktionen mit Aurora My verwenden müssenSQL, befolgen Sie diese bewährten Methoden:

  • 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 My SQL finden Sie unter XA-Transaktionen in der SQL Dokumentation zu My.

Fremdschlüssel bei DML Kontoauszügen aktiviert lassen

Es wird dringend empfohlen, keine Anweisungen in der Datendefinitionssprache (DDL) auszuführen, wenn die foreign_key_checks Variable 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 Änderungen an Ihrer Datenmanipulationssprache (DML) 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 My SQL 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 trägt dazu bei, dass die Datenbank ACIDkonform bleibt. Wir empfehlen, die Standardeinstellung 1 beizubehalten.

Die Änderung innodb_flush_log_at_trx_commit zu einem anderen Wert als dem Standardwert kann dazu beitragen, die Latenz der Datenmanipulationssprache (DML) zu reduzieren, beeinträchtigt jedoch die Haltbarkeit der Protokolldatensätze. Aufgrund dieser mangelnden Beständigkeit ist die Datenbank ACID nicht konform. Wir empfehlen, dass Ihre Datenbanken ACID konform sind, um das Risiko eines Datenverlusts bei einem Serverneustart zu vermeiden. Weitere Informationen zu diesem Parameter finden Sie unter innodb_flush_log_at_trx_commit in der Dokumentation My. SQL

In Aurora My SQL wird die Redo-Log-Verarbeitung auf die Speicherschicht ausgelagert, sodass keine Protokolldateien auf der DB-Instance gespeichert werden. 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 eine Transaktion festschreibt, auf die Bestätigung durch das 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 My speichert SQL keine Protokolle in Datendateien, wie dies bei My SQL 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 My SQL Version 2:

  • innodb_flush_log_at_trx_commit= 0 oder 2 — Die Datenbank wartet nicht auf die Bestätigung, dass die Redo-Log-Datensä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-Log-Datensätze auf das Aurora-Cluster-Volume geschrieben wurden.

Für Aurora My SQL Version 3:

  • innodb_flush_log_at_trx_commit= 0 — Die Datenbank wartet nicht auf die Bestätigung, dass die Redo-Log-Datensä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-Log-Datensätze auf das Aurora-Cluster-Volume geschrieben wurden.

Um in Aurora My SQL Version 3 dasselbe nicht standardmäßige Verhalten zu erzielen, das Sie mit dem Wert 0 oder 2 in Aurora My SQL 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 aber auch zu Datenverlust im Falle eines Failovers oder Neustarts führen. Daher empfehlen wir, für den Parameter innodb_flush_log_at_trx_commit den Standardwert 1 beizubehalten.

Datenverlust kann zwar sowohl in My SQL Community Edition als auch in Aurora My auftretenSQL, das Verhalten unterscheidet sich jedoch in jeder Datenbank aufgrund ihrer unterschiedlichen Architekturen. Diese Unterschiede in der Architektur können zu unterschiedlich starkem Datenverlust führen. Um sicherzustellen, dass Ihre Datenbank ACID konform ist, sollten Sie immer innodb_flush_log_at_trx_commit auf 1 setzen.

Anmerkung

Bevor Sie in Aurora My SQL Version 3 innodb_flush_log_at_trx_commit zu einem anderen Wert als 1 wechseln 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.

Minimierung und Behebung von Aurora My-Deadlocks SQL

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 Timeouts sind auf eine Fehlerkorrektur in My SQL Community Edition zurückzuführen.

Dieser Fix ist in den Versionen 5.7.26 und höher von My SQL Community Edition enthalten und wurde in die Aurora SQL My-Versionen 2.10.3 und höher zurückportiert. Das Update ist notwendig, um die Serialisierbarkeit durchzusetzen, indem zusätzliche Sperren für diese Art 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 eine frühere Fehlerkorrektur in My Community Edition verursacht wurden. SQL

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 Dokumentation Meine SQL.

  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 sie zu verwendenREPLACE INTO, schreiben Sie die SQL Anweisung als Transaktion mit mehreren Anweisungen um, z. B. wie folgt:

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

    3. Anstatt zu verwendenINSERT...ON DUPLICATE KEY UPDATE, schreiben Sie die SQL Anweisung als Transaktion mit mehreren Anweisungen um, z. B. wie folgt:

      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 „Meine SQL Kunden“ -Sitzungen, die möglicherweise über einen längeren Zeitraum geöffnet sind und eine Transaktion noch nicht festgeschrieben hat. 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 Isolationsstufen für Transaktionen in der SQL Dokumentation Meine.

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 My auftreten, SQL wenn Anwendungstransaktionen versuchen, Sperren auf Tabellen- und Zeilenebene so zu umgehen, dass es zu zirkulären Wartezeiten kommt. 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.