wshwsh12
commented
1 month ago Reproduce Step:
use test;
create table t(a int, b int);
insert into t values(1,1);
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
create table t2(a int, b int);
set @@tidb_mem_quota_query = 500 << 30;
insert into t2 select row_number() over(), row_number() over() from t;
set @@tidb_mem_quota_query = 500 << 20; // 500MB
desc analyze select * from t2 t1 join t2 t2 on t1.a=t2.a;
ERROR 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=499122182]
desc analyze select * from (select t1.a as a,t2.a as b from t2 t1 join t2 t2 on t1.a=t2.a) t order by a limit 1000;
tidb> desc analyze select * from (select t1.a as a,t2.a as b from t2 t1 join t2 t2 on t1.a=t2.a) t order by a limit 1000;
+--------------------------------+-------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+-------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+----------+---------+
| TopN_14 | 1000.00 | 1000 | root | | time:18.9s, loops:2, RU:27904.914497 | test.t2.a, offset:0, count:1000 | 59.6 KB | 0 Bytes |
| └─HashJoin_19 | 20950548.48 | 16777216 | root | | time:18.4s, loops:16387, build_hash_table:{total:5.75s, fetch:24.7ms, build:5.72s}, probe:{concurrency:5, total:1m34.3s, max:18.9s, probe:1m4.9s, fetch and wait:29.4s} | inner join, equal:[eq(test.t2.a, test.t2.a)] | 826.1 MB | 384 MB |
| ├─TableReader_26(Build) | 16760438.78 | 16777216 | root | | time:63.7ms, loops:16417, cop_task: {num: 492, max: 70.4ms, min: 393.2µs, avg: 11ms, p95: 26.7ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 5.1s, tot_wait: 29.1ms, copr_cache_hit_ratio: 0.00, build_task_duration: 108.8µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:492, total_time:5.41s}} | data:Selection_25 | 6.14 MB | N/A |
| │ └─Selection_25 | 16760438.78 | 16777216 | cop[tikv] | | tikv_task:{proc max:70ms, min:0s, avg: 10.2ms, p80:12ms, p95:26ms, iters:18347, tasks:492}, scan_detail: {total_process_keys: 16777216, total_process_keys_size: 788397830, total_keys: 16777708, get_snapshot_time: 6.77ms, rocksdb: {delete_skipped_count: 12260563, key_skipped_count: 29037779, block: {cache_hit_count: 82881, read_count: 35228, read_byte: 172.7 MB, read_time: 85.4ms}}}, time_detail: {total_process_time: 5.1s, total_suspend_time: 8.89ms, total_wait_time: 29.1ms, total_kv_read_wall_time: 4.83s, tikv_wall_time: 5.19s} | not(isnull(test.t2.a)) | N/A | N/A |
| │ └─TableFullScan_24 | 16777216.00 | 16777216 | cop[tikv] | table:t2 | tikv_task:{proc max:70ms, min:0s, avg: 9.81ms, p80:12ms, p95:26ms, iters:18347, tasks:492} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_23(Probe) | 16760438.78 | 16777216 | root | | time:52.5ms, loops:16416, cop_task: {num: 492, max: 70.9ms, min: 127.9µs, avg: 11ms, p95: 27.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 5.08s, tot_wait: 37.5ms, copr_cache_hit_ratio: 0.03, build_task_duration: 1.4ms, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:492, total_time:5.38s}} | data:Selection_22 | 6.14 MB | N/A |
| └─Selection_22 | 16760438.78 | 16777216 | cop[tikv] | | tikv_task:{proc max:65ms, min:0s, avg: 10.2ms, p80:14ms, p95:27ms, iters:18347, tasks:492}, scan_detail: {total_process_keys: 16753184, total_process_keys_size: 787276390, total_keys: 16753659, get_snapshot_time: 7.67ms, rocksdb: {delete_skipped_count: 12260563, key_skipped_count: 29013747, block: {cache_hit_count: 115529, read_count: 2457, read_byte: 51.4 MB, read_time: 46.2ms}}}, time_detail: {total_process_time: 5.08s, total_suspend_time: 10ms, total_wait_time: 37.5ms, total_kv_read_wall_time: 4.73s, tikv_wall_time: 5.17s} | not(isnull(test.t2.a)) | N/A | N/A |
| └─TableFullScan_21 | 16777216.00 | 16777216 | cop[tikv] | table:t1 | tikv_task:{proc max:65ms, min:0s, avg: 9.7ms, p80:13ms, p95:27ms, iters:18347, tasks:492} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------+-------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+----------+---------+
8 rows in set (18.90 sec)
ti-chi-bot[bot]
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
3. What did you see instead (Required)
4. What is your TiDB version? (Required)