Обсуждение: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
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
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
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:8.3?
> Hi All,
>
> Recently a postgres database embedded within our product faced a
> series of hiccups at a customer site, as follows:
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
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
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.
Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
От
subhasis.bhattacharya@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-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
Re: Autovaccum failure with Postgres 8.3.14? (max-fsm_pages issue?)
От
subhasis.bhattacharya@gmail.com
Дата:
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