pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum questions
6+ messages / 5 participants
[nested] [flat]
* Vacuum questions
@ 2005-03-15 20:46 Chris Hoover <revoohc@sermonaudio.com>
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-16 07:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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-16 17:55 Chris Hoover <revoohc@sermonaudio.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-16 18:04 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Chris Hoover <revoohc@sermonaudio.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>
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-22 16:53 Robert Treat <robert@omniti.com>
parent: Juliann Meyer <Julie.Meyer@noaa.gov>
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