Estatísticas das consultas ativas mais antigas

As consultas ativas mais antigas, também conhecidas como consultas de execução mais longa, são uma lista de consultas ativas na sua base de dados, ordenadas pela duração da execução. A obtenção de estatísticas sobre estas consultas pode ajudar a identificar as causas da latência do sistema e da utilização elevada da CPU à medida que ocorrem.

O Spanner fornece uma tabela incorporada,SPANNER_SYS.OLDEST_ACTIVE_QUERIES, que apresenta as consultas em execução, incluindo consultas que contêm declarações DML, ordenadas por hora de início, por ordem ascendente. Não inclui consultas de streams de alterações.

Se estiver a executar um grande número de consultas, os resultados podem ser limitados a um subconjunto do total de consultas devido às restrições de memória que o sistema aplica à recolha destes dados. Por conseguinte, o Spanner fornece uma tabela adicional, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, que mostra estatísticas de resumo para todas as consultas ativas (exceto para consultas de fluxo de alterações). Pode obter informações de ambas as tabelas incorporadas através de declarações SQL.

Neste documento, vamos descrever ambas as tabelas, mostrar alguns exemplos de consultas que usam estas tabelas e, por último, demonstrar como as usar para ajudar a mitigar problemas causados por consultas ativas.

Disponibilidade

Os dados de SPANNER_SYS só estão disponíveis através de interfaces SQL, por exemplo:

Outros métodos de leitura única fornecidos pelo Spanner não são compatíveis com SPANNER_SYS.

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES devolve uma lista de consultas ativas ordenadas por hora de início. Se estiver a executar um grande número de consultas, os resultados podem estar limitados a um subconjunto do total de consultas devido às restrições de memória que o Spanner aplica à recolha destes dados. Para ver estatísticas de resumo de todas as consultas ativas, consulte ACTIVE_QUERIES_SUMMARY.

Esquema da tabela

Nome da coluna Tipo Descrição
START_TIME TIMESTAMP Hora de início da consulta.
TEXT_FINGERPRINT INT64 A impressão digital é um hash das operações envolvidas na transação.
TEXT STRING O texto da instrução de consulta.
TEXT_TRUNCATED BOOL Verdadeiro se o texto da consulta no campo TEXT for truncado. Caso contrário, falso.
SESSION_ID STRING O ID da sessão que está a executar a consulta. Isto é usado para observabilidade.
QUERY_ID. STRING O ID da consulta. Usa este ID com CALL cancel_query(query_id) para cancelar a consulta.

Consultas de exemplo

Pode executar as seguintes declarações SQL de exemplo através das bibliotecas de cliente, da Google Cloud CLI ou da Google Cloud consola.

Liste as consultas em execução mais antigas

A consulta seguinte devolve uma lista das consultas em execução mais antigas, ordenadas pela hora de início da consulta.

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 texto 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

Apresentar as 2 consultas em execução mais antigas

Uma ligeira variação da consulta anterior, este exemplo devolve as 2 consultas em execução mais antigas ordenadas pela hora de início da consulta.

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 2;
Resultado da consulta
start_time text_fingerprint texto 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

Como o nome sugere, a tabela incorporada, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, mostra estatísticas de resumo para todas as consultas ativas. Conforme mostrado no esquema seguinte, as consultas são agrupadas por idade em três contentores ou contadores: mais de um segundo, mais de 10 segundos e mais de 100 segundos.

Esquema da tabela

Nome da coluna Tipo Descrição
ACTIVE_COUNT INT64 O número total de consultas em execução.
OLDEST_START_TIME TIMESTAMP Um limite superior para a hora de início da consulta em execução mais antiga.
COUNT_OLDER_THAN_1S INT64 O número de consultas com mais de 1 segundo.
COUNT_OLDER_THAN_10S INT64 O número de consultas com mais de 10 segundos.
COUNT_OLDER_THAN_100S INT64 O número de consultas com mais de 100 segundos.

Uma consulta pode ser contabilizada em mais do que um destes grupos. Por exemplo, se uma consulta estiver a ser executada durante 12 segundos, é contabilizada em COUNT_OLDER_THAN_1S e COUNT_OLDER_THAN_10S porque cumpre ambos os critérios.

Consultas de exemplo

Pode executar as seguintes declarações SQL de exemplo através das bibliotecas de cliente, do gcloud spanner ou da Google Cloud consola.

Obtenha um resumo das consultas ativas

A consulta seguinte devolve as estatísticas de resumo sobre as consultas em execução.

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;
Resultado da consulta
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

Limitações

Embora o objetivo seja oferecer-lhe as estatísticas mais abrangentes possíveis, existem algumas circunstâncias em que as consultas não são incluídas nos dados devolvidos nestas tabelas.

  • As consultas DML (UPDATE/INSERT/DELETE) não são incluídas se estiverem na fase Aplicar mutações.

  • Uma consulta não é incluída se estiver a meio do reinício devido a um erro temporário.

  • As consultas de servidores sobrecarregados ou sem resposta não estão incluídas.

  • Não é possível usar OLDEST_ACTIVE_QUERIES numa transação de leitura/escrita. Mesmo numa transação de leitura, ignora a data/hora da transação e devolve sempre os dados atuais a partir da respetiva execução. Em casos raros, pode devolver um erro com resultados parciais. Nesse caso, rejeite os resultados parciais e tente a consulta novamente.ABORTED

Use dados de consultas ativas para resolver problemas de elevada utilização da CPU

As estatísticas de consultas e as estatísticas de transações fornecem informações úteis quando resolve problemas de latência numa base de dados do Spanner. Estas ferramentas fornecem informações sobre as consultas que já foram concluídas. No entanto, por vezes, é necessário saber o que está a ser executado no sistema. Por exemplo, considere o cenário em que a utilização da CPU é bastante elevada e quer responder às seguintes perguntas.

  • Quantas consultas estão a ser executadas neste momento?
  • O que são estas consultas?
  • Quantas consultas estão a ser executadas durante muito tempo, ou seja, mais de 100 segundos?
  • Que sessão está a executar a consulta?

Com as respostas às perguntas anteriores, pode decidir tomar a seguinte ação.

  • Eliminar a sessão que executa a consulta para uma resolução imediata.
  • Melhore o desempenho das consultas adicionando um índice.
  • Reduza a frequência da consulta se estiver associada a uma tarefa em segundo plano periódica.
  • Identificar o utilizador ou o componente que está a emitir a consulta e que pode não estar autorizado a executá-la.

Neste passo a passo, examinamos as nossas consultas ativas e determinamos que ação, se aplicável, tomar.

Obtenha um resumo das consultas ativas

No nosso cenário de exemplo, verificamos uma utilização da CPU superior ao normal, pelo que decidimos executar a seguinte consulta para devolver um resumo das consultas ativas.

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

A consulta gera os seguintes resultados.

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

Descobrimos que temos uma consulta em execução há mais de 100 segundos. Isto é invulgar para a nossa base de dados, por isso, queremos investigar mais aprofundadamente.

Obtenha uma lista de consultas ativas

Determinámos no passo anterior que temos uma consulta em execução há mais de 100 segundos.Para investigar mais a fundo, executamos a seguinte consulta para devolver mais informações sobre as 5 consultas em execução mais antigas.

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;

Neste exemplo, executámos a consulta a 28 de março de 2024, aproximadamente às 16:44:09 EDT, e devolveu os seguintes resultados. (Pode ter de deslocar a página horizontalmente para ver o resultado completo.)

start_time text_fingerprint texto text_truncated session_id query_id
2024-03-28 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

A consulta mais antiga (impressão digital = -2833175298673875968) está realçada na tabela. É um CROSS JOIN caro. Decidimos tomar medidas.

Cancele uma consulta dispendiosa

Neste exemplo, encontrámos uma consulta que estava a executar uma CROSS JOIN dispendiosa, pelo que decidimos cancelá-la. Os resultados da consulta que recebemos no passo anterior incluíram um query_id. Podemos executar o seguinte comando CALL cancel_query(query_id) para o GoogleSQL e o comando spanner.cancel_query(query_id) para o PostgreSQL para cancelar a consulta.

GoogleSQL 

CALL cancel_query(query_id)

PostgreSQL 

CALL spanner.cancel_query(query_id)

Por exemplo, na seguinte declaração, a declaração CALL cancela uma consulta com o ID 37190103859320827:

CALL cancel_query('37190103859320827')

Tem de consultar a spanner_sys.oldest_active_queriestabela para verificar se a consulta foi cancelada.

Este passo a passo demonstra como usar SPANNER_SYS.OLDEST_ACTIVE_QUERIES e SPANNER_SYS.ACTIVE_QUERIES_SUMMARY para analisar as nossas consultas em execução e tomar medidas, se necessário, em quaisquer consultas que estejam a contribuir para uma elevada utilização da CPU. Claro que é sempre mais barato evitar operações dispendiosas e criar o esquema certo para os seus exemplos de utilização. Para mais informações sobre a criação de declarações SQL executadas de forma eficiente, consulte as práticas recomendadas de SQL.

O que se segue?