Come individuare le query BigQuery più costose

Un estratto dalla nostra serie sull'ottimizzazione di costi e performance di BigQuery: ecco come individuare, all'interno del suo ambiente, le query che incidono maggiormente sui costi.

Un estratto dalla serie sull'ottimizzazione di costi e performance di BigQuery

La prima parte di questa serie ha illustrato i prerequisiti per eseguire query in BigQuery, mentre la seconda parte ha analizzato gli errori più frequenti che si incontrano quando si ottimizza l'utilizzo di BigQuery. In questo articolo vediamo come identificare le query più costose all'interno del proprio ambiente. Tutto il codice utilizzato nella serie è disponibile in questo repository GitHub.

Le query più costose in assoluto

La prima query da prendere in esame si trova nel file top_costly_queries.sql. È probabilmente la più importante in assoluto sul fronte dei costi, perché restituisce il costo complessivo che una query genera nell'intervallo considerato, sommando tutte le sue iterazioni. L'obiettivo è duplice: capire qual è la query più onerosa nel suo ambiente e verificare se una query costosa viene eseguita più volte del necessario.

Le singole query più costose

La seconda query da utilizzare in questa sezione si trova nel file top_billed_queries.sql. Elenca le query più costose del suo ambiente, ordinate in modo decrescente a partire dalla più onerosa. A differenza della precedente, non tiene conto delle esecuzioni multiple della stessa query: mostra semplicemente il costo per singola esecuzione.

Eseguendo questa query, le più costose verranno subito in cima al risultato. Individui la prima della lista e controlli quanti dati ha elaborato (le colonne total*Billed). Quindi prenda quel valore e analizzi la query stessa per capire cosa la rende così onerosa.

Una nota sulle query duplicate

Se la stessa query compare più volte nei risultati, falsando il quadro o rendendolo difficile da consultare, utilizzi il file top_billed_queries_deduplicated.sql. Si tratta della stessa query, ma deduplica le esecuzioni identiche in modo da restituire un solo risultato. Sarà più lenta da eseguire e analizzerà più dati, ma le restituirà risultati deduplicati.

Al momento BigQuery non offre un'aggregazione nativa di "string similarity", come la distanza di Hamming o di Levenshtein, quindi filtrare query simili che differiscono solo per piccoli dettagli (per esempio una data) è relativamente complesso. In caso di necessità, si trovano alcune soluzioni UDF che implementano per BigQuery alcuni algoritmi di "string similarity", ma è preferibile costruire una soluzione esterna a BigQuery, perché le implementazioni UDF di algoritmi così onerosi sul piano computazionale risultano piuttosto lente.

Gli utenti più costosi

Una terza query utile per analizzare i costi è quella contenuta nel file top_cost_users.sql. Elenca le query più costose del suo ambiente, ma ordinate per utente e, a seguire, per query più onerosa.

L'obiettivo è mostrare quali utenti o service account stanno spendendo di più e su quali query. Spesso in questa lista emergono processi che eseguono query inefficienti di cui forse non era a conoscenza. Nei casi più estremi, questi processi si trovano in altri progetti o cloud. Aggiungere ulteriori filtri per escluderli può rivelarsi utile.

Altre query nel repository

Nel repository GitHub sono incluse anche query aggiuntive per scopi più specifici: individuare le query originate da Looker, contare il numero di esecuzioni di una query, calcolare il costo di query con etichette specifiche, e così via. Sono query molto particolari, ma le abbiamo utilizzate in prima persona in DoiT e abbiamo deciso di condividerle con la community.

Individuare le query con problemi di performance

Il prossimo grande tema è come individuare le query che consumano più risorse del necessario e non offrono le performance attese. Spesso queste query coincidono con quelle più costose, quindi ci sarà una certa sovrapposizione.

In questa sezione definiamo "complessità" come il numero di slot che una query utilizza durante la sua esecuzione. Il valore corrisponde al tempo totale di slot impiegato per il lavoro, diviso per il tempo totale di esecuzione della query. Ecco un esempio applicato alla 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

Si tratta di un'approssimazione perché, purtroppo, BigQuery non restituisce i valori esatti; è del resto lo stesso calcolo che mostra anche la UI di BigQuery.

Complessità e numero di slot

In qualsiasi sistema di database, una maggiore complessità logica di una query si traduce di norma in una maggiore complessità di esecuzione interna. BigQuery non fa eccezione. A questo si aggiunge che, in genere, esiste una correlazione diretta tra complessità e costo della query. In linea di massima, ridurre la complessità significa ridurre anche i costi.

Detto questo, la query principale del repository GitHub per misurare la complessità è top_complex_queries.sql. Restituisce le query che utilizzano il maggior numero di slot durante la loro esecuzione: prendendo le prime in classifica si possono individuare quelle che potrebbero presentare problemi di performance. Non è una scienza esatta, perché alcune query devono restare complesse per funzionare correttamente, ma è un buon modo per individuare i candidati problematici.

Query a lunga esecuzione

Un altro modo per intercettare problemi di performance è osservare le query che impiegano più tempo a essere eseguite. Va però considerato che spesso una query è lunga semplicemente perché non può essere altrimenti.

La query in longest_running_queries.sql restituisce tutte le query nell'intervallo temporale ordinate dalla più lenta alla più veloce, portando in cima quelle che richiedono più tempo, in modo da poterle analizzare. La colonna runtimeToBytesBilledRatio offre una buona indicazione dei byte elaborati dalla query per millisecondo. In genere, se questo valore è superiore a 1, vale la pena verificare se sono possibili ottimizzazioni per ridurre la quantità di dati elaborati.

Una nota sui problemi di performance

In una prossima puntata di questa serie tratterò il performance tuning più in dettaglio, illustrando alcune delle insidie più comuni e meno note delle performance di BigQuery, insieme ai metodi per superarle.

Query di carattere generale dal repository

L'ultimo tema affrontato in questa parte della serie è un insieme di query disponibili nel repository GitHub che restituiscono informazioni o metadati di carattere più generale rispetto a quelle viste finora.

Query per tipo di job

Tra i vari tipi di job esistenti (query, load, extract e table copy), i più diffusi in assoluto sono query e load. I file load_job_information.sql e query_job_information.sql presenti nel repository restituiscono questi tipi di job con i relativi metadati utili. Una versione più generica, chiamata general_job_information.sql, restituisce invece tutti i tipi di job con i metadati di carattere generale.

Query concorrenti

Nel dicembre 2022 Google ha modificato il comportamento di BigQuery in relazione alle query concorrenti. In vista delle novità in arrivo e dell'introduzione delle query queues, ha aumentato il limite fisso di 100 query concorrenti per progetto, scalandolo in base al numero di slot disponibili. Al momento della stesura di questo articolo, la modifica non era ancora completamente documentata, ma abbiamo ricostruito queste informazioni a partire dal supporto e dalla documentazione disponibile.

Per impostazione predefinita il valore è zero, il che significa che BigQuery determina dinamicamente la concorrenza in base alle risorse disponibili allocabili al progetto. Passando al pricing flat-rate, è possibile impostare sulla reservation un parametro chiamato maximum concurrency, con cui richiedere un determinato livello massimo di concorrenza. Google non può garantire che il valore venga effettivamente rispettato, ma si impegna a rispettarlo. La documentazione ufficiale è consultabile nella pagina dedicata alle query queues qui. Fatta questa premessa, passiamo ai problemi legati alle query concorrenti.

Se esegue troppe query in parallelo, riceverà il temuto messaggio 503 "Service Unavailable" oppure, più di recente, "Query was not admitted as the maximum concurrency has been reached." Significa che il livello di concorrenza è stato superato e che le query non verranno schedulate. È una situazione da evitare per ovvie ragioni.

Quando le novità introdotte da Google saranno state analizzate e comprese a fondo, presenteremo alcune strategie per mitigare il problema o per progettare i sistemi in modo che non si verifichi. Nel frattempo, può utilizzare le query concurrent_queries_by_minute.sql e concurrent_queries_by_seconds.sql per calcolare la media di concorrenza al minuto e al secondo. Restituiscono il numero medio di query in esecuzione concorrente in ciascun minuto (o secondo) nell'intervallo definito.

Conteggio delle query

Capita spesso che le query vengano eseguite più volte, da un utente o tramite un processo automatizzato. La query query_counts.sql mostra quante volte una query è stata eseguita nell'intervallo temporale considerato. È molto utile per capire se una query specifica viene eseguita più del dovuto, generando spesa inutile. Include anche il costo totale della query nell'intervallo indicato.

I prossimi passi

Questo articolo è una versione condensata della mia serie sull'ottimizzazione delle query BigQuery, che verrà ampliata man mano che BigQuery introdurrà nuove modifiche. Se è già cliente DoiT, può eseguire tutti questi passaggi all'interno della funzionalità BQ Lens di DoiT Cloud Intelligence™. Nel frattempo, può contattarci in DoiT per avvalersi della nostra esperienza ampia e approfondita su BigQuery, machine learning e business intelligence.