Statistiche sulle query

Spanner fornisce tabelle integrate che conservano molte statistiche per il query e istruzioni DML che hanno utilizzato la maggior parte della CPU e tutte le query in forma aggregata (incluse le query stream di modifiche).

Disponibilità

I dati di SPANNER_SYS sono disponibili solo tramite le interfacce SQL. Ad esempio:

Gli altri metodi di lettura singoli forniti da Spanner non supportano SPANNER_SYS.

Utilizzo CPU raggruppato per query

Le seguenti tabelle tengono traccia delle query con il maggiore utilizzo di CPU durante un specifico per un periodo di tempo:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE: query a intervalli di 1 minuto
  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE: query a intervalli di 10 minuti
  • SPANNER_SYS.QUERY_STATS_TOP_HOUR: query durante intervalli di 1 ora

Queste tabelle hanno le seguenti proprietà:

  • Ogni tabella contiene dati per intervalli di tempo non sovrapposti della durata del nome di una tabella specifica.

  • Gli intervalli sono basati sugli orari dell'orologio. Gli intervalli di 1 minuto terminano ogni minuto, 10 gli intervalli di minuti terminano ogni 10 minuti a partire dall'ora e ogni ora che terminano dopo un'ora.

    Ad esempio, alle 11:59:30, gli intervalli più recenti disponibili per SQL le query sono:

    • 1 minuto: 11:58:00-11:58:59
    • 10 minuti: 11:40:00-11:49:59
    • 1 ora: dalle 10:00:00 alle 10:59:59 AM

  • Spanner raggruppa le statistiche in base al testo della query SQL. Se una query utilizza i parametri di query, Spanner raggruppa tutte le esecuzioni di quella query in una singola riga. Se la query utilizza stringhe letterali, Spanner raggruppa le statistiche solo se il testo completo della query è identico; se il testo è diverso, ogni query viene visualizzata come riga separata. Per DML in modalità batch, Spanner normalizza il batch in base deduplicare le istruzioni identiche consecutive prima di generare impronta.

  • Se è presente un tag richiesta, FPRINT è l'hash del tag richiesta. In caso contrario, si tratta dell'hash del valore TEXT.

  • Ogni riga contiene le statistiche di tutte le esecuzioni di una determinata query SQL per la quale Spanner acquisisce le statistiche durante l'intervallo specificato.

  • Se Spanner non è in grado di archiviare tutte le query eseguite durante l'intervallo, il sistema assegna la priorità alle query con il massimo utilizzo di CPU durante il periodo specificato intervallo di tempo.

  • Le query monitorate includono quelle che sono state completate, non sono andate a buon fine o sono state annullate dal utente.

  • Un sottoinsieme di statistiche è specifico per le query eseguite, ma non completate:

    • Conteggio delle esecuzioni e latenza media in secondi per tutte le query non è riuscito.

    • Conteggio delle esecuzioni per le query scadute.

    • Conteggio delle esecuzioni per le query annullate dall'utente o non riuscite a causa di problemi di connettività di rete.

  • Tutte le colonne delle tabelle sono nulli.

Schema tabella

Nome colonna Tipo Descrizione
INTERVAL_END TIMESTAMP Fine dell'intervallo di tempo in cui si sono verificate le esecuzioni delle query incluse.
REQUEST_TAG STRING Il tag di richiesta facoltativo per questa operazione di query. Per ulteriori informazioni sull'uso dei tag, consulta Risoluzione dei problemi relativi ai tag di richiesta.
QUERY_TYPE STRING Indica se una query è PARTITIONED_QUERY o QUERY. Un PARTITIONED_QUERY è una query con un token partizione ottenuto dall'API PartitionQuery. Tutte le altre query e istruzioni DML sono indicate dal QUERY tipo di query. Le statistiche sulle query per DML partizionato non sono supportate.
TEXT STRING Testo della query SQL troncato a circa 64 kB.

Statistiche per più query che hanno la stessa stringa tag sono raggruppati in una singola riga con i valori REQUEST_TAG corrispondenti la stringa tag. Solo il testo di una di queste query viene visualizzato in questo campo, troncato a circa 64 kB. Per la DML batch, l'insieme di istruzioni SQL viene appiattito in una singola riga, concatenata utilizzando un delimitatore di punto e virgola. SQL identico consecutivo vengono deduplicati prima del troncamento.
TEXT_TRUNCATED BOOL Indica se il testo della query è stato troncato o meno.
TEXT_FINGERPRINT INT64 L'hash del valore REQUEST_TAG, se presente; Altrimenti, l'hash del valore TEXT.
EXECUTION_COUNT INT64 Numero di volte in cui Spanner ha visto la query durante l'intervallo.
AVG_LATENCY_SECONDS FLOAT64 Il tempo medio, in secondi, per ogni esecuzione di query all'interno del per configurare un database. Questa media esclude il tempo di codifica e trasmissione per il il set di risultati e l'overhead.
AVG_ROWS FLOAT64 Numero medio di righe restituite dalla query.
AVG_BYTES FLOAT64 Numero medio di byte di dati restituiti dalla query, esclusi l'overhead della codifica della trasmissione.
AVG_ROWS_SCANNED FLOAT64 Numero medio di righe analizzate dalla query, esclusi i valori eliminati.
AVG_CPU_SECONDS FLOAT64 Numero medio di secondi di tempo di CPU impiegato da Spanner su tutti operazioni per eseguire la query.
ALL_FAILED_EXECUTION_COUNT INT64 Numero di volte in cui la query non è riuscita durante l'intervallo.
ALL_FAILED_AVG_LATENCY_SECONDS FLOAT64 Durata media, in secondi, di ogni esecuzione della query non riuscita all'interno del database. Questa media esclude il tempo di codifica e trasmissione del set di risultati, nonché il sovraccarico.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Numero di volte in cui la query è stata annullata dall'utente o non è riuscita a causa di la connessione di rete si interrompe durante l'intervallo di tempo.
TIMED_OUT_EXECUTION_COUNT INT64 Numero di volte in cui la query è scaduta durante l'intervallo.
AVG_BYTES_WRITTEN FLOAT64 Numero medio di byte scritti dall'istruzione.
AVG_ROWS_WRITTEN FLOAT64 Numero medio di righe modificate dall'istruzione.
STATEMENT_COUNT INT64 La somma delle affermazioni aggregate in questa voce. Per regolari e DML, equivale al conteggio delle esecuzioni. Per DML in batch, Spanner acquisisce il numero di istruzioni nel batch.
RUN_IN_RW_TRANSACTION_EXECUTION_COUNT INT64 Il numero di volte in cui la query è stata eseguita nell'ambito di una transazione di lettura-scrittura. Questa colonna consente di determinare se è possibile evitare i conflitti di blocco spostando a una transazione di sola lettura.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Un istogramma del tempo di esecuzione della query. I valori sono misurate in secondi.

L'array contiene un solo elemento e presenta il seguente tipo:
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>
Per ulteriori informazioni sui valori, consulta Distribuzione.

Per calcolare la latenza percentile dalla distribuzione, usa la funzione SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), che restituisce l'ennesimo percentile stimato. Per un esempio correlato, consulta Trovare la latenza del 99° percentile per le query.

Per saperne di più, consulta Percentile e metriche con valori di distribuzione.

AVG_MEMORY_PEAK_USAGE_BYTES FLOAT64

Durante l'esecuzione di una query distribuita, il picco medio di utilizzo di memoria (in byte).

Utilizza questa statistica per identificare le dimensioni delle query o dei dati delle tabelle che hanno maggiori probabilità di incontrare limiti di memoria.
AVG_MEMORY_USAGE_PERCENTAGE FLOAT64

Durante l'esecuzione di una query distribuita, l'utilizzo medio della memoria richiesto (come percentuale del limite di memoria consentito per questa query).

Questa statistica traccia solo la memoria necessaria affinché la query eseguire il deployment. Alcuni operatori utilizzano memoria di buffering aggiuntiva per migliorare il rendimento. La memoria di buffering aggiuntiva utilizzata è visibile nella query piano, ma non viene utilizzato per calcolare AVG_MEMORY_USAGE_PERCENTAGE poiché la memoria di buffering viene utilizzata per l'ottimizzazione e non è obbligatoria.

Usa questa statistica per identificare le query che si avvicinano alla memoria limite di utilizzo e rischiano di non funzionare se la dimensione dei dati aumenta. A per ridurre il rischio che la query non vada a buon fine, consulta Best practice SQL per ottimizzare queste query o suddividere la query in parti con meno dati di lettura.
AVG_QUERY_PLAN_CREATION_TIME_SECS FLOAT64

Il tempo medio della CPU in secondi impiegato per la compilazione delle query, inclusa la creazione del runtime delle query.

Se il valore di questa colonna è elevato, utilizza le query con parametri.
AVG_FILESYSTEM_DELAY_SECS FLOAT64

Il tempo medio impiegato dalla query per leggere dal file system o essere bloccati su input/output (I/O).

Utilizza questa statistica per identificare la potenziale latenza elevata causata dal file all'I/O di sistema. Per ridurre, aggiungi un indice oppure aggiungi una clausola STORING (GoogleSQL) o INCLUDE (PostgreSQL) a un indice esistente.
AVG_REMOTE_SERVER_CALLS FLOAT64

Il numero medio di chiamate al server remoto (RPC) completate dalla query.

Utilizza questa statistica per identificare se query diverse analizzano lo stesso di righe ha un numero molto diverso di RPC. La query con un un valore RPC più elevato può trarre vantaggio dall'aggiunta di un indice o l'aggiunta di un Clausola STORING (GoogleSQL) o INCLUDE (PostgreSQL) a un indice esistente.
AVG_ROWS_SPOOLED FLOAT64

Il numero medio di righe scritte su un disco temporaneo (non in memoria) dall'istruzione della query.

Utilizza questa statistica per identificare le query potenzialmente ad alta latenza che richiedono molte risorse per la memoria e non possono essere eseguiti in memoria. Per attenuare il problema, modifica l'ordine di JOIN o aggiungi un indice che fornisca un SORT obbligatorio.

EXECUTION_COUNT, AVG_LATENCY_SECONDS e LATENCY_DISTRIBUTION per non riusciti includono query non riuscite a causa di una sintassi errata o che hanno riscontrato errore temporaneo ma il nuovo tentativo è riuscito.

Statistiche aggregate

Esistono anche tabelle che tengono traccia dei dati aggregati per tutte le query per cui Spanner ha acquisito le statistiche in un periodo di tempo specifico:

  • SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: query durante intervalli di 1 minuto
  • SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: query a intervalli di 10 minuti
  • SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: query a intervalli di 1 ora

Queste tabelle hanno le seguenti proprietà:

  • Ogni tabella contiene dati per intervalli di tempo non sovrapposti della durata del nome di una tabella specifica.

  • Gli intervalli sono basati sugli orari dell'orologio. Gli intervalli di 1 minuto terminano ogni minuto, 10 gli intervalli di minuti terminano ogni 10 minuti a partire dall'ora e ogni ora che terminano dopo un'ora.

    Ad esempio, alle 11:59:30, gli intervalli più recenti disponibili per SQL le query sono:

    • 1 minuto: 11:58:00-11:58:59
    • 10 minuti: 11:40:00-11:49:59
    • 1 ora: 10:00:00–10:59:59

  • Ogni riga contiene le statistiche per tutte le query eseguite sul database durante l'intervallo specificato, aggregati. È presente una sola riga per dell'intervallo di tempo e include query completate, query non riuscite e query annullato dall'utente.

  • Le statistiche acquisite nelle tabelle TOTAL potrebbero includere query che Spanner non ha acquisito nelle tabelle TOP.

  • Alcune colonne di queste tabelle sono esposte come metriche in Cloud Monitoring. Le metriche esposte sono:

    • Conteggio esecuzioni query
    • Errori di query
    • Latenze di query
    • Conteggio delle righe restituito
    • Conteggio righe scansionate
    • Conteggio byte restituiti
    • Tempo di CPU per la query

    Per ulteriori informazioni, consulta Metriche di Spanner.

Schema tabella

Nome colonna Tipo Descrizione
INTERVAL_END TIMESTAMP Fine dell'intervallo di tempo in cui si sono verificate le esecuzioni delle query incluse.
EXECUTION_COUNT INT64 Numero di volte in cui Spanner ha visto la query durante l'intervallo di tempo in questione.
AVG_LATENCY_SECONDS FLOAT64 Il tempo medio, in secondi, per ogni esecuzione di query all'interno del per configurare un database. Questa media esclude il tempo di codifica e trasmissione per il il set di risultati e l'overhead.
AVG_ROWS FLOAT64 Numero medio di righe restituite dalla query.
AVG_BYTES FLOAT64 Numero medio di byte di dati restituiti dalla query, esclusi l'overhead della codifica della trasmissione.
AVG_ROWS_SCANNED FLOAT64 Numero medio di righe analizzate dalla query, esclusi i valori eliminati.
AVG_CPU_SECONDS FLOAT64 Numero medio di secondi di tempo di CPU impiegato da Spanner su tutti operazioni per eseguire la query.
ALL_FAILED_EXECUTION_COUNT INT64 Numero di volte in cui la query non è riuscita durante l'intervallo.
ALL_FAILED_AVG_LATENCY_SECONDS FLOAT64 Durata media, in secondi, di ogni esecuzione della query non riuscita all'interno del database. Questa media esclude il tempo di codifica e trasmissione del set di risultati, nonché il sovraccarico.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Numero di volte in cui la query è stata annullata dall'utente o non è riuscita a causa di la connessione di rete si interrompe durante l'intervallo di tempo.
TIMED_OUT_EXECUTION_COUNT INT64 Numero di volte in cui la query è scaduta durante l'intervallo.
AVG_BYTES_WRITTEN FLOAT64 Numero medio di byte scritti dall'istruzione.
AVG_ROWS_WRITTEN FLOAT64 Numero medio di righe modificate dall'istruzione.
RUN_IN_RW_TRANSACTION_EXECUTION_COUNT INT64 Il numero di volte in cui le query sono state eseguite nell'ambito di transazioni di lettura/scrittura. Questa colonna consente di determinare se è possibile evitare i conflitti di blocco spostando alcune query alle transazioni di sola lettura.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Un istogramma del tempo di esecuzione tra le query. I valori vengono misurati in secondi.

Specifica l'array come segue:
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>
Per ulteriori informazioni sui valori, consulta Distribuzione.

Per calcolare la latenza percentile dalla distribuzione, usa la funzione SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), che restituisce l'ennesimo percentile stimato. Per un esempio correlato, consulta Trova la latenza del 99° percentile per le query.

Per saperne di più, consulta Percentile e metriche con valori di distribuzione.

Conservazione dei dati

Spanner conserva i dati di ogni tabella come minimo per il periodo di tempo seguente periodi:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: intervalli che coprono i 6 precedenti nell'orario lavorativo locale del TAM.

  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: intervalli che coprono i 4 precedenti giorni.

  • SPANNER_SYS.QUERY_STATS_TOP_HOUR e SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: intervalli che coprono i 30 giorni precedenti.

Esempi di query

Questa sezione include diverse istruzioni SQL di esempio che recuperano la query statistiche. Puoi eseguire queste istruzioni SQL utilizzando librerie client, Google Cloud CLI, oppure Console Google Cloud.

Elenca le statistiche di base per ogni query in un determinato periodo di tempo

La seguente query restituisce i dati non elaborati per le query principali della query minuto:

SELECT text,
       request_tag,
       interval_end,
       execution_count,
       avg_latency_seconds,
       avg_rows,
       avg_bytes,
       avg_rows_scanned,
       avg_cpu_seconds
FROM spanner_sys.query_stats_top_minute
ORDER BY interval_end DESC;

Elenca le query con il maggiore utilizzo di CPU

La seguente query restituisce le query con il maggiore utilizzo di CPU nel ora precedente:

SELECT text,
       request_tag,
       execution_count AS count,
       avg_latency_seconds AS latency,
       avg_cpu_seconds AS cpu,
       execution_count * avg_cpu_seconds AS total_cpu
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY total_cpu DESC;

Trovare il conteggio totale delle esecuzioni in un determinato periodo di tempo

La seguente query restituisce il numero totale di query eseguite nell' intervallo completo recente di 1 minuto:

SELECT interval_end,
       execution_count
FROM spanner_sys.query_stats_total_minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_minute);

Trovare la latenza media per una query

La seguente query restituisce le informazioni sulla latenza media per una query:

SELECT avg_latency_seconds
FROM spanner_sys.query_stats_top_hour
WHERE text LIKE "SELECT x FROM table WHERE x=@foo;";

Trovare la latenza al 99° percentile per le query

La seguente query restituisce il 99° percentile di tempo di esecuzione tra le query eseguiti nei 10 minuti precedenti:

SELECT interval_end, avg_latency_seconds, SPANNER_SYS.DISTRIBUTION_PERCENTILE(latency_distribution[OFFSET(0)], 99.0)
  AS percentile_latency
FROM spanner_sys.query_stats_total_10minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_total_10minute)
ORDER BY interval_end;

Il confronto tra la latenza media e la latenza al 99° percentile consente identificare possibili query outlier con tempi di esecuzione elevati.

Trova le query che analizzano la maggior parte dei dati

Puoi utilizzare il numero di righe analizzate da una query come misura della quantità ai dati analizzati dalla query. La seguente query restituisce il numero di righe analizzate dalle query eseguite nell'ora precedente:

SELECT text,
       execution_count,
       avg_rows_scanned
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY avg_rows_scanned DESC;

Trovare le istruzioni che hanno scritto più dati

Puoi utilizzare il numero di righe scritte (o byte scritti) da DML come misura la quantità di dati modificati dalla query. La seguente query restituisce numero di righe scritte da istruzioni DML eseguite nell'ora precedente:

SELECT text,
       execution_count,
       avg_rows_written
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY avg_rows_written DESC;

Utilizzo totale della CPU in tutte le query

La seguente query restituisce il numero di ore CPU utilizzate nell'ora precedente:

SELECT (avg_cpu_seconds * execution_count / 60 / 60)
  AS total_cpu_hours
FROM spanner_sys.query_stats_total_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_total_hour);

Elenca le query non riuscite in un determinato periodo di tempo

La seguente query restituisce i dati non elaborati, inclusi il conteggio delle esecuzioni e la latenza media delle query non riuscite per le query principali nel minuto precedente:

SELECT text,
       request_tag,
       interval_end,
       execution_count,
       all_failed_execution_count,
       all_failed_avg_latency_seconds,
       avg_latency_seconds,
       avg_rows,
       avg_bytes,
       avg_rows_scanned,
       avg_cpu_seconds
FROM spanner_sys.query_stats_top_minute
WHERE all_failed_execution_count > 0
ORDER BY interval_end;

Trovare il conteggio totale degli errori in un determinato periodo di tempo

La seguente query restituisce il numero totale di query che non è stato possibile eseguire nel l'intervallo completo di 1 minuto più recente.

SELECT interval_end,
       all_failed_execution_count
FROM spanner_sys.query_stats_total_minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_minute)
ORDER BY interval_end;

Elenca le query che hanno raggiunto il timeout più spesso

La seguente query restituisce le query con il conteggio dei timeout più elevato nell'ora precedente.

SELECT text,
       execution_count AS count,
       timed_out_execution_count AS timeout_count,
       avg_latency_seconds AS latency,
       avg_cpu_seconds AS cpu,
       execution_count * avg_cpu_seconds AS total_cpu
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY timed_out_execution_count DESC;

Trovare la latenza media delle esecuzioni riuscite e non riuscite per una query

La seguente query restituisce la latenza media combinata, la latenza media per le esecuzioni riuscite e la latenza media per le esecuzioni non riuscite per una query specifica.

SELECT avg_latency_seconds AS combined_avg_latency,
       all_failed_avg_latency_seconds AS failed_execution_latency,
       ( avg_latency_seconds * execution_count -
         all_failed_avg_latency_seconds * all_failed_execution_count
       ) / (
       execution_count - all_failed_execution_count ) AS success_execution_latency
FROM   spanner_sys.query_stats_top_hour
WHERE  text LIKE "select x from table where x=@foo;";

Risolvi i problemi di utilizzo elevato della CPU o latenza elevata delle query con le statistiche delle query

Le statistiche sulle query sono utili quando devi analizzare l'utilizzo elevato della CPU sui tuoi o quando vuoi semplicemente capire il funzionamento forme di query complicate sul database. Ispezione delle query che utilizzano di risorse di database offre agli utenti di Spanner un modo potenziale per ridurre i costi operativi ed eventualmente migliorare le latenze generali del sistema.

Puoi utilizzare il codice SQL o Query Insights per esaminare le query problematiche nel tuo database. I seguenti argomenti mostrano come esaminare queste query utilizzando il codice SQL.

Sebbene l'esempio seguente sia incentrato sull'utilizzo della CPU, è possibile seguire passaggi simili per risolvere i problemi di latenza elevata e trovare le query con e la latenza minima. È sufficiente selezionare intervalli di tempo e query in base alla latenza anziché alla CPU all'utilizzo delle risorse.

Seleziona un periodo di tempo per l'indagine

Inizia la tua indagine cercando il momento in cui l'applicazione ha iniziato di utilizzare CPU elevato. Ad esempio, se il problema ha iniziato a verificarsi all'incirca alle 17:00 del 24 luglio 2020 UTC.

Raccogli le statistiche sulle query per il periodo di tempo selezionato

Dopo aver selezionato un periodo di tempo per l'inizio dell'indagine, esamineremo statistiche raccolte nella tabella QUERY_STATS_TOTAL_10MINUTE nello stesso periodo. I risultati di questa query potrebbero indicare il modo in cui CPU e altre query statistiche cambiate nel corso di quel periodo.

La seguente query restituisce le statistiche aggregate delle query dalle 16:30 alle 17:30 UTC, incluse. Utilizziamo ROUND nella nostra query per limitare il numero di cifre decimali a fini di visualizzazione.

SELECT interval_end,
       execution_count AS count,
       ROUND(avg_latency_seconds,2) AS latency,
       ROUND(avg_rows,2) AS rows_returned,
       ROUND(avg_bytes,2) AS bytes,
       ROUND(avg_rows_scanned,2) AS rows_scanned,
       ROUND(avg_cpu_seconds,3) AS avg_cpu
FROM spanner_sys.query_stats_total_10minute
WHERE
  interval_end >= "2020-07-24T16:30:00Z"
  AND interval_end <= "2020-07-24T17:30:00Z"
ORDER BY interval_end;

L'esecuzione della query ha prodotto i seguenti risultati.

interval_end conteggio latenza rows_returned byte rows_scanned avg_cpu
2020-07-24T16:30:00Z 6 0,06 5,00 536,00 16,67 0,035
2020-07-24T16:40:00Z 55 0,02 0,22 25,29 0,22 0,004
2020-07-24T16:50:00Z 102 0,02 0,30 33,35 0,30 0,004
2020-07-24T17:00:00Z 154 1.06 4.42 486.33 7792208.12 4.633
2020-07-24T17:10:00Z 94 0,02 1,68 106,84 1,68 0,006
2020-07-24T17:20:00Z 110 0,02 0,38 34,60 0,38 0,005
2020-07-24T17:30:00Z 47 0,02 0,23 24,96 0,23 0,004

Nella tabella precedente vediamo che il tempo di CPU medio, la colonna avg_cpu in la tabella dei risultati, il valore più alto negli intervalli evidenziati che terminano alle 17:00. Me riscontrerai anche un numero molto più elevato di righe scansionate in media. Ciò indica che le query più costose sono state eseguite tra le 16:50 e le 17:00. Scegli questo intervallo per approfondire nel passaggio successivo.

Trova le query che causano un utilizzo elevato della CPU

Ora che è stato selezionato un intervallo di tempo per l'indagine, eseguiamo una query Tabella QUERY_STATS_TOP_10MINUTE. I risultati di questa query possono essere utili per indicare quali query causano un elevato utilizzo della CPU.

SELECT text_fingerprint AS fingerprint,
       execution_count AS count,
       ROUND(avg_latency_seconds,2) AS latency,
       ROUND(avg_cpu_seconds,3) AS cpu,
       ROUND(execution_count * avg_cpu_seconds,3) AS total_cpu
FROM spanner_sys.query_stats_top_10MINUTE
WHERE
  interval_end = "2020-07-24T17:00:00Z"
ORDER BY total_cpu DESC;

L'esecuzione di questa query restituisce i seguenti risultati.

impronta conteggio latenza cpu total_cpu
5505124206529314852 30 3.88 17.635 529.039
1697951036096498470 10 4.49 18.388 183.882
2295109096748351518 1 0,33 0,048 0,048
11618299167612903606 1 0,25 0,021 0,021
10302798842433860499 1 0,04 0,006 0,006
123771704548746223 1 0,04 0,006 0,006
4216063638051261350 1 0,04 0,006 0,006
3654744714919476398 1 0,04 0,006 0,006
2999453161628434990 1 0,04 0,006 0,006
823179738756093706 1 0,02 0,005 0,0056

Le due query principali, evidenziate nella tabella dei risultati, sono outlier in termini di: di CPU e latenza medie, nonché di numero di esecuzioni e CPU totale. Esamina la prima query elencata in questi risultati.

Confronta le esecuzioni delle query nel tempo

Avendo ristretto l'indagine, possiamo rivolgere la nostra attenzione alle Tabella QUERY_STATS_TOP_MINUTE. Confrontando le esecuzioni nel tempo per un una query, possiamo cercare correlazioni tra il numero di righe o byte o il numero di righe analizzate e CPU o latenza elevata. Una deviazione potrebbe indicare la non uniformità dei dati. Numero di righe costantemente elevato scansionati può indicare la mancanza di indici appropriati o di un join non ottimale ordinazione.

Esamina la query con l'utilizzo medio della CPU e la latenza più elevati eseguendo la seguente istruzione che filtra in base al valore text_fingerprint della query.

SELECT interval_end,
       ROUND(avg_latency_seconds,2) AS latency,
       avg_rows AS rows_returned,
       avg_bytes AS bytes_returned,
       avg_rows_scanned AS rows_scanned,
       ROUND(avg_cpu_seconds,3) AS cpu,
FROM spanner_sys.query_stats_top_minute
WHERE text_fingerprint = 5505124206529314852
ORDER BY interval_end DESC;

L'esecuzione di questa query restituisce i seguenti risultati.

interval_end latenza rows_returned bytes_returned rows_scanned cpu
2020-07-24T17:00:00Z 4,55 21 2365 30000000 19,255
2020-07-24T16:00:00Z 3,62 21 2365 30000000 17,255
2020-07-24T15:00:00Z 4,37 21 2365 30000000 18,350
2020-07-24T14:00:00Z 4,02 21 2365 30000000 17,748
2020-07-24T13:00:00Z 3,12 21 2365 30000000 16,380
2020-07-24T12:00:00Z 3,45 21 2365 30000000 15,476
2020-07-24T11:00:00Z 4,94 21 2365 30000000 22,611
2020-07-24T10:00:00Z 6,48 21 2365 30000000 21,265
2020-07-24T09:00:00Z 0,23 21 2365 5 0,040
2020-07-24T08:00:00Z 0,04 21 2365 5 0,021
2020-07-24T07:00:00Z 0,09 21 2365 5 0,030

Esaminando i risultati precedenti, vediamo che il numero di righe sottoposte a scansione, la CPU impiegata e la latenza sono cambiati in modo significativo intorno alle 9:00. Per capire perché questi numeri sono aumentati così drasticamente, esamineremo il testo della query e verificheremo se eventuali modifiche allo schema potrebbero aver influito sulla query.

Usa la seguente query per recuperare il testo della query che stiamo indagando.

SELECT text,
       text_truncated
FROM spanner_sys.query_stats_top_hour
WHERE text_fingerprint = 5505124206529314852
LIMIT 1;

Viene restituito il seguente risultato.

testo text_truncated
select * from orders where o_custkey = 36901; falso

Esaminando il testo della query che viene restituito, vediamo che la query filtrando in base a un campo chiamato o_custkey. Questa è una colonna non chiave della tabellaorders. In precedenza, in quella colonna c'era un indice che è sceso intorno alle 09:00. Questo spiega la variazione del costo per questa query. Possiamo aggiungere l'indice o, se la query viene eseguita raramente, decidi di non avere indicizzare e accettare il costo di lettura più elevato.

Finora abbiamo concentrato la nostra indagine sulle query che sono state completate correttamente e abbiamo è stato trovato un motivo per cui le prestazioni del database hanno subito un peggioramento. Nel passaggio successivo, ci concentreremo sulle query non riuscite o annullate e illustreremo come esaminare questi dati per ottenere ulteriori approfondimenti.

Esamina le query non riuscite

Le query che non vengono completate correttamente consumano comunque risorse prima di scadono, vengono annullati o altrimenti non vanno a buon fine. Spanner monitora numero di esecuzioni e risorse consumate da query non riuscite insieme a query quelli.

Per verificare se le query non riuscite contribuiscono in modo significativo al sistema di utilizzo, possiamo prima verificare quante query non sono riuscite nell'intervallo di tempo interesse.

SELECT interval_end,
       all_failed_execution_count AS failed_count,
       all_failed_avg_latency_seconds AS latency
FROM spanner_sys.query_stats_total_minute
WHERE
  interval_end >= "2020-07-24T16:50:00Z"
  AND interval_end <= "2020-07-24T17:00:00Z"
ORDER BY interval_end;
interval_end failed_count latenza
2020-07-24T16:52:00Z 1 15,211391
2020-07-24T16:53:00Z 3 58,312232

Approfondendo ulteriormente l'analisi, possiamo cercare le query che hanno maggiori probabilità di utilizzando la seguente query.

SELECT interval_end,
       text_fingerprint,
       execution_count,
       avg_latency_seconds AS avg_latency,
       all_failed_execution_count AS failed_count,
       all_failed_avg_latency_seconds AS failed_latency,
       cancelled_or_disconnected_execution_count AS cancel_count,
       timed_out_execution_count AS to_count
FROM spanner_sys.query_stats_top_minute
WHERE all_failed_execution_count > 0
ORDER BY interval_end;
interval_end text_fingerprint execution_count failed_count cancel_count to_count
2020-07-24T16:52:00Z 5505124206529314852 3 1 1 0
2020-07-24T16:53:00Z 1697951036096498470 2 1 1 0
2020-07-24T16:53:00Z 5505124206529314852 5 2 1 1

Come mostra la tabella precedente, la query con impronta 5505124206529314852 non ha funzionato più volte in diversi intervalli di tempo. Dato uno schema di errori come questo, è interessante confrontare la latenza non riuscite e non riuscite.

SELECT interval_end,
       avg_latency_seconds AS combined_avg_latency,
       all_failed_avg_latency_seconds AS failed_execution_latency,
       ( avg_latency_seconds * execution_count -
         all_failed_avg_latency_seconds * all_failed_execution_count
       ) / (
       execution_count - all_failed_execution_count ) AS success_execution_latency
FROM   spanner_sys.query_stats_top_hour
WHERE  text_fingerprint = 5505124206529314852;
interval_end combined_avg_latency failed_execution_latency success_execution_latency
2020-07-24T17:00:00Z 3,880420 13,830709 2,774832

Applica le best practice

Dopo aver identificato una query candidata per l'ottimizzazione, possiamo ora esaminare profilo di query e prova a ottimizzarlo usando le best practice per SQL.

Passaggi successivi