pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum questions
6+ messages / 5 participants
[nested] [flat]

* Vacuum questions
@ 2005-03-15 20:46 Chris Hoover <revoohc@sermonaudio.com>
  2005-03-16 07:27 ` Re: Vacuum questions Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Chris Hoover @ 2005-03-15 20:46 UTC (permalink / raw)
  To: pgsql-admin

Question on vacuuming.

When you do a vacuum <table>, are the "freed" tuples available only the 
table, or to the entire db, or to the entire cluster?

The reason I'm asking is that we are getting ready to preform a major 
upgrade to our application that involves adding some new fields to 
almost every table and populating them.  This is causing our test 
conversion db's to double in size (we are assuming that every update is 
causing a delete and re-insert behind the scenes due to the populating 
of the new columns).  Anyway, we are working on trying to find the 
fastest way to recover the space.

We have also had one test occurrence where after the upgrade, a vacuum 
full would not recover the space.  However, when we bounced the 
postmaster, and then performed a vacuum full, the space was recovered.  
Any ideas on what might cause this?  This happened on a newly restored 
db that was converted and then vacuumed full.  There would have been no 
connections to the db after the conversion.

PG 7.3.4
RH 2.1

Thanks,

Chris




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

* Re: Vacuum questions
  2005-03-15 20:46 Vacuum questions Chris Hoover <revoohc@sermonaudio.com>
@ 2005-03-16 07:27 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-16 17:55   ` Re: Vacuum questions Chris Hoover <revoohc@sermonaudio.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2005-03-16 07:27 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-admin

"Chris Hoover" <revoohc@sermonaudio.com> writes:
> We have also had one test occurrence where after the upgrade, a vacuum 
> full would not recover the space.  However, when we bounced the 
> postmaster, and then performed a vacuum full, the space was recovered.  
> Any ideas on what might cause this?

Most likely, you had an open transaction lurking that was old enough
that it could still "see" the deleted data, and so VACUUM couldn't
safely reclaim the data.

VACUUM's decisions about this are cluster-wide; so even though you'd
recently created the database in question, a long-running transaction
in another database under the same postmaster could still cause the
problem.

			regards, tom lane



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

* Re: Vacuum questions
  2005-03-15 20:46 Vacuum questions Chris Hoover <revoohc@sermonaudio.com>
  2005-03-16 07:27 ` Re: Vacuum questions Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-16 17:55   ` Chris Hoover <revoohc@sermonaudio.com>
  2005-03-16 18:04     ` Re: Vacuum questions Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Chris Hoover @ 2005-03-16 17:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Tom,

Thanks so much for the possible reason the vacuum fulls are not running.

But, how about the first question.  When you run a vacuum <table>, who 
has access to the freed tuples?  (only the table, the db, or the entire 
cluster)???

Thanks,

Chris




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

* Re: Vacuum questions
  2005-03-15 20:46 Vacuum questions Chris Hoover <revoohc@sermonaudio.com>
  2005-03-16 07:27 ` Re: Vacuum questions Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-16 17:55   ` Re: Vacuum questions Chris Hoover <revoohc@sermonaudio.com>
@ 2005-03-16 18:04     ` Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Scott Marlowe @ 2005-03-16 18:04 UTC (permalink / raw)
  To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

On Wed, 2005-03-16 at 11:55, Chris Hoover wrote:
> Tom,
> 
> Thanks so much for the possible reason the vacuum fulls are not running.
> 
> But, how about the first question.  When you run a vacuum <table>, who 
> has access to the freed tuples?  (only the table, the db, or the entire 
> cluster)???

the table



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

* vacuum  questions
@ 2007-08-20 18:33 Juliann Meyer <Julie.Meyer@noaa.gov>
  2007-08-22 16:53 ` Re: vacuum  questions Robert Treat <robert@omniti.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Juliann Meyer @ 2007-08-20 18:33 UTC (permalink / raw)
  To: pgsql-admin

Currently running v7.4.8 postgres on a RHE Linux 4.0  box.  Will be 
upgraded to postgres 8.2.x sometime this fall.

Several of the tables in the database that resides on this system have 
lots of inserts and updates.  Very little is deleted at this time, as 
the database is intended for archiving.

Currently a cron job runs a vacuum analyze  3 times a day.  Is there any 
need to run a vacuum full every so often?
If so, how often should a vacuum full be done?  And do all other 
processes that "hit" the database need to shut off when a vacuum full is 
done?

Also another office that has a similar setup has switched to running 
pg_autovacuum instead of the vacuum analyze 3 times/day, are there any 
advantages and/or disadvantages to this approach?

Thanks in advance for any information and guidance you can give,
Julie

Attachments:

julie.meyer.vcf (text/x-vcard, 247B) download begin:vcard fn:A. Juliann Meyer org:National Weather Service;Missouri Basin River Forecast Center adr;dom:;;;Pleasant Hill;MO;64080 email;internet:julie.meyer@noaa.gov title:Sr. Hydrologist - Data Systems x-mozilla-html:FALSE version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: vacuum  questions
  2007-08-20 18:33 vacuum  questions Juliann Meyer <Julie.Meyer@noaa.gov>
@ 2007-08-22 16:53 ` Robert Treat <robert@omniti.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Robert Treat @ 2007-08-22 16:53 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Juliann Meyer <Julie.Meyer@noaa.gov>

On Monday 20 August 2007 14:33, Juliann Meyer wrote:
> Currently running v7.4.8 postgres on a RHE Linux 4.0  box.  Will be
> upgraded to postgres 8.2.x sometime this fall.
>
> Several of the tables in the database that resides on this system have
> lots of inserts and updates.  Very little is deleted at this time, as
> the database is intended for archiving.
>

Be aware that an update is equivilant to a delete+insert, so even if you 
aren't "deleting", your updates will still cause accumlation of dead rows in 
your table.

> Currently a cron job runs a vacuum analyze  3 times a day.  Is there any
> need to run a vacuum full every so often?

There could be.  If the number of updates your doing is greater than your 
free_space_map settings, then you wont be recovering all of the space you 
would want to be.  Granted, a better solution is to increase free_space_map 
settings or vacuum analyze frequency, but you might still need an initial 
vacuum full to recover space.  I *think* on 7.4 the output of vacuum analyze 
will tell you what your free space map settings need to be, so examine it's 
output the next few runs. 

> If so, how often should a vacuum full be done?  And do all other
> processes that "hit" the database need to shut off when a vacuum full is
> done?

Again, ideally you dont ever want to run vacuum full, so I'd look into getting 
more regular vacuum runs if needed, rather than doing regular vacuum fulls. 
Other things don't neccessarily need to be shut off, but vacuum full will 
lock tables exclusivly, so it will block other processes while it runs. How 
bad that is depends on your situation. Also, be aware that vacuum full may 
take considerably longer than a regular vacuum. 

>
> Also another office that has a similar setup has switched to running
> pg_autovacuum instead of the vacuum analyze 3 times/day, are there any
> advantages and/or disadvantages to this approach?
>

The main advantage is that you will get more accurate vacuum/analyze 
information from your system while reducing overall load, as tables will only 
be vacuum as needed. (Think of it as a sniper approach, rather than the 
bazooka method your using now)  The downside is that 7.4's pg_autovacuum is a 
little more complex than what you'll find in more recent versions of 
postgres, but if you're having trouble getting a handle on a good vacuum 
scheme, it might be worth it.  

-- 
Robert Treat
Database Architect
http://www.omniti.com/



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


end of thread, other threads:[~2007-08-22 16:53 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 20:46 Vacuum questions Chris Hoover <revoohc@sermonaudio.com>
2005-03-16 07:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-16 17:55   ` Chris Hoover <revoohc@sermonaudio.com>
2005-03-16 18:04     ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-08-20 18:33 vacuum  questions Juliann Meyer <Julie.Meyer@noaa.gov>
2007-08-22 16:53 ` Re: vacuum  questions Robert Treat <robert@omniti.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