pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedBogus temp file reporting?
4+ messages / 3 participants
[nested] [flat]
* Bogus temp file reporting?
@ 2023-07-27 07:30 kg.postgresql@olympiakos.com
0 siblings, 2 replies; 4+ messages in thread
From: kg.postgresql@olympiakos.com @ 2023-07-27 07:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
Is there a known issue with temp file reporting? I have a production db that is reporting 231TB of temp space usage, which can’t be true. temp_blks_written in pg_stat_statements sort of confirms this, as the reported queries show just a few MB of temp space usage. I suspect a reboot will clear this (to do) and wanted to check with you to see if someone else has had similar experiences.
Regards,
Kiriakos Georgiou
select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.8 (Ubuntu 14.8-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
select
datname,
temp_files as "# of temp files",
pg_size_pretty(temp_bytes) as "size of temp files"
from
pg_stat_database;
datname | # of temp files | size of temp files
---------------+-----------------+--------------------
| 0 | 0 bytes
postgres | 0 | 0 bytes
testdb | 269647 | 231 TB
template0 | 0 | 0 bytes
template1 | 0 | 0 bytes=
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bogus temp file reporting?
@ 2023-07-27 08:31 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
parent: kg.postgresql@olympiakos.com
1 sibling, 1 reply; 4+ messages in thread
From: Kyotaro Horiguchi @ 2023-07-27 08:31 UTC (permalink / raw)
To: kg.postgresql@olympiakos.com; +Cc: pgsql-general@lists.postgresql.org
At Thu, 27 Jul 2023 10:30:41 +0300, kg.postgresql@olympiakos.com wrote in
> Hello,
>
> Is there a known issue with temp file reporting? I have a
> production db that is reporting 231TB of temp space usage, which
> can¢t be true. temp_blks_written in pg_stat_statements sort of
> confirms this, as the reported queries show just a few MB of temp
> space usage. I suspect a reboot will clear this (to do) and wanted
> to check with you to see if someone else has had similar
> experiences.
Just to clarify, "temprary files" and "temp blocks" refer to different
entities in this context. The "temprary files" are ephemeral storage
that is created and alive only for tasks like tuple sorting, whereas
"temp blocks" are referring to the storage for temprary tables.
If you're performing a massive insert into a temporary table, you'll
find that pg_stat_database.temp_bytes doesn't increase at all.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bogus temp file reporting?
@ 2023-07-27 10:27 kg.postgresql@olympiakos.com
parent: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: kg.postgresql@olympiakos.com @ 2023-07-27 10:27 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Thanks for the clarification. Also, I was wrong about the temp_* columns in the pg_stat_database view in that they report the overall temp file usage since the last stats reset and not the “current” usage.
Regards,
Kiriakos Georgiou
> On Jul 27, 2023, at 11:31 AM, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
>
> At Thu, 27 Jul 2023 10:30:41 +0300, kg.postgresql@olympiakos.com <mailto:kg.postgresql@olympiakos.com> wrote in
>> Hello,
>>
>
>> Is there a known issue with temp file reporting? I have a
>> production db that is reporting 231TB of temp space usage, which
>> can’t be true. temp_blks_written in pg_stat_statements sort of
>> confirms this, as the reported queries show just a few MB of temp
>> space usage. I suspect a reboot will clear this (to do) and wanted
>> to check with you to see if someone else has had similar
>> experiences.
>
>
> Just to clarify, "temprary files" and "temp blocks" refer to different
> entities in this context. The "temprary files" are ephemeral storage
> that is created and alive only for tasks like tuple sorting, whereas
> "temp blocks" are referring to the storage for temprary tables.
>
> If you're performing a massive insert into a temporary table, you'll
> find that pg_stat_database.temp_bytes doesn't increase at all.
>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bogus temp file reporting?
@ 2023-07-28 17:29 Ron <ronljohnsonjr@gmail.com>
parent: kg.postgresql@olympiakos.com
1 sibling, 0 replies; 4+ messages in thread
From: Ron @ 2023-07-28 17:29 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
231*TB* seems, to me, an unreasonably large number.
On 7/27/23 02:30, kg.postgresql@olympiakos.com wrote:
> Hello,
>
> Is there a known issue with temp file reporting? I have a production db
> that is reporting 231TB of temp space usage, which can’t be true.
> temp_blks_written in pg_stat_statements sort of confirms this, as the
> reported queries show just a few MB of temp space usage. I suspect a
> reboot will clear this (to do) and wanted to check with you to see if
> someone else has had similar experiences.
>
> Regards,
> Kiriakos Georgiou
>
>
> select version();
> version
> -----------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 14.8 (Ubuntu 14.8-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
> (1 row)
> select
> datname,
> temp_files as "# of temp files",
> pg_size_pretty(temp_bytes) as "size of temp files"
> from
> pg_stat_database;
>
> datname | # of temp files | size of temp files
> ---------------+-----------------+--------------------
> | 0 | 0 bytes
> postgres | 0 | 0 bytes
> testdb | 269647 | 231 TB
> template0 | 0 | 0 bytes
> template1 | 0 | 0 bytes
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-07-28 17:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-27 07:30 Bogus temp file reporting? kg.postgresql@olympiakos.com
2023-07-27 08:31 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-07-27 10:27 ` kg.postgresql@olympiakos.com
2023-07-28 17:29 ` Ron <ronljohnsonjr@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