## Module 2: Identify and Manage Runaway Queries + In this module, we learn how to identify runaway queries and take actions to manage those runaway queries. + Runaway queries can be identified by `EXEC_ELAPSED`, `PROCESSED_KEYS`, and `RU` (Request Unit). + Possible actions against runaway queries includes `DRYRUN` (take no actions), `COOLDOWN`, `KILL`, and `SWITCH GROUP`. ### Step 1. Connect to TiDB ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ### Step 2. Use database `sbtest`, and fetch some data + The following query generates 5 queries, which can be used later. ```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) ``` ### Step 3. Run a query and observe the baseline + Let's run the query from the first line of the output in Step 2. **Please copy the query from the output of Step 2.** ```output +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1854967 | 5293668 | 24897543010-03275216457-94900901372-55667311372-53395739269-97843435613-23430060855-92623057954-07277204320-98044593151 | 39073290773-94355648707-21992331802-01117487022-85887895161 | +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (2.18 sec) ``` ### Step 4. Create resource group with definitions of runaway queries + In this resource group (`rg1`), runaway queries are defined as those queries which run for more than 1 second. The action we take is to kill those queries. ```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) ``` ### Step 5. Run a SELECT and an UPDATE query + Run the query from **the second line** of output from Step 2. **Please copy the query from the output of Step 2.** ```output ERROR 1105 (HY000): context canceled ``` + Let's now generate some update queries. ```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) ``` + Run the query from **the first line** of output above. **Please copy the query from the output above.** ```output ERROR 1105 (HY000): context canceled ``` + From step 3, we know that our query takes approximately 2 seconds. In step 4, we define any query that runs for more than 1 second as a `runaway query`, and should be killed. Those two queries in this step are killed as expected. Another typical output, you might see when a runaway query is killed, is `ERROR 8253 (HY000): Query execution was interrupted, identified as runaway query`. ### Step 6. Redefine runaway queries + This time we will define runaway queries based on `PROCESS_KEYS`. Note that `PROCESSED_KEYS=100` is a very small value, and in a production environment its value is usually much larger. ```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) ``` ### Step 7. Fetch some values from the database + Again, let's generate some updates queries. ```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) ``` ### Step 8. Run a query which processed more than 100 keys + Let's run **the first query** from the output of the Step 7. ```output ERROR 8253 (HY000): Query execution was interrupted, identified as runaway query [ProcessedKeys = 1095654(100)] ``` + The output message tells us that this query processes `1095654` keys. However, in step 6, we define that those queries that process more than 100 keys are runaway queries, and should be killed. The output meets our expectations.