public inbox for [email protected]
help / color / mirror / Atom feedEven if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
6+ messages / 3 participants
[nested] [flat]
* Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
@ 2023-09-08 11:22 Nature Conservation Geovista Space <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Nature Conservation Geovista Space @ 2023-09-08 11:22 UTC (permalink / raw)
To: [email protected]
Dear Pg-users,
I am coming back to Postgres/PostGIS after a few years. I am dealing with a
big database with a lot of geometries and too many vertices.
After hours running a query to Subdivide, I get this Postgres error
*2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR: could not
extend file "base/16388/7985375.1020": No space left on device 2023-09-08
02:11:23.745 BST [328594] postgres@database HINT: Check free disk space.*
2023-09-08 02:11:23.745 BST [328594] postgres@database STATEMENT: CREATE
TABLE _gaul_administrative_subdivided100 AS (
SELECT *, st_subdivide(geom,100) AS geom_subdivided100
FROM gaul_administrative
);
*2023-09-08 02:15:38.251 BST [313729] LOG: checkpoint complete: wrote
81956 buffers (1.6%); 0 WAL file(s) added, 0 removed, 608 recycled;
write=269.414 s, sync=0.001 s, total=269.634 s; sync files=1, longest=0.001
s, average=0.001 s; distance=9962549 kB, estimate=9980351 kB;
lsn=291/BF46ABE8, redo lsn=291/A0FB7D98*
It seems that it is not a problem of space.
Command *df -h* returns:
Filesystem Size Used Avail Use% Mounted on
tmpfs 6.3G 1.1M 6.3G 1% /run
/dev/sda 1.3T 164G 1.1T 14% /
tmpfs 32G 3.2M 32G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 6.3G 4.0K 6.3G 1% /run/user/1000
Command *df -ih* returns:
Filesystem Inodes IUsed IFree IUse% Mounted on
tmpfs 7.9M 724 7.9M 1% /run
/dev/sda 80M 179K 80M 1% /
tmpfs 7.9M 4 7.9M 1% /dev/shm
tmpfs 7.9M 3 7.9M 1% /run/lock
tmpfs 1.6M 28 1.6M 1% /run/user/1000
I suppose it is an issue with temporary table, here my present
configuration in *postgresql.conf*
#temp_tablespaces = '' # a list of tablespace names,
'' uses
# only default tablespace
#temp_file_limit = -1 # limits per-process temp file
space
# in kilobytes, or -1 for no limit
What do you suggest?
cheers,
Enzopolo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
@ 2023-09-08 17:51 Laurenz Albe <[email protected]>
parent: Nature Conservation Geovista Space <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2023-09-08 17:51 UTC (permalink / raw)
To: Nature Conservation Geovista Space <[email protected]>; [email protected]
On Fri, 2023-09-08 at 12:22 +0100, Nature Conservation Geovista Space wrote:
> After hours running a query to Subdivide, I get this Postgres error
> >
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR: could not extend file "base/16388/7985375.1020": No space left on device
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database HINT: Check free disk space.
> >
> > 2023-09-08 02:11:23.745 BST [328594] postgres@database STATEMENT: CREATE TABLE _gaul_administrative_subdivided100 AS (
> > SELECT *, st_subdivide(geom,100) AS geom_subdivided100
> > FROM gaul_administrative
> > );
>
> It seems that it is not a problem of space. [df shows there is space everywhere]
"After running a query for hours" makes it appear like you run out of disk space with
temporary files. These only exist during the lifetime of the query, so they are gone again after
the statement has failed.
Set "temp_file_limit" to avoid that kind of problem.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
@ 2023-09-08 20:01 bruno da silva <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: bruno da silva @ 2023-09-08 20:01 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Nature Conservation Geovista Space <[email protected]>; [email protected]
Hello. Does temp_file_limt also applies to reindex?
Thanks
On Fri, Sept 8, 2023, 3:54 p.m. Laurenz Albe <[email protected]>
wrote:
> On Fri, 2023-09-08 at 12:22 +0100, Nature Conservation Geovista Space
> wrote:
> > After hours running a query to Subdivide, I get this Postgres error
> > >
> > > 2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR: could
> not extend file "base/16388/7985375.1020": No space left on device
> > > 2023-09-08 02:11:23.745 BST [328594] postgres@database HINT: Check
> free disk space.
> > >
> > > 2023-09-08 02:11:23.745 BST [328594] postgres@database STATEMENT:
> CREATE TABLE _gaul_administrative_subdivided100 AS (
> > > SELECT *, st_subdivide(geom,100) AS geom_subdivided100
> > > FROM gaul_administrative
> > > );
> >
> > It seems that it is not a problem of space. [df shows there is space
> everywhere]
>
> "After running a query for hours" makes it appear like you run out of disk
> space with
> temporary files. These only exist during the lifetime of the query, so
> they are gone again after
> the statement has failed.
>
> Set "temp_file_limit" to avoid that kind of problem.
>
> Yours,
> Laurenz Albe
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
@ 2023-09-09 02:54 Laurenz Albe <[email protected]>
parent: bruno da silva <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2023-09-09 02:54 UTC (permalink / raw)
To: bruno da silva <[email protected]>; +Cc: Nature Conservation Geovista Space <[email protected]>; [email protected]
On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote:
> Hello. Does temp_file_limt also applies to reindex?
To the extent that REINDEX creates temporary files, yes.
It will limit the size of temporary files created when the
rows are sorted.
It does *not* limit the size of the data files for the new
index.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
@ 2023-09-12 12:12 Nature Conservation Geovista Space <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Nature Conservation Geovista Space @ 2023-09-12 12:12 UTC (permalink / raw)
To: [email protected]
Hi Laurenz,
Thanks a lot for your kind support.
1 - At the moment my *postgresql.conf *is by default with no limit
*#temp_file_limit
= -1*
Maybe I just have to uncomment it so that this rule really applies. Let's
see this evening if your suggestion fix it.
2 - Do you think it could come from another param not set up correctly?
3 - Can I try to watch out the log or size of the temporary table created
after my query failed so that I can investigate what happened?
Cheers,
Vincent
On Sat, 9 Sept 2023 at 03:54, Laurenz Albe <[email protected]> wrote:
> On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote:
> > Hello. Does temp_file_limt also applies to reindex?
>
> To the extent that REINDEX creates temporary files, yes.
> It will limit the size of temporary files created when the
> rows are sorted.
>
> It does *not* limit the size of the data files for the new
> index.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device"
@ 2023-09-12 12:48 Laurenz Albe <[email protected]>
parent: Nature Conservation Geovista Space <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2023-09-12 12:48 UTC (permalink / raw)
To: Nature Conservation Geovista Space <[email protected]>; [email protected]
On Tue, 2023-09-12 at 13:12 +0100, Nature Conservation Geovista Space wrote:
> On Sat, 9 Sept 2023 at 03:54, Laurenz Albe <[email protected]> wrote:
> > On Fri, 2023-09-08 at 16:01 -0400, bruno da silva wrote:
> > > Hello. Does temp_file_limt also applies to reindex?
> >
> > To the extent that REINDEX creates temporary files, yes.
> > It will limit the size of temporary files created when the
> > rows are sorted.
> >
> > It does *not* limit the size of the data files for the new
> > index.
>
> 1 - At the moment my postgresql.conf is by default with no limit #temp_file_limit = -1
> Maybe I just have to uncomment it so that this rule really applies. Let's see this evening if
> your suggestion fix it.
"-1" meanse "no limit", so removing the comment won't do anything.
You also have to change the value.
> 2 - Do you think it could come from another param not set up correctly?
"work_mem" influences the amount of temp files required. But if your query
needs enough temp files to fill your disk, higher settings of "work_mem" are
unlikely to fix that.
> 3 - Can I try to watch out the log or size of the temporary table created after my query failed
> so that I can investigate what happened?
Sure. Set "log_temp_files = '100kB'", and every temp file above that size will be logged.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-09-12 12:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-08 11:22 Even if I have a lot of free space PgSQL returns "ERROR: could not extend file - No space left on device" Nature Conservation Geovista Space <[email protected]>
2023-09-08 17:51 ` Laurenz Albe <[email protected]>
2023-09-08 20:01 ` bruno da silva <[email protected]>
2023-09-09 02:54 ` Laurenz Albe <[email protected]>
2023-09-12 12:12 ` Nature Conservation Geovista Space <[email protected]>
2023-09-12 12:48 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox