Estatísticas de consultas ativas mais antigas

Consultas ativas mais antigas, também conhecidas como consultas mais longas, é uma lista das 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 por 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 podem ser limitados a um subconjunto de consultas totais devido às restrições de memória que o sistema impõe 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). Recupere informações das duas tabelas integradas usando instruções SQL.

Neste artigo, descreveremos as duas tabelas, mostraremos alguns exemplos de consulta que usam essas tabelas e, finalmente, demonstraremos 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 são compatíveis com 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, os resultados poderão ser limitados a um subconjunto do total de consultas devido às restrições de memória que o Spanner impõe na coleta desses dados. Para ver as 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. A exclusão do ID da sessão cancelará a consulta.

Exemplo de consultas

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

Como listar 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 que estão sendo executadas atualmente.
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 instruções SQL de exemplo a seguir usando as bibliotecas de cliente, a gcloud spanner ou o Console do Google Cloud.

Como recuperar um resumo das 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.

Como usar dados de consultas ativas para resolver problemas de alta utilização da CPU

As estatísticas de consulta e de transações fornecem informações úteis ao solucionar problemas de latência em um banco de dados do Spanner. Essas ferramentas fornecem informações sobre as consultas que já foram concluídas. No entanto, às vezes é necessário saber o que está em execução no sistema. Por exemplo, considere o cenário quando o uso da CPU for muito alto e você quiser 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 uma tarefa periódica em segundo plano.
  • Identifique o usuário ou componente que emite a consulta que pode não estar autorizado a executá-la.

Neste tutorial, analisamos nossas consultas ativas e determinamos qual ação será realizada, se for o caso.

Como recuperar um resumo das consultas ativas no momento

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

No momento, temos uma consulta em execução por mais de 100 segundos. Isso é incomum para nosso banco de dados, então queremos investigar mais a fundo.

Como 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
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 5;

Neste exemplo, executamos a consulta no sábado, 18 de julho de 2020, aproximadamente às 16h54 e produziu os resultados abaixo. Talvez seja necessário rolar a tela horizontalmente para visualizar toda a saída.

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; False 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

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

Como cancelar uma consulta cara

Encontramos uma consulta que estava executando um CROSS JOIN caro, por isso decidimos cancelar a consulta. Os resultados da consulta na etapa anterior incluíam um session_id, que é o ID da sessão que executa a consulta. Assim, é possível executar o comando gcloud spanner databases sessions delete a seguir para excluir a sessão usando esse ID, que, por sua vez, cancela a consulta.

gcloud spanner databases sessions delete\
   ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw \
    --database=singer_db --instance=test-instance

Este tutorial demonstra como usar SPANNER_SYS.OLDEST_ACTIVE_QUERIES e SPANNER_SYS.ACTIVE_QUERIES_SUMMARY para analisar nossas consultas em execução e agir, se necessário, em todas as consultas que contribuem 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