Melhores práticas do Amazon Aurora MySQL - Amazon Aurora
Como determinar a qual instância de banco de dados você está conectadoPráticas recomendadas para a performance e escalabilidade do Aurora MySQLPráticas recomendadas para alta disponibilidade do Aurora MySQLRecomendações do Aurora MySQL

Melhores práticas do Amazon Aurora MySQL

Este tópico inclui informações sobre as melhores práticas e opções para usar ou migrar dados para um cluster de banco de dados Amazon Aurora MySQL. As informações neste tópico resumem e reiteram algumas das diretrizes e procedimentos que você pode encontrar em Como gerenciar um cluster de banco de dados do Amazon Aurora.

Como determinar a qual instância de banco de dados você está conectado

Para determinar a qual instância de banco de dados dentro de um cluster de banco de dados Aurora MySQL uma conexão está conectada, verifique a variável global innodb_read_only, conforme mostrado no exemplo a seguir.

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

Se você estiver conectado a uma instância de banco de dados de leitor, a variável innodb_read_only será definida como ON. Se você estiver conectado a uma instância de banco de dados de gravador, como, por exemplo, uma instância primária em um cluster provisionado, essa configuração será OFF.

Essa abordagem pode ser útil se você quiser adicionar lógica ao seu código de aplicação para equilibrar a workload ou para garantir que uma operação de gravação esteja usando a conexão correta.

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.

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.

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

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.

  • 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 e Multi-range read optimization 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 MySQL.

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_key_access e mrr_cost_based, consulte Switchable optimizations 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 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 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.

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 ter mais informações, consulte Como usar o Amazon Aurora para escalar leituras para seu banco de dados MySQL.

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_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 ter mais informações, consulte Modificar parâmetros em um grupo de parâmetros de cluster de banco de dados.

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.

Práticas recomendadas para alta disponibilidade do Aurora MySQL

Você pode aplicar as práticas recomendadas a seguir para melhorar a disponibilidade dos seus clusters do Aurora MySQL.

Como utilizar o Amazon Aurora para recuperação de desastres com seus bancos de dados MySQL

Você pode usar o Amazon Aurora com sua instância de banco de dados MySQL para criar um backup fora do local para recuperação de desastres. Para usar o Aurora para a recuperação de desastres da instância de banco de dados MySQL, crie um cluster de bancos de dados Amazon Aurora e faça dele uma réplica de leitura da instância de banco de dados MySQL. Isso se aplica a uma instância de banco de dados do RDS for MySQL ou a um banco de dados MySQL executado externamente em relação ao Amazon RDS.

Importante

Ao configurar a replicação entre uma instância de banco de dados MySQL e um cluster de bancos de dados Amazon Aurora MySQL, você deve monitorar a replicação para garantir que ela permaneça íntegra e repará-la, se necessário.

Para obter instruções sobre como criar um cluster de bancos de dados Amazon Aurora MySQL e torná-lo uma réplica de leitura da instância de banco de dados MySQL, siga o procedimento em Como usar o Amazon Aurora para escalar leituras para seu banco de dados MySQL.

Para ter mais informações sobre modelos de recuperação de desastres, consulte Como escolher a melhor opção de recuperação de desastres para seu cluster do Amazon Aurora MySQL.

Migrar do MySQL para o Amazon Aurora MySQL com o tempo de inatividade reduzido

Ao importar dados de um banco de dados MySQL que suporte uma aplicação on-line para um cluster de bancos de dados Amazon Aurora MySQL, você pode reduzir o tempo de interrupção do serviço durante a migração. Para tal, você pode usar o procedimento descrito em Importar dados para uma instância de banco de dados MariaDB ou MySQL do Amazon RDS com tempo de inatividade reduzido no Guia do usuário do Amazon Relational Database Service. Este procedimento pode ser especialmente útil se você está trabalhando com um banco de dados muito grande. É possível usar o procedimento para reduzir o custo da importação, minimizando a quantidade de dados que será passada pela rede para a AWS.

O procedimento lista as etapas para transferir uma cópia dos dados do banco de dados para uma instância do Amazon EC2 e importar os dados para uma nova instância do banco de dados do RDS para MySQL. Como o Amazon Aurora é compatível com o MySQL, você pode usar um cluster de bancos de dados Amazon Aurora para a instância do banco de dados MySQL do Amazon RDS de destino.

Evitar baixa performance, reinicialização automática e failover para instâncias de banco de dados do Aurora MySQL

Se você estiver executando uma workload pesada ou workloads que ultrapassam os recursos alocados de sua instância de banco de dados, você pode esgotar os recursos nos quais está executando sua aplicação e o banco de dados do Aurora. Para obter métricas em sua instância de banco de dados, como utilização de CPU, uso de memória e número de conexões de banco de dados utilizadas, você pode consultar as métricas fornecidas pelo Amazon CloudWatch, Performance Insights e Enhanced Monitoring. Para obter informações sobre como monitorar a instância de banco de dados, consulte Monitorar métricas em um cluster do Amazon Aurora.

Se sua workload esgotar os recursos que você está utilizando, sua instância de banco de dados poderá ficar lenta, ser reiniciada ou até mesmo realizar o failover para outra instância de banco de dados. Para evitar isso, monitore a utilização dos recursos, examine a workload em execução em sua instância de banco de dados e faça otimizações quando necessário. Se as otimizações não melhorarem as métricas da instância e mitigarem a exaustão de recursos, considere aumentar a escala verticalmente de sua instância de banco de dados antes de atingir seus limites. Para ter mais informações sobre as classes de instância de banco de dados disponíveis e suas especificações, consulte Classes de instância de banco de dados Aurora.

Recomendações do Aurora MySQL

Os recursos a seguir estão disponíveis no Aurora MySQL compatíveis com o MySQL. Contudo, eles apresentam problemas de performance, escalabilidade, estabilidade ou compatibilidade no ambiente do Aurora. Assim, recomendamos que você siga determinadas diretrizes no uso desses recursos. Por exemplo, recomendamos não usar determinados recursos para implantações de produção do Aurora.

Utilizar a replicação de vários threads no Aurora MySQL versão 3

Por padrão, o Aurora usa replicação de único thread quando um cluster de bancos de dados Aurora MySQL é usado como uma réplica de leitura para replicação de log binário.

Embora o Aurora MySQL não proíba a replicação de vários threads, esse recurso só é compatível com o Aurora MySQL versão 3.

O Aurora MySQL versão 2 herdou diversos problemas relacionados à replicação de vários threads do MySQL. Para essa versão, recomendamos não usar a replicação de vários threads em ambientes de produção.

Se você usa a replicação de vários threads, recomendamos testar qualquer uso completamente.

Para ter mais informações sobre o uso de replicação no Amazon Aurora, consulte Replicação com o Amazon Aurora. Para obter informações sobre a replicação de vários threads no Aurora MySQL versão 3, consulte Replicação de logs binários com threads múltiplos (Aurora MySQL versão 3).

Invocar funções do AWS Lambda com o uso de funções nativas do MySQL

Recomendamos usar as funções nativas do MySQL lambda_sync e lambda_async para invocar funções do Lambda.

Se você estiver usando o procedimento mysql.lambda_async obsoleto, recomendamos que você faça chamadas do procedimento mysql.lambda_async em um procedimento armazenado. Você pode chamar este procedimento armazenado a partir de origens diferentes, como triggers ou código do cliente. Essa abordagem pode ajudar a evitar problemas de incompatibilidade de impedância e facilita para seus programadores de banco de dados invocar funções do Lambda.

Para ter mais informações sobre como invocar funções do Lambda por meio do Amazon Aurora, consulte Invocar uma função do Lambda a partir de um cluster de banco de dados do Amazon Aurora MySQL.

Como evitar transações XA com o Amazon Aurora MySQL

Recomendamos que você não use transações eXtended Architecture (XA) com o Aurora MySQL, porque elas podem gerar longos tempos de recuperação se a XA estiver no estado PREPARED. Se você precisa usar transações XA com o Aurora MySQL, siga estas práticas recomendadas:

  • Não deixe uma transação XA aberta no estado PREPARED.

  • Mantenha as transações XA menores o possível.

Para ter mais informações sobre o uso de transações XA com o MySQL, consulte Transações XA na documentação do MySQL.

Como manter chaves externas ativadas durante instruções DML

Recomendamos que você não execute nenhuma instrução de linguagem de definição de dados (DDL) quando a variável foreign_key_checks estiver definida como 0 (desativada).

Se você precisar inserir ou atualizar linhas que exigem uma violação transitória das chaves externas, siga estas etapas:

  1. Defina foreign_key_checks como 0.

  2. Faça as alterações na sua linguagem de manipulação de dados (DML).

  3. Verifique se as alterações realizadas não violam restrições de chaves externas.

  4. Defina foreign_key_checks como 1 (ativada).

Além disso, siga estas práticas recomendadas adicionais para restrições de chaves externas:

  • Verifique se suas aplicações cliente não definiram a variável foreign_key_checks como 0, como parte da variável init_connect.

  • Se a restauração a partir de um backup lógico, como mysqldump, apresentar falha ou não for concluída, defina foreign_key_checks como 1 antes de iniciar qualquer outra operação na mesma sessão. Um backup lógico define foreign_key_checks como 0 ao ser inicializado.

Configurar a frequência com que o buffer de log é liberado

No MySQL Community Edition, para tornar as transações duráveis, o buffer de log do InnoDB deve ser liberado para um armazenamento durável. Use o parâmetro innodb_flush_log_at_trx_commit para configurar a frequência com que o buffer de log é liberado para o disco.

Quando você define o parâmetro innodb_flush_log_at_trx_commit como o valor padrão de 1, o buffer de log é liberado em cada confirmação de transação. Essa configuração ajuda a manter o banco de dados compatível com ACID. Recomendamos que você mantenha a configuração padrão 1.

Alterar innodb_flush_log_at_trx_commit para um valor não padrão pode ajudar a reduzir a latência da linguagem de manipulação de dados (DML), mas sacrifica a durabilidade dos registros de log. Essa falta de durabilidade torna o banco de dados incompatível com ACID. Recomendamos que seus bancos de dados estejam em conformidade com ACID para evitar o risco de perda de dados em caso de uma reinicialização do servidor. Para ter mais informações sobre esse parâmetro, consulte innodb_flush_log_at_trx_commit na documentação do MySQL.

No Aurora MySQL, o processamento de redo log é transferido para a camada de armazenamento, portanto, nenhuma descarga dos arquivos de log ocorre na instância de banco de dados. Quando uma gravação é emitida, os redo logs são enviados da instância de banco de dados do gravador diretamente ao volume do cluster do Aurora. As únicas gravações que atravessam a rede são os registros de redo log. Nenhuma página é gravada na camada do banco de dados.

Por padrão, cada thread que confirma uma transação aguarda a confirmação do volume do cluster do Aurora. Essa confirmação indica que esse registro e todos os registros anteriores de redo log foram gravados e atingiram o quórum. A persistência dos registros de log e a obtenção do quórum tornam a transação durável, seja por meio de confirmação automática ou confirmação explícita. Para ter mais informações sobre a arquitetura de armazenamento do Aurora, consulte Amazon Aurora storage demystified (Armazenamento desmistificado do Amazon Aurora).

O Aurora MySQL não libera logs para arquivos de dados como o MySQL Community Edition. No entanto, você pode usar o parâmetro innodb_flush_log_at_trx_commit para reduzir as restrições de durabilidade ao gravar registros de redo log no volume do cluster do Aurora.

Para o Aurora MySQL versão 2:

  • innodb_flush_log_at_trx_commit = 0 ou 2: o banco de dados não espera pela confirmação de que os registros de log redo sejam gravados no volume do cluster do Aurora.

  • innodb_flush_log_at_trx_commit = 1: o banco de dados aguarda a confirmação de que os registros de log redo foram gravados no volume do cluster do Aurora.

Para o Aurora MySQL versão 3:

  • innodb_flush_log_at_trx_commit = 0: o banco de dados não espera pela confirmação de que os registros de log redo sejam gravados no volume do cluster do Aurora.

  • innodb_flush_log_at_trx_commit = 1 ou 2: o banco de dados aguarda a confirmação de que os registros de log redo foram gravados no volume do cluster do Aurora.

Portanto, para obter o mesmo comportamento não padrão no Aurora MySQL versão 3 que seria obtido com o valor definido como 0 ou 2 no Aurora MySQL versão 2, defina o parâmetro como 0.

Embora essas configurações possam diminuir a latência do DML para o cliente, elas também podem ocasionar perda de dados no caso de failover ou reinicialização. Portanto, recomendamos que você mantenha o parâmetro innodb_flush_log_at_trx_commit definido como o valor padrão de 1.

Embora a perda de dados possa ocorrer tanto no MySQL Community Edition quanto no Aurora MySQL, o comportamento difere em cada banco de dados devido às diferentes arquiteturas. Essas diferenças de arquitetura podem ocasionar vários graus de perda de dados. Para garantir que o banco de dados seja compatível com ACID, sempre defina innodb_flush_log_at_trx_commit como 1.

nota

No Aurora MySQL versão 3, antes de alterar innodb_flush_log_at_trx_commit para um valor diferente de 1, é necessário primeiro alterar o valor de innodb_trx_commit_allow_data_loss para 1. Ao fazer isso, você reconhece o risco de perda de dados.

Minimizar e solucionar problemas de deadlocks do Aurora MySQL

Usuários que executam workloads que regularmente enfrentam violações de restrições em índices secundários exclusivos ou chaves estrangeiras, ao modificar registros na mesma página de dados simultaneamente, podem ter maiores deadlocks e tempos de espera de bloqueio. Esses deadlocks e tempos limite são causados por uma correção de bug do MySQL Community Edition.

Essa correção está incluída nas versões 5.7.26 e posteriores do MySQL Community Edition e foi transferida para as versões 2.10.3 e posteriores do Aurora MySQL. A correção é necessária para reforçar a capacidade de serialização por meio da implementação de bloqueios adicionais para esses tipos de operação de linguagem de manipulação de dados (DML) nas alterações feitas nos registros em uma tabela do InnoDB. Esse problema foi descoberto como parte de uma investigação sobre problemas de deadlock introduzidos por uma correção de bug anterior do MySQL Community Edition.

A correção alterou o tratamento interno da reversão parcial de uma atualização de tupla (linha) no mecanismo de armazenamento InnoDB. Operações que geram violações de restrição em chaves estrangeiras ou índices secundários exclusivos causam reversão parcial. Isso inclui, entre outros, as instruções simultâneas INSERT...ON DUPLICATE KEY UPDATE, REPLACE INTO, e INSERT IGNORE (upserts).

Nesse contexto, a reversão parcial não se refere à reversão de transações em nível de aplicação, mas sim a uma reversão interna do InnoDB das alterações em um índice em cluster, quando uma violação de restrição é encontrada. Por exemplo, um valor de chave duplicado é encontrado durante uma operação de upsert.

Em uma operação normal de inserção, o InnoDB cria atomicamente entradas de índice secundárias e em cluster para cada índice. Se o InnoDB detectar um valor duplicado em um índice secundário exclusivo durante uma operação de upsert, a entrada inserida no índice em cluster deverá ser revertida (reversão parcial) e a atualização deverá ser aplicada à linha duplicada existente. Durante essa etapa interna de reversão parcial, o InnoDB deve bloquear cada registro visto como parte da operação. A correção garante a capacidade de serialização da transação ao introduzir bloqueios adicionais após a reversão parcial.

Minimizar os deadlocks do InnoDB

Você pode adotar as abordagens a seguir para reduzir a frequência de deadlocks em sua instância de banco de dados. Mais exemplos podem ser encontrados na documentação do MySQL.

  1. Para reduzir as chances de deadlock, confirme as transações imediatamente depois de fazer um conjunto relacionado de alterações. Você pode fazer isso dividindo transações grandes (atualizações de várias linhas entre confirmações) em transações menores. Se você estiver inserindo linhas em lote, tente reduzir os tamanhos das inserções em lote, especialmente ao usar as operações de upsert mencionadas anteriormente.

    Para reduzir o número de possíveis reversões parciais, você pode tentar algumas das seguintes abordagens:

    1. Substitua as operações de inserção em lote pela inserção de uma linha por vez. Isso pode reduzir o tempo em que os bloqueios são mantidos por transações que podem ter conflitos.

    2. Em vez de usar REPLACE INTO, reescreva a instrução SQL como uma transação com várias instruções, como a seguinte:

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. Em vez de usar INSERT...ON DUPLICATE KEY UPDATE, reescreva a instrução SQL como uma transação com várias instruções, como a seguinte:

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. Evite transações de longa execução, ativas ou inativas, que possam ficar bloqueadas. Isso inclui sessões interativas do cliente MySQL que podem ficar abertas por um longo período com uma transação não confirmada. Ao otimizar os tamanhos das transações ou dos lotes, o impacto pode variar dependendo de vários fatores, como simultaneidade, número de duplicatas e estrutura da tabela. Todas as alterações devem ser implementadas e testadas com base em sua workload.

  3. Em algumas situações, deadlocks podem ocorrer quando duas transações tentam acessar os mesmos conjuntos de dados, em uma ou várias tabelas, em ordens diferentes. Para evitar isso, você pode modificar as transações para acessar os dados na mesma ordem, serializando o acesso. Por exemplo, crie uma fila de transações a serem concluídas. Essa abordagem pode ajudar a evitar deadlocks quando ocorrem várias transações simultaneamente.

  4. Adicionar índices cuidadosamente escolhidos às suas tabelas pode melhorar a seletividade e reduzir a necessidade de acessar linhas, o que ocasiona menos bloqueios.

  5. Se você encontrar um bloqueio de lacunas, poderá modificar o nível de isolamento da transação READ COMMITTED para a sessão ou a transação a fim de evitá-lo. Para ter mais informações sobre os níveis de isolamento do InnoDB e seus comportamentos, consulte Níveis de isolamento de transações na documentação do MySQL.

nota

Embora você possa tomar precauções para reduzir a possibilidade de ocorrência de deadlocks, eles são um comportamento esperado do banco de dados e ainda podem ocorrer. As aplicações devem ter a lógica necessária para lidar com deadlocks ao encontrá-los. Por exemplo, implemente a lógica de repetição e recuo na aplicação. É melhor resolver a causa raiz do problema, mas, se ocorrer um deadlock, a aplicação terá a opção de esperar e tentar novamente.

Monitorar deadlocks do InnoDB

Deadlocks podem ocorrer no MySQL quando as transações da aplicação tentam fazer um bloqueio em nível de tabela e linha de uma forma que resulta em espera circular. Um deadlock ocasional do InnoDB não é necessariamente um problema, porque o mecanismo de armazenamento do InnoDB detecta a condição imediatamente e reverte uma das transações de forma automática. Se você se deparar com deadlocks com frequência, recomendamos revisar e modificar sua aplicação para amenizar problemas de performance e evitá-los. Quando a detecção de deadlocks está ativada (o padrão), o InnoDB os detecta automaticamente nas transações e reverte uma ou mais transações para romper o deadlock. O InnoDB tenta selecionar pequenas transações para reverter, caso em que o tamanho de uma transação é determinado pelo número de linhas inseridas, atualizadas ou excluídas.

  • Instrução SHOW ENGINE: a instrução SHOW ENGINE INNODB STATUS \G contém detalhes do deadlock mais recente encontrado no banco de dados desde a última reinicialização.

  • Log de erros do MySQL: se você encontrar deadlocks frequentes em que a saída da instrução SHOW ENGINE é inadequada, você poderá ativar o parâmetro de cluster de banco de dados innodb_print_all_deadlocks.

    Quando esse parâmetro é ativado, as informações sobre todos os deadlocks nas transações do usuário do InnoDB são registradas no log de erros do Aurora MySQL.

  • Métricas do Amazon CloudWatch: também recomendamos que você monitore proativamente os deadlocks usando a métrica do CloudWatch Deadlocks. Para ter mais informações, consulte Métricas no nível da instância do Amazon Aurora.

  • Amazon CloudWatch Logs: com o CloudWatch Logs, você pode visualizar métricas, analisar dados de log e criar alarmes em tempo real. Para ter mais informações, consulte Monitorar erros no Amazon Aurora MySQL e no Amazon RDS para MySQL usando o Amazon CloudWatch e enviar notificações usando o Amazon SNS.

    Usando o CloudWatch Logs com innodb_print_all_deadlocks ativado, você pode configurar alarmes para receber notificação quando o número de deadlocks exceder determinado limite. Para definir um limite, recomendamos que você observe suas tendências e use um valor com base em sua workload normal.

  • Performance Insights: ao usar o Performance Insights, você pode monitorar as métricas innodb_deadlocks e innodb_lock_wait_timeout. Para ter mais informações sobre essas métricas, consulte Contadores não nativos para o Aurora MySQL.