Estadísticas de las consultas activas más antiguas

Consultas activas más antiguas, también conocidas como consultas de mayor duración, es una lista de consultas que están activas en tu base de datos, ordenadas según el tiempo que llevan ejecutándose. Obtener información valiosa sobre estas consultas puede ayudar a identificar las causas de la latencia del sistema y del uso elevado de la CPU a medida que se producen.

Spanner proporciona una tabla integrada, SPANNER_SYS.OLDEST_ACTIVE_QUERIES, que muestra las consultas en ejecución, incluidas las que contienen instrucciones DML, ordenadas por hora de inicio en orden ascendente. No incluye las consultas de flujo de cambios.

Si se están ejecutando muchas consultas, los resultados pueden limitarse a un subconjunto del total de consultas debido a las restricciones de memoria que el sistema aplica a la recogida de estos datos. Por lo tanto, Spanner proporciona una tabla adicional, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, que muestra estadísticas de resumen de todas las consultas activas (excepto las consultas de flujo de cambios). Puede recuperar información de ambas tablas integradas mediante instrucciones SQL.

En este documento, describiremos ambas tablas, mostraremos algunas consultas de ejemplo que las usan y, por último, explicaremos cómo usarlas para mitigar los problemas causados por las consultas activas.

Disponibilidad

Los datos de SPANNER_SYS solo están disponibles a través de interfaces SQL. Por ejemplo:

Otros métodos de lectura única que proporciona Spanner no admiten SPANNER_SYS.

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES devuelve una lista de consultas activas ordenadas por la hora de inicio. Si se están ejecutando muchas consultas, los resultados pueden limitarse a un subconjunto del total de consultas debido a las restricciones de memoria que Spanner aplica a la recogida de estos datos. Para ver las estadísticas de resumen de todas las consultas activas, consulta ACTIVE_QUERIES_SUMMARY.

Esquema de tabla

Nombre de la columna Tipo Descripción
START_TIME TIMESTAMP Hora de inicio de la consulta.
TEXT_FINGERPRINT INT64 La huella digital es un hash de las operaciones que se incluyen en la transacción.
TEXT STRING El texto de la instrucción de consulta.
TEXT_TRUNCATED BOOL Es true si el texto de la consulta del campo TEXT se trunca. De lo contrario, es false.
SESSION_ID STRING ID de la sesión que ejecuta la consulta. Se usa para la observabilidad.
QUERY_ID. STRING ID de la consulta. Usa este ID con CALL cancel_query(query_id) para cancelar la consulta.

Consultas de ejemplo

Puedes ejecutar las siguientes instrucciones SQL de ejemplo con las bibliotecas de cliente, Google Cloud CLI o la Google Cloud consola.

Lista de las consultas en ejecución más antiguas

La siguiente consulta devuelve una lista de las consultas en ejecución más antiguas ordenadas por la hora de inicio de la 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 texto 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

Mostrar las dos consultas activas más antiguas

Este ejemplo, que es una ligera variación de la consulta anterior, devuelve las dos consultas más antiguas ordenadas por la hora de inicio de la consulta.

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 2;
Salida de la consulta
start_time text_fingerprint texto 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 su nombre indica, la tabla integrada SPANNER_SYS.ACTIVE_QUERIES_SUMMARY muestra estadísticas de resumen de todas las consultas activas. Como se muestra en el siguiente esquema, las consultas se agrupan por edad en tres contenedores o contadores: más de un segundo, más de 10 segundos y más de 100 segundos.

Esquema de tabla

Nombre de la columna Tipo Descripción
ACTIVE_COUNT INT64 Número total de consultas que se están ejecutando.
OLDEST_START_TIME TIMESTAMP Límite superior de la hora de inicio de la consulta en ejecución más antigua.
COUNT_OLDER_THAN_1S INT64 Número de consultas con una antigüedad superior a 1 segundo.
COUNT_OLDER_THAN_10S INT64 Número de consultas con una antigüedad superior a 10 segundos.
COUNT_OLDER_THAN_100S INT64 Número de consultas con una antigüedad superior a 100 segundos.

Una consulta puede contabilizarse en más de uno de estos segmentos. Por ejemplo, si una consulta se ha estado ejecutando durante 12 segundos, se contabilizará en COUNT_OLDER_THAN_1S y COUNT_OLDER_THAN_10S porque cumple ambos criterios.

Consultas de ejemplo

Puedes ejecutar las siguientes instrucciones SQL de ejemplo con las bibliotecas de cliente, gcloud spanner o la Google Cloud consola.

Recuperar un resumen de las consultas activas

La siguiente consulta devuelve las estadísticas de resumen sobre las consultas en ejecución.

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;
Salida de la 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

Limitaciones

Aunque el objetivo es ofrecerte las estadísticas más completas posibles, hay algunas circunstancias en las que las consultas no se incluyen en los datos devueltos en estas tablas.

  • Las consultas DML (UPDATE, INSERT y DELETE) no se incluyen si están en la fase Aplicar mutaciones.

  • Una consulta no se incluye si está en medio de un reinicio debido a un error transitorio.

  • No se incluyen las consultas de servidores sobrecargados o que no responden.

  • OLDEST_ACTIVE_QUERIES no se puede usar en una transacción de lectura y escritura. Incluso en una transacción de solo lectura, ignora la marca de tiempo de la transacción y siempre devuelve los datos actuales en el momento de su ejecución. En casos excepcionales, puede devolver un error ABORTED con resultados parciales. En ese caso, descarta los resultados parciales y vuelve a intentar la consulta.

Usar los datos de consultas activas para solucionar problemas de uso elevado de la CPU

Las estadísticas de consultas y las estadísticas de transacciones proporcionan información útil para solucionar problemas de latencia en una base de datos de Spanner. Estas herramientas proporcionan información sobre las consultas que ya se han completado. Sin embargo, a veces es necesario saber qué se está ejecutando en el sistema. Por ejemplo, supongamos que el uso de la CPU es bastante alto y quieres responder a las siguientes preguntas.

  • ¿Cuántas consultas se están ejecutando en este momento?
  • ¿Qué son estas consultas?
  • ¿Cuántas consultas se están ejecutando durante mucho tiempo (más de 100 segundos)?
  • ¿Qué sesión está ejecutando la consulta?

Con las respuestas a las preguntas anteriores, puedes decidir qué hacer.

  • Elimina la sesión que ejecuta la consulta para obtener una solución inmediata.
  • Mejora el rendimiento de las consultas añadiendo un índice.
  • Reduce la frecuencia de la consulta si está asociada a una tarea periódica en segundo plano.
  • Identifica al usuario o al componente que emite la consulta, que puede no estar autorizado para ejecutarla.

En esta guía, examinaremos nuestras consultas activas y determinaremos qué medidas debemos tomar, si procede.

Recuperar un resumen de las consultas activas

En nuestro ejemplo, observamos un uso de CPU superior al normal, por lo que decidimos ejecutar la siguiente consulta para obtener un resumen de las consultas activas.

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

La consulta devuelve los siguientes resultados.

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

Resulta que tenemos una consulta que se está ejecutando durante más de 100 segundos. Es algo inusual en nuestra base de datos, por lo que queremos investigar más a fondo.

Recuperar una lista de consultas activas

En el paso anterior, hemos determinado que hay una consulta que se está ejecutando durante más de 100 segundos.Para investigar más a fondo, ejecutamos la siguiente consulta para obtener más información sobre las 5 consultas más antiguas que se están ejecutando.

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id,
       query_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 5;

En este ejemplo, hemos ejecutado la consulta el 28 de marzo del 2024 a las 16:44:09 (EDT) aproximadamente y hemos obtenido los siguientes resultados. (Puede que tengas que desplazarte horizontalmente para ver todo el resultado).

start_time text_fingerprint texto text_truncated session_id query_id
2024-03-28 16:44:09.356939+00:00 -2833175298673875968 select * from spanner_sys.oldest_active_queries falso ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw 37190103859320827
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; falso ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ 48946620525959556

La consulta más antigua (huella digital = -2833175298673875968) se destaca en la tabla. Es un CROSS JOIN caro. Decidimos tomar medidas.

Cancelar una consulta costosa

En este ejemplo, hemos encontrado una consulta que estaba ejecutando un CROSS JOIN caro, así que hemos decidido cancelarla. Los resultados de la consulta que hemos recibido en el paso anterior incluían un query_id. Podemos ejecutar el siguiente comando CALL cancel_query(query_id) para GoogleSQL y el comando spanner.cancel_query(query_id) para PostgreSQL para cancelar la consulta.

GoogleSQL 

CALL cancel_query(query_id)

PostgreSQL 

CALL spanner.cancel_query(query_id)

Por ejemplo, en el siguiente ejemplo, la instrucción CALL cancela una consulta con el ID 37190103859320827:

CALL cancel_query('37190103859320827')

Debes consultar la tabla spanner_sys.oldest_active_queries para verificar que la consulta se ha cancelado.

En este tutorial se muestra cómo usar SPANNER_SYS.OLDEST_ACTIVE_QUERIES y SPANNER_SYS.ACTIVE_QUERIES_SUMMARY para analizar nuestras consultas en ejecución y tomar medidas si es necesario en las consultas que contribuyen a un uso elevado de la CPU. Por supuesto, siempre es más barato evitar operaciones costosas y diseñar el esquema adecuado para tus casos prácticos. Para obtener más información sobre cómo crear instrucciones SQL que se ejecuten de forma eficiente, consulta las prácticas recomendadas para SQL.

Siguientes pasos