Statistiche sulle query attive meno recenti

Le query attive meno recenti, note anche come query in esecuzione da più tempo, sono un elenco di query attive nel database, ordinate in base al tempo di esecuzione. L'analisi di queste query può aiutare a identificare le cause della latenza del sistema e dell'utilizzo elevato della CPU man mano che si verificano.

Spanner fornisce una tabella integrata,SPANNER_SYS.OLDEST_ACTIVE_QUERIES, che elenca le query in esecuzione, incluse quelle contenenti istruzioni DML, ordinate in base all'ora di inizio, in ordine crescente. Non include le query sui flussi di variazioni.

Se è in esecuzione un numero elevato di query, i risultati potrebbero essere limitati a un sottoinsieme delle query totali a causa dei vincoli di memoria applicati dal sistema alla raccolta di questi dati. Pertanto, Spanner fornisce un'altra tabella, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, che mostra statistiche di riepilogo per tutte le query attive (tranne le query sugli stream di modifiche). Puoi recuperare informazioni da entrambe queste tabelle predefinite utilizzando le istruzioni SQL.

In questo documento descriveremo entrambe le tabelle, mostreremo alcune query di esempio che le utilizzano e, infine, illustreremo come utilizzarle per contribuire ad attenuare i problemi causati dalle query attive.

Disponibilità

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

• Pagina Spanner Studio di un database nella console Google Cloud

• Il comando gcloud spanner databases execute-sql

• L'API executeQuery

Gli altri metodi di lettura singola forniti da Spanner non supportanoSPANNER_SYS.

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES restituisce un elenco di query attive ordinate in base all'ora di inizio. Se è in esecuzione un numero elevato di query, i risultati potrebbero essere limitati a un sottoinsieme di query totali a causa dei vincoli di memoria applicati da Spanner alla raccolta di questi dati. Per visualizzare le statistiche di riepilogo di tutte le query attive, consulta ACTIVE_QUERIES_SUMMARY.

Schema tabella

Nome colonna	Tipo	Descrizione
START_TIME	TIMESTAMP	Ora di inizio della query.
TEXT_FINGERPRINT	INT64	L'impronta è un hash delle operazioni coinvolte nella transazione.
TEXT	STRING	Il testo dell'istruzione query.
TEXT_TRUNCATED	BOOL	True se il testo della query nel campo TEXT è troncato; altrimenti, false.
SESSION_ID	STRING	L'ID della sessione che esegue la query. Viene utilizzato per l'osservabilità.
QUERY_ID.	STRING	L'ID della query. Utilizza questo ID con CALL cancel_query(query_id) per annullare la query.

Esempi di query

Puoi eseguire i seguenti esempi di istruzioni SQL utilizzando le librerie client, Google Cloud CLI o la console Google Cloud.

Elenca le query in esecuzione meno recenti

La seguente query restituisce un elenco delle query in esecuzione più vecchie ordinate in base al tempo di inizio della query.

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC;

start_time	text_fingerprint	testo	text_truncated	session_id
2020-07-18T07:52:28.225877Z	-3426560921851907385	SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b;	Falso	ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z	-9206690983832919848	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000;	Falso	ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ
2020-07-18T07:54:08.631744Z	-105437553161169030	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE a.SingerId > 7 LIMIT 1000000;	Falso	ACjbPvanq3MesDNT98t64KdKAz3TlDZoCC-zgW-FJn91cJHuczQ_cOFN_Hdflw
2020-07-18T07:54:08.720011Z	-9206690983832919848	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000;	Falso	ACjbPvYIE2QHkhnmMXuAGpB4inK7yMnQjmYgQ9FoygKNaB5KCXu7Sf7f9aghYw
2020-07-18T07:54:08.731006Z	6561582859583559006	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE a.SingerId > 10 LIMIT 1000000;	Falso	ACjbPvYNZ06N2YyvwV0YMlSRBNDtXBqZEK-iAAyPFnFcTmshPvzWkhr034ud7w

Elenco delle due query in esecuzione meno recenti

Una leggera variazione della query precedente, questo esempio restituisce le due query in esecuzione più vecchie ordinate in base all'ora di inizio della query.

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 2;

Output della query

start_time	text_fingerprint	testo	text_truncated	session_id
2020-07-18T07:52:28.225877Z	-3426560921851907385	SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b;	Falso	ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z	-9206690983832919848	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000;	Falso	ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ

ACTIVE_QUERIES_SUMMARY

Come suggerisce il nome, la tabella SPANNER_SYS.ACTIVE_QUERIES_SUMMARY integrata mostra le statistiche di riepilogo per tutte le query attive. Come mostrato nello schema seguente, le query vengono raggruppate in base all'età in tre bucket o contatori: più di un secondo, più di 10 secondi e più di 100 secondi.

Schema tabella

Nome colonna	Tipo	Descrizione
ACTIVE_COUNT	INT64	Il numero totale di query in esecuzione.
OLDEST_START_TIME	TIMESTAMP	Un limite superiore per l'ora di inizio della query in esecuzione più vecchia.
COUNT_OLDER_THAN_1S	INT64	Il numero di query precedenti a 1 secondo.
COUNT_OLDER_THAN_10S	INT64	Il numero di query precedenti a 10 secondi.
COUNT_OLDER_THAN_100S	INT64	Il numero di query precedenti a 100 secondi.

Una query può essere conteggiata in più di uno di questi bucket. Ad esempio, se una query è in esecuzione da 12 secondi, verrà conteggiata in COUNT_OLDER_THAN_1S e COUNT_OLDER_THAN_10S perché soddisfa entrambi i criteri.

Esempi di query

Puoi eseguire i seguenti esempi di istruzioni SQL utilizzando le librerie client, gcloud spanner o la console Google Cloud.

Recuperare un riepilogo delle query attive

La seguente query restituisce le statistiche di riepilogo relative alle query in esecuzione.

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

Output della query

active_count	oldest_start_time	count_older_than_1s	count_older_than_10s	count_older_than_100s
22	2020-07-18T07:52:28.225877Z	21	21	1

Limitazioni

L'obiettivo è fornire le informazioni più complete possibili, ma esistono alcune circostanze in cui le query non sono incluse nei dati restituiti in queste tabelle.

• Le query DML (UPDATE/INSERT/DELETE) non sono incluse se si trovano nella fase di applicazione delle mutazioni.

• Una query non viene inclusa se è in corso di riavvio a causa di un errore temporaneo.

• Le query provenienti da server sovraccaricati o non rispondenti non sono incluse.

• OLDEST_ACTIVE_QUERIES non può essere utilizzato in una transazione di lettura/scrittura. Anche in una transazione di sola lettura, ignora il timestamp della transazione e restituisce sempre i dati aggiornati a partire dalla sua esecuzione. In rari casi, potrebbe restituire un errore ABORTED con risultati parziali. In questo caso, ignora i risultati parziali e riprova a eseguire la query.

Utilizzare i dati delle query attive per risolvere i problemi di utilizzo elevato della CPU

Le statistiche sulle query e le statistiche sulle transazioni forniscono informazioni utili per la risoluzione dei problemi di latenza in un database Spanner. Questi strumenti forniscono informazioni sulle query già completate. Tuttavia, a volte è necessario sapere cosa è in esecuzione nel sistema. Ad esempio, prendi in considerazione lo scenario in cui l'utilizzo della CPU è piuttosto elevato e vuoi rispondere alle seguenti domande.

• Quante query sono in esecuzione al momento?
• Che cosa sono queste query?
• Quante query sono in esecuzione per un periodo di tempo prolungato, ovvero più di 100 secondi?
• In quale sessione viene eseguita la query?

In base alle risposte alle domande precedenti, puoi decidere di intraprendere la seguente azione.

• Elimina la sessione che esegue la query per una risoluzione immediata.
• Migliora le prestazioni delle query aggiungendo un indice.
• Riduci la frequenza della query se è associata a un'attività in background periodica.
• Identifica l'utente o il componente che ha emesso la query che potrebbe non essere autorizzato a eseguire la query.

In questa procedura dettagliata, esamineremo le query attive e determineremo eventuali provvedimenti da adottare.

Recuperare un riepilogo delle query attive

Nel nostro scenario di esempio, notiamo un utilizzo della CPU superiore al normale, quindi decidiamo di eseguire la seguente query per restituire un riepilogo delle query attive.

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

La query restituisce i seguenti risultati.

active_count	oldest_start_time	count_older_than_1s	count_older_than_10s	count_older_than_100s
22	2020-07-18T07:52:28.225877Z	21	21	1

Abbiamo rilevato una query in esecuzione da più di 100 secondi. Questo è insolito per il nostro database, quindi vogliamo effettuare ulteriori accertamenti.

Recuperare un elenco di query attive

Nel passaggio precedente abbiamo stabilito che è in esecuzione una query da oltre 100 secondi.Per effettuare ulteriori accertamenti, eseguiamo la seguente query per restituire ulteriori informazioni sulle 5 query in esecuzione più vecchie.

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id,
       query_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 5;

In questo esempio, abbiamo eseguito la query il 28 marzo 2024 alle ore 16:44:09 circa EDT e abbiamo ottenuto i seguenti risultati. Potrebbe essere necessario scorrere la pagina in senso orizzontale per visualizzare l'intero output.

start_time	text_fingerprint	testo	text_truncated	session_id	query_id
28/03/2024 16:44:09.356939+00:00	-2833175298673875968	select * from spanner_sys.oldest_active_queries	falso	ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw	37190103859320827
2020-07-18T07:52:28.225877Z	-3426560921851907385	SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b;	falso	ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ	48946620525959556

La query più vecchia (fingerprint = -2833175298673875968) è evidenziata nella tabella. È un CROSS JOIN costoso. Decidiamo di intervenire.

Annullare una query dispendiosa

In questo esempio, abbiamo trovato una query che eseguiva un'operazione CROSS JOIN di costo elevato, pertanto abbiamo deciso di annullarla. I risultati della query che abbiamo ricevuto nel passaggio precedente includevano un query_id. Per annullare la query, possiamo eseguire il seguente comando CALL cancel_query(query_id) per GoogleSQL e il comando spanner.cancel_query(query_id) per PostgreSQL.

GoogleSQL

CALL cancel_query(query_id)

PostgreSQL

CALL spanner.cancel_query(query_id)

Ad esempio, nel seguente, l'istruzione CALL annulla una query con ID 37190103859320827:

CALL cancel_query('37190103859320827')

Nota: in Spanner, l'operazione CALL supporta solo la chiamata della procedura cancel_query. Per saperne di più sulle stored procedure, consulta la pagina Stored procedure (GoogleSQL o PostgreSQL).

Devi eseguire una query sulla tabella spanner_sys.oldest_active_queries per verificare che la query sia annullata.

Nota: CALL cancel_query è un'operazione secondo il criterio del massimo impegno. Potrebbe non annullare una query quando i server Spanner sono occupati a causa di carichi di query elevati. In questo caso, prova a eseguire CALL cancel_query una seconda volta.

Questa procedura dettagliata mostra come utilizzare SPANNER_SYS.OLDEST_ACTIVE_QUERIES e SPANNER_SYS.ACTIVE_QUERIES_SUMMARY per analizzare le query in esecuzione e intervenire, se necessario, su eventuali query che contribuiscono a un elevato utilizzo della CPU. Ovviamente, è sempre più economico evitare operazioni costose e progettare lo schema giusto per i tuoi casi d'uso. Per saperne di più sulla creazione di istruzioni SQL che vengono eseguite in modo efficiente, consulta le best practice per SQL.

Passaggi successivi

• Scopri altri strumenti di introspezione.
• Scopri altre informazioni archiviate da Spanner per ogni database nelle tabelle dello schema delle informazioni del database.
• Scopri di più sulle best practice per SQL per Spanner.