pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
vacuum taking longer over time
3+ messages / 2 participants
[nested] [flat]

* vacuum taking longer over time
@ 2005-06-27 06:13 Enzo D'addario <enzo@pienetworks.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Enzo D'addario @ 2005-06-27 06:13 UTC (permalink / raw)
  To: pgsql-admin

To whom it may concern,

I currently have postgres 7.4.2 running on a dedicated server which has
1GB RAM and a Pentium 4 2600 Mhz processor.

After approximately 1 month I have to dump & restore the database
because vacuum time spirals out of control. To give you an idea of what
I experience, when the DB is restored it takes 1 hour to vacuum, however
by the time the restore is 30 days old it can take up to 6 hours.

The DB is approximately 6 GB in size and is vacuumed analyzed nightly
because of a relatively high number of transactions per day. 

The file system is reiserfs and contains no errors.

Another thing that I noticed from the vacuum logs is that the total
pages needed shortly after restore is approximately 17000 and ends up
over 50000 by the time the restore is 30 days old. 

Does this mean the DB is becoming fragmented?

Will upgrading to postgres 8.0 resolve this problem?

Any ideas would be greatly appreciated.

Thanks in advance.

Enzo D'Addario





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* vacuum taking longer over time
@ 2005-06-28 00:42 Enzo D'addario <enzo@pienetworks.com>
  2005-06-28 04:55 ` Re: vacuum taking longer over time Matthew T. O'Connor <matthew@zeut.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Enzo D'addario @ 2005-06-28 00:42 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

I currently have postgres 7.4.2 running on a dedicated server which has
1GB RAM, a Pentium 4 2600 Mhz processor, uses ReiserFS filesystem and is
running Debian stable.

After approximately 1 month I have to dump & restore the database
because vacuum time spirals out of control. To give you an idea of what
I experience, when the DB is restored it takes 1 hour to vacuum, however
by the time the restore is 30 days old it can take up to 6 hours. I
never experienced such issues with earlier versions of postgres.

The DB is approximately 6 GB in size and is vacuumed analyzed nightly
because of a relatively high number of transactions per day. 

Another thing that I noticed from the vacuum logs is that the total
pages needed shortly after restore is approximately 17000 and ends up
over 50000 by the time the restore is 30 days old. 

Does this mean the DB is becoming fragmented?

Will upgrading to postgres 8.0 resolve this problem?

Any ideas would be greatly appreciated.

Thanks in advance.

Enzo D'Addario






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: vacuum taking longer over time
  2005-06-28 00:42 vacuum taking longer over time Enzo D'addario <enzo@pienetworks.com>
@ 2005-06-28 04:55 ` Matthew T. O'Connor <matthew@zeut.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Matthew T. O'Connor @ 2005-06-28 04:55 UTC (permalink / raw)
  To: Enzo D'addario <enzo@pienetworks.com>; +Cc: pgsql-admin

Enzo D'addario wrote:

>Hi All,
>
>I currently have postgres 7.4.2 running on a dedicated server which has
>1GB RAM, a Pentium 4 2600 Mhz processor, uses ReiserFS filesystem and is
>running Debian stable.
>
>After approximately 1 month I have to dump & restore the database
>because vacuum time spirals out of control. To give you an idea of what
>I experience, when the DB is restored it takes 1 hour to vacuum, however
>by the time the restore is 30 days old it can take up to 6 hours. I
>never experienced such issues with earlier versions of postgres.
>
>The DB is approximately 6 GB in size and is vacuumed analyzed nightly
>because of a relatively high number of transactions per day. 
>
>Another thing that I noticed from the vacuum logs is that the total
>pages needed shortly after restore is approximately 17000 and ends up
>over 50000 by the time the restore is 30 days old. 
>
>Does this mean the DB is becoming fragmented?
>  
>

Not fragmented, but bloated.  Sounds like you need to vacuum more often 
and / or increase your FSM settings.

>Will upgrading to postgres 8.0 resolve this problem?
>  
>

No, I think a more agressive vacuum regiment and / or larger FSM 
settings will solve this even 7.4.  Lots of people either setup cron to 
hit their more active tables more often, or use pg_autovacuum.

Good luck.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-06-28 04:55 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-27 06:13 vacuum taking longer over time Enzo D'addario <enzo@pienetworks.com>
2005-06-28 00:42 vacuum taking longer over time Enzo D'addario <enzo@pienetworks.com>
2005-06-28 04:55 ` Matthew T. O'Connor <matthew@zeut.net>

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