Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qPRHZ-00B1Rq-6b for pgsql-general@arkaria.postgresql.org; Fri, 28 Jul 2023 17:29:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qPRHX-00HYdd-EK for pgsql-general@arkaria.postgresql.org; Fri, 28 Jul 2023 17:29:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qPRHW-00HYdU-Vh for pgsql-general@lists.postgresql.org; Fri, 28 Jul 2023 17:29:39 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qPRHS-0024mr-Jd for pgsql-general@lists.postgresql.org; Fri, 28 Jul 2023 17:29:37 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-56c7eb17945so234217eaf.2 for ; Fri, 28 Jul 2023 10:29:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1690565374; x=1691170174; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=cwoTVuDQ1LR/kuU22pa4iHJzhcHULzVzDtYhcnQe/ng=; b=HSXNC3sCnW6bmsk9u2VxNS2THrzISHJvHjKgiAZz0pxK8JGRDVnnkcBAqwxmVp/Zs/ I5K2BLP74utod05rMsb9GRtlp6c7Nf21N3KLrPECyOSi5VvB8+RFYDyMErOZAqIA0rwe ifOAw0OwIUuxUOvz0GxamK9zjHhXPtFhF0C6GPcL6Z/sFZl2rntlh3J5JMe6cEz6z8s4 LJwF7AHih6PP8AorVXtfrocwxGRAycgtyaRaL60vftTR0nBQUy/U4IDUsTSi5mNMWP3m YOOhiaE1ywbU18yVQRsDYOtL2FKm0lyvYpNqCH0EgbTl2SF390vOyvs16bkw9bz6hQ+K PSYQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1690565374; x=1691170174; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=cwoTVuDQ1LR/kuU22pa4iHJzhcHULzVzDtYhcnQe/ng=; b=YZT4f9CNnXF5sGPx9HJ3I2ksWLXZuoq3ZNBrjGvsr1HxoXJkQBFNIoT36saL1Sd9P0 bsmfXluoeFowwBb+oaKWIZSLq+AfN+6RbsFBoeQMzlhi2GAVS5j7JQgq2ourzupkFc8d VG3QOlKWPSvQwMNia7NWn0/8q484oTWiodsh2XTEz5HPBtiiUsAr5GdMJZav5zhJwB+8 07jKtJEqCzg4LONHB4kf5eiNNtr//aW+HRAXYq/1HCaVApIb+ViVMnnLl6qQqGjkqNUF dI3HlHymoplgLhHxc1694PNqr8ZtpaTwVIZRp/2TvqXCV1IRf+JJuYPNVgm90uemsRlL T9iQ== X-Gm-Message-State: ABy/qLY89edPMa98VSohZGfgIIDmyLMNskGr+Un9Z/jaU7PTid81T1QC m7yHHta7s9ODEhdm6A7OEeqxyq25xlk= X-Google-Smtp-Source: APBJJlGsxVzo9JDygdOrT4IwFq9UnL3Lm/rmpeBq/ojgbdEUmZZ7w3IOnid9m2AyVGBipq48eHE7KA== X-Received: by 2002:a05:6358:33a2:b0:135:24ed:5108 with SMTP id i34-20020a05635833a200b0013524ed5108mr3017356rwd.10.1690565373682; Fri, 28 Jul 2023 10:29:33 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:c11c:5df6:9acb:ecd1? ([2600:1700:bd2c:b810:c11c:5df6:9acb:ecd1]) by smtp.googlemail.com with ESMTPSA id u94-20020a25ab67000000b00d20d4ffbbdbsm951421ybi.0.2023.07.28.10.29.32 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 28 Jul 2023 10:29:33 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------VZTr0xaB88VYHpAocEv0Ylrl" Message-ID: <99686954-6faa-4022-6361-df4181a4349e@gmail.com> Date: Fri, 28 Jul 2023 12:29:32 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Bogus temp file reporting? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------VZTr0xaB88VYHpAocEv0Ylrl Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------VZTr0xaB88VYHpAocEv0Ylrl Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 231TB 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.
--------------VZTr0xaB88VYHpAocEv0Ylrl--