## 模块 2：识别和管理 Runaway Query + 本模块将学习如何识别 Runaway Query ，并采取措施进行管理。 + Runaway Query 可以通过 `EXEC_ELAPSED`（执行耗时）、`PROCESSED_KEYS`（处理键数）和 `RU`（Request Unit）等指标进行识别。 + 针对 Runaway Query 可以采取的操作包括：`DRYRUN`（仅检测不处理）、`COOLDOWN`（冷却）、`KILL`（终止）、`SWITCH GROUP`（切换资源组）。 ### 步骤 1. 连接到 TiDB ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ### 步骤 2. 使用 `sbtest` 数据库并获取部分数据 + 以下查询会生成 5 条查询语句，后续步骤将会用到。 ```sql USE sbtest; SELECT CONCAT( 'SELECT * FROM sbtest1' ' WHERE c = ''', c, ''';' ) AS select_stmt FROM sbtest1 WHERE id IN (1854967, 2546588, 4547856, 6374139, 8574239); ``` ```output +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | select_stmt | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SELECT * FROM sbtest1 WHERE c = '24897543010-03275216457-94900901372-55667311372-53395739269-97843435613-23430060855-92623057954-07277204320-98044593151'; | | SELECT * FROM sbtest1 WHERE c = '47331191657-14185857848-12598233640-45055926060-46507744483-13583166121-53370901880-15584534112-38498490607-22462784430'; | | SELECT * FROM sbtest1 WHERE c = '62445920995-81738237642-75309534239-00447205380-25802359858-30258723001-26103301616-59116803023-21063200722-98626229643'; | | SELECT * FROM sbtest1 WHERE c = '46607730431-76054520626-41005099754-96974085130-47547229605-86689999868-70104955183-39864721160-67802107023-56507121954'; | | SELECT * FROM sbtest1 WHERE c = '31507130228-72374452976-86320120947-15463984680-93359017252-69581927316-99176516606-73015610785-25943837292-41425794962'; | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) ``` ### 步骤 3. 执行查询并观察基线 + 让我们运行步骤 2 输出结果中第一行的查询。**请从步骤 2 的输出中复制该查询。** ```output +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1854967 | 4990337 | 51220495326-57294572228-74180497837-75653544056-24893586547-92403044009-87520022933-03279515577-21319387107-06005857619 | 16034900718-39255886096-26655700027-54928764285-07114323127 | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (1.87 sec) ``` ### 步骤 4. 创建资源组并定义 Runaway Query + 在此资源组（`rg1`）中，将运行超过 1 秒的查询定义为 Runaway Query ，采取的操作是终止（KILL）这些查询。 ```sql CREATE RESOURCE GROUP IF NOT EXISTS rg1 RU_PER_SEC = 500000 QUERY_LIMIT=(EXEC_ELAPSED='1s', ACTION=KILL); ``` ```output Query OK, 0 rows affected (0.07 sec) ``` ```sql SET RESOURCE GROUP `rg1`; SELECT CURRENT_RESOURCE_GROUP(); ``` ```output mysql> SET RESOURCE GROUP `rg1`; Query OK, 0 rows affected (0.00 sec) mysql> SELECT CURRENT_RESOURCE_GROUP(); +--------------------------+ | CURRENT_RESOURCE_GROUP() | +--------------------------+ | rg1 | +--------------------------+ 1 row in set (0.00 sec) ``` ### 步骤 5. 执行 SELECT 和 UPDATE 查询 + 执行步骤 2 输出结果中**第二行**的查询。**请从步骤 2 的输出中复制该查询。** ```output ERROR 1105 (HY000): context canceled ``` + 现在让我们生成一些更新（UPDATE）语句。 ```sql SELECT CONCAT( 'UPDATE sbtest1 SET k = ', k, ' WHERE c = ''', c, ''';' ) AS update_stmt FROM sbtest1 WHERE id IN (1352967, 3646538, 5543556, 9528239); ``` ```output +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | update_stmt | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | UPDATE sbtest1 SET k = 5572807 WHERE c = '66769965526-22288246937-08772982046-39736592708-85565542463-16253515139-87788040417-16516967986-79761698887-90363782788'; | | UPDATE sbtest1 SET k = 4890272 WHERE c = '77751511479-77301323689-60724124182-56634052966-85022579170-95485486629-85511175487-24497764529-19028317287-55284140309'; | | UPDATE sbtest1 SET k = 5044683 WHERE c = '07884020628-55189473747-23156287117-55412424061-23981532176-33586725174-57709714730-45907782845-11012331015-05750365369'; | | UPDATE sbtest1 SET k = 5029615 WHERE c = '58693437664-70153861813-92566944634-27074701047-38913304646-44201618079-59665704931-59737601649-85799104421-65986769359'; | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + 执行上面输出结果中**第一行**的查询。**请从上面的输出中复制该查询。** ```output ERROR 1105 (HY000): context canceled ``` + 从步骤 3 可知，查询大约需要 2 秒。在步骤 4 中，我们将运行超过 1 秒的查询定义为 Runaway Query 并应被终止。因此本步骤的两个查询被如预期终止。你也可能看到另一种典型输出：`ERROR 8253 (HY000): Query execution was interrupted, identified as runaway query`。 ### 步骤 6. 重新定义 Runaway Query + 这次我们基于 `PROCESSED_KEYS`（处理键数）来定义 Runaway Query 。注意：`PROCESSED_KEYS=100` 是一个非常小的值，实际生产环境中通常会设置得更大。 ```sql ALTER RESOURCE GROUP rg1 QUERY_LIMIT=(PROCESSED_KEYS=100, ACTION=KILL); ``` ```sql SET RESOURCE GROUP `rg1`; SELECT CURRENT_RESOURCE_GROUP(); ``` ```output tidb:db1> SET RESOURCE GROUP `rg1`; Query OK, 0 rows affected (0.00 sec) tidb:db1> SELECT CURRENT_RESOURCE_GROUP(); +--------------------------+ | CURRENT_RESOURCE_GROUP() | +--------------------------+ | rg1 | +--------------------------+ 1 row in set (0.00 sec) ``` ### 步骤 7. 再次获取部分数据 + 再次生成一些更新 (UPDATE) 查询。 ```sql SELECT CONCAT( 'UPDATE sbtest1 SET k = ', k, ' WHERE c = ''', c, ''';' ) AS update_stmt FROM sbtest1 WHERE id IN (1352127, 3645438, 5548556, 9526339); ``` ```output +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | update_stmt | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | UPDATE sbtest1 SET k = 4910244 WHERE c = '69548467213-73791607785-10957730918-78445356961-91004694742-80323790863-13593873623-29524209298-71843592568-28064133015'; | | UPDATE sbtest1 SET k = 5182108 WHERE c = '87619334087-12889838231-05423612926-91915277761-80000970680-01099535465-40190779332-69927181259-86609575474-36554130556'; | | UPDATE sbtest1 SET k = 5043778 WHERE c = '46457191195-10255489756-60509274356-37025881706-07298342317-91405313638-74801992298-20461048824-96377089638-25184990994'; | | UPDATE sbtest1 SET k = 5039888 WHERE c = '96519115164-73671707684-33674179113-65994753810-08206447227-36642846222-72994512859-35744412659-41458322631-88308679056'; | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.01 sec) ``` ### 步骤 8. 执行处理键数超过 100 的查询 + 让我们执行第 7 步输出中的**第一条查询语句**。 ```output ERROR 8253 (HY000): Query execution was interrupted, identified as runaway query [ProcessedKeys = 1095654(100)] ``` + 输出信息显示该查询处理了 `1095654` 个键。而在步骤 6 中，我们定义处理键数超过 100 的查询为 Runaway Query 并应被终止。输出结果符合预期。