Vérifier quelles instructions utilisent une requête parallèle pour Aurora My SQL

En règle générale, aucune action spécifique n'est requise de votre part pour tirer parti des requêtes parallèles. Lorsqu'une requête est compatible avec la fonction de requête parallèle, l'optimiseur détermine automatiquement dans quel cas utiliser cette fonction pour chaque requête.

Si vous effectuez des expérimentations dans un environnement de développement ou de test, vous constaterez peut-être que la fonction de requête parallèle n'est pas utilisée, car vos tables ne contiennent pas assez de lignes ou pas assez de données. Les données associées à la table, notamment celles que vous avez créées récemment pour réaliser ces expérimentations, peuvent également se trouver entièrement dans un pool de mémoires tampons.

À mesure que vous surveillez ou ajustez les performances de vos clusters, veillez à déterminer si les requêtes parallèles sont déclenchées dans les contextes appropriés. Vous pouvez ajuster le schéma de base de données, les paramètres, les SQL requêtes ou même la topologie du cluster et les paramètres de connexion aux applications pour tirer parti de cette fonctionnalité.

Pour vérifier si une requête utilise une requête parallèle, vérifiez le plan de requête (également appelé « plan d'explication ») en exécutant l'EXPLAINinstruction. Pour des exemples illustrant la façon dont SQL les instructions, les clauses et les expressions affectent le EXPLAIN résultat d'une requête parallèle, consultezSQLconstructions pour les requêtes parallèles dans Aurora My SQL.

L'exemple suivant illustre la différence entre un plan de requête traditionnel et un plan de requête parallèle. Ce plan d'explication provient de la requête 3 du benchmark TPC -H. La plupart des exemples de requêtes présentés dans cette section utilisent les tables du jeu de données TPC -H. Vous pouvez obtenir les définitions des tables, les requêtes et le dbgen programme qui génère des exemples de données à partir du site Web TPC -h.

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;

Par défaut, la requête peut avoir un plan semblable au suivant. Si vous ne voyez pas la jointure par hachage utilisée dans le plan de requête, assurez-vous que l'optimisation est activée en premier.

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| 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) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

Pour Aurora My SQL version 3, vous activez la jointure par hachage au niveau de la session en émettant l'instruction suivante.

SET optimizer_switch='block_nested_loop=on';

Pour Aurora My SQL version 2.09 et versions ultérieures, vous définissez le paramètre de aurora_disable_hash_join base de données ou le paramètre de cluster de base de données sur 0 (off). La désactivation de aurora_disable_hash_join définit optimizer_switch sur la valeur hash_join=on.

Après avoir activé la jointure par hachage, réessayez d'exécuter l'instruction EXPLAIN. Pour de plus amples informations sur la façon d'utiliser les jointures par hachage efficacement, consultez Optimisation des requêtes Aurora My SQL join de grande taille avec des jointures par hachage.

Lorsque la jointure par hachage est activée et que la fonction de requête parallèle est désactivée, la requête peut avoir un plan d'exécution semblable au suivant, qui utilise la jointure par hachage, mais pas la requête parallèle.

+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
| 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) |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+

Lorsque la fonction de requête parallèle est activée, deux étapes de ce plan de requête peuvent utiliser l'optimisation de requête parallèle, comme l'indique la colonne Extra de la sortie EXPLAIN. Le traitement CPU intensif en E/S associé à ces étapes est transféré jusqu'à la couche de stockage.

+----+...+--------------------------------------------------------------------------------------------------------------------------------+
| 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) |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+

Pour plus d'informations sur la façon d'interpréter le EXPLAIN résultat d'une requête parallèle et sur les parties des SQL instructions auxquelles la requête parallèle peut s'appliquer, consultezSQLconstructions pour les requêtes parallèles dans Aurora My SQL.

L'exemple de sortie suivant présente les résultats de l'exécution de la requête précédente au niveau d'une instance db.r4.2xlarge avec un pool de mémoires tampons à froid. L'exécution de la requête est beaucoup plus rapide avec la fonction de requête parallèle.

Note

Comme la durée dépend de nombreux facteurs environnementaux, vos résultats peuvent différer. Menez toujours vos propres tests de performances pour confirmer ces résultats avec votre propre environnement, votre charge de travail, etc.

-- 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)

La plupart des exemples de requêtes présentés dans cette section utilisent les tables de cet ensemble de données TPC -H, en particulier la PART table, qui comporte 20 millions de lignes et la définition suivante.

+---------------+---------------+------+-----+---------+-------+
| 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    |       |
+---------------+---------------+------+-----+---------+-------+

Testez votre charge de travail pour savoir si des SQL instructions individuelles peuvent tirer parti des requêtes parallèles. Utilisez ensuite les techniques de surveillance suivantes pour identifier la fréquence d'utilisation des requêtes parallèles dans les charges de travail réelles au fil du temps. Pour les charges de travail réelles, des facteurs supplémentaires tels que les limites de simultanéité s'appliquent.