Noções básicas do tempo gasto com consultas Revisar as configurações do otimizador de consultas

Solucionar problemas de performance em bancos de dados do Aurora MySQL

O MySQL fornece controle do otimizador de consultas por meio de variáveis do sistema que afetam a forma como os planos de consulta são avaliados, otimizações selecionáveis, dicas de otimizador e índice e o modelo de custo do otimizador. Esses pontos de dados podem ser úteis não apenas para comparar diferentes ambientes do MySQL, mas também para comparar planos de execução de consultas anteriores com planos de execução atuais e para entender a execução geral de uma consulta do MySQL a qualquer momento.

O desempenho da consulta depende de muitos fatores, incluindo o plano de execução, o esquema e o tamanho da tabela, estatísticas, recursos, índices e configuração de parâmetros. O ajuste de consultas requer a identificação de gargalos e a otimização do caminho de execução.

Encontre o plano de execução da consulta e verifique se ela está usando índices apropriados. É possível otimizar a consulta usando EXPLAIN e revisando os detalhes de cada plano.
O Aurora MySQL versão 3 (compatível com o MySQL 8.0 Community Edition) usa uma instrução EXPLAIN ANALYZE. A instrução EXPLAIN ANALYZE é uma ferramenta de criação de perfil que mostra onde o MySQL gasta tempo na consulta e por quê. Com EXPLAIN ANALYZE, o Aurora MySQL planeja, prepara e executa a consulta enquanto conta as linhas e mede o tempo gasto em vários pontos do plano de execução. Quando a consulta é concluída, EXPLAIN ANALYZE imprime o plano e suas medições em vez do resultado da consulta.
Mantenha as estatísticas do esquema atualizadas usando a instrução ANALYZE. Às vezes, o otimizador de consultas pode escolher planos de execução inadequados devido a estatísticas desatualizadas. Isso pode levar a um desempenho insatisfatório de uma consulta devido às estimativas de cardinalidade imprecisas das tabelas e dos índices. A coluna last_update da tabela innodb_table_stats mostra a última vez que as estatísticas do esquema foram atualizadas, o que é um bom indicador de “desatualização”.
Outros problemas podem ocorrer, como distorção da distribuição de dados, que não são levados em consideração para a cardinalidade da tabela. Consulte mais informações em Estimating ANALYZE TABLE complexity for InnoDB tables e Histogram statistics in MySQL na documentação do MySQL.

Noções básicas do tempo gasto com consultas

Veja a seguir formas de determinar o tempo gasto pelas consultas:

Criação de perfil

Por padrão, a criação de perfil está desabilitada. Habilite a criação de perfil, depois execute a consulta lenta e revise seu perfil.


SET profiling = 1;
Run your query.
SHOW PROFILE;

Identifique o estágio em que a maior parte do tempo é gasto. De acordo com General thread states na documentação do MySQL, ler e processar linhas de uma instrução SELECT geralmente é o estado de execução mais longa durante a vida útil de uma determinada consulta. É possível usar a instrução EXPLAIN para entender como o MySQL executa essa consulta.
Analise o log de consultas lentas para avaliar rows_examined e rows_sent a fim de garantir que a workload seja semelhante em cada ambiente. Para ter mais informações, consulte Registro em log de bancos de dados do Aurora MySQL.
Execute o comando a seguir para tabelas que fazem parte da consulta identificada:
```
SHOW TABLE STATUS\G;
```
Capture as seguintes saídas antes e depois de executar a consulta em cada ambiente:
```
SHOW GLOBAL STATUS;
```
Execute os comandos a seguir em cada ambiente para ver se há alguma outra consulta/sessão influenciando o desempenho dessa consulta de amostra.
```
SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;
```
Às vezes, quando os recursos no servidor estão ocupados, isso afeta todas as outras operações no servidor, incluindo consultas. Também é possível capturar informações periodicamente quando as consultas são executadas ou configurar um trabalho cron para capturar informações em intervalos úteis.

Esquema de desempenho

O Performance Schema fornece informações úteis sobre o desempenho do tempo de execução do servidor, embora tenha um impacto mínimo sobre esse desempenho. Isso é diferente de information_schema, que fornece informações de esquema sobre a instância de banco de dados. Para ter mais informações, consulte Visão geral do Performance Schema para o Insights de Performance no Aurora MySQL.

Rastreamento do otimizador de consulta

Para entender por que um plano de consulta específico foi escolhido para execução, é possível configurar optimizer_trace para acessar o otimizador de consultas do MySQL.

Execute um rastreamento do otimizador para mostrar informações abrangentes sobre todos os caminhos disponíveis para o otimizador e sua escolha.


SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

Revisar as configurações do otimizador de consultas

O Aurora MySQL versão 3 (compatível com MySQL 8.0 Community Edition) tem muitas alterações relacionadas ao otimizador em comparação com o Aurora MySQL versão 2 (compatível com o MySQL 5.7 Community Edition). Se você tiver alguns valores personalizados para optimizer_switch, recomendamos que analise as diferenças nos padrões e defina os valores de optimizer_switch que funcionem melhor para sua workload. Também recomendamos que você teste as opções disponíveis para o Aurora MySQL versão 3 para examinar o desempenho das consultas.

nota

O Aurora MySQL versão 3 usa o valor padrão da comunidade de 20 para o parâmetro innodb_stats_persistent_sample_pages.

É possível usar o seguinte comando para mostrar os valores de optimizer_switch:


SELECT @@optimizer_switch\G;

A tabela a seguir mostra os valores padrão de optimizer_switch do Aurora MySQL versões 2 e 3.

Configuração	Aurora MySQL versão 2	Aurora MySQL versão 3
`batched_key_access`	off	off
`block_nested_loop`	ativado	ativado
`condition_fanout_filter`	ativado	ativado
`derived_condition_pushdown`	–	ativado
`derived_merge`	ativado	ativado
`duplicateweedout`	ativado	ativado
`engine_condition_pushdown`	ativado	ativado
`firstmatch`	ativado	ativado
`hash_join`	off	ativado
`hash_join_cost_based`	ativado	–
`hypergraph_optimizer`	–	off
`index_condition_pushdown`	ativado	ativado
`index_merge`	ativado	ativado
`index_merge_intersection`	ativado	ativado
`index_merge_sort_union`	ativado	ativado
`index_merge_union`	ativado	ativado
`loosescan`	ativado	ativado
`materialization`	ativado	ativado
`mrr`	ativado	ativado
`mrr_cost_based`	ativado	ativado
`prefer_ordering_index`	ativado	ativado
`semijoin`	ativado	ativado
`skip_scan`	–	ativado
`subquery_materialization_cost_based`	ativado	ativado
`subquery_to_derived`	–	off
`use_index_extensions`	ativado	ativado
`use_invisible_indexes`	–	off

Consulte mais informações em Switchable optimizations (MySQL 5.7) e Switchable optimizations (MySQL 8.0) na documentação do MySQL.

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Registro em log do Aurora MySQL

Referência do Aurora MySQL