Novo comportamento de tabela temporária no Aurora MySQL versão 3 - Amazon Aurora
Mecanismo de armazenamento para tabelas temporárias internas (implícitas)Limitar o tamanho de tabelas temporárias internas na memóriaMitigar problemas de volume em tabelas temporárias internas em réplicas do AuroraTabelas temporárias criadas pelo usuário (explícitas) em instâncias de banco de dados de leitorMitigação e erros de criação de tabelas temporárias

Novo comportamento de tabela temporária no Aurora MySQL versão 3

O Aurora MySQL versão 3 processa as tabelas temporárias internas de forma diferente das versões anteriores do Aurora MySQL. Esse novo comportamento é herdado do MySQL 8.0 Community Edition. Existem dois tipos de tabelas temporárias que podem ser criadas com o Aurora MySQL versão 3:

  • Tabelas temporárias internas (ou implícitas): criadas pelo mecanismo do Aurora MySQL para lidar com operações, como agregação de classificação, tabelas derivadas ou expressões de tabela comuns (CTEs).

  • Tabelas temporárias criadas pelo usuário (ou explícitas): criadas pelo mecanismo do Aurora MySQL quando você usa a instrução CREATE TEMPORARY TABLE.

Há considerações adicionais sobre tabelas temporárias internas e criadas pelo usuário em instâncias de banco de dados do leitor do Aurora. Abordamos essas alterações nas seções a seguir.

Mecanismo de armazenamento para tabelas temporárias internas (implícitas)

Ao gerar conjuntos de resultados intermediários, o Aurora MySQL inicialmente tenta gravar em tabelas temporárias na memória. Esse procedimento pode não ser bem-sucedido devido a tipos de dados incompatíveis ou limites configurados. Se esse for o caso, a tabela temporária será convertida em uma tabela temporária no disco, em vez de mantida na memória. Mais informações sobre isso podem ser encontradas em Uso de tabela temporária interna no MySQL na documentação do MySQL.

No Aurora MySQL versão 3, a maneira como as tabelas temporárias internas funcionam é diferente das versões anteriores do Aurora MySQL. Em vez de escolher entre os mecanismos de armazenamento InnoDB e MyISAM para essas tabelas temporárias, agora você escolhe entre os mecanismos de armazenamento TempTable e MEMORY.

Com o mecanismo de armazenamento TempTable, é possível fazer uma escolha adicional de como lidar com determinados dados. Os dados afetados transbordam o pool de memória que contém todas as tabelas temporárias internas da instância de banco de dados.

Essas opções podem influenciar a performance de consultas que geram altos volumes de dados temporários, por exemplo, ao realizar agregações como GROUP BY em tabelas grandes.

dica

Se a sua workload incluir consultas que geram tabelas temporárias internas, confirme a performance da sua aplicação com essa alteração executando benchmarks e monitorando métricas de performance.

Em alguns casos, a quantidade de dados temporários se encaixa no grupo de memória TempTable ou apenas transborda o grupo de memória em uma pequena quantidade. Nesses casos, convém utilizar a configuração TempTable para tabelas temporárias internas e arquivos mapeados para a memória a fim de conter quaisquer dados de estouro. Essa é a configuração padrão.

O mecanismo de armazenamento TempTable é o padrão. TempTable usa um grupo de memória comum para todas as tabelas temporárias que usam esse mecanismo, em vez de um limite máximo de memória por tabela. O tamanho desse grupo de memória é especificado pelo parâmetro temptable_max_ram. O padrão é 1 GiB em instâncias de banco de dados com 16 GiB ou mais de memória e 16 MB em instâncias de banco de dados com menos de 16 GiB de memória. O tamanho do grupo de memória influencia o consumo de memória em nível de sessão.

Em alguns casos, quando você usa o mecanismo de armazenamento TempTable, os dados temporários podem exceder o tamanho do grupo de memória. Nesse caso, o Aurora MySQL armazena os dados de transbordamento usando um mecanismo secundário.

É possível definir o parâmetro temptable_max_mmap para escolher se os dados transbordam para arquivos temporários mapeados pela memória ou para tabelas temporárias internas do InnoDB no disco. Os diferentes formatos de dados e critérios de transbordamento desses mecanismos de transbordamento podem afetar a performance das consultas. Isso ocorre devido à sua influência sobre a quantidade de dados gravados no disco e a demanda na taxa de transferência de armazenamento em disco.

O Aurora MySQL armazena os dados de transbordamento de maneira diferente, dependendo da escolha do destino do transbordamento de dados e se a consulta é executada em uma instância de banco de dados de gravador ou leitor:

  • Na instância de gravador, os dados que transbordam para tabelas temporárias internas do InnoDB são armazenados no volume do cluster do Aurora.

  • Na instância de gravador, os dados que transbordam para arquivos temporários mapeados para a memória residem no armazenamento local na instância do Aurora MySQL versão 3.

  • Em instâncias de leitor, os dados de estouro sempre residem em arquivos temporários mapeados para a memória no armazenamento local. Isso ocorre porque instâncias somente leitura não podem armazenar dados no volume do cluster do Aurora.

Os parâmetros de configuração relacionados a tabelas temporárias internas são aplicáveis de maneira diferente às instâncias de gravador e de leitor no seu cluster:

  • Em instâncias do leitor, o Aurora MySQL sempre utiliza o mecanismo de armazenamento TempTable.

  • O tamanho de temptable_max_mmap é de 1 GiB por padrão, tanto para instâncias do gravador quanto do leitor, independentemente do tamanho da memória da instância de banco de dados. Você pode ajustar esse valor em instâncias do gravador e do leitor.

  • A configuração de temptable_max_mmap para 0 desativa o uso de arquivos temporários mapeados na memória em instâncias do gravador.

  • Você não pode definir temptable_max_mmap como 0 em instâncias do leitor.

nota

Não recomendamos utilizar o parâmetro temptable_use_mmap. Ele foi descontinuado, e há previsões de que o suporte será removido em uma versão futura do MySQL.

Limitar o tamanho de tabelas temporárias internas na memória

Conforme abordado em Mecanismo de armazenamento para tabelas temporárias internas (implícitas), é possível controlar recursos de tabelas temporárias globalmente usando as configurações temptable_max_ram e temptable_max_mmap.

Você também pode limitar o tamanho de qualquer tabela temporária interna individual na memória usando o parâmetro de banco de dados tmp_table_size. Esse limite tem como objetivo evitar que consultas individuais consumam uma quantidade excessiva de recursos globais de tabelas temporárias, o que pode afetar a performance de consultas simultâneas que exigem esses recursos.

O parâmetro tmp_table_size define o tamanho máximo das tabelas temporárias criadas pelo mecanismo de armazenamento MEMORY no Aurora MySQL versão 3.

No Aurora MySQL versão 3.04 e posterior, tmp_table_size também define o tamanho máximo das tabelas temporárias criadas pelo mecanismo de armazenamento TempTable quando o parâmetro de banco de dados aurora_tmptable_enable_per_table_limit é definido como ON. Esse comportamento, que está desabilitado (OFF) por padrão, é o mesmo que no Aurora MySQL versão 3.03 e versões anteriores.

  • Quando aurora_tmptable_enable_per_table_limit está OFF, tmp_table_size não é considerado para tabelas temporárias internas na memória criadas pelo mecanismo de armazenamento TempTable.

    No entanto, o limite dos recursos TempTable globais ainda se aplica. O Aurora MySQL tem o seguinte comportamento quando o limite de recursos TempTable globais é atingido:

    • Instâncias de banco de dados de gravador: o Aurora MySQL converte automaticamente a tabela temporária na memória em uma tabela temporária em disco do InnoDB.

    • Instâncias de banco de dados de leitor: a consulta termina com um erro.

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

  • Quando aurora_tmptable_enable_per_table_limit está ON e o limite de tmp_table_size é atingido, o Aurora MySQL tem o seguinte comportamento:

    • Instâncias de banco de dados de gravador: o Aurora MySQL converte automaticamente a tabela temporária na memória em uma tabela temporária em disco do InnoDB.

    • Instâncias de banco de dados de leitor: a consulta termina com um erro.

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

      Tanto o limite de recursos TempTable globais quanto o limite por tabela se aplicam nesse caso.

nota

O parâmetro aurora_tmptable_enable_per_table_limit não tem efeito quando internal_tmp_mem_storage_engine está definido como MEMORY. Nesse caso, o tamanho máximo de uma tabela temporária na memória é definido pelo valor tmp_table_size ou max_heap_table_size, o que for menor.

Os exemplos a seguir mostram o comportamento do parâmetro aurora_tmptable_enable_per_table_limit para instâncias de banco de dados de gravador e leitor.

exemplo da instância de banco de dados de gravador com aurora_tmptable_enable_per_table_limit definido como OFF.

A tabela temporária na memória não é convertida em uma tabela temporária em disco do 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)
exemplo da instância de banco de dados de gravador com aurora_tmptable_enable_per_table_limit definido como ON.

A tabela temporária na memória não é convertida em uma tabela temporária em disco do 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)
exemplo da instância de banco de dados de leitor com aurora_tmptable_enable_per_table_limit definido como OFF.

A consulta termina sem um erro porque tmp_table_size não se aplica e o limite de recursos TempTable globais não foi atingido.

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)
exemplo da instância de banco de dados de leitor com aurora_tmptable_enable_per_table_limit definido como OFF.

Essa consulta atinge o limite global de recursos TempTable com aurora_tmptable_enable_per_table_limit definido como DESATIVADO. A consulta termina com um erro nas instâncias de leitor.

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
exemplo da instância de banco de dados de leitor com aurora_tmptable_enable_per_table_limit definido como ON.

A consulta termina com um erro quando o limite tmp_table_size é atingido.

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

Mitigar problemas de volume em tabelas temporárias internas em réplicas do Aurora

Para evitar problemas de limitação de tamanho de tabelas temporárias, defina os parâmetros temptable_max_ram e temptable_max_mmap para um valor combinado que pode atender aos requisitos de sua workload.

Tenha cuidado ao definir o valor do parâmetro temptable_max_ram. Definir o valor muito alto reduz a memória disponível na instância do banco de dados, o que pode causar uma condição de falta de memória. Monitore a memória média livre na instância de banco de dados. Depois, determine um valor apropriado para temptable_max_ram para que você ainda tenha uma quantidade razoável de memória livre na instância. Para obter mais informações, consulte Problemas de memória liberável no Amazon Aurora.

Também é importante monitorar o tamanho do armazenamento local e o consumo de espaço em tabelas temporárias. É possível monitorar o armazenamento temporário disponível para uma instância de banco de dados específica com a métrica FreeLocalStorage do Amazon CloudWatch, descrita em Métricas do Amazon CloudWatch para o Amazon Aurora.

nota

Esse procedimento não funciona quando o parâmetro aurora_tmptable_enable_per_table_limit está definido como ON. Para ter mais informações, consulte Limitar o tamanho de tabelas temporárias internas na memória.

exemplo 1

Você sabe que suas tabelas temporárias aumentam até um tamanho cumulativo de 20 GiB. Você deseja definir tabelas temporárias na memória como 2 GiB e aumentar para um máximo de 20 GiB em disco.

Defina temptable_max_ram como 2,147,483,648 e temptable_max_mmap como 21,474,836,480. Esses valores estão em bytes.

Essas configurações de parâmetros garantem que suas tabelas temporárias possam aumentar até um total cumulativo de 22 GiB.

exemplo 2

O tamanho da instância atual é 16xlarge ou maior. Você não sabe o tamanho total das tabelas temporárias que pode precisar. Você deseja ter a capacidade de usar até 4 GiB na memória e até o tamanho máximo de armazenamento disponível no disco.

Defina temptable_max_ram como 4,294,967,296 e temptable_max_mmap como 1,099,511,627,776. Esses valores estão em bytes.

Aqui você está definindo temptable_max_mmap como 1 TiB, que é inferior ao armazenamento local máximo de 1,2 TiB em uma instância de banco de dados 16xlarge do Aurora.

Em um tamanho de instância menor, ajuste o valor de temptable_max_mmap para que ele não preencha o armazenamento local disponível. Por exemplo, uma instância 2xlarge tem apenas 160 GiB de armazenamento local disponível. Por isso, recomendamos definir o valor como menos de 160 GiB. Para obter mais informações sobre o armazenamento local disponível para tamanhos de instância de banco de dados, consulte Limites de armazenamento temporário para o Aurora MySQL.

Tabelas temporárias criadas pelo usuário (explícitas) em instâncias de banco de dados de leitor

Você pode criar explicitamente tabelas temporárias usando uma palavra-chave TEMPORARY em sua instrução CREATE TABLE. As tabelas temporárias explícitas são compatíveis com a instância de banco de dados do gravador em um cluster de banco de dados do Aurora. Você também pode usar tabelas temporárias explícitas em instâncias de banco de dados do leitor, mas as tabelas não podem impor o uso do mecanismo de armazenamento InnoDB.

Para evitar erros ao criar tabelas temporárias explícitas em instâncias de banco de dados do leitor do Aurora MySQL, execute todas as instruções CREATE TEMPORARY TABLE em instâncias de banco de dados do leitor de uma destas maneiras:

  • Não especifique a cláusula ENGINE=InnoDB.

  • Não defina o modo SQL como NO_ENGINE_SUBSTITUTION.

Mitigação e erros de criação de tabelas temporárias

O erro retornado é diferente dependendo de você utilizar uma instrução CREATE TEMPORARY TABLE simples ou a variação CREATE TEMPORARY TABLE AS SELECT. Os exemplos a seguir mostram os diferentes tipos de erros.

Esse comportamento de tabela temporária apenas se aplica a instâncias somente leitura. Esse primeiro exemplo confirma que este é o tipo de instância à qual a sessão está conectada.

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

Para instruções CREATE TEMPORARY TABLE simples, a instrução falha quando o modo SQL NO_ENGINE_SUBSTITUTION está habilitado. Quando NO_ENGINE_SUBSTITUTION está desativado (padrão), a substituição do mecanismo apropriado é feita e a criação temporária da tabela é bem-sucedida.

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

Para instruções CREATE TEMPORARY TABLE AS SELECT, a instrução falha quando o modo SQL NO_ENGINE_SUBSTITUTION está ativado. Quando NO_ENGINE_SUBSTITUTION está desativado (padrão), a substituição do mecanismo apropriado é feita e a criação temporária da tabela é bem-sucedida.

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)

Para obter mais informações sobre os aspectos de armazenamento e as implicações de performance de tabelas temporárias no Aurora MySQL versão 3, consulte a postagem do blog Usar o mecanismo de armazenamento TempTable no Amazon RDS para MySQL e Amazon Aurora MySQL.