Search

Top 60 Oracle Blogs

Recent comments

Understanding Distribution in #Exasol

Exasol doesn’t need much administration but getting distribution right matters

Exasol uses a clustered shared-nothing architecture with many sophisticated internal mechanisms to deliver outstanding performance without requiring much administration. Getting the distribution of rows between cluster nodes right is one of the few critical tasks left, though. To explain this, let’s say we have two tables t1 and t2:

https://uhesse.files.wordpress.com/2018/10/tablest1t2.png?w=150&h=137 150w, https://uhesse.files.wordpress.com/2018/10/tablest1t2.png 497w" sizes="(max-width: 300px) 100vw, 300px" />

The two tables are joined on the column JoinCol, while WHERE conditions for filtering are done with the column WhereCol. Other columns are not shown to keep the sketches small and simple. Now say these two tables are stored on a three-node cluster. Again, for simplicity only active nodes are on the sketch – no reserve nodes or license nodes. We also ignore the fact that small tables will be replicated across all active nodes.

Distribution will be random if no distribution key is specified

Without specifying a distribution key, the rows of the tables are distributed randomly across the nodes like this:

https://uhesse.files.wordpress.com/2018/10/randomdistribution.png?w=150&... 150w, https://uhesse.files.wordpress.com/2018/10/randomdistribution.png?w=300&... 300w, https://uhesse.files.wordpress.com/2018/10/randomdistribution.png?w=768&... 768w, https://uhesse.files.wordpress.com/2018/10/randomdistribution.png 888w" sizes="(max-width: 620px) 100vw, 620px" />

Absence of proper distribution keys: global joins

The two tables are then joined:

SELECT  FROM t1 JOIN t2 ON t1.JoinCol = t2.JoinCol;

Internally, this is processed as a global join which means network communication between the nodes on behalf of the join is required. This is the case because some rows do not find local join partners on the same node:

https://uhesse.files.wordpress.com/2018/10/globaljoin.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2018/10/globaljoin.png?w=300&h=117 300w, https://uhesse.files.wordpress.com/2018/10/globaljoin.png?w=768&h=299 768w, https://uhesse.files.wordpress.com/2018/10/globaljoin.png 889w" sizes="(max-width: 620px) 100vw, 620px" />

Distribution on join columns: local joins

If the two tables were distributed on their join columns with statements like these

ALTER TABLE t1 DISTRIBUTE BY JoinCol;

ALTER TABLE t2 DISTRIBUTE BY JoinCol;

then the same query can be processed internally as a local join:

https://uhesse.files.wordpress.com/2018/10/localjoin.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2018/10/localjoin.png?w=300&h=119 300w, https://uhesse.files.wordpress.com/2018/10/localjoin.png?w=768&h=304 768w, https://uhesse.files.wordpress.com/2018/10/localjoin.png 891w" sizes="(max-width: 620px) 100vw, 620px" />

Here every row finds a local join partner on the same node so no network communication between the nodes on behalf of the join is required. The performance with this local join is much better than with the global join although it’s the same statement as before.

Why you shouldn’t distribute on WHERE-columns

While it’s generally a good idea to distribute on JOIN-columns, it’s by contrast a bad idea to distribute on columns that are used for filtering with WHERE conditions. If both tables would have been distributed on the WhereCol columns, it would look like this:

https://uhesse.files.wordpress.com/2018/10/wherecols.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2018/10/wherecols.png?w=300&h=119 300w, https://uhesse.files.wordpress.com/2018/10/wherecols.png?w=768&h=304 768w, https://uhesse.files.wordpress.com/2018/10/wherecols.png 883w" sizes="(max-width: 620px) 100vw, 620px" />

This distribution is actually worse than the initial random distribution! Not only does this cause global joins between the two tables as already explained, statements like e.g.

 WHERE t2.WhereCol='A';

will utilize only one node (the first with this WHERE condition) and that effectively disables one of Exasol’s best strengths, the Massive Parallel Processing (MPP) functionality. This distribution leads to poor performance because all other nodes in the cluster have to stand by being idle while one node has to do all the work alone.

Examine existing distribution with iproc()

The function iproc() helps investigating the existing distribution of rows across cluster nodes. This statement shows the distribution of the table t1:

SELECT iproc(),COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;

Evaluate the effect of distribution keys with value2proc()

The function value2proc() can be used to display the effect that a (new) distribution key would have:

SELECT home_node,COUNT(*) FROM (SELECT value2proc(JoinCol) AS home_node FROM t1) GROUP BY 1 ORDER BY 1;

Conclusion

Distribution on JOIN-columns leads to local joins which perform better than global joins: Do that!

Distribution on WHERE-columns leads to global joins and disables the MPP functionality, both causing poor performance: Don’t do that!