Обсуждение: PSQLException: ERROR: could not write to hash-join temporary file: Nospace 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
Re: PSQLException: ERROR: could not write to hash-join temporaryfile: No space left on device
Hello, main problem is that one or many queries have generated more than 2TB of temp space ... I would start by querying pg_stat_activity or pg_stat_statements (if installed and enabled) to identify some long queries, then try to understand why it generates so much hash join temp space Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html
Re: PSQLException: ERROR: could not write to hash-join temporaryfile: No space left on device
and two additionnal idea: - check logs to find the initial SQL statement(s) that failed, - restart the cluster to free all that space Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html
RE: PSQLException: ERROR: could not write to hash-join temporaryfile: No space left on device
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:
- Optimize The Query to not need such a large join
- Make sure you have the indexes you need
- Extended statistics bridging the joining variable and those in the WHERE clause will help the query planner a lot in avoiding unnecessary overhead
- Nested queries of this size are likely to be a very bad idea.
- 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
- Break the work into chunks
- 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@gmail.com>
Sent: Tuesday, November 12, 2019 2:28 PM
To: pgsql-novice@lists.postgresql.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
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:
- Optimize The Query to not need such a large join
- Make sure you have the indexes you need
- Extended statistics bridging the joining variable and those in the WHERE clause will help the query planner a lot in avoiding unnecessary overhead
- Nested queries of this size are likely to be a very bad idea.
- 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
- Break the work into chunks
- 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@gmail.com>
Sent: Tuesday, November 12, 2019 2:28 PM
To: pgsql-novice@lists.postgresql.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
Calvin Kim <calvinkhkim@gmail.com> writes: > 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? Temp files get cleared as part of end-of-query cleanup, there's no separate maintenance job for that. So you'd only see them in the filesystem if you looked while the query was running. regards, tom lane