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.

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:

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:

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:

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:

As seguintes restrições se aplicam a hash joins para Aurora MySQL:

Permitir hash joins

Para permitir junções de hash:

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';

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';

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:

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 Escalar leituras para o banco de dados MySQL com o Amazon Aurora.

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 do cluster de banco de dados no Amazon Aurora.

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.