Überprüfen, welche Anweisungen die parallel Abfrage für Aurora My verwenden SQL

Im normalen Betrieb müssen Sie nichts Besonderes unternehmen, um Parallel Query zu nutzen. Erfüllt eine Abfrage die grundlegenden Anforderungen einer Parallelabfrage, entscheidet der Abfrageoptimierer bei jeder Abfrage automatisch, ob Parallel Query verwendet werden soll.

Wenn parallele Abfragen in Versuchen in einer Entwicklungs- oder Testumgebung nicht verwendet werden, kann dies daran liegen, dass Ihre Tabellen zu klein sind (zu wenige Zeilen oder zu wenig Datenvolumen). Die Daten für die Tabelle können sich auch vollständig im Pufferpool befinden, insbesondere bei Tabellen, die Sie kürzlich erstellt haben, um Experimente durchzuführen.

Während Sie die Cluster-Leistung überwachen oder optimieren, müssen Sie entscheiden, ob parallele Abfragen in den richtigen Kontexten verwendet werden. Sie können das Datenbankschema, die Einstellungen, SQL Abfragen oder sogar die Clustertopologie und die Anwendungsverbindungseinstellungen anpassen, um diese Funktion nutzen zu können.

Um zu überprüfen, ob eine Abfrage eine parallel Abfrage verwendet, überprüfen Sie den Abfrageplan (auch als „Erläuterungsplan“ bezeichnet), indem Sie die EXPLAINAnweisung ausführen. Beispiele dafür, wie sich SQL Anweisungen, Klauseln und Ausdrücke auf die EXPLAIN Ausgabe für parallel Abfragen auswirken, finden Sie unterSQLKonstrukte für parallel Abfragen in Aurora My SQL.

Das nachfolgende Beispiel verdeutlicht den Unterschied zwischen einem normalen Abfrageplan und einem parallelen Abfrageplan (Parallel Query). Dieser Plan zur Erläuterung stammt aus Query 3 aus dem TPC -H-Benchmark. Viele der Beispielabfragen in diesem Abschnitt verwenden die Tabellen aus dem TPC -H-Datensatz. Sie können die Tabellendefinitionen, Abfragen und das dbgen Programm, das Beispieldaten generiert, von der TPC -h-Website abrufen.

EXPLAIN SELECT l_orderkey,
  sum(l_extendedprice * (1 - l_discount)) AS revenue,
  o_orderdate,
  o_shippriority
FROM customer,
  orders,
  lineitem
WHERE c_mktsegment = 'AUTOMOBILE'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < date '1995-03-13'
AND l_shipdate > date '1995-03-13'
GROUP BY l_orderkey,
  o_orderdate,
  o_shippriority
ORDER BY revenue DESC,
  o_orderdate LIMIT 10;

Standardmäßig hat die Abfrage möglicherweise einen Plan wie den folgenden. Wenn im Abfrageplan kein Hash-Join verwendet wird, stellen Sie sicher, dass zuerst die Optimierung aktiviert ist.

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  1480234 |    10.00 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | orders   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 14875240 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59270573 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

Für Aurora My SQL Version 3 aktivieren Sie den Hash-Join auf Sitzungsebene, indem Sie die folgende Anweisung ausgeben.

SET optimizer_switch='block_nested_loop=on';

Für Aurora My SQL Version 2.09 und höher setzen Sie den aurora_disable_hash_join DB-Parameter oder den DB-Cluster-Parameter auf 0 (off). Durch Deaktivierung von aurora_disable_hash_join wird der Wert von optimizer_switch auf hash_join=on gesetzt.

Nachdem Sie den Hash-Join aktiviert haben, versuchen Sie erneut, die EXPLAIN-Anweisung auszuführen. Informationen zur effektiven Verwendung von Hash-Joins finden Sie unter Optimierung großer Aurora My SQL Join-Abfragen mit Hash-Joins.

Bei aktiviertem Hash-Join, aber deaktivierter paralleler Abfrage kann die Abfrage einen Plan wie den folgenden haben, der Hash-Join, aber keine parallele Abfrage verwendet.

+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
| id | select_type | table    |...| rows      | Extra                                                           |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | customer |...|   5798330 | Using where; Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | orders   |...| 154545408 | Using where; Using join buffer (Hash Join Outer table orders)   |
|  1 | SIMPLE      | lineitem |...| 606119300 | Using where; Using join buffer (Hash Join Outer table lineitem) |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+

Nachdem die parallele Abfrage aktiviert wurde, können zwei Schritte in diesem Abfrageplan die parallele Abfrageoptimierung verwenden, wie unter der Spalte Extra in der Ausgabe EXPLAIN angezeigt. Die I/O-intensive und CPU -intensive Verarbeitung für diese Schritte wird auf die Speicherschicht verlagert.

+----+...+--------------------------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                                          |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using index; Using temporary; Using filesort                                                                      |
|  1 |...| Using where; Using join buffer (Hash Join Outer table orders); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra)   |
|  1 |...| Using where; Using join buffer (Hash Join Outer table lineitem); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+

Hinweise zur Interpretation der EXPLAIN Ausgabe für eine parallel Abfrage und zu den Teilen von SQL Anweisungen, für die parallel Abfragen gelten können, finden Sie unterSQLKonstrukte für parallel Abfragen in Aurora My SQL.

Aus der nachfolgenden Beispielausgabe geht hervor, welche Ergebnisse zustande kommen, wenn die vorherige Abfrage auf einer db.r4.2xlarge-Instance mit kaltem Bufferpool ausgeführt wird. Die Abfrage wird mit Parallel Query erheblich schneller ausgeführt.

Anmerkung

Weil Durchlaufzeiten von vielen Umgebungsfaktoren abhängen, können Ihre Ergebnisse anders lauten. Führen Sie deshalb stets eigene Leistungstests durch. Damit prüfen Sie die Ergebnisse gegen Ihre eigene Umgebung, Workloads und andere Faktoren.

-- Without parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   92511430 | 514726.4896 | 1995-03-06  |              0 |
.
.
|   28840519 | 454748.2485 | 1995-03-08  |              0 |
+------------+-------------+-------------+----------------+
10 rows in set (24 min 49.99 sec)

-- With parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   92511430 | 514726.4896 | 1995-03-06  |              0 |
.
.
|   28840519 | 454748.2485 | 1995-03-08  |              0 |
+------------+-------------+-------------+----------------+
10 rows in set (1 min 49.91 sec)

Viele der Beispielabfragen in diesem Abschnitt verwenden die Tabellen aus diesem TPC -H-Datensatz, insbesondere die PART Tabelle mit 20 Millionen Zeilen und der folgenden Definition.

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| p_partkey     | int(11)       | NO   | PRI | NULL    |       |
| p_name        | varchar(55)   | NO   |     | NULL    |       |
| p_mfgr        | char(25)      | NO   |     | NULL    |       |
| p_brand       | char(10)      | NO   |     | NULL    |       |
| p_type        | varchar(25)   | NO   |     | NULL    |       |
| p_size        | int(11)       | NO   |     | NULL    |       |
| p_container   | char(10)      | NO   |     | NULL    |       |
| p_retailprice | decimal(15,2) | NO   |     | NULL    |       |
| p_comment     | varchar(23)   | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

Experimentieren Sie mit Ihrer Arbeitslast, um ein Gefühl dafür zu bekommen, ob einzelne SQL Anweisungen die Vorteile parallel Abfragen nutzen können. Anschließend können Sie mit den folgenden Überwachungstechniken nachprüfen, wie oft Parallel Query im Laufe der Zeit in echten Workloads verwendet wird. Für tatsächliche Workloads gelten zusätzliche Faktoren (z. B. Obergrenzen für gleichzeitige Abfragen).