Estatísticas das consultas ativas mais antigas

Consultas ativas mais antigas, também conhecidas como consultas mais longas, é uma lista de consultas ativas no seu banco de dados, classificadas pelo tempo em que estavam em execução. Conseguir informações sobre essas consultas pode ajudar a identificar as causas de latência do sistema e do alto uso da CPU conforme elas acontecem.

O Spanner fornece uma tabela integrada, SPANNER_SYS.OLDEST_ACTIVE_QUERIES, que lista as consultas em execução, incluindo aquelas que contêm instruções DML, classificadas pelo horário de início, em ordem crescente. Ela não inclui consultas de fluxo de alterações.

Se houver um grande número de consultas em execução, os resultados poderão ser Limitado a um subconjunto do total de consultas devido às restrições de memória que o sistema aplica na coleta desses dados. Portanto, o Spanner fornece uma tabela extra, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, que mostra estatísticas resumidas de todas as consultas ativas (exceto para consultas de fluxo de alterações). É possível recuperar informações dessas duas tabelas integradas usando instruções SQL.

Neste documento, vamos descrever as duas tabelas, mostrar alguns exemplos de consulta que usam essas tabelas e, por fim, demonstrar como usá-las para ajudar a mitigar problemas causados por consultas ativas.

Disponibilidade

Os dados do SPANNER_SYS estão disponíveis somente por meio de interfaces SQL. Por exemplo:

Outros métodos de leitura única fornecidos pelo Spanner não têm suporte SPANNER_SYS:

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES retorna uma lista de consultas ativas classificadas pelo horário de início. Se houver um grande número de consultas em execução, o os resultados podem ser limitados a um subconjunto do total de consultas devido ao uso restrições que o Spanner aplica na coleta desses dados. Para visualizar estatísticas resumidas de todas as consultas ativas, consulte ACTIVE_QUERIES_SUMMARY

Esquema de tabela

Nome da coluna Tipo Descrição
START_TIME TIMESTAMP Horário 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 estiver truncado. Caso contrário, falso.
SESSION_ID STRING O ID da sessão que está executando a consulta. Isso é usado para observabilidade.
QUERY_ID STRING O ID da consulta. Use esse ID com CALL cancel_query(query_id) para cancelar a consulta.

Exemplo de consultas

É possível executar os exemplos de instruções SQL usando as bibliotecas de cliente, a CLI do Google Cloud ou o Console do Google Cloud.

Listar as consultas em execução mais antigas

A consulta a seguir retorna uma lista de consultas em execução mais antigas, classificadas pelo horário 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 text 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

Como listar as duas consultas em execução mais antigas

Uma pequena variação na consulta anterior, este exemplo retorna as duas consultas em execução mais antigas classificadas pelo horário 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;
Saída da consulta
start_time text_fingerprint text 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 próprio nome sugere, a tabela integrada, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, mostra estatísticas resumidas de todas as consultas ativas. Conforme mostrado no esquema a seguir, as consultas são agrupadas por idade em três buckets ou contadores: mais de um segundo, mais de 10 segundos e mais de 100 segundos.

Esquema de 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 no horário 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

É possível contar uma consulta em mais de um desses buckets. Por exemplo, se uma consulta estiver em execução por 12 segundos, ela será contada em COUNT_OLDER_THAN_1S e COUNT_OLDER_THAN_10S porque atende aos dois critérios.

Exemplo de consultas

É possível executar as seguintes instruções SQL usando as bibliotecas de cliente, o gcloud spanner ou o Console do Google Cloud.

Recuperar um resumo de consultas ativas

A consulta a seguir retorna as estatísticas resumidas da execução de consultas.

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;
Saída 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 fornecer os insights mais abrangentes possíveis, há algumas circunstâncias em que as consultas não estão incluídas nos dados retornados nessas tabelas.

  • As consultas DML (ATUALIZAR/INSERIR/EXCLUIR) não são incluídas se estiverem na fase Apply mutations.

  • Uma consulta não será incluída se estiver no meio da reinicialização devido a um erro transitório.

  • As consultas de servidores sobrecarregados ou que não respondem não são incluídas.

  • OLDEST_ACTIVE_QUERIES não pode ser usado em uma transação de leitura e gravação. Mesmo em uma transação somente leitura, ele ignora o carimbo de data/hora da transação e sempre retorna dados atuais a partir da execução. Em casos raros, ele pode retornar um erro ABORTED com resultados parciais. Nesse caso, descarte os resultados parciais e tente a consulta novamente.

Usar dados de consultas ativas para solucionar problemas de alta utilização da CPU

Estatísticas de consulta e estatísticas de transações oferecem informações úteis para solucionar problemas de latência em um banco de dados do Spanner. Essas ferramentas fornecem informações consultas já concluídas. No entanto, às vezes é necessário saber o que está sendo executado no sistema. Por exemplo, considere o cenário em que a utilização da CPU é muito alta e você quer responder às perguntas a seguir.

  • Quantas consultas estão em execução no momento?
  • O que são essas consultas?
  • Quantas consultas estão em execução por muito tempo, ou seja, mais de 100 segundos?
  • Qual sessão está executando a consulta?

Com respostas às perguntas anteriores, você pode optar pela ação a seguir.

  • Exclua a sessão que executa a consulta para uma resolução imediata.
  • Melhore o desempenho da consulta adicionando um índice.
  • Reduza a frequência da consulta se ela estiver associada a um em segundo plano.
  • Identifique o usuário ou componente que emite a consulta que pode não estar autorizado a executar a consulta.

Neste tutorial, examinamos nossas consultas ativas e determinamos qual ação tomar, se houver.

Recuperar um resumo de consultas ativas

No nosso cenário de exemplo, notamos um uso maior do que o uso normal da CPU. Por isso, decidimos executar a consulta a seguir para retornar 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 apresenta os resultados a seguir.

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

Acontece que uma consulta está em execução para mais de 100 segundos. Isso é incomum para nosso banco de dados, então queremos investigar mais a fundo.

Recuperar uma lista de consultas ativas

Decidimos na etapa anterior que temos uma consulta em execução por mais de 100 segundos. Para investigar mais a fundo, executamos a seguinte consulta para retornar mais informações sobre as cinco principais consultas em execução.

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, executamos a consulta em 28 de março de 2024, aproximadamente às 16h44min09s EDT, e ela retornou os resultados abaixo. Talvez seja necessário rolar a tela horizontalmente para ver toda a saída.

start_time text_fingerprint text 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

A consulta mais antiga (impressão digital = -2833175298673875968) é destacada na tabela. É um CROSS JOIN caro. Decidimos tomar medidas.

Cancelar uma consulta cara

Neste exemplo, encontramos uma consulta que estava executando um CROSS JOIN caro. Por isso, decidimos cancelar a consulta. Os resultados da consulta que recebemos na etapa anterior incluíam um query_id. Podemos executar o seguinte o comando CALL cancel_query(query_id) para GoogleSQL e o spanner.cancel_query(query_id) para PostgreSQL para para cancelar a consulta.

GoogleSQL 

CALL cancel_query(query_id)

PostgreSQL 

CALL spanner.cancel_query(query_id)

Por exemplo, a seguir, a instrução CALL cancela uma consulta com o ID 37190103859320827:

CALL cancel_query('37190103859320827')

É necessário consultar a tabela spanner_sys.oldest_active_queries para verificar se a consulta foi cancelada.

Este tutorial demonstra como usar SPANNER_SYS.OLDEST_ACTIVE_QUERIES e SPANNER_SYS.ACTIVE_QUERIES_SUMMARY para analisar as consultas em execução e tomar as medidas necessárias em qualquer consulta que contribua para o alto uso da CPU. Obviamente, é sempre mais barato evitar operações caras e desenvolver o esquema certo para seus casos de uso. Para mais informações sobre como construir instruções SQL que são executadas com eficiência, consulte Práticas recomendadas de SQL.

A seguir