[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[
https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16589225#comment-16589225
]
ASF GitHub Bot commented on DRILL-6566:
---
Ben-Zvi closed pull request #1438: DRILL-6566: Reduce Hash Agg Batch size and
estimate when low available memory
URL: https://github.com/apache/drill/pull/1438
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
index ba928ae8f2d..72837a89121 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggBatch.java
@@ -48,6 +48,7 @@
import org.apache.drill.exec.physical.impl.common.Comparator;
import org.apache.drill.exec.physical.impl.common.HashTable;
import org.apache.drill.exec.physical.impl.common.HashTableConfig;
+import org.apache.drill.exec.planner.physical.AggPrelBase;
import org.apache.drill.exec.record.AbstractRecordBatch;
import org.apache.drill.exec.record.BatchSchema;
import org.apache.drill.exec.record.BatchSchema.SelectionVectorMode;
@@ -185,6 +186,23 @@ public HashAggBatch(HashAggregate popConfig, RecordBatch
incoming, FragmentConte
// get the output batch size from config.
int configuredBatchSize = (int)
context.getOptions().getOption(ExecConstants.OUTPUT_BATCH_SIZE_VALIDATOR);
+
+// If needed - reduce the size to allow enough batches in the available
memory
+long memAvail = oContext.getAllocator().getLimit();
+long minBatchesPerPartition =
context.getOptions().getOption(ExecConstants.HASHAGG_MIN_BATCHES_PER_PARTITION_VALIDATOR);
+long minBatchesNeeded = 2 * minBatchesPerPartition; // 2 - to cover
overheads, etc.
+boolean is2ndPhase = popConfig.getAggPhase() ==
AggPrelBase.OperatorPhase.PHASE_2of2;
+boolean fallbackEnabled =
context.getOptions().getOption(ExecConstants.HASHAGG_FALLBACK_ENABLED_KEY).bool_val;
+if ( is2ndPhase && !fallbackEnabled ) {
+ minBatchesNeeded *= 2; // 2nd phase (w/o fallback) needs at least 2
partitions
+}
+if ( configuredBatchSize > memAvail / minBatchesNeeded ) { // no cast -
memAvail may be bigger than max-int
+ int reducedBatchSize = (int)(memAvail / minBatchesNeeded);
+ logger.trace("Reducing configured batch size from: {} to: {}, due to Mem
limit: {}",
+configuredBatchSize, reducedBatchSize, memAvail);
+ configuredBatchSize = reducedBatchSize;
+}
+
hashAggMemoryManager = new HashAggMemoryManager(configuredBatchSize);
logger.debug("BATCH_STATS, configured output batch size: {}",
configuredBatchSize);
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
index 1954c79a7e4..65ca82972a0 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/aggregate/HashAggTemplate.java
@@ -80,7 +80,7 @@
import org.apache.drill.exec.vector.VariableWidthVector;
import static org.apache.drill.exec.physical.impl.common.HashTable.BATCH_MASK;
-import static org.apache.drill.exec.record.RecordBatch.MAX_BATCH_SIZE;
+import static org.apache.drill.exec.record.RecordBatch.MAX_BATCH_ROW_COUNT;
public abstract class HashAggTemplate implements HashAggregator {
protected static final org.slf4j.Logger logger =
org.slf4j.LoggerFactory.getLogger(HashAggregator.class);
@@ -562,9 +562,15 @@ private void updateEstMaxBatchSize(RecordBatch incoming) {
else { estValuesRowWidth += fieldSize; }
}
// multiply by the max number of rows in a batch to get the final
estimated max size
-estMaxBatchSize = Math.max(estRowWidth, estInputRowWidth) * MAX_BATCH_SIZE;
+long estimatedMaxWidth = Math.max(estRowWidth, estInputRowWidth);
+estMaxBatchSize = estimatedMaxWidth * MAX_BATCH_ROW_COUNT;
+// estimated batch size should not exceed the configuration given size
+int configuredBatchSize =
outgoing.getRecordBatchMemoryManager().getOutputBatchSize();
+estMaxBatchSize = Math.min(estMaxBatchSize, configuredBatchSize);
+// work back the number of rows (may have been reduced from
MAX_BATCH_ROW_COUNT)
+long rowsInBatch = estMaxBatchSize / estimatedMaxWidth;
// (When there are no aggr functions, use '1' as later code relies on this
size being non-zero)
-estValuesBatchSize =
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16588284#comment-16588284 ] ASF GitHub Bot commented on DRILL-6566: --- Ben-Zvi opened a new pull request #1438: DRILL-6566: Reduce Hash Agg Batch size and estimate when low available memory URL: https://github.com/apache/drill/pull/1438 (1) First commit just renamed MAX_BATCH_SIZE to MAX_BATCH_ROW_COUNT in order to avoid confusion over "size". (2) 2nd commit: Addressing two issues: The configuration batch size (default 16M) is taken as is (by the memory manager). And the (outgoing) batch size estimates are created early (when the outgoing is empty), based on 64K rows per batch. The change: Taking the Hash-Agg memory limit into account, and planning for multiple batches, the configured size (e.g. 16M) may be reduced to allow for the needed number of batches (this new size is given to the memory manager). Later when the estimates are made, that (possibly reduced) size is used to reduce the estimates, if needed. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.15.0 > > Attachments: drillbit.log.6566 > > > This is TPCDS Query 66. > Query: tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16586743#comment-16586743 ] Robert Hou commented on DRILL-6566: --- parquet views can be found on 10.10.100.186:/tmp/createViewsParquet.sql > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.15.0 > > Attachments: drillbit.log.6566 > > > This is TPCDS Query 66. > Query: tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS mar_net, > Sum(CASE > WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS apr_net, > Sum(CASE > WHEN d_moy = 5 THEN
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16586720#comment-16586720 ] Robert Hou commented on DRILL-6566: --- When I run the query66 with hive generated data, and set the max_query_memory_per_node to 10 GB, I see the AGGR OOM message: Error: RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase. Partitions: 1. Estimated batch size: 31260672. values size: 25165824. Output alloc size: 25165824. Planned batches: 1 Memory limit: 2302755 so far allocated: 262144. Fragment 6:0 If I use the default for max_query_memory_per_node = 2 GB, I see a generic message: Error: RESOURCE ERROR: One or more nodes ran out of memory while executing the query. > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.15.0 > > Attachments: drillbit.log.6566 > > > This is TPCDS Query 66. > Query: tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16586708#comment-16586708 ] Boaz Ben-Zvi commented on DRILL-6566: - Looks like as batch-sizing makes the #rows smaller, the estimate (used to determine when to spill) still uses the original estimate (based on 64K rows per batch). Need to check if this estimate needs to be updated. > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.15.0 > > Attachments: drillbit.log.6566 > > > This is TPCDS Query 66. > Query: tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 >
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[
https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16586681#comment-16586681
]
Robert Hou commented on DRILL-6566:
---
Here is the explain plan for the hive generated parquet file.
{noformat}
| 00-00Screen
00-01 Project(w_warehouse_name=[$0], w_warehouse_sq_ft=[$1], w_city=[$2],
w_county=[$3], w_state=[$4], w_country=[$5], ship_carriers=[$6], year1=[$7],
jan_sales=[$8], feb_sales=[$9], mar_sales=[$10], apr_sales=[$11],
may_sales=[$12], jun_sales=[$13], jul_sales=[$14], aug_sales=[$15],
sep_sales=[$16], oct_sales=[$17], nov_sales=[$18], dec_sales=[$19],
jan_sales_per_sq_foot=[$20], feb_sales_per_sq_foot=[$21],
mar_sales_per_sq_foot=[$22], apr_sales_per_sq_foot=[$23],
may_sales_per_sq_foot=[$24], jun_sales_per_sq_foot=[$25],
jul_sales_per_sq_foot=[$26], aug_sales_per_sq_foot=[$27],
sep_sales_per_sq_foot=[$28], oct_sales_per_sq_foot=[$29],
nov_sales_per_sq_foot=[$30], dec_sales_per_sq_foot=[$31], jan_net=[$32],
feb_net=[$33], mar_net=[$34], apr_net=[$35], may_net=[$36], jun_net=[$37],
jul_net=[$38], aug_net=[$39], sep_net=[$40], oct_net=[$41], nov_net=[$42],
dec_net=[$43])
00-02SelectionVectorRemover
00-03 Limit(fetch=[100])
00-04SelectionVectorRemover
00-05 TopN(limit=[100])
00-06HashAgg(group=[{0, 1, 2, 3, 4, 5, 6, 7}],
jan_sales=[SUM($8)], feb_sales=[SUM($9)], mar_sales=[SUM($10)],
apr_sales=[SUM($11)], may_sales=[SUM($12)], jun_sales=[SUM($13)],
jul_sales=[SUM($14)], aug_sales=[SUM($15)], sep_sales=[SUM($16)],
oct_sales=[SUM($17)], nov_sales=[SUM($18)], dec_sales=[SUM($19)],
jan_sales_per_sq_foot=[SUM($20)], feb_sales_per_sq_foot=[SUM($21)],
mar_sales_per_sq_foot=[SUM($22)], apr_sales_per_sq_foot=[SUM($23)],
may_sales_per_sq_foot=[SUM($24)], jun_sales_per_sq_foot=[SUM($25)],
jul_sales_per_sq_foot=[SUM($26)], aug_sales_per_sq_foot=[SUM($27)],
sep_sales_per_sq_foot=[SUM($28)], oct_sales_per_sq_foot=[SUM($29)],
nov_sales_per_sq_foot=[SUM($30)], dec_sales_per_sq_foot=[SUM($31)],
jan_net=[SUM($32)], feb_net=[SUM($33)], mar_net=[SUM($34)], apr_net=[SUM($35)],
may_net=[SUM($36)], jun_net=[SUM($37)], jul_net=[SUM($38)], aug_net=[SUM($39)],
sep_net=[SUM($40)], oct_net=[SUM($41)], nov_net=[SUM($42)], dec_net=[SUM($43)])
00-07 Project(w_warehouse_name=[$0], w_warehouse_sq_ft=[$1],
w_city=[$2], w_county=[$3], w_state=[$4], w_country=[$5], ship_carriers=[$6],
year1=[$7], jan_sales=[$8], feb_sales=[$9], mar_sales=[$10], apr_sales=[$11],
may_sales=[$12], jun_sales=[$13], jul_sales=[$14], aug_sales=[$15],
sep_sales=[$16], oct_sales=[$17], nov_sales=[$18], dec_sales=[$19], $f20=[$20],
$f21=[$21], $f22=[$22], $f23=[$23], $f24=[$24], $f25=[$25], $f26=[$26],
$f27=[$27], $f28=[$28], $f29=[$29], $f30=[$30], $f31=[$31], jan_net=[$32],
feb_net=[$33], mar_net=[$34], apr_net=[$35], may_net=[$36], jun_net=[$37],
jul_net=[$38], aug_net=[$39], sep_net=[$40], oct_net=[$41], nov_net=[$42],
dec_net=[$43])
00-08HashToRandomExchange(dist0=[[$0]])
01-01 UnorderedMuxExchange
02-01Project(w_warehouse_name=[$0],
w_warehouse_sq_ft=[$1], w_city=[$2], w_county=[$3], w_state=[$4],
w_country=[$5], ship_carriers=[$6], year1=[$7], jan_sales=[$8], feb_sales=[$9],
mar_sales=[$10], apr_sales=[$11], may_sales=[$12], jun_sales=[$13],
jul_sales=[$14], aug_sales=[$15], sep_sales=[$16], oct_sales=[$17],
nov_sales=[$18], dec_sales=[$19], $f20=[$20], $f21=[$21], $f22=[$22],
$f23=[$23], $f24=[$24], $f25=[$25], $f26=[$26], $f27=[$27], $f28=[$28],
$f29=[$29], $f30=[$30], $f31=[$31], jan_net=[$32], feb_net=[$33],
mar_net=[$34], apr_net=[$35], may_net=[$36], jun_net=[$37], jul_net=[$38],
aug_net=[$39], sep_net=[$40], oct_net=[$41], nov_net=[$42], dec_net=[$43],
E_X_P_R_H_A_S_H_F_I_E_L_D=[hash32AsDouble($0, 1301011)])
02-02 UnionAll(all=[true])
02-04Project(w_warehouse_name=[$0],
w_warehouse_sq_ft=[$1], w_city=[$2], w_county=[$3], w_state=[$4],
w_country=[$5], ship_carriers=[||(||('ZOUROS', ','), 'ZHOU')], year1=[$6],
jan_sales=[$7], feb_sales=[$8], mar_sales=[$9], apr_sales=[$10],
may_sales=[$11], jun_sales=[$12], jul_sales=[$13], aug_sales=[$14],
sep_sales=[$15], oct_sales=[$16], nov_sales=[$17], dec_sales=[$18], $f20=[/($7,
$1)], $f21=[/($8, $1)], $f22=[/($9, $1)], $f23=[/($10, $1)], $f24=[/($11, $1)],
$f25=[/($12, $1)], $f26=[/($13, $1)], $f27=[/($14, $1)], $f28=[/($15, $1)],
$f29=[/($16, $1)], $f30=[/($17, $1)], $f31=[/($18, $1)], jan_net=[$19],
feb_net=[$20], mar_net=[$21], apr_net=[$22], may_net=[$23], jun_net=[$24],
jul_net=[$25], aug_net=[$26], sep_net=[$27], oct_net=[$28], nov_net=[$29],
dec_net=[$30])
02-06 HashAgg(group=[{0, 1, 2, 3, 4, 5, 6}],
jan_sales=[SUM($7)], feb_sales=[SUM($8)], mar_sales=[SUM($9)],
apr_sales=[SUM($10)], may_sales=[SUM($11)],
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16586555#comment-16586555 ] Robert Hou commented on DRILL-6566: --- The default batch size is 16MB. I ran the query with 8 MB and got the same errors. > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Timothy Farkas >Priority: Critical > Fix For: 1.15.0 > > > This is TPCDS Query 66. > Query: tpcds/tpcds_sf1/original/parquet/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS mar_net, > Sum(CASE > WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS apr_net, > Sum(CASE > WHEN d_moy = 5 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16582913#comment-16582913 ] Boaz Ben-Zvi commented on DRILL-6566: - This query is doing 36 SUM() aggregations !! Hence leading to a very large batch of about 31 MB ( ~ 64K * 36 * ~ 13) . The memory available to the first phase Hash-Agg is only 27 MB -> OOM. Should the batch-sizing address this situation ? > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Timothy Farkas >Priority: Critical > Fix For: 1.15.0 > > > This is TPCDS Query 66. > Query: tpcds/tpcds_sf1/original/parquet/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS mar_net, > Sum(CASE > WHEN
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16581767#comment-16581767 ] Robert Hou commented on DRILL-6566: --- This is the stack trace: java.sql.SQLException: RESOURCE ERROR: One or more nodes ran out of memory while executing the query. Too little memory available Fragment 2:0 [Error Id: 2a30c3de-bb5c-4932-bc3c-1029ad97b29d on atsqa6c87.qa.lab:31010] (org.apache.drill.exec.exception.OutOfMemoryException) Too little memory available org.apache.drill.exec.test.generated.HashAggregatorGen10873.delayedSetup():421 org.apache.drill.exec.test.generated.HashAggregatorGen10873.doWork():595 org.apache.drill.exec.physical.impl.aggregate.HashAggBatch.innerNext():273 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.test.generated.HashAggregatorGen10873.doWork():649 org.apache.drill.exec.physical.impl.aggregate.HashAggBatch.innerNext():273 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.record.AbstractRecordBatch.next():109 org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext():63 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():142 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.physical.impl.union.UnionAllRecordBatch$UnionInputIterator.next():381 org.apache.drill.exec.physical.impl.union.UnionAllRecordBatch.innerNext():118 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.test.generated.HashAggregatorGen11019.doWork():649 org.apache.drill.exec.physical.impl.aggregate.HashAggBatch.innerNext():273 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.record.AbstractRecordBatch.next():109 org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext():63 org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():142 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.physical.impl.BaseRootExec.next():103 org.apache.drill.exec.physical.impl.SingleSenderCreator$SingleSenderRootExec.innerNext():93 org.apache.drill.exec.physical.impl.BaseRootExec.next():93 org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():294 org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():281 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1595 org.apache.drill.exec.work.fragment.FragmentExecutor.run():281 org.apache.drill.common.SelfCleaningRunnable.run():38 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 at org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:528) at org.apache.drill.jdbc.impl.DrillCursor.next(DrillCursor.java:632) at oadd.org.apache.calcite.avatica.AvaticaResultSet.next(AvaticaResultSet.java:207) at org.apache.drill.jdbc.impl.DrillResultSetImpl.next(DrillResultSetImpl.java:153) at org.apache.drill.test.framework.DrillTestJdbc.executeQuery(DrillTestJdbc.java:253) at org.apache.drill.test.framework.DrillTestJdbc.run(DrillTestJdbc.java:115) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: oadd.org.apache.drill.common.exceptions.UserRemoteException: RESOURCE ERROR: One or more nodes ran out of memory while executing the query. Too little memory available Fragment 2:0 [Error Id: 2a30c3de-bb5c-4932-bc3c-1029ad97b29d on atsqa6c87.qa.lab:31010] (org.apache.drill.exec.exception.OutOfMemoryException) Too little memory available org.apache.drill.exec.test.generated.HashAggregatorGen10873.delayedSetup():421 org.apache.drill.exec.test.generated.HashAggregatorGen10873.doWork():595 org.apache.drill.exec.physical.impl.aggregate.HashAggBatch.innerNext():273 org.apache.drill.exec.record.AbstractRecordBatch.next():172 org.apache.drill.exec.record.AbstractRecordBatch.next():119 org.apache.drill.exec.test.generated.HashAggregatorGen10873.doWork():649
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16581755#comment-16581755 ] Robert Hou commented on DRILL-6566: --- I believe commit 89e0fe6b34259a2f51a7c45070935a2a2400eca4 introduced the problem. The problem occurs regardless of whether fallback_enabled is set to true or false: alter system set `drill.exec.hashagg.fallback.enabled` = true; alter system set `drill.exec.hashjoin.fallback.enabled` = true; alter system set `drill.exec.hashagg.fallback.enabled` = false; alter system set `drill.exec.hashjoin.fallback.enabled` = false; > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Timothy Farkas >Priority: Critical > Fix For: 1.15.0 > > > This is TPCDS Query 66. > Query: > /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16540937#comment-16540937 ] Robert Hou commented on DRILL-6566: --- This query passed on April 25. > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.14.0 > > > This is TPCDS Query 66. > Query: > /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS mar_net, > Sum(CASE > WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS apr_net, > Sum(CASE > WHEN d_moy = 5 THEN ws_net_paid_inc_ship
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16540936#comment-16540936 ] Robert Hou commented on DRILL-6566: --- DC was able to get the query to run because he set `exec.hashjoin.num_partitions` to 1. > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.14.0 > > > This is TPCDS Query 66. > Query: > /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS mar_net, > Sum(CASE > WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16539214#comment-16539214 ] Robert Hou commented on DRILL-6566: --- This is what we get from running the latest commit: 1.14.0-SNAPSHOT e79db14d81f8ee15d27cc6026bc0ee409e0c0a3cDRILL-6529 Project Batch Sizing causes two LargeFileCompilation tests to timeout 09.07.2018 @ 04:08:59 PDT Unknown 09.07.2018 @ 15:34:37 PDT >> Query: select * from sys.options where status = 'CHANGED'; namekindaccessibleScopesoptionScope status num_val string_val bool_valfloat_val drill.exec.hashagg.fallback.enabled BOOLEAN ALL SYSTEM CHANGED null nulltruenull drill.exec.hashjoin.fallback.enabledBOOLEAN ALL SYSTEM CHANGED null nulltruenull The output batch size should be 16MB. Query: /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1dot2_hiveplugin/query66.sql SELECT w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, ship_carriers, year1, Sum(jan_sales) AS jan_sales, Sum(feb_sales) AS feb_sales, Sum(mar_sales) AS mar_sales, Sum(apr_sales) AS apr_sales, Sum(may_sales) AS may_sales, Sum(jun_sales) AS jun_sales, Sum(jul_sales) AS jul_sales, Sum(aug_sales) AS aug_sales, Sum(sep_sales) AS sep_sales, Sum(oct_sales) AS oct_sales, Sum(nov_sales) AS nov_sales, Sum(dec_sales) AS dec_sales, Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, Sum(jan_net) AS jan_net, Sum(feb_net) AS feb_net, Sum(mar_net) AS mar_net, Sum(apr_net) AS apr_net, Sum(may_net) AS may_net, Sum(jun_net) AS jun_net, Sum(jul_net) AS jul_net, Sum(aug_net) AS aug_net, Sum(sep_net) AS sep_net, Sum(oct_net) AS oct_net, Sum(nov_net) AS nov_net, Sum(dec_net) AS dec_net FROM (SELECT w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, 'ZOUROS' || ',' || 'ZHOU' AS ship_carriers, d_yearAS year1, Sum(CASE WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS jan_sales, Sum(CASE WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS feb_sales, Sum(CASE WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS mar_sales, Sum(CASE WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS apr_sales, Sum(CASE WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS may_sales, Sum(CASE WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS jun_sales, Sum(CASE WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS jul_sales, Sum(CASE WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS aug_sales, Sum(CASE WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS sep_sales, Sum(CASE WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS oct_sales, Sum(CASE WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS nov_sales, Sum(CASE WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS dec_sales, Sum(CASE WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS jan_net, Sum(CASE WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS feb_net, Sum(CASE WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS mar_net, Sum(CASE WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS apr_net, Sum(CASE WHEN d_moy = 5 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS may_net, Sum(CASE WHEN d_moy = 6 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS jun_net, Sum(CASE WHEN d_moy = 7 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS jul_net, Sum(CASE WHEN d_moy = 8 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS aug_net,
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[
https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16539209#comment-16539209
]
Robert Hou commented on DRILL-6566:
---
DC has indicated this query now works for him. This is an email from June 29.
{noformat}
Hi Salim,
Just verified your PR, looks like the issue is fixed: SF1 TPCDS queries 10, 35,
66, and 69 all completed:
http://10.10.106.202:8047/profiles/24c9011d-66f0-62c5-db66-3521609683c6
{noformat}
The PR is 1354.
> Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more
> nodes ran out of memory while executing the query. AGGR OOM at First Phase.
> --
>
> Key: DRILL-6566
> URL: https://issues.apache.org/jira/browse/DRILL-6566
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Relational Operators
>Affects Versions: 1.14.0
>Reporter: Robert Hou
>Assignee: Boaz Ben-Zvi
>Priority: Critical
> Fix For: 1.14.0
>
>
> This is TPCDS Query 66.
> Query:
> /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql
> SELECT w_warehouse_name,
> w_warehouse_sq_ft,
> w_city,
> w_county,
> w_state,
> w_country,
> ship_carriers,
> year1,
> Sum(jan_sales) AS jan_sales,
> Sum(feb_sales) AS feb_sales,
> Sum(mar_sales) AS mar_sales,
> Sum(apr_sales) AS apr_sales,
> Sum(may_sales) AS may_sales,
> Sum(jun_sales) AS jun_sales,
> Sum(jul_sales) AS jul_sales,
> Sum(aug_sales) AS aug_sales,
> Sum(sep_sales) AS sep_sales,
> Sum(oct_sales) AS oct_sales,
> Sum(nov_sales) AS nov_sales,
> Sum(dec_sales) AS dec_sales,
> Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
> Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
> Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
> Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
> Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
> Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
> Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
> Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
> Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
> Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
> Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
> Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
> Sum(jan_net) AS jan_net,
> Sum(feb_net) AS feb_net,
> Sum(mar_net) AS mar_net,
> Sum(apr_net) AS apr_net,
> Sum(may_net) AS may_net,
> Sum(jun_net) AS jun_net,
> Sum(jul_net) AS jul_net,
> Sum(aug_net) AS aug_net,
> Sum(sep_net) AS sep_net,
> Sum(oct_net) AS oct_net,
> Sum(nov_net) AS nov_net,
> Sum(dec_net) AS dec_net
> FROM (SELECT w_warehouse_name,
> w_warehouse_sq_ft,
> w_city,
> w_county,
> w_state,
> w_country,
> 'ZOUROS'
> \|\| ','
> \|\| 'ZHOU' AS ship_carriers,
> d_yearAS year1,
> Sum(CASE
> WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS jan_sales,
> Sum(CASE
> WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS feb_sales,
> Sum(CASE
> WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS mar_sales,
> Sum(CASE
> WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS apr_sales,
> Sum(CASE
> WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS may_sales,
> Sum(CASE
> WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS jun_sales,
> Sum(CASE
> WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS jul_sales,
> Sum(CASE
> WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS aug_sales,
> Sum(CASE
> WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS sep_sales,
> Sum(CASE
> WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS oct_sales,
> Sum(CASE
> WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS nov_sales,
> Sum(CASE
> WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity
> ELSE 0
> END) AS dec_sales,
> Sum(CASE
> WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity
> ELSE 0
> END) AS jan_net,
> Sum(CASE
> WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16528400#comment-16528400 ] Robert Hou commented on DRILL-6566: --- This query should not be run as part of Jenkins. According to Khurram, it does not complete. > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.14.0 > > > This is TPCDS Query 66. > Query: > /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS jan_net, > Sum(CASE > WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS feb_net, > Sum(CASE > WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS mar_net, > Sum(CASE > WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity > ELSE 0 > END) AS
[jira] [Commented] (DRILL-6566) Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more nodes ran out of memory while executing the query. AGGR OOM at First Phase.
[ https://issues.apache.org/jira/browse/DRILL-6566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16528397#comment-16528397 ] Boaz Ben-Zvi commented on DRILL-6566: - Note: "Estimated batch size: 31260672." That's about 31M !! And "Memory limit: 2302755" - that's only 23M. [~rhou] - Was the Hash-Agg *fallback* option enabled ? By default (the 2nd phase) should have failed and given an error asking for more memory. Side comment: Padma's PR is ready to commit ( #1324 - DRILL-6310 ) - it would lower the batch size for Hash-Agg (but not enough to fix this example). > Jenkins Regression: TPCDS query 66 fails with RESOURCE ERROR: One or more > nodes ran out of memory while executing the query. AGGR OOM at First Phase. > -- > > Key: DRILL-6566 > URL: https://issues.apache.org/jira/browse/DRILL-6566 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators >Affects Versions: 1.14.0 >Reporter: Robert Hou >Assignee: Boaz Ben-Zvi >Priority: Critical > Fix For: 1.14.0 > > > This is TPCDS Query 66. > Query: > /root/drillAutomation/framework-master/framework/resources/Advanced/tpcds/tpcds_sf1/hive-generated-parquet/hive1_native/query66.sql > SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > ship_carriers, > year1, > Sum(jan_sales) AS jan_sales, > Sum(feb_sales) AS feb_sales, > Sum(mar_sales) AS mar_sales, > Sum(apr_sales) AS apr_sales, > Sum(may_sales) AS may_sales, > Sum(jun_sales) AS jun_sales, > Sum(jul_sales) AS jul_sales, > Sum(aug_sales) AS aug_sales, > Sum(sep_sales) AS sep_sales, > Sum(oct_sales) AS oct_sales, > Sum(nov_sales) AS nov_sales, > Sum(dec_sales) AS dec_sales, > Sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, > Sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, > Sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, > Sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, > Sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, > Sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, > Sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, > Sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, > Sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, > Sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, > Sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, > Sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, > Sum(jan_net) AS jan_net, > Sum(feb_net) AS feb_net, > Sum(mar_net) AS mar_net, > Sum(apr_net) AS apr_net, > Sum(may_net) AS may_net, > Sum(jun_net) AS jun_net, > Sum(jul_net) AS jul_net, > Sum(aug_net) AS aug_net, > Sum(sep_net) AS sep_net, > Sum(oct_net) AS oct_net, > Sum(nov_net) AS nov_net, > Sum(dec_net) AS dec_net > FROM (SELECT w_warehouse_name, > w_warehouse_sq_ft, > w_city, > w_county, > w_state, > w_country, > 'ZOUROS' > \|\| ',' > \|\| 'ZHOU' AS ship_carriers, > d_yearAS year1, > Sum(CASE > WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jan_sales, > Sum(CASE > WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS feb_sales, > Sum(CASE > WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS mar_sales, > Sum(CASE > WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS apr_sales, > Sum(CASE > WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS may_sales, > Sum(CASE > WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jun_sales, > Sum(CASE > WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS jul_sales, > Sum(CASE > WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS aug_sales, > Sum(CASE > WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS sep_sales, > Sum(CASE > WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS oct_sales, > Sum(CASE > WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS nov_sales, > Sum(CASE > WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity > ELSE 0 > END) AS dec_sales, > Sum(CASE > WHEN d_moy = 1 THEN ws_net_paid_inc_ship *
