Constructos do SQL para consulta paralela no Aurora MySQL

Na seção a seguir, você pode encontrar mais detalhes sobre por que determinadas instruções SQL usam ou não usam a consulta paralela. Esta seção também detalha como os recursos do Aurora MySQL interagem com a consulta paralela. Essas informações podem ajudar a diagnosticar problemas de performance de um cluster que usa a consulta paralela ou a entender como a consulta paralela pode ser aplicada em sua workload específica.

A decisão de usar a consulta paralela depende de muitos fatores que ocorrem no momento em que a instrução está em execução. Assim, a consulta paralela pode ser usada para algumas consultas sempre, nunca ou somente sob determinadas condições.

Instrução EXPLAIN

Conforme mostrado em exemplos ao longo desta seção, a instrução EXPLAIN indica se cada estágio de uma consulta se qualifica ou não à consulta paralela. Ela também indica quais aspectos de uma consulta são empurrados para a camada de armazenamento. Os itens mais importantes no plano de consulta são os seguintes:

Por exemplo, considere a seguinte saída da instrução EXPLAIN.

mysql> explain select p_name, p_mfgr from part
    -> where p_brand is not null
    -> and upper(p_type) is not null
    -> and round(p_retailprice) is not null;
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table |...| rows     | Extra                                                                      |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part  |...| 20427936 | Using where; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+

A informação da coluna Extra mostra que cinco colunas são extraídas de cada linha para avaliar as condições da consulta e construir o conjunto de resultados. Um predicado de WHERE envolve um filtro, isto é, uma coluna que é diretamente testada na cláusula WHERE. Duas cláusulas WHERE exigem a avaliação das expressões mais complicadas, nesse caso envolvendo chamadas de funções. O campo 0 extra confirma que todas as operações na cláusula WHERE são empurradas para a camada de armazenamento como parte do processamento de consulta paralela.

Nos casos em que a consulta paralela não é escolhida, normalmente é possível deduzir a razão a partir das outras colunas da saída de EXPLAIN. Por exemplo, o valor de rows pode ser muito pequeno ou a coluna possible_keys pode indicar que a consulta pode usar uma busca com índices, em vez de uma varredura com muitos dados. O exemplo a seguir mostra uma consulta em que o otimizador pode estimar que a consulta verificará apenas um pequeno número de linhas. Ele faz isso com base nas características da chave primária. Nesse caso, a consulta paralela não é necessária.

mysql> explain select count(*) from part where p_partkey between 1 and 100;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | part  | range | PRIMARY       | PRIMARY | 4       | NULL |   99 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

A saída que mostra se a consulta paralela será usada leva em conta todos os fatores disponíveis no momento em que a instrução EXPLAIN é executada. O otimizador pode fazer uma escolha diferente quando a consulta é de fato executada, caso a situação tenha sido alterada nesse meio-tempo. Por exemplo, EXPLAIN pode relatar que uma instrução usará a consulta paralela. Porém, quando a consulta é executada mais tarde, ela poderá não usar a consulta paralela devido às condições atuais. Essas condições podem incluir várias outras consultas paralelas em execução simultaneamente. Elas também podem incluir linhas sendo excluídas da tabela, um novo índice sendo criado, tempo excessivo em uma transação aberta, e assim por diante.

Cláusula WHERE

Para que uma consulta use a otimização de consulta paralela, ela deve incluir uma cláusula WHERE.

A otimização de consulta paralela acelera vários tipos de expressões usadas na cláusula WHERE:

Por exemplo, a consulta a seguir faz uma varredura completa na tabela e processa todos os valores da coluna P_BRAND. No entanto, ela não usa a consulta paralela porque não inclui nenhuma cláusula WHERE.

mysql> explain select count(*), p_brand from part group by p_brand;
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | part  | ALL  | NULL          | NULL | NULL    | NULL | 20427936 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

Por outro lado, a seguinte consulta inclui predicados de WHERE que filtram os resultados e, portanto, a consulta paralela pode ser aplicada:

mysql> explain select count(*), p_brand from part where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000
    -> group by p_brand;
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                                                       |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using temporary; Using filesort; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+

Se o otimizador estimar que o número de linhas retornadas para um bloco de consultas for pequeno, a consulta paralela não será usada para esse bloco. O exemplo a seguir mostra um caso em que um operador "maior que" na coluna da chave primária é aplicado em milhões de linhas, o que determina o uso da consulta paralela. O teste inverso com "menor que" estima a aplicação em algumas linhas apenas e não usa a consulta paralela.

mysql> explain select count(*) from part where p_partkey > 10;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

mysql> explain select count(*) from part where p_partkey < 10;
+----+...+------+--------------------------+
| id |...| rows | Extra                    |
+----+...+------+--------------------------+
|  1 |...|    9 | Using where; Using index |
+----+...+------+--------------------------+

Linguagem de definição de dados (DDL)

No Aurora MySQL versão 2, a consulta paralela só está disponível para tabelas cujas operações de linguagem de definição de dados (DDL) rápida estão pendentes. No Aurora MySQL versão 3, é possível utilizar a consulta paralela em uma tabela ao mesmo tempo que uma operação de DDL instantânea.

A DDL instantânea no Aurora MySQL versão 3 substitui o recurso de DDL rápida no Aurora MySQL versão 2. Para saber mais sobre a DDL instantânea, consulte DDL instantânea (Aurora MySQL versão 3).

Tipos de dados de coluna

No Aurora MySQL versão 3, a consulta paralela pode funcionar com tabelas que contêm colunas com os tipos de dados TEXT,BLOB,JSON eGEOMETRY. Ela também pode funcionar com colunas VARCHAR e CHAR com um comprimento máximo declarado superior a 768 bytes. Se a consulta fizer referência a colunas que contenham tipos de objetos grandes, o trabalho adicional para recuperá-las adicionará uma certa sobrecarga ao processamento das consultas. Nesse caso, verifique se a consulta é capaz de omitir as referências a essas colunas. Caso contrário, execute benchmarks para confirmar se essas consultas são mais rápidas com a consulta paralela habilitada ou desabilitada.

No Aurora MySQL versão 2, a consulta paralela apresenta estas limitações para tipos de objetos grandes:

Tabelas particionadas

É possível utilizar tabelas particionadas com consulta paralela no Aurora MySQL versão 3. Como essas tabelas particionadas são representadas internamente como várias tabelas menores, uma consulta que utiliza a consulta paralela em uma tabela não particionada pode não utilizar a consulta paralela em uma tabela particionada idêntica. O Aurora MySQL considera se cada partição é grande o suficiente a ponto de se qualificar para a otimização de consulta paralela, em vez de avaliar o tamanho da tabela inteira. Verifique se a variável de status Aurora_pq_request_not_chosen_small_table é incrementada quando uma consulta em uma tabela particionada não utiliza a consulta paralela na ocasião esperada.

Por exemplo, considere uma tabela particionada com PARTITION BY HASH (column) PARTITIONS 2 e outra tabela particionada com PARTITION BY HASH (column) PARTITIONS 10. Na tabela com duas partições, estas são cinco vezes maiores que a tabela com dez partições. Portanto, é mais provável que a consulta paralela seja utilizada para consultas na tabela contendo menos partições. No exemplo a seguir, a tabela PART_BIG_PARTITIONS apresenta duas partições e PART_SMALL_PARTITIONS apresenta dez partições. Com dados idênticos, são maiores as chances de que a consulta paralela seja utilizada para a tabela com menos partições grandes.

mysql> explain select count(*), p_brand from part_big_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
| id | select_type | table               | partitions | Extra                                                                                                             |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | part_big_partitions | p0,p1      | Using where; Using temporary; Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra; 1 group-bys, 1 aggrs) |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+

mysql> explain select count(*), p_brand from part_small_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+-----------------------+-------------------------------+------------------------------+
| id | select_type | table                 | partitions                    | Extra                        |
+----+-------------+-----------------------+-------------------------------+------------------------------+
|  1 | SIMPLE      | part_small_partitions | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | Using where; Using temporary |
+----+-------------+-----------------------+-------------------------------+------------------------------+

Funções agregadas, cláusulas GROUP BY e cláusulas HAVING.

As consultas que envolvem as funções agregadas frequentemente são boas candidatas ao uso da consulta paralela, pois envolvem a varredura de grandes números de linhas em tabelas grandes.

No Aurora MySQL 3, a consulta paralela pode otimizar chamadas de função agregadas na lista de seleção e na cláusula HAVING.

Antes do Aurora MySQL 3, chamadas de funções agregadas na lista selecionada ou na cláusula HAVING não são enviadas para a camada de armazenamento. No entanto, a consulta paralela ainda pode melhorar a performance dessas consultas com funções agregadas. Para isso, ela primeiro extrai os valores das colunas das páginas de dados brutos em paralelo na camada de armazenamento. Em seguida, ela transmite esses valores de volta para o nó de cabeçalho em um formato de tupla compacta, em vez de páginas inteiras de dados. Como sempre, a consulta precisa ter pelo menos um predicado WHERE para que a consulta paralela seja ativada.

Os exemplos simples a seguir ilustram os tipos de consultas agregadas que podem se beneficiar da consulta paralela. Eles fazem isso retornando resultados intermediários de forma compacta para o nó de cabeçalho ou filtrando as linhas que não encontram correspondência dos resultados intermediários, ou ambos.

mysql> explain select sql_no_cache count(distinct p_brand) from part where p_mfgr = 'Manufacturer#5';
+----+...+----------------------------------------------------------------------------+
| id |...| Extra                                                                      |
+----+...+----------------------------------------------------------------------------+
|  1 |...| Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------------------------------------------------------------------------+

mysql> explain select sql_no_cache p_mfgr from part where p_retailprice > 1000 group by p_mfgr having count(*) > 100;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (3 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

Chamadas de funções na cláusula WHERE

O Aurora pode aplicar a otimização de consulta paralela em chamadas para a maioria das funções incorporadas na cláusula WHERE. A paralelização dessas chamadas de funções alivia alguma workload da CPU a partir do nó de cabeçalho. A avaliação das funções de predicado em paralelo durante o estágio inicial da consulta ajuda o Aurora a minimizar a quantidade de dados transmitidos e processados nos estágios posteriores.

No momento, a paralelização não se aplica às chamadas de funções na lista selecionada. Essas funções são avaliadas pelo nó de cabeçalho, mesmo que as chamadas de funções idênticas apareçam na cláusula WHERE. Os valores originais das colunas relevantes são incluídos nas tuplas transmitidas dos nós de armazenamento de volta até o nó de cabeçalho. O nó de cabeçalho realiza qualquer transformação, como UPPER, CONCATENATE e assim por diante, para produzir os valores finais do conjunto de resultados.

No exemplo a seguir, a consulta paralela paraleliza a chamada LOWER porque ela aparece na cláusula WHERE. A consulta paralela não afeta as chamadas SUBSTR e UPPER porque elas aparecem na lista selecionada.

mysql> explain select sql_no_cache distinct substr(upper(p_name),1,5) from part
    -> where lower(p_name) like '%cornflower%' or lower(p_name) like '%goldenrod%';
+----+...+---------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                       |
+----+...+---------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+---------------------------------------------------------------------------------------------+

As mesmas considerações se aplicam a outras expressões, como as expressões CASE ou os operadores LIKE. O exemplo a seguir mostra que a consulta paralela avalia a expressão CASE e os operadores LIKE na cláusula WHERE.

mysql> explain select p_mfgr, p_retailprice from part
    -> where p_retailprice > case p_mfgr
    ->   when 'Manufacturer#1' then 1000
    ->   when 'Manufacturer#2' then 1200
    ->   else 950
    -> end
    -> and p_name like '%vanilla%'
    -> group by p_retailprice;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (4 columns, 0 filters, 2 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

Cláusula LIMIT

No momento, a consulta paralela não é usada para blocos de consulta que incluam uma cláusula LIMIT. A consulta paralela ainda pode ser usada em fases anteriores da consulta com GROUP by, ORDER BY ou junções.

Operadores de comparação

O otimizador estima quantas linhas serão examinadas para avaliar os operadores de comparação e determina se vai usar a consulta paralela com base nessa estimativa.

O primeiro exemplo, a seguir, mostra que uma comparação de igualdade com uma coluna de chave primária pode ser realizada de maneira eficiente sem a consulta paralela. O segundo exemplo, a seguir, mostra que uma comparação semelhante com uma coluna não indexada exige a varredura de milhões de linhas e, portanto, pode se beneficiar da consulta paralela.

mysql> explain select * from part where p_partkey = 10;
+----+...+------+-------+
| id |...| rows | Extra |
+----+...+------+-------+
|  1 |...|    1 | NULL  |
+----+...+------+-------+

mysql> explain select * from part where p_type = 'LARGE BRUSHED BRASS';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

As mesmas considerações se aplicam aos testes de desigualdade e a comparações com intervalos, como menor que, maior que, igual a ou BETWEEN. O otimizador estima o número de linhas que serão examinadas e determina se vale a pena usar a consulta paralela com base no volume total de E/S.

Junções

As consultas que usam junções com grandes tabelas normalmente envolvem operações com muitos dados que se beneficiam da otimização de consulta paralela. As comparações de valores de colunas entre várias tabelas (ou seja, os próprios predicados das junções) não são paralelizadas no momento. No entanto, a consulta paralela pode empurrar uma parte do processamento interno de outras fases da junção, como a construção do filtro Bloom durante uma junção hash. A consulta paralela pode ser aplicada a consultas de junções, mesmo sem uma cláusula WHERE. Portanto, uma consulta de junção é uma exceção à regra de que uma cláusula WHERE é necessária para o uso da consulta paralela.

Cada fase do processamento da junção é avaliado para verificar se ela se qualifica para a consulta paralela. Se mais de uma fase puder usar a consulta paralela, essas fases serão executadas em sequência. Assim, cada consulta de junção conta como uma única sessão de consulta paralela em termos de limites de simultaneidade.

Por exemplo, quando uma consulta de junção incluir predicados WHERE para filtrar as linhas de uma das tabelas associadas, essa opção de filtragem poderá usar a consulta paralela. Em um outro exemplo, vamos supor que uma consulta de junção use o mecanismo de junção hash para unir uma tabela grande a uma tabela pequena. Nesse caso, a varredura da tabela para produzir a estrutura de dados do filtro Bloom pode conseguir usar a consulta paralela.

mysql> explain select count(*) from orders join customer where o_custkey = c_custkey;
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
| id |...| table    | type  | possible_keys | key         |...| rows      | Extra                                                                                                           |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
|  1 |...| customer | index | PRIMARY       | c_nationkey |...|  15051972 | Using index                                                                                                     |
|  1 |...| orders   | ALL   | o_custkey     | NULL        |...| 154545408 | Using join buffer (Hash Join Outer table orders); Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+

Para uma consulta de junção que usa o mecanismo de loop aninhado, o bloco de loop aninhado mais externo poderá usar a consulta paralela. O uso da consulta paralela depende dos mesmos fatores de sempre, como a presença de condições de filtragem adicionais na cláusula WHERE.

mysql> -- Nested loop join with extra filter conditions can use parallel query.
mysql> explain select count(*) from part, partsupp where p_partkey != ps_partkey and p_name is not null and ps_availqty > 0;
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table    |...| rows     | Extra                                                                      |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part     |...| 20427936 | Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
|  1 | SIMPLE      | partsupp |...| 78164450 | Using where; Using join buffer (Block Nested Loop)                         |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+

Subconsultas

O bloco de consulta externa e o bloco de subconsulta interna podem usar a consulta paralela ou não. Isso é determinado com base nas características habituais da tabela, na cláusula WHERE e assim por diante, para cada bloco. Por exemplo, a consulta a seguir usa a consulta paralela para o bloco de subconsultas mas não para o bloco mais externo.

mysql> explain select count(*) from part where
   --> p_partkey < (select max(p_partkey) from part where p_name like '%vanilla%');
+----+-------------+...+----------+----------------------------------------------------------------------------+
| id | select_type |...| rows     | Extra                                                                      |
+----+-------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY     |...|     NULL | Impossible WHERE noticed after reading const tables                        |
|  2 | SUBQUERY    |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+-------------+...+----------+----------------------------------------------------------------------------+

No momento, as subconsultas correlacionadas não podem usar a otimização de consulta paralela.

UNION

Cada bloco de consultas em uma consulta UNION pode usar a consulta paralela ou não, dependendo das características da tabela, da cláusula WHERE etc., para cada parte da UNION.

mysql> explain select p_partkey from part where p_name like '%choco_ate%'
    -> union select p_partkey from part where p_name like '%vanil_a%';
+----+----------------+...+----------+----------------------------------------------------------------------------+
| id | select_type    |...| rows     | Extra                                                                      |
+----+----------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY        |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
|  2 | UNION          |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
| NULL | UNION RESULT | <union1,2> |...|     NULL | Using temporary                                           |
+----+--------------+...+----------+----------------------------------------------------------------------------+

Visões

O otimizador reescreve as consultas que usam visualizações como consultas mais longas que usam tabelas subjacentes. Assim, a consulta paralela funciona da mesma forma, sejam as referências de tabelas visualizações ou tabelas reais. Todas as mesmas considerações que se aplicam ao uso de consultas paralelas para uma consulta, e a quais partes são empurradas, são aplicáveis à consulta final que é reescrita.

Por exemplo, o plano de consulta a seguir mostra uma definição de visualização que normalmente não usa a consulta paralela. Quando a visualização é consultada com cláusulas WHERE adicionais, o Aurora MySQL usa a consulta paralela.

mysql> create view part_view as select * from part;
mysql> explain select count(*) from part_view where p_partkey is not null;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 0 filters, 0 exprs; 1 extra) |
+----+...+----------+----------------------------------------------------------------------------+

Instruções na linguagem de manipulação de dados (DML)

A instrução INSERT pode usar a consulta paralela na fase de processamento SELECT, caso a parte SELECT atenda às outras condições para usar a consulta paralela.

mysql> create table part_subset like part;
mysql> explain insert into part_subset select * from part where p_mfgr = 'Manufacturer#1';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

A instrução CREATE TABLE AS SELECT não usa a consulta paralela, mesmo que a porção SELECT da instrução seja qualificada para usar a consulta paralela. O aspecto de DDL dessa instrução torna ela incompatível com o processamento de consultas paralelas. Por outro lado, na instrução INSERT ... SELECT, a porção SELECT pode usar a consulta paralela.

A consulta paralela nunca é usada em instruções DELETE ou UPDATE, independentemente do tamanho da tabela e dos predicados na cláusula WHERE.

mysql> explain delete from part where p_name is not null;
+----+-------------+...+----------+-------------+
| id | select_type |...| rows     | Extra       |
+----+-------------+...+----------+-------------+
|  1 | SIMPLE      |...| 20427936 | Using where |
+----+-------------+...+----------+-------------+

Transações e bloqueio

É possível usar todos os níveis de isolamento na instância primária do Aurora.

Em instâncias de banco de dados do leitor do Aurora, a consulta paralela se aplica a instruções executadas sob o nível de isolamento REPEATABLE READ. O Aurora MySQL versão 2.09 e versões posteriores também podem usar o nível de isolamento READ COMMITTED em instâncias de banco de dados do leitor. O nível de isolamento padrão para instâncias de banco de dados do leitor do Aurora é REPEATABLE READ. Para usar o nível de isolamento READ COMMITTED em instâncias de banco de dados de leitor, é necessário definir a opção de configuração aurora_read_replica_read_committed no nível da sessão. O nível de isolamento READ COMMITTED para instâncias de leitor está em conformidade com o comportamento padrão SQL. No entanto, o isolamento é menos rigoroso em instâncias de leitor do que quando as consultas usam o nível de isolamento READ COMMITTED na instância de gravador.

Para saber mais sobre níveis de isolamento do Aurora, especialmente as diferenças em READ COMMITTED entre instâncias de gravador e de leitor, consulte Níveis de isolamento do Aurora MySQL.

Após a conclusão de uma grande transação, as estatísticas da tabela podem ficar obsoletas. Tais estatísticas obsoletas podem exigir o uso de uma instrução ANALYZE TABLE para que o Aurora possa estimar o número de linhas com precisão. Uma instrução em DML de grande escala também pode trazer uma porção substancial dos dados da tabela para o grupo de buffers. A presença desses dados no grupo de buffers pode levar à redução da frequência de uso da consulta paralela para essa tabela até que os dados sejam removidos do grupo.

Quando sua sessão está em uma transação demorada (por padrão, 10 minutos), as consultas adicionais dentro dessa sessão não usam a consulta paralela. Também é possível que o tempo limite seja esgotado durante uma consulta única de longa duração. Esse tipo de tempo limite esgotado pode ocorrer se a execução da consulta se estender por um tempo maior do que o intervalo máximo (atualmente, 10 minutos) antes de o processamento da consulta paralela começar.

Você pode reduzir a probabilidade de iniciar uma transação de longa duração acidentalmente definindo autocommit=1 nas sessões mysql, onde você executa as consultas ad hoc (de execução única). Até mesmo uma instrução SELECT em uma tabela inicia uma transação criando uma visualização de leitura. Uma visualização de leitura é um conjunto de dados consistente para as consultas posteriores que permanecem até que a transação seja confirmada. Esteja ciente dessa restrição também ao usar aplicativos JDBC ou ODBC com o Aurora, pois eles podem estar sendo executados com a configuração de autocommit desativada.

O exemplo a seguir mostra como a execução de uma consulta em uma tabela com a configuração de autocommit desativada cria uma visualização de leitura que inicia implicitamente uma transação. As consultas executadas logo em seguida ainda podem usar a consulta paralela. No entanto, após uma pausa de vários minutos, as consultas não se qualificam mais para a consulta paralela. O encerramento da transação com COMMIT ou ROLLBACK restaura a possibilidade de qualificação para a consulta paralela.

mysql> set autocommit=0;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

mysql> select sleep(720); explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+------------+
| sleep(720) |
+------------+
|          0 |
+------------+
1 row in set (12 min 0.00 sec)

+----+...+---------+-------------+
| id |...| rows    | Extra       |
+----+...+---------+-------------+
|  1 |...| 2976129 | Using where |
+----+...+---------+-------------+

mysql> commit;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

Para ver quantas vezes as consultas não foram qualificadas para a consulta paralela porque estavam dentro de transações de longa duração, verifique a variável de status Aurora_pq_request_not_chosen_long_trx.

mysql> show global status like '%pq%trx%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Aurora_pq_request_not_chosen_long_trx | 4     |
+-------------------------------+-------+

As instruções SELECT que obtêm bloqueios, como as sintaxes SELECT FOR UPDATE ou SELECT LOCK IN SHARE MODE, não podem usar a consulta paralela.

A consulta paralela pode funcionar para uma tabela que seja bloqueada por uma instrução LOCK TABLES.

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055';
+----+...+-----------+----------------------------------------------------------------------------+
| id |...| rows      | Extra                                                                      |
+----+...+-----------+----------------------------------------------------------------------------+
|  1 |...| 154545408 | Using where; Using parallel query (3 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+-----------+----------------------------------------------------------------------------+

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055' for update;
+----+...+-----------+-------------+
| id |...| rows      | Extra       |
+----+...+-----------+-------------+
|  1 |...| 154545408 | Using where |
+----+...+-----------+-------------+

Índices de árvore B

As estatísticas coletadas pela instrução ANALYZE TABLE ajudam o otimizador a decidir quando usar a consulta paralela ou as buscas com índice, com base nas características dos dados de cada coluna. Mantenha as estatísticas atualizadas com a execução de ANALYZE TABLE após as operações DML que fazem alterações substanciais nos dados dentro de uma tabela.

Se as buscas com índices podem executar uma consulta de maneira eficiente sem uma varredura com muitos dados, o Aurora pode usar buscas com índices. Isso evita a sobrecarga do processamento de consultas paralelas. Também existem limites de simultaneidade para o número de consultas paralelas que podem ser executadas simultaneamente em um cluster de bancos de dados Aurora. Certifique-se de usar as práticas recomendadas na indexação de suas tabelas, de modo que suas consultas mais frequentes e aquelas que são mais usadas de forma simultânea utilizem buscas com índices.

Índices de pesquisa de texto completo (FTS)

No momento, a consulta paralela não é usada em tabelas que contêm um índice de pesquisa de texto completo, independentemente de a consulta fazer referência ou não a colunas indexadas dessa forma ou usar o operador MATCH.

Virtual columns

No momento, a consulta paralela não é usada para tabelas que contêm uma coluna virtual, independentemente de a consulta se referir a qualquer coluna virtual.

Mecanismos de armazenamento em cache integrados

O Aurora inclui mecanismos de armazenamento em cache incorporados: o grupo de buffers e o cache de consultas. O otimizador do Aurora escolhe um entre esses mecanismos de armazenamento em cache e a consulta paralela de acordo com a eficiência oferecida por cada um deles para cada consulta específica.

Quando uma consulta paralela filtra as linhas, e transforma e extrai valores das colunas, os dados são transmitidos de volta para o nó de cabeçalho como tuplas em vez de páginas de dados. Portanto, a execução de uma consulta paralela não adiciona páginas ao grupo de buffers ou remove páginas que já estão no grupo de buffers.

O Aurora verifica o número de páginas de dados da tabela que estão presentes no grupo de buffers e a proporção dos dados da tabela que esse número representa. O Aurora usa essas informações para determinar se é mais eficiente usar a consulta paralela (e ignorar os dados presentes no grupo de buffers). Como alternativa, o Aurora pode usar o caminho de processamento de consulta não paralela, que usa os dados armazenados em cache no grupo de buffers. Quais páginas são armazenadas em cache e como as consultas com muitos dados afetam o armazenamento em cache e a remoção vai depender das configurações relacionadas ao grupo de buffers. Portanto, pode ser difícil prever se uma consulta específica vai usar a consulta paralela, pois a escolha depende de dados que estão em constante mudança dentro do grupo de buffers.

Além disso, o Aurora impõe limites de simultaneidade para as consultas paralelas. Como nem toda consulta usa a consulta paralela, em geral as tabelas que são acessadas por várias consultas simultaneamente têm uma porção substancial de seus dados no grupo de buffers. Portanto, o Aurora muitas vezes não escolhe essas tabelas para a consulta paralela.

Quando você executa uma sequência de consultas não paralelas na mesma tabela, a primeira consulta pode ficar lenta devido à ausência de dados do grupo de buffers. Em seguida, a segunda consulta, bem como as subsequentes, são muito mais rápidas porque o grupo de buffers agora está "aquecido". As consultas paralelas normalmente apresentam uma performance consistente desde a primeira consulta em uma tabela. Ao conduzir testes de performance, compare as consultas não paralelas tanto com um grupo de buffers ocioso quanto com um aquecido. Em alguns casos, os resultados com um grupo de buffers aquecido pode ser comparável aos tempos obtidos com a consulta paralela. Nesses casos, considere fatores como a frequência de consultas em relação a essa tabela. Considere também se vale a pena manter os dados dessa tabela no grupo de buffer.

O cache de consultas evita a que a consulta seja executada novamente quando uma consulta idêntica é enviada e a tabela subjacente não foi alterada. As consultas otimizadas pelo recurso de consulta paralela podem acessar o cache de consulta, tornando-as efetivamente instantâneas quando executadas novamente.

Dicas do Optimizer

Outra forma de controlar o Optimizer é usar dicas do Optimizer, que podem ser especificadas em instruções individuais. Por exemplo, é possível ativar a otimização para uma tabela em uma instrução e, depois, desativá-la para outra tabela. Para ter mais informações sobre essas dicas, consulte Dicas do Optimizer no Guia de referência do MySQL.

É possível usar dicas SQL com consultas do Aurora MySQL para ajustar a performance. Também é possível usar dicas para impedir que planos de execução para consultas importantes sejam alterados devido a condições imprevisíveis.

Estendemos o recurso de dicas de SQL para ajudar você a controlar as opções do Optimizer para seus planos de consulta. Essas dicas se aplicam a consultas que usam a otimização de consultas paralelas. Para ter mais informações, consulte Dicas do Aurora MySQL.

Tabelas temporárias MyISAM

A otimização de consulta paralela se aplica somente a tabelas InnoDB. Como o Aurora MySQL usa o MyISAM nos bastidores para tabelas temporárias, as fases de consulta internas que envolvem tabelas temporárias nunca usam a consulta paralela. Essas fases da consulta são indicadas por Using temporary na saída de EXPLAIN.