Issue distributing data among nodes

Question

Hi! I'm currently facing an issue trying to equally distribute data among nodes, this is my current situation:
![current status](/media/postImages/original/IM2R45l2I0StqojJD2AOFOkw)
I used this query to look for node0 MB vs the other 2 nodes to look for imbalances.

`with space_used (schemaname, tablename, mbytes, avg_node_mbytes, node0_mbytes) as
        (
        select  trim(pgn.nspname),
                trim(tbl.name),
                sum(bl.mbytes),
                sum(bl.mbytes) / (select count(distinct node) from stv_slices),
                sum(case when bl.node = 0 then bl.mbytes else 0 end)
        from    (
                select  id, name, sum(rows) as rows
                from    STV_TBL_PERM
                group by db_id, id, name
                ) as tbl
        join    PG_CLASS as pgc on pgc.oid = tbl.id
        join    PG_NAMESPACE as pgn on pgn.oid = pgc.relnamespace
        join    (
                select  bl.tbl, s.node, count(*) as mbytes
                from    stv_blocklist bl
                join    stv_slices s on s.slice = bl.slice
                group by bl.tbl, s.node
                ) bl on bl.tbl = tbl.id
        group by 1, 2
        )
select  * 
from    space_used
order   by (node0_mbytes - avg_node_mbytes) desc
limit   20;`

This is the result:
![Enter image description here](/media/postImages/original/IMk2cYIvEhTdmz_IJERvziow)
I changed several tables, for example rows 2, and 5 from the original distkey to diststyle EVEN, and there is still imbalance between nodes.

This imbalance has a huge impact on processor usage:
![Enter image description here](/media/postImages/original/IMyz2AP3PIRYyGpOlZ_-xQ9g)

What I'm doing wrong? is there a way to know specifically witch tables are impacting on the overuse of node0, also, why EVEN is not "evenly" distributing the tables?

Thanks!

Oleksii Bebych · Answer

Try running VACUUM and ANALYZE. This can help re-sort the tables and update statistics, which might improve performance.
```
VACUUM FULL;
ANALYZE;
```

Vinayaka-Gangadhar · Answer

Hello,

In Redshift data distribution happens across slices. A compute node is partitioned into slices. Each slice is allocated a portion of the node's memory and disk space, where it processes a portion of the workload assigned to the node. The leader node manages distributing data to the slices and apportions the workload for any queries or other database operations to the slices. The slices then work in parallel to complete the operation.

The number of slices per node is determined by the node size of the cluster. For more information about the number of slices for each node size, refer the [doc](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html#rs-about-clusters-and-nodes). To check the number of slices for each node, please run below query

``select * from stv_slices;``

Please see if the slices are equally distributed across all compute nodes.

When you create a table, you can optionally specify one column as the distribution key. When the table is loaded with data, the rows are distributed to the node slices according to the distribution key that is defined for a table. Choosing a good distribution key enables Amazon Redshift to use parallel processing to load data and run queries efficiently. In case of even data will be evenly distributed across all slices. When you choose ALL based distribution, a copy of the entire table is distributed to every node.

Then run query from [link](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_check_data_distribution.sql) to get data distribution across slices. Result will show table wise distribution across slice. Use this information to identify the table/s with uneven distribution.

Issue distributing data among nodes

Add your answer

Relevant content