pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
(no subject)
3+ messages / 3 participants
[nested] [flat]

* (no subject)
@ 2006-05-09 20:03 mark.dingee@cox.net
  2006-05-09 21:01 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: mark.dingee@cox.net @ 2006-05-09 20:03 UTC (permalink / raw)
  To: pgsql-admin

Everyone,

I had an odd situation occur this morning with PGSQL 7.4 run on Red Hat Enterprise 4 (update 3) and could really use some wisdom.  

...  Single postmaster running.
...  vacuum full is run every night as part of a cron job
...  At start, data files consume about 28 GB
...  This morning I dropped the database and reloaded from current backup
... New instance consumes about 6 GB

I can only assume that the database was not compacted, but I thought vacuum full performed that function along with tuple maintenance.  Can anyone expound on the problem and suggest a solution other than dropping and reloading the database?

Thanks in advance




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

* Re:
  2006-05-09 20:03 (no subject) mark.dingee@cox.net
@ 2006-05-09 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-05-09 23:51   ` Re: Mark R. Dingee <mark.dingee@cox.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2006-05-09 21:01 UTC (permalink / raw)
  To: mark.dingee@cox.net; +Cc: pgsql-admin

<mark.dingee@cox.net> writes:
> I had an odd situation occur this morning with PGSQL 7.4 run on Red Hat Enterprise 4 (update 3) and could really use some wisdom.  

> ...  Single postmaster running.
> ...  vacuum full is run every night as part of a cron job
> ...  At start, data files consume about 28 GB
> ...  This morning I dropped the database and reloaded from current backup
> ... New instance consumes about 6 GB

> I can only assume that the database was not compacted, but I thought vacuum full performed that function along with tuple maintenance.  Can anyone expound on the problem and suggest a solution other than dropping and reloading the database?

The evidence is mostly gone now, but what I'd suggest is waiting a while
to see if it bloats again, and if so finding out exactly *where* the
bloat is.  Make some notes now about the sizes of your tables and
indexes, and see what's getting larger.

My guess offhand is that the problem is index bloat.  VACUUM FULL not
only doesn't help much with that, it tends to make it worse.  If the
database size is supposed to be fairly stable, you'd probably be better
off with a maintenance regime that doesn't use VACUUM FULL but just
plain VACUUM.  Make sure your FSM settings are high enough.

			regards, tom lane



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

* Re:
  2006-05-09 20:03 (no subject) mark.dingee@cox.net
  2006-05-09 21:01 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-09 23:51   ` Mark R. Dingee <mark.dingee@cox.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Mark R. Dingee @ 2006-05-09 23:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Tom,

Thanks for the advice.  I'll track it over the next couple weeks and see
what comes up.

Mark

On Tue, 2006-05-09 at 17:01 -0400, Tom Lane wrote:
> <mark.dingee@cox.net> writes:
> > I had an odd situation occur this morning with PGSQL 7.4 run on Red Hat Enterprise 4 (update 3) and could really use some wisdom.  
> 
> > ...  Single postmaster running.
> > ...  vacuum full is run every night as part of a cron job
> > ...  At start, data files consume about 28 GB
> > ...  This morning I dropped the database and reloaded from current backup
> > ... New instance consumes about 6 GB
> 
> > I can only assume that the database was not compacted, but I thought vacuum full performed that function along with tuple maintenance.  Can anyone expound on the problem and suggest a solution other than dropping and reloading the database?
> 
> The evidence is mostly gone now, but what I'd suggest is waiting a while
> to see if it bloats again, and if so finding out exactly *where* the
> bloat is.  Make some notes now about the sizes of your tables and
> indexes, and see what's getting larger.
> 
> My guess offhand is that the problem is index bloat.  VACUUM FULL not
> only doesn't help much with that, it tends to make it worse.  If the
> database size is supposed to be fairly stable, you'd probably be better
> off with a maintenance regime that doesn't use VACUUM FULL but just
> plain VACUUM.  Make sure your FSM settings are high enough.
> 
> 			regards, tom lane




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


end of thread, other threads:[~2006-05-09 23:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-09 20:03  mark.dingee@cox.net
2006-05-09 21:01 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
2006-05-09 23:51   ` Re: Mark R. Dingee <mark.dingee@cox.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