How to Understand RU in Resource Control

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何理解资源管控 (Resource Control) 中的RU

| username: 人如其名

【 TiDB Usage Environment】 Poc
【 TiDB Version】8.0.11-TiDB-v8.1.0
In the official documentation on resource control: 使用资源管控 (Resource Control) 实现资源隔离 | PingCAP 文档中心, the Request Unit section is introduced as follows:

  • Does performing CPU operations (sorting) on the tidb-server side increase RU consumption?
# TiKV scans the entire table, topN is not pushed down, a large amount of data is sorted on the tidb-server, and the impact of high CPU consumption on RU is observed.
mysql> explain analyze select c_name from customer order by C_ADDRESS limit 1000000,10;
+----------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+---------+
| id                         | estRows    | actRows | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info             | memory   | disk    |
+----------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+---------+
| Limit_9                    | 10.00      | 10      | root      |                | time:2.8s, loops:2, RU:4873.498317                                                                                                                                                                                                                                                                                                                                                                                                                         | offset:1000000, count:10  | N/A      | N/A     |
| └─Sort_10                  | 1000010.00 | 1000010 | root      |                | time:2.8s, loops:977                                                                                                                                                                                                                                                                                                                                                                                                                                       | tpch10.customer.c_address | 108.5 MB | 0 Bytes |
|   └─TableReader_13         | 1500000.00 | 1500000 | root      |                | time:243.8ms, loops:1469, cop_task: {num: 54, max: 42.8ms, min: 467.2µs, avg: 13.3ms, p95: 37.7ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 571.4ms, tot_wait: 3.41ms, copr_cache: disabled, build_task_duration: 11µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:54, total_time:719.3ms}}                                                                                                                                          | data:TableFullScan_12     | 8.47 MB  | N/A     |
|     └─TableFullScan_12     | 1500000.00 | 1500000 | cop[tikv] | table:customer | tikv_task:{proc max:38ms, min:0s, avg: 9.52ms, p80:16ms, p95:33ms, iters:1679, tasks:54}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1500054, get_snapshot_time: 1.33ms, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 10250}}}, time_detail: {total_process_time: 571.4ms, total_suspend_time: 1.45ms, total_wait_time: 3.41ms, total_kv_read_wall_time: 514ms, tikv_wall_time: 584.3ms} | keep order:false          | N/A      | N/A     |
+----------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+---------+
4 rows in set (2.80 sec)

# TiKV scans the entire table, topN is not pushed down, a small amount of data is sorted on the tidb-server, and the impact of low CPU consumption on RU is observed.
mysql> explain analyze select c_name from customer order by C_ADDRESS limit 10,10;
+----------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+---------+
| id                         | estRows    | actRows | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info             | memory   | disk    |
+----------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+---------+
| Limit_9                    | 10.00      | 10      | root      |                | time:2.49s, loops:2, RU:4881.730370                                                                                                                                                                                                                                                                                                                                                                                                                        | offset:10, count:10       | N/A      | N/A     |
| └─Sort_10                  | 20.00      | 20      | root      |                | time:2.49s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                        | tpch10.customer.c_address | 108.5 MB | 0 Bytes |
|   └─TableReader_13         | 1500000.00 | 1500000 | root      |                | time:234ms, loops:1469, cop_task: {num: 54, max: 47.7ms, min: 574.8µs, avg: 13.6ms, p95: 45.2ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 596.1ms, tot_wait: 3.83ms, copr_cache: disabled, build_task_duration: 9.68µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:54, total_time:734.4ms}}                                                                                                                                          | data:TableFullScan_12     | 5.70 MB  | N/A     |
|     └─TableFullScan_12     | 1500000.00 | 1500000 | cop[tikv] | table:customer | tikv_task:{proc max:42ms, min:0s, avg: 9.59ms, p80:15ms, p95:38ms, iters:1679, tasks:54}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1500054, get_snapshot_time: 1.14ms, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 10250}}}, time_detail: {total_process_time: 596.1ms, total_suspend_time: 1.43ms, total_wait_time: 3.83ms, total_kv_read_wall_time: 518ms, tikv_wall_time: 607.5ms} | keep order:false          | N/A      | N/A     |
+----------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+----------+---------+
4 rows in set (2.49 sec)

From the tests, it appears that a large amount of data sorting is done on the tidb-server, but the RU consumption remains basically unchanged.

  • Does performing CPU operations (sorting) on the tikv side increase RU consumption?
# TiKV scans the entire table, topN is pushed down, a large amount of sorting is done in TiKV, and a small amount of sorting is done on the tidb side. Observe the impact of high CPU consumption on RU in TiKV.
mysql> explain analyze select  /*+ LIMIT_TO_COP() */ c_name from customer order by C_ADDRESS limit 1000000,10;
+------------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+------+
| id                           | estRows    | actRows | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info                                       | memory  | disk |
+------------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+------+
| Projection_7                 | 10.00      | 10      | root      |                | time:3.15s, loops:2, RU:4871.174181, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                       | tpch10.customer.c_name                              | 1.49 KB | N/A  |
| └─TopN_8                     | 10.00      | 10      | root      |                | time:3.15s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                        | tpch10.customer.c_address, offset:1000000, count:10 | 90.4 MB | N/A  |
|   └─TableReader_15           | 1000010.00 | 1500000 | root      |                | time:170ms, loops:1469, cop_task: {num: 54, max: 48.3ms, min: 508.9µs, avg: 12.3ms, p95: 41.7ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 564.4ms, tot_wait: 3.11ms, copr_cache: disabled, build_task_duration: 9.48µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:54, total_time:661.4ms}}                                                                                                                                          | data:TopN_14                                        | 11.3 MB | N/A  |
|     └─TopN_14                | 1000010.00 | 1500000 | cop[tikv] |                | tikv_task:{proc max:42ms, min:0s, avg: 9.48ms, p80:15ms, p95:38ms, iters:1679, tasks:54}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1500054, get_snapshot_time: 1.09ms, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 10250}}}, time_detail: {total_process_time: 564.4ms, total_suspend_time: 27.5ms, total_wait_time: 3.11ms, total_kv_read_wall_time: 511ms, tikv_wall_time: 601.9ms} | tpch10.customer.c_address, offset:0, count:1000010  | N/A     | N/A  |
|       └─TableFullScan_13     | 1500000.00 | 1500000 | cop[tikv] | table:customer | tikv_task:{proc max:42ms, min:0s, avg: 9.46ms, p80:15ms, p95:38ms, iters:1679, tasks:54}                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                    | N/A     | N/A  |
+------------------------------+------------+---------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+------+
5 rows in set (3.15 sec)

# TiKV scans the entire table, topN is pushed down, a small amount of sorting is done in TiKV, and a small amount of sorting is done on the tidb side. Observe the impact of low CPU consumption on RU in TiKV.
mysql> explain analyze select  /*+ LIMIT_TO_COP() */ c_name from customer order by C_ADDRESS limit 10,10;
+------------------------------+------------+---------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+
| id                           | estRows    | actRows | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                    | operator info                                  | memory  | disk |
+------------------------------+------------+---------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+
| Projection_7                 | 10.00      | 10      | root      |                | time:278.2ms, loops:2, RU:4895.388275, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                            | tpch10.customer.c_name                         | 1.54 KB | N/A  |
| └─TopN_8                     | 10.00      | 10      | root      |                | time:278.2ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                             | tpch10.customer.c_address, offset:10, count:10 | 1.59 KB | N/A  |
|   └─TableReader_15           | 20.00      | 60      | root      |                | time:278.2ms, loops:3, cop_task: {num: 3, max: 278.1ms, min: 215.6ms, avg: 237.7ms, p95: 278.1ms, max_proc_keys: 603265, p95_proc_keys: 603265, tot_proc: 709.8ms, tot_wait: 513.1µs, copr_cache: disabled, build_task_duration: 31.9µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:3, total_time:713.2ms}}                                                                                                                                              | data:TopN_14                                   | 2.75 KB | N/A  |
|     └─TopN_14                | 20.00      | 60      | cop[tikv] |                | tikv_task:{proc max:277ms, min:214ms, avg: 236.3ms, p80:277ms, p95:277ms, iters:1466, tasks:3}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1500003, get_snapshot_time: 374µs, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 9995}}}, time_detail: {total_process_time: 709.8ms, total_suspend_time: 1.52ms, total_wait_time: 513.1µs, total_kv_read_wall_time: 549ms, tikv_wall_time: 712.2ms}   | tpch10.customer.c_address, offset:0, count:20  | N/A     | N/A  |
|       └─TableFullScan_13     | 1500000.00 | 1500000 | cop[tikv] | table:customer | tikv_task:{proc max:212ms, min:146ms, avg: 183ms, p80:212ms, p95:212ms, iters:1466, tasks:3}                                                                                                                                                                                                                                                                                                                                                                      | keep order:false                               | N/A     | N/A  |
+------------------------------+------------+---------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+
5 rows in set (0.28 sec)

From the tests, it appears that a large amount of data sorting is done on the tikv side, but the RU consumption remains basically unchanged.

  • Analyze whether it is only related to the requests from the coprocessor to the storage layer on the tikv side
# TiKV scans the entire table, does not sort, performs predicate filtering, and no records are returned after filtering.
mysql> explain analyze select count(*) from customer where c_name='xxx';
+------------------------------+------------+---------+-----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------+------+
| id                           | estRows    | actRows | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                      | operator info                     | memory    | disk |
+------------------------------+------------+---------+-----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------+------+
| StreamAgg_20                 | 1.00       | 1       | root      |                | time:235.8ms, loops:2, RU:4867.325278                                                                                                                                                                                                                                                                                                                                                                                                                               | funcs:count(Column#11)->Column#9  | 388 Bytes | N/A  |
| └─TableReader_21             | 1.00       | 0       | root      |                | time:235.8ms, loops:1, cop_task: {num: 3, max: 235.8ms, min: 196.1ms, avg: 209.6ms, p95: 235.8ms, max_proc_keys: 603265, p95_proc_keys: 603265, tot_proc: 625.6ms, tot_wait: 445.7µs, copr_cache: disabled, build_task_duration: 11.4µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:3, total_time:628.8ms}}                                                                                                                                                | data:StreamAgg_9                  | 367 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00       | 0       | cop[tikv] |                | tikv_task:{proc max:235ms, min:194ms, avg: 208.7ms, p80:235ms, p95:235ms, iters:1466, tasks:3}, scan_detail: {total_process_keys: 1500000, total_process_keys_size: 305225771, total_keys: 1500003, get_snapshot_time: 362.6µs, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 9995}}}, time_detail: {total_process_time: 625.6ms, total_suspend_time: 1.03ms, total_wait_time: 445.7µs, total_kv_read_wall_time: 563ms, tikv_wall_time: 627.7ms}   | funcs:count(1)->Column#11         | N/A       | N/A  |
|     └─Selection_19           | 1.00       | 0       | cop[tikv] |                | tikv_task:{proc max:235ms, min:194ms, avg: 208.7ms, p80:235ms, p95:235ms, iters:1466, tasks:3}                                                                                                                                                                                                                                                                                                                                                                      | eq(tpch10.customer.c_name, "xxx") | N/A       | N/A  |
|       └─TableFullScan_18     | 1500000.00 | 1500000 | cop[tikv] | table:customer | tikv_task:{proc max:205ms, min:178ms, avg: 187.7ms, p80:205ms, p95:205ms, iters:1466, tasks:3}                                                                                                                                                                                                                                                                                                                                                                      | keep order:false                  | N/A       | N/A  |
+------------------------------+------------+---------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| username: ziptoam | Original post link

Pay attention and study, it’s a very profound question!