Обсуждение: pg_stat_tmp

Hi all,

The past few days I’ve been encountering the following error, followed by a full db restart and recovery

2013-12-16 07:12:53 GMT LOG:  could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device

This occurs at a time of moderate load, during the same set of operations each morning.

Interestingly, when I execute this manually at any other time of date, the process completes normally.

I presume that the pg_stat_tmp location is system-wide and likely is not impacted by temp_tablespaces

The root partition, where postgresql is installed does not have a lot of disk available (4GB). 

My first instinct here is to symlink pg_stat_tmp against another disk with a little more room to breathe, however I’m surprised that pgstat.tmp would grow to be so large in the first place – possibly there is something else at play here.

I should note that this issue has only recently occurred, no major changes have been introduced.

Thanks for any advice on this.

Hi all,

The past few days I’ve been encountering the following error, followed by a full db restart and recovery

2013-12-16 07:12:53 GMT LOG:  could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device

This occurs at a time of moderate load, during the same set of operations each morning.

Interestingly, when I execute this manually at any other time of date, the process completes normally.

I presume that the pg_stat_tmp location is system-wide and likely is not impacted by temp_tablespaces

The root partition, where postgresql is installed does not have a lot of disk available (4GB).

My first instinct here is to symlink pg_stat_tmp against another disk with a little more room to breathe, however I’m surprised that pgstat.tmp would grow to be so large in the first place – possibly there is something else at play here.

Thanks Jeff, Magnus

Thanks for the suggestions.

This morning the same issue occurred, but this time it also complained that it failed to write to pg_clog  (previous days, the only failure was in writing to pg_stat_tmp)

2013-12-17 07:13:08 GMT LOG:  could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on dev2013-12-17 07:25:15 GMT WARNING:  terminating connection because of crash of another server

 process

It’s never failed to write pg_xlog’s though – or at least, never reported a failure.

For now, I’ve altered the pg_stat_tmp path and we’ll see what happens tomorrow – Ideally though, yes.. I’d like to see this happening at runtime and get a better picture of what’s blowing out here.

Further to this however, I notice that the base directory is consuming more disk than I would have expected it to (all our relations are stored in tablespaces assigned to other disks).

Looking for the larger culprits, I note the following files consuming 4.4GB

9.2/main/base/74641/2260957

9.2/main/base/74641/2260957.1

9.2/main/base/74641/2260957.2

9.2/main/base/74641/2260957.3

9.2/main/base/74641/2260957.4

I notice the timestamps are just shy of the time we experience that pg_stat_tmp issue.

-rw------- 1 postgres postgres 1073741824 Dec 17 06:56 2260957

-rw------- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.1

-rw------- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.2

-rw------- 1 postgres postgres 1073741824 Dec 17 06:56 2260957.3

-rw------- 1 postgres postgres  328466432 Dec 17 06:56 2260957.4

They appear to be orphaned oid’s, though I’m not sure this is definitive:

clone=# select pg_relation_filepath(2260957);