pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
7+ messages / 4 participants
[nested] [flat]
* Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
@ 2012-11-23 11:32 Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
2012-11-23 13:05 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Craig Ringer <craig@2ndQuadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Subhasis Bhattacharya @ 2012-11-23 11:32 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
2012-11-23 11:32 Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
@ 2012-11-23 13:05 ` Craig Ringer <craig@2ndQuadrant.com>
2012-11-24 14:25 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Craig Ringer @ 2012-11-23 13:05 UTC (permalink / raw)
To: Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>; +Cc: pgsql-admin
On 11/23/2012 07:32 PM, Subhasis Bhattacharya wrote:
> Hi All,
>
> Recently a postgres database embedded within our product faced a
> series of hiccups at a customer site, as follows:
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
2012-11-23 11:32 Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
2012-11-23 13:05 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Craig Ringer <craig@2ndQuadrant.com>
@ 2012-11-24 14:25 ` Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
2012-11-27 23:25 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Subhasis Bhattacharya @ 2012-11-24 14:25 UTC (permalink / raw)
To: Craig Ringer <craig@2ndquadrant.com>; +Cc: pgsql-admin
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
On Fri, Nov 23, 2012 at 6:35 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 11/23/2012 07:32 PM, Subhasis Bhattacharya wrote:
> > Hi All,
> >
> > Recently a postgres database embedded within our product faced a
> > series of hiccups at a customer site, as follows:
> 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
2012-11-23 11:32 Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
2012-11-23 13:05 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Craig Ringer <craig@2ndQuadrant.com>
2012-11-24 14:25 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
@ 2012-11-27 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2012-11-27 23:25 UTC (permalink / raw)
To: Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>; +Cc: Craig Ringer <craig@2ndquadrant.com>; pgsql-admin
On Sat, Nov 24, 2012 at 7:25 AM, Subhasis Bhattacharya
<subhasis.bhattacharya@gmail.com> wrote:
> 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 via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
@ 2012-11-28 05:05 subhasis.bhattacharya@gmail.com
2012-11-28 05:08 ` Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: subhasis.bhattacharya@gmail.com @ 2012-11-28 05:05 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Craig Ringer <craig@2ndquadrant.com>; pgsql-admin
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-admin@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
<subhasis.bhattacharya@gmail.com> wrote:
> 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
2012-11-28 05:05 Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) subhasis.bhattacharya@gmail.com
@ 2012-11-28 05:08 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2012-11-28 05:08 UTC (permalink / raw)
To: subhasis.bhattacharya@gmail.com; +Cc: Craig Ringer <craig@2ndquadrant.com>; pgsql-admin
latest is 8.3.21 according to postgresql.org.
On Tue, Nov 27, 2012 at 10:05 PM, <subhasis.bhattacharya@gmail.com> wrote:
> 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-admin@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
> <subhasis.bhattacharya@gmail.com> wrote:
>> 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
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
@ 2012-11-28 05:13 subhasis.bhattacharya@gmail.com
0 siblings, 0 replies; 7+ messages in thread
From: subhasis.bhattacharya@gmail.com @ 2012-11-28 05:13 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Craig Ringer <craig@2ndquadrant.com>; pgsql-admin
Many thanks Scott..
------Original Message------
From: Scott Marlowe
To: subhasis.bhattacharya@gmail.com
Cc: Craig Ringer
Cc: pgsql-admin@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.
On Tue, Nov 27, 2012 at 10:05 PM, <subhasis.bhattacharya@gmail.com> wrote:
> 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-admin@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
> <subhasis.bhattacharya@gmail.com> wrote:
>> 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
--
To understand recursion, one must first understand recursion.
Sent from BlackBerry® on Airtel
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-11-28 05:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-23 11:32 Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
2012-11-23 13:05 ` Craig Ringer <craig@2ndQuadrant.com>
2012-11-24 14:25 ` Subhasis Bhattacharya <subhasis.bhattacharya@gmail.com>
2012-11-27 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-11-28 05:05 Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) subhasis.bhattacharya@gmail.com
2012-11-28 05:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-11-28 05:13 Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?) subhasis.bhattacharya@gmail.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox