Обсуждение: Vacuum full

Hi Andrew,

How much activity do you have on these archive type of tables?  If you
run SELECT queries on them a lot, and you're often updating or deleting
rows (am specifically not meaning INSERTing new rows) then it might be
worthwhile VACUUMing them.

Otherwise, it looks like you might not gain much from doing so.  :)

As a curiosity question, when you VACUUM, do you use the ANALYZE option?

As a second curiosity question, when you mention PostgreSQL 7.2, do you
mean 7.2 literally or 7.2.3?  7.2.3 is the recommended production
version due to bugs having been found in 7.2.2 and 7.2.1.

Hope that helps.

:-)

Regards and best wishes,

Justin Clift


Andrew Bartley wrote:
>
> We are using postgres 7.2



Can anyone tell me what the ramifications are if I do not vacuum full
the whole DB?



Say if I was to selectively vacuum all of the PG tables, and only some
of the Application tables.



We have a number of tables that are effectively archive type tables.
They are large, but do not change. If

I was to vacuum these tables on a regular basis, it would add a
significant portion of time to the housekeeping process.



Thanks

Andrew

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Thanks Justin,



These tables will be used for reporting only, No update or insert activity
at all.



Q1.

Once a day

All application table indexes are dropped.

A vacuum full analyse is executed.

Then all app table indexes are created.



Q2.

We are running.  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
2.95.4

I know we are several versions behind.  Is there a known bug list for this
version?



I am also aware there is an upper limit on the number of transactions
postgres can do before the DB needs vacuuming.  Somthing to do with tran ids
wrapping or something.  Do you know anything about this?



Thanks



Andrew


----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 06, 2002 1:02 PM
Subject: Re: [GENERAL] Vacuum full


> Hi Andrew,
>
> How much activity do you have on these archive type of tables?  If you
> run SELECT queries on them a lot, and you're often updating or deleting
> rows (am specifically not meaning INSERTing new rows) then it might be
> worthwhile VACUUMing them.
>
> Otherwise, it looks like you might not gain much from doing so.  :)
>
> As a curiosity question, when you VACUUM, do you use the ANALYZE option?
>
> As a second curiosity question, when you mention PostgreSQL 7.2, do you
> mean 7.2 literally or 7.2.3?  7.2.3 is the recommended production
> version due to bugs having been found in 7.2.2 and 7.2.1.
>
> Hope that helps.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Andrew Bartley wrote:
> >
> > We are using postgres 7.2
>
>
>
> Can anyone tell me what the ramifications are if I do not vacuum full
> the whole DB?
>
>
>
> Say if I was to selectively vacuum all of the PG tables, and only some
> of the Application tables.
>
>
>
> We have a number of tables that are effectively archive type tables.
> They are large, but do not change. If
>
> I was to vacuum these tables on a regular basis, it would add a
> significant portion of time to the housekeeping process.
>
>
>
> Thanks
>
> Andrew
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>
>

Andrew Bartley wrote:
>
> Thanks Justin,
>
> These tables will be used for reporting only, No update or insert activity
> at all.

As a thought, do you mean "no update or *delete* activity at all"?  No
insert activity would generally mean you have empty tables (unless using
COPY to gather data).

If you're only adding data, then selecting against that, and not
updating or deleting records, you should be fine to skip the VACUUM of
those tables.  Unsure as to whether you'd want to skip a seperate
ANALYZE of the tables though.


> Q1.
>
> Once a day
>
> All application table indexes are dropped.
>
> A vacuum full analyse is executed.
>
> Then all app table indexes are created.

Well, that should definitely keep things in check and maintainable.  :)


> Q2.
>
> We are running.  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
> 2.95.4
>
> I know we are several versions behind.  Is there a known bug list for this
> version?

Hmmm.  Don't remember.  There are overview details of the changes in the
7.2.2 and 7.2.3 releases though on the PostgreSQL News page:

http://www.ca.postgresql.org/news.html


> I am also aware there is an upper limit on the number of transactions
> postgres can do before the DB needs vacuuming.  Somthing to do with tran ids
> wrapping or something.  Do you know anything about this?

Not my area.  One of the more-hard-core developers will be able to tell
you about this though.  From memory though it happens around the 2 or 4
billionth transaction mark, and something may have been done about it
for the upcoming 7.3 release (we're in beta testing of this at present).

Hope that helps.

:-)

Regards and best wishes,

Justin Clift

> Thanks
>
> Andrew
>
> ----- Original Message -----
> From: "Justin Clift" <justin@postgresql.org>
> To: "Andrew Bartley" <abartley@evolvosystems.com>
> Cc: <pgsql-general@postgresql.org>
> Sent: Wednesday, November 06, 2002 1:02 PM
> Subject: Re: [GENERAL] Vacuum full
>
> > Hi Andrew,
> >
> > How much activity do you have on these archive type of tables?  If you
> > run SELECT queries on them a lot, and you're often updating or deleting
> > rows (am specifically not meaning INSERTing new rows) then it might be
> > worthwhile VACUUMing them.
> >
> > Otherwise, it looks like you might not gain much from doing so.  :)
> >
> > As a curiosity question, when you VACUUM, do you use the ANALYZE option?
> >
> > As a second curiosity question, when you mention PostgreSQL 7.2, do you
> > mean 7.2 literally or 7.2.3?  7.2.3 is the recommended production
> > version due to bugs having been found in 7.2.2 and 7.2.1.
> >
> > Hope that helps.
> >
> > :-)
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> >
> > Andrew Bartley wrote:
> > >
> > > We are using postgres 7.2
> >
> >
> >
> > Can anyone tell me what the ramifications are if I do not vacuum full
> > the whole DB?
> >
> >
> >
> > Say if I was to selectively vacuum all of the PG tables, and only some
> > of the Application tables.
> >
> >
> >
> > We have a number of tables that are effectively archive type tables.
> > They are large, but do not change. If
> >
> > I was to vacuum these tables on a regular basis, it would add a
> > significant portion of time to the housekeeping process.
> >
> >
> >
> > Thanks
> >
> > Andrew
> >
> > --
> > "My grandfather once told me that there are two kinds of people: those
> > who work and those who take the credit. He told me to try to be in the
> > first group; there was less competition there."
> >    - Indira Gandhi
> >
> >

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

I'd say VACUUM them as often as you can, because it updates the planner's
optimzation code or decision making process.

VACUUM also give back your disk space on both deleted and updated rows...

Also, 7.2.3 now allows VACUUMing to take place along with other normal DB
transactions, ie in parallel, so, you cron can outsource your VACUUM-ing to
a third party cron-job, give it a key to the building (ie DB) and go home...

Cheers....

On Tuesday 05 November 2002 18:50, Justin Clift wrote:
> Andrew Bartley wrote:
> > Thanks Justin,
> >
> > These tables will be used for reporting only, No update or insert
> > activity at all.
>
> As a thought, do you mean "no update or *delete* activity at all"?  No
> insert activity would generally mean you have empty tables (unless using
> COPY to gather data).
>
> If you're only adding data, then selecting against that, and not
> updating or deleting records, you should be fine to skip the VACUUM of
> those tables.  Unsure as to whether you'd want to skip a seperate
> ANALYZE of the tables though.
>
> > Q1.
> >
> > Once a day
> >
> > All application table indexes are dropped.
> >
> > A vacuum full analyse is executed.
> >
> > Then all app table indexes are created.
>
> Well, that should definitely keep things in check and maintainable.  :)
>
> > Q2.
> >
> > We are running.  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
> > 2.95.4
> >
> > I know we are several versions behind.  Is there a known bug list for
> > this version?
>
> Hmmm.  Don't remember.  There are overview details of the changes in the
> 7.2.2 and 7.2.3 releases though on the PostgreSQL News page:
>
> http://www.ca.postgresql.org/news.html
>
> > I am also aware there is an upper limit on the number of transactions
> > postgres can do before the DB needs vacuuming.  Somthing to do with tran
> > ids wrapping or something.  Do you know anything about this?
>
> Not my area.  One of the more-hard-core developers will be able to tell
> you about this though.  From memory though it happens around the 2 or 4
> billionth transaction mark, and something may have been done about it
> for the upcoming 7.3 release (we're in beta testing of this at present).
>
> Hope that helps.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
> > Thanks
> >
> > Andrew
> >
> > ----- Original Message -----
> > From: "Justin Clift" <justin@postgresql.org>
> > To: "Andrew Bartley" <abartley@evolvosystems.com>
> > Cc: <pgsql-general@postgresql.org>
> > Sent: Wednesday, November 06, 2002 1:02 PM
> > Subject: Re: [GENERAL] Vacuum full
> >
> > > Hi Andrew,
> > >
> > > How much activity do you have on these archive type of tables?  If you
> > > run SELECT queries on them a lot, and you're often updating or deleting
> > > rows (am specifically not meaning INSERTing new rows) then it might be
> > > worthwhile VACUUMing them.
> > >
> > > Otherwise, it looks like you might not gain much from doing so.  :)
> > >
> > > As a curiosity question, when you VACUUM, do you use the ANALYZE
> > > option?
> > >
> > > As a second curiosity question, when you mention PostgreSQL 7.2, do you
> > > mean 7.2 literally or 7.2.3?  7.2.3 is the recommended production
> > > version due to bugs having been found in 7.2.2 and 7.2.1.
> > >
> > > Hope that helps.
> > >
> > > :-)
> > >
> > > Regards and best wishes,
> > >
> > > Justin Clift
> > >
> > > Andrew Bartley wrote:
> > > > We are using postgres 7.2
> > >
> > > Can anyone tell me what the ramifications are if I do not vacuum full
> > > the whole DB?
> > >
> > >
> > >
> > > Say if I was to selectively vacuum all of the PG tables, and only some
> > > of the Application tables.
> > >
> > >
> > >
> > > We have a number of tables that are effectively archive type tables.
> > > They are large, but do not change. If
> > >
> > > I was to vacuum these tables on a regular basis, it would add a
> > > significant portion of time to the housekeeping process.
> > >
> > >
> > >
> > > Thanks
> > >
> > > Andrew
> > >
> > > --
> > > "My grandfather once told me that there are two kinds of people: those
> > > who work and those who take the credit. He told me to try to be in the
> > > first group; there was less competition there."
> > >    - Indira Gandhi

Thanks Justin,



I suppose the reason for the question is related to the limit on the number
trans before there is a problem.



Regarding the static type app tables I keep referring to.  These tables are
period based. i.e. At the end of the period these tables are loaded and
vacuumed, then used for reporting purposes only.  In the next/new period a
new table will be created added to/updated, then closed at the end of the
period.  The tables are dynamically accessed using "execute" based on the
period selected in the report criteria requested by the user from the GUI.



The tables in question will never change from the point when the period is
closed.



Thanks for your help and info.



Andrew


----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 06, 2002 1:50 PM
Subject: Re: [GENERAL] Vacuum full


> Andrew Bartley wrote:
> >
> > Thanks Justin,
> >
> > These tables will be used for reporting only, No update or insert
activity
> > at all.
>
> As a thought, do you mean "no update or *delete* activity at all"?  No
> insert activity would generally mean you have empty tables (unless using
> COPY to gather data).
>
> If you're only adding data, then selecting against that, and not
> updating or deleting records, you should be fine to skip the VACUUM of
> those tables.  Unsure as to whether you'd want to skip a seperate
> ANALYZE of the tables though.
>
>
> > Q1.
> >
> > Once a day
> >
> > All application table indexes are dropped.
> >
> > A vacuum full analyse is executed.
> >
> > Then all app table indexes are created.
>
> Well, that should definitely keep things in check and maintainable.  :)
>
>
> > Q2.
> >
> > We are running.  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
> > 2.95.4
> >
> > I know we are several versions behind.  Is there a known bug list for
this
> > version?
>
> Hmmm.  Don't remember.  There are overview details of the changes in the
> 7.2.2 and 7.2.3 releases though on the PostgreSQL News page:
>
> http://www.ca.postgresql.org/news.html
>
>
> > I am also aware there is an upper limit on the number of transactions
> > postgres can do before the DB needs vacuuming.  Somthing to do with tran
ids
> > wrapping or something.  Do you know anything about this?
>
> Not my area.  One of the more-hard-core developers will be able to tell
> you about this though.  From memory though it happens around the 2 or 4
> billionth transaction mark, and something may have been done about it
> for the upcoming 7.3 release (we're in beta testing of this at present).
>
> Hope that helps.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
> > Thanks
> >
> > Andrew
> >
> > ----- Original Message -----
> > From: "Justin Clift" <justin@postgresql.org>
> > To: "Andrew Bartley" <abartley@evolvosystems.com>
> > Cc: <pgsql-general@postgresql.org>
> > Sent: Wednesday, November 06, 2002 1:02 PM
> > Subject: Re: [GENERAL] Vacuum full
> >
> > > Hi Andrew,
> > >
> > > How much activity do you have on these archive type of tables?  If you
> > > run SELECT queries on them a lot, and you're often updating or
deleting
> > > rows (am specifically not meaning INSERTing new rows) then it might be
> > > worthwhile VACUUMing them.
> > >
> > > Otherwise, it looks like you might not gain much from doing so.  :)
> > >
> > > As a curiosity question, when you VACUUM, do you use the ANALYZE
option?
> > >
> > > As a second curiosity question, when you mention PostgreSQL 7.2, do
you
> > > mean 7.2 literally or 7.2.3?  7.2.3 is the recommended production
> > > version due to bugs having been found in 7.2.2 and 7.2.1.
> > >
> > > Hope that helps.
> > >
> > > :-)
> > >
> > > Regards and best wishes,
> > >
> > > Justin Clift
> > >
> > >
> > > Andrew Bartley wrote:
> > > >
> > > > We are using postgres 7.2
> > >
> > >
> > >
> > > Can anyone tell me what the ramifications are if I do not vacuum full
> > > the whole DB?
> > >
> > >
> > >
> > > Say if I was to selectively vacuum all of the PG tables, and only some
> > > of the Application tables.
> > >
> > >
> > >
> > > We have a number of tables that are effectively archive type tables.
> > > They are large, but do not change. If
> > >
> > > I was to vacuum these tables on a regular basis, it would add a
> > > significant portion of time to the housekeeping process.
> > >
> > >
> > >
> > > Thanks
> > >
> > > Andrew
> > >
> > > --
> > > "My grandfather once told me that there are two kinds of people: those
> > > who work and those who take the credit. He told me to try to be in the
> > > first group; there was less competition there."
> > >    - Indira Gandhi
> > >
> > >
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>
>

Andrew Bartley wrote:
>
> Thanks Justin,
>
> I suppose the reason for the question is related to the limit on the number
> trans before there is a problem.

No stress.  One of the developers will be able to answer you in regards
to this.  :)  If no-one pipes up and says anything, then it's worth
asking on the PostgreSQL Hackers mailing list
(pgsql-hackers@postgresql.org).

> Regarding the static type app tables I keep referring to.  These tables are
> period based. i.e. At the end of the period these tables are loaded and
> vacuumed, then used for reporting purposes only.  In the next/new period a
> new table will be created added to/updated, then closed at the end of the
> period.  The tables are dynamically accessed using "execute" based on the
> period selected in the report criteria requested by the user from the GUI.
>
> The tables in question will never change from the point when the period is
> closed.

Ok, after they're loaded it's worth doing an ANALYZE to ensure the
planner statistics and similar are updated and made accurate.  After
that though, as they're totally static you'd be wasting your time
running VACUUM or any further ANALYZE's on them.


> Thanks for your help and info.

That's cool.  :)

Regards and best wishes,

Justin Clift

> Andrew

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Thanks



I think that is what I wanted to hear.



Regarding the version we are running.  The last upgrade I did from 7.1 to
7.2.1 took several months.  The main problem was not the change in the
semantics of the syntax, but the performance blow out we experienced.  Once
I had re-written the offending code, several weeks of regression testing had
to be done.  I will look at the link you sent me regarding the 7.2/3 news,
to see if there are any bugs that could be a potential problem for us.



Thanks again for your time



Andrew Bartley


----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 06, 2002 2:36 PM
Subject: Re: [GENERAL] Vacuum full


> Andrew Bartley wrote:
> >
> > Thanks Justin,
> >
> > I suppose the reason for the question is related to the limit on the
number
> > trans before there is a problem.
>
> No stress.  One of the developers will be able to answer you in regards
> to this.  :)  If no-one pipes up and says anything, then it's worth
> asking on the PostgreSQL Hackers mailing list
> (pgsql-hackers@postgresql.org).
>
> > Regarding the static type app tables I keep referring to.  These tables
are
> > period based. i.e. At the end of the period these tables are loaded and
> > vacuumed, then used for reporting purposes only.  In the next/new period
a
> > new table will be created added to/updated, then closed at the end of
the
> > period.  The tables are dynamically accessed using "execute" based on
the
> > period selected in the report criteria requested by the user from the
GUI.
> >
> > The tables in question will never change from the point when the period
is
> > closed.
>
> Ok, after they're loaded it's worth doing an ANALYZE to ensure the
> planner statistics and similar are updated and made accurate.  After
> that though, as they're totally static you'd be wasting your time
> running VACUUM or any further ANALYZE's on them.
>
>
> > Thanks for your help and info.
>
> That's cool.  :)
>
> Regards and best wishes,
>
> Justin Clift
>
> > Andrew
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>
>

On Wed, Nov 06, 2002 at 14:14:23 +1100,
  Andrew Bartley <abartley@evolvosystems.com> wrote:
>
> I suppose the reason for the question is related to the limit on the number
> trans before there is a problem.

This is covered in the documentation. It is recommended that you vacuum
at least every billion (10^9) transactions. Problems occur after about
2 billion transactions. The documentation covers this in more detail.

On Wed, Nov 06, 2002 at 01:50:00PM +1100, Justin Clift wrote:
> Andrew Bartley wrote:
>
> > I am also aware there is an upper limit on the number of transactions
> > postgres can do before the DB needs vacuuming.  Somthing to do with tran ids
> > wrapping or something.  Do you know anything about this?
>
> Not my area.  One of the more-hard-core developers will be able to tell
> you about this though.  From memory though it happens around the 2 or 4
> billionth transaction mark, and something may have been done about it
> for the upcoming 7.3 release (we're in beta testing of this at present).

There has alwaus been a problem with transaction id wrap-around.
There is a maximum number of transactions the system can perform
before the transaction identifiers (which are required for MVCC to
work) wrap (the ids are int4, so the number is strictly speaking 4
billion).

Prior to 7.2.x, the only way to solve the problem was a complete
initdb-and-restore.

As of 7.2, there is a better solution; but the price of the solution
is that _every table_ in the database must be vacuumed at least once
every billion transactions.  See

<http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND>

(that's a long line.  Sorry.)

VACUUM without FULL doesn't block, however.  It will cost you almost
nothing to VACUUM nightly with cron.  Think Martha Stewart: It's a
Good Thing.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Wed, Nov 06, 2002 at 02:52:11PM +1100, Andrew Bartley wrote:

> I had re-written the offending code, several weeks of regression testing had
> to be done.  I will look at the link you sent me regarding the 7.2/3 news,
> to see if there are any bugs that could be a potential problem for us.

There are.  In particular, there are issues with VACUUM that turn out
to be bad news if a non-superuser vacuums.

7.2.3 is a binary replacement for 7.2.1, so you should be able just
to stop the postmaster and re-start with 7.2.1.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110