Estatísticas de consultas

O Spanner fornece tabelas incorporadas que mantêm muitas estatísticas para as consultas e as declarações de linguagem de manipulação de dados (DML) que usaram mais CPU e todas as consultas de forma agregada (incluindo consultas de fluxo de alterações).

Aceda às estatísticas de consultas

O Spanner fornece as estatísticas de consultas no esquema SPANNER_SYS. Pode usar as seguintes formas para aceder aos dados do SPANNER_SYS:

Os seguintes métodos de leitura única fornecidos pelo Spanner não suportam o SPANNER_SYS:

  • Executar uma leitura forte a partir de uma única linha ou de várias linhas numa tabela.
  • Executar uma leitura desatualizada de uma única linha ou várias linhas numa tabela.
  • Leitura de uma única linha ou várias linhas num índice secundário.

Para mais informações, consulte o artigo Métodos de leitura única.

Utilização da CPU agrupada por consulta

As tabelas seguintes monitorizam as consultas com a utilização mais elevada da CPU durante um período específico:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE: consultas durante intervalos de 1 minuto
  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE: consultas durante intervalos de 10 minutos
  • SPANNER_SYS.QUERY_STATS_TOP_HOUR: consultas durante intervalos de 1 hora

Estas tabelas têm as seguintes propriedades:

  • Cada tabela contém dados para intervalos de tempo não sobrepostos com a duração especificada no nome da tabela.

  • Os intervalos baseiam-se nas horas do relógio. Os intervalos de 1 minuto terminam no minuto, os intervalos de 10 minutos terminam a cada 10 minutos a partir da hora, e os intervalos de 1 hora terminam na hora.

    Por exemplo, às 11:59:30, os intervalos mais recentes disponíveis para consultas SQL são:

    • 1 minuto: 11:58:00–11:58:59
    • 10 minutos: 11:40:00–11:49:59
    • 1 hora: 10:00:00–10:59:59

  • O Spanner agrupa as estatísticas pelo texto da consulta SQL. Se uma consulta usar parâmetros de consulta, o Spanner agrupa todas as execuções dessa consulta numa linha. Se a consulta usar literais de strings, o Spanner só agrupa as estatísticas se o texto da consulta completo for idêntico. Quando o texto difere, cada consulta aparece como uma linha separada. Para DML em lote, o Spanner normaliza o lote removendo duplicados de declarações idênticas consecutivas antes de gerar a impressão digital.

  • Se estiver presente uma etiqueta de pedido, FPRINT é o hash da etiqueta de pedido. Caso contrário, é o hash do valor TEXT. Para DMLs particionados, FPRINT é sempre o hash do valor TEXT.

  • Cada linha contém estatísticas para todas as execuções de uma determinada consulta SQL para a qual o Spanner captura estatísticas durante o intervalo especificado.

  • Se o Spanner não conseguir armazenar todas as consultas executadas durante o intervalo, o sistema dá prioridade às consultas com a utilização mais elevada da CPU durante o intervalo especificado.

  • As consultas monitorizadas incluem as que foram concluídas, falharam ou foram canceladas pelo utilizador.

  • Um subconjunto de estatísticas é específico das consultas que foram executadas, mas não foram concluídas:

    • Contagem de execuções e latência média em segundos em todas as consultas que não foram bem-sucedidas.

    • Contagem de execuções para consultas que excederam o tempo limite.

    • Número de execuções de consultas que foram canceladas pelo utilizador ou falharam devido a problemas de ligação de rede.

  • Todas as colunas nas tabelas são anuláveis.

As estatísticas de consultas para declarações de DML particionadas executadas anteriormente têm as seguintes propriedades:

  • Cada declaração DML particionada bem-sucedida conta estritamente como uma execução. Uma instrução DML particionada que falhou, foi cancelada ou está a ser executada tem uma contagem de execuções de zero.

  • As estatísticas de ALL_FAILED_EXECUTION_COUNT, ALL_FAILED_AVG_LATENCY_SECONDS, CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT e TIMED_OUT_EXECUTION_COUNT não são monitorizadas para DMLs particionados.

  • As estatísticas de cada declaração DML particionada executada anteriormente podem aparecer em intervalos diferentes. SPANNER_SYS.QUERY_STATS_TOP_10MINUTE e SPANNER_SYS.QUERY_STATS_TOP_HOUR oferecem uma vista agregada para declarações DML particionadas que são concluídas no prazo de 10 minutos e 1 hora, respetivamente. Para ver estatísticas de declarações cuja duração seja superior a 1 hora, consulte o exemplo de consulta.

Esquema da tabela

Nome da coluna Tipo Descrição
INTERVAL_END TIMESTAMP Fim do intervalo de tempo em que ocorreram as execuções de consultas incluídas.
REQUEST_TAG STRING A etiqueta de pedido opcional para esta operação de consulta. Para mais informações acerca da utilização de etiquetas, consulte Resolução de problemas com etiquetas de pedidos.
QUERY_TYPE STRING Indica se uma consulta é um PARTITIONED_QUERY ou um QUERY. Uma PARTITIONED_QUERY é uma consulta com um partitionToken obtido a partir da API PartitionQuery ou uma instrução DML particionada. Todas as outras consultas e declarações DML são indicadas pelo QUERY tipo de consulta.
TEXT STRING Texto da consulta SQL, truncado para aproximadamente 64 KB.

As estatísticas de várias consultas que têm a mesma string de etiqueta são agrupadas numa única linha com a correspondência REQUEST_TAG dessa string de etiqueta. Apenas o texto de uma dessas consultas é apresentado neste campo, truncado para aproximadamente 64 KB. Para DML em lote, o conjunto de declarações SQL é reduzido a uma única linha, concatenada com um delimitador de ponto e vírgula. Os textos SQL idênticos consecutivos são desduplicados antes de serem truncados.
TEXT_TRUNCATED BOOL Se o texto da consulta foi ou não truncado.
TEXT_FINGERPRINT INT64 O hash do valor REQUEST_TAG, se estiver presente; caso contrário, o hash do valor TEXT. Corresponde ao campo query_fingerprint no registo de auditoria
EXECUTION_COUNT INT64 O número de vezes que o Spanner viu a consulta durante o intervalo.
AVG_LATENCY_SECONDS FLOAT64 Duração média, em segundos, de cada execução de consulta na base de dados. Esta média exclui o tempo de codificação e transmissão do conjunto de resultados, bem como a sobrecarga.
AVG_ROWS FLOAT64 Número médio de linhas devolvidas pela consulta.
AVG_BYTES FLOAT64 Número médio de bytes de dados devolvidos pela consulta, excluindo a sobrecarga de codificação de transmissão.
AVG_ROWS_SCANNED FLOAT64 Número médio de linhas analisadas pela consulta, excluindo valores eliminados.
AVG_CPU_SECONDS FLOAT64 Número médio de segundos de tempo de CPU que o Spanner gastou em todas as operações para executar a consulta.
ALL_FAILED_EXECUTION_COUNT INT64 O número de vezes que a consulta falhou durante o intervalo.
ALL_FAILED_AVG_LATENCY_SECONDS FLOAT64 Duração média, em segundos, de cada execução de consulta que falhou na base de dados. Esta média exclui o tempo de codificação e transmissão do conjunto de resultados, bem como a sobrecarga.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Número de vezes que a consulta foi cancelada pelo utilizador ou falhou devido a uma ligação de rede interrompida durante o intervalo.
TIMED_OUT_EXECUTION_COUNT INT64 O número de vezes que a consulta excedeu o tempo limite durante o intervalo.
AVG_BYTES_WRITTEN FLOAT64 Número médio de bytes escritos pela declaração.
AVG_ROWS_WRITTEN FLOAT64 Número médio de linhas modificadas pela declaração.
STATEMENT_COUNT INT64 A soma das declarações agregadas nesta entrada. Para consultas normais e DML, este valor é igual à contagem de execuções. Para DML em lote, o Spanner captura o número de declarações no lote.
RUN_IN_RW_TRANSACTION_EXECUTION_COUNT INT64 O número de vezes que a consulta foi executada como parte de uma transação de leitura/escrita. Esta coluna ajuda a determinar se pode evitar contestações de bloqueio movendo a consulta para uma transação só de leitura.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Um histograma do tempo de execução da consulta. Os valores são medidos em segundos.

A matriz contém um único elemento e tem o seguinte 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>>>
Para mais informações sobre os valores, consulte Distribuição.

Para calcular a latência do percentil a partir da distribuição, use a função SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), que devolve o percentil n estimado. Para um exemplo relacionado, consulte o artigo Encontre a latência do percentil 99 para consultas.

Para mais informações, consulte o artigo Percentis e métricas com valores de distribuição.

AVG_MEMORY_PEAK_USAGE_BYTES FLOAT64

Durante uma execução de consulta distribuída, a utilização máxima média de memória (em bytes).

Use esta estatística para identificar que consultas ou tamanhos de dados de tabelas têm maior probabilidade de atingir os limites de memória.
AVG_MEMORY_USAGE_PERCENTAGE FLOAT64

Durante uma execução de consulta distribuída, a utilização média da memória necessária (como uma percentagem do limite de memória permitido para esta consulta).

Esta estatística apenas acompanha a memória necessária para a consulta ser executada. Alguns operadores usam memória de buffer adicional para melhorar o desempenho. A memória de buffer adicional usada é visível no plano de consulta, mas não é usada para calcular AVG_MEMORY_USAGE_PERCENTAGE porque a memória de buffer é usada para otimização e não é necessária.

Use esta estatística para identificar consultas que se estão a aproximar do limite de utilização de memória e que correm o risco de falhar se o tamanho dos dados aumentar. Para mitigar o risco de falha da consulta, consulte as práticas recomendadas de SQL para otimizar estas consultas ou divida a consulta em partes que leiam menos dados.
AVG_QUERY_PLAN_CREATION_TIME_SECS FLOAT64

O tempo médio da CPU em segundos gasto na compilação de consultas, incluindo a criação do tempo de execução da consulta.

Se o valor desta coluna for elevado, use consultas parametrizadas.
AVG_FILESYSTEM_DELAY_SECS FLOAT64

O tempo médio que a consulta passa a ler do sistema de ficheiros ou a ser bloqueada na entrada/saída (E/S).

Use esta estatística para identificar uma potencial latência elevada causada pela E/S do sistema de ficheiros. Para mitigar, adicione um índice ou adicione uma cláusula STORING (GoogleSQL) ou INCLUDE (PostgreSQL) a um índice existente.
AVG_REMOTE_SERVER_CALLS FLOAT64

O número médio de chamadas de servidor remoto (RPC) que foram concluídas pela consulta.

Use esta estatística para identificar se diferentes consultas que analisam o mesmo número de linhas têm um número de RPCs muito diferente. A consulta com um valor de RPC mais elevado pode beneficiar da adição de um índice ou de uma cláusula STORING (GoogleSQL) ou INCLUDE (PostgreSQL) a um índice existente.
AVG_ROWS_SPOOLED FLOAT64

O número médio de linhas escritas num disco temporário (não na memória) pela declaração de consulta.

Use esta estatística para identificar consultas com potencial de latência elevada que são dispendiosas em termos de memória e não podem ser executadas na memória. Para mitigar este problema: altere a ordem de JOIN ou adicione um índice que forneça um SORT obrigatório.
AVG_DISK_IO_COST FLOAT64

O custo médio desta consulta em termos de carregamento de disco do HDD do Spanner.

Use este valor para fazer comparações relativas de custos de E/S de HDD entre leituras que executa na base de dados. A consulta de dados no armazenamento de HDD incorre num custo relativo à capacidade de carregamento do disco HDD da instância. Um valor mais elevado indica que está a usar mais carga do disco rígido e que a sua consulta pode ser mais lenta do que se estivesse a ser executada num SSD. Além disso, se a carga do disco rígido estiver no limite da capacidade, o desempenho das suas consultas pode ser ainda mais afetado. Pode monitorizar o total da carga do disco rígido da instância como uma percentagem. Para adicionar mais capacidade de carga do disco rígido, pode adicionar mais unidades de processamento ou nós à sua instância. Para mais informações, consulte Altere a capacidade de computação. Para melhorar o desempenho das consultas, também pode considerar mover alguns dados para um SSD.

Para cargas de trabalho que consomem muitas E/S de disco, recomendamos que armazene dados acedidos com frequência no armazenamento SSD. Os dados acedidos a partir do SSD não consomem a capacidade de carga do disco rígido. Pode armazenar tabelas, colunas ou índices secundários seletivos no armazenamento SSD, conforme necessário, enquanto mantém os dados acedidos com pouca frequência no armazenamento HDD. Para mais informações, consulte o artigo Vista geral do armazenamento hierárquico.

EXECUTION_COUNT, AVG_LATENCY_SECONDS e LATENCY_DISTRIBUTION para consultas com falhas incluem consultas que falharam devido a sintaxe incorreta ou que encontraram um erro temporário, mas foram bem-sucedidas ao tentar novamente. Estas estatísticas não acompanham as instruções DML particionadas com falhas nem canceladas.

Estatísticas agregadas

Também existem tabelas que monitorizam dados agregados para todas as consultas para as quais o Spanner capturou estatísticas num período específico:

  • SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: consultas durante intervalos de 1 minuto
  • SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: consultas durante intervalos de 10 minutos
  • SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: consultas durante intervalos de 1 hora

Estas tabelas têm as seguintes propriedades:

  • Cada tabela contém dados para intervalos de tempo não sobrepostos com a duração especificada no nome da tabela.

  • Os intervalos baseiam-se nas horas do relógio. Os intervalos de 1 minuto terminam no minuto, os intervalos de 10 minutos terminam a cada 10 minutos a partir da hora, e os intervalos de 1 hora terminam na hora.

    Por exemplo, às 11:59:30, os intervalos mais recentes disponíveis para consultas SQL são:

    • 1 minuto: 11:58:00–11:58:59
    • 10 minutos: 11:40:00–11:49:59
    • 1 hora: 10:00:00–10:59:59

  • Cada linha contém estatísticas para todas as consultas executadas na base de dados durante o intervalo especificado, agregadas. Existe apenas uma linha por intervalo de tempo e inclui consultas concluídas, consultas com falhas e consultas canceladas pelo utilizador.

  • As estatísticas capturadas nas tabelas TOTAL podem incluir consultas que o Spanner não capturou nas tabelas TOP.

  • Algumas colunas nestas tabelas são expostas como métricas no Cloud Monitoring. As métricas expostas são:

    • Contagem de execuções de consultas
    • Falhas de consultas
    • Latências de consultas
    • Contagem de linhas devolvidas
    • Contagem de linhas analisadas
    • Contagem de bytes devolvidos
    • Tempo da CPU da consulta

    Para mais informações, consulte o artigo Métricas do Spanner.

Esquema da tabela

Nome da coluna Tipo Descrição
INTERVAL_END TIMESTAMP Fim do intervalo de tempo em que ocorreram as execuções de consultas incluídas.
EXECUTION_COUNT INT64 Número de vezes que o Spanner viu a consulta durante o intervalo de tempo.
AVG_LATENCY_SECONDS FLOAT64 Duração média, em segundos, de cada execução de consulta na base de dados. Esta média exclui o tempo de codificação e transmissão do conjunto de resultados, bem como a sobrecarga.
AVG_ROWS FLOAT64 Número médio de linhas devolvidas pela consulta.
AVG_BYTES FLOAT64 Número médio de bytes de dados devolvidos pela consulta, excluindo a sobrecarga de codificação de transmissão.
AVG_ROWS_SCANNED FLOAT64 Número médio de linhas analisadas pela consulta, excluindo valores eliminados.
AVG_CPU_SECONDS FLOAT64 Número médio de segundos de tempo de CPU que o Spanner gastou em todas as operações para executar a consulta.
ALL_FAILED_EXECUTION_COUNT INT64 O número de vezes que a consulta falhou durante o intervalo.
ALL_FAILED_AVG_LATENCY_SECONDS FLOAT64 Duração média, em segundos, de cada execução de consulta que falhou na base de dados. Esta média exclui o tempo de codificação e transmissão do conjunto de resultados, bem como a sobrecarga.
CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT INT64 Número de vezes que a consulta foi cancelada pelo utilizador ou falhou devido a uma ligação de rede interrompida durante o intervalo.
TIMED_OUT_EXECUTION_COUNT INT64 O número de vezes que a consulta excedeu o tempo limite durante o intervalo.
AVG_BYTES_WRITTEN FLOAT64 Número médio de bytes escritos pela declaração.
AVG_ROWS_WRITTEN FLOAT64 Número médio de linhas modificadas pela declaração.
RUN_IN_RW_TRANSACTION_EXECUTION_COUNT INT64 O número de vezes que as consultas foram executadas como parte de transações de leitura/escrita. Esta coluna ajuda a determinar se pode evitar contestações de bloqueio movendo algumas consultas para transações só de leitura.
LATENCY_DISTRIBUTION ARRAY<STRUCT>

Um histograma do tempo de execução em todas as consultas. Os valores são medidos em segundos.

Especifique a matriz da seguinte forma:
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>
Para mais informações sobre os valores, consulte Distribuição.

Para calcular a latência do percentil a partir da distribuição, use a função SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64), que devolve o percentil n estimado. Para um exemplo relacionado, consulte o artigo Encontre a latência do percentil 99 para consultas.

Para mais informações, consulte o artigo Percentis e métricas com valores de distribuição.

Retenção de dados

No mínimo, o Spanner mantém os dados de cada tabela durante os seguintes períodos:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: intervalos que abrangem as 6 horas anteriores.

  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: intervalos que abrangem os 4 dias anteriores.

  • SPANNER_SYS.QUERY_STATS_TOP_HOUR e SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: intervalos que abrangem os 30 dias anteriores.

Consultas de exemplo

Esta secção inclui vários exemplos de declarações SQL que obtêm estatísticas de consultas. Pode executar estas declarações SQL através das bibliotecas de cliente, da Google Cloud CLI ou da Google Cloud consola.

Indicar as estatísticas básicas de cada consulta num determinado período

A seguinte consulta devolve os dados não processados das principais consultas no minuto anterior:

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;

Apresente as estatísticas das instruções DML particionadas que são executadas durante mais de uma hora

A seguinte consulta devolve a contagem de execuções e as linhas médias escritas pelas principais consultas DML particionadas nas horas anteriores:

SELECT text,
       request_tag,
       interval_end,
       sum(execution_count) as execution_count
       sum(avg_rows_written*execution_count)/sum(execution_count) as avg_rows_written
FROM spanner_sys.query_stats_top_hour
WHERE starts_with(text, "UPDATE") AND query_type = "PARTITIONED_QUERY"
group by text, request_tag, interval_end
ORDER BY interval_end DESC;

Apresente a lista das consultas com a utilização mais elevada da CPU

A consulta seguinte devolve as consultas com a utilização da CPU mais elevada na hora anterior:

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;

Encontre a quantidade total de execuções num determinado período

A seguinte consulta devolve o número total de consultas executadas no intervalo de 1 minuto completo mais 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);

Encontre a latência média de uma consulta

A seguinte consulta devolve as informações de latência média para uma consulta específica:

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

Encontre a latência do percentil 99 para consultas

A seguinte consulta devolve o percentil 99 do tempo de execução nas consultas executadas nos 10 minutos anteriores:

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;

A comparação da latência média com a latência do percentil 99 ajuda a identificar possíveis consultas atípicas com tempos de execução elevados.

Encontre as consultas que analisam mais dados

Pode usar o número de linhas analisadas por uma consulta como uma medida da quantidade de dados que a consulta analisou. A seguinte consulta devolve o número de linhas analisadas por consultas executadas na hora anterior:

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;

Encontre as declarações que escreveram mais dados

Pode usar o número de linhas escritas (ou bytes escritos) pela DML como uma medida da quantidade de dados que a consulta modificou. A seguinte consulta devolve o número de linhas escritas por declarações DML executadas na hora anterior:

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;

Totalizar a utilização da CPU em todas as consultas

A seguinte consulta devolve o número de horas de CPU usadas na hora anterior:

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

Liste as consultas que falharam num determinado período

A seguinte consulta devolve os dados não processados, incluindo a contagem de execuções e a latência média das consultas com falhas para as principais consultas no minuto anterior. Estas estatísticas não acompanham as declarações DML particionadas com falhas nem canceladas.

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;

Encontre a quantidade total de erros num determinado período

A consulta seguinte devolve o número total de consultas cuja execução falhou no intervalo de 1 minuto completo mais recente. Estas estatísticas não acompanham as declarações DML particionadas com falhas nem canceladas.

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;

Indique as consultas que atingem o limite de tempo com maior frequência

A consulta seguinte devolve as consultas com a contagem de tempo limite mais elevada na hora anterior.

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;

Encontre a latência média das execuções bem-sucedidas e com falhas de uma consulta

A consulta seguinte devolve a latência média combinada, a latência média para execuções bem-sucedidas e a latência média para execuções falhadas de uma consulta específica. Estas estatísticas não monitorizam as declarações DML particionadas falhadas e canceladas.

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

Resolva problemas de utilização elevada da CPU ou latência de consultas elevada com estatísticas de consultas

As estatísticas de consultas são úteis quando precisa de investigar a elevada utilização da CPU na sua base de dados do Spanner ou quando está apenas a tentar compreender as formas de consultas que exigem muita CPU na sua base de dados. A inspeção de consultas que usam quantidades significativas de recursos da base de dados dá aos utilizadores do Spanner uma forma potencial de reduzir os custos operacionais e, possivelmente, melhorar as latências gerais do sistema.

Pode usar o código SQL ou o painel de controlo Estatísticas de consultas para investigar consultas problemáticas na sua base de dados. Os tópicos seguintes mostram como pode investigar essas consultas através do código SQL.

Embora o exemplo seguinte se foque na utilização da CPU, pode seguir passos semelhantes para resolver problemas de latência de consultas elevada e encontrar as consultas com as latências mais elevadas. Basta selecionar intervalos de tempo e consultas por latência em vez de utilização da CPU.

Selecione um período para investigar

Comece a sua investigação procurando uma altura em que a sua aplicação começou a ter uma utilização elevada da CPU. Por exemplo, se o problema tiver começado a ocorrer por volta das 17:00 UTC de 24 de julho de 2020.

Recolher estatísticas de consultas para o período selecionado

Depois de selecionar um período para iniciar a nossa investigação, vamos analisar as estatísticas recolhidas na tabela QUERY_STATS_TOTAL_10MINUTE nesse período. Os resultados desta consulta podem indicar como a CPU e outras estatísticas de consultas mudaram durante esse período.

A consulta seguinte devolve as estatísticas de consultas agregadas de 16:30 a 17:30 UTC, inclusive. Estamos a usar ROUND na nossa consulta para restringir o número de casas decimais para fins de apresentação.

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;

A execução da consulta gerou os seguintes resultados.

interval_end contagem latência rows_returned bytes 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

Na tabela anterior, vemos que o tempo médio da CPU, a coluna avg_cpu na tabela de resultados, é mais elevado nos intervalos realçados que terminam às 17:00. Também vemos um número muito superior de linhas analisadas em média. Isto indica que as consultas mais caras foram executadas entre as 16:50 e as 17:00. Escolha esse intervalo para investigar mais detalhadamente no passo seguinte.

Encontre as consultas que estão a causar uma elevada utilização da CPU

Com um intervalo de tempo selecionado para investigar, consultamos agora a tabela QUERY_STATS_TOP_10MINUTE. Os resultados desta consulta podem ajudar a indicar que consultas causam uma utilização elevada da 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;

A execução desta consulta gera os seguintes resultados.

impressão digital contagem latência 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

As 2 principais consultas, realçadas na tabela de resultados, são valores atípicos em termos de média de CPU e latência, bem como número de execuções e CPU total. Investigue a primeira consulta apresentada nestes resultados.

Compare execuções de consultas ao longo do tempo

Depois de restringirmos a investigação, podemos concentrar a nossa atenção na tabela QUERY_STATS_TOP_MINUTE. Ao comparar execuções ao longo do tempo para uma consulta específica, podemos procurar correlações entre o número de linhas ou bytes devolvidos, ou o número de linhas analisadas e o aumento da CPU ou da latência. Um desvio pode indicar não uniformidade nos dados. Os números consistentemente elevados de linhas analisadas podem indicar a falta de índices adequados ou a ordenação de junções abaixo do ideal.

Investigue a consulta que apresenta a utilização média mais elevada da CPU e a latência mais elevada executando a seguinte declaração que filtra a text_fingerprint dessa consulta.

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;

A execução desta consulta devolve os seguintes resultados.

interval_end latência 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

Ao analisar os resultados anteriores, vemos que o número de linhas analisadas, a CPU usada e a latência mudaram significativamente por volta das 09:00. Para compreender por que motivo estes números aumentaram de forma tão drástica, vamos analisar o texto da consulta e ver se alguma alteração no esquema pode ter afetado a consulta.

Use a seguinte consulta para obter o texto da consulta que estamos a investigar.

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

Isto devolve o seguinte resultado.

texto text_truncated
select * from orders where o_custkey = 36901; falso

Ao examinar o texto da consulta devolvido, percebemos que a consulta está a filtrar um campo denominado o_custkey. Esta é uma coluna não chave na tabela orders. Acontece que existia um índice nessa coluna que foi removido por volta das 09:00. Isto explica a alteração no custo desta consulta. Podemos adicionar novamente o índice ou, se a consulta for executada com pouca frequência, decidir não ter o índice e aceitar o custo de leitura mais elevado.

A nossa investigação focou-se até agora em consultas concluídas com êxito e encontrámos um motivo pelo qual a base de dados estava a sofrer alguma degradação do desempenho. No passo seguinte, vamos focar-nos nas consultas falhadas ou canceladas e mostrar como examinar esses dados para obter mais estatísticas.

Investigue consultas com falhas

As consultas que não são concluídas com êxito continuam a consumir recursos antes de expirar o tempo limite, serem canceladas ou falharem de outra forma. O Spanner acompanha a quantidade de execuções e os recursos consumidos pelas consultas com falhas, bem como pelas consultas com êxito. Estas estatísticas não monitorizam as declarações DML particionadas falhadas e canceladas.

Para verificar se as consultas com falhas contribuem significativamente para a utilização do sistema, podemos primeiro verificar quantas consultas falharam no intervalo de tempo de 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 latência
2020-07-24T16:52:00Z 1 15.211391
2020-07-24T16:53:00Z 3 58.312232

Investigando mais aprofundadamente, podemos procurar consultas com maior probabilidade de falhar através da seguinte consulta.

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

Conforme mostra a tabela anterior, a consulta com a impressão digital 5505124206529314852 falhou várias vezes durante diferentes intervalos de tempo. Perante um padrão de falhas como este, é interessante comparar a latência das execuções bem-sucedidas e das execuções sem êxito.

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

Aplique práticas recomendadas

Depois de identificar uma consulta candidata à otimização, podemos analisar o perfil da consulta e tentar otimizar através das práticas recomendadas de SQL.

O que se segue?