Statistiche sulle query

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

Disponibilità

I dati di SPANNER_SYS sono disponibili solo tramite le interfacce SQL; ad esempio:

Altri metodi di lettura singola forniti da Spanner non supportano SPANNER_SYS.

Utilizzo della CPU raggruppato per query

Le seguenti tabelle monitorano le query con il massimo utilizzo della CPU durante un periodo di tempo specifico:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE: query durante intervalli di 1 minuto
  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE: query durante 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 lunghezza specificata dal nome della tabella.

  • Gli intervalli sono basati sulle ore di orologio. Gli intervalli di 1 minuto terminano al minuto, gli intervalli di 10 minuti terminano ogni 10 minuti a partire dall'ora e gli intervalli di 1 ora terminano all'ora.

    Ad esempio, alle 11:59:30, gli intervalli più recenti disponibili per le query SQL 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

  • Spanner raggruppa le statistiche in base al testo della query SQL. Se una query utilizza parametri di query, Spanner raggruppa tutte le esecuzioni di tale query in una sola riga. Se la query utilizza valori letterali stringa, 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 deduplicando istruzioni identiche consecutive prima di generare l'impronta digitale.

  • Se è presente un tag di richiesta, Fprint è l'hash del tag di richiesta. Altrimenti, è l'hash del valore TEXT.

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

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

  • Le query monitorate includono quelle completate, non riuscite o annullate dall'utente.

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

    • Numero di esecuzioni e latenza media in secondi per tutte le query non riuscite.

    • Conteggio delle esecuzioni per le query scadute.

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

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 maggiori informazioni sull'utilizzo dei tag, consulta la risoluzione dei problemi con i tag di richiesta.
QUERY_TYPE STRING Indica se una query è PARTITIONED_QUERY o QUERY. Un PARTITIONED_QUERY è una query con partitionToken ottenuto dall'API PartitionQuery. Tutte le altre query e istruzioni DML sono indicate dal tipo di query QUERY. Le statistiche di query per DML partizionato non sono supportate.
TEXT STRING Testo della query SQL, troncato a circa 64 kB.

Le statistiche per più query che hanno la stessa stringa tag vengono raggruppate in una singola riga con il valore REQUEST_TAG corrispondente alla stringa tag. In questo campo viene mostrato solo il testo di una di queste query, troncato a circa 64 kB. Per DML in modalità batch, l'insieme di istruzioni SQL viene suddiviso in una singola riga, concatenato utilizzando un delimitatore del punto e virgola. I testi SQL identici e consecutivi vengono deduplicati prima del troncamento.
TEXT_TRUNCATED BOOL Indica se il testo della query è stato troncato.
TEXT_FINGERPRINT INT64 L'hash del valore REQUEST_TAG, se presente. In caso contrario, l'hash del valore TEXT.
EXECUTION_COUNT INT64 Numero di volte in cui Cloud Spanner ha visualizzato la query durante l'intervallo.
AVG_LATENCY_SECONDS FLOAT64 Durata di tempo media, in secondi, per ogni esecuzione di query all'interno del database. Questa media esclude i tempi di codifica e trasmissione per il set di risultati, nonché l'overhead.
AVG_ROWS FLOAT64 Numero medio di righe restituite dalla query.
AVG_BYTES FLOAT64 Numero medio di byte di dati restituiti dalla query, escluso l'overhead di codifica di 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 utilizzati da Cloud Spanner su tutte le 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 di tempo media, in secondi, per ogni esecuzione di query non riuscita all'interno del database. Questa media esclude i tempi di codifica e trasmissione per il set di risultati, nonché l'overhead.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Numero di volte in cui la query è stata annullata dall'utente o non è andata a buon fine a causa di una connessione di rete interrotta durante l'intervallo.
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 istruzioni aggregate in questa voce. Per le query regolari e DML, equivale al numero delle esecuzioni. Per DML 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 contese del blocco spostando la query in una transazione di sola lettura.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

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

L'array contiene un singolo elemento e ha 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 saperne di più sui valori, consulta Distribuzione.

Per calcolare la latenza del percentile desiderato dalla distribuzione, utilizza la funzione SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), che restituisce il n° percentile stimato. Per un esempio correlato, consulta Trovare la latenza al 99° percentile per le query.

Per ulteriori informazioni, consulta Percentile e metriche con valore di distribuzione.

EXECUTION_COUNT, AVG_LATENCY_SECONDS e LATENCY_DISTRIBUTION per le query non riuscite includono le query che non sono andate a buon fine a causa di una sintassi errata o che hanno riscontrato un errore temporaneo, che però sono riusciti a riprovare.

Statistiche aggregate

Esistono anche tabelle che monitorano i dati aggregati per tutte le query per cui Spanner ha acquisito statistiche in un determinato periodo di tempo:

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

Queste tabelle hanno le seguenti proprietà:

  • Ogni tabella contiene dati per intervalli di tempo non sovrapposti della lunghezza specificata dal nome della tabella.

  • Gli intervalli sono basati sulle ore di orologio. Gli intervalli di 1 minuto terminano al minuto, gli intervalli di 10 minuti terminano ogni 10 minuti a partire dall'ora e gli intervalli di 1 ora terminano all'ora.

    Ad esempio, alle 11:59:30, gli intervalli più recenti disponibili per le query SQL 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 aggregate per tutte le query eseguite sul database durante l'intervallo specificato. Esiste una sola riga per intervallo di tempo e include le query completate, le query non riuscite e quelle annullate 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
    • Numero di righe restituito
    • Conteggio righe scansionate
    • Conteggio byte restituiti
    • Tempo di CPU della query

    Per saperne di più, consulta la sezione 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 Cloud Spanner ha visualizzato la query nell'intervallo di tempo.
AVG_LATENCY_SECONDS FLOAT64 Durata di tempo media, in secondi, per ogni esecuzione di query all'interno del database. Questa media esclude i tempi di codifica e trasmissione per il set di risultati, nonché l'overhead.
AVG_ROWS FLOAT64 Numero medio di righe restituite dalla query.
AVG_BYTES FLOAT64 Numero medio di byte di dati restituiti dalla query, escluso l'overhead di codifica di 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 utilizzati da Cloud Spanner su tutte le 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 di tempo media, in secondi, per ogni esecuzione di query non riuscita all'interno del database. Questa media esclude i tempi di codifica e trasmissione per il set di risultati, nonché l'overhead.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Numero di volte in cui la query è stata annullata dall'utente o non è andata a buon fine a causa di una connessione di rete interrotta durante l'intervallo.
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 contese del blocco spostando alcune query in transazioni di sola lettura.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Un istogramma del tempo di esecuzione delle query. I valori sono 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 saperne di più sui valori, consulta Distribuzione.

Per calcolare la latenza del percentile desiderato dalla distribuzione, utilizza la funzione SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), che restituisce il n° percentile stimato. Per un esempio correlato, consulta Trovare la latenza al 99° percentile per le query.

Per ulteriori informazioni, consulta Percentile e metriche con valore di distribuzione.

Conservazione dei dati

Spanner conserva i dati di ogni tabella come minimo per i seguenti periodi di tempo:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: intervalli relativi alle 6 ore precedenti.

  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: intervalli relativi ai 4 giorni precedenti.

  • SPANNER_SYS.QUERY_STATS_TOP_HOUR e SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: intervalli relativi ai 30 giorni precedenti.

Esempi di query

Questa sezione include diversi esempi di istruzioni SQL che recuperano le statistiche delle query. Puoi eseguire queste istruzioni SQL utilizzando le librerie client, lo strumento a riga di comando gcloud o la 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 del minuto precedente:

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 massimo utilizzo della CPU

La seguente query restituisce le query con il massimo utilizzo di CPU nell'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 di 1 minuto completo più recente:

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 di una query

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

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

Trovare la latenza del 99° percentile per le query

La seguente query restituisce il 99° percentile del tempo di esecuzione nelle query eseguite 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 della latenza media con la latenza del 99° percentile aiuta a identificare possibili query outlier con tempi di esecuzione elevati.

Trovare le query che analizzano la maggior parte dei dati

Puoi utilizzare il numero di righe analizzate da una query come misura della quantità di dati scansionati 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 affermazioni che hanno scritto il maggior numero di dati

Puoi utilizzare il numero di righe scritte (o byte scritte) da DML come misura della quantità di dati modificati dalla query. La seguente query restituisce il numero di righe scritte dalle 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 per 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, tra cui 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 numero totale di errori in un determinato periodo di tempo

La seguente query restituisce il numero totale di query che non è stato possibile eseguire nell'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 con maggiore timeout

La seguente query restituisce le query con il numero di timeout più alto 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 delle query elevata con le statistiche delle query

Le statistiche sulle query sono utili quando hai bisogno di esaminare un utilizzo elevato di CPU nel database Spanner o quando stai semplicemente cercando di comprendere le forme di query più pesanti della CPU sul tuo database. L'ispezione delle query che utilizzano quantità significative di risorse di database offre agli utenti di Spanner un potenziale modo per ridurre i costi operativi e possibilmente migliorare le latenze generali del sistema.

Puoi utilizzare il codice SQL o la dashboard Approfondimenti sulle query per esaminare le query problematiche nel tuo database. I seguenti argomenti mostrano come esaminare tali query utilizzando il codice SQL.

Mentre l'esempio seguente si concentra sull'utilizzo della CPU, è possibile seguire passaggi simili per risolvere i problemi di latenza delle query elevata e trovare le query con latenza più elevata. Basta selezionare intervalli di tempo e query in base alla latenza anziché all'utilizzo della CPU.

Seleziona un periodo di tempo per l'indagine

Inizia la tua indagine cercando il momento in cui l'applicazione ha iniziato a riscontrare un utilizzo elevato della CPU. Ad esempio, supponiamo che il problema si sia verificato intorno 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 in cui iniziare la nostra indagine, esamineremo le statistiche raccolte nella tabella QUERY_STATS_TOTAL_10MINUTE all'incirca in quel periodo. I risultati di questa query potrebbero indicare come sono cambiate le statistiche della CPU e di altre query in quel periodo di tempo.

La seguente query restituisce le statistiche delle query aggregate dalle ore 16:30 alle 17:30 UTC incluse. Utilizziamo ROUND nella nostra query per limitare il numero di cifre decimali ai 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 medio di CPU, ovvero la colonna avg_cpu nella tabella dei risultati, è il più alto negli intervalli evidenziati che terminano alle 17:00. Vediamo anche un numero molto più elevato di righe scansionate in media. Ciò indica che le query più costose venivano eseguite tra le 16:50 e le 17:00. Scegliamo quell'intervallo per esaminare ulteriormente nel passaggio successivo.

Trovare le query che causano un utilizzo elevato della CPU

Con un intervallo di tempo selezionato per esaminare i dati, ora eseguiamo una query sulla tabella QUERY_STATS_TOP_10MINUTE. I risultati di questa query possono aiutare a 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 produce 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 valori anomali in termini di CPU e latenza medie, nonché di numero di esecuzioni e CPU totale. Analizziamo la prima query elencata in questi risultati.

Confronta le esecuzioni delle query nel tempo

Dopo aver limitato l'indagine, possiamo rivolgere la nostra attenzione alla tabella QUERY_STATS_TOP_MINUTE. Confrontando le esecuzioni nel tempo di una determinata query, possiamo cercare correlazioni tra il numero di righe o byte restituiti o il numero di righe analizzate e CPU o latenza elevata. Una deviazione può indicare una non uniformità dei dati. Un numero costantemente elevato di righe analizzate potrebbe indicare la mancanza di indici appropriati o un ordine dei join non ottimale.

Analizziamo la query con l'utilizzo medio della CPU più elevato e la latenza massima eseguendo la seguente istruzione, che filtra in base al 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, notiamo che il numero di righe analizzate, la CPU utilizzata e la latenza sono cambiati in modo significativo intorno alle 9:00. Per capire perché questi numeri sono aumentati in modo significativo, esamineremo il testo della query e verificheremo se eventuali modifiche allo schema potrebbero aver influito sulla query.

Utilizza la seguente query per recuperare il testo della query per la query che stiamo esaminando.

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

Questa operazione restituisce il seguente risultato.

testo text_truncated
select * dagli ordini in cui o_custkey = 36901; false

Esaminando il testo della query che viene restituito, ci rendiamo conto che la query viene filtrata in base a un campo chiamato o_custkey. Questa è una colonna non chiave nella tabella orders. In realtà, in quella colonna c'era un indice che è stato eliminato intorno alle 9:00. Questo spiega la variazione del costo per questa query. Possiamo aggiungere di nuovo l'indice o, se la query viene eseguita di rado, decidere di non includerlo e accettare il costo di lettura più elevato.

La nostra indagine finora si è concentrata sulle query completate correttamente e abbiamo trovato un motivo per cui il database stava riscontrando un peggioramento delle prestazioni. Nel passaggio successivo, ci concentreremo sulle query non riuscite o annullate e mostreremo come esaminare tali dati per ulteriori approfondimenti.

Analizzare le query non riuscite

Le query che non vengono completate correttamente consumano comunque le risorse prima del timeout, dell'annullamento o dell'errore. Spanner tiene traccia del numero di esecuzioni e delle risorse utilizzate dalle query non riuscite, insieme a quelle riuscite.

Per verificare se le query non riuscite contribuiscono in modo significativo all'utilizzo del sistema, possiamo prima controllare quante query non sono riuscite nell'intervallo di tempo di 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

A seguito di ulteriori indagini, possiamo cercare le query che hanno maggiori probabilità di non riuscire utilizzando la query seguente.

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 l'impronta 5505124206529314852 ha avuto esito negativo più volte in diversi intervalli di tempo. Dato un modello di errori come questo, è interessante confrontare la latenza delle esecuzioni 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 esaminare il profilo della query e provare a eseguire l'ottimizzazione utilizzando le best practice SQL.

Passaggi successivi