Nouveau comportement des tables temporaires dans Aurora My SQL version 3 - Amazon Aurora
Moteur de stockage pour tables temporaires internes (implicites)Limitation de la taille des tables temporaires internes en mémoireAtténuation des problèmes de remplissage pour les tables temporaires internes sur les réplicas AuroraTables temporaires (explicites) créées par l'utilisateur sur les instances de base de données de lecteurErreurs de création d'une table temporaire et atténuation

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Nouveau comportement des tables temporaires dans Aurora My SQL version 3

Aurora My SQL version 3 gère les tables temporaires différemment des SQL versions antérieures d'Aurora My. Ce nouveau comportement est hérité de My SQL 8.0 Community Edition. Il existe deux types de tables temporaires qui peuvent être créées avec Aurora My SQL version 3 :

  • Tables temporaires internes (ou implicites) : créées par le SQL moteur Aurora My pour gérer des opérations telles que le tri, l'agrégation, les tables dérivées ou les expressions de table courantes (CTEs).

  • Tables temporaires créées par l'utilisateur (ou explicites) : créées par le SQL moteur Aurora My lorsque vous utilisez l'CREATE TEMPORARY TABLEinstruction.

Il existe des considérations supplémentaires pour les tables temporaires internes et créées par l'utilisateur sur les instances de base de données de lecteur Aurora. Ces modifications sont présentées dans les sections suivantes.

Moteur de stockage pour tables temporaires internes (implicites)

Lors de la génération d'ensembles de résultats intermédiaires, Aurora My tente SQL initialement d'écrire dans des tables temporaires en mémoire. Cela peut échouer, en raison de types de données incompatibles ou de limites configurées. Si c'est le cas, la table temporaire est convertie en table temporaire sur disque plutôt que d'être conservée en mémoire. Vous trouverez plus d'informations à ce sujet dans la section Utilisation interne des tables temporaires dans la SQL documentation My SQL in the My.

Dans Aurora My SQL version 3, le fonctionnement des tables temporaires internes est différent de celui des SQL versions antérieures d'Aurora My. Au lieu de choisir entre les moteurs de ISAM stockage InnoDB et My pour ces tables temporaires, vous pouvez désormais choisir entre les moteurs de MEMORY stockage TempTable et.

Avec le moteur de stockage TempTable, vous disposez d'un choix supplémentaire pour gérer certaines données. Les données affectées dépassent la capacité du pool de mémoire qui contient toutes les tables temporaires internes de l'instance de base de données.

Ces choix peuvent influencer les performances des requêtes qui génèrent des volumes élevés de données temporaires, par exemple lors de l'exécution d'agrégations telles que GROUP BY sur des tables volumineuses.

Astuce

Si votre charge de travail inclut des requêtes qui génèrent des tables temporaires internes, confirmez les performances de votre application avec cette modification en exécutant des définitions de points de référence et en surveillant les métriques liées aux performances.

Dans certains cas, la quantité de données temporaires correspond à la capacité du pool de mémoire TempTable ou est légèrement supérieure à cette dernière. Le cas échéant, nous vous recommandons d'utiliser le paramètre TempTable pour les tables temporaires internes et les fichiers mappés en mémoire pour conserver toutes les données excédentaires. Il s'agit de la valeur par défaut.

Le moteur de stockage TempTable est le moteur par défaut. TempTable utilise un pool de mémoire commun pour toutes les tables temporaires utilisant ce moteur, au lieu d'une limite de mémoire maximale par table. La taille de ce pool de mémoire est spécifiée par le paramètre temptable_max_ram. Elle est par défaut de 1 Gio sur les instances de base de données de 16 Gio de mémoire ou plus, et de 16 Mo sur les instances de base de données de moins de 16 Gio de mémoire. La taille du pool de mémoire influe sur la consommation de mémoire au niveau de la session.

Dans certains cas, lorsque vous utilisez le moteur de stockage TempTable, les données temporaires peuvent dépasser la taille du pool de mémoire. Dans ce cas, Aurora My SQL stocke les données de débordement à l'aide d'un mécanisme secondaire.

Vous pouvez définir le paramètre temptable_max_mmap pour choisir si les données dépassent la taille des fichiers temporaires mappés en mémoire ou des tables temporaires internes InnoDB sur le disque. Les différents formats de données et les critères de dépassement de ces mécanismes de dépassement peuvent affecter les performances des requêtes. Pour ce faire, Ils influencent la quantité de données écrites sur disque et la demande de débit de stockage sur le disque.

Aurora My SQL stocke les données de débordement différemment en fonction de votre choix de destination de débordement de données et du fait que la requête s'exécute sur une instance de base de données d'écriture ou de lecture :

  • Sur l'instance d'enregistreur, les données qui dépassent vers des tables temporaires internes InnoDB sont stockées dans le volume du cluster Aurora.

  • Sur l'instance Writer, les données qui débordent vers des fichiers temporaires mappés en mémoire se trouvent sur le stockage local de l'instance Aurora My SQL version 3.

  • Sur les instances de lecteur, les données excédentaires se trouvent toujours dans des fichiers temporaires mappés en mémoire sur le stockage local. En effet, les instances en lecture seule ne peuvent stocker aucune donnée sur le volume de cluster Aurora.

Les paramètres de configuration associés aux tables temporaires internes s'appliquent différemment aux instances de lecteur et d'enregistreur sur votre cluster.

  • Sur les instances de lecteur, Aurora My utilise SQL toujours le moteur TempTable de stockage.

  • La taille par défaut de temptable_max_mmap est de 1 Gio, pour les instances de lecteur et d'enregistreur, quelle que soit la taille de la mémoire de l'instance de base de données. Vous pouvez ajuster cette valeur à la fois sur les instances d'enregistreur et de lecteur.

  • Définir temptable_max_mmap sur 0 désactive l'utilisation des fichiers temporaires mappés en mémoire sur les instances d'enregistreur.

  • Vous ne pouvez pas définir temptable_max_mmap sur 0 sur les instances de lecteur.

Note

Nous déconseillons l'utilisation du paramètre temptable_use_mmap. Il est devenu obsolète et son support devrait être supprimé dans une future version de MySQL.

Limitation de la taille des tables temporaires internes en mémoire

Comme indiqué dans Moteur de stockage pour tables temporaires internes (implicites), vous pouvez contrôler les ressources de tables temporaires de manière globale en utilisant les paramètres temptable_max_ram et temptable_max_mmap.

Vous pouvez également limiter la taille de n'importe quelle table temporaire interne en mémoire en utilisant le paramètre de base de données tmp_table_size. Cette limite vise à empêcher les requêtes individuelles de consommer une quantité excessive de ressources de tables temporaires globales, ce qui peut affecter les performances de requêtes simultanées nécessitant ces ressources.

Le tmp_table_size paramètre définit la taille maximale des tables temporaires créées par le moteur MEMORY de stockage dans Aurora My SQL version 3.

Dans Aurora My SQL version 3.04 et versions ultérieures, définit tmp_table_size également la taille maximale des tables temporaires créées par le moteur de TempTable stockage lorsque le paramètre de aurora_tmptable_enable_per_table_limit base de données est défini sur. ON Ce comportement est désactivé par défaut (OFF), ce qui est le même que dans Aurora My SQL version 3.03 et versions antérieures.

  • Quand aurora_tmptable_enable_per_table_limit a pour valeur OFF, tmp_table_size n'est pas pris en compte pour les tables temporaires internes en mémoire créées par le moteur de stockage TempTable.

    Cependant, la limite globale des ressources TempTable s'applique toujours. Aurora My se SQL comporte comme suit lorsque la limite globale TempTable des ressources est atteinte :

    • Instances de base de données Writer — Aurora My convertit SQL automatiquement la table temporaire en mémoire en table temporaire InnoDB sur disque.

    • Instances de base de données de lecteur : la requête se termine par une erreur.

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

  • Dans aurora_tmptable_enable_per_table_limit ce casON, Aurora My se SQL comporte comme suit lorsque la tmp_table_size limite est atteinte :

    • Instances de base de données Writer — Aurora My convertit SQL automatiquement la table temporaire en mémoire en table temporaire InnoDB sur disque.

    • Instances de base de données de lecteur : la requête se termine par une erreur.

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

      La limite globale des ressources TempTable et la limite par table s'appliquent toutes les deux dans ce cas.

Note

Le paramètre aurora_tmptable_enable_per_table_limit n'a aucun effet quand internal_tmp_mem_storage_engine a pour valeur MEMORY. Dans ce cas, la taille maximale d'une table temporaire en mémoire est définie par la valeur tmp_table_size ou max_heap_table_size, la plus petite de ces deux valeurs étant retenue.

Les exemples suivants montrent le comportement du paramètre aurora_tmptable_enable_per_table_limit pour les instances de base de données d'enregistreur et de lecteur.

Exemple d'une instance de base de données d'enregistreur avec aurora_tmptable_enable_per_table_limit défini sur OFF

La table temporaire en mémoire n'est pas convertie en table temporaire InnoDB sur disque.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  0 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (13.99 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)
Exemple d'une instance de base de données d'enregistreur avec aurora_tmptable_enable_per_table_limit défini sur ON

La table temporaire en mémoire est convertie en table temporaire InnoDB sur disque.

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  0 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
+---------+
| max(n)  |
+---------+
| 6000000 |
+---------+
1 row in set (4.10 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)
Exemple d'une instance de base de données de lecteur avec aurora_tmptable_enable_per_table_limit défini sur OFF

La requête se termine sans erreur car tmp_table_size ne s'applique pas, et la limite globale des ressources TempTable n'a pas été atteinte.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (14.05 sec)
Exemple d'une instance de base de données de lecteur avec aurora_tmptable_enable_per_table_limit défini sur OFF

Cette requête atteint la limite globale TempTable des ressources lorsqu'elle aurora_tmptable_enable_per_table_limit est définie surOFF. La requête se termine avec une erreur sur les instances de lecteur.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
Exemple d'une instance de base de données de lecteur avec aurora_tmptable_enable_per_table_limit défini sur ON

La requête se termine avec une erreur lorsque la limite tmp_table_size est atteinte.

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  1 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full

Atténuation des problèmes de remplissage pour les tables temporaires internes sur les réplicas Aurora

Pour éviter les problèmes de limite de taille pour les tables temporaires, définissez les paramètres temptable_max_ram et temptable_max_mmap sur une valeur combinée, adaptée aux exigences de votre charge de travail.

Soyez vigilant lorsque vous définissez la valeur du paramètre temptable_max_ram. La définition d'une valeur trop élevée réduit la mémoire disponible sur l'instance de base de données, ce qui peut provoquer un out-of-memory problème. Surveillez la quantité moyenne de mémoire libérable sur l'instance de base de données. Déterminez ensuite une valeur appropriée pour temptable_max_ram, de sorte qu'il vous reste une quantité raisonnable de mémoire libre sur l'instance. Pour de plus amples informations, veuillez consulter .

Il est également important de surveiller la taille du stockage local et la consommation d'espace des tables temporaires. Vous pouvez surveiller le stockage temporaire disponible pour une instance de base de données spécifique à l'aide de la CloudWatch métrique FreeLocalStorage Amazon, décrite dans CloudWatch Métriques Amazon pour Amazon Aurora.

Note

Cette procédure ne fonctionne pas quand le paramètre aurora_tmptable_enable_per_table_limit est défini sur ON. Pour de plus amples informations, veuillez consulter Limitation de la taille des tables temporaires internes en mémoire.

Exemple 1

Vous savez que vos tables temporaires atteignent une taille cumulée de 20 Gio. Vous souhaitez définir les tables temporaires en mémoire sur 2 Gio et atteindre une taille maximale de 20 Gio sur disque.

Définissez temptable_max_ram sur 2,147,483,648 et temptable_max_mmap sur 21,474,836,480. Ces valeurs sont exprimées en octets.

Ces valeurs de paramètre garantissent que vos tables temporaires peuvent atteindre un total cumulé de 22 Gio.

Exemple 2

La taille actuelle de votre instance est 16xlarge ou supérieure. Vous ne connaissez pas la taille totale des tables temporaires dont vous pourriez avoir besoin. Vous souhaitez pouvoir utiliser jusqu'à 4 Gio en mémoire et jusqu'à la taille de stockage maximale disponible sur disque.

Définissez temptable_max_ram sur 4,294,967,296 et temptable_max_mmap sur 1,099,511,627,776. Ces valeurs sont exprimées en octets.

Ici, vous êtes en train de définir temptable_max_mmap sur 1 Tio, ce qui est inférieur au stockage local maximal de 1,2 Tio sur une instance de base de données Aurora 16xlarge.

Sur une taille d'instance plus petite, ajustez la valeur de temptable_max_mmap afin qu'elle ne remplisse pas le stockage local disponible. Par exemple, une instance 2xlarge ne dispose que de 160 Gio de stockage local disponible. Par conséquent, nous vous recommandons de définir la valeur sur 160 Gio au maximum. Pour plus d'informations sur le stockage local disponible pour les tailles d'instance de base de données, consultez Limites de stockage temporaires pour Aurora My SQL.

Tables temporaires (explicites) créées par l'utilisateur sur les instances de base de données de lecteur

Vous pouvez créer des tables temporaires explicites en utilisant le mot-clé TEMPORARY dans votre instruction CREATE TABLE. Les tables temporaires explicites sont prises en charge sur l'instance de base de données d'enregistreur dans un cluster de bases de données Aurora. Vous pouvez également utiliser des tables temporaires explicites sur les instances de base de données de lecteur, mais les tables ne peuvent pas imposer l'utilisation du moteur de stockage InnoDB.

Pour éviter les erreurs lors de la création de tables temporaires explicites sur les instances de base de données Aurora My SQL reader, assurez-vous d'exécuter toutes les CREATE TEMPORARY TABLE instructions de l'une ou des deux manières suivantes :

  • Ne spécifiez pas la clause ENGINE=InnoDB.

  • Ne réglez pas le SQL mode surNO_ENGINE_SUBSTITUTION.

Erreurs de création d'une table temporaire et atténuation

L'erreur que vous recevez est différente selon que vous utilisez ou non une instruction CREATE TEMPORARY TABLE simple ou la variante CREATE TEMPORARY TABLE AS SELECT. Les exemples suivants montrent les différents types d'erreurs.

Ce comportement de table temporaire s'applique uniquement aux instances en lecture seule. Ce premier exemple confirme que c'est le type d'instance à laquelle la session est connectée.

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  1 |
+--------------------+

Pour les CREATE TEMPORARY TABLE instructions simples, l'instruction échoue lorsque le NO_ENGINE_SUBSTITUTION SQL mode est activé. Lorsque NO_ENGINE_SUBSTITUTION est désactivé (par défaut), la substitution du moteur approprié est effectuée et la création de la table temporaire aboutit.

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql>  CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;

mysql> SHOW CREATE TABLE tt4\G
*************************** 1. row ***************************
       Table: tt4
Create Table: CREATE TEMPORARY TABLE `tt4` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Pour CREATE TEMPORARY TABLE AS SELECT les instructions, l'instruction échoue lorsque le NO_ENGINE_SUBSTITUTION SQL mode est activé. Lorsque NO_ENGINE_SUBSTITUTION est désactivé (par défaut), la substitution du moteur approprié est effectuée et la création de la table temporaire aboutit.

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql> CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> show create table tt3;
+-------+----------------------------------------------------------+
| Table | Create Table                                             |
+-------+----------------------------------------------------------+
| tt3   | CREATE TEMPORARY TABLE `tt3` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)

Pour plus d'informations sur les aspects de stockage et les implications en termes de performances des tables temporaires dans Aurora My SQL version 3, consultez le billet de blog Utiliser le moteur de TempTable stockage sur Amazon RDS for My SQL et Amazon Aurora My SQL.