Data ingest issues hive: java.lang.OutOfMemoryError: unable to create new native thread

Question

I'm a hive newbie and having an odyssey of problems getting a large (1TB) HDFS file into a partitioned Hive managed table. Can you please help me get around this? I feel like I have a bad config somewhere because I'm not able to complete reducer jobs.

Here is my query:

DROP TABLE IF EXISTS ts_managed;

SET hive.enforce.sorting = true;

CREATE TABLE IF NOT EXISTS ts_managed (
 svcpt_id VARCHAR(20),
 usage_value FLOAT,
 read_time SMALLINT)
PARTITIONED BY (read_date INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC
TBLPROPERTIES("orc.compress"="snappy","orc.create.index"="true","orc.bloom.filter.columns"="svcpt_id");

SET hive.vectorized.execution.enabled = true;
SET hive.vectorized.execution.reduce.enabled = true;
SET set hive.cbo.enable=true;
SET hive.tez.auto.reducer.parallelism=true;
SET hive.exec.reducers.max=20000;
SET yarn.nodemanager.pmem-check-enabled = true;
SET optimize.sort.dynamic.partitioning=true;
SET hive.exec.max.dynamic.partitions=10000;

INSERT OVERWRITE TABLE ts_managed
PARTITION (read_date)
SELECT svcpt_id, usage, read_time, read_date
FROM ts_raw
DISTRIBUTE BY svcpt_id
SORT BY svcpt_id;

My cluster specs are:

• VM cluster
• 4 total nodes
• 4 data nodes
• 32 cores
• 140 GB RAM
• Hortonworks HDP 3.0
• Apache Tez as default Hive engine
• I am the only user of the cluster

My yarn configs are:

yarn.nodemanager.resource.memory-mb = 32GB
yarn.scheduler.minimum-allocation-mb = 512MB
yarn.scheduler.maximum-allocation-mb = 8192MB
yarn-heapsize = 1024MB

My Hive configs are:

hive.tez.container.size = 682MB
hive.heapsize = 4096MB
hive.metastore.heapsize = 1024MB
hive.exec.reducer.bytes.per.reducer = 1GB
hive.auto.convert.join.noconditionaltask.size = 2184.5MB
hive.tex.auto.reducer.parallelism = True
hive.tez.dynamic.partition.pruning = True

My tez configs:

tez.am.resource.memory.mb = 5120MB
tez.grouping.max-size = 1073741824 Bytes
tez.grouping.min-size = 16777216 Bytes
tez.grouping.split-waves = 1.7
tez.runtime.compress = True
tez.runtime.compress.codec = org.apache.hadoop.io.compress.SnappyCodec

I've tried countless configurations including:

• Partition on date
• Partition on date, cluster on svcpt_id with buckets
• Partition on date, bloom filter on svcpt, sort by svcpt_id
• Partition on date, bloom filter on svcpt, distribute by and sort by svcpt_id

I can get my mapping vertex to run, but I have not gotten my first reducer vertex to complete. Here is my most recent example from the above query:

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED   1043       1043        0        0       0       0
Reducer 2        container       RUNNING   9636          0        0     9636       1       0
Reducer 3        container        INITED   9636          0        0     9636       0       0
----------------------------------------------------------------------------------------------
VERTICES: 01/03  [=>>-------------------------] 4%    ELAPSED TIME: 6804.08 s
----------------------------------------------------------------------------------------------

The error was:

Error: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.tez.TezTask. Vertex failed, vertexName=Reducer 2, vertexId=vertex_1537061583429_0010_2_01, diagnostics=[Task failed, taskId=task_1537061583429_0010_2_01_000070, diagnostics=[TaskAttempt 0 failed, info=[Error: Error while running task ( failure ) : java.lang.OutOfMemoryError: unable to create new native thread

I either get this OOM error which I cannot seem to get around or I get datanodes going offline and not being able to meet my replication factor requirements.

At this point I've been troubleshooting for over 2 weeks. Any contacts for professional consultants I can pay to solve this problem would also be appreciated.

Thanks in advance!

Answer 1

I ended up solving this after speaking with a Hortonworks tech guy. Turns out I was over-partitioning my table. Instead of partitioining by day over about 4 years I partitioned by month and it worked great.