Monitoring Skew

There are 2 kinds of Skew in Greenplum

1. Data Skew

2. Computational Skew also called Query processing skew

Data skew is caused by an uneven distribution of data because of the wrong selection of distribution columns. It is present at the table level, can be easily identified and avoided by selecting optimal distribution columns.

Avoiding Data Skew

Set up even data distribution when you create a table by using a DISTRIBUTED BY clause in the CREATE TABLE statement. Setting the DISTRIBUTED BY based on the unique or primary key, or a key that is relatively unique, is an easy way to avoid skew. You can use the distributed by clause in an ALTER TABLE statement to change distribution in an existing table.

1. You can specify more than one distribution column with a clause like DISTRIBUTED BY (prod_id, name, company_id). If the combined columns are unique or nearly so, even distribution is guaranteed. Otherwise use your knowledge of the data to pick a distribution key that will not have skew.

2. With no DISTRIBUTED clause, the distribution key defaults to the PRIMARY KEY or UNIQUE constraint on the table. If neither constraint is specified the first column of the table is the

default distribution key. If that column is not suitable, data skews will occur. In that case you need to explicitly set the DISTRIBUTED BY to a column or set of columns that will distribute evenly.

3. If you cannot figure out a combination of columns that will distribute without skew, using the DISTRIBUTED RANDOMLY clause will distribute the data randomly and evenly.

4. Use the views gp_skew_coefficients and gp_skew_idle_fractions in gp_toolkit to find out how data is distributed on the segments.

1. gp_skew_coefficients

gpadmin=# \d gp_toolkit.gp_skew_coefficients

View "gp_toolkit.gp_skew_coefficients"

Column    |  Type   | Modifiers 

--------------+---------+-----------

 skcoid       | oid     | 

 skcnamespace | name    | 

 skcrelname   | name    | 

 skccoeff     | numeric | 

View definition:

 SELECT skew.skewoid AS skcoid, pgn.nspname AS skcnamespace, pgc.relname AS skcrelname, skew.skewval AS skccoeff

   FROM gp_toolkit.__gp_skew_coefficients() skew(skewoid, skewval)

   JOIN pg_class pgc ON skew.skewoid = pgc.oid

   JOIN pg_namespace pgn ON pgc.relnamespace = pgn.oid;