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:
>>
>> http://www.postgresql.org/docs/9.2/static/storage-file-layout.html
>>
>> "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
