tidb对执行数据落盘算子的参数设置后不生效

[mono (Hacker 3 Lu Tkd Re)]

【 TiDB 使用环境】测试
【 TiDB 版本】6.5.0

mysql> explain analyze select count(*) from t1 a join t1 b on a.c1=b.c1 group by a.c1,b.c1 order by a.c1;
ERROR 1105 (HY000): Out Of Memory Quota![conn_id=7800340107721965973]

show variables like ‘tidb_mem_quota_query’;
±---------------------±--------+
| Variable_name | Value |
±---------------------±--------+
| tidb_mem_quota_query | 2097152 |
±---------------------±--------+

mysql> show variables like ‘%tmp%’;
±---------------------------------±------------+
| Variable_name | Value |
±---------------------------------±------------+
| default_tmp_storage_engine | InnoDB |
| internal_tmp_disk_storage_engine | |
| max_tmp_tables | 32 |
| slave_load_tmpdir | /var/tmp/ |
| tidb_enable_tmp_storage_on_oom | ON |
| tidb_tmp_table_max_size | 21474836480 |
| tmp_table_size | 16777216 |
| tmpdir | /var/tmp/ |
±---------------------------------±------------+
mysql> show variables like ‘%exec%conc%’;
±--------------------------±------+
| Variable_name | Value |
±--------------------------±------+
| tidb_executor_concurrency | 1 |
±--------------------------±------+

[人如其名]

猜测c1字段重复值太多，创建一个hashtable都放不下，所以oom了。内存给大点啊，或者你c1别我那么多重复值，实际场景一般不会这样数据有笛卡尔积的。–这里更正下，hashtable可以落盘，只是如果一行中匹配的行太多会导致列表过大，只是一行记录匹配多行记录（指针），整个匹配过程是不落盘的，这种列表过大场景导致内存OOM很少，因为里面都是行指针而已。

[mono (Hacker 3 Lu Tkd Re)]

内存调大，是可以的。但看不到数据落盘的情况。我是想测试当内存不足，数据落盘的情况。谢谢了！

[人如其名]

很容易，弄两个不同的表，右侧关联字段唯一或者重复值低

[xfworld (魔幻之翼)]

还有其他的参数也需要配置的，如果配置错了，就不会生效了，参考这篇文档：

https://docs.pingcap.com/zh/tidb/stable/configure-memory-usage

[mono (Hacker 3 Lu Tkd Re)]

t1.c1是主键列。执行了这么长时间，但还是看不到使用的硬盘空间 。

[人如其名]

只要做分组聚合字段数据的基数足够多，就能落盘。
如果内存给的太小有可能还没达到分组聚合这里就被其它地方占用内存过多导致oom-killer了。比如从tidb获取的数据缓存过来的占用内存多出发杀死会话。看我这里可以落盘，需要注意的是，落盘会导致性能很慢：

mysql> explain analyze select count(*) from orders group by o_custkey;
+---------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+----------+---------+
| id                        | estRows     | actRows  | task      | access object | execution info                                                                                                                                                                                                                                                                                         | operator info                                                     | memory   | disk    |
+---------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+----------+---------+
| HashAgg_9                 | 4008704.67  | 2999660  | root      |               | time:10m36.6s, loops:2931                                                                                                                                                                                                                                                                              | group by:tpch.orders.o_custkey, funcs:count(Column#11)->Column#10 | 395.5 MB | 10.7 GB |
| └─TableReader_10          | 4008704.67  | 37114427 | root      |               | time:77.6ms, loops:1215, cop_task: {num: 1214, max: 36.6ms, min: 554.7µs, avg: 12.4ms, p95: 23.1ms, max_proc_keys: 51200, p95_proc_keys: 51200, tot_proc: 13.1s, tot_wait: 42ms, rpc_num: 1214, rpc_time: 15s, copr_cache: disabled, build_task_duration: 58.1µs, max_distsql_concurrency: 15}         | data:HashAgg_5                                                    | 12.4 MB  | N/A     |
|   └─HashAgg_5             | 4008704.67  | 37114427 | cop[tikv] |               | tikv_task:{proc max:34ms, min:0s, avg: 11ms, p80:18ms, p95:21ms, iters:36586, tasks:1214}, scan_detail: {total_process_keys: 37438464, total_process_keys_size: 5686354813, total_keys: 37439678, get_snapshot_time: 66.8ms, rocksdb: {key_skipped_count: 37438464, block: {cache_hit_count: 199034}}} | group by:tpch.orders.o_custkey, funcs:count(1)->Column#11         | N/A      | N/A     |
|     └─TableFullScan_8     | 37327872.00 | 37438464 | cop[tikv] | table:orders  | tikv_task:{proc max:29ms, min:0s, avg: 8.75ms, p80:15ms, p95:17ms, iters:36586, tasks:1214}                                                                                                                                                                                                            | keep order:false                                                  | N/A      | N/A     |
+---------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+----------+---------+
4 rows in set (10 min 38.10 sec)

[mono (Hacker 3 Lu Tkd Re)]

我再测试看吧。多谢！