creating sort index

L'état de thread creating sort index pour indique qu'un thread traite une instruction SELECT qui requiert l'utilisation d'une table temporaire interne pour trier les données.

Versions de moteur prises en charge

Ces informations relatives aux états de thread sont prises en charge dans les versions suivantes :

• Aurora MySQL versions 2 à 2.09.2

Contexte

L'état creating sort index apparaît lorsqu'une requête avec une clause ORDER BY ou GROUP BY ne peut pas utiliser un index existant pour effectuer l'opération. Dans ce cas, MySQL doit effectuer une opération filesort plus coûteuse. Cette opération s'effectue généralement en mémoire si l'ensemble de résultats n'est pas trop volumineux. Sinon, elle implique la création d'un fichier sur disque.

Causes probables de l'augmentation du nombre d'événements d'attente

L'apparition de creating sort index n'indique pas en soi un problème. Si les performances sont médiocres et que les instances de creating sort index se multiplient, il y a fort à parier que cela soit dû à la lenteur des requêtes avec les opérateurs ORDER BY ou GROUP BY.

Actions

De manière générale, nous vous conseillons de déterminer les requêtes avec des clauses ORDER BY ou GROUP BY associées aux augmentations de l'état creating sort index. Voyez ensuite si l'ajout d'un index ou l'augmentation de la taille du tampon de tri permet de résoudre le problème.

Rubriques

• Activer le schéma de performance, le cas échéant
• Identifier les requêtes problématiques
• Examiner les plans d'explication de l'utilisation de filesort
• Augmenter la taille du tampon de tri

Activer le schéma de performance, le cas échéant

Performance Insights signale les états de thread uniquement si les instruments du schéma de performance ne sont pas activés. Lorsque les instruments du schéma de performance sont activés, Performance Insights signale plutôt les événements d'attente. Les instruments du schéma de performance fournissent des informations supplémentaires et de meilleurs outils pour examiner les possibles problèmes de performances. Par conséquent, nous vous recommandons d'activer le schéma de performance. Pour de plus amples informations, veuillez consulter Activation du schéma de performance pour Performance Insights sur Aurora MySQL.

Identifier les requêtes problématiques

Pour identifier les requêtes actuelles entraînant des augmentations de l'état creating sort index, exécutez show processlist et vérifiez si l'une des requêtes est accompagnées de ORDER BY ou GROUP BY. Vous pouvez également exécuter explain for connection N, où N correspond à l'ID de liste de processus de la requête avec filesort.

Pour identifier les requêtes antérieures à l'origine de ces augmentations, activez le journal des requêtes lentes et recherchez les requêtes avec ORDER BY. Exécutez EXPLAIN sur les requêtes lentes et recherchez « using filesort ». Pour de plus amples informations, veuillez consulter Examiner les plans d'explication de l'utilisation de filesort.

Examiner les plans d'explication de l'utilisation de filesort

Identifiez les déclarations accompagnées de clauses ORDER BY ou GROUP BY aboutissant à l'état creating sort index.

L'exemple suivant illustre l'exécution de explain sur une requête. La colonne Extra indique que cette requête utilise filesort.

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2064548
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

L'exemple suivant illustre le résultat de l'exécution de EXPLAIN sur la même requête après la création d'un index sur la colonne c1.

mysql> alter table mytable add index (c1);

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: index
possible_keys: NULL
          key: c1
      key_len: 1023
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

Pour plus d'informations sur l'utilisation des index à des fins d'optimisation de l'ordre de tri, consultez ORDER BY Optimization dans la documentation MySQL.

Augmenter la taille du tampon de tri

Pour savoir si une requête spécifique a nécessité un processus filesort qui a créé un fichier sur disque, vérifiez la valeur de la variable sort_merge_passes après l'exécution de la requête. Vous en trouverez un exemple ci-dessous.

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

--- run query
mysql> select * from mytable order by u limit 10; 
--- run status again:

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

Si la valeur de sort_merge_passes est élevée, envisagez d'augmenter la taille du tampon de tri. Appliquez l'augmentation au niveau de la session, car une augmentation globale peut considérablement accroître la quantité de RAM utilisée par MySQL. L'exemple suivant montre comment modifier la taille du tampon de tri avant d'exécuter une requête.

mysql> set session sort_buffer_size=10*1024*1024;
Query OK, 0 rows affected (0.00 sec)
-- run query