bug: Q18 Remove the subquery part, Out of memory: Killed process

davidshiz commented 1 year ago

Have you read the Contributing Guidelines on issues?

[X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

[X] I confirm there is no existing issue for this

Describe the problem

the SQL below is TPC-H Q18 Remove the subquery part，

select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 10;

My Ubuntu operating system is 32c64G, the TPC-H test data size is 10G. when set the parameters tianmu_groupby_parallel_degree = 64, execute the above sql，process was killed by Linux OOM

Feb  8 19:38:25 ub01 kernel: [97035.392269] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=54495c1a494d7b1e4a5a2ff54b3011ac93227a6c86793d85af56010edc189aec,mems_allowed=0,global_oom,task_memcg=/user.slice/user-0.slice/session-26.scope,task=mysqld,pid=66145,uid=1001
Feb  8 19:38:25 ub01 kernel: [97035.393319] Out of memory: Killed process 66145 (mysqld) total-vm:85045008kB, anon-rss:64567216kB, file-rss:0kB, shmem-rss:0kB, UID:1001 pgtables:138920kB oom_score_adj:0
Feb  8 19:38:29 ub01 kernel: [97038.783342] oom_reaper: reaped process 66145 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

Expected behavior

No response

How To Reproduce

No response

Environment

root@ub01:~# cat /etc/issue Ubuntu 20.04.5 LTS \n \l

Are you interested in submitting a PR to solve the problem?

[ ] Yes, I will!

davidshiz commented 1 year ago

The latest version has new problems：
when there are 3 sessions executing the above SQL at the same time, it will be OOM

root@ub01:/stonedb57/install/bin# ./mysqld --version
./mysqld  Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: f5cd1f9bb
        Last commit time: Date:   Tue Mar 7 10:50:50 2023 +0800
        Build time: Date: Fri Mar 10 20:14:28 CST 2023

adofsauron commented 1 year ago

ACK

adofsauron commented 1 year ago

A deeper understanding of mysql session management is required to solve OOM problems in multiple client sessions

stoneatom / stonedb