Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)

[Subhasis Bhattacharya]

Hi All,

Recently a postgres database embedded within our product faced a series of
hiccups at a customer site, as follows:

For about 24 h ours the postgres log file had logged errors like:

2012-09-24 00:00:12 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:13 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:15 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:16 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:20 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:21 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:22 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:22 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:23 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error
2012-09-24 00:00:24 GMTLOG: could not rename temporary statistics file
"global/pgstat.tmp" to "global/pgstat.stat": Input/output error

....
Its an extremely volatile database with rows deleted very often....

After about 24 hours we saw this:
2012-10-01 00:19:21 GMTWARNING: relation "pg_toast.pg_toast_16509"
contains more than "max_fsm_pages" pages with useful free space
2012-10-01 00:19:21 GMTHINT: Consider using VACUUM FULL on this relation
or increasing the configuration parameter "max_fsm_pages".
2012-10-01 00:57:12 GMTWARNING: relation "pbs.job_attr" contains more than
"max_fsm_pages" pages with useful free space
2012-10-01 00:57:12 GMTHINT: Consider using VACUUM FULL on this relation
or increasing the configuration parameter "max_fsm_pages".
2012-10-01 01:09:30 GMTWARNING: relation "pg_toast.pg_toast_16509"
contains more than "max_fsm_pages" pages with useful free space
2012-10-01 01:09:30 GMTHINT: Consider using VACUUM FULL on this relation
or increasing the configuration parameter "max_fsm_pages".
2012-10-01 01:18:45 GMTERROR: canceling autovacuum task


So looks like autovacuum encountered a situation it could not handle and
was asking for a manual vacuum full. Queries henceforth ran very very slow,
since autovacuum kept popping up and cancelling itself...

My question to the postgres guru's or developer's:

- I read that the autovacuum daemon depends on the statistics collector to
do its job properly. Could it be that the fact that the collector could not
update the pgstat file earlier for over 24 hours or so, led autovacuum to
NOT do its job leading to the situation where autovacuum could no longer
handle it....

- In a normal situation, should we assume that autovacuum must be able to
do it job without needing a manual vacuum full ever?

Thanks and Regards,
Subhasis Bhattacharya

[Craig Ringer]

Post by Subhasis Bhattacharya
Hi All,
Recently a postgres database embedded within our product faced a

8.3?

An old 8.3 at that?

Do you have any upgrade plans? I'm pretty sure I remember seeing this
discussed a long time ago, but it hasn't come up recently.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[Subhasis Bhattacharya]

Thanks Craig,

We have plans to upgrade to a later version of postgres, but that could
take a while.

Meantime, I wanted to understand whether the autovacuum failure could be
linked to the fact that the stats collector could not update (rename) the
pgstats file?

Thanks and Regards,
Subhasis

Post by Craig Ringer

Post by Subhasis Bhattacharya
Hi All,
Recently a postgres database embedded within our product faced a

8.3?
An old 8.3 at that?
Do you have any upgrade plans? I'm pretty sure I remember seeing this
discussed a long time ago, but it hasn't come up recently.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[Scott Marlowe]

On Sat, Nov 24, 2012 at 7:25 AM, Subhasis Bhattacharya

Post by Subhasis Bhattacharya
Thanks Craig,
We have plans to upgrade to a later version of postgres, but that could take
a while.
Meantime, I wanted to understand whether the autovacuum failure could be
linked to the fact that the stats collector could not update (rename) the
pgstats file?

It's important to understand that Craig is implying you're getting
bitten by a bug in an early version of pg 8.3 that can be fixed by
updating to a later version of pg 8.3. Updating within a minor
version number like that causes minimum down time as it simply copies
in new binary files and restarts the db with no need for upgrading the
/data directory. On most debian based boxes it's as simple as:

sudo apt-get update
or
sudo apt-get upgrade

[Scott Marlowe]

latest is 8.3.21 according to postgresql.org.

Hi Scott,
Thanks for ur reply. BTW I thought 8.3.14 was the latest in the 8.3 series...no?
------Original Message------
From: Scott Marlowe
To: Subhasis Bhattacharya
Cc: Craig Ringer
Subject: Re: [ADMIN] Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
Sent: Nov 28, 2012 4:55 AM
On Sat, Nov 24, 2012 at 7:25 AM, Subhasis Bhattacharya

Post by Subhasis Bhattacharya
Thanks Craig,
We have plans to upgrade to a later version of postgres, but that could take
a while.
Meantime, I wanted to understand whether the autovacuum failure could be
linked to the fact that the stats collector could not update (rename) the
pgstats file?

It's important to understand that Craig is implying you're getting
bitten by a bug in an early version of pg 8.3 that can be fixed by
updating to a later version of pg 8.3. Updating within a minor
version number like that causes minimum down time as it simply copies
in new binary files and restarts the db with no need for upgrading the
sudo apt-get update
or
sudo apt-get upgrade
Sent from BlackBerry® on Airtel

[s***@gmail.com]

Hi Scott,

Thanks for ur reply. BTW I thought 8.3.14 was the latest in the 8.3 series...no?


------Original Message------
From: Scott Marlowe
To: Subhasis Bhattacharya
Cc: Craig Ringer
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
Sent: Nov 28, 2012 4:55 AM

On Sat, Nov 24, 2012 at 7:25 AM, Subhasis Bhattacharya

Post by Subhasis Bhattacharya
Thanks Craig,
We have plans to upgrade to a later version of postgres, but that could take
a while.
Meantime, I wanted to understand whether the autovacuum failure could be
linked to the fact that the stats collector could not update (rename) the
pgstats file?

It's important to understand that Craig is implying you're getting
bitten by a bug in an early version of pg 8.3 that can be fixed by
updating to a later version of pg 8.3. Updating within a minor
version number like that causes minimum down time as it simply copies
in new binary files and restarts the db with no need for upgrading the
/data directory. On most debian based boxes it's as simple as:

sudo apt-get update
or
sudo apt-get upgrade


Sent from BlackBerry® on Airtel

[s***@gmail.com]

Many thanks Scott..


------Original Message------
From: Scott Marlowe
To: ***@gmail.com
Cc: Craig Ringer
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
Sent: Nov 28, 2012 10:38 AM

latest is 8.3.21 according to postgresql.org.

Hi Scott,
Thanks for ur reply. BTW I thought 8.3.14 was the latest in the 8.3 series...no?
------Original Message------
From: Scott Marlowe
To: Subhasis Bhattacharya
Cc: Craig Ringer
Subject: Re: [ADMIN] Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
Sent: Nov 28, 2012 4:55 AM
On Sat, Nov 24, 2012 at 7:25 AM, Subhasis Bhattacharya

Post by Subhasis Bhattacharya
Thanks Craig,
We have plans to upgrade to a later version of postgres, but that could take
a while.
Meantime, I wanted to understand whether the autovacuum failure could be
linked to the fact that the stats collector could not update (rename) the
pgstats file?

It's important to understand that Craig is implying you're getting
bitten by a bug in an early version of pg 8.3 that can be fixed by
updating to a later version of pg 8.3. Updating within a minor
version number like that causes minimum down time as it simply copies
in new binary files and restarts the db with no need for upgrading the
sudo apt-get update
or
sudo apt-get upgrade
Sent from BlackBerry® on Airtel