Skip site navigation (1) Skip section navigation (2)

Re: query speed depends on lifetime of frozen db?

From: "Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: query speed depends on lifetime of frozen db?
Date: 2002-09-27 08:06:26
Message-ID: 3D945EDA.6827.18466783@localhost (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On 27 Sep 2002 at 10:58, Andriy Tkachuk wrote:

> Hi hackers.
> 
> There is ineresting behavior of some select query mentioned in $subj.
> 
> In working db this query takes:
> real    3m10.219s
> user    0m0.074s
> sys     0m0.074s
> 
> it's interesting that vacuum or analyze or reinex not helpfull, BUT
> 
> if dump this db and create it again (whith another name maybe, no matter,
> just for testing) and query the same query on this db, it takes:
> real    0m6.225s
> user    0m0.072s
> sys     0m0.074s
> (other databases continue running)

Looks like a database defrag to me...

> There is no end of this story!
> With some time (couple of days for example) this the same query overloads
> machine on this new test db also! No one working with this db during this
> time. Works continued only with real working databases. Vacuuming was
> as usual (every 2 hours without -f and with it at night one time) :
> as i said this behavior does not depend on any vacuuming.

was that vacuum full or vacuum analyze? Vacuum full should help in this case..

Is it that some tables with few rows gets updated heavily causing lot of dead 
tuples? May be 2 hour is bit too long before vacuum should be called. Try 
running table speific vacuum more periodically..

HTH...


Bye
 Shridhar

--
lawsuit, n.:	A machine which you go into as a pig and come out as a sausage.		--
 Ambrose Bierce


In response to

Responses

pgsql-hackers by date

Next:From: Andriy TkachukDate: 2002-09-27 08:49:08
Subject: Re: query speed depends on lifetime of frozen db?
Previous:From: Andriy TkachukDate: 2002-09-27 07:58:34
Subject: query speed depends on lifetime of frozen db?

pgsql-general by date

Next:From: mourad_dhambriDate: 2002-09-27 08:34:39
Subject: multi-database
Previous:From: Andriy TkachukDate: 2002-09-27 07:58:34
Subject: query speed depends on lifetime of frozen db?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group