【详述】两张表inner join关联的sql运行时，不插入另外一张表单纯count可以跑出来，但是将结果插入另外一张表就会报错，经过过次尝试，需要将query_mem_limit设置为100GiB才能稳定运行成功，但是数据量没有这么大，如何定位内存消耗在哪里？
mysql> show VARIABLES like '%mem_limit%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| load_mem_limit | 0 |
| query_mem_limit | 0 |
+-----------------+-------+
2 rows in set (0.00 sec)
mysql> insert into liqingzhong.test2
-> select
-> start_time
-> ,phone_number
-> ,apn
-> ,domain_Name
-> ,ipaddress
-> ,rcode
-> ,'dns'
-> ,FROM_UNIXTIME(end_time/1000000)
-> from ods_dns a
-> inner join
-> ods_wan b
-> on a.haoduan = b.haoduan
-> where start_time >= '2024-01-30 10:00:00'
-> and start_time < '2024-01-30 10:15:00'
-> and phone_number !='';
ERROR 1064 (HY000): Memory of Query47341d92-bf3a-11ee-89c4-bc169519a30e exceed limit. try consume:1342177280 Used: 3044650384, Limit: 4294967296. Mem usage has exceed the limit of single query, You can change the limit by set session variable query_mem_limit.
mysql> select count(1) from (
-> select
-> start_time
-> ,phone_number
-> ,apn
-> ,domain_Name
-> ,ipaddress
-> ,rcode
-> ,'dns'
-> ,FROM_UNIXTIME(end_time/1000000)
-> from ods_dns a
-> inner join
-> ods_wan b
-> on a.haoduan = b.haoduan
-> where start_time >= '2024-01-30 10:00:00'
-> and start_time < '2024-01-30 10:15:00'
-> and phone_number !='') c;
+----------+
| count(1) |
+----------+
| 785156 |
+----------+
1 row in set (7.30 sec)
mysql> set query_mem_limit=107374182400;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into liqingzhong.test2
-> select
-> start_time
-> ,phone_number
-> ,apn
-> ,domain_Name
-> ,ipaddress
-> ,rcode
-> ,'dns'
-> ,FROM_UNIXTIME(end_time/1000000)
-> from ods_dns a
-> inner join
-> ods_wan b
-> on a.haoduan = b.haoduan
-> where start_time >= '2024-01-30 10:00:00'
-> and start_time < '2024-01-30 10:15:00'
-> and phone_number !='';
Query OK, 785156 rows affected (1 min 50.96 sec)
{'label':'insert_1aa9e449-bf4e-11ee-89c4-bc169519a30e', 'status':'VISIBLE', 'txnId':'3249589'}
mysql>
【是否存算分离】否
【StarRocks版本】3.2.2
【集群规模】例如：3fe（3 follower）+16be（fe与be混部）
【机器信息】64C/192G/万兆
【附件】
- 慢查询：
- Profile信息
profile.txt (68.4 KB)
- Profile信息