Ottimizzazioni BigQuery (Parte 3)

Parte 1

Parte 2

Le due sezioni precedenti hanno trattato molti argomenti: prerequisiti, nozioni utili da avere a portata di mano e configurazione dell'infrastruttura per analizzare i dati.

Tutto il codice utilizzato in questa serie si trova nel seguente repository GitHub: https://github.com/doitintl/bigquery-optimization-queries

Poiché i due metodi di monitoraggio generano schemi molto diversi tra loro, ho suddiviso i file SQL in due cartelle (audit_log e information_schema) all'interno del repository.

Ogni directory contiene una serie di file SQL dedicati a uno scopo specifico e in ciascuna cartella è presente un file README.md che ne illustra la funzione, per agevolarne la consultazione.

Nella directory radice si trova uno script Python denominato generate_sql_files.py che genera una copia dei file SQL personalizzati con le informazioni del suo progetto e del suo dataset.

Trattandosi di un repository GitHub in continua evoluzione, ho preparato un articolo che elenca ciascuna query con la relativa descrizione, disponibile qui.

L'articolo verrà aggiornato man mano che saranno aggiunte nuove query.

Dalla prossima sezione e per il resto della serie farò riferimento a numerose query e analizzerò molti dati. L'interfaccia di BigQuery se la cava abbastanza bene con grandi volumi di query e analisi, ma consiglio CALDAMENTE di esportare questi dati in un foglio di calcolo.

Google Sheets è la soluzione ideale, perché BigQuery può esportarvi i risultati direttamente (con qualche limite di dimensione). Inoltre, esportare i dati una sola volta in un foglio di calcolo è più economico che rieseguire più volte la stessa query per condurre l'analisi.

Occorre anche tenere d'occhio i costi. Alcune di queste query possono essere molto onerose, quindi consiglio sempre di verificare il costo della query prima di eseguirla. In ogni query ho inserito una variabile chiamata interval_in_days, che definisce l'intervallo temporale considerato e può essere modificata per ridurre la quantità di dati analizzati. Alcuni nostri clienti hanno semplicemente modificato le clausole WHERE per definire intervalli su misura: con queste query, insomma, i margini di personalizzazione sono praticamente illimitati.

Tutti i passaggi descritti in questa parte possono essere eseguiti dalla sezione BQ Lens del CMP. Il pannello Explorer consente di compiere queste operazioni in modo visuale; di fatto, se si utilizzano le query basate su audit log sink, molte di esse sono pressoché identiche a quelle eseguite dal CMP per visualizzare i dati.

Il primo passo per ottimizzare i costi di BigQuery è individuare le query più onerose nel proprio ambiente. Spesso non si è nemmeno consapevoli che esistano e quindi, citando le celebri parole di G.I. Joe nei cartoni del sabato mattina: "Sapere è metà della battaglia".

Una volta individuate, è il momento di ottimizzarle, identificare i processi o gli utenti problematici che le eseguono e correggerli.

Farò riferimento ai file SQL del repository GitHub citato sopra (link). Tenga presente che è possibile utilizzare indifferentemente i file basati su audit log o su information_schema, perché restituiscono lo stesso risultato.

La prima query da utilizzare nella nostra analisi si trova nel file top_costly_queries.sql ed è probabilmente la più importante in assoluto sul fronte dei costi. Considera il costo totale che una query genera nell'intervallo, sommando tutte le sue iterazioni. Mostra quindi le query realmente più costose, che possono essere eseguite più volte nell'intervallo arrivando a costare molto più di quanto possa sembrare a prima vista.

Lo scopo di questa query è duplice: individuare la query più costosa in assoluto del proprio ambiente e capire se una query onerosa viene eseguita più volte del necessario. Spesso un processo come un cron job, un task all'interno di un DAG su un'istanza Airflow, una Cloud Function ecc. esegue una query più volte — in molti casi più di quanto serva. Ancora più spesso, chi l'ha creata, chi la mantiene o chi paga la fattura del cloud non ha la minima idea di quanto costi quella query per singola iterazione e/o al mese. Ed è qui che questa query torna utile!

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 dalla più onerosa in ordine decrescente. A differenza della prima, non considera il numero di esecuzioni: mostra semplicemente il costo per singola esecuzione.

Eseguendola, le query più costose verranno immediatamente in cima ai risultati. Una delle prime cose da fare è individuare la query in testa e verificare quanti dati ha elaborato (le colonne total*Billed). A questo punto, prenda quel valore ed esamini la query stessa per capire cosa la renda così costosa.

Spesso i colpevoli sono i problemi che ho elencato sopra, ma potrebbero esserci anche altre dinamiche in gioco. Raccomando quindi di analizzare le query per vedere se emerge qualche elemento evidente.

Se i risultati restituiscono più volte la stessa identica query, distorcendo l'analisi o rendendola difficile da leggere, utilizzi invece il file top_billed_queries_deduplicated.sql. Si tratta della stessa query, ma con una deduplicazione che fa apparire una sola volta ciascuna query identica. L'esecuzione richiederà più tempo e analizzerà più dati, ma restituirà risultati senza duplicati.

Tenga presente che, al momento in cui scrivo, BigQuery non offre nativamente una funzione di aggregazione per la "similarità tra stringhe", come la distanza di Hamming o la distanza di Levenshtein, quindi filtrare query simili con piccole differenze (ad esempio una data) è relativamente difficile. Se proprio serve, esistono alcune soluzioni basate su UDF che implementano qualche algoritmo di "string similarity" per BigQuery, ma suggerisco piuttosto di realizzare la soluzione al di fuori di BigQuery, perché le implementazioni UDF di algoritmi così onerosi dal punto di vista computazionale sono piuttosto lente.

La terza e ultima query che utilizzerà per analizzare i costi è il file top_cost_users.sql. Elenca le query più costose del suo ambiente, ordinate per utente e poi per query più onerosa.

Lo scopo è mostrare quali utenti o service account stanno spendendo di più e su quali query. Spesso in elenco compaiono processi che eseguono query inefficienti di cui non si è a conoscenza. In casi estremi può trattarsi di processi che si trovano in altri progetti o cloud. Aggiungere ulteriori filtri per escluderli può rivelarsi utile.

Queste sono le tre query principali, abbastanza generiche, utili per scoprire le query che costano più del previsto. Nel repository GitHub sono incluse altre query con scopi più specifici: individuare le query originate da Looker, contare quante volte una query viene eseguita, calcolare il costo delle query con etichette specifiche e così via. Sono molto puntuali, ma sono query che io e altri colleghi del mio team in DoiT abbiamo utilizzato in passato e che condividiamo con la community.

Il prossimo macro-tema riguarda l'individuazione delle query che consumano più risorse del necessario e che potrebbero non offrire le prestazioni attese. In molti casi le query identificate qui coincidono con le più costose, quindi è probabile trovare alcune sovrapposizioni.

In questa sezione il termine complessità ricorrerà spesso. Definirlo è, beh, complesso, ma per semplicità qui lo intendo come "quanti slot una query utilizza durante la sua esecuzione". Questo valore è dato dal tempo totale degli slot impiegato a svolgere lavoro, diviso per il tempo totale di esecuzione della query. Ecco un esempio applicato alla vista 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

Per rispondere a una domanda che mi viene posta spesso: si tratta di un'approssimazione, perché purtroppo BigQuery non restituisce i valori esatti, ed è anche il calcolo che mostra l'interfaccia stessa di BigQuery.

In qualsiasi sistema di database, quando una query è più complessa dal punto di vista logico esiste in genere una correlazione diretta con la complessità della sua esecuzione interna. BigQuery non fa eccezione e, in più, esiste di solito una correlazione diretta tra questa complessità e il costo della query. In linea di massima, quindi, ridurre la complessità riduce anche i costi.

Detto questo, la query principale del repository GitHub da utilizzare per valutare la complessità è top_complex_queries.sql. Restituisce le query che usano il maggior numero di slot durante la loro esecuzione: analizzando le prime in classifica si possono individuare quelle che potrebbero presentare problemi di prestazioni.

Non è una scienza esatta: spesso le query complesse devono restare tali e funzionano correttamente così come sono, ma è un metodo per individuare candidati che potrebbero avere criticità.

Un altro modo per individuare problemi di prestazioni è osservare le query che impiegano più tempo a essere eseguite. Come accennato, anche in questo caso non si tratta di una scienza esatta, perché spesso una query è semplicemente lunga per natura.

La query in longest_running_queries.sql restituisce tutte le query nell'intervallo considerato, ordinate dalla più lunga alla più breve. In questo modo le query più lente si trovano subito in cima e possono essere analizzate per prime. La colonna runtimeToBytesBilledRatio offre una buona indicazione di quanti byte la query ha elaborato per millisecondo. In generale, se questo valore è superiore a 1, vale la pena verificare se sia possibile ottimizzare la query per ridurre la quantità di dati elaborati.

In una prossima parte di questa serie il tema del performance tuning verrà approfondito molto più nel dettaglio, illustrando alcune delle insidie meno note delle prestazioni di BigQuery e i metodi per superarle.

L'ultimo tema di questa parte della serie riguarda un insieme di query nel repository GitHub che mostrano informazioni o metadati di carattere più generale rispetto a quelli trattati finora.

Esistono diversi tipi di job: query, load, extract e copie di tabelle. I più comuni sono di gran lunga query e load, perciò nel repository sono presenti i file load_job_information.sql e query_job_information.sql, che restituiscono questi tipi di job e i relativi metadati utili.

Esiste anche una versione più generica chiamata general_job_information.sql, che restituisce tutti i tipi di job con i metadati generali a essi associati.

Apro questa sezione segnalando che a dicembre 2022 Google ha modificato il comportamento di BigQuery in materia di query concorrenti. In precedenza esisteva un limite rigido di 100 query concorrenti per progetto, ma in vista delle modifiche imminenti e del rilascio delle query queues questo valore è stato reso scalabile in funzione del numero di slot disponibili.

Tenga presente che, al momento in cui scrivo, questa novità non è ancora completamente documentata: le informazioni che riporto provengono dal supporto e dalla documentazione esistente.

Per impostazione predefinita questo valore è impostato a zero, il che significa che BigQuery determina dinamicamente la concorrenza in base alle risorse disponibili che possono essere allocate al progetto. Quando si passa a un modello di prezzo flat-rate, è possibile impostare un parametro chiamato maximum concurrency sulla reservation, per richiedere quel numero come concorrenza massima. Google non può garantire che venga rispettato esattamente, ma cerca di farlo. La documentazione ufficiale è disponibile nella pagina dedicata alle query queues qui.

Fatta questa premessa, vediamo i problemi legati alle query concorrenti e come individuarli.

Quando si eseguono query, capita di lanciarne troppe in parallelo e ricevere il temuto errore 503 "Service Unavailable" oppure il più recente messaggio "Query was not admitted as the maximum concurrency has been reached". Significa che il livello di concorrenza è stato superato e le query non verranno schedulate. Per ovvie ragioni, è un problema.

Anche se la sua organizzazione non ha ancora raggiunto questo limite in alcuni progetti, è bene esserne consapevoli per evitarlo in modo preventivo. In una parte successiva di questa serie illustrerò alcune strategie per mitigare il problema o per progettare in modo da prevenirlo, dopo aver analizzato e compreso a fondo le novità appena introdotte da Google.

Nel frattempo, per calcolare le medie di concorrenza al minuto e al secondo, utilizzi le query concurrent_queries_by_minute.sql e concurrent_queries_by_seconds.sql. Restituiranno la media delle query concorrenti in esecuzione per ogni minuto (o secondo) nell'intervallo definito.

Spesso una query viene eseguita più volte, da un utente o tramite un processo automatizzato. Poiché ogni esecuzione comporta un costo, è utile sapere con quale frequenza una query viene eseguita, per capire se ciò avviene troppo spesso e genera spese eccessive.

La query query_counts.sql mostra quante volte una query è stata eseguita nell'intervallo temporale considerato. È molto utile per stabilire se una specifica query viene eseguita più del necessario, generando costi maggiori. Include anche il costo totale della query nell'intervallo specificato, per facilitare la valutazione su quanto sia onerosa.

Si concludono così le prime tre sezioni dedicate all'ottimizzazione di BigQuery. Nella prossima ho inserito una tabella che illustra i contenuti del repository GitHub, che verrà aggiornata man mano che saranno aggiunte nuove query, documentando la funzionalità di ciascuna.

Ulteriori sezioni di questa serie verranno pubblicate a breve: BigQuery sta attraversando una fase di cambiamenti e sarò qui per analizzare come mantenere il suo utilizzo ottimizzato man mano che verranno rilasciati.