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 <
[email protected]> 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 <[email protected]>
> *Sent:* Tuesday, November 12, 2019 2:28 PM
> *To:* [email protected]
> *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
>