最早的活跃查询

最早的活跃查询(也称为运行时间最长的查询)是数据库中当前活跃的查询列表,按查询运行的时长排序。深入了解这些查询有助于确定出现系统延迟和 CPU 使用率较高的原因。

Cloud Spanner 提供了一个内置表 SPANNER_SYS.OLDEST_ACTIVE_QUERIES,该表按照开始时间以升序列出运行中的查询,其中包括包含 DML 语句的查询。

如果当前正在运行大量查询,则由于系统对此类数据的集合实施内存限制,结果可能会仅限于总查询的一部分。因此,Cloud Spanner 提供了一个附加表 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY,其中显示所有活跃查询的摘要统计信息。您可以使用 SQL 语句从这两个内置表中检索信息。

在本文中,我们将介绍两个表,并介绍一些使用这些表的示例查询,最后演示如何使用这些表来帮助缓解由活跃查询引发的问题。

可用性

SPANNER_SYS 数据只能通过 SQL 接口获得;例如:

  • Cloud Console 中的数据库查询页面

  • gcloud spanner databases execute-sql 命令

  • executeQuery API

Cloud Spanner 提供的其他单次读取方法不支持 SPANNER_SYS

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES 返回按开始时间排序的活跃查询列表。如果当前正在运行大量查询,则由于 Cloud Spanner 对此类数据的集合实施内存限制,结果可能会仅限于总查询的一部分。如需查看所有活跃查询的摘要统计信息,请参阅 ACTIVE_QUERIES_SUMMARY

表架构

列名 类型 说明
START_TIME TIMESTAMP 查询的开始时间。
TEXT_FINGERPRINT INT64 Fingerprint 是事务中所涉及操作的哈希。
TEXT STRING 查询语句文本。
TEXT_TRUNCATED BOOL 如果 TEXT 字段中的查询文本被截断,则为 true;否则为 false。
SESSION_ID STRING 执行查询的会话的 ID。删除会话 ID 将会取消查询。

示例查询

您可以使用客户端库gcloud 命令行工具或 Cloud Console 运行下列示例 SQL 语句。

列出最早运行的查询

以下查询将返回最早运行的查询列表,按查询的开始时间排序。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC;
start_time text_fingerprint text text_truncated session_id
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; False ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z -9206690983832919848 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000; False 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; False 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; False 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; False ACjbPvYNZ06N2YyvwV0YMlSRBNDtXBqZEK-iAAyPFnFcTmshPvzWkhr034ud7w

列出最热门的 2 个最早运行的查询

以上查询略有不同,本示例会返回前 2 个最早的运行中查询(按查询开始时间排序)。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 2;
查询输出
start_time text_fingerprint text text_truncated session_id
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; False ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z -9206690983832919848 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000; False ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ

ACTIVE_QUERIES_SUMMARY

顾名思义,内置表 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY 显示所有活跃查询的摘要统计信息。如以下架构所示,查询按存在时间划分为三个存储分区(即计数器)- 时长超过 1 秒、超过 10 秒以及超过 100 秒。

表架构

列名 类型 说明
ACTIVE_COUNT INT64 当前正在运行的查询总数。
OLDEST_START_TIME TIMESTAMP 最早运行的查询的开始时间上限。
COUNT_OLDER_THAN_1S INT64 超过 1 秒的查询数。
COUNT_OLDER_THAN_10S INT64 超过 10 秒的查询数。
COUNT_OLDER_THAN_100S INT64 超过 100 秒的查询数。

一个查询可以计入多个存储分区中。例如,如果查询已运行 12 秒,则它将被计入 COUNT_OLDER_THAN_1SCOUNT_OLDER_THAN_10S 中,因为它同时满足两个条件。

示例查询

您可以使用客户端库gcloud 命令行工具或 Cloud Console 运行下列示例 SQL 语句。

检索活跃查询的摘要

以下查询将返回有关运行中查询的摘要统计信息。

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;
查询输出
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

限制

虽然目标是为您提供最全面的数据分析,但在某些情况下,查询不会包含在这些表中返回的数据中。

  • 如果 DML 查询 (UPDATE/INSERT/DELETE) 处于应用 Mutation 阶段,则不会包含这些查询。

  • 如果查询由于暂时性错误而正在重启,则不会包含该查询。

  • 不会包含来自过载服务器或无响应服务器的查询。

  • OLDEST_ACTIVE_QUERIES 不能用于读写事务。即使在只读事务中,它也会忽略事务时间戳,并在执行过程中始终返回当前数据。在极少数情况下,它可能会返回 ABORTED 错误和部分结果;在这种情况下,请舍弃部分结果并再次尝试查询。

使用活跃查询数据排查高 CPU 利用率的问题

在排查 Cloud Spanner 数据库中的延迟问题时,查询统计信息事务统计信息会提供一些有用的信息。这些工具会提供有关已完成查询的信息。但是,有时候需要知道系统中当前正在运行的程序。例如,在 CPU 利用率非常高的情况下,您需要回答以下问题。

  • 目前正在运行多少个查询?
  • 这些查询是什么?
  • 有多少个查询在长时间运行(即时长超过 100 秒)?
  • 哪个会话正在运行查询?

通过回答上述问题,您可以决定执行以下操作。

  • 删除执行查询的会话可立即解决问题。
  • 通过添加索引来提高查询性能。
  • 降低与周期性后台任务关联的查询的频率。
  • 找出发出可能无权执行查询的查询的用户/组件。

在以下演示中,我们将研究活跃的查询,并确定要采取的行动(如果有的话)。

检索当前活跃查询的摘要

在示例场景中,我们发现高于正常 CPU 使用率的情况,因此决定运行以下查询返回活跃查询摘要。

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

该查询会产生以下结果。

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

目前,我们有一个查询的运行时间超过 100 秒。这对于我们的数据库而言并不常见,因此我们需要进一步调查。

检索活跃查询列表

我们在上一步已确定有一个查询的运行时间超过了 100 秒。如需进一步调查,请运行以下查询以返回有关前 5 个最早运行的查询的更多信息。

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

在本示例中,我们在 2020 年 7 月 18 日星期六凌晨 12:54:18(太平洋夏令时间/PDT)运行了查询,随后获得了以下结果。(您可能需要水平滚动才能看到整个输出。)

start_time text_fingerprint text text_truncated session_id
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; False ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z -9206690983832919848 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000; False 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; False 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; False 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; False ACjbPvYNZ06N2YyvwV0YMlSRBNDtXBqZEK-iAAyPFnFcTmshPvzWkhr034ud7w

最早的查询 (fingerprint = -3426560921851907385) 在表格中突出显示。它是费用高昂的 CROSS JOIN。我们决定采取行动。

取消费用高昂的查询

我们发现了一个运行费用高昂的 CROSS JOIN 查询,因此决定取消该查询。上一步中的查询结果包含一个 session_id,这是执行查询的会话的 ID。因此,我们可以运行以下 gcloud spanner databases sessions delete 命令来删除使用此 ID 的会话,进而取消查询。

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

下面将演示如何使用 SPANNER_SYS.OLDEST_ACTIVE_QUERIESSPANNER_SYS.ACTIVE_QUERIES_SUMMARY 来分析正在运行的查询,并在必要时对任何导致高 CPU 使用率的查询采取运行。当然,避免执行费用高昂的操作并为您的使用场景设计合适的架构始终更便宜。如需详细了解如何构建高效运行的 SQL 语句,请参阅 SQL 最佳做法

后续步骤