Обсуждение: Autovacuuming
Wanted to know when the auto-vacuum daemon will run an database-wide vacuum call to avoid transaction ID wraparound failures.
~ gourish singbal
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.
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)
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
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.
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
"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
Hi,
I just noticed a bug in PostgreSQL 7.3.2
I don't know if it exists in other versions
If I have a UNICODE database and I make the following query
SELECT * FROM table where code like 'abcz%';
Or
SELECT * FROM table where code like 'abcz_';
(or anything that ends with z% or z_)
It gives the following error :
ERROR: Invalid UNICODE character sequence found (0xc000)
Other queries like
SELECT * FROM table where code ilike 'abcz%';
SELECT * FROM table where code like '%abcz%';
Or the same query but in an SQL_ASCII database don’t give the same error
Is it a bug in Postgres ????
I’d appreciate an explanation
Thx
Pascal
Hi Pascal,
7.3 is now quite out-dated – have you tried the latest release, 8.1.3, to see if the problem is still present?
Andy
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Pascal Tufenkji
Sent: 25 April 2006 10:02 am
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Is it a bug ?
Hi,
I just noticed a bug in PostgreSQL 7.3.2
I don't know if it exists in other versions
If I have a UNICODE database and I make the following query
SELECT * FROM table where code like 'abcz%';
Or
SELECT * FROM table where code like 'abcz_';
(or anything that ends with z% or z_)
It gives the following error :
ERROR: Invalid UNICODE character sequence found (0xc000)
Other queries like
SELECT * FROM table where code ilike 'abcz%';
SELECT * FROM table where code like '%abcz%';
Or the same query but in an SQL_ASCII database don’t give the same error
Is it a bug in Postgres ????
I’d appreciate an explanation
Thx
Pascal
!DSPAM:14,444debdd33695139936371!