Re: PostgreSQL 9.2.4 temp files never released?

From: Edson Richter <edsonrichter(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL 9.2.4 temp files never released?
Date: 2013-09-28 21:25:43
Message-ID: BLU0-SMTP179B92C5102247CD961A4B3CF2A0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Em 28/09/2013 18:12, Tomas Vondra escreveu:
> On 28 Září 2013, 22:54, Edson Richter wrote:
>> Em 28/09/2013 15:54, Adrian Klaver escreveu:
>>> On 09/28/2013 11:30 AM, Edson Richter wrote:
>>>> Em 28/09/2013 15:22, Adrian Klaver escreveu:
>>>>> On 09/28/2013 11:16 AM, Edson Richter wrote:
>>>>>> I've a 12Gb database running without problems in Linux Centos 64bit
>>>>>> for
>>>>>> years now.
>>>>>> Looking database statistics (in pgAdmin III), I can see that there
>>>>>> are
>>>>>> 366 temporary files, and they sum up 11,863,839,867 bytes in size.
>>>>> What are the temp files named and where are they located?
>>>> Sorry if this sounds silly, but how can I discover this information?
>>> Assuming pgAdmin is using pg_stat_database then:
>>> "Temporary files (for operations such as sorting more data than can
>>> fit in memory) are created within PGDATA/base/pgsql_tmp, or within a
>>> pgsql_tmp subdirectory of a tablespace directory if a tablespace other
>>> than pg_default is specified for them. The name of a temporary file
>>> has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning
>>> backend and NNN distinguishes different temporary files of that
>>> backend."
>> Ok. Found the place.
>> So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory.
>> I've run the query over pg_stat_database view and there is nothing wrong
>> with pgAdmin III - the information is all there.
>> I've also run a vacuum freeze analyze, but made no difference.
>> I believe that statistics are outdated, since there is no temp file at
>> all.
> Do you realize the counters in pg_stat_database (temp_files, temp_bytes)
> are counters tracking all the temp files created since the last reset of
> the stats (possible since the cluster was created)?
> So these values only increment (and it's meant to behave like that). BTW
> this is true for most values in the stats catalogs, so there's nothing
> like "current stats" - to get something like that you need to get two
> snapshots and subtract them (to get the delta).
> Tomas

Ah, ok! One more lesson learned...
I would expect stats being current of, or up-to-date after a vacuum
Thank you all very much.


In response to


Browse pgsql-general by date

  From Date Subject
Next Message Edson Richter 2013-09-28 21:26:58 Re: PostgreSQL 9.2.4 temp files never released?
Previous Message Jeff Janes 2013-09-28 21:23:45 Re: PostgreSQL 9.2.4 temp files never released?