pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Autovacuuming
11+ messages / 4 participants
[nested] [flat]

* Autovacuuming
@ 2006-04-24 05:51  Gourish Singbal <gourish@gmail.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Gourish Singbal @ 2006-04-24 05:51 UTC (permalink / raw)
  To: pgsql-admin

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

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

~ gourish singbal

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

* Re: Autovacuuming
@ 2006-04-24 12:41  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Gourish Singbal <gourish@gmail.com>
  1 sibling, 2 replies; 11+ messages in thread

From: Alvaro Herrera @ 2006-04-24 12:41 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin

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.



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

* Re: Autovacuuming
@ 2006-04-24 13:09  Gourish Singbal <gourish@gmail.com>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Gourish Singbal @ 2006-04-24 13:09 UTC (permalink / raw)
  To: pgsql-admin

Show autovacuum displays 'on'.

db1=# select datname, datvacuumxid, datfrozenxid from pg_database;
       datname       | datvacuumxid | datfrozenxid
---------------------+--------------+--------------
 postgres            |          499 |          499
 db1           |     71677296 |   3292902769
 db2 |     99962197 |   3321187670
 db3       |     72274322 |   3293587302
 template1           |          499 |          499
 template0           |          499 |          499
(6 rows)

db1=# SELECT datname, age(datfrozenxid) FROM pg_database;
       datname       |    age
---------------------+------------
 postgres            |  143239306
 db1          | 1145304332
 db2 | 1117019431
 db3       | 1144619799
 template1           |  143239306
 template0           |  143239306
(6 rows)

Since we are currently 465208401 transactions away from the 1610512733.

Can i safely sit back and wait for the autovacuum deamon to do its job
without any issues ?.


On 4/24/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> 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.
>



--
Best,
Gourish Singbal

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

* Re: Autovacuuming
@ 2006-04-24 17:51  Jim C. Nasby <jnasby@pervasive.com>
  parent: Gourish Singbal <gourish@gmail.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Jim C. Nasby @ 2006-04-24 17:51 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin

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



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

* Re: Autovacuuming
@ 2006-04-24 18:00  Jim C. Nasby <jnasby@pervasive.com>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Jim C. Nasby @ 2006-04-24 18:00 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; pgsql-admin

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



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

* Re: Autovacuuming
@ 2006-04-24 19:06  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Alvaro Herrera @ 2006-04-24 19:06 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Gourish Singbal <gourish@gmail.com>; pgsql-admin

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



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

* Re: Autovacuuming
@ 2006-04-24 21:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Tom Lane @ 2006-04-24 21:36 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; Gourish Singbal <gourish@gmail.com>; pgsql-admin

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



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

* Re: Autovacuuming
@ 2006-04-24 21:49  Jim C. Nasby <jnasby@pervasive.com>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Jim C. Nasby @ 2006-04-24 21:49 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; pgsql-admin

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



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

* Re: Autovacuuming
@ 2006-04-24 22:04  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Alvaro Herrera @ 2006-04-24 22:04 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Gourish Singbal <gourish@gmail.com>; pgsql-admin

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.



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

* Re: Autovacuuming
@ 2006-04-25 06:03  Gourish Singbal <gourish@gmail.com>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Gourish Singbal @ 2006-04-25 06:03 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-admin

Thanks for the reply. we will be reviewing the settings soon.
Also have manually ran the database-wide vacuum on the 50GB database .  (for
safety reasons). the transaction count is 1147399191. we plan to automate
this process once a month or so.

Do we need to Disable the Auto-vacuum deamon when we manually run the
database-wide vacuum command ?.

thanks in advance.


On 4/24/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> 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
>



--
Best,
Gourish Singbal

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

* Re: Autovacuuming
@ 2006-04-25 07:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gourish Singbal <gourish@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Tom Lane @ 2006-04-25 07:19 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-admin

"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



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


end of thread, other threads:[~2006-04-25 07:19 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-24 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 13:09   ` Gourish Singbal <gourish@gmail.com>
2006-04-24 18:00   ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 19:06     ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 21:36       ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-24 21:49       ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 22:04         ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 17:51 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 06:03   ` Gourish Singbal <gourish@gmail.com>
2006-04-25 07:19     ` Tom Lane <tgl@sss.pgh.pa.us>

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