Nuovo comportamento della tabella temporanea in Aurora MySQL versione 3

Aurora MySQL versione 3 gestisce le tabelle temporanee in maniera diversa dalle versioni precedenti di Aurora MySQL. Questo nuovo comportamento è ereditato dalla edizione della comunità MySQL 8.0. Esistono due tipi di tabelle temporanee che possono essere create con Aurora MySQL versione 3:

• Tabelle temporanee interne (o implicite): create dal motore Aurora MySQL per gestire operazioni quali l'ordinamento dell'aggregazione, le tabelle derivate o le espressioni di tabella comuni (CTE).

• Tabelle temporanee create dall'utente (o esplicite): create dal motore Aurora MySQL quando si utilizza l'istruzione CREATE TEMPORARY TABLE.

Ci sono ulteriori considerazioni per le tabelle temporanee interne e create dall'utente sulle istanze database di lettura Aurora. Queste modifiche vengono illustrate nelle sezioni seguenti.

Motore di storage per tabelle temporanee (implicite) interne

Quando si generano set di risultati intermedi, Aurora MySQL tenta inizialmente di scrivere nelle tabelle temporanee in memoria. Questa operazione potrebbe non riuscire, a causa di tipi di dati incompatibili o limiti configurati. In tal caso, la tabella temporanea viene convertita in una tabella temporanea su disco anziché essere conservata in memoria. Ulteriori informazioni sono disponibili in Utilizzo della tabella temporanea interna in MySQL nella documentazione MySQL.

In Aurora MySQL versione 3, il modo in cui funzionano le tabelle temporanee interne è diverso dalle versioni precedenti di Aurora MySQL. Invece di scegliere tra i motori di archiviazione InnoDB e MyISAM per tali tabelle temporanee, ora scegli tra le TempTable e i motori di archiviazione InnoDB.

Con il motore di archiviazione TempTable, è possibile fare una scelta aggiuntiva su come gestire determinati dati. I dati interessati fanno traboccare il pool di memoria che contiene tutte le tabelle temporanee interne per l'istanza database.

Tali scelte possono influenzare le prestazioni delle query che generano volumi elevati di dati temporanei, ad esempio durante l'esecuzione di aggregazioni come GROUP BY su tabelle grandi.

Suggerimento

Se il carico di lavoro include query che generano tabelle temporanee interne, verificare le prestazioni dell'applicazione con questa modifica eseguendo benchmark e monitorando le metriche relative alle prestazioni.

In alcuni casi, la quantità di dati temporanei si inserisce nella pool di memoria TempTable o fa solo traboccare il pool di memoria in piccola misura. In questi casi, si consiglia di utilizzare l'impostazione TempTable per tabelle temporanee interne e file mappati in memoria per contenere i dati di overflow. Questa è l'impostazione di default.

Il motore di archiviazione di TempTable è il valore di default. TempTable utilizza un pool di memoria comune per tutte le tabelle temporanee che utilizzano questo motore, anziché un limite massimo di memoria per tabella. La dimensione di questo pool di memoria è specificata dal parametro temptable_max_ram. Il valore predefinito è 1 GiB su istanze database con 16 o più GiB di memoria e 16 MB su istanze database con meno di 16 GiB di memoria. La dimensione del pool di memoria influisce sul consumo di memoria a livello di sessione.

In alcuni casi, quando si utilizza il motore di archiviazione TempTable i dati temporanei potrebbero superare le dimensioni del pool di memoria. In tal caso, Aurora MySQL archivia i dati di overflow utilizzando un meccanismo secondario.

È possibile impostare il parametro temptable_max_mmap per scegliere se i dati eseguono l'overflow in file temporanei mappati in memoria o in tabelle temporanee interne di InnoDB su disco. I diversi formati di dati e i criteri di overflow di questi meccanismi di overflow possono influire sulle prestazioni delle query. Lo fanno influenzando la quantità di dati scritti su disco e la domanda sulla velocità effettiva di archiviazione su disco.

Aurora MySQL memorizza i dati di overflow in modo diverso a seconda della scelta della destinazione di overflow dei dati e se la query viene eseguita su un'istanza database di scrittura o lettura:

• Nell'istanza di scrittura, i dati che traboccano sulle tabelle temporanee interne di InnoDB vengono archiviati nel volume del cluster Aurora.

• Sull'istanza di scrittura, i dati che traboccano su file temporanei mappati in memoria risiedono sull'archivio locale sull'istanza Aurora MySQL versione 3.

• Sulle istanze di lettura, i dati di overflow risiedono sempre su file temporanei mappati in memoria sull'archivio locale. Questo perché le istanze di sola lettura non possono memorizzare dati sul volume del cluster Aurora.

I parametri di configurazione relativi alle tabelle temporanee interne si applicano in modo diverso alle istanze di scrittura e lettura nel cluster:

• Nelle istanze di lettura, Aurora MySQL utilizza sempre il motore di archiviazione TempTable.

• La dimensione per il valore di temptable_max_mmap predefinito è 1 GiB, per entrambe le istanze di scrittura e lettura, indipendentemente dalle dimensioni della memoria dell'istanza database. È possibile modificare questo valore sia nelle istanze di scrittura che in quelle di lettura.

• L'impostazione di temptable_max_mmap su 0 disattiva l'uso dei file temporanei mappati in memoria nelle istanze di scrittura.

• Non puoi impostare temptable_max_mmap su 0 nelle istanze di lettura.

Nota

Ti consigliamo di non utilizzare il parametro temptable_use_mmap. È deprecato e il relativo supporto verrà rimosso in una versione futura di MySQL.

Limitazione delle dimensioni delle tabelle temporanee interne in memoria

Come discusso in Motore di storage per tabelle temporanee (implicite) interne, è possibile controllare le risorse temporanee delle tabelle a livello globale utilizzando le impostazioni temptable max_ram e temptable_max_mmap.

Inoltre, è possibile limitare le dimensioni di ogni singola tabella temporanea interna in memoria utilizzando il parametro database tmp_table_size. Questo limite serve a evitare che le singole query consumino una quantità eccessiva di risorse globali delle tabelle temporanee, che può influire sulle prestazioni delle query simultanee che richiedono tali risorse.

Il parametro tmp_table_size definisce le dimensioni massime delle tabelle temporanee create dal motore di storage MEMORY in Aurora MySQL versione 3.

In Aurora MySQL versione 3.04 e successive, il parametro tmp_table_size definisce anche le dimensioni massime delle tabelle temporanee create dal motore di storage TempTable quando il parametro database aurora_tmptable_enable_per_table_limit è impostato su ON. Questo comportamento è disabilitato per impostazione predefinita (OFF), che è identico al comportamento in Aurora MySQL versione 3.03 e precedenti.

• Quando aurora_tmptable_enable_per_table_limit è OFF, tmp_table_size non è considerato per le tabelle temporanee interne in memoria create dal motore di storage TempTable.

  Tuttavia, il limite di risorse TempTable globali rimane comunque in essere. Aurora MySQL presenta il seguente comportamento quando viene raggiunto il limite di risorse TempTable globale:

  • Istanze database di scrittura: Aurora MySQL converte automaticamente la tabella temporanea in memoria in una tabella temporanea su disco InnoDB.

  • Istanze database di lettura: la query termina con un errore.

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

• Quando aurora_tmptable_enable_per_table_limit è ON, Aurora MySQL presenta il seguente comportamento quando viene raggiunto il limite tmp_table_size:

  • Istanze database di scrittura: Aurora MySQL converte automaticamente la tabella temporanea in memoria in una tabella temporanea su disco InnoDB.

  • Istanze database di lettura: la query termina con un errore.

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

    Il limite di risorse TempTable globale e il limite per tabella si applicano entrambi in questo caso.

Nota

Il parametro aurora_tmptable_enable_per_table_limit non ha effetto quando internal_tmp_mem_storage_engine è impostato su MEMORY. In questo caso, le dimensioni massime di una tabella temporanea in memoria sono definite dal valore tmp_table_size o dimensione max_heap_table_size, a seconda di quale sia il più piccolo.

Gli esempi seguenti mostrano il comportamento del parametro aurora_tmptable_enable_per_table_limitper le istanze database di scrittura e lettura.

Esempio dell'istanza database di scrittura con aurora_tmptable_enable_per_table_limit impostato su OFF

La tabella temporanea in memoria non viene convertita in una tabella temporanea su disco InnoDB.

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)

Esempio dell'istanza database di scrittura con aurora_tmptable_enable_per_table_limit impostato su ON

La tabella temporanea in memoria viene convertita in una tabella temporanea su disco InnoDB.

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)

Esempio dell'istanza database di lettura con aurora_tmptable_enable_per_table_limit impostato su OFF

La query termina senza errori perché tmp_table_size non è applicabile e il limite di risorse TempTable globale non è stato raggiunto.

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)

Esempio dell'istanza database di lettura con aurora_tmptable_enable_per_table_limit impostato su OFF

Questa query raggiunge il limite di risorse TempTable globale con aurora_tmptable_enable_per_table_limit impostato su OFF. La query termina con un errore su istanze di lettura.

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

Esempio dell'istanza database di lettura con aurora_tmptable_enable_per_table_limit impostato su ON

La query termina con un errore quando viene raggiunto il limite tmp_table_size.

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

Mitigazione dei problemi di pienezza per le tabelle temporanee interne su repliche Aurora

Per evitare problemi di limitazione delle dimensioni per le tabelle temporanee, impostare i parametri temptable_max_ram e temptable_max_mmap su un valore combinato in grado di soddisfare i requisiti del carico di lavoro.

Presta attenzione durante l'impostazione del valore del parametro temptable_max_ram. Se si imposta un valore troppo alto, la memoria disponibile sull'istanza database viene ridotta e si può verificare una condizione di memoria insufficiente. Monitora la memoria liberabile media sull'istanza database. Quindi determina un valore appropriato per temptable_max_ram in modo da avere ancora una quantità ragionevole di memoria libera sull'istanza. Per ulteriori informazioni, consulta Problemi di memoria liberabile in Amazon Aurora.

È inoltre importante monitorare le dimensioni dello storage locale e il consumo temporaneo di spazio della tabella. Per ulteriori informazioni sul monitoraggio dell’archiviazione locale su un'istanza, consulta l'articolo di AWS Knowledge Center What is stored in Aurora MySQL-compatible local storage, and how can I troubleshoot local storage issues?.

Nota

Questa procedura non funziona quando il parametro aurora_tmptable_enable_per_table_limit è impostato su ON. Per ulteriori informazioni, consulta Limitazione delle dimensioni delle tabelle temporanee interne in memoria.

Esempio 1

È noto che le tabelle temporanee raggiungono una dimensione cumulativa di 20 GiB. Desideri impostare tabelle temporanee in memoria su 2 GiB e aumentare fino a un massimo di 20 GiB su disco.

Imposta temptable_max_ram su 2,147,483,648 e temptable_max_mmap su 21,474,836,480. Questi valori sono espressi in byte.

Queste impostazioni dei parametri garantiscono che le dimensioni delle tabelle temporanee possano aumentare fino a un totale cumulativo di 22 GiB.

Esempio 2

La dimensione istanza corrente è 16xlarge o superiore. Non conosci le dimensioni totali delle tabelle temporanee che potrebbero essere richieste. Vuoi poter utilizzare fino a 4 GiB in memoria e fino alla dimensione di storage massima disponibile su disco.

Imposta temptable_max_ram su 4,294,967,296 e temptable_max_mmap su 1,099,511,627,776. Questi valori sono espressi in byte.

Qui stai impostando temptable_max_mmap su 1 TiB, che è inferiore allo storage locale massimo di 1,2 TiB su un'istanza database di Aurora 16xlarge.

Su una dimensione istanza più piccola, regola il valore di temptable_max_mmap in modo che non riempia lo storage locale disponibile. Ad esempio, un'istanza 2xlarge dispone solo di 160 GiB di storage locale disponibile. Pertanto, si consiglia di impostare su un valore inferiore a 160 GiB. Per ulteriori informazioni sullo storage locale disponibile per le dimensioni delle istanze database, consulta Limiti di storage temporaneo per Aurora MySQL.

Tabelle temporanee create dall'utente (esplicite) su istanze database di lettura

Puoi creare tabelle temporanee esplicite utilizzando la parola chiave TEMPORARY nell'istruzione CREATE TABLE. Le tabelle temporanee esplicite sono supportate nell'istanza database di scrittura in un cluster di database Aurora. Puoi inoltre utilizzare tabelle temporanee esplicite sulle istanze database di lettura, ma le tabelle non possono applicare l'uso del motore di storage InnoDB.

Per evitare errori durante la creazione di tabelle temporanee esplicite su istanze database di lettura di Aurora MySQL, assicurati che tutte le istruzioni CREATE TEMPORARY TABLE vengano eseguite in uno o entrambi i seguenti modi:

• Non specificare la clausola ENGINE=InnoDB.

• Non impostare la modalità SQL su NO_ENGINE_SUBSTITUTION.

Errori di creazione tabelle temporanee e mitigazione

L'errore che si riceve è diverso a seconda che si utilizzi una semplice istruzione CREATE TEMPORARY TABLE o la variazione CREATE TEMPORARY TABLE AS SELECT. Nell'esempio seguente vengono illustrati tipi diversi di errori.

Questo comportamento temporaneo della tabella si applica solo alle istanze di sola lettura. Questo primo esempio conferma che è il tipo di istanza a cui è connessa la sessione.

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

Per istruzioni semplici CREATE TEMPORARY TABLE, l'istruzione fallisce quando la modalità SQL NO_ENGINE_SUBSTITUTION è attivata. Quando NO_ENGINE_SUBSTITUTION è disattivato (impostazione predefinita), viene effettuata la sostituzione motore appropriata e la creazione della tabella temporanea ha esito positivo.

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

Per le istruzioni CREATE TEMPORARY TABLE AS SELECT, l'istruzione ha esito negativo quando la modalità SQL NO_ENGINE_SUBSTITUTION è attivata. Quando NO_ENGINE_SUBSTITUTION è disattivato (impostazione predefinita), viene effettuata la sostituzione motore appropriata e la creazione della tabella temporanea ha esito positivo.

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)

Per ulteriori informazioni sugli aspetti di archiviazione e sulle implicazioni sulle prestazioni delle tabelle temporanee in Aurora MySQL versione 3, consulta il post di blog Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL.