Ottenere informazioni sul rendimento delle query

Il grafico di esecuzione di una query è una rappresentazione visiva dei passaggi che BigQuery esegue per eseguire la query. Questo documento descrive come utilizzare il grafico di esecuzione delle query per diagnosticare i problemi di prestazioni delle query e per visualizzare gli approfondimenti sulle prestazioni delle query.

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 tue query vengono eseguite più lentamente del previsto o rispetto alle esecuzioni precedenti, capire cosa è successo può essere difficile.

Il grafico di esecuzione della query fornisce un'interfaccia grafica dinamica per l'ispezione del piano di query e dei dettagli sulle prestazioni della 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 sul rendimento 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 Identity and Access Management (IAM) predefiniti di BigQuery:

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

Struttura del grafico di esecuzione

Il grafico di esecuzione della query fornisce una visualizzazione grafica del piano della query nella console. Ogni casella rappresenta una fase del piano di query come le seguenti:

  • Input: lettura dei dati da una tabella o selezione di colonne specifiche
  • Unione: unione dei dati di due tabelle in base alla condizione JOIN
  • Aggregazione: esecuzione di calcoli come SUM
  • Ordina: ordinamento dei risultati

Le fasi sono costituite da passaggi che descrivono le singole operazioni eseguite da ogni worker all'interno di una fase. Puoi fare clic su una fase per aprirla e visualizzarne i passaggi. Le tappe includono anche informazioni sul timing relativo e assoluto. I nomi delle fasi riassumono i passaggi eseguiti. Ad esempio, una fase con join nel nome indica che il passaggio principale della fase è un'operazione JOIN. I nomi delle fasi che terminano con + indicano che vengono eseguiti ulteriori passaggi importanti. Ad esempio, una fase con JOIN+ nel nome significa che esegue un'operazione di unione 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 shuffle 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 shuffle viene superata, la memoria di shuffle può essere riversata sul disco e rallentare drasticamente le prestazioni della query.

Visualizzare gli approfondimenti sul rendimento delle query

Console

Per visualizzare gli approfondimenti sul rendimento delle query:

  1. Apri la pagina BigQuery nella console Google Cloud .

    Vai alla pagina BigQuery

  2. Nell'Editor, fai clic su Cronologia personale o Cronologia del progetto.

  3. Nell'elenco dei job, individua il job di query che ti interessa. Fai clic su Azioni e scegli Apri query nell'editor.

  4. Seleziona la scheda Grafico di esecuzione per visualizzare una rappresentazione grafica di ogni fase della query:

    Il piano di query grafico nel grafico di esecuzione.

    Per determinare se una fase della query ha informazioni sul rendimento, osserva l'icona che viene visualizzata. Le fasi con un'icona informativa hanno approfondimenti sul rendimento. Le fasi con un'icona di spunta non lo sono.

  5. Fai clic su una fase per aprire il riquadro dei dettagli della fase, in cui puoi visualizzare le seguenti informazioni:

    Dettagli della fase della query.

  6. (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.

    Sincronizza il grafico con una query in esecuzione.

  7. (Facoltativo) Per evidenziare le fasi principali in base alla durata della fase nel grafico, fai clic su Evidenzia le fasi principali per durata.

    Mostra le fasi principali per durata.

  8. (Facoltativo) Per evidenziare le fasi principali in base al tempo di slot utilizzato nel grafico, fai clic su Evidenzia le fasi principali per elaborazione.

    Mostra le fasi principali per elaborazione.

  9. (Facoltativo) Per includere le fasi di ridistribuzione dello shuffling nel grafico, fai clic su Mostra le fasi di ridistribuzione dello shuffling.

    Mostra le fasi principali per elaborazione.

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

    Le fasi di ripartizione 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, vengono nascoste per semplificare il piano di query visualizzato.

Per qualsiasi query che presenta problemi di regressione delle prestazioni, gli approfondimenti sul rendimento vengono visualizzati anche nella scheda Informazioni sul job per la query:

La scheda Informazioni sul lavoro.

SQL

  1. Nella console Google Cloud , vai alla pagina BigQuery.

    Vai a BigQuery

  2. 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
    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
  );

  3. Fai clic su Esegui.

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

API

Puoi ottenere approfondimenti sul rendimento delle query in un formato non grafico chiamando il metodo API jobs.list ed esaminando le informazioni JobStatistics2 restituite.

Interpretare gli approfondimenti sul rendimento delle query

Utilizza questa sezione per scoprire di più sul significato delle informazioni sul rendimento e su come risolverle.

Gli approfondimenti sul rendimento sono destinati a due tipi di pubblico:

  • Analisti: eseguono query in un progetto. Ti interessa scoprire perché una query che hai eseguito in precedenza viene eseguita in modo inaspettatamente più lento e ricevere suggerimenti su come migliorare il rendimento di una query. Disponi delle autorizzazioni descritte in Autorizzazioni richieste.

  • Amministratori di data lake o data warehouse: gestisci le risorse e le prenotazioni BigQuery della tua organizzazione. Disponi delle autorizzazioni associate al ruolo Amministratore BigQuery.

Ciascuna delle seguenti sezioni fornisce indicazioni su cosa puoi fare per risolvere un approfondimento sul rendimento 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 in una fase e da cui viene generato un output. Un singolo slot preleva un'attività ed esegue quella porzione di dati per la fase. Idealmente, gli slot di BigQuery eseguono queste attività in parallelo per ottenere prestazioni elevate. La contesa degli slot si verifica quando la query ha molte attività pronte per l'esecuzione, ma BigQuery non riesce a ottenere slot disponibili sufficienti per eseguirle.

Cosa fare se sei un analista

Riduci i dati che stai elaborando 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 adottando le seguenti azioni:

  • Se utilizzi i prezzi on-demand di BigQuery, le tue query utilizzano un pool condiviso di slot. Valuta la possibilità di passare ai prezzi dell'analisi basati sulla capacità acquistando prenotazioni. Le prenotazioni ti consentono di riservare slot dedicati per le query della tua organizzazione.

  • Se utilizzi le prenotazioni BigQuery, assicurati che ci siano slot sufficienti nella prenotazione assegnata al progetto che eseguiva la query. 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 amministrative per vedere come la tua organizzazione utilizza la prenotazione.
    • La prenotazione non dispone di 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 quella 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 query siano in cluster. Il clustering consente di garantire che BigQuery possa leggere rapidamente le colonne con dati correlati.

  • Assicurati che le tabelle su cui esegui query siano partizionate. Per le tabelle non partizionate, BigQuery legge l'intera tabella. Il partizionamento delle tabelle ti consente di eseguire query solo sul sottoinsieme delle tabelle che ti interessano.

Quota di shuffling insufficiente

Prima di eseguire la query, BigQuery divide 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, memorizza i risultati intermedi in shuffle. Le fasi successive della query leggono i dati dal rimescolamento per continuare l'esecuzione della query. La quota di shuffling insufficiente si verifica quando hai più dati da scrivere in shuffle rispetto alla capacità di shuffle disponibile.

Cosa fare se sei un analista

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

Determinate operazioni in SQL tendono a fare un uso più esteso di shuffle, in particolare le operazioni JOIN e le clausole GROUP BY. Se possibile, la riduzione della quantità di dati in queste operazioni potrebbe ridurre l'utilizzo di shuffle.

Cosa fare se sei un amministratore

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

  • Analogamente alla contesa degli slot, se utilizzi i prezzi on demand di BigQuery, le tue query utilizzano un pool di slot condiviso. Valuta la possibilità di passare ai prezzi dell'analisi basati sulla capacità acquistando 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 in modo intensivo lo shuffle, ciò potrebbe causare una capacità di shuffle insufficiente per altre query eseguite in parallelo. Puoi identificare i job che utilizzano in modo intensivo la capacità di shuffling eseguendo query sulla colonna period_shuffle_ram_usage_ratio nella visualizzazione INFORMATION_SCHEMA.JOBS_TIMELINE.

    Per risolvere il problema, puoi provare una o più delle seguenti soluzioni:

    • Aggiungi altri posti alla prenotazione.
    • Crea una prenotazione aggiuntiva e assegnala al progetto che esegue la query.
    • Distribuisci le query che richiedono un'intensa attività di shuffling nel tempo all'interno di una prenotazione o in prenotazioni diverse.

Modifica della scala di input dei dati

Questo insight sul rendimento indica che la query legge almeno il 50% di dati in più per una determinata tabella di input rispetto all'ultima volta che è stata eseguita. 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 che stai elaborando 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 di quelle di una delle tabelle di input. Questo approfondimento indica che il rapporto tra le righe di output e quelle di input è elevato e fornisce informazioni su questi conteggi delle 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 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 saperne di più, consulta Ridurre i dati prima di utilizzare un JOIN.

Distorsione della partizione

Per fornire un feedback o richiedere assistenza per questa funzionalità, invia un'email a [email protected].

La distribuzione distorta dei dati può causare l'esecuzione lenta delle query. Quando viene eseguita una query, BigQuery suddivide i dati in piccole partizioni. Non puoi condividere partizioni tra slot. Pertanto, se i dati sono distribuiti in modo non uniforme, alcune partizioni diventano molto grandi, il che causa l'arresto anomalo dello slot che elabora la partizione sovradimensionata.

Lo sbilanciamento si verifica in JOIN fasi. Quando esegui un'operazione JOIN, BigQuery suddivide i dati sul lato destro e sinistro dell'operazione JOIN in partizioni. Se una partizione è troppo grande, i dati vengono ribilanciati dalle fasi di ripartizione. Se lo sbilanciamento è troppo grave e BigQuery non può ribilanciare ulteriormente, alla fase "JOIN" viene aggiunto un insight sullo sbilanciamento delle partizioni. Questo processo è noto come fasi di ripartizione. Se BigQuery rileva partizioni di grandi dimensioni che non possono essere suddivise ulteriormente, alla fase JOIN viene aggiunto un approfondimento sul disallineamento delle partizioni.

Cosa fare se sei un analista

Per evitare lo sbilanciamento delle partizioni, filtra i dati il prima possibile.

Interpretare le informazioni sulla fase della query

Oltre a utilizzare le informazioni sul rendimento delle query, puoi anche utilizzare le seguenti linee guida quando esamini i dettagli della fase di query per determinare se si è verificato un problema con una query:

  • Se il valore di Attesa ms per una o più fasi è elevato rispetto alle esecuzioni precedenti della query:
    • Verifica di avere slot sufficienti per ospitare il tuo carico di lavoro. In caso contrario, bilancia il carico quando esegui query che richiedono molte risorse in modo che non siano in concorrenza tra loro.
    • Se il valore Attesa ms è superiore a quello di una sola fase, esamina la fase precedente per verificare se è stato introdotto un collo di bottiglia in quella fase. Modifiche sostanziali ai dati o allo schema delle tabelle coinvolte nella query potrebbero influire sulle prestazioni della query.
  • Se il valore di Byte di output di rimescolamento 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 inaspettatamente grandi. 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, può essere restituita una quantità di dati inaspettatamente elevata.
  • Utilizza il grafico di esecuzione per esaminare le fasi principali in base a durata ed elaborazione. Considera la quantità di dati che producono e se è proporzionata alle dimensioni delle tabelle a cui viene fatto riferimento nella query. In caso contrario, esamina i passaggi di queste fasi per verificare se qualcuno di questi potrebbe produrre una quantità imprevista di dati intermedi.

Passaggi successivi