Ottenere informazioni sulle prestazioni delle query

Il grafico di esecuzione di una query è una rappresentazione visiva dei passaggi eseguiti da BigQuery per eseguire la query. Questo documento descrive come utilizzare il grafico di esecuzione delle query per diagnosticare i problemi di prestazioni di queste ultime e per visualizzarne le informazioni sulle prestazioni.

BigQuery offre prestazioni delle query elevate, ma è anche un sistema distribuito complesso con molti fattori interni ed esterni che possono influire sulla velocità delle query. La natura dichiarativa di SQL può anche nascondere la complessità dell'esecuzione delle query. Ciò significa che, quando le query vengono eseguite più lentamente del previsto o più lentamente delle esecuzioni precedenti, comprendere cosa è successo può essere difficile.

Il grafico di esecuzione delle query fornisce un'interfaccia grafica dinamica per esaminare il piano di query e i dettagli sulle prestazioni delle query. Puoi esaminare il grafico di esecuzione delle query per qualsiasi query in esecuzione o completata.

Puoi anche utilizzare il grafico di esecuzione delle query per ottenere informazioni sulle prestazioni delle query. Gli insight sulle prestazioni offrono suggerimenti secondo il criterio del "best effort" per aiutarti a migliorare le prestazioni delle query. Poiché le prestazioni delle query sono sfaccettate, gli insight sulle prestazioni potrebbero fornire solo un quadro parziale delle prestazioni complessive delle query.

Autorizzazioni obbligatorie

Per utilizzare il grafico di esecuzione delle query, devi disporre delle seguenti autorizzazioni:

bigquery.jobs.get
bigquery.jobs.listAll

Queste autorizzazioni sono disponibili tramite i seguenti ruoli IAM (Identity and Access Management) predefiniti di BigQuery:

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

Struttura del grafico di esecuzione

Il grafico di esecuzione delle query fornisce una visualizzazione grafica del piano di query nella console. Ogni riquadro rappresenta una fase del piano di query ad esempio:

Input: lettura dei dati da una tabella o selezione di colonne specifiche
Join: unione dei dati di due tabelle in base alla condizione JOIN
Aggregate: esecuzione di calcoli come SUM
Sort: ordinamento dei risultati

Le fasi sono costituite da passaggi che descrivono le singole operazioni eseguite da ogni worker all'interno di una fase esegue. Puoi fare clic su una fase per aprirla e visualizzarne i passaggi. Le fasi includono anche informazioni sui tempi relativi e assoluti. I nomi delle fasi riepilogano i passaggi eseguiti. Ad esempio, una fase con join nel nome significa che il passaggio principale della fase è un'operazione JOIN. I nomi delle fasi che terminano con + indicano che eseguono altri passaggi importanti. Ad esempio, una fase con JOIN+ nel nome significa che la fase esegue un'operazione di join e altri passaggi importanti.

Le linee che collegano le fasi rappresentano lo scambio di dati intermedi tra le fasi. BigQuery archivia i dati intermedi nella memoria di shuffling durante l'esecuzione delle fasi. I numeri sui bordi indicano il numero stimato di righe scambiate tra le fasi. La quota di memoria di shuffling è correlata al numero di slot allocati all'account. Se la quota di shuffling viene superata, la memoria di shuffling può essere trasferita sul disco e causare un rallentamento drastico delle prestazioni delle query.

Visualizzare gli insight sulle prestazioni delle query

Console

Per visualizzare gli insight sulle prestazioni delle query:

Apri la pagina BigQuery nella Google Cloud console.

Vai alla pagina BigQuery
Nel riquadro a sinistra, fai clic su Spazio di esplorazione:

Se non vedi il riquadro a sinistra, fai clic su Espandi riquadro a sinistra per aprirlo.
Nel riquadro Spazio di esplorazione, fai clic su Cronologia job.
Fai clic su Cronologia personale o Cronologia progetto.
Nell'elenco dei job, individua il job di query che ti interessa. Fai clic su Azioni e scegli Visualizza job nell'editor.
Seleziona la scheda Grafico di esecuzione per visualizzare una rappresentazione grafica di ogni fase della query:

Per determinare se una fase della query ha insight sulle prestazioni, esamina l'icona visualizzata. Le fasi con un'icona di informazioni hanno insight sulle prestazioni. Le fasi con un'icono di spunta check icon non hanno insight sulle prestazioni.
Fai clic su una fase per aprire il riquadro dei dettagli della fase, dove puoi visualizzare le seguenti informazioni:
- Informazioni sul piano di query per la fase.
- I passaggi eseguiti nella fase.
- Eventuali insight sulle prestazioni applicabili.
(Facoltativo) Se stai esaminando una query in esecuzione, fai clic su Sincronizza per aggiornare il grafico di esecuzione in modo che rifletta lo stato attuale della query.
(Facoltativo) Per evidenziare le fasi principali in base alla durata della fase nel grafico, fai clic su Evidenzia le fasi principali per durata.
(Facoltativo) Per evidenziare le fasi principali in base al tempo di slot utilizzato nel grafico, fai clic su Evidenzia le fasi principali per elaborazione.
(Facoltativo) Per includere le fasi di ridistribuzione dello shuffling nel grafico, fai clic su Mostra le fasi di ridistribuzione dello shuffling.

Utilizza questa opzione per mostrare le fasi di ripartizionamento e unione nascoste nel grafico di esecuzione predefinito.

Le fasi di ripartizionamento e unione vengono introdotte durante l'esecuzione della query e vengono utilizzate per migliorare la distribuzione dei dati tra i worker che elaborano la query. Poiché queste fasi non sono correlate al testo della query, sono nascoste per semplificare il piano di query visualizzato.

Per qualsiasi query con problemi di regressione delle prestazioni, gli insight sulle prestazioni vengono visualizzati anche nella scheda Informazioni sul job della query:

La scheda Informazioni sul job.

SQL

Nella Google Cloud console, vai alla pagina BigQuery.

Vai a BigQuery

Nell'editor di query, inserisci la seguente istruzione:

SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-REGION_NAME`.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
      OR bi_engine_reasons IS NOT NULL
      OR high_cardinality_joins IS NOT NULL
      OR partition_skew IS NOT NULL
    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
  );

Fai clic su Esegui.

Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.

API

Puoi ottenere insight sulle prestazioni delle query in un formato non grafico chiamando il metodo API jobs.list ed esaminando le informazioni JobStatistics2 restituite.

Interpretare gli insight sulle prestazioni delle query

Utilizza questa sezione per scoprire di più sul significato degli insight sulle prestazioni e su come risolverli.

Gli insight sulle prestazioni sono destinati a due tipi di utenti:

Analisti: eseguono query in un progetto. Sono interessati a scoprire perché una query eseguita in precedenza viene eseguita in modo imprevisto più lentamente e a ricevere suggerimenti su come migliorare le prestazioni di una query. Dispongono delle autorizzazioni descritte in Autorizzazioni obbligatorie.
Amministratori di data lake o data warehouse: gestiscono le risorse e le prenotazioni BigQuery della loro organizzazione. Dispongono delle autorizzazioni associate al ruolo Amministratore BigQuery.

Ciascuna delle seguenti sezioni fornisce indicazioni su cosa puoi fare per risolvere un insight sulle prestazioni che ricevi, in base al ruolo che ricopri.

Contesa slot

Quando esegui una query, BigQuery tenta di suddividere il lavoro necessario per la query in attività. Un'attività è una singola porzione di dati che viene inserita e generata da una fase. Un singolo slot seleziona un'attività ed esegue la porzione di dati per la fase. Idealmente, gli slot BigQuery eseguono queste attività in parallelo per ottenere prestazioni elevate. La contesa slot si verifica quando la query ha molte attività pronte per l'esecuzione, ma BigQuery non riesce a ottenere un numero sufficiente di slot disponibili per eseguirle.

Cosa fare se sei un analista

Riduci i dati elaborati nella query seguendo le indicazioni riportate in Ridurre i dati elaborati nelle query.

Cosa fare se sei un amministratore

Aumenta la disponibilità degli slot o riduci l'utilizzo degli slot eseguendo le seguenti azioni:

Se utilizzi i prezzi on demand di BigQuery, le query utilizzano un pool di slot condiviso. Valuta la possibilità di passare ai prezzi di analisi basati sulla capacità acquistando invece le prenotazioni. Le prenotazioni ti consentono di prenotare slot dedicati per le query della tua organizzazione.
Se utilizzi le prenotazioni BigQuery, assicurati che nella prenotazione assegnata al progetto che ha eseguito la query siano presenti slot sufficienti. La prenotazione potrebbe non avere slot sufficienti in questi scenari:
- Esistono altri job che utilizzano gli slot di prenotazione. Puoi utilizzare i grafici delle risorse di amministrazione per vedere in che modo la tua organizzazione utilizza la prenotazione.
- La prenotazione non ha slot assegnati sufficienti per eseguire le query abbastanza velocemente. Puoi utilizzare lo strumento di stima degli slot per ottenere una stima delle dimensioni delle prenotazioni necessarie per elaborare in modo efficiente le attività delle query.
Per risolvere il problema, puoi provare una delle seguenti soluzioni:
- Aggiungi altri slot (slot di riferimento o slot di prenotazione massimi) a questa prenotazione.
- Crea una prenotazione aggiuntiva e assegnala al progetto che esegue la query.
- Distribuisci le query che richiedono molte risorse nel tempo all'interno di una prenotazione o in prenotazioni diverse.
Assicurati che le tabelle su cui esegui le query siano sottoposte a clustering. Il clustering consente a BigQuery di leggere rapidamente le colonne con dati correlati.
Assicurati che le tabelle su cui esegui le query siano partizionate. Per le tabelle non partizionate, BigQuery legge l'intera tabella. Il partizionamento delle tabelle consente di eseguire query solo sul sottoinsieme di tabelle che ti interessa.

Quota di shuffling insufficiente

Prima di eseguire la query, BigQuery suddivide la logica della query in fasi. Gli slot BigQuery eseguono le attività per ogni fase. Quando uno slot completa l'esecuzione delle attività di una fase, archivia i risultati intermedi in shuffling. Le fasi successive della query leggono i dati dallo shuffling per continuare l'esecuzione della query. La quota di shuffling insufficiente si verifica quando hai più dati da scrivere nello shuffling rispetto alla capacità di shuffling disponibile.

Cosa fare se sei un analista

Analogamente alla contesa slot, la riduzione della quantità di dati elaborati dalla query potrebbe ridurre l'utilizzo dello shuffling. Per farlo, segui le indicazioni riportate in Ridurre i dati elaborati nelle query.

Alcune operazioni in SQL tendono a utilizzare più intensamente lo shuffling, in particolare JOIN operazioni e GROUP BY clausole. Se possibile, la riduzione della quantità di dati in queste operazioni potrebbe ridurre l'utilizzo dello shuffling.

Cosa fare se sei un amministratore

Riduci la contesa della quota di shuffling eseguendo le seguenti azioni:

Analogamente alla contesa slot, se utilizzi i prezzi on demand di BigQuery, le query utilizzano un pool di slot condiviso. Valuta la possibilità di passare ai prezzi di analisi basati sulla capacità acquistando invece le prenotazioni. Le prenotazioni ti offrono slot dedicati e capacità di shuffling per le query dei tuoi progetti.
Se utilizzi le prenotazioni BigQuery, gli slot sono dotati di capacità di shuffling dedicata. Se la prenotazione esegue alcune query che utilizzano intensamente lo shuffling, è possibile che altre query eseguite in parallelo non abbiano capacità di shuffling sufficiente. Puoi identificare i job che utilizzano intensamente la capacità di shuffling eseguendo una query sulla period_shuffle_ram_usage_ratio colonna nella INFORMATION_SCHEMA.JOBS_TIMELINE vista.

Per risolvere il problema, puoi provare una o più delle seguenti soluzioni:
- Aggiungi altri slot a questa prenotazione.
- Crea una prenotazione aggiuntiva e assegnala al progetto che esegue la query.
- Distribuisci le query che richiedono molte risorse nel tempo all'interno di una prenotazione o in prenotazioni diverse.

Per ulteriori informazioni sulla risoluzione dei problemi, consulta Errori relativi al limite di dimensioni dello shuffling nella pagina Risoluzione dei problemi di BigQuery.

Modifica della scala di input dei dati

La visualizzazione di questo insight sulle prestazioni indica che la query legge almeno il 50% di dati in più per una determinata tabella di input rispetto all'ultima esecuzione della query. Puoi utilizzare la cronologia delle modifiche delle tabelle per verificare se le dimensioni di una delle tabelle utilizzate nella query sono aumentate di recente.

Cosa fare se sei un analista

Riduci i dati elaborati nella query seguendo le indicazioni riportate in Ridurre i dati elaborati nelle query.

Join con cardinalità elevata

Quando una query contiene un join con chiavi non univoche su entrambi i lati del join, le dimensioni della tabella di output possono essere notevolmente maggiori rispetto alle dimensioni di una delle tabelle di input. Questo insight indica che il rapporto tra righe di output e righe di input è elevato e offre informazioni su questi conteggi di righe.

Cosa fare se sei un analista

Controlla le condizioni di join per verificare che l'aumento delle dimensioni della tabella di output sia previsto. Evita di utilizzare i cross join. Se devi utilizzare un cross join, prova a utilizzare una clausola GROUP BY per pre-aggregare i risultati o utilizza una funzione finestra. Per ulteriori informazioni, consulta Ridurre i dati prima di utilizzare un JOIN.

Disallineamento partizione

Per fornire feedback o richiedere assistenza per questa funzionalità, invia un'email a bq-query-inspector-feedback@google.com.

La distribuzione distorta dei dati può causare l'esecuzione lenta delle query. Quando una query è in esecuzione, BigQuery suddivide i dati in piccole partizioni per l'elaborazione parallela. Il disallineamento si verifica quando i dati vengono distribuiti in modo non uniforme tra queste partizioni, spesso a causa di valori che si verificano di frequente nelle chiavi di join o di raggruppamento, rendendo alcune partizioni significativamente più grandi di altre. Poiché un singolo slot elabora un'intera partizione e non può condividere il lavoro, una partizione di dimensioni eccessive può rallentare l'elaborazione, causare errori di "risorse superate" e, in casi estremi, bloccare lo slot.

Quando esegui un'operazione JOIN, BigQuery partiziona i dati sui lati sinistro e destro del join in base alle chiavi di join. Se una partizione è troppo grande, BigQuery tenta di ribilanciare i dati. Se il disallineamento è troppo grave per essere ribilanciato completamente, viene aggiunto un insight sul disallineamento della partizione alla fase JOIN nel grafico di esecuzione.

Identificare il disallineamento della partizione

Utilizza la scheda Grafico di esecuzione in BigQuery Studio per trovare la fase della query che presenta il disallineamento della partizione. L'insight viene contrassegnato nella fase. Dai dettagli della fase, puoi determinare la parte pertinente del testo della query e le tabelle in fase di elaborazione. Per ulteriori informazioni, consulta Comprendere i passaggi con il testo della query.

Esempio

La seguente query unisce le informazioni sul repository con le informazioni sui file. Il disallineamento può verificarsi se alcuni repository hanno molti più file di altri.

SELECT r.repo_name, COUNT(f.path) AS file_count
FROM `bigquery-public-data.github_repos.sample_repos` AS r
JOIN `bigquery-public-data.github_repos.sample_files` AS f
  ON r.repo_name = f.repo_name
WHERE r.watch_count > 10
GROUP BY r.repo_name

La chiave di join è repo_name. Nella tabella sample_repos, repo_name deve essere univoca. Tuttavia, nella tabella sample_files, repo_name può essere visualizzata più volte. Se alcuni valori repo_name vengono visualizzati in modo sproporzionato di frequente in sample_files, si verifica una distorsione dei dati.

Per verificare se esiste una distorsione dei dati, analizza la distribuzione della chiave di join nella tabella più grande (sample_files in questo caso). Esegui la seguente query per valutare la distribuzione di repo_name:

SELECT repo_name, COUNT(*) AS occurrences
FROM `bigquery-public-data.github_repos.sample_files`
GROUP BY repo_name
ORDER BY occurrences DESC

Per le tabelle di grandi dimensioni, utilizza la APPROX_TOP_COUNT funzione per stimare in modo efficiente i valori più frequenti.

SELECT APPROX_TOP_COUNT(repo_name, 100)
FROM `bigquery-public-data.github_repos.sample_files`

Se i conteggi per i valori principali sono di ordini di grandezza maggiori rispetto ad altri, è presente una distorsione dei dati.

Ridurre il disallineamento della partizione

Puoi utilizzare le seguenti strategie per risolvere il disallineamento della partizione:

Filtra i dati in anticipo. Riduci la quantità di dati elaborati applicando i filtri il prima possibile nella query. In questo modo puoi ridurre il numero di righe associate a chiavi distorte prima che raggiungano operazioni come JOIN o GROUP BY.

Dividi la query per isolare le chiavi distorte. Se il disallineamento è causato da alcuni valori di chiave specifici, simili al campo repo_name nell'esempio precedente, valuta la possibilità di dividere la query. Elabora i dati per le chiavi distorte separatamente dal resto dei dati, quindi combina i risultati utilizzando UNION ALL.

Esempio: isolamento di una chiave utilizzata di frequente.

-- Query for the skewed key
SELECT r.repo_name, COUNT(f.path) AS file_count
FROM `bigquery-public-data.github_repos.sample_repos` AS r
JOIN `bigquery-public-data.github_repos.sample_files` AS f
  ON r.repo_name = f.repo_name
WHERE r.watch_count > 10 AND r.repo_name = 'popular_repo'
GROUP BY r.repo_name

UNION ALL

-- Query for all other keys
SELECT r.repo_name, COUNT(f.path) AS file_count
FROM `bigquery-public-data.github_repos.sample_repos` AS r
JOIN `bigquery-public-data.github_repos.sample_files` AS f
  ON r.repo_name = f.repo_name
WHERE r.watch_count > 10 AND r.repo_name != 'popular_repo'
GROUP BY r.repo_name

Gestisci NULL e i valori predefiniti: una causa comune di disallineamento è un numero elevato di righe con NULL o stringhe vuote nelle colonne chiave. Se non hai bisogno di queste righe per l'analisi, filtrale utilizzando una clausola WHERE prima di JOIN o GROUP BY.
Riordina le operazioni: nelle query con più join, l'ordine può essere importante. Se possibile, esegui i join che riducono significativamente i conteggi delle righe all'inizio della query.
Utilizza funzioni approssimative: per le aggregazioni su dati distorti, valuta se un risultato approssimativo è accettabile. Le funzioni come APPROX_COUNT_DISTINCT sono più tolleranti alla distorsione dei dati rispetto alle funzioni esatte come COUNT(DISTINCT).

Interpretare le informazioni sulla fase della query

Oltre a utilizzare gli insight sulle prestazioni delle query, puoi anche utilizzare le seguenti linee guida quando esamini i dettagli della fase della query per determinare se esiste un problema con una query:

Se il valore Wait ms (Attesa in ms) per una o più fasi è elevato rispetto alle esecuzioni precedenti della query:
- Verifica se hai slot sufficienti per gestire il carico di lavoro. In caso contrario, esegui il bilanciamento del carico quando esegui query che richiedono molte risorse in modo che non competano tra loro.
- Se il valore Wait ms è più alto rispetto a una sola fase, esamina la fase precedente per verificare se è stato introdotto un collo di bottiglia. Elementi come modifiche sostanziali ai dati o allo schema delle tabelle coinvolte nella query potrebbero influire sulle prestazioni della query.
Se il valore Shuffle output bytes (Byte di output di shuffling) per una fase è elevato rispetto alle esecuzioni precedenti della query o rispetto a una fase precedente, valuta i passaggi elaborati in quella fase per verificare se qualcuno crea quantità di dati impreviste di grandi dimensioni. Una causa comune è quando un passaggio elabora un INNER JOIN in cui sono presenti chiavi duplicate su entrambi i lati del join. In questo modo è possibile restituire una quantità di dati imprevista di grandi dimensioni.
Utilizza il grafico di esecuzione per esaminare le fasi principali in base alla durata e all'elaborazione. Considera la quantità di dati prodotti e se è proporzionale alle dimensioni delle tabelle a cui viene fatto riferimento nella query. In caso contrario, esamina i passaggi in queste fasi per verificare se qualcuno di essi potrebbe produrre una quantità imprevista di dati intermedi.

Passaggi successivi

Consulta le linee guida per l'ottimizzazione delle query per suggerimenti su come migliorare le prestazioni delle query.