## Module 3: Monitoring Potential Runaway Queries

+ From module 2, we know that it takes some time for a runaway query to be identified. For example, if we set `EXEC_ELAPSED='10s'`, it takes at least 10 seconds for each runaway query to be identified. Too many yet to be identified runaway queries may exhaust system resources.
+ By using `WATCH` feature, TiDB is able to identify runaway queries even before it starts running.
+ Options for `WATCH` feature includes `EXACT`, `SIMILAR` (matching SQL digest), and `PLAN` (matching plan digest).

### Step 1. Redefine the recourse group

+ In the following query, we redefine the resource group. Compared to the resource groups in module 2, this time `WATCH=SIMILAR` and `DURATION='100m'` are added. It means that, once a query is identified as a runaway query, all similar queries (queries with the same SQL digest) will be categorized as runaway queries without executing. The watch duration is 100 minutes.

  ```sql
  ALTER RESOURCE GROUP rg1 QUERY_LIMIT=(PROCESSED_KEYS=100, ACTION=KILL, WATCH=SIMILAR DURATION='100m');
  ```

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

### Step 2. Let's now generate some queries

  ```sql
  USE sbtest;
  SELECT
    CONCAT(
      'UPDATE sbtest1 SET k = ',
      k,
      ' WHERE c = ''',
      c,
      ''';'
    ) AS update_stmt
  FROM sbtest1
  WHERE id IN (1123127, 3852438, 5964556, 9475339);
  ```

  ```output
  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | update_stmt                                                                                                                                                         |
  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | UPDATE sbtest1 SET k = 5017151 WHERE c = '84493442613-55715520812-07466942581-87906122717-51164395568-98852954940-85704352045-80573166498-09592001496-94838080154'; |
  | UPDATE sbtest1 SET k = 4473488 WHERE c = '16343179582-65765105184-12762861962-64885861338-91163734747-54197012107-93125617766-13310713443-81881465217-52031359817'; |
  | UPDATE sbtest1 SET k = 5018359 WHERE c = '30686701254-95654597113-74779030530-51744141684-25702975952-73545252771-00587103499-70903471471-10016980123-39589338114'; |
  | UPDATE sbtest1 SET k = 4978938 WHERE c = '91059747631-65344828194-11158241479-43561595237-60087824823-10114555067-54188804463-85679024352-56990670474-10055950458'; |
  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4 rows in set (0.00 sec)
  ```

### Step 3. Run a query

+ Let run **the first query** from the output of Step 2.
+ In this step, we run a query, which will be classified as a runaway query, since it processes 1095654 keys.
+ Note that, this time the query is actually executed, before it is terminated. For all later queries with the same SQL digest within 100 minutes time window, they will be categorized as runaway queries without execution.

  ```output
  ERROR 8253 (HY000): Query execution was interrupted, identified as runaway query [ProcessedKeys = 1095654(100)]
  ```

### Step 4. Run a query with similar pattern

+ In this step, we run a query which has the same pattern as the one in step 3, except that the `c` value is updated to `abc0`. There should be no such record in our table.
+ The execution output is self-explanatory: `Quarantined and interrupted because of being in runaway watch list`. The query has the same SQL digest with the query in step 3. As a result, it is quarantined without execution.

  ```sql
  UPDATE sbtest1 SET k = 4751268 WHERE c = 'abc0';
  ```

  ```output
  ERROR 8254 (HY000): Quarantined and interrupted because of being in runaway watch list
  ```

### Step 5. Check the runaway query watch list

+ In the following command, we list the queries in the watch list.
+ The result shows that, in the resource group `rg1`, all queries whose SQL digest is `a7a2fbb8a227456777fd6d3dd68dee4089cb05413ccac0f3731d2fa538a09dea` will be killed in the time window from `2025-07-15 01:13:54` to `2025-07-15 02:53:54`.

  ```sql
  SELECT * from information_schema.runaway_watches ORDER BY id\G
  ```

  ```output
  tidb:db1> SELECT * from information_schema.runaway_watches ORDER BY id\G
  *************************** 1. row ***************************
                   ID: 1
  RESOURCE_GROUP_NAME: rg1
           START_TIME: 2025-07-15 01:13:54
             END_TIME: 2025-07-15 02:53:54
                WATCH: Similar
           WATCH_TEXT: a7a2fbb8a227456777fd6d3dd68dee4089cb05413ccac0f3731d2fa538a09dea
               SOURCE: 10.90.2.141:4000
               ACTION: Kill
                 RULE: ProcessedKeys = 1095654(100)
  ```

### Step 6. Add a query to WATCH list manually

+ Sometimes, you need to manually add a query to the watch list. Some queries are obviously harmful to the system, such as a `SELECT *` query.

  ```sql
  QUERY WATCH ADD RESOURCE GROUP rg1 ACTION KILL SQL TEXT SIMILAR TO 'select * from sbtest1';
  ```

  ```output
  tidb:db1> QUERY WATCH ADD RESOURCE GROUP rg1 ACTION KILL SQL TEXT SIMILAR TO 'select * from sbtest1';
  +----------+
  | WATCH_ID |
  +----------+
  |        2 |
  +----------+
  1 row in set (0.00 sec)
  ```

### Step 7. Run the `SELECT *` query

+ Again, from the output, you can find that the query is quarantined and killed. It is never executed.

  ```sql
  SELECT * FROM sbtest1;
  ```

  ```output
  tidb:db1> SELECT * FROM sbtest1;
  ERROR 8254 (HY000): Quarantined and interrupted because of being in runaway watch list
  ```

### Step 8. Check the WATCH query list

+ Check the runaway query watch list again, you can find currently there are two queries on the watch list.

  ```sql
  SELECT * from information_schema.runaway_watches ORDER BY id\G
  ```

  ```output
  tidb:db1> SELECT * from information_schema.runaway_watches ORDER BY id\G
  *************************** 1. row ***************************
                  ID: 1
  RESOURCE_GROUP_NAME: rg1
          START_TIME: 2025-07-15 01:13:54
            END_TIME: 2025-07-15 02:53:54
                WATCH: Similar
          WATCH_TEXT: a7a2fbb8a227456777fd6d3dd68dee4089cb05413ccac0f3731d2fa538a09dea
              SOURCE: 10.90.2.141:4000
              ACTION: Kill
                RULE: ProcessedKeys = 1095654(100)
  *************************** 2. row ***************************
                   ID: 2
  RESOURCE_GROUP_NAME: rg1
           START_TIME: 2025-07-15 01:17:38
             END_TIME: UNLIMITED
                WATCH: Similar
           WATCH_TEXT: 35c978d76693ff8e8db1c5cb395ed9a09408c04deb295e4e78dbadedccd18fd6
               SOURCE: manual
               ACTION: Kill
                 RULE: None
  2 rows in set (0.00 sec)
  ```