Receber insights de desempenho da consulta

Neste documento, descrevemos como usar o gráfico de execução de consultas para diagnosticar problemas de desempenho e ver insights de desempenho da consulta.

O BigQuery oferece um bom desempenho de consulta, mas também é um sistema distribuído complexo com muitos fatores internos e externos que podem afetar a velocidade da consulta. A natureza declarativa da linguagem SQL também pode ocultar a complexidade da execução da consulta. Isso significa que, quando suas consultas estão mais lentas do que o esperado ou mais lentas do que as anteriores, entender o que aconteceu pode ser um desafio.

O gráfico de execução de consultas oferece uma interface intuitiva para inspecionar detalhes de desempenho das consultas. Ao usá-lo, é possível analisar as informações do plano de consulta no formato gráfico para qualquer consulta, esteja ela em execução ou concluída.

Também é possível usar o gráfico de execução de consultas para ver insights de desempenho das consultas. Os insights de desempenho oferecem sugestões de melhor esforço para ajudar a melhorar o desempenho das consultas. Como o desempenho da consulta é multifacetado, os insights de desempenho talvez só forneçam uma visão parcial do desempenho geral da consulta.

Observação: os insights de desempenho podem não ser gerados para jobs de consulta executados antes de 1o de outubro de 2022.

Permissões necessárias

Para usar o gráfico de execução de consulta, você precisa ter as seguintes permissões:

• bigquery.jobs.get
• bigquery.jobs.listAll

Essas permissões estão disponíveis por meio dos seguintes papéis predefinidos do Identity and Access Management (IAM) do BigQuery:

• roles/bigquery.admin
• roles/bigquery.resourceAdmin
• roles/bigquery.resourceEditor
• roles/bigquery.resourceViewer

Conferir insights de desempenho da consulta

Console

Siga estas etapas para conferir os insights de desempenho da consulta:

1. Abra a página do BigQuery no console do Google Cloud.

   Acesse a página do BigQuery

2. No Editor, clique em Histórico pessoal ou Histórico do projeto.

3. Na lista de jobs, identifique o job de consulta que interessa a você. Clique em Ações e escolha Abrir consulta no editor.

4. Selecione a guia Gráfico de execução para ver uma representação gráfica de cada estágio da consulta:

   

   Para determinar se um estágio de consulta tem insights de desempenho, veja o ícone exibido. Os estágios que têm um ícone de informação têm insights de desempenho. Os estágios que têm um ícone de verificação não têm.

5. Clique em um cenário para abrir o painel de detalhes dele e ver as seguintes informações:

   • Informações do plano de consulta para o cenário.
   • As etapas executadas no cenário.
   • Todos os insights de desempenho aplicáveis.

   

6. Opcional: se você estiver inspecionando uma consulta em execução, clique em Sincronizar para atualizar o gráfico de execução para que ele reflita o status atual da consulta.

   

7. Opcional: para destacar os principais estágios por duração no gráfico, clique em Destacar os principais estágios por duração.

   

8. Opcional: para destacar os principais estágios por tempo de slot no gráfico, clique em Destacar os principais estágios por processamento.

   

9. Opcional: para incluir estágios de redistribuição embaralhada no gráfico, clique em Mostrar estágios de redistribuição aleatória.

   

   Use essa opção para mostrar os estágios de repartição e união que estão ocultos no gráfico de execução padrão.

   Os estágios de repartição e união são introduzidos durante a execução da consulta e usados para melhorar a distribuição de dados entre os workers que processam a consulta. Como esses estágios não estão relacionados ao texto da consulta, eles ficam ocultos para simplificar o plano de consulta exibido.

Para qualquer consulta que tenha problemas de regressão de desempenho, os insights de desempenho também são exibidos na guia Informações do job:

SQL

1. No Console do Google Cloud, acesse a página BigQuery.

   Ir para o BigQuery

2. No editor de consultas, digite a seguinte instrução:

   
   SELECT
     `bigquery-public-data`.persistent_udfs.job_url(
       project_id || ':us.' || job_id) AS job_url,
     query_info.performance_insights
   FROM
     `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
     DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
     AND job_type = 'QUERY'
     AND state = 'DONE'
     AND error_result IS NULL
     AND statement_type != 'SCRIPT'
     AND EXISTS ( -- Only include queries which had performance insights
       SELECT 1
       FROM UNNEST(
         query_info.performance_insights.stage_performance_standalone_insights
       )
       WHERE slot_contention OR insufficient_shuffle_quota
       UNION ALL
       SELECT 1
       FROM UNNEST(
         query_info.performance_insights.stage_performance_change_insights
       )
       WHERE input_data_change.records_read_diff_percentage IS NOT NULL
     );
   

3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

API

É possível conseguir insights de desempenho da consulta em um formato não gráfico chamando ojobs.list método da API e inspecionar a JobStatistics2 informações retornadas.

Interpretar insights de desempenho da consulta

Use esta seção para saber mais sobre o que os insights de desempenho significam e como lidar com eles.

Os insights de desempenho são destinados a dois públicos-alvo:

• Analistas: você executa consultas em um projeto. Você quer saber por que uma consulta executada anteriormente está inesperadamente mais lenta e receber dicas sobre como melhorar o desempenho de uma consulta. Você tem as permissões descritas em Permissões necessárias.

• Administradores de data lake ou data warehouse: você gerencia os recursos e as reservas do BigQuery da sua organização. Você tem as permissões associadas ao papel de administrador do BigQuery.

Cada uma das seções a seguir fornece orientações sobre o que você pode fazer para resolver um insight de desempenho recebido com base em um desses papéis ocupados.

Contenção de slot

Quando você executa uma consulta, o BigQuery tenta dividir o trabalho necessário em tarefas. Uma tarefa é uma fração única de dados que são entradas em ou retiradas de um estágio. Um único slot seleciona uma tarefa e executa aquela parte de dados do estágio. O ideal é que os slots do BigQuery executem essas tarefas em paralelo para alcançar um alto desempenho. A contenção de slots ocorre quando sua consulta tem muitas tarefas prontas para começar a ser executada, mas o BigQuery não consegue slots suficientes disponíveis para executá-las.

O que fazer se você for analista

Reduza os dados processados na consulta seguindo as orientações em Reduzir os dados processados em consultas.

O que fazer se você for administrador

Aumente a disponibilidade ou diminua o uso de slots realizando as seguintes ações:

• Se você usar o preço sob demanda do BigQuery, suas consultas usarão um pool compartilhado de slots. Em vez disso, considere mudar para os preços de análise com base em capacidade comprando reservas. As reservas permitem reservar slots dedicados para as consultas da sua organização.

• Se você estiver usando reservas do BigQuery, verifique se há slots suficientes na reserva atribuída ao projeto que estava executando a consulta. A reserva pode não ter slots suficientes nestes cenários:

  • Há outros jobs que consomem slots de reserva. Use Gráficos de recursos do administrador para ver como sua organização está usando a reserva.
  • A reserva não tem slots atribuídos suficientes para executar consultas com rapidez suficiente. Use o Estimador de slot para ter uma estimativa do tamanho das reservas para processar as tarefas das consultas com eficiência.

  Para resolver isso, você pode tentar:

  • Adicionar mais slots a esta reserva.
  • Criar outra reserva e atribuí-la ao projeto que executa a consulta.
  • Distribuir consultas que usam muitos recursos ao longo do tempo em uma reserva ou em reservas diferentes.

• Verifique se as tabelas que você está consultando são em cluster. O clustering garante que o BigQuery possa ler rapidamente as colunas com dados correlacionados.

• Verifique se as tabelas que você está consultando estão particionadas. No caso de tabelas não particionadas, o BigQuery lê toda a tabela. O particionamento de tabelas ajuda a garantir que você consulte apenas o subconjunto de tabelas em que tem interesse.

Cota de embaralhamento insuficiente

Antes de executar sua consulta, o BigQuery divide a lógica da consulta em estágios. Os slots do BigQuery executam as tarefas para cada estágio. Quando um slot conclui a execução de tarefas de um estágio, ele armazena os resultados intermediários em shuffle. Os estágios seguintes na sua consulta leem dados do embaralhamento para continuar a execução da consulta. A cota de embaralhamento insuficiente ocorre quando você tem mais dados que precisam ser gravados para serem embaralhados do que você tem de capacidade de shuffle.

O que fazer se você for analista

Da mesma forma que a contenção de slots, reduzir a quantidade de dados que a consulta processa pode reduzir o uso do embaralhamento. Para fazer isso, siga as orientações em Reduzir dados processados em consultas.

Certas operações no SQL tendem a fazer uso mais abrangente do embaralhamento, principalmente operações JOIN e cláusulas GROUP BY. Quando possível, a redução da quantidade de dados nessas operações pode reduzir o uso de embaralhamento.

O que fazer se você for administrador

Reduza a contenção de cota de embaralhamento realizando as seguintes ações:

• Assim como a contenção de slots, se você usar o preço sob demanda do BigQuery, suas consultas usarão um pool compartilhado de slots. Em vez disso, considere mudar para os preços de análise com base em capacidade comprando reservas. As reservas oferecem slots dedicados e capacidade de embaralhamento para as consultas dos seus projetos.

• Se você estiver usando reservas do BigQuery, os slots serão oferecidos com capacidade dedicada de embaralhamento. Se a reserva estiver executando algumas consultas que fazem uso extensivo do embaralhamento, isso pode fazer com que outras consultas em paralelo não tenham capacidade suficiente de embaralhamento. É possível identificar quais jobs fazem grande uso da capacidade de embaralhamento consultando a coluna period_shuffle_ram_usage_ratio na visualização INFORMATION_SCHEMA.JOBS_TIMELINE.

  Para resolver isso, tente uma ou mais das seguintes soluções:

  • Adicionar mais slots a esta reserva.
  • Criar outra reserva e atribuí-la ao projeto que executa a consulta.
  • Distribuir consultas com uso intensivo de embaralhamento ao longo do tempo em uma reserva ou em reservas diferentes.

Mudança da escala de entrada de dados

Esse insight de desempenho indica que sua consulta está lendo pelo menos 50% mais dados de uma determinada tabela de entrada do que a última vez que você a executou. Use o histórico de alterações da tabela para ver se o tamanho de qualquer uma das tabelas usadas na consulta aumentou recentemente.

O que fazer se você for analista

Reduza os dados processados na consulta seguindo as orientações em Reduzir os dados processados em consultas.

Agrupamento de alta cardinalidade

Quando uma consulta contém uma mesclagem com chaves não exclusivas em ambos os lados da mesclagem, o tamanho da tabela de saída pode ser consideravelmente maior do que o de qualquer uma das tabelas de entrada. Esse insight indica que a proporção de linhas de saída para linhas de entrada é alta e oferece informações sobre essas contagens de linhas.

O que fazer se você for analista

Verifique as condições de mesclagem para confirmar se o aumento no tamanho da tabela de saída é esperado. Evite usar correlações. Se você precisar usar uma correlação, tente usar uma cláusula GROUP BY para pré-agregar os resultados ou use uma função de janela. Para mais informações, consulte Reduzir dados antes de usar um JOIN.

Desvio da partição

Visualização

Esse recurso está sujeito aos "Termos de ofertas pré-GA" na seção "Termos de Serviço gerais" dos Termos específicos de serviço. Os recursos pré-GA estão disponíveis "no estado em que se encontram" e podem ter suporte limitado. Para mais informações, consulte as descrições da fase de lançamento.

Para enviar feedback ou pedir suporte para esse recurso, envie um e-mail para [e-mail].

Isso pode fazer com que as consultas sejam executadas lentamente. Quando uma consulta é executada, o BigQuery divide os dados em pequenas partições. Não é possível compartilhar partições entre slots. Portanto, se os dados forem distribuídos de maneira desigual, algumas partições ficarão muito grandes, causando falha no slot que processa a partição superdimensionada.

O desvio ocorre em fases JOIN. Quando você executa uma operação JOIN, o BigQuery divide os dados dos lados direito e esquerdo da operação JOIN em partições. Se uma partição for muito grande, os dados serão reequilibrados por estágios de repartição. Se o desvio for muito ruim e o BigQuery não puder se reequilibrar ainda mais, um insight de distorção de partição será adicionado ao estágio "JOIN". Esse processo é conhecido como estágios de repartição. Se o BigQuery detectar partições grandes que não podem ser divididas, um insight de distorção de partição será adicionado ao cenário JOIN.

O que fazer se você for analista

Para evitar o desvio de partição, filtre os dados o quanto antes. Para mais informações sobre como evitar desvio de partição, consulte Filtrar dados para dados distorcidos.

Interpretar as informações do estágio de consulta

Além de usar insights de desempenho da consulta, também é possível usar as diretrizes a seguir ao analisar os detalhes do estágio de consulta para ajudar a determinar se há um problema com uma consulta:

• Se o valor de Wait ms para um ou mais estágios for alto em comparação com as execuções anteriores da consulta:
  • Veja se você tem slots suficientes disponíveis para acomodar sua carga de trabalho. Caso contrário, faça o balanceamento de carga ao executar consultas com uso intensivo de recursos para que eles não concorram entre si.
  • Se o valor Wait ms estiver maior do que em um único estágio, procure o estágio antes dele para ver se um gargalo foi introduzido. Coisas como mudanças substanciais nos dados ou no esquema das tabelas envolvidas na consulta podem afetar o desempenho da consulta.
• Se o valor de Bytes de saída do embaralhamento de um estágio for alto em comparação com execuções anteriores da consulta ou com um estágio anterior, avalie as etapas processadas nesse estágio para ver se alguma criou volumes de dados inesperados. Uma causa comum para isso é quando uma etapa processa uma INNER JOIN em que há chaves duplicadas nos dois lados da mesclagem. Isso pode retornar uma grande quantidade inesperada de dados.
• Use o gráfico de execução para analisar os principais estágios por duração e processamento. Considere a quantidade de dados que eles produzem e se está em conformidade com o tamanho das tabelas referenciadas na consulta. Se não estiver, veja as etapas desses estágios para ver se alguma deles pode produzir uma quantidade inesperada de dados temporários.

A seguir

• Consulte as diretrizes de otimização de consulta para ver dicas sobre como melhorar o desempenho da consulta.