Обсуждение: Autovacuuming

Gourish Singbal wrote:
> The Age of the database is 1144759836 . We have enabled autovacuuming .
> The details are:-
> autovacuum = on

Are you sure autovacuum is enabled?  Do a "SHOW autovacuum" just to be
sure.

The test for database-wide vacuum is:

database age > big_constant

where:

big_constant = (MaxTransactionId >> 3) * 3 - 100000) = 1610512733
MaxTransactionId = 0xFFFFFFFF
database age = Max(frozen age, vacuum age)
frozen age = next Transaction id - pg_database.datfrozenxid
vacuum age = next Transaction id - pg_database.datvacuumxid


Your database is still 465752897 transactions away from that apparently.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Mon, Apr 24, 2006 at 11:21:16AM +0530, Gourish Singbal wrote:
> The Age of the database is 1144759836 . We have enabled autovacuuming .
> The details are:-
> autovacuum = on
> autovacuum_naptime = 300
> autovacuum_vacuum_threshold = 10000
> autovacuum_analyze_threshold = 5000
> autovacuum_vacuum_scale_factor = 0.3
> autovacuum_analyze_scale_factor = 0.3

Those settings are *really* conservative... I normally use 400, 200,
0.2, 0.1 respectively...

> Wanted to know when the auto-vacuum daemon will run an database-wide  vacuum
> call to avoid transaction ID wraparound failures.

IIRC it will do that somewhere between 2B and 4B transactions have
elapsed. The true answer lies somewhere in
src/backend/postmaster/autovacuum.c.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Mon, Apr 24, 2006 at 08:41:55AM -0400, Alvaro Herrera wrote:
> The test for database-wide vacuum is:
>
> database age > big_constant
>
> where:
>
> big_constant = (MaxTransactionId >> 3) * 3 - 100000) = 1610512733

Ok, I'll bite... any idea how (MaxTransactionId >> 3) * 3 was chosen? I
don't see anything about it in vacuum.c...

Also, maybe that should be defined in one place rather than duplicating
it in autovacuum.c?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:
> On Mon, Apr 24, 2006 at 08:41:55AM -0400, Alvaro Herrera wrote:
> > The test for database-wide vacuum is:
> >
> > database age > big_constant
> >
> > where:
> >
> > big_constant = (MaxTransactionId >> 3) * 3 - 100000) = 1610512733
>
> Ok, I'll bite... any idea how (MaxTransactionId >> 3) * 3 was chosen? I
> don't see anything about it in vacuum.c...

I don't remember exactly why was that constant chosen.  IIRC Tom pulled
it out of thin air.  You can see in vac_truncate_clog (vacuum.c) a
similar constant is used to emit messages about impending wraparound;
it's ((MaxTransactionId >> 3) * 3).  The one in autovacuum.c allows for
more slack.  (Which is kinda right because we want autovac to take care
of it _before_ the user sees any warning.)

> Also, maybe that should be defined in one place rather than duplicating
> it in autovacuum.c?

Hmm ... do you care enough to send a patch?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Jim C. Nasby wrote:
>> Ok, I'll bite... any idea how (MaxTransactionId >> 3) * 3 was chosen? I
>> don't see anything about it in vacuum.c...

> I don't remember exactly why was that constant chosen.  IIRC Tom pulled
> it out of thin air.

Pretty much.  One of the main constraints at the time was that we wanted
it to be warning for a good long time before actual wraparound happened,
because the consequences would be so catastrophic.  Now that there's
some failsafe code in place, it might be reasonable to narrow the margin
so it doesn't start bleating quite so soon.  We'd still be pulling
specific numbers out of the air though ...

            regards, tom lane

On Mon, Apr 24, 2006 at 03:06:03PM -0400, Alvaro Herrera wrote:
> > Also, maybe that should be defined in one place rather than duplicating
> > it in autovacuum.c?
>
> Hmm ... do you care enough to send a patch?

I'm always up for a challenge... :)

Where should I go about defining it? Better yet, is there some other
variable you can give me as an example?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:
> On Mon, Apr 24, 2006 at 03:06:03PM -0400, Alvaro Herrera wrote:
> > > Also, maybe that should be defined in one place rather than duplicating
> > > it in autovacuum.c?
> >
> > Hmm ... do you care enough to send a patch?
>
> I'm always up for a challenge... :)
>
> Where should I go about defining it?

vacuum.h probably.

> Better yet, is there some other
> variable you can give me as an example?

Huh, INT_ALIGN_MASK?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Gourish Singbal" <gourish@gmail.com> writes:
> Do we need to Disable the Auto-vacuum deamon when we manually run the
> database-wide vacuum command ?.

No.

            regards, tom lane