Otimizações do BigQuery (Parte 3)

Parte 1

Parte 2

As duas seções anteriores abordaram diversos tópicos sobre pré-requisitos, conhecimentos gerais que vale a pena ter e como montar uma infraestrutura para analisar os dados.

Todo o código usado nesta série está no seguinte repositório do GitHub: https://github.com/doitintl/bigquery-optimization-queries

Como os dois métodos de monitoramento geram schemas bem diferentes, separei os arquivos SQL em duas pastas (audit_log e information_schema) no repositório.

Cada diretório tem um conjunto de arquivos SQL para cada finalidade, e há um README.md em cada pasta explicando o propósito de cada script.

No diretório raiz há um script Python chamado generate_sql_files.py, que gera uma cópia dos arquivos SQL com as informações do seu projeto e dataset.

Como esse repositório do GitHub é uma criação dinâmica, escrevi um artigo listando cada query nele junto com as descrições aqui.

Esse artigo será atualizado conforme novas queries forem adicionadas.

A partir da próxima seção e ao longo do restante desta série, vou referenciar muitas queries e analisar muitos dados. A interface do BigQuery dá conta razoavelmente bem de muitas queries e análises, mas recomendo FORTEMENTE exportar esses dados para uma planilha.

O Google Sheets é a opção ideal porque o BigQuery consegue exportar resultados direto para ele (com algumas limitações de tamanho). Além disso, exportar os dados uma única vez para uma planilha sai mais barato do que rodar a mesma query várias vezes para fazer a análise.

Você também precisa ficar de olho nos custos. Algumas dessas queries podem ser bem caras, então sempre recomendo verificar o custo da query antes de executá-la. Coloquei em cada query uma variável chamada interval_in_days, que controla até quanto tempo no passado a query vai olhar, e que pode ser ajustada para reduzir a quantidade de dados escaneados. Alguns dos nossos clientes simplesmente foram lá e modificaram as cláusulas WHERE para definir intervalos próprios, então o céu é o limite quando se trata dessas queries.

Todos os passos desta parte podem ser executados dentro do BQ Lens no CMP. O painel Explorer permite fazer essas etapas de forma visual; aliás, se você estiver usando as queries do sink de audit log, muitas delas são quase idênticas às que o CMP roda para exibir os dados.

O primeiro passo da otimização de custos no BigQuery é encontrar as queries mais caras do seu ambiente. Muitas vezes, nem se sabe que elas existem, então, nas palavras famosas que o GI Joe nos ensinou nos desenhos de sábado de manhã: "Saber é metade da batalha".

Depois que as queries forem identificadas, é hora de otimizá-las, descobrir quais processos ou usuários problemáticos estão executando-as e então corrigir tudo isso.

Vou referenciar arquivos SQL no repositório do GitHub mencionado acima (link). Vale lembrar que você pode usar tanto os arquivos do audit log quanto os do information_schema, já que chegam ao mesmo resultado.

A primeira query que você vai usar na nossa análise está no arquivo top_costly_queries.sql e é, sem dúvida, a mais importante de todas em relação a custo. Ela considera o custo total de uma query ao longo do intervalo, somando todas as suas execuções. Ou seja, vai te mostrar quais são, de fato, as queries mais caras — aquelas que rodam várias vezes ao longo do intervalo e custam mais do que aparentam à primeira vista.

O propósito dessa query é duplo: identificar a query mais cara do seu ambiente como um todo e descobrir se uma query cara está rodando mais vezes do que precisa. Frequentemente, processos como cron jobs, tarefas dentro de uma DAG em uma instância do Airflow, Cloud Functions etc. rodam várias vezes — muitas vezes mais do que precisariam — disparando uma query. E mais comum ainda é o criador, o mantenedor ou a pessoa que paga a conta da nuvem não fazer ideia de quanto essas queries custam por execução e/ou por mês. É aí que essa query entra em cena!

A segunda query que você vai usar nesta seção está no arquivo top_billed_queries.sql. Ela lista as queries mais caras do seu ambiente em ordem decrescente de custo. Diferentemente da primeira, essa não leva em conta a quantidade de execuções: mostra apenas o custo por execução da query.

Ao rodar essa query, as mais caras já sobem direto para o topo do conjunto de resultados. Uma boa prática aqui é pegar a query do topo e primeiro ver quantos dados ela processou (as colunas total*Billed). Depois, com esse número em mãos, dê uma olhada na própria query para entender o que está acontecendo para ela custar tanto.

Muitas vezes, os culpados são os problemas que listei acima, mas pode haver outras coisas em jogo. Então recomendo analisar as queries para ver se há algo evidente acontecendo nelas.

Se os resultados estiverem trazendo a mesma query várias vezes, distorcendo a análise ou dificultando a visualização, dê uma olhada no arquivo top_billed_queries_deduplicated.sql. É exatamente a mesma query, mas ela deduplica a query exata sendo executada, fazendo com que apareça apenas um resultado. Vai demorar mais para rodar e escanear mais dados, mas retorna resultados sem duplicatas.

Vale notar que, no momento em que escrevo este artigo, o BigQuery não oferece uma agregação nativa de "similaridade entre strings", como distância de Hamming ou Levenshtein, então filtrar queries parecidas com pequenas diferenças (como uma data) é relativamente difícil. Se for necessário, há algumas soluções com UDFs que implementam alguns desses algoritmos de "similaridade entre strings" para o BigQuery, mas eu sugiro criar uma solução fora do BigQuery, já que implementações em UDF de algoritmos computacionalmente caros como esses são bem lentas.

A terceira e última query que você vai usar para analisar custos está no arquivo top_cost_users.sql. Ela lista as queries mais caras do seu ambiente, mas ordenadas por usuário e, em seguida, pela query mais cara.

O propósito dessa query é mostrar quais usuários ou service accounts estão gastando mais e em quais queries. Muitas vezes, nessa lista aparecem processos rodando queries ineficientes dos quais você nem sabia. Em alguns casos extremos, podem ser processos em outros projetos ou nuvens. Adicionar filtros para removê-los também pode ser útil.

Essas são as três queries principais, bastante genéricas, para ajudar a descobrir queries que custam mais do que o esperado. No repositório do GitHub há queries adicionais para finalidades mais específicas, como encontrar queries originadas no Looker, quantas vezes uma query roda, quanto custam queries com labels específicas etc. São bem específicas, mas foram usadas por mim e por outros membros do meu time na DoiT no passado, então estamos compartilhando com a comunidade.

O próximo grande tópico é encontrar queries que estão consumindo mais recursos do que o necessário e podem não estar performando como esperado. Em muitos casos, essas queries andam de mãos dadas com as mais caras, então é provável que algumas se repitam.

Nesta seção, o termo complexidade vai aparecer bastante. Definir o termo complexidade é, bem, complexo, mas, para simplificar, defino aqui como "quantos slots uma query usa durante sua execução". Esse valor é definido como o tempo total que os slots passaram trabalhando, dividido pelo tempo total de execução da query. Veja um exemplo desse cálculo aplicado à view JOBS_BY_PROJECT:

SELECT
 SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount
FROM
 `<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

Respondendo a uma dúvida comum: trata-se de uma aproximação, pois, infelizmente, o BigQuery não retorna os valores exatos. Esse cálculo é o mesmo que a interface do BigQuery exibe.

Em qualquer sistema de banco de dados, quando uma query é mais complexa do ponto de vista lógico, isso costuma ter correlação direta com a complexidade de executá-la internamente. O BigQuery não foge à regra e, além disso, em geral há correlação direta entre essa complexidade e o custo da query. Ou seja, de modo geral, reduzir a complexidade também reduz custos.

Dito isso, a principal query do repositório do GitHub para avaliar a complexidade de queries é a top_complex_queries.sql. Ela retorna as queries que mais usam slots ao longo da execução, então pegar as do topo ajuda a identificar queries que podem ter problemas de performance.

Não é uma ciência exata, pois muitas vezes queries complexas precisam continuar assim e funcionam corretamente, mas é uma forma de encontrar candidatas que podem estar com algum problema.

Outra forma de identificar problemas de performance em queries é olhar para aquelas que demoram mais para rodar. Como mencionado acima, isso também não é uma ciência exata, pois muitas vezes uma query demora simplesmente porque tem que demorar.

A query em longest_running_queries.sql retorna todas as queries do período, ordenadas da mais longa para a mais curta. Isso joga as queries que demoram mais para o topo, facilitando a análise. A coluna runtimeToBytesBilledRatio dessa query dá uma boa ideia de quantos bytes a query processou por milissegundo. Em geral, se esse número for maior que 1, vale verificar se há algo que possa ser otimizado para reduzir a quantidade de dados processados.

Em uma parte futura desta série, o tuning de performance será abordado com bem mais profundidade, mostrando algumas das armadilhas mais comuns e pouco conhecidas da performance do BigQuery, além de métodos para superá-las.

O último tópico abordado nesta parte da série é um conjunto de queries no repositório do GitHub que mostram informações ou metadados mais gerais do que os abordados até aqui.

Existem vários tipos de jobs, como queries, loads, extracts e cópias de tabelas. Os mais comuns são, de longe, queries e loads, então o repositório tem os arquivos load_job_information.sql e query_job_information.sql, que retornam esses tipos de jobs e metadados úteis sobre eles.

Há também uma versão mais genérica chamada general_job_information.sql, que retorna todos os tipos de jobs e metadados genéricos sobre eles.

Vou abrir esta seção dizendo que, em dezembro de 2022, o Google fez uma mudança no comportamento do BigQuery em relação a queries concorrentes. Antes havia um limite rígido de 100 queries concorrentes por projeto, mas, antecipando mudanças futuras e o lançamento das filas de queries, esse valor foi alterado para escalar conforme a quantidade de slots disponíveis.

Vale notar que isso não está totalmente documentado no momento em que escrevo este artigo, mas reuni essas informações junto ao suporte e na documentação existente.

Por padrão, esse valor é definido como zero, ou seja, o BigQuery determina dinamicamente a concorrência com base nos recursos disponíveis que podem ser alocados ao projeto. Ao migrar para o pricing flat-rate, é possível definir uma configuração chamada concorrência máxima na reserva para solicitar esse número como teto de concorrência. O Google não pode garantir que esse valor será atingido, mas tenta cumpri-lo. A documentação oficial está na página de filas de queries aqui.

Feito esse aviso, vamos mergulhar nos problemas com queries concorrentes e em como identificá-los.

Ao executar queries, há momentos em que você roda muitas em paralelo e recebe a temida mensagem 503 "Service Unavailable" ou a mais nova "Query was not admitted as the maximum concurrency has been reached". Isso significa que seu nível de concorrência foi ultrapassado e as queries não serão agendadas. Ruim por motivos óbvios.

Mesmo que sua organização ainda não tenha esbarrado nesse limite em alguns projetos, é bom estar ciente para evitá-lo de forma preventiva. Vou discutir algumas estratégias para mitigar isso ou para projetar de modo a evitar que aconteça em uma parte futura desta série, depois que as novas mudanças implementadas pelo Google puderem ser totalmente analisadas e compreendidas.

Enquanto isso, para conferir suas médias de concorrência por minuto e por segundo, use as queries concurrent_queries_by_minute.sql e concurrent_queries_by_seconds.sql. Elas retornam a quantidade média de queries concorrentes em execução a cada minuto (ou segundo) no intervalo definido.

Muitas vezes, uma query é executada várias vezes, seja por um usuário, seja por algum processo automatizado. Como cada query custa dinheiro, é uma boa ideia saber com que frequência ela roda para identificar se está sendo executada vezes demais e gerando custos excessivos.

A query query_counts.sql mostra quantas vezes uma query foi executada dentro do intervalo de tempo. Isso é muito útil para descobrir se uma query específica está rodando mais do que deveria, custando mais dinheiro. Ela também inclui o custo total da query no intervalo especificado, ajudando você a decidir se está rodando e custando demais ou não.

Encerramos aqui as três primeiras partes do conteúdo sobre otimização do BigQuery. Na próxima seção, montei uma tabela com o conteúdo do repositório do GitHub, que será atualizada conforme novas queries forem adicionadas, documentando a funcionalidade de cada uma.

Mais partes desta série serão adicionadas em breve, já que o BigQuery está passando por uma fase de mudanças, e estarei aqui para mergulhar em como manter seu uso otimizado conforme essas novidades forem implantadas.