Bewährte Verfahren für die Leistung und Skalierung von Aurora MySQL

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.

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.

Sie können die folgenden bewährten Verfahren anwenden, um die Leistung und Skalierbarkeit Ihrer Aurora-MySQL-Cluster zu verbessern.

Verwendung von T-Instance-Klassen für Entwicklung und Tests

Amazon Aurora MySQL-Instances, die die DB-Instanzklassen db.t2, db.t3 oder db.t4g verwenden, eignen sich am besten für Anwendungen, die nicht über einen längeren Zeitraum eine hohe Arbeitslast unterstützen. Die T-Instanzen sind so konzipiert, dass sie eine mäßige Basisleistung bieten und je nach Workload eine deutlich höhere Leistung erreichen können. Sie eignen sich für Workloads, die die volle CPU-Leistung selten oder uneinheitlich verwenden, jedoch gelegentlich Spitzenlasten verarbeiten müssen. Wir empfehlen, die T-DB-Instance-Klassen nur für Entwicklungs- und Testserver oder andere Nicht-Produktionsserver zu verwenden. Weitere Einzelheiten zu den T-Instance-Klassen finden Sie unter Burstable Performance Instances.

Wenn Ihr Aurora-Cluster größer als 40 TB ist, sollten Sie die T-Instance-Klassen nicht verwenden. Wenn Ihre Datenbank ein großes Datenvolumen hat, kann der Speicher-Overhead für die Verwaltung von Schemaobjekten die Kapazität einer T-Instance übersteigen.

Aktivieren Sie das MySQL-Leistungsschema nicht auf Amazon Aurora MySQL T-Instances. Wenn das Leistungsschema aktiviert ist, geht der Instance möglicherweise der Speicher aus.

Wenn Sie eine T-Instance als DB-Instance in einem Aurora-MySQL DB-Cluster verwenden, empfehlen wir Folgendes:

Optimierung von mit Aurora MySQL indizierten Join-Abfragen mit asynchronem Key Prefetch

Aurora MySQL kann die asynchrone Key Prefetch (AKP)-Funktion verwenden, um die Performance von Abfragen zu verbessern, die Tabellen über Indizes hinweg verknüpfen. Diese Funktion verbessert die Leistung, indem sie die Zeilen vorwegnimmt, die zum Ausführen von Abfragen benötigt werden, bei denen eine JOIN-Abfrage die Verwendung des Batched Key Access (BKA) Join-Algorithmus und der Multi-Range Read (MRR)-Optimierungsfunktionen erfordert. Weitere Informationen zu BKA und MRR finden Sie unter Block Nested-Loop und Batched Key Access Joins und Multi-Range Read Optimization in der MySQL-Dokumentation.

Um einen Vorteil aus der AKP-Funktion zu ziehen, muss eine Abfrage sowohl BKA als auch MRR verwenden. Typischerweise tritt eine solche Abfrage auf, wenn die JOIN-Klausel einer Abfrage einen Sekundärindex verwendet, aber auch einige Spalten aus dem Primärindex benötigt. Sie können beispielsweise AKP verwenden, wenn eine JOIN-Klausel einen Equijoin auf Indexwerten zwischen einer kleinen äußeren und einer großen inneren Tabelle darstellt und der Index auf der größeren Tabelle hoch selektiv ist. AKP arbeitet mit BKA und MRR zusammen, um während der Evaluierung der JOIN-Klausel einen sekundären Index-Lookup durchzuführen. AKP identifiziert die Zeilen, die während der Auswertung der JOIN-Klausel benötigt werden, um die Abfrage auszuführen. Anschließend werden die Seiten, die diese Zeilen enthalten, mit einem Hintergrund-Thread asynchron in den Speicher geladen, bevor die Abfrage ausgeführt wird.

AKP ist für Aurora MySQL Version 2.10 und höher sowie Version 3 verfügbar. Weitere Informationen zu den Aurora MySQL-Versionen erhalten Sie unter Datenbank-Engine-Updates für Amazon Aurora My SQL.

Asynchrones Key Prefetch aktivieren

Sie können die AKP-Funktion aktivieren, indem Sie aurora_use_key_prefetch, eine MySQL-Servervariable, auf on setzen. Standardmäßig ist dieser Wert auf on festgelegt. AKP kann jedoch erst aktiviert werden, wenn Sie auch den BKA-Join-Algorithmus aktivieren und die preisbasierte MRR-Funktionalität deaktivieren. Dazu müssen Sie die folgenden Werte für optimizer_switch setzen, eine Variable von MySQL-Server:

Derzeit können Sie diese Werte nur auf Sitzungsebene festlegen. Das folgende Beispiel veranschaulicht, wie diese Werte gesetzt werden können, um AKP für die aktuelle Sitzung durch Ausführen von SET-Anweisungen zu aktivieren.

mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

Ähnlich können Sie mit SET-Anweisungen AKP und den BKA Join-Algorithmus deaktivieren und kostenbasierte MRR-Funktionalität für die aktuelle Sitzung wieder aktivieren, wie im folgenden Beispiel gezeigt.

mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

Weitere Informationen über die Optimierungsschalter batched_key_access und mrr_cost_based finden Sie unter Switchable Optimizations in der MySQL-Dokumentation.

Optimieren von Abfragen für asynchrones Key Prefetch

Sie können bestätigen, ob eine Abfrage die AKP-Funktion nutzen können soll. Verwenden Sie dazu die EXPLAIN-Anweisung, um die Abfrage vor der Ausführung zu profilieren. Die EXPLAIN-Anweisung stellt Informationen über den Ausführungsplan bereit, der für eine angegebene Abfrage verwendet werden soll.

In der Ausgabe der EXPLAIN-Anweisung beschreibt die Spalte Extra zusätzliche Informationen, die im Ausführungsplan eingeschlossen sind. Wenn das AKP-Feature auf eine in der Abfrage verwendete Tabelle zutrifft, enthält diese Spalte einen der folgenden Werte:

Das folgende Beispiel zeigt die Verwendung von EXPLAIN, um den Ausführungsplan für eine Abfrage anzuzeigen, die von AKP profitieren kann.

mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

Weitere Informationen über das EXPLAIN-Ausgabeformat finden Sie unter Extended EXPLAIN Output Format in der MySQL-Dokumentation.

Optimierung von großen Aurora-MySQL-Join-Abfragen mit Hash-Joins

Wenn Sie eine große Datenmenge mit Hilfe eines Equijoins verknüpfen müssen, kann ein Hash-Join die Abfrageleistung verbessern. Sie können Hash-Joins für Aurora MySQL aktivieren.

Eine Hash-Join-Spalte kann ein beliebiger komplexer Ausdruck sein. In einer Hash-Join-Spalte haben Sie folgende Möglichkeiten, Datentypen übergreifend zu vergleichen:

Die folgenden Einschränkungen gelten für Hash-Joins für Aurora MySQL:

Aktivieren von Hash-Joins

So aktivieren Sie Hash-Joins:

Hash-Joins sind standardmäßig in Aurora-MySQL-Version 3 aktiviert und in Aurora-MySQL-Version 2 deaktiviert. Das folgende Beispiel zeigt, wie man Hash-Joins für Aurora-MySQL-Version 3 aktivieren kann. Sie können zuerst die Anweisung select @@optimizer_switch ausgeben, um zu sehen, welche anderen Einstellungen in der SET-Parameterzeichenfolge vorhanden sind. Das Aktualisieren einer Einstellung im Parameter optimizer_switch löscht oder ändert die anderen Einstellungen nicht.

mysql> SET optimizer_switch='block_nested_loop=on';

Mit dieser Einstellung wählt der Optimierer einen Hash-Join auf der Grundlage von Kosten, Abfragemerkmalen und Ressourcenverfügbarkeit. Wenn die Kalkulation fehlerhaft ist, können Sie den Optimierer zwingen, einen bestimmten Hash-Join zu wählen. Sie erreichen dies, indem Sie hash_join_cost_based, eine MySQL-Servervariable, auf off setzen. Das folgende Beispiel zeigt, wie Sie den Optimierer zwingen können, einen Hash-Join zu wählen.

mysql> SET optimizer_switch='hash_join_cost_based=off';

Optimieren von Abfragen für Hash-Joins

Um herauszufinden, ob eine Abfrage einen Hash-Join nutzen kann, verwenden Sie die EXPLAIN-Anweisung, um die Abfrage zuerst zu profilieren. Die EXPLAIN-Anweisung stellt Informationen über den Ausführungsplan bereit, der für eine angegebene Abfrage verwendet werden soll.

In der Ausgabe der EXPLAIN-Anweisung beschreibt die Spalte Extra zusätzliche Informationen, die im Ausführungsplan eingeschlossen sind. Wenn ein Hash-Join für die in der Abfrage verwendeten Tabellen gilt, enthält diese Spalte Werte, die den folgenden ähnlich sind:

Das folgende Beispiel zeigt die Verwendung von EXPLAIN, um den Ausführungsplan für eine Hash-Join-Abfrage anzuzeigen.

mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

In der Ausgabe ist der Hash Join Inner table die Tabelle, die zum Aufbau der Hash-Tabelle verwendet wird, und der Hash Join Outer table ist die Tabelle, die zum Prüfen der Hash-Tabelle verwendet wird.

Weitere Informationen über das erweiterte EXPLAIN-Ausgabeformat finden Sie unter Extended EXPLAIN Output Format in der MySQL-Produktdokumentation.

In Aurora MySQL 2.08 und höher können Sie mithilfe von SQL-Hinweisen beeinflussen, ob eine Abfrage einen Hash-Join verwendet oder nicht und welche Tabellen für die Build- und Testseite des Joins verwendet werden sollen. Details hierzu finden Sie unter Aurora Meine SQL Tipps.

Verwenden von Amazon Aurora für das Skalieren von Lesevorgängen in Ihrer MySQL-Datenbank

Sie können Amazon Aurora mit Ihrer MySQL-DB-Instance verwenden, um die Möglichkeiten der Skalierung von Lesevorgängen von Amazon Aurora zu nutzen und den Lese-Workload für Ihre MySQL-DB-Instance zu erweitern. Erstellen Sie einen Aurora-MySQL-DB-Cluster und machen Sie ihn zum Read Replica Ihrer MySQL-DB-Instance, um Aurora für die Skalierung von Lesevorgängen Ihrer MySQL-DB-Instance zu verwenden. Verbinden Sie sich dann mit dem Aurora-MySQL-Cluster, um die Leseabfragen zu verarbeiten. Die Quelldatenbank kann eine RDS for MySQL-DB-Instance oder eine MySQL-Datenbank sein, die außerhalb von Amazon RDS ausgeführt wird. Weitere Informationen finden Sie unter Skalierung von Lesevorgängen für Ihre My SQL Database mit Amazon Aurora.

Optimierung von Zeitstempeloperationen

Wenn der Wert der Systemvariablen time_zone auf SYSTEM gesetzt ist, führt jeder MySQL-Funktionsaufruf, der eine Zeitzonenberechnung erfordert, einen Systembibliotheksaufruf aus. Wenn Sie SQL-Anweisungen ausführen, die solche TIMESTAMP-Werte mit hoher Nebenläufigkeit zurückgeben oder ändern, kann es zu einer höheren Latenz und CPU-Auslastung sowie zu Sperrkonflikten kommen. Weitere Informationen finden Sie unter time_zone in der MySQL-Dokumentation.

Wir empfehlen, den Wert des DB-Cluster-Parameters time_zone in UTC zu ändern, um dieses Verhalten zu vermeiden. Weitere Informationen finden Sie unter Ändern von Parametern in einer DB-Cluster-Parametergruppe in Amazon Aurora.

Der Parameter time_zone ist zwar dynamisch (er erfordert keinen Neustart des Datenbankservers), der neue Wert wird jedoch nur für neue Verbindungen verwendet. Um sicherzustellen, dass alle Verbindungen so aktualisiert werden, dass sie den neuen time_zone-Wert verwenden, empfehlen wir, Ihre Anwendungsverbindungen nach der Aktualisierung des DB-Cluster-Parameters wiederzuverwenden.

Fehler beim Überlauf der virtuellen Index-ID

Aurora MySQL begrenzt die Werte für den virtuellen Index IDs auf 8 Bit und verhindert ein Problem, das durch das Undo-Format in MySQL verursacht wird. Wenn ein Index das ID-Limit für den virtuellen Index überschreitet, ist Ihr Cluster möglicherweise nicht verfügbar. Wenn sich ein Index dem ID-Limit für den virtuellen Index nähert oder wenn Sie versuchen, einen Index zu erstellen, der über dem ID-Limit für den virtuellen Index liegt, gibt RDS möglicherweise einen Fehler 63955 - oder Warncode aus63955. Um einen Fehler beim ID-Limit für den virtuellen Index zu beheben, empfehlen wir Ihnen, Ihre Datenbank mit einem logischen Dump und Restore neu zu erstellen.

Weitere Informationen zu logischem Dump und Restore für Amazon Aurora MySQL finden Sie unter Migrieren sehr großer Datenbanken zu Amazon Aurora MySQL mithilfe von MyDumper und MyLoader. Weitere Informationen zum Zugriff auf Fehlerprotokolle in Amazon Aurora finden Sie unterÜberwachung von Aurora Aurora-Protokolldateien.