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

Поиск
Список
Период
Сортировка

Autovacuuming

От
"Gourish Singbal"
Дата:
 
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

Re: Autovacuuming

От
Alvaro Herrera
Дата:
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.

Re: Autovacuuming

От
"Gourish Singbal"
Дата:
 
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

Re: Autovacuuming

От
"Jim C. Nasby"
Дата:
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

Re: Autovacuuming

От
"Jim C. Nasby"
Дата:
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

Re: Autovacuuming

От
Alvaro Herrera
Дата:
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

Re: Autovacuuming

От
Tom Lane
Дата:
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

Re: Autovacuuming

От
"Jim C. Nasby"
Дата:
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

Re: Autovacuuming

От
Alvaro Herrera
Дата:
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.

Re: Autovacuuming

От
"Gourish Singbal"
Дата:
 
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

Re: Autovacuuming

От
Tom Lane
Дата:
"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

Is it a bug ?

От
"Pascal Tufenkji"
Дата:

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

 

 

Re: Is it a bug ?

От
"Andy Shellam"
Дата:

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!