Estatísticas de consulta

Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

O Spanner fornece tabelas integradas que mantêm várias estatísticas para as consultas e instruções da linguagem de manipulação de dados (DML) que usaram mais CPU, e todas as consultas agregadas (incluindo consultas de fluxo de alterações).

Disponibilidade

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

• A página do Spanner Studio de um banco de dados no console do Google Cloud

• O comando gcloud spanner databases execute-sql

• Painéis de insights de consulta

• A API executeQuery

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

Uso de CPU agrupado por consulta

As tabelas a seguir rastreiam as consultas com o maior uso de 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 em intervalos de 10 minutos
• SPANNER_SYS.QUERY_STATS_TOP_HOUR: consultas durante intervalos de 1 hora

Essas tabelas têm as seguintes propriedades:

• Cada uma contém dados para intervalos de tempo não sobrepostos do comprimento que o nome da tabela específica.

• Os intervalos são baseados em tempos de relógio. Os de 1 minuto terminam no minuto, os de 10 minutos terminam a cada 10 minutos, começando na hora, e os intervalos de 1 hora terminam na hora.

  Por exemplo, às 11:59:30, os intervalos mais recentes disponíveis para as 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 usa parâmetros de consulta, o Spanner agrupa todas as execuções dela em uma linha. Se a consulta usar literais de string, o Spanner só vai agrupar as estatísticas se o texto completo da consulta for idêntico. Quando qualquer texto diferir, cada consulta vai aparecer como uma linha separada. Para a DML em lote, o Spanner normaliza o lote eliminando instruções idênticas consecutivas antes de gerar a impressão digital.

• Se uma tag de solicitação estiver presente, FPRINT será o hash da tag de solicitação. Caso contrário, é o hash do valor TEXT. Para DMLs particionadas, FPRINT é sempre o hash do valor TEXT.

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

• Se o Spanner não puder armazenar todas as consultas em execução durante o intervalo, o sistema priorizará as consultas com o maior uso de CPU durante o intervalo especificado.

• As consultas rastreadas incluem aquelas que foram concluídas, falharam ou foram canceladas pelo usuário.

• 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ção para consultas que expiraram.

  • Contagem de execuções para consultas que foram canceladas pelo usuário ou falharam devido a problemas de conectividade de rede.

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

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

• Cada instrução DML particionada bem-sucedida é contada como uma execução. Uma instrução DML particionada que falhou, foi cancelada ou está sendo executada tem uma contagem de execução de zero.

• As estatísticas ALL_FAILED_EXECUTION_COUNT, ALL_FAILED_AVG_LATENCY_SECONDS, CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT e TIMED_OUT_EXECUTION_COUNT não são acompanhadas para DMLs particionadas.

• As estatísticas de cada instrução DML particionada executada anteriormente podem aparecer em intervalos diferentes. SPANNER_SYS.QUERY_STATS_TOP_10MINUTE e SPANNER_SYS.QUERY_STATS_TOP_HOUR fornecem uma visualização agregada de instruções DML particionadas que são concluídas em 10 minutos e 1 hora, respectivamente. Para visualizar estatísticas de instruções com duração superior a uma hora, consulte o exemplo de consulta.

Esquema de tabela

EXECUTION_COUNT, AVG_LATENCY_SECONDS e LATENCY_DISTRIBUTION para consultas com falha incluem consultas que falharam devido a sintaxe incorreta ou encontraram um erro temporário, mas foram bem-sucedidas na nova tentativa. Essas estatísticas não rastreiam instruções DML particionadas canceladas e com falha.

Agregar estatísticas

Há também tabelas que rastreiam dados agregados para todas as consultas para as quais o Spanner capturou estatísticas em um período específico:

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

Essas tabelas têm as seguintes propriedades:

• Cada uma contém dados para intervalos de tempo não sobrepostos do comprimento que o nome da tabela específica.

• Os intervalos são baseados em tempos de relógio. Os de 1 minuto terminam no minuto, os de 10 minutos terminam a cada 10 minutos, começando na hora, e os intervalos de 1 hora terminam na hora.

  Por exemplo, às 11:59:30, os intervalos mais recentes disponíveis para as 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 no banco de dados durante o intervalo especificado, agregadas juntas. Há apenas uma linha por intervalo de tempo e inclui consultas concluídas, consultas com falha e consultas canceladas pelo usuário.

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

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

  • Contagem de execuções de consulta
  • Falhas de consulta
  • Latências de consulta
  • Contagem de linhas retornadas
  • Contagem de linhas verificadas
  • Contagem de bytes retornados
  • Consultar o tempo de CPU

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

Esquema de tabela

Retenção de dados

O Spanner mantém dados para cada tabela, no mínimo, pelos períodos a seguir:

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

• SPANNER_SYS.QUERY_STATS_TOP_10MINUTE e SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: intervalos abrangendo os quatro dias anteriores.

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

Exemplo de consultas

Nesta seção, há várias instruções SQL de exemplo que recuperam estatísticas de consulta. É possível executar essas instruções SQL usando as bibliotecas de cliente, a CLI do Google Cloud ou o Console do Google Cloud.

Listar as estatísticas básicas de cada consulta em um determinado período

A consulta a seguir retorna os dados brutos para as 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;

Listar as estatísticas de instruções DML particionadas que são executadas por mais de uma hora

A consulta a seguir retorna a contagem de execuções e as linhas médias gravadas 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, query_type;

Listar as consultas com o maior uso de CPU

A consulta a seguir retorna as consultas com o maior uso de CPU 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;

Encontrar a contagem total de execuções em um determinado período

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

Encontrar a latência média de uma consulta

A consulta a seguir retorna as informações de latência média de 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;";

Encontrar a latência do 99º percentil para consultas

A consulta a seguir retorna o 99º percentil do tempo de execução em 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 99º percentil ajuda a identificar possíveis consultas fora da curva com tempos de execução altos.

Encontrar as consultas que verificam a maioria dos dados

É possível usar o número de linhas verificadas por uma consulta como uma medida da quantidade de dados que a consulta verificou. A consulta a seguir retorna o número de linhas verificadas 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;

Encontrar as instruções que gravaram mais dados

É possível usar o número de linhas gravadas (ou bytes gravados) pela DML como uma medida da quantidade de dados que a consulta modificou. A consulta a seguir retorna o número de linhas gravadas por instruçõ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 o uso da CPU em todas as consultas

A consulta a seguir retorna 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);

Listar as consultas que falharam em um determinado período

A consulta a seguir retorna os dados brutos, incluindo a contagem de execução e a latência média das consultas com falha das principais consultas no minuto anterior. Essas estatísticas não rastreiam instruções DML particionadas canceladas e com falha.

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;

Encontrar a contagem total de erros em um determinado período

A consulta a seguir retorna o número total de consultas que não foram executadas no intervalo completo de 1 minuto mais recente. Essas estatísticas não rastreiam instruções DML particionadas com falha e 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;

Listar as consultas que têm mais tempo limite

A consulta a seguir retorna as consultas com a contagem de tempo limite mais alta 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;

Encontrar a latência média de execuções bem-sucedidas e com falha em uma consulta

A consulta a seguir retorna 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 com falha de uma consulta específica. Essas estatísticas não rastreiam instruções DML particionadas com falhas 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;";

Como solucionar problemas de alto uso da CPU ou latência de consulta elevada com estatísticas de consulta

As estatísticas de consulta são úteis quando você precisa investigar o alto uso de CPU no banco de dados do Spanner ou quando está apenas tentando entender os formatos de consulta intensivos da CPU no banco de dados. A inspeção de consultas que usam quantidades significativas de recursos de banco de dados oferece aos usuários do Spanner uma maneira potencial de reduzir custos operacionais e possivelmente melhorar as latências gerais do sistema.

É possível usar o código SQL ou o painel Query Insights para investigar consultas problemáticas no seu banco de dados. Os tópicos a seguir mostram como investigar essas consultas usando o código SQL.

O exemplo a seguir se concentra no uso da CPU, mas etapas semelhantes podem ser seguidas para solucionar problemas de latência de consulta elevada e encontrar as consultas com maior latência. Basta selecionar intervalos e consultas por latência em vez de uso da CPU.

Selecione um período para investigar

Inicie a investigação procurando um horário em que o aplicativo começou a apresentar uso elevado da CPU. Por exemplo, se o problema começou a ocorrer por volta das 17h de 24 de julho de 2020 UTC.

Coletar estatísticas de consulta para o período selecionado

Depois de selecionar um período para iniciar nossa investigação, analisaremos as estatísticas coletadas na tabela QUERY_STATS_TOTAL_10MINUTE desse período. Os resultados dessa consulta podem indicar como a CPU e outras estatísticas de consulta mudaram nesse período.

A consulta a seguir retorna as estatísticas de consulta agregadas de 16:30 a 17:30 UTC, inclusive. Estamos usando ROUND em nossa consulta para restringir o número de casas decimais para fins de exibiçã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 resultados a seguir.

Na tabela anterior, vemos que o tempo médio de CPU, a coluna avg_cpu na tabela de resultados, é mais alto nos intervalos destacados que termina em 17:00. Também vemos um número muito maior de linhas verificadas em média. Isso indica que consultas mais caras foram executadas entre 16h50 e 17h. Escolha esse intervalo para investigar mais na próxima etapa.

Encontrar as consultas que estão causando alto uso da CPU

Com um intervalo de tempo para investigar o selecionado, agora consultamos a tabela QUERY_STATS_TOP_10MINUTE. Os resultados dessa consulta podem ajudar a indicar quais consultas causam alto uso de 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 dessa consulta gera os seguintes resultados.

As duas principais consultas, destacadas na tabela de resultados, são outliers em termos de CPU e latência média, bem como o número de execuções e o total de CPU. Investigue a primeira consulta listada nesses resultados.

Comparar execuções de consulta ao longo do tempo

Depois de restringir a investigação, podemos focar nossa atenção na tabela QUERY_STATS_TOP_MINUTE. Ao comparar execuções ao longo do tempo em uma consulta específica, podemos procurar correlações entre o número de linhas ou bytes retornados, ou o número de linhas verificadas e de CPU ou latência elevadas. Um desvio pode indicar não uniformidade nos dados. Um número consistente de linhas verificadas pode indicar a falta de índices apropriados ou uma ordem de mesclagem não ideal.

Para investigar a consulta que exibe o maior uso médio de CPU e a maior latência, execute a seguinte instrução, que filtra o 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;

Executar essa consulta retorna os seguintes resultados.

Analisando os resultados anteriores, vemos que o número de linhas verificadas, a CPU usada e a latência mudaram significativamente por volta de 9h. Para entender porque esses números aumentaram tão drasticamente, examinaremos o texto da consulta e veremos se alguma mudança no esquema pode ter afetado a consulta.

Use a consulta a seguir para recuperar o texto da consulta que estamos investigando.

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

Isso retorna o resultado a seguir.

Analisando o texto retornado, percebemos que a consulta está filtrando em um campo chamado o_custkey. Esta é uma coluna não chave na tabela orders. Durante o processo, havia um índice nessa coluna que era solto por volta das 9h. Isso explica a mudança no custo dessa consulta. Podemos adicionar o índice novamente ou, se a consulta não for executada com frequência, decidir não ter o índice e aceitar o custo de leitura mais alto.

Até o momento, nossa investigação se concentrou nas consultas concluídas e encontramos um motivo pelo qual o banco de dados teve alguma degradação no desempenho. Na próxima etapa, focaremos em consultas com falha ou canceladas e mostraremos como examinar esses dados para ter mais insights.

Investigar consultas com falha

As consultas que não são concluídas ainda consomem recursos antes de expirar, são canceladas ou falham. O Spanner rastreia a contagem de execução e os recursos consumidos por consultas com falha junto com as bem-sucedidas. Essas estatísticas não rastreiam instruções DML particionadas com falhas e canceladas.

Para verificar se as consultas com falha contribuem significativamente para a utilização do sistema, podemos verificar quantas consultas falharam no intervalo 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;

Após uma investigação, podemos procurar consultas com mais probabilidade de falhar usando a consulta a seguir.

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;

Como mostrado na tabela anterior, a consulta com impressão digital 5505124206529314852 falhou várias vezes em intervalos diferentes. Considerando um padrão de falhas como esse, é interessante comparar a latência de execuções bem-sucedidas e malsucedidas.

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;

Aplicar as práticas recomendadas

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

A seguir

• Use as consultas ativas mais antigas para determinar as consultas ativas mais longas.

• Saiba mais sobre como investigar a alta utilização da CPU.

• Saiba mais sobre outras ferramentas de introspecção.

• Saiba mais sobre outras informações que o Spanner armazena para cada banco de dados nas tabelas de esquema de informações do banco de dados.

• Saiba mais sobre as práticas recomendadas de SQL para o Spanner.