TiDB's Parameter Settings for Executing Data Spill Operators Are Ineffective

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

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

| username: mono

[TiDB Usage Environment] Test
[TiDB Version] 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 |
±--------------------------±------+

| username: 人如其名 | Original post link

It is speculated that there are too many duplicate values in the c1 field, and a hashtable cannot accommodate them all, leading to an OOM. Increase the memory, or reduce the number of duplicate values in c1. In actual scenarios, data generally doesn’t have Cartesian products. – To correct this, the hashtable can spill to disk, but if too many rows match in one row, it will cause the list to be too large. The entire matching process does not spill to disk, and such scenarios where the list is too large rarely cause memory OOM because it only contains row pointers.

| username: mono | Original post link

Increasing the memory is possible, but I can’t see the data being written to disk. I want to test the situation when the memory is insufficient and the data is written to disk. Thank you!

| username: 人如其名 | Original post link

It’s very easy, just create two different tables, with the right-side associated field being unique or having low duplicate values.

| username: xfworld | Original post link

There are other parameters that also need to be configured. If configured incorrectly, they will not take effect. Refer to this document:

| username: mono | Original post link

t1.c1 is the primary key column. It has been running for such a long time, but the used disk space is still not visible.

| username: 人如其名 | Original post link

As long as the cardinality of the grouped aggregate field data is large enough, it can be written to disk. If the memory allocated is too small, it might be used up by other processes before reaching the grouped aggregation, causing an OOM-killer. For example, the memory occupied by the data cached from TiDB might trigger the session to be killed. As you can see here, it can be written to disk, but note that writing to disk will significantly slow down performance:

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)
| username: mono | Original post link

I’ll test it again. Thanks a lot! :handshake:

| username: system | Original post link

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