[HIVE-26184] COLLECT_SET with GROUP BY is very slow when some keys are highly skewed

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3.8, 3.1.3
• Fix Version/s: 4.0.0-alpha-2
• Component/s: Hive
• Labels: pull-request-available
• Assignee: Shohei Okumiya
• Reporter: Shohei Okumiya
• Created: 28/Apr/22 07:56
• Updated: 16/Nov/22 13:50
• Resolved: 13/Jun/22 15:26

Description

I observed some reducers spend 98% of CPU time in invoking `java.util.HashMap#clear`.

Looking the detail, I found COLLECT_SET reuses a LinkedHashSet and its `clear` can be quite heavy when a relation has a small number of highly skewed keys.

 

To reproduce the issue, first, we will create rows with a skewed key.

INSERT INTO test_collect_set
SELECT '00000000-0000-0000-0000-000000000000' AS key, CAST(UUID() AS VARCHAR) AS value
FROM table_with_many_rows
LIMIT 100000;

Then, we will create many non-skewed rows.

INSERT INTO test_collect_set
SELECT UUID() AS key, UUID() AS value
FROM table_with_many_rows
LIMIT 5000000;

We can observe the issue when we aggregate values by `key`.

SELECT key, COLLECT_SET(value) FROM group_by_skew GROUP BY key

Issue Links

• links to: GitHub Pull Request #3253