From: Justin Pryzby <pryzby@telsasoft.com>
To: Frits Jalvingh <jal@etc.to>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Fwd: temp_file_limit?
Date: Mon, 19 Dec 2022 11:15:43 -0600
Message-ID: <20221219171543.GZ1153@telsasoft.com> (raw)
In-Reply-To: <CAKhTGFXkTstt5NUqfk=WWbKZW0h16hijjvtJM_yK_XMLiUuHyg@mail.gmail.com>
References: <CAKhTGFXmSBSXYjBXVGiQ_VO7Wz14SrNy2sQOU8KKH3priWKWUQ@mail.gmail.com>
<20221218155705.GT1153@telsasoft.com>
<CAKhTGFVwMLCL04hU6Q9rvwg0q2C9aCrj3KKOcgPjL-JRQwTDUg@mail.gmail.com>
<CAKhTGFX7uH3bK-NTcOB06+2TA0j5Czu3YWrepD7v8VNT45d99Q@mail.gmail.com>
<20221218201052.GV1153@telsasoft.com>
<CAKhTGFXkTstt5NUqfk=WWbKZW0h16hijjvtJM_yK_XMLiUuHyg@mail.gmail.com>
On Mon, Dec 19, 2022 at 05:57:42PM +0100, Frits Jalvingh wrote:
> @justin:
>
> Ran the query again. Top shows the following processes:
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
Thanks
> root@chatelet:/d2/var/lib/postgresql/15/main/base# du --max=2 -mx
> ./pgsql_tmp |sort -nr
> 412021 ./pgsql_tmp/pgsql_tmp650830.3.fileset
> 412021 ./pgsql_tmp
> ^^^ a few seconds after this last try the query aborted:
> ERROR: temporary file size exceeds temp_file_limit (104857600kB)
>
> One possibility is that there are files in the tmpdir, which have been
> > unlinked, but are still opened, so their space hasn't been reclaimed.
> > You could check for that by running lsof -nn |grep pgsql_tmp Any deleted
> > files would say things like 'DEL|deleted|inode|no such'
>
> I do not really understand what you would like me to do, and when. The disk
> space is growing, and it is actual files under pgsql_tmp?
Run this during the query as either postgres or root:
| lsof -nn |grep pgsql_tmp |grep -E 'DEL|deleted|inode|no such'
Any files it lists would be interesting to know about.
> Hope this tells you something, please let me know if you would like more
> info, and again - thanks!
I think Thomas' idea is more likely. We'd want to know the names of
files being written, either as logged by log_temp_files or from
| find pgsql_tmp -ls
during the query.
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, jal@etc.to, pgsql-performance@lists.postgresql.org
Subject: Re: Fwd: temp_file_limit?
In-Reply-To: <20221219171543.GZ1153@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox