Hi John, Would you mind sharing the query plan for this query (by running "EXPLAIN <query>")?
Thanks, Maryann On Thu, Aug 25, 2016 at 11:19 AM, John Leach <[email protected]> wrote: > Yeah, this query. > > QUERY: > SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY > FROM > TPCH.LINEITEM, > TPCH.PART > WHERE > P_PARTKEY = L_PARTKEY > AND P_BRAND = 'BRAND#23' > AND P_CONTAINER = 'MED BOX' > AND L_QUANTITY < ( > SELECT 0.2 * AVG(L_QUANTITY) > FROM > TPCH.LINEITEM > WHERE > L_PARTKEY = P_PARTKEY > ); > > ROW COUNTS: > TPCH.REGION 5 > TPCH.NATION 25 > TPCH.SUPPLIER 1000000 > TPCH.PART 20000000 > TPCH.PARTSUPP 80000000 > TPCH.ORDERS 150000000 > TPCH.CUSTOMER 15000000 > TPCH.LINEITEM 600037902 > > Does the client have to sort all of LINEITEM and PART? > > Thanks James. > > Regards, > John > > > > On Aug 25, 2016, at 1:16 PM, James Taylor <[email protected]> wrote: > > The client typically does a merge sort when combining results from > paralllel scans. Not sure if this would explain the CPU/memory usage you're > seeing. Can you narrow it down to a particular query that's causing the > issue? > > Thanks, > James > > On Thu, Aug 25, 2016 at 6:49 AM, John Leach <[email protected]> > wrote: > >> Can anyone explain why the client would be burning so much CPU and memory >> if the result is a single row? >> >> I suspect we configured something wrong on Phoenix but we are having a >> hard time figuring it out. >> >> Thanks in advance. >> >> Regards, >> John >> >> > On Aug 24, 2016, at 9:54 AM, Aaron Molitor <[email protected]> >> wrote: >> > >> > Seeing higher than expected CPU/MEM usage for the java process started >> by the sqlline.py client. >> > >> > From top: >> > top - 14:37:32 up 7 days, 22:15, 2 users, load average: 25.52, 9.74, >> 7.89 >> > Tasks: 509 total, 1 running, 508 sleeping, 0 stopped, 0 zombie >> > Cpu(s): 61.2%us, 6.3%sy, 0.0%ni, 31.0%id, 0.5%wa, 0.0%hi, 1.0%si, >> 0.0%st >> > Mem: 65920564k total, 31913580k used, 34006984k free, 647004k buffers >> > Swap: 33030140k total, 0k used, 33030140k free, 10464056k cached >> > >> > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND >> > 8729 splice 20 0 29.9g 14g 889m S 1616.8 22.4 26:19.21 java >> > >> > Why is a the JDBC client using so much memory/cpu? The expected result >> is only a single row. >> > >> > QUERY: >> > SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY >> > FROM >> > TPCH.LINEITEM, >> > TPCH.PART >> > WHERE >> > P_PARTKEY = L_PARTKEY >> > AND P_BRAND = 'BRAND#23' >> > AND P_CONTAINER = 'MED BOX' >> > AND L_QUANTITY < ( >> > SELECT 0.2 * AVG(L_QUANTITY) >> > FROM >> > TPCH.LINEITEM >> > WHERE >> > L_PARTKEY = P_PARTKEY >> > ); >> > >> > ROW COUNTS: >> > TPCH.REGION 5 >> > TPCH.NATION 25 >> > TPCH.SUPPLIER 1000000 >> > TPCH.PART 20000000 >> > TPCH.PARTSUPP 80000000 >> > TPCH.ORDERS 150000000 >> > TPCH.CUSTOMER 15000000 >> > TPCH.LINEITEM 600037902 >> > >> > Thanks, >> > Aaron >> >> > >
