Em um trecho editado de uma série sobre otimização de custos e desempenho no BigQuery, mostramos como identificar as queries que mais geram custos no seu ambiente.
Um trecho editado de uma série sobre otimização de custos e desempenho no BigQuery
A primeira parte desta série mostrou o que você precisa preparar para executar queries no BigQuery, e a segunda parte falou sobre os erros mais comuns que aparecem quando se otimiza o uso do BigQuery. Neste artigo, vamos ver como identificar as queries mais caras do seu ambiente. Todo o código usado na série está disponível neste repositório do GitHub.
As queries mais caras no geral
A primeira query que você precisa olhar está no arquivo top_costly_queries.sql. Provavelmente a mais importante de todas em termos de custo, ela traz o custo total que uma query gera no intervalo, somando todas as suas execuções. O objetivo é duplo: descobrir qual é a query mais cara do seu ambiente no geral e identificar se uma query cara está sendo executada mais vezes do que o necessário.
As queries individuais mais caras
A segunda query desta seção está no arquivo top_billed_queries.sql. Ela lista as queries mais caras do seu ambiente, em ordem decrescente de custo. Diferente da primeira, esta não leva em conta quantas vezes a query foi executada — mostra apenas o custo por execução.
Ao rodar essa query, as mais caras vão direto para o topo dos resultados. Pegue a query do topo e veja primeiro quanto de dado ela processou (as colunas total*Billed). Depois, com esse número em mãos, analise a query em si para entender o que está fazendo o custo subir tanto.
Observação sobre queries duplicadas
Se os resultados estiverem retornando a mesma query várias vezes, distorcendo os dados ou dificultando a leitura, use o arquivo top_billed_queries_deduplicated.sql. É a mesma query, mas ela elimina duplicatas exatas, deixando só um resultado por query. Demora mais para rodar e escaneia mais dados, mas devolve o resultado já deduplicado.
Hoje o BigQuery não tem um agregador nativo de "similaridade de strings", como distância de Hamming ou de Levenshtein, então filtrar queries parecidas que mudam só em pequenos detalhes (uma data, por exemplo) é relativamente difícil. Se precisar, dá para encontrar algumas UDFs que implementam alguns desses algoritmos de similaridade no BigQuery, mas o ideal é resolver isso fora do BigQuery, porque implementações em UDF de algoritmos pesados como esses tendem a ser bem lentas.
Os usuários mais caros
Uma terceira query útil para analisar custos é o arquivo top_cost_users.sql. Ela lista as queries mais caras do seu ambiente — só que ordenadas por usuário e, dentro disso, pela query mais cara.
O objetivo aqui é mostrar quais usuários ou contas de serviço estão gastando mais e em quais queries. Essa lista costuma incluir processos rodando queries ineficientes que talvez você nem saiba que existem. Em alguns casos extremos, esses processos estão em outros projetos ou até em outras nuvens. Adicionar filtros para descartá-los também pode ajudar a deixar o resultado mais limpo.
Outras queries no repositório
O repositório do GitHub também traz queries adicionais para usos mais específicos, como encontrar queries originadas no Looker, contar quantas vezes uma query foi executada, calcular o custo de queries com determinados rótulos, e por aí vai. São bem específicas, mas são as que nós da DoiT usamos no dia a dia, e por isso decidimos compartilhar com a comunidade.
Encontrando queries com problemas de desempenho
O próximo grande tema é encontrar queries que consomem mais recursos do que deveriam e não entregam o desempenho esperado. Muitas vezes essas também são as queries mais caras, então pode haver alguma sobreposição.
Nesta seção, vamos definir "complexidade" como o número de slots que uma query usa durante sua execução. Esse valor é calculado dividindo o tempo total de slots gasto em trabalho pelo tempo total de execução da query. Veja um exemplo aplicado à view JOBS_BY_PROJECT:
SELECTSAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCountFROM`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECTTrata-se de uma aproximação porque, infelizmente, o BigQuery não devolve os valores exatos — e esse mesmo cálculo é o que aparece na interface do BigQuery.
Complexidade e contagem de slots
Em qualquer banco de dados, quanto maior a complexidade lógica de uma query, maior costuma ser a complexidade de executá-la internamente. O BigQuery não foge à regra. E, em geral, há uma correlação direta entre essa complexidade e o custo da query. Ou seja: reduzir a complexidade quase sempre reduz custos.
Dito isso, a query principal do repositório no GitHub para medir complexidade é a top_complex_queries.sql. Ela retorna as queries que mais consomem slots ao longo do tempo de execução, então pegar as do topo ajuda a identificar candidatas com possíveis problemas de desempenho. Não é uma ciência exata, porque queries complexas muitas vezes precisam continuar complexas para funcionar como deveriam, mas é uma boa forma de encontrar suspeitas.
Queries de longa duração
Outra forma de identificar problemas de desempenho é olhar para as queries que mais demoram. Mas atenção: muitas vezes uma query demora simplesmente porque precisa demorar.
A query em longest_running_queries.sql retorna todas as queries do período, da mais longa para a mais curta. Isso joga as mais demoradas para o topo, prontas para análise. A coluna runtimeToBytesBilledRatio dá uma boa noção de quantos bytes a query processou por milissegundo. Em geral, se esse número for maior que 1, vale investigar para ver se há algo a otimizar e reduzir o volume de dados processado.
Uma observação sobre problemas de desempenho
Em uma próxima parte desta série, vou tratar de tuning de desempenho com mais profundidade, mostrando algumas das armadilhas mais comuns (e pouco conhecidas) do BigQuery e como contorná-las.
Queries de uso geral do repositório
O último tema desta parte da série é um conjunto de queries no repositório do GitHub que trazem informações ou metadados mais genéricos do que os vistos até aqui.
Queries por tipo de job
De longe, os tipos mais comuns dentre os vários tipos de jobs (queries, loads, extracts e cópias de tabela) são queries e loads. Por isso, os arquivos load_job_information.sql e query_job_information.sql no repositório retornam esses tipos de jobs e metadados úteis sobre eles. Há também uma versão mais genérica, a general_job_information.sql, que cobre todos os tipos de jobs e traz metadados gerais.
Queries simultâneas
Em dezembro de 2022, o Google mudou o comportamento do BigQuery em relação a queries simultâneas. Antecipando mudanças futuras e a chegada das filas de queries, o limite rígido de 100 queries simultâneas por projeto passou a escalar conforme o número de slots disponíveis. Quando este texto foi escrito, isso ainda não estava totalmente documentado, mas reunimos essas informações junto ao suporte e à documentação existente.
Por padrão, esse valor fica em zero, ou seja, o BigQuery determina dinamicamente a concorrência com base nos recursos disponíveis para o projeto. No modelo de preços flat-rate, há uma configuração na reserva chamada maximum concurrency, que permite solicitar esse número como teto de concorrência. O Google não garante que vai chegar a esse valor, mas se esforça para atendê-lo. A documentação oficial está na página de filas de queries aqui. Feito o disclaimer, vamos aos problemas com queries simultâneas.
Se você roda muitas queries em paralelo, vai bater na temida mensagem 503 "Service Unavailable" ou na versão mais recente, "Query was not admitted as the maximum concurrency has been reached". Isso significa que o limite de concorrência foi estourado e as queries não serão agendadas. É ruim por motivos óbvios e precisa ser evitado.
Quando as mudanças do Google estiverem totalmente esmiuçadas e compreendidas, vamos apresentar estratégias para mitigar o problema ou desenhar o ambiente de forma que ele nem aconteça. Por enquanto, use as queries concurrent_queries_by_minute.sql e concurrent_queries_by_seconds.sql para acompanhar suas médias de concorrência por minuto e por segundo. Elas retornam a quantidade média de queries simultâneas em execução em cada minuto (ou segundo) do intervalo definido.
Contagem de queries
Queries costumam ser executadas várias vezes, seja por um usuário, seja por algum processo automatizado. A query query_counts.sql mostra quantas vezes uma query rodou no intervalo de tempo. É bem útil para descobrir se alguma query específica está rodando mais do que deveria — e gastando dinheiro à toa. Ela também traz o custo total da query no intervalo escolhido.
O que fazer a seguir
Este post é uma versão condensada da minha série de artigos sobre otimização das suas queries no BigQuery, que será ampliada conforme novas mudanças forem chegando ao BigQuery. Se você já é cliente DoiT, dá para fazer todos esses passos pelo recurso BQ Lens do DoiT Cloud Intelligence™. E, se quiser, fale com a gente da DoiT para aproveitar nossa expertise ampla e profunda em BigQuery, machine learning e business intelligence.