Re: PSQLException: ERROR: could not write to hash-join temporary file: No space left on device

Hi Stephen,

Thanks for the advise. I will work with developer to take a look at the
queries and try to optimize them or break them down into chunks.

What is strange for me is that I don't see any files in /base/pgsql_tmp and
that temp_tablespaces is not set in postgresql.conf.
I know that temp files supposed to be removed after process is completed,
but not sure why I'm seeing such a huge numbers in pg_stat_database. Would
there be any maintenance job that clears this?
I'm using PostgreSQL version 9.6.

Thanks,
Calvin

On Wed, Nov 13, 2019 at 11:53 AM Stephen Froehlich <
s(dot)froehlich(at)cablelabs(dot)com> wrote:

> It seems impossible to use hundreds of gigs of temp space, but I’ve done
> it. Adding temp space beyond 2TB here is not likely to be the right
> solution. Instead you want to avoid needing THAT much temp space in the
> first place. Your
>
> PostgreSQL server is not a large Spark cluster.
>
>
>
> Posting the text of the query here would help … as would the output of
> EXPLAIN [query].
>
>
>
> Yes, optimizing your postgresql.conf for analysis type work is also very
> helpful, but this is not that.
> Here is a good place to start on that … https://pgtune.leopard.in.ua/#/
>
>
>
>
>
> Not having the query here, there are two approaches I’d probably use:
>
> 1. *Optimize The Query to not need such a large join*
> 1. Make sure you have the indexes you need
> 2. Extended statistics bridging the joining variable and those in
> the WHERE clause will help the query planner a lot in avoiding unnecessary
> overhead
> 3. Nested queries of this size are likely to be a very bad idea.
> 4. If the underlying tables are partitioned (I assume they are at
> this size), and you’re joining on a partition variable, try applying the
> when clause to each hypertable
> 2. *Break the work into chunks*
> 1. Try breaking the work into reasonable-sized chunks, and either
> loop through or parallelize with a scripting language (bash, R, Python,
> etc.)
>
>
>
> --Stephen
>
>
>
> *From:* Calvin Kim <calvinkhkim(at)gmail(dot)com>
> *Sent:* Tuesday, November 12, 2019 2:28 PM
> *To:* pgsql-novice(at)lists(dot)postgresql(dot)org
> *Subject:* PSQLException: ERROR: could not write to hash-join temporary
> file: No space left on device
>
>
>
> Hi team,
>
>
>
> I have an issue with Postgres error: *PSQLException: ERROR: could not
> write to hash-join temporary file: No space left on device*
>
> After investigation, I could see that both *work_mem* and
> *temp_tablespace* is NOT specified in postgresql.conf file,
>
> but when I queried database, it showed as 15728kB.
>
>
>
> $ cat postgresql.conf | grep temp_tablespaces
>
> #temp_tablespaces = '' # a list of tablespace names, ''
> uses
>
>
>
> $ cat postgresql.conf | grep work_mem
>
> #work_mem = 4MB # min 64kB
>
> #maintenance_work_mem = 64MB # min 1MB
>
> #autovacuum_work_mem = -1 # min 1MB, or -1 to use
> maintenance_work_mem
>
> --Recommend is 64MB
>
>
>
> postgres=# show work_mem;
>
> work_mem
>
> ----------
>
> 15728kB
>
> (1 row)
>
>
>
> I can see that large number of temp files have been created.
>
>
>
> postgres=# SELECT datname, temp_files,temp_bytes from
> pg_catalog.pg_stat_database;
>
> datname | temp_files | temp_bytes
>
> ------------+------------+---------------
>
> postgres | 24 | 25013533
>
> user_db | 200797 | 2774922398171
>
> template1 | 0 | 0
>
> template0 | 0 | 0
>
> (4 rows)
>
>
>
> $ free -h
>
> total used free shared buffers cached
>
> Mem: 31G 18G 12G 5.4G 408M 17G
>
> -/+ buffers/cache: 1.4G 29G
>
> Swap: 4.0G 226M 3.8G
>
>
>
> In my opinion, *work_mem* needs to be set since temp files created seem
> quite large.
>
> Can someone please advise the recommended value for work_mem (is 64MB
> recommended?)
>
> And if setting up temp_tablespace is also recommended in this case?
>
>
>
> Thanks,
>
> Calvin
>