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 InnoDB.

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 especificar 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 forma diferente, dependendo de algumas considerações. Esta é sua escolha do destino do transbordamento de dados e se a consulta é executada em uma instância de banco de dados do gravador ou do 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.

nota

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. Para instâncias de leitor, o Aurora MySQL sempre utiliza o mecanismo de armazenamento TempTable e um valor de 1 para temptable_use_mmap. O tamanho de temptable_max_mmap é de 1 GiB por padrão, tanto para instâncias de gravador quanto de leitor, independentemente do tamanho da memória da instância de banco de dados. Você pode ajustar esse valor de forma semelhante à maneira como você faz isso na instância do gravador. No entanto, não é possível especificar um valor de zero para temptable_max_mmap em instâncias do leitor.

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. Para obter mais informações sobre como monitorar o armazenamento local em uma instância, consulte o artigo do Centro de Conhecimentos da AWS O que é armazenado no armazenamento local compatível com o Aurora MySQL e como solucionar problemas de armazenamento local?.

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 independentemente de o modo SQL NO_ENGINE_SUBSTITUTION estar habilitado ou não. O MySQL Community Edition não é compatível com a substituição do mecanismo de armazenamento com instruções CREATE TABLE AS SELECT ou CREATE TEMPORARY TABLE AS SELECT. Para essas instruções, remova a cláusula ENGINE=InnoDB do seu código SQL.

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> CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 1874 (HY000): InnoDB is in read only mode.

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.