Consultar estadísticas

Spanner proporciona tablas integradas que conservan muchas estadísticas de las consultas y las instrucciones del lenguaje de manipulación de datos (DML) que han usado la mayor cantidad de CPU, así como todas las consultas agregadas (incluidas las consultas de flujo de cambios).

Acceder a las estadísticas de consultas

Spanner proporciona las estadísticas de las consultas en el esquema SPANNER_SYS. Puedes acceder a los datos de SPANNER_SYS de las siguientes formas:

• La página de Spanner Studio de una base de datos en la consola de Google Cloud .

• El comando gcloud spanner databases execute-sql.

• Paneles de control Información útil sobre la consulta.

• El método executeSql o el método executeStreamingSql.

Los siguientes métodos de lectura única que proporciona Spanner no admiten SPANNER_SYS:

• Realizar una lectura fuerte desde una sola fila o varias filas en una tabla.
• Realizar una lectura inactiva de una sola fila o varias filas en una tabla.
• Leer desde una sola fila o varias filas en un índice secundario.

Para obtener más información, consulta Métodos de lectura única.

Uso de CPU agrupado por consulta

En las siguientes tablas se registran las consultas con el mayor uso de CPU durante un periodo 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 tablas tienen las siguientes propiedades:

• Cada tabla contiene datos de intervalos de tiempo no superpuestos de la duración que se especifica en el nombre de la tabla.

• Los intervalos se basan en la hora del reloj. Los intervalos de 1 minuto terminan en el minuto, los intervalos de 10 minutos terminan cada 10 minutos a partir de la hora en punto y los intervalos de 1 hora terminan a la hora en punto.

  Por ejemplo, a las 11:59:30, los intervalos más recientes disponibles para las consultas SQL son los siguientes:

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

• Spanner agrupa las estadísticas por el texto de la consulta de SQL. Si una consulta usa parámetros de consulta, Spanner agrupa todas las ejecuciones de esa consulta en una fila. Si la consulta usa literales de cadena, Spanner solo agrupa las estadísticas si el texto completo de la consulta es idéntico. Si el texto es diferente, cada consulta aparece en una fila independiente. En el caso de las DML por lotes, Spanner normaliza el lote deduplicando las instrucciones idénticas consecutivas antes de generar la huella digital.

• Si hay una etiqueta de solicitud, FPRINT es el hash de la etiqueta de solicitud. De lo contrario, es el hash del valor TEXT. En el caso de las DMLs con particiones, FPRINT siempre es el hash del valor TEXT.

• Cada fila contiene estadísticas de todas las ejecuciones de una consulta SQL concreta de las que Spanner registra estadísticas durante el intervalo especificado.

• Si Spanner no puede almacenar todas las consultas ejecutadas durante el intervalo, el sistema prioriza las consultas con el mayor uso de CPU durante el intervalo especificado.

• Las consultas monitorizadas incluyen las que se han completado, las que han fallado o las que ha cancelado el usuario.

• Un subconjunto de estadísticas es específico de las consultas que se ejecutaron, pero no se completaron:

  • Número de ejecuciones y latencia media en segundos de todas las consultas que no se han completado correctamente.

  • Número de ejecuciones de consultas cuyo tiempo de espera se ha agotado.

  • Número de ejecuciones de consultas que el usuario ha cancelado o que no se han podido completar debido a problemas de conectividad de red.

• Todas las columnas de las tablas admiten valores nulos.

Las estadísticas de las consultas de las instrucciones de DML particionado ejecutadas anteriormente tienen las siguientes propiedades:

• Cada declaración de DML particionado que se ejecute correctamente se contabiliza como una ejecución. Una instrucción DML particionada que ha fallado, se ha cancelado o se está ejecutando tiene un recuento de ejecuciones igual a cero.

• No se registran estadísticas de ALL_FAILED_EXECUTION_COUNT, ALL_FAILED_AVG_LATENCY_SECONDS, CANCELLED_OR_DISCONNECTED_EXECUTION_COUNT y TIMED_OUT_EXECUTION_COUNT para las DMLs particionadas.

• Las estadísticas de cada instrucción DML con particiones ejecutada anteriormente pueden aparecer en intervalos diferentes. SPANNER_SYS.QUERY_STATS_TOP_10MINUTE y SPANNER_SYS.QUERY_STATS_TOP_HOUR proporcionan una vista agregada de las instrucciones DML particionadas que se completan en 10 minutos y 1 hora, respectivamente. Para ver las estadísticas de las instrucciones cuya duración sea superior a 1 hora, consulta el ejemplo de consulta.

Esquema de tabla

EXECUTION_COUNT, AVG_LATENCY_SECONDS y LATENCY_DISTRIBUTION de las consultas fallidas incluyen las consultas que han fallado debido a una sintaxis incorrecta o que han encontrado un error transitorio, pero que se han completado al volver a intentarlo. Estas estadísticas no registran las declaraciones de DML particionado fallidas ni canceladas.

Estadísticas agregadas

También hay tablas que registran datos agregados de todas las consultas de las que Spanner ha recogido estadísticas en un periodo concreto:

• 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 tablas tienen las siguientes propiedades:

• Cada tabla contiene datos de intervalos de tiempo no superpuestos de la duración que se especifica en el nombre de la tabla.

• Los intervalos se basan en la hora del reloj. Los intervalos de 1 minuto terminan en el minuto, los intervalos de 10 minutos terminan cada 10 minutos a partir de la hora en punto y los intervalos de 1 hora terminan a la hora en punto.

  Por ejemplo, a las 11:59:30, los intervalos más recientes disponibles para las consultas SQL son los siguientes:

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

• Cada fila contiene estadísticas de todas las consultas ejecutadas en la base de datos durante el intervalo especificado, agregadas. Solo hay una fila por intervalo de tiempo, que incluye las consultas completadas, las fallidas y las canceladas por el usuario.

• Las estadísticas recogidas en las tablas TOTAL pueden incluir consultas que Spanner no haya recogido en las tablas TOP.

• Algunas columnas de estas tablas se muestran como métricas en Cloud Monitoring. Las métricas expuestas son las siguientes:

  • Número de ejecuciones de consultas
  • Errores de consulta
  • Latencias de las consultas
  • Número de filas devueltas
  • Recuento de filas analizadas
  • Número de bytes devueltos
  • Tiempo de CPU de la consulta

  Para obtener más información, consulta Métricas de Spanner.

Esquema de tabla

Conservación de datos

Como mínimo, Spanner conserva los datos de cada tabla durante los siguientes periodos:

• SPANNER_SYS.QUERY_STATS_TOP_MINUTE y SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: intervalos que abarcan las 6 horas anteriores.

• SPANNER_SYS.QUERY_STATS_TOP_10MINUTE y SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: intervalos que abarcan los 4 días anteriores.

• SPANNER_SYS.QUERY_STATS_TOP_HOUR y SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: intervalos que abarcan los 30 días anteriores.

Consultas de ejemplo

En esta sección se incluyen varios ejemplos de instrucciones SQL que obtienen estadísticas de consultas. Puedes ejecutar estas instrucciones SQL con las bibliotecas de cliente, la CLI de Google Cloud o la consola deGoogle Cloud .

Lista las estadísticas básicas de cada consulta en un periodo determinado

La siguiente consulta devuelve los datos sin procesar de las consultas principales del 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;

Lista de estadísticas de instrucciones DML particionadas que se ejecutan durante más de una hora

La siguiente consulta devuelve el recuento de ejecuciones y la media de filas escritas por las consultas de DML con particiones principales de las últimas horas:

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;

Enumera las consultas con el mayor uso de CPU

La siguiente consulta devuelve las consultas con el mayor uso de CPU de la 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;

Consultar el número total de ejecuciones en un periodo determinado

La siguiente consulta devuelve el número total de consultas ejecutadas en el intervalo de 1 minuto completo más reciente:

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

Consultar la latencia media de una consulta

La siguiente consulta devuelve la información de latencia media de una consulta específica:

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

Consulta la latencia del percentil 99 de las consultas

La siguiente consulta devuelve el percentil 99 del tiempo de ejecución de las consultas que se han ejecutado en los últimos 10 minutos:

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;

Comparar la latencia media con la latencia del percentil 99 ayuda a identificar posibles consultas atípicas con tiempos de ejecución elevados.

Buscar las consultas que analizan la mayor cantidad de datos

Puedes usar el número de filas analizadas por una consulta como medida de la cantidad de datos que ha analizado la consulta. La siguiente consulta devuelve el número de filas analizadas por las consultas ejecutadas en la 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;

Buscar las instrucciones que han escrito la mayor cantidad de datos

Puedes usar el número de filas escritas (o bytes escritos) por DML como medida de la cantidad de datos que ha modificado la consulta. La siguiente consulta devuelve el número de filas escritas por las instrucciones DML ejecutadas en la 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;

Total del uso de CPU en todas las consultas

La siguiente consulta devuelve el número de horas de CPU usadas en la 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);

Mostrar las consultas que han fallado en un periodo determinado

La siguiente consulta devuelve los datos sin procesar, incluido el recuento de ejecuciones y la latencia media de las consultas fallidas de las consultas principales del minuto anterior. Estas estadísticas no registran las declaraciones de DML particionado fallidas ni 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;

Consultar el número total de errores en un periodo determinado

La siguiente consulta devuelve el número total de consultas que no se han podido ejecutar en el intervalo de 1 minuto completo más reciente. Estas estadísticas no registran las declaraciones de DML particionado fallidas ni 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;

Enumera las consultas que agotan el tiempo de espera con más frecuencia

La siguiente consulta devuelve las consultas con el mayor número de tiempos de espera de la 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;

Consulta la latencia media de las ejecuciones correctas y fallidas de una consulta

La siguiente consulta devuelve la latencia media combinada, la latencia media de las ejecuciones correctas y la latencia media de las ejecuciones fallidas de una consulta específica. Estas estadísticas no registran las instrucciones DML particionadas fallidas ni 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;";

Solucionar problemas de uso elevado de la CPU o de latencia de las consultas con estadísticas de consultas

Las estadísticas de consultas son útiles cuando necesitas investigar un uso elevado de la CPU en tu base de datos de Spanner o cuando solo quieres entender las formas de las consultas que consumen mucha CPU en tu base de datos. Al inspeccionar las consultas que usan cantidades significativas de recursos de la base de datos, los usuarios de Spanner pueden reducir los costes operativos y mejorar las latencias generales del sistema.

Puedes usar código SQL o el panel de control Información valiosa sobre las consultas para investigar las consultas problemáticas de tu base de datos. En los siguientes temas se muestra cómo investigar estas consultas mediante código SQL.

Aunque el siguiente ejemplo se centra en el uso de la CPU, se pueden seguir pasos similares para solucionar problemas de latencia de consulta elevada y encontrar las consultas con las latencias más altas. Solo tiene que seleccionar intervalos de tiempo y consultas por latencia en lugar de por uso de CPU.

Seleccionar un periodo para investigar

Empieza tu investigación buscando un momento en el que tu aplicación empezara a experimentar un uso elevado de la CPU. Por ejemplo, si el problema empezó a producirse alrededor de las 17:00 del 24 de julio del 2020 (UTC).

Recoger estadísticas de consultas del periodo seleccionado

Una vez que hayamos seleccionado el periodo para iniciar la investigación, consultaremos las estadísticas recogidas en la tabla QUERY_STATS_TOTAL_10MINUTE en ese momento. Los resultados de esta consulta pueden indicar cómo han cambiado la CPU y otras estadísticas de la consulta durante ese periodo.

La siguiente consulta devuelve las estadísticas de consultas agregadas desde las 16:30 hasta las 17:30 UTC (ambas incluidas). Usamos ROUND en nuestra consulta para restringir el número de decimales con fines de visualización.

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;

Al ejecutar la consulta, se obtuvieron los siguientes resultados.

En la tabla anterior, vemos que el tiempo medio de CPU, la columna avg_cpu de la tabla de resultados, es más alto en los intervalos resaltados que terminan a las 17:00. También vemos un número de filas analizadas mucho mayor de media. Esto indica que las consultas más caras se ejecutaron entre las 16:50 y las 17:00. Elige ese intervalo para investigar más a fondo en el siguiente paso.

Buscar las consultas que provocan un uso elevado de la CPU

Una vez que hemos seleccionado el intervalo de tiempo que queremos investigar, consultamos la tabla QUERY_STATS_TOP_10MINUTE. Los resultados de esta consulta pueden ayudar a identificar qué consultas provocan un uso elevado de la 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;

Al ejecutar esta consulta, se obtienen los siguientes resultados.

Las dos consultas principales, destacadas en la tabla de resultados, son valores atípicos en cuanto a la latencia y la CPU medias, así como al número de ejecuciones y a la CPU total. Investiga la primera consulta que aparece en estos resultados.

Comparar ejecuciones de consultas a lo largo del tiempo

Ahora que hemos acotado la investigación, podemos centrarnos en la tabla QUERY_STATS_TOP_MINUTE. Al comparar las ejecuciones de una consulta concreta a lo largo del tiempo, podemos buscar correlaciones entre el número de filas o bytes devueltos, o el número de filas analizadas y la latencia o el uso de CPU elevados. Una desviación puede indicar una falta de uniformidad en los datos. Si el número de filas analizadas es alto de forma constante, puede indicar que faltan índices adecuados o que el orden de las combinaciones no es óptimo.

Investiga la consulta que muestra el uso medio de CPU más alto y la latencia más alta ejecutando la siguiente instrucción, que filtra por el texto_fingerprint de esa 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;

Al ejecutar esta consulta, se obtienen los siguientes resultados.

Al examinar los resultados anteriores, vemos que el número de filas analizadas, la CPU usada y la latencia cambiaron significativamente alrededor de las 9:00. Para saber por qué aumentaron tanto estas cifras, examinaremos el texto de la consulta y veremos si algún cambio en el esquema ha podido afectar a la consulta.

Usa la siguiente consulta para obtener el texto de la consulta que estamos investigando.

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

Se devuelve el siguiente resultado.

Al examinar el texto de la consulta devuelta, nos damos cuenta de que la consulta está filtrando un campo llamado o_custkey. Se trata de una columna que no es clave en la tabla orders. Resulta que había un índice en esa columna que se eliminó alrededor de las 9:00. Esto explica el cambio en el coste de esta consulta. Podemos volver a añadir el índice o, si la consulta se ejecuta con poca frecuencia, decidir no tener el índice y aceptar el mayor coste de lectura.

Hasta ahora, nuestra investigación se ha centrado en las consultas que se han completado correctamente y hemos encontrado un motivo por el que la base de datos ha experimentado una degradación del rendimiento. En el siguiente paso, nos centraremos en las consultas fallidas o canceladas y mostraremos cómo examinar esos datos para obtener más información valiosa.

Investigar consultas fallidas

Las consultas que no se completan correctamente siguen consumiendo recursos antes de que se agote el tiempo de espera, se cancelen o fallen de otro modo. Spanner monitoriza el número de ejecuciones y los recursos consumidos por las consultas fallidas, así como por las que se han completado correctamente. Estas estadísticas no registran las instrucciones DML particionadas fallidas ni canceladas.

Para comprobar si las consultas fallidas contribuyen de forma significativa a la utilización del sistema, primero podemos comprobar cuántas consultas han fallado en el intervalo de tiempo de interés.

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;

Si investigamos más a fondo, podemos buscar las consultas que tengan más probabilidades de fallar con la siguiente 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;

Como se muestra en la tabla anterior, la consulta con la huella digital 5505124206529314852 ha fallado varias veces durante diferentes intervalos de tiempo. Si se da un patrón de errores como este, es interesante comparar la latencia de las ejecuciones correctas y las incorrectas.

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 prácticas recomendadas

Una vez que hemos identificado una consulta candidata para la optimización, podemos consultar el perfil de la consulta y probar a optimizarla con las prácticas recomendadas de SQL.

Siguientes pasos

• Usa Consultas activas más antiguas para determinar las consultas activas que llevan más tiempo ejecutándose.

• Consulta más información sobre cómo investigar el uso elevado de la CPU.

• Consulta información sobre otras herramientas de introspección.

• Consulta más información sobre los datos que almacena Spanner de cada base de datos en las tablas del esquema de información de la base de datos.

• Consulta más información sobre las prácticas recomendadas de SQL para Spanner.