pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuuming
11+ messages / 4 participants
[nested] [flat]
* Autovacuuming
@ 2006-04-24 05:51 Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 17:51 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
@ 2006-04-24 12:41 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 13:09 ` Re: Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 18:00 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-04-24 13:09 ` Gourish Singbal <gourish@gmail.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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-04-24 18:00 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 19:06 ` Re: Autovacuuming 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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 18:00 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
@ 2006-04-24 19:06 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 21:36 ` Re: Autovacuuming Tom Lane <tgl@sss.pgh.pa.us>
2006-04-24 21:49 ` Re: Autovacuuming 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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 18:00 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 19:06 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-04-24 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 18:00 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 19:06 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-04-24 21:49 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 22:04 ` Re: Autovacuuming 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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 12:41 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 18:00 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
2006-04-24 19:06 ` Re: Autovacuuming Alvaro Herrera <alvherre@commandprompt.com>
2006-04-24 21:49 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
@ 2006-04-24 22:04 ` Alvaro Herrera <alvherre@commandprompt.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-24 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
@ 2006-04-24 17:51 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 06:03 ` Re: Autovacuuming 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 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 17:51 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
@ 2006-04-25 06:03 ` Gourish Singbal <gourish@gmail.com>
2006-04-25 07:19 ` Re: Autovacuuming Tom Lane <tgl@sss.pgh.pa.us>
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-24 05:51 Autovacuuming Gourish Singbal <gourish@gmail.com>
2006-04-24 17:51 ` Re: Autovacuuming Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 06:03 ` Re: Autovacuuming Gourish Singbal <gourish@gmail.com>
@ 2006-04-25 07:19 ` Tom Lane <tgl@sss.pgh.pa.us>
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