[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Bogus temp file reporting?
From: Ron <ronljohnsonjr () gmail ! com>
Date: 2023-07-28 17:29:32
Message-ID: 99686954-6faa-4022-6361-df4181a4349e () gmail ! com
[Download RAW message or body]
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.
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
231<b>TB</b> seems, to me, an unreasonably large number.<br>
<br>
<div class="moz-cite-prefix">On 7/27/23 02:30,
<a class="moz-txt-link-abbreviated" \
href="mailto:kg.postgresql@olympiakos.com">kg.postgresql@olympiakos.com</a> \
wrote:<br> </div>
<blockquote type="cite"
cite="mid:FBC3A2FB-D1BC-4903-8211-E4D6D0700F0A@olympiakos.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<div style="margin: 0in; font-size: 11pt;" class="">Hello,</div>
<div style="margin: 0in; font-size: 11pt;" class=""><br class="">
</div>
<div style="margin: 0in; font-size: 11pt;" class="">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.</div>
<div style="margin: 0in; font-size: 11pt;" class=""><br class="">
</div>
<div style="margin: 0in; font-size: 11pt;" class="">Regards,</div>
<div style="margin: 0in; font-size: 11pt;" class="">Kiriakos
Georgiou</div>
<div style="margin: 0in; font-size: 11pt;" class=""><br class="">
</div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"><br class="">
</font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">select version();<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> \
version <o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier \
New">-----------------------------------------------------------------------------------------------------------------------------------<o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">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<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">(1 row)<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> </font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> </font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">select <o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> datname,<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> temp_files as "# of temp files",<o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> pg_size_pretty(temp_bytes) as "size of
temp files"<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">from<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">pg_stat_database;<o:p class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"><br class="">
</font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> datname | # of temp files | size of
temp files</font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><span
style="font-family: "Courier New"; font-size: 11pt;"
class="">---------------+-----------------+--------------------</span></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New"> | 0 | 0 bytes<o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">postgres | 0 | 0 bytes<o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">testdb | 269647 | 231 TB<o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">template0 | 0 | 0 bytes<o:p
class=""></o:p></font></div>
<div style="margin: 0in; font-size: 11pt;" class=""><font class=""
face="Courier New">template1 | 0 | 0 bytes</font></div>
</blockquote>
<br>
<div class="moz-signature">-- <br>
Born in Arizona, moved to Babylonia.</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic