# Práticas recomendadas para a performance e escalabilidade do Aurora MySQL Você pode aplicar as práticas recomendadas a seguir para melhorar a performance e a escalabilidade dos seus clusters do Aurora MySQL. **Topics** + [Uso de classes de instância T para desenvolvimento e testes](#AuroraMySQL.BestPractices.T2Medium) + [Otimizar consultas de junção indexadas do Aurora MySQL com pré-busca de chave assíncrona](#Aurora.BestPractices.AKP) + [Otimizando grandes consultas de junção do Aurora MySQL com junções hash](#Aurora.BestPractices.HashJoin) + [Como usar o Amazon Aurora para escalar leituras para seu banco de dados MySQL](#AuroraMySQL.BestPractices.ReadScaling) + [Otimizando as operações de carimbo de data/hora](#AuroraMySQL.BestPractices.Performance.TimeZone) + [Erros de estouro de ID de índice virtual](#AuroraMySQL.BestPractices.Performance.VirtualIndexIDOverflow) ## Uso de classes de instância T para desenvolvimento e testes As instâncias do MySQL do Amazon Aurora que usam as classes de instância de banco de dados `db.t2`, `db.t3` ou `db.t4g` são mais adequadas para aplicações que não oferecem suporte a uma workload elevada por um período prolongado. As instâncias T são projetadas para fornecer uma performance de linha de base moderada e capacidade de intermitência para obter uma performance significativamente mais alta, conforme necessário para a sua workload. Elas são destinadas a workloads que não usam a CPU inteira com frequência ou de forma consistente, mas que às vezes precisam de intermitência. Recomendamos usar as classes de instância de banco de dados T somente para servidores de desenvolvimento e teste, ou outros servidores que não sejam de produção. Para obter mais detalhes sobre as classes de instância T, consulte [Instâncias expansíveis](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/burstable-performance-instances.html). Se o seu cluster do Aurora tiver mais que 40 TB, não use classes da instância T. Quando o seu banco de dados tiver um grande volume de dados, a sobrecarga de memória para gerenciar objetos de esquema poderá exceder a capacidade de uma instância T. Não habilite o esquema de performance do MySQL em instâncias T do Amazon Aurora MySQL. Se o esquema de performance for habilitado, a instância poderá ficar sem memória. **dica** Se o banco de dados às vezes ficar ocioso, mas outras vezes tiver uma workload substancial, você poderá usar o Aurora Serverless v2 como alternativa às instâncias T. Com o Aurora Serverless v2, você define um intervalo de capacidade e o Aurora reduz ou expande automaticamente o banco de dados, dependendo da workload atual. Para obter mais detalhes sobre uso, consulte [Usar o Aurora Serverless v2](aurora-serverless-v2.md). Para saber as versões do mecanismo de banco de dados que você pode usar com o Aurora Serverless v2, consulte [Requisitos e limitações do Aurora Serverless v2](aurora-serverless-v2.requirements.md). Ao usar uma instância T como instância de banco de dados em um cluster de bancos de dados Aurora MySQL, recomendamos o seguinte: + Use a mesma classe de instância de banco de dados para todas as instâncias em seu cluster de banco de dados. Por exemplo, se você usar `db.t2.medium` para a sua instância de gravador, recomendamos que também use `db.t2.medium` para as suas instâncias de leitor. + Não ajuste nenhuma configuração relacionada à memória, como, por exemplo, `innodb_buffer_pool_size`. O Aurora usa um conjunto altamente ajustado de valores padrão para buffers de memória em instâncias T. Esses padrões especiais são necessários para que o Aurora seja executado em instâncias com restrições de memória. Se você alterar as configurações relacionadas à memória em uma instância T, o mais provável é que encontre um panorama de memória insuficiente, mesmo que a sua alteração se destine a aumentar tamanhos de buffer. + Monitore seu saldo de crédito da CPU (`CPUCreditBalance`) para garantir que esteja em um nível sustentável. Ou seja, se os créditos da CPU estão sendo acumulados no mesmo ritmo em que estão sendo usados. Quando tiver esgotado os créditos da CPU de uma instância, você verá uma queda imediata na CPU disponível e um aumento na latência de leitura e gravação da instância. Essa situação resulta em uma diminuição elevada no performance geral da instância. Se o seu saldo de crédito da CPU não estiver em um nível sustentável, recomendamos que você modifique a sua instância de banco de dados para usar uma das classes de instância de banco de dados R compatíveis (computação de escalabilidade). Para ter mais informações sobre as métricas de monitoramento, consulte [Visualizar métricas no console do Amazon RDS](USER_Monitoring.md). + Monitore o atraso de réplicas (`AuroraReplicaLag`) entre a instância do gravador e as instâncias do leitor. Se uma instância de leitor ficar sem créditos de CPU antes da instância de gravador, o consequente atraso poderá gerar reinicializações frequentes na instância de leitor. Esse resultado é comum quando uma aplicação tem uma carga pesada de operações de leitura distribuídas entre as instâncias do leitor, ao mesmo tempo que a instância do gravador tem uma carga mínima de operações de gravação. Se você observar um aumento constante no atraso de réplicas, certifique-se de que o seu saldo de crédito da CPU para instâncias do gravador no seu cluster de banco de dados não tenha se esgotado. Se o seu saldo de crédito da CPU não estiver em um nível sustentável, recomendamos que você modifique a sua instância de banco de dados para usar uma das classes de instância de banco de dados R compatíveis (computação de escalabilidade). + Mantenha o número de inserções por transação abaixo de 1 milhão para clusters de banco de dados com log binário habilitado. Se o grupo de parâmetros do cluster de banco de dados para o seu cluster de banco de dados tiver o parâmetro `binlog_format` configurado com um valor diferente de `OFF`, seu cluster de banco de dados deve enfrentar condições de falta de memória caso o cluster de banco de dados receba transações grandes que contenham mais de 1 milhão de linhas de inserção. Você pode controlar a métrica (`FreeableMemory`) de memória disponível para determinar se o seu cluster de banco de dados está ficando sem memória disponível. Verifique a métrica (`VolumeWriteIOPS`) de operações de gravação para ver se uma instância de gravação está recebendo uma carga pesada de operações de gravação. Se for esse o caso, recomendamos que você atualize sua aplicação para limitar o número de inserções em uma transação para menos de 1 milhão. Como alternativa, você pode modificar a sua instância para usar uma das classes de instância de banco de dados R compatíveis (computação em escala). ## Otimizar consultas de junção indexadas do Aurora MySQL com pré-busca de chave assíncrona O Aurora MySQL pode usar o atributo de pré-busca de chave assíncrona (AKP) para melhorar a performance das consultas de junção de tabelas entre índices. Esse recurso melhora a performance ao antecipar as linhas necessárias para executar consultas nas quais uma consulta JOIN requer o uso do algoritmo de junção de Batched Key Access (BKA - Acesso a chaves em lote) e dos recursos de otimização de Multi-Range Read (MRR - Leitura de vários intervalos). Para obter mais informações sobre BKA e MRR, consulte [Block nested-loop and batched key access joins](https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html) e [Multi-range read optimization](https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html) na documentação do MySQL. Para usar o recurso AKP, uma consulta deve usar BKA e MRR. Normalmente, essa consulta ocorre quando a cláusula JOIN de uma consulta usa um índice secundário, mas também precisa de algumas colunas do índice primário. Por exemplo, você pode usar a AKP quando uma cláusula JOIN representa uma junção equivalente nos valores de índice entre uma tabela interna grande e uma tabela externa pequena, e o índice é altamente seletivo na tabela maior. A AKP funciona de acordo com o BKA e a MRR para executar uma consulta de índice secundário no primário durante a avaliação da cláusula JOIN. A AKP identifica as linhas necessárias para executar a consulta durante a avaliação da cláusula JOIN. Em seguida, ela usa um thread em segundo plano para carregar de forma assíncrona as páginas contendo as linhas na memória antes de executar a consulta. O recurso AKP está disponível para Aurora MySQL versão 2.10 e posteriores, e versão 3. Para obter mais informações sobre as versões do Aurora MySQL, consulte [Atualizações do mecanismo de banco de dados Amazon Aurora MySQLSuporte de longo prazo (LTS) e versões beta do Amazon Aurora MySQL](AuroraMySQL.Updates.md). ### Como habilitar a pré-busca de chave assíncrona Você pode habilitar o recurso de AKP definindo `aurora_use_key_prefetch`, uma variável de servidor MySQL, para `on`. Por padrão, esse valor é definido como `on`. Contudo, o recurso AKP não poderá ser habilitado enquanto você não habilitar o algoritmo de junção do BKA e desabilitar a funcionalidade MRR baseada em custo. Para fazer isso, é necessário definir os seguintes valores para `optimizer_switch`, uma variável do servidor MySQL: + Defina `batched_key_access` como `on`. Esse valor controla o valor uso do algoritmo de junção do BKA. Por padrão, esse valor é definido como `off`. + Defina `mrr_cost_based` como `off`. Esse valor controla o uso da funcionalidade MRR baseada no custo. Por padrão, esse valor é definido como `on`. No momento, só é possível configurar esses valores no nível da sessão. O exemplo a seguir ilustra como definir esses valores para habilitar a AKP para a sessão atual executando declarações SET. ``` mysql> set @@session.aurora_use_key_prefetch=on; mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off'; ``` Da mesma forma, é possível usar declarações SET para desabilitar a AKP e o algoritmo de junção do BKA e habilitar a funcionalidade MRR baseada no custo novamente para a sessão atual, conforme mostrado no exemplo a seguir. ``` mysql> set @@session.aurora_use_key_prefetch=off; mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on'; ``` Para mais informações sobre os switches otimizadores **batched\$1key\$1access** e **mrr\$1cost\$1based**, consulte [Switchable optimizations](https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html) na documentação do MySQL. ### Como otimizar consultas de pré-busca de chave assíncrona É possível confirmar se uma consulta pode utilizar o recurso AKP. Para fazer isso, use a instrução `EXPLAIN` com a palavra-chave para criar o perfil da consulta antes de executá-la. A declaração `EXPLAIN` fornece informações sobre o plano de execução a ser usado para uma consulta específica. Na saída da instrução `EXPLAIN`, a coluna `Extra` descreve informações adicionais incluídas no plano de execução. Se o recurso AKP se aplica a uma tabela usada na consulta, essa coluna inclui um destes valores: + `Using Key Prefetching` + `Using join buffer (Batched Key Access with Key Prefetching)` O exemplo a seguir mostra o uso de `EXPLAIN` para visualizar o plano de execução de uma consulta que pode utilizar a AKP. ``` mysql> explain select sql_no_cache -> ps_partkey, -> sum(ps_supplycost * ps_availqty) as value -> from -> partsupp, -> supplier, -> nation -> where -> ps_suppkey = s_suppkey -> and s_nationkey = n_nationkey -> and n_name = 'ETHIOPIA' -> group by -> ps_partkey having -> sum(ps_supplycost * ps_availqty) > ( -> select -> sum(ps_supplycost * ps_availqty) * 0.0000003333 -> from -> partsupp, -> supplier, -> nation -> where -> ps_suppkey = s_suppkey -> and s_nationkey = n_nationkey -> and n_name = 'ETHIOPIA' -> ) -> order by -> value desc; +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ | 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index | | 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) | | 2 | SUBQUERY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where | | 2 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index | | 2 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) | +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec) ``` Para obter mais informações sobre o formato de saída `EXPLAIN`, consulte [Extended EXPLAIN output format](https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html) na documentação do MySQL. ## Otimizando grandes consultas de junção do Aurora MySQL com junções hash Quando você precisa unir uma grande quantidade de dados usando uma junção equivalente, um hash join pode melhorar a performance da consulta. Você pode permitir hash joins para o Aurora MySQL. Uma coluna de hash join pode ser qualquer expressão complexa. Em uma coluna de hash join, você pode comparar entre tipos de dados nas seguintes formas: + Você pode comparar tudo na categoria de tipos de dados numéricos precisos, como `int`, `bigint`, `numeric` e `bit`. + Você pode comparar tudo na categoria de tipos de dados numéricos aproximados, como `float` e `double`. + Você pode comparar itens entre tipos de string se os tipos de string tiverem o mesmo conjunto de caracteres e agrupamento. + Você pode comparar itens com tipos de dados de data e hora, se os tipos forem iguais. **nota** Não é possível comparar os tipos de dados em diferentes categorias. As seguintes restrições se aplicam a hash joins para Aurora MySQL: + As junções externas da esquerda para a direita não são compatíveis com o Aurora MySQL versão 2, mas são compatíveis com a versão 3. + Semijunções, como subconsultas, não são compatíveis, a menos que as subconsultas sejam materializadas primeiro. + Não há compatibilidade para atualizações ou exclusões de várias tabelas. **nota** Há suporte a atualizações ou exclusões de uma única tabela. + As colunas BLOB e de tipo de dados espaciais não podem ser colunas unidas em um hash join. ### Permitir hash joins Para permitir junções de hash: + Aurora MySQL versão 2: defina o parâmetro de banco de dados ou o parâmetro de cluster de banco de dados `aurora_disable_hash_join` como `0`. Desativar `aurora_disable_hash_join` define o valor de `optimizer_switch` para `hash_join=on`. + Aurora MySQL versão 3: defina o parâmetro de servidor MySQL `optimizer_switch` como `block_nested_loop=on`. Por padrão, as junções de hash estão habilitadas no Aurora MySQL versão 3 e desabilitadas no Aurora MySQL versão 2. O exemplo a seguir ilustra como permitir junções de hash no Aurora MySQL versão 3. É possível enviar a instrução `select @@optimizer_switch` primeiro para ver que outras configurações estão presentes na string de parâmetros `SET`. A ação de atualizar uma configuração no parâmetro `optimizer_switch` não exclui nem modifica as demais configurações. ``` mysql> SET optimizer_switch='block_nested_loop=on'; ``` **nota** Para o Aurora MySQL versão 3, o suporte para hash joins está disponível em todas as versões secundárias e está habilitado por padrão. Para o Aurora MySQL versão 2, o suporte para junções de hash está disponível em todas as versões secundárias. No Aurora MySQL versão 2, o recurso de hash join é sempre controlado pelo valor de `aurora_disable_hash_join`. Com esta configuração, o otimizador opta por usar uma hash join com base no custo, nas características da consulta e na disponibilidade de recursos. Se a estimativa de custo estiver incorreta, você pode forçar o otimizador a escolher um hash join. Para fazer isso, configure `hash_join_cost_based`, uma variável de servidor MySQL, como `off`. O exemplo a seguir ilustra como forçar o otimizador a escolher um hash join. ``` mysql> SET optimizer_switch='hash_join_cost_based=off'; ``` **nota** Essa configuração substitui as decisões do otimizador baseado em custos. Embora a configuração possa ser útil para testes e desenvolvimento, recomendamos não usá-la na produção. ### Otimizar consultas para hash joins Para descobrir se uma consulta pode aproveitar uma junção hash, use a instrução `EXPLAIN` para obter um perfil da consulta primeiro. A declaração `EXPLAIN` fornece informações sobre o plano de execução a ser usado para uma consulta específica. Na saída da instrução `EXPLAIN`, a coluna `Extra` descreve informações adicionais incluídas no plano de execução. Se um hash join se aplica às tabelas usadas na consulta, essa coluna inclui valores semelhantes aos seguintes: + `Using where; Using join buffer (Hash Join Outer table table1_name)` + `Using where; Using join buffer (Hash Join Inner table table2_name)` O exemplo a seguir mostra como usar EXPLAIN para visualizar o plano de execução para uma consulta de hash join. ``` mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2 -> WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1; +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ | 1 | SIMPLE | hj_small | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 1 | SIMPLE | hj_big | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Hash Join Outer table hj_big) | | 1 | SIMPLE | hj_big2 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (Hash Join Inner table hj_big2) | +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ 3 rows in set (0.04 sec) ``` No resultado, a `Hash Join Inner table` é a tabela usada para criar a tabela hash, e a `Hash Join Outer table` é a tabela usada para testar a tabela hash. Para ter mais informações sobre o formato de resultado `EXPLAIN` estendido, consulte [Extended EXPLAIN Output Format](https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html) na documentação do produto do MySQL. No Aurora MySQL versão 2.08 e posterior, é possível usar dicas SQL para influenciar se uma consulta usa ou não a junção de hash e quais tabelas usar para os lados de compilação e teste da junção. Para obter detalhes, consulte [Dicas do Aurora MySQL](AuroraMySQL.Reference.Hints.md). ## Como usar o Amazon Aurora para escalar leituras para seu banco de dados MySQL Você pode usar o Amazon Aurora com sua instância de banco de dados MySQL para aproveitar os recursos de escalabilidade de leitura do Amazon Aurora e expandir a workload de leitura de sua instância do banco de dados MySQL. Para usar o Aurora para dimensionar a leitura da instância de banco de dados MySQL, crie um cluster de bancos de dados Aurora MySQL e faça dele uma réplica de leitura da instância do banco de dados MySQL. Em seguida, conecte-se ao cluster do Aurora MySQL para processar as consultas de leitura. O banco de dados de origem pode ser uma instância de banco de dados do RDS para MySQL ou um banco de dados MySQL executado externamente em relação ao Amazon RDS. Para obter mais informações, consulte [Escalar leituras para o banco de dados MySQL com o Amazon Aurora](AuroraMySQL.Replication.ReadScaling.md). ## Otimizando as operações de carimbo de data/hora Quando o valor da variável do sistema `time_zone` é definido como `SYSTEM`, cada chamada de função do MySQL que requer um cálculo de fuso horário faz uma chamada à biblioteca do sistema. Ao executar instruções SQL que retornam ou alteram esses valores de `TIMESTAMP` em alta simultaneidade, você poderá sofrer maior latência, contenção de bloqueios e uso da CPU. Para obter mais informações, consulte [time\$1zone](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_time_zone) na documentação do MySQL. Para evitar esse comportamento, recomendamos que você altere o valor do parâmetro `time_zone` de cluster de banco de dados para `UTC`. Para obter mais informações, consulte [Modificar parâmetros em um grupo de parâmetros de cluster de banco de dadosno Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md). Embora o parâmetro `time_zone` seja dinâmico (não requer a reinicialização do servidor de banco de dados), o novo valor é usado somente para novas conexões. Para garantir que todas as conexões sejam atualizadas para usar o novo valor de `time_zone`, recomendamos que você recicle as conexões da aplicação depois de atualizar o parâmetro de cluster de banco de dados. ## Erros de estouro de ID de índice virtual O Aurora MySQL limita os valores de ID de índice virtual a 8 bits com o objetivo de evitar um problema causado pelo formato undo no MySQL. Se um índice exceder o limite de ID de índice virtual, seu cluster poderá ficar indisponível. Quando um índice se aproxima do limite de ID de índice virtual ou quando você tenta criar um índice acima desse limite, o RDS pode emitir o código de erro `63955` ou o código de aviso `63955`. Para solucionar um erro de limite de ID de índice virtual, recomendamos recriar o banco de dados com um despejo lógico e restauração. Para ter mais informações sobre despejo lógico e restauração para o Amazon Aurora MySQL, consulte [Migrate very large databases to Amazon Aurora MySQL using MyDumper and MyLoader](https://aws.amazon.com/blogs/database/migrate-very-large-databases-to-amazon-aurora-mysql-using-mydumper-and-myloader/). Para ter mais informações sobre como acessar logs de erro no Amazon Aurora, consulte [Monitorar arquivos de log do Amazon Aurora](USER_LogAccess.md).