[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15564169#comment-15564169
]
Ioana Delaney commented on SPARK-17626:
---
[~ron8hu] Thank you for your comments. Our current star schema detection uses
simple, basic heuristics to identify the star join with the largest fact table
and places it on the driving arm of the join. Even with this simple, intuitive
join reordering, the performance results show very good improvement. The next
step would be to improve the schema detection logic based on cardinality
heuristics and then with more reliable informational RI constraints. When CBO
implements the planning rules, the two algorithms can be integrated. Regarding
predicate selectivity hint, it can also be used to diagnose query
planning/performance. By changing predicate selectivity, we can influence the
choice of a plan and thus test various planning options.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15559044#comment-15559044
]
Ron Hu commented on SPARK-17626:
In the CBO design spec we posted in
https://issues.apache.org/jira/browse/SPARK-16026,
we illustrated a Multi-way Join Ordering Optimization algorithm using dynamic
programming technique. This algorithm should be able to pick up the best join
re-ordering plan. It is possible that the search space is big. We need some
heuristics to reduce the search space.
As Zhenhua pointed out, we can identify all the primary-key/foreign-key joins
as we collect number of distinct values to infer whether or not a join column
is a primary key. If a join relation has primary key join column, then it is a
dimension table. If a join relation has foreign key columns, then it is a fact
table. Once a fact table is identified, we form a star schema by finding out
all the dimension tables that have join conditions with the given fact table.
As for the selectivity hint, we do not need selectivity hint to deal with
comparison expression like:
column_nameconstant_value
where a comparison operator is =, <, <=, >, >=, etc.
This is because, with the histogram we are implementing now in CBO, we can find
the filtering selectivity properly. However, for the following cases, a
selectivity hint will be helpful.
Case 1:
WHERE o_comment not like '%special%request%' /* TPC-H Q13 */
Histogram cannot provide such detailed statistics information for a string
pattern which can a complex regular expression.
Case 2:
WHERE l_commitdate < l_receiptdate /* TPC-H Q4 */
Today we define one-dimensional histogram to keep track the data distribution
of a single column. We do not handle the non-equal relationship between two
columns.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15558762#comment-15558762
]
Ioana Delaney commented on SPARK-17626:
---
[~mikewzh] Thank you. Yes, having informational RI constraints available in
Spark will open many opportunities for optimizations. Star schema detection is
just one of them. Our team here at IBM already started some initial design
discussions in this direction. We are hoping to have something more concrete
soon.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15557867#comment-15557867
]
Zhenhua Wang commented on SPARK-17626:
--
This is pretty good. We can use star schema detection in CBO to significantly
reduce the search space, especially when we want to construct bushy trees.
BTW, I think we can infer some kind of RI constraints now. Since we've already
added column-level statistics, we may infer a column as a primary key if its
ndv(number of distinct values) is very close to the number of rows, i.e. ndv /
#rows ≈ 1, then those columns which are used to join with it become candidate
foreign keys. This may help to improve the current detection algorithm.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15556645#comment-15556645
]
Reynold Xin commented on SPARK-17626:
-
Thanks - this makes sense (especially the bushy tree part).
For runtime, a lot of known optimizations one can do (e.g. based on RI to turn
hash map lookups into dense array lookups) are already done entirely adaptively
during query execution in whole stage code generation.
Also looping in [~ron8hu] and [~ZenWzh].
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15556305#comment-15556305
]
Ioana Delaney commented on SPARK-17626:
---
[~rxin] Hi Reynold, Thank you for your reply. My argument for why they
complement each other is the following. A star join has a clear join pattern
established based on referential integrity constraints among the fact and the
dimension tables. It is desirable to identify and preserve such patterns in a
query as they offer optimal join executions among those particular set of
tables. Cost based optimizer has a generic purpose of finding the best
execution plan based on tables’ statistics and a set of cost formulas. Without
additional star schema knowledge, CBO may destroy the fact-dimension join
patterns by mixing in tables outside the RI relationship, for example.
Moreover, a query may have multiple fact tables with more than one star-join
plan. Ideally, the star-joins should be planned in the form of a bushy-join
plan that preserves each individual star plan. Without additional knowledge, it
might be hard for CBO to observe such join patterns. Therefore, star schema
detection may provide hints to CBO to consider certain planning patterns. Then,
within an identified star join plan, the cost based optimizer will offer the
best join order among the dimension tables based on their cardinality,
predicate selectivity, etc.
Our current implementation uses a very simple algorithm, that applies generally
accepted heuristics, and shows good performance results. More improvements can
be made with support for plan cardinality, predicate selectivity estimates,
and, in the near future, maybe support for informational RI constraints.
Regarding the need for a specialized operator, it might not be necessary given
the current Spark runtime implementation. I didn’t get a chance to explore
spark code gen and runtime areas. But I think that in general, knowing that a
certain sequence of joins is a star-plan, with or without a specialized
operator, might open up runtime for various optimizations.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
>
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15554459#comment-15554459
]
Reynold Xin commented on SPARK-17626:
-
[~ioana-delaney] Thanks for commenting.
1. I think specialized join operator makes sense for the volcano style (or
column batch) operators, but I am not sure if they are still necessary with
whole stage code generation. For example, a special star join operator would
just look like a sequence of broadcast joins with whole stage code generation
(since the rows are never materialized).
2. Can you comment on the reasons why this is still useful with a cost-based
optimizer, from first principle? So far your argument is mostly many RDBMSes
have both and they are complementary. It would be great to comment on why they
are complementary.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15552570#comment-15552570
]
Ioana Delaney commented on SPARK-17626:
---
Selectivity hint is a very useful feature, widely used in industry to fine tune
the Optimizer cost model. I expect this feature to be needed once the cost
model optimizer is in place. Given our star schema work we took the liberty to
implement it since it helped our query investigation and tuning. The proposed
selectivity design, which will be posted soon, aligns to the existing
statistics design and should be easily integrated into the ongoing cost model
implementation.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15552517#comment-15552517
]
Ioana Delaney commented on SPARK-17626:
---
Star schema optimization and cost based model will complement each other. Many
RDBMS systems observe star schema queries and have special implementations for
star joins e.g. either optimize the chain of RI joins or they introduce a new
operator to efficiently execute the join. I am envisioning that our work will
go towards that direction. Short term, with the cost model support we can (1)
further refine our schema detection heuristics with cardinality and predicate
selectivity, and (2) use the cost model to find the optimal join order within a
star join. Longer term, for star schema queries, we can further enhance the
star join detection using reliable RI constraints and ultimately we can
implement runtime operators to efficiently execute star join plans.
So I expect the star schema optimizations to evolve in parallel with the cost
base model. There are many, many things that we can build to improve the
performance of this category of queries which are widely used in the industry.
This work would be our first step.
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15550822#comment-15550822
]
Xiao Li commented on SPARK-17626:
-
The short answer is provided in the design doc:
{noformat}
The new join reordering complements the ongoing cost model support in the
Optimizer with natural heuristics for star-schema queries. Star detection logic
can be refined when plan cardinality and predicate selectivity features are
implemented in the Optimizer.
{noformat}
We will input more details soon. Thanks!
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15550808#comment-15550808
]
Reynold Xin commented on SPARK-17626:
-
Can you guys comment on the usefulness of this in the context of cost-based
optimization?
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15550799#comment-15550799
]
Xiao Li commented on SPARK-17626:
-
Selectivity Hint is another one. The current JIRA `Join reordering using star
schema detection` does not depend on it.
In the slides, we document the needs of selectivity hints for resolving the
regression of these four queries.
{noformat}
Queries 3, 10, 68, and 84
Selectivity on the dimension tables was not taken into account
The regressions were fixed by specifying the selectivity hints on the dimension
predicates reorder most selective joins first
{noformat}
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-17626) TPC-DS performance improvements using star-schema heuristics
[
https://issues.apache.org/jira/browse/SPARK-17626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15550764#comment-15550764
]
Reynold Xin commented on SPARK-17626:
-
Is there more than one task to be done here?
> TPC-DS performance improvements using star-schema heuristics
>
>
> Key: SPARK-17626
> URL: https://issues.apache.org/jira/browse/SPARK-17626
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
>Affects Versions: 2.1.0
>Reporter: Ioana Delaney
>Priority: Critical
> Attachments: StarSchemaJoinReordering.pptx
>
>
> *TPC-DS performance improvements using star-schema heuristics*
> \\
> \\
> TPC-DS consists of multiple snowflake schema, which are multiple star schema
> with dimensions linking to dimensions. A star schema consists of a fact table
> referencing a number of dimension tables. Fact table holds the main data
> about a business. Dimension table, a usually smaller table, describes data
> reflecting the dimension/attribute of a business.
> \\
> \\
> As part of the benchmark performance investigation, we observed a pattern of
> sub-optimal execution plans of large fact tables joins. Manual rewrite of
> some of the queries into selective fact-dimensions joins resulted in
> significant performance improvement. This prompted us to develop a simple
> join reordering algorithm based on star schema detection. The performance
> testing using *1TB TPC-DS workload* shows an overall improvement of *19%*.
> \\
> \\
> *Summary of the results:*
> {code}
> Passed 99
> Failed 0
> Total q time (s) 14,962
> Max time1,467
> Min time3
> Mean time 145
> Geomean44
> {code}
> *Compared to baseline* (Negative = improvement; Positive = Degradation):
> {code}
> End to end improved (%) -19%
> Mean time improved (%) -19%
> Geomean improved (%) -24%
> End to end improved (seconds) -3,603
> Number of queries improved (>10%) 45
> Number of queries degraded (>10%) 6
> Number of queries unchanged48
> Top 10 queries improved (%) -20%
> {code}
> Cluster: 20-node cluster with each node having:
> * 10 2TB hard disks in a JBOD configuration, 2 Intel(R) Xeon(R) CPU E5-2680
> v2 @ 2.80GHz processors, 128 GB RAM, 10Gigabit Ethernet.
> * Total memory for the cluster: 2.5TB
> * Total storage: 400TB
> * Total CPU cores: 480
> Hadoop stack: IBM Open Platform with Apache Hadoop v4.2. Apache Spark 2.0 GA
> Database info:
> * Schema: TPCDS
> * Scale factor: 1TB total space
> * Storage format: Parquet with Snappy compression
> Our investigation and results are included in the attached document.
> There are two parts to this improvement:
> # Join reordering using star schema detection
> # New selectivity hint to specify the selectivity of the predicates over base
> tables. Selectivity hint is optional and it was not used in the above TPC-DS
> tests.
> \\
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
