Thread: Vacuum Vs Vacuum Full

Hi,

I've been trying to get to the bottom of the differences between a vacuum and a vacuum full, it seems to me that the difference is that a vacuum full also recovers disk space(and locks things making it less than useful on production servers).  But I believe that both will fix the transaction ID(example message below).

"WARNING: database "mydb" must be vacuumed within 177009986 transactions

HINT:  To avoid a database shutdown, execute a full-database VACUUM in "mydb"."

Which is reason I ask the question, is full vacuum backup useful for anything other than reclaiming disk space.

On a side note, we doubled our page slots, but they ran out much faster(of course) than we thought, is there a good sql statement that can tell you what your current transaction ID is?

Thanks in advance.

Cheers,
Rob

---

Find out: SEEK Salary Centre Are you paid what you're worth?

On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:
> Hi,
>
> I've been trying to get to the bottom of the differences between a vacuum
> and a vacuum full, it seems to me that the difference is that a vacuum full
> also recovers disk space(and locks things making it less than useful on
> production servers).  But I believe that both will fix the transaction
> ID(example message below).
>
> "WARNING:  database "mydb" must be vacuumed within 177009986 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "mydb"."Which is reason I ask the question, is full vacuum backup useful
> for anything other than reclaiming disk space.
>
> On a side note, we doubled our page slots, but they ran out much faster(of
> course) than we thought, is there a good sql statement that can tell you
> what your current transaction ID is?
>
> Thanks in advance.
>
> Cheers,
> Rob
>

Actually its not asking for a VACUUM FULL but a VACUUM of the full database,
instead of selected tables.

See below for complete details
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND




--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver wrote:
> On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:
>> "WARNING:  database "mydb" must be vacuumed within 177009986 transactions
>> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
>> "mydb"."Which is reason I ask the question, is full vacuum backup useful
>> for anything other than reclaiming disk space.
>
> Actually its not asking for a VACUUM FULL but a VACUUM of the full database,
> instead of selected tables.
>
> See below for complete details
> http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND


BTW, what version of PostgreSQL is this?  Database-wide vacuum is no
longer required for XID wraparound issues.  I think this was an 8.3
change but might have happened in 8.2, I don't remember.

Matt

"Matthew T. O'Connor" <matthew@zeut.net> writes:
>> On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:
>>> "WARNING:  database "mydb" must be vacuumed within 177009986 transactions
>>> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
>>> "mydb".

> BTW, what version of PostgreSQL is this?  Database-wide vacuum is no
> longer required for XID wraparound issues.  I think this was an 8.3
> change but might have happened in 8.2, I don't remember.

8.2.  But you could still get that message, even in CVS HEAD, if
autovacuum was failing to complete for some reason (and had been
failing for quite a long time).

            regards, tom lane

Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
>> BTW, what version of PostgreSQL is this?  Database-wide vacuum is no
>> longer required for XID wraparound issues.  I think this was an 8.3
>> change but might have happened in 8.2, I don't remember.
>
> 8.2.  But you could still get that message, even in CVS HEAD, if
> autovacuum was failing to complete for some reason (and had been
> failing for quite a long time).

Should that message to updated since a database-wide vacuum is no longer
required, or are you saying that the message is still relevant is some
corner cases?

"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Tom Lane wrote:
>> 8.2.  But you could still get that message, even in CVS HEAD, if
>> autovacuum was failing to complete for some reason (and had been
>> failing for quite a long time).

> Should that message to updated since a database-wide vacuum is no longer
> required, or are you saying that the message is still relevant is some
> corner cases?

I think the message is okay as-is, or at least that the code doesn't
have the information available to do better --- it knows which database
is the most problematic, but not which table(s) within that DB most need
vacuuming.  So the easiest manual fix is still a DB-wide vacuum.
Besides which, if you've got one problem table then you've probably got
more than one.

The odds of anyone seeing this message in the field in 8.2 or later seem
pretty remote anyway, so I'm not feeling like we should expend
tremendous effort to make it better.

            regards, tom lane

Its 8.1 and I'm doing a Vacuum using the vacuumdb program.

Thanks Matt, might be time for an upgrade.

> Date: Tue, 5 Aug 2008 11:21:44 -0400
> From: matthew@zeut.net
> To: aklaver@comcast.net
> CC: pgsql-general@postgresql.org; redsmurfau@msn.com
> Subject: Re: [GENERAL] Vacuum Vs Vacuum Full
>
> Adrian Klaver wrote:
> > On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:
> >> "WARNING: database "mydb" must be vacuumed within 177009986 transactions
> >> HINT: To avoid a database shutdown, execute a full-database VACUUM in
> >> "mydb"."Which is reason I ask the question, is full vacuum backup useful
> >> for anything other than reclaiming disk space.
> >
> > Actually its not asking for a VACUUM FULL but a VACUUM of the full database,
> > instead of selected tables.
> >
> > See below for complete details
> > http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>
>
> BTW, what version of PostgreSQL is this? Database-wide vacuum is no
> longer required for XID wraparound issues. I think this was an 8.3
> change but might have happened in 8.2, I don't remember.
>
> Matt

---

Sell your car for just $40 at CarPoint.com.au It's simple!