pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16739: Temporary files not deleting from data folder on disk
12+ messages / 6 participants
[nested] [flat]
* BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-23 08:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 12+ messages in thread
From: PG Bug reporting form @ 2020-11-23 08:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: manojkumar@ameyo.com
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-23 15:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2020-11-23 15:33 UTC (permalink / raw)
To: manojkumar@ameyo.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-24 15:47 Manoj Kumar <manojkumar@ameyo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Manoj Kumar @ 2020-11-24 15:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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.
Attachments:
ameyo-new.png (image/png, 3.2K) download
view image
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-24 16:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Manoj Kumar <manojkumar@ameyo.com>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2020-11-24 16:42 UTC (permalink / raw)
To: Manoj Kumar <manojkumar@ameyo.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-24 18:51 Maxim Boguk <maxim.boguk@gmail.com>
parent: Manoj Kumar <manojkumar@ameyo.com>
1 sibling, 1 reply; 12+ messages in thread
From: Maxim Boguk @ 2020-11-24 18:51 UTC (permalink / raw)
To: Manoj Kumar <manojkumar@ameyo.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-24 18:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maxim Boguk <maxim.boguk@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2020-11-24 18:59 UTC (permalink / raw)
To: Maxim Boguk <maxim.boguk@gmail.com>; +Cc: Manoj Kumar <manojkumar@ameyo.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2020-11-25 04:39 Manoj Kumar <manojkumar@ameyo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Manoj Kumar @ 2020-11-25 04:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Maxim Boguk <maxim.boguk@gmail.com>; pgsql-bugs@lists.postgresql.org
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.
Attachments:
ameyo-new.png (image/png, 3.2K) download
view image
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2023-06-05 09:54 Adrien Nayrat <adrien.nayrat@anayrat.info>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 12+ messages in thread
From: Adrien Nayrat @ 2023-06-05 09:54 UTC (permalink / raw)
To: manojkumar@ameyo.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2023-06-05 09:59 Adrien Nayrat <adrien.nayrat@anayrat.info>
parent: Adrien Nayrat <adrien.nayrat@anayrat.info>
0 siblings, 1 reply; 12+ messages in thread
From: Adrien Nayrat @ 2023-06-05 09:59 UTC (permalink / raw)
To: manojkumar@ameyo.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2023-06-05 10:14 Dilip Kumar <dilipbalaut@gmail.com>
parent: Adrien Nayrat <adrien.nayrat@anayrat.info>
0 siblings, 1 reply; 12+ messages in thread
From: Dilip Kumar @ 2023-06-05 10:14 UTC (permalink / raw)
To: Adrien Nayrat <adrien.nayrat@anayrat.info>; +Cc: manojkumar@ameyo.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2023-06-05 10:40 Adrien Nayrat <adrien.nayrat@anayrat.info>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Adrien Nayrat @ 2023-06-05 10:40 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: manojkumar@ameyo.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16739: Temporary files not deleting from data folder on disk
@ 2023-06-05 10:48 Dilip Kumar <dilipbalaut@gmail.com>
parent: Adrien Nayrat <adrien.nayrat@anayrat.info>
0 siblings, 0 replies; 12+ messages in thread
From: Dilip Kumar @ 2023-06-05 10:48 UTC (permalink / raw)
To: Adrien Nayrat <adrien.nayrat@anayrat.info>; +Cc: manojkumar@ameyo.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-06-05 10:48 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-23 08:41 BUG #16739: Temporary files not deleting from data folder on disk PG Bug reporting form <noreply@postgresql.org>
2020-11-23 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-24 15:47 ` Manoj Kumar <manojkumar@ameyo.com>
2020-11-24 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-24 18:51 ` Maxim Boguk <maxim.boguk@gmail.com>
2020-11-24 18:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-25 04:39 ` Manoj Kumar <manojkumar@ameyo.com>
2023-06-05 09:54 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2023-06-05 09:59 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2023-06-05 10:14 ` Dilip Kumar <dilipbalaut@gmail.com>
2023-06-05 10:40 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2023-06-05 10:48 ` Dilip Kumar <dilipbalaut@gmail.com>
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