Copr_cache_hit_ratio is not effective in partitioned-raft-kv mode

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

Original topic: partitioned-raft-kv模式下copr_cache_hit_ratio不生效

| username: 人如其名

Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the issue can help the development team address it promptly.
[TiDB Version] v7.0.0
[Impact of the Bug] Repeated queries, and copr_cache_hit_ratio remains 0

[Possible Steps to Reproduce the Issue]
Any table: explain analyze select count(*) from t, repeated queries.

[Observed Unexpected Behavior]
copr_cache_hit_ratio=0
[Expected Behavior]
copr_cache_hit_ratio > 0

mysql> show config where name like 'tikv-client.copr-cache.capacity-mb';
+------+---------------------+------------------------------------+-------+
| Type | Instance            | Name                               | Value |
+------+---------------------+------------------------------------+-------+
| tidb | 192.168.31.201:4000 | tikv-client.copr-cache.capacity-mb | 1000  |
+------+---------------------+------------------------------------+-------+
1 row in set (0.01 sec)
mysql> show config where name like 'storage.engine';
+------+----------------------+----------------+----------+
| Type | Instance             | Name           | Value    |
+------+----------------------+----------------+----------+
| tikv | 192.168.31.201:20160 | storage.engine | raft-kv2 |
+------+----------------------+----------------+----------+
1 row in set (0.01 sec)
mysql> explain analyze select count(*) from tpch100.customer;
+----------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| id                         | estRows     | actRows  | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                               | operator info                    | memory    | disk |
+----------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| HashAgg_11                 | 1.00        | 1        | root      |                | time:19.7s, loops:2, RRU:64191.095707, WRU:0.000000, partial_worker:{wall_time:19.707367785s, concurrency:5, task_num:1, tot_wait:1m38.535453168s, tot_exec:44.681µs, tot_time:1m38.535514877s, max:19.707113388s, p95:19.707113388s}, final_worker:{wall_time:0s, concurrency:5, task_num:1, tot_wait:1m38.535629349s, tot_exec:28.337µs, tot_time:1m38.535658973s, max:19.707135779s, p95:19.707135779s}   | funcs:count(Column#10)->Column#9 | 11.1 KB   | N/A  |
| └─TableReader_12           | 1.00        | 34       | root      |                | time:19.7s, loops:2, cop_task: {num: 34, max: 13s, min: 4.9s, avg: 7.77s, p95: 12.5s, max_proc_keys: 467300, p95_proc_keys: 467198, tot_proc: 2m54s, tot_wait: 82.3ms, rpc_num: 34, rpc_time: 4m24.2s, copr_cache_hit_ratio: 0.00, build_task_duration: 683.4µs, max_distsql_concurrency: 15}                                                                                                                | data:HashAgg_5                   | 404 Bytes | N/A  |
|   └─HashAgg_5              | 1.00        | 34       | cop[tikv] |                | tikv_task:{proc max:13s, min:4.89s, avg: 7.77s, p80:9.25s, p95:12.5s, iters:14662, tasks:34}, scan_detail: {total_process_keys: 15000000, total_process_keys_size: 405000000, total_keys: 15000034, get_snapshot_time: 6.36ms, rocksdb: {key_skipped_count: 15000000, block: {cache_hit_count: 83, read_count: 99868, read_byte: 1.60 GB, read_time: 50.2s}}}                                                | funcs:count(1)->Column#10        | N/A       | N/A  |
|     └─TableFullScan_10     | 15000000.00 | 15000000 | cop[tikv] | table:customer | tikv_task:{proc max:13s, min:4.89s, avg: 7.77s, p80:9.25s, p95:12.5s, iters:14662, tasks:34}                                                                                                                                                                                                                                                                                                                 | keep order:false, stats:pseudo   | N/A       | N/A  |
+----------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
4 rows in set (19.71 sec)

mysql> explain analyze select count(*) from tpch100.customer;
+----------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| id                         | estRows     | actRows  | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                             | operator info                    | memory    | disk |
+----------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| HashAgg_11                 | 1.00        | 1        | root      |                | time:950.4ms, loops:2, RRU:8918.221802, WRU:0.000000, partial_worker:{wall_time:950.366723ms, concurrency:5, task_num:1, tot_wait:4.751215875s, tot_exec:21.739µs, tot_time:4.751242336s, max:950.25069ms, p95:950.25069ms}, final_worker:{wall_time:0s, concurrency:5, task_num:1, tot_wait:4.751279297s, tot_exec:10.061µs, tot_time:4.751291593s, max:950.264823ms, p95:950.264823ms}   | funcs:count(Column#10)->Column#9 | 11.1 KB   | N/A  |
| └─TableReader_12           | 1.00        | 34       | root      |                | time:950.2ms, loops:2, cop_task: {num: 34, max: 666ms, min: 199.8ms, avg: 376.9ms, p95: 643.5ms, max_proc_keys: 467300, p95_proc_keys: 467198, tot_proc: 8.19s, tot_wait: 32.7ms, rpc_num: 34, rpc_time: 12.8s, copr_cache_hit_ratio: 0.00, build_task_duration: 29.8µs, max_distsql_concurrency: 15}                                                                                      | data:HashAgg_5                   | 389 Bytes | N/A  |
|   └─HashAgg_5              | 1.00        | 34       | cop[tikv] |                | tikv_task:{proc max:666ms, min:198ms, avg: 370.4ms, p80:427ms, p95:629ms, iters:14662, tasks:34}, scan_detail: {total_process_keys: 15000000, total_process_keys_size: 405000000, total_keys: 15000034, get_snapshot_time: 17.2ms, rocksdb: {key_skipped_count: 15000000, block: {cache_hit_count: 99951}}}                                                                                | funcs:count(1)->Column#10        | N/A       | N/A  |
|     └─TableFullScan_10     | 15000000.00 | 15000000 | cop[tikv] | table:customer | tikv_task:{proc max:666ms, min:198ms, avg: 369ms, p80:427ms, p95:629ms, iters:14662, tasks:34}                                                                                                                                                                                                                                                                                             | keep order:false, stats:pseudo   | N/A       | N/A  |
+----------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
4 rows in set (0.95 sec)

mysql> explain analyze select count(*) from tpch100.customer;
+----------------------------+-------------+----------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| id                         | estRows     | actRows  | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                       | operator info                    | memory    | disk |
+----------------------------+-------------+----------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| HashAgg_11                 | 1.00        | 1        | root      |                | time:928.9ms, loops:2, RRU:8790.317043, WRU:0.000000, partial_worker:{wall_time:928.874944ms, concurrency:5, task_num:1, tot_wait:4.643754282s, tot_exec:17.086µs, tot_time:4.643777282s, max:928.757591ms, p95:928.757591ms}, final_worker:{wall_time:928.912445ms, concurrency:5, task_num:1, tot_wait:4.64387552s, tot_exec:12.804µs, tot_time:4.64389074s, max:928.797034ms, p95:928.797034ms}   | funcs:count(Column#10)->Column#9 | 11.1 KB   | N/A  |
| └─TableReader_12           | 1.00        | 34       | root      |                | time:928.7ms, loops:2, cop_task: {num: 34, max: 588.2ms, min: 245.5ms, avg: 365.4ms, p95: 581.3ms, max_proc_keys: 467300, p95_proc_keys: 467198, tot_proc: 7.81s, tot_wait: 21.5ms, rpc_num: 34, rpc_time: 12.4s, copr_cache_hit_ratio: 0.00, build_task_duration: 31.8µs, max_distsql_concurrency: 15}                                                                                              | data:HashAgg_5                   | 383 Bytes | N/A  |
|   └─HashAgg_5              | 1.00        | 34       | cop[tikv] |                | tikv_task:{proc max:557ms, min:245ms, avg: 354.9ms, p80:400ms, p95:551ms, iters:14662, tasks:34}, scan_detail: {total_process_keys: 15000000, total_process_keys_size: 405000000, total_keys: 15000034, get_snapshot_time: 480.2µs, rocksdb: {key_skipped_count: 15000000, block: {cache_hit_count: 99951}}}                                                                                         | funcs:count(1)->Column#10        | N/A       | N/A  |
|     └─TableFullScan_10     | 15000000.00 | 15000000 | cop[tikv] | table:customer | tikv_task:{proc max:551ms, min:244ms, avg: 353.1ms, p80:399ms, p95:547ms, iters:14662, tasks:34}                                                                                                                                                                                                                                                                                                     | keep order:false, stats:pseudo   | N/A       | N/A  |
+----------------------------+-------------+----------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+------+
4 rows in set (0.93 sec)

The current database only has this query, and after repeated queries, copr_cache_hit_ratio remains 0.

| username: aytrack | Original post link

It’s a bug. Follow this issue for updates: [Dynamic Regions] coprocessor cache is not working due to miss applied index in snapshot · Issue #14570 · tikv/tikv · GitHub

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.