Hi Gautam,
Can you please share the query profile?
Yes, but I don't know how to share it, therefore, I am attaching the whole
JSON profile. Hope that will work for you.
did you start with hash_join disabled or did it for troubleshooting?
Yes, I also tried with disabling hash join but the issue remains the same.
On Fri, Oct 26, 2018 at 3:17 AM Gautam Parai <[email protected]> wrote:
> Hi Ashish,
>
> I am curious - did you start with
> hash_join disabled or did it for troubleshooting?
>
> Gautam
>
> On Thu, Oct 25, 2018 at 12:01 AM Ashish Pancholi <[email protected]>
> wrote:
>
> > ---------- Forwarded message ---------
> > From: Ashish Pancholi <[email protected]>
> > Date: Thu, Oct 25, 2018 at 12:21 PM
> > Subject: How to resolve error- "One or more nodes ran out of memory while
> > executing the query"?
> > To: <[email protected]>, <[email protected]>
> >
> >
> >
> > I am using `Apache Drill` version `1.14` on `windows` system and running
> > Drill using the command:
> >
> > sqlline.bat -u "jdbc:drill:zk=local"
> >
> >
> > I am trying to `execute` a `join query` on two `compressed` and
> `archived`
> > `CSV` files.
> >
> > Query:
> >
> > SELECT * FROM
> > >
> >
> dfs.`C:\Users\admin\Desktop\DRILL_FILES\csvFileParquet\TBL_MOREDATA-20180924181406.tar.gz`
> > > AS Table0 INNER JOIN
> > >
> >
> dfs.`C:\Users\admin\Desktop\DRILL_FILES\csvFileParquet\TBL_MOREDATA1-20180924181406.tar.gz`
> > > AS Table1 ON Table0.columns[0]=Table1.columns[0]
> >
> >
> > But an out of memory, error occurred:
> >
> > org.apache.drill.common.exceptions.UserRemoteException: RESOURCE
> ERROR:
> > > One or more nodes ran out of memory while executing the query. Unable
> to
> > > allocate buffer of size 131072 (rounded from 86104) due to memory limit
> > > (630194176). Current allocation: 630108434 Fragment 0:0 [Error Id:
> > > 585c0644-5fd5-446e-b9b3-d48e0771eb2a on DESKTOP-SM3E3KM:31010]
> >
> >
> > To resolve the issue, I tried to update `config\drill-env.sh` file but
> the
> > issue remains the same and it looks like updating the script file does
> not
> > reflect the changes because I am trying to increase the DIRECT MEMORY
> > beyond the system memory (RAM), every time drill starts up peacefully.
> Not
> > even complaining that you have exceeded the memory, therefore, it looks
> > like the changes are not reflecting.
> >
> > export DRILLBIT_MAX_PROC_MEM=12G
> > > export DRILL_HEAP=2G
> > > export DRILL_MAX_DIRECT_MEMORY=10G
> >
> >
> > whereas my system's main memory is only 8 GB.
> >
> > *Please help me to resolve the out of memory error*. I had even run the
> > below queries, in order to follow the troubleshooting instructions but
> the
> > issue remains the same.
> >
> >
> > - alter session set `planner.enable_hashagg` = false;
> > - alter session set `planner.enable_hashjoin` = false;
> > - alter session set planner.width.max_per_node=3;
> > - alter system set planner.width.max_per_query = 100;
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > Thanks
> > Ashish Pancholi
> >
> >
> > --
> > Thanks
> > Ashish Pancholi
> >
>
--
Thanks
Ashish Pancholi
{
"id": {
"part1": 2607427184328789500,
"part2": -4368435147040162300
},
"type": 1,
"start": 1540394697541,
"end": 1540395001127,
"query": "SELECT * FROM
dfs.`C:\\Users\\admin\\Desktop\\DRILL_FILES\\csvFileParquet\\TBL_MOREDATA-20180924181406.tar.gz`
AS Table0 INNER JOIN
dfs.`C:\\Users\\admin\\Desktop\\DRILL_FILES\\csvFileParquet\\TBL_MOREDATA1-20180924181406.tar.gz`
AS Table1 ON Table0.columns[0]=Table1.columns[0]",
"plan": "00-00 Screen : rowType = RecordType(DYNAMIC_STAR **,
DYNAMIC_STAR **0): rowcount = 829576.0, cumulative cost = {9208293.6 rows,
3.65843016E7 cpu, 0.0 io, 0.0 network, 1.4600537600000001E7 memory}, id =
602\r\n00-01 ProjectAllowDup(**=[$0], **0=[$1]) : rowType =
RecordType(DYNAMIC_STAR **, DYNAMIC_STAR **0): rowcount = 829576.0, cumulative
cost = {9125336.0 rows, 3.6501344E7 cpu, 0.0 io, 0.0 network,
1.4600537600000001E7 memory}, id = 601\r\n00-02 Project(T0¦¦**=[$0],
T1¦¦**=[$2]) : rowType = RecordType(DYNAMIC_STAR T0¦¦**, DYNAMIC_STAR T1¦¦**):
rowcount = 829576.0, cumulative cost = {8295760.0 rows, 3.4842192E7 cpu, 0.0
io, 0.0 network, 1.4600537600000001E7 memory}, id = 600\r\n00-03
HashJoin(condition=[=($1, $3)], joinType=[inner]) : rowType =
RecordType(DYNAMIC_STAR T0¦¦**, ANY $f2, DYNAMIC_STAR T1¦¦**, ANY $f20):
rowcount = 829576.0, cumulative cost = {7466184.0 rows, 3.318304E7 cpu, 0.0 io,
0.0 network, 1.4600537600000001E7 memory}, id = 599\r\n00-04
Project(T1¦¦**=[$0], $f20=[$1]) : rowType = RecordType(DYNAMIC_STAR T1¦¦**, ANY
$f20): rowcount = 829576.0, cumulative cost = {3318304.0 rows, 9125336.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 598\r\n00-06
Project(T1¦¦**=[$0], $f2=[ITEM($1, 0)]) : rowType = RecordType(DYNAMIC_STAR
T1¦¦**, ANY $f2): rowcount = 829576.0, cumulative cost = {2488728.0 rows,
7466184.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 597\r\n00-08
Project(T1¦¦**=[$0], columns=[$1]) : rowType = RecordType(DYNAMIC_STAR
T1¦¦**, ANY columns): rowcount = 829576.0, cumulative cost = {1659152.0 rows,
3318304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 596\r\n00-10
Scan(groupscan=[EasyGroupScan
[selectionRoot=file:/C:/Users/admin/Desktop/DRILL_FILES/csvFileParquet/TBL_MOREDATA1-20180924181406.tar.gz,
numFiles=1, columns=[`**`],
files=[file:/C:/Users/admin/Desktop/DRILL_FILES/csvFileParquet/TBL_MOREDATA1-20180924181406.tar.gz]]])
: rowType = RecordType(DYNAMIC_STAR **, ANY columns): rowcount = 829576.0,
cumulative cost = {829576.0 rows, 1659152.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 595\r\n00-05 Project(T0¦¦**=[$0], $f2=[ITEM($1, 0)]) :
rowType = RecordType(DYNAMIC_STAR T0¦¦**, ANY $f2): rowcount = 829576.0,
cumulative cost = {2488728.0 rows, 7466184.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 594\r\n00-07 Project(T0¦¦**=[$0], columns=[$1]) :
rowType = RecordType(DYNAMIC_STAR T0¦¦**, ANY columns): rowcount = 829576.0,
cumulative cost = {1659152.0 rows, 3318304.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 593\r\n00-09 Scan(groupscan=[EasyGroupScan
[selectionRoot=file:/C:/Users/admin/Desktop/DRILL_FILES/csvFileParquet/TBL_MOREDATA-20180924181406.tar.gz,
numFiles=1, columns=[`**`],
files=[file:/C:/Users/admin/Desktop/DRILL_FILES/csvFileParquet/TBL_MOREDATA-20180924181406.tar.gz]]])
: rowType = RecordType(DYNAMIC_STAR **, ANY columns): rowcount = 829576.0,
cumulative cost = {829576.0 rows, 1659152.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 592\r\n",
"foreman": {
"address": "DESKTOP-SM3E3KM",
"userPort": 31010,
"controlPort": 31011,
"dataPort": 31012,
"version": "1.14.0",
"state": 0
},
"state": 4,
"totalFragments": 1,
"finishedFragments": 0,
"fragmentProfile": [
{
"majorFragmentId": 0,
"minorFragmentProfile": [
{
"state": 2,
"minorFragmentId": 0,
"operatorProfile": [
{
"inputProfile": [
{
"records": 16633479,
"batches": 2056,
"schemas": 1
}
],
"operatorId": 10,
"operatorType": 28,
"setupNanos": 0,
"processNanos": 17036879776,
"peakLocalMemoryAllocated": 4210688,
"waitNanos": 6357163275
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2055,
"schemas": 1
}
],
"operatorId": 8,
"operatorType": 10,
"setupNanos": 513653738,
"processNanos": 5565181172,
"peakLocalMemoryAllocated": 1638400,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2055,
"schemas": 1
}
],
"operatorId": 6,
"operatorType": 10,
"setupNanos": 86341410,
"processNanos": 834749974,
"peakLocalMemoryAllocated": 892928,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2055,
"schemas": 1
}
],
"operatorId": 4,
"operatorType": 10,
"setupNanos": 52584606,
"processNanos": 646558209,
"peakLocalMemoryAllocated": 892928,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2056,
"schemas": 1
}
],
"operatorId": 9,
"operatorType": 28,
"setupNanos": 0,
"processNanos": 17679408673,
"peakLocalMemoryAllocated": 4210688,
"waitNanos": 14762609718
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2055,
"schemas": 1
}
],
"operatorId": 7,
"operatorType": 10,
"setupNanos": 4562210,
"processNanos": 5890823295,
"peakLocalMemoryAllocated": 1638400,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2055,
"schemas": 1
}
],
"operatorId": 5,
"operatorType": 10,
"setupNanos": 3606287,
"processNanos": 925538357,
"peakLocalMemoryAllocated": 892928,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 16633479,
"batches": 2055,
"schemas": 1
},
{
"records": 16633479,
"batches": 2055,
"schemas": 1
}
],
"operatorId": 3,
"operatorType": 4,
"setupNanos": 0,
"processNanos": 100422422051,
"peakLocalMemoryAllocated": 490333184,
"metric": [
{
"metricId": 0,
"longValue": 2097152
},
{
"metricId": 3,
"longValue": 0
},
{
"metricId": 7,
"longValue": 1
},
{
"metricId": 2,
"longValue": 0
},
{
"metricId": 5,
"longValue": 7
},
{
"metricId": 4,
"longValue": 32
},
{
"metricId": 1,
"longValue": 0
}
],
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 2184485,
"batches": 40,
"schemas": 1
}
],
"operatorId": 2,
"operatorType": 10,
"setupNanos": 23649871,
"processNanos": 13603123,
"peakLocalMemoryAllocated": 170194567,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 2184485,
"batches": 36,
"schemas": 1
}
],
"operatorId": 1,
"operatorType": 10,
"setupNanos": 2440892,
"processNanos": 5059532,
"peakLocalMemoryAllocated": 170194567,
"waitNanos": 0
},
{
"inputProfile": [
{
"records": 2184485,
"batches": 36,
"schemas": 1
}
],
"operatorId": 0,
"operatorType": 13,
"setupNanos": 0,
"processNanos": 2360653,
"peakLocalMemoryAllocated": 170194567,
"metric": [
{
"metricId": 0,
"longValue": 122336522
}
],
"waitNanos": 18891702227
}
],
"startTime": 1540394700435,
"endTime": 1540394997706,
"memoryUsed": 492071826,
"maxMemoryUsed": 506436590,
"endpoint": {
"address": "DESKTOP-SM3E3KM",
"userPort": 31010,
"controlPort": 31011,
"dataPort": 31012,
"version": "1.14.0",
"state": 0
},
"lastUpdate": 1540394997706,
"lastProgress": 1540394997706
}
]
}
],
"user": "anonymous",
"error": "RESOURCE ERROR: Drill Remote Exception\n\n",
"verboseError": "RESOURCE ERROR: Drill Remote Exception\n\n\n\n",
"errorId": "b34ee07b-3999-44f7-822c-5802bd608ae3",
"errorNode": "DESKTOP-SM3E3KM:31010",
"optionsJson": "[ ]",
"planEnd": 1540394700435,
"queueWaitEnd": 1540394700435,
"totalCost": 9125336,
"queueName": "Unknown",
"queryId": "242f7135-9b9d-4d7b-c360-3360f456fd5c"
}
