Pivotal Knowledge Base

Follow

Could not write to temporary file: No space left on device

Environment

Product Version
 Pivotal Greenplum  4.3.x
 Pivotal HDB  1.3.x / 2.x
 Others  

 

Symptom

When doing a hash join against very large datasets, it errors out, giving the following message,
- Extreme skew in the innerside of Hashjoin
- Could not write to temporary file: No space left on device

Error Messages from pg_log -

"Extreme skew in the innerside of Hashjoin, nbatch 32, mintuples 6672, maxtuples 1258717",,,,,,"
create table summary_topic_tmp
with (appendonly=true, orientation=parquet, compresstype=snappy) as
select t2.reader_id, t3.topic_id, sum(t2.pv) as pv from
(
select st.reader_id, st.vertical_id, sum(st.pv) as pv from
(
select date, reader_id, vertical_id, pv from daily_vertical_201609
union
select date, reader_id, vertical_id, pv from daily_vertical_201608
union
select date, reader_id, vertical_id, pv from daily_vertical_201607
union
select date, reader_id, vertical_id, pv from daily_vertical_201606
) st
where st.date between '2016-06-26' and '2016-09-24'
group by st.reader_id, st.vertical_id
) t2
inner join relay_vertical_googletopic t3
on t2.vertical_id = t3.vertical_id
group by t2.reader_id, t3.topic_id;
:
:

"ERROR","58030","could not write to temporary file: No space left on device",,,,,,"


Cause 
The output from the query shows that the stats for the related tables were not updated and it caused extreme skew and subsequent large spills on a certain segment node which caused the 'No space left on device' error.

RCA 

Explain output feed into PlanChecker shows 'rows=1' which suggests the table stats are not up to date - 

:
-> Table Scan on daily_vertical_201609 (cost=0.00..431.00 rows=1 width=28)
Filter: date >= '2016-06-26'::date AND date <= '2016-09-24'::date
WARNING: Estimated rows is 1 | May need to run ANALYZE on table "daily_vertical_201609"

 

Resolution

To resolve this issue, as suggested by the PlanChecker, run 'analyze' against all the tables associated with the failed query like shown below -

gpadmin-# analyze daily_vertical_201606
gpadmin-# analyze daily_vertical_201607
gpadmin-# analyze daily_vertical_201608
gpadmin-# analyze daily_vertical_201609

 

 

Comments

Powered by Zendesk