Horton

Thread

  1. BUG #16739: Temporary files not deleting from data folder on disk

    PG Bug reporting form <noreply@postgresql.org> — 2020-11-23T08:41:07Z

    The following bug has been logged on the website:

Bug reference:      16739
Logged by:          Manoj Kumar
Email address:      manojkumar@ameyo.com
PostgreSQL version: 10.8
Operating system:   Centos OS 7.6 Version 0
Description:        

There is a problem with Postgres that temporary tables being created are not
cleaning up by Postgres itself.
We have an AutoVacuum job going on for the cleanup as well.
However, when we restart the Postgres service this data is cleaned up by
itself


We are facing huge issues with this problem, as our customer database size
is tremendously increasing from 80GB (actual Size) to 7TB.
Hampering the live production.

need a solution for this ASAP.
Database size on Disk-
weblogin_setup7          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
                      | 7338 GB | pg_default |

Count of temporary files-
select count(*) from pg_ls_dir ( '/var/lib/pgsql/10/data/base/16384' ) as
file where file::text not in (select oid::text from pg_class );
 count
-------
 95263
(1 row)

The Actual size of the database -
38 GB

  2. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-23T15:33:21Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> There is a problem with Postgres that temporary tables being created are not
> cleaning up by Postgres itself.

Can you produce any actual evidence of that, or even better a reproduction
sequence?

> Count of temporary files-
> select count(*) from pg_ls_dir ( '/var/lib/pgsql/10/data/base/16384' ) as
> file where file::text not in (select oid::text from pg_class );

This is not evidence, because the test is completely incorrect.

* OID is not the pg_class field to use.  relfilenode is closer,
although that will still mislead you for certain system catalogs.
pg_relation_filenode(oid) is really the recommended way to get the
base file name for a pg_class entry.

* Even once you've got the right filename, it's only the *base* file
name.  There might be additional segments (nnn.1, nnn.2, etc) if any
table exceeds 1GB.  There are likely to also be "nnn_fsm" and
"nnn_vm" subsidiary files, and maybe "nnn_init" files.  All of those
would have to be excluded before concluding that files are being
leaked.

See

https://www.postgresql.org/docs/current/storage.html

for more info about what you should expect to see in a database
directory.

			regards, tom lane

  3. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Manoj Kumar <manojkumar@ameyo.com> — 2020-11-24T15:47:38Z

    Hello Tom,

Thanks for writing back.

I have checked the files, but these files are temporary and vanish once the
Postgres service is restarted.
This has been seen many times.

Adding more data to support my point in form of attachment.

*Thanks and Regards*
[image: Inline image 1]
*Manoj Kumar*
Associate Tech Lead |  AMEYO
<http://www.ameyo.com/?utm_source=signature&utm_medium=email&utm_campaign=Email-Signature>
+91 9999437526
[image: Facebook] <https://www.facebook.com/AmeyoCIM> [image: Twitter]
<https://twitter.com/AmeyoCIM> [image: Google Plus]
<https://plus.google.com/+AmeyoCIM/> [image: Linkedin]
<https://www.linkedin.com/company/ameyocim>


On Mon, Nov 23, 2020 at 9:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> PG Bug reporting form <noreply@postgresql.org> writes:
> > There is a problem with Postgres that temporary tables being created are
> not
> > cleaning up by Postgres itself.
>
> Can you produce any actual evidence of that, or even better a reproduction
> sequence?
>
> > Count of temporary files-
> > select count(*) from pg_ls_dir ( '/var/lib/pgsql/10/data/base/16384' ) as
> > file where file::text not in (select oid::text from pg_class );
>
> This is not evidence, because the test is completely incorrect.
>
> * OID is not the pg_class field to use.  relfilenode is closer,
> although that will still mislead you for certain system catalogs.
> pg_relation_filenode(oid) is really the recommended way to get the
> base file name for a pg_class entry.
>
> * Even once you've got the right filename, it's only the *base* file
> name.  There might be additional segments (nnn.1, nnn.2, etc) if any
> table exceeds 1GB.  There are likely to also be "nnn_fsm" and
> "nnn_vm" subsidiary files, and maybe "nnn_init" files.  All of those
> would have to be excluded before concluding that files are being
> leaked.
>
> See
>
> https://www.postgresql.org/docs/current/storage.html
>
> for more info about what you should expect to see in a database
> directory.
>
>                         regards, tom lane
>

-- 


*Disclaimer:* The information in this communication is confidential and 
may be legally privileged. It is intended solely for the use of the 
individual or entity to whom it is addressed and others authorized to 
receive it. If you are not the intended recipient you are hereby notified 
that any disclosure, copying, distribution or taking action in reliance of 
the contents of this information is strictly prohibited and may be 
unlawful. Drishti is neither liable for the improper, incomplete 
transmission of the information contained in this communication nor any 
delay in its receipt. The communication is not intended to operate as an 
electronic signature under any applicable law. Drishti assumes no 
responsibility for any loss or damage resulting from the use of e-mails.

  4. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-24T16:42:16Z

    Manoj Kumar <manojkumar@ameyo.com> writes:
> Adding more data to support my point in form of attachment.

Well, those t-something files are certainly temporary tables,
so what you've got there is that some session has created a bunch
of quite large temporary tables (the "t20_" files) and another has
created a bunch of rather smaller ones (the "t62_" group).

However, this is not direct evidence of PG misbehavior.  You've
offered no reason to think that the sessions that made those files
have exited.  Even if you're sure they have, we can't do anything
to help you on the basis of the zero facts you've supplied so far
about what the sessions were doing.

If you can provide a reproducible test case that leaks temp tables,
we could get to the bottom of it pretty quickly, I'm sure.  But
we cannot help you on the basis of what you've shown us.

			regards, tom lane

  5. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Maxim Boguk <maxim.boguk@gmail.com> — 2020-11-24T18:51:04Z

    On Tue, Nov 24, 2020 at 5:57 PM Manoj Kumar <manojkumar@ameyo.com> wrote:

> I have checked the files, but these files are temporary and vanish once
> the Postgres service is restarted.
> This has been seen many times.
>
> Adding more data to support my point in form of attachment.
>

It seems that these temp files are the result of normal query execution
(and will be cleared when query finishes).

Have you tried enabling log_temp_files and check what query generates these
temp files?
It is a very common situation when a single badly written query generates
hundred gigabytes of temp files until the end of disk space.
Alternatively, you can try enabling temp_file_limit to limit the maximum
size of temp files per query.

Kind regards,
Maxim


-- 
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone RU: +7  985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"

  6. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-24T18:59:02Z

    Maxim Boguk <maxim.boguk@gmail.com> writes:
> It seems that these temp files are the result of normal query execution
> (and will be cleared when query finishes).
> Have you tried enabling log_temp_files and check what query generates these
> temp files?

Judging by the file names, these are temp tables (created by CREATE TEMP
TABLE or the like), not temp files generated during query execution.
The latter would have names beginning in "pgsql_tmp", if I read the
code correctly.

			regards, tom lane

  7. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Manoj Kumar <manojkumar@ameyo.com> — 2020-11-25T04:39:37Z

    Thanks, Tom for your reply.

I am struggling to find out the name of the table corresponding to these
temporary files (files starting with t on, saved on disk).
If somehow I can discover these, it will be helpful to close down the
problem.


*Thanks and Regards*
[image: Inline image 1]
*Manoj Kumar*
Associate Tech Lead |  AMEYO
<http://www.ameyo.com/?utm_source=signature&utm_medium=email&utm_campaign=Email-Signature>
+91 9999437526
[image: Facebook] <https://www.facebook.com/AmeyoCIM> [image: Twitter]
<https://twitter.com/AmeyoCIM> [image: Google Plus]
<https://plus.google.com/+AmeyoCIM/> [image: Linkedin]
<https://www.linkedin.com/company/ameyocim>


On Wed, Nov 25, 2020 at 12:29 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Maxim Boguk <maxim.boguk@gmail.com> writes:
> > It seems that these temp files are the result of normal query execution
> > (and will be cleared when query finishes).
> > Have you tried enabling log_temp_files and check what query generates
> these
> > temp files?
>
> Judging by the file names, these are temp tables (created by CREATE TEMP
> TABLE or the like), not temp files generated during query execution.
> The latter would have names beginning in "pgsql_tmp", if I read the
> code correctly.
>
>                         regards, tom lane
>

-- 


*Disclaimer:* The information in this communication is confidential and 
may be legally privileged. It is intended solely for the use of the 
individual or entity to whom it is addressed and others authorized to 
receive it. If you are not the intended recipient you are hereby notified 
that any disclosure, copying, distribution or taking action in reliance of 
the contents of this information is strictly prohibited and may be 
unlawful. Drishti is neither liable for the improper, incomplete 
transmission of the information contained in this communication nor any 
delay in its receipt. The communication is not intended to operate as an 
electronic signature under any applicable law. Drishti assumes no 
responsibility for any loss or damage resulting from the use of e-mails.

  8. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Adrien Nayrat <adrien.nayrat@anayrat.info> — 2023-06-05T09:54:27Z

    Hello,


I am experiencing the same issue on an up-to-date Postgres (13.11).
I have many files corresponding to temporary tables (for example 
t7_4191281453.90). These files are not cleaned until we manually restart 
Postgres (all client session are closed).

When temporary table were created, we reached disk full (Disk quota 
exceeded). Then the instance crashed and restarted in recovery mode. But 
files related to temporary tables still exist.

It seems there is something missing to clean these files after a recovery ?

Regards,

-- 
Adrien NAYRAT

  9. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Adrien Nayrat <adrien.nayrat@anayrat.info> — 2023-06-05T09:59:04Z

    On 6/5/23 11:54, Adrien Nayrat wrote:
> Hello,
> 
> 
> I am experiencing the same issue on an up-to-date Postgres (13.11).
> I have many files corresponding to temporary tables (for example 
> t7_4191281453.90). These files are not cleaned until we manually restart 
> Postgres (all client session are closed).
> 
> When temporary table were created, we reached disk full (Disk quota 
> exceeded). Then the instance crashed and restarted in recovery mode. But 
> files related to temporary tables still exist.
> 
> It seems there is something missing to clean these files after a recovery ?
> 
> Regards,
> 

Replying to myself when reading this item in PG 14 release notes:

Remove temporary files after backend crashes (Euler Taveira)

Previously, such files were retained for debugging purposes. If 
necessary, deletion can be disabled with the new server parameter 
remove_temp_files_after_crash.


I understand, it is expected to keep these files for debugging purpose 
after a recovery. And since 14, we can ask Postgres to clean them thanks 
to remove_temp_files_after_crash

  10. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Dilip Kumar <dilipbalaut@gmail.com> — 2023-06-05T10:14:14Z

    On Mon, Jun 5, 2023 at 3:29 PM Adrien Nayrat <adrien.nayrat@anayrat.info> wrote:
>
>
> > I am experiencing the same issue on an up-to-date Postgres (13.11).
> > I have many files corresponding to temporary tables (for example
> > t7_4191281453.90). These files are not cleaned until we manually restart
> > Postgres (all client session are closed).
> >
>
> Replying to myself when reading this item in PG 14 release notes:
>
> Remove temporary files after backend crashes (Euler Taveira)
>
> Previously, such files were retained for debugging purposes. If
> necessary, deletion can be disabled with the new server parameter
> remove_temp_files_after_crash.
>
>
> I understand, it is expected to keep these files for debugging purpose
> after a recovery. And since 14, we can ask Postgres to clean them thanks
> to remove_temp_files_after_crash

IIUC, this flag is for the temp files created for query level
operation so this will clean up the files like  "pgsql_tmp" but in
this thread, we are talking about the files related to temp tables
starting with "t_"

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

  11. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Adrien Nayrat <adrien.nayrat@anayrat.info> — 2023-06-05T10:40:14Z

    On 6/5/23 12:14, Dilip Kumar wrote:
> IIUC, this flag is for the temp files created for query level
> operation so this will clean up the files like  "pgsql_tmp" but in
> this thread, we are talking about the files related to temp tables
> starting with "t_"

I am not sure per comment and code in fd.c :


/*
  * Remove temporary and temporary relation files left over from a prior
  * postmaster session
  *
  * This should be called during postmaster startup.  It will forcibly
  * remove any leftover files created by OpenTemporaryFile and any leftover
  * temporary relation files created by mdcreate.
  *
  * During post-backend-crash restart cycle, this routine is called when
  * remove_temp_files_after_crash GUC is enabled. Multiple crashes while
  * queries are using temp files could result in useless storage usage 
that can
  * only be reclaimed by a service restart. The argument against 
enabling it is
  * that someone might want to examine the temporary files for debugging
  * purposes. This does however mean that OpenTemporaryFile had better 
allow for
  * collision with an existing temp file name.

The function RemovePgTempFiles is called, which call 
RemovePgTempRelationFiles, then RemovePgTempRelationFilesInDbspace
and looks_like_temp_rel_name which check:
/* t<digits>_<digits>, or t<digits>_<digits>_<forkname> */




-- 
Adrien NAYRAT

  12. Re: BUG #16739: Temporary files not deleting from data folder on disk

    Dilip Kumar <dilipbalaut@gmail.com> — 2023-06-05T10:48:39Z

    On Mon, Jun 5, 2023 at 4:10 PM Adrien Nayrat <adrien.nayrat@anayrat.info> wrote:
>

> I am not sure per comment and code in fd.c :
>

Looking at the code, I think you are right, I was just confused thanks
for pointing that out.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com