From: | Andriy Tkachuk <ant(at)imt(dot)com(dot)ua> |
---|---|
To: | Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: query speed depends on lifetime of frozen db? |
Date: | 2002-09-27 08:49:08 |
Message-ID: | 20020927113628.D48465-100000@pool.imt.com.ua |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Fri, 27 Sep 2002, Shridhar Daithankar wrote:
> 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..
it was full with analize
That's what i want to say, that this is very strange for me that vacuum
not helpfull in this situation!
>
> 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..
As I said there was no work with this test database during the time after which
query becomes overloading. There was just work with another databases.
Thanks,
Andriy.
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2002-09-27 09:20:07 | Re: query speed depends on lifetime of frozen db? |
Previous Message | mourad_dhambri | 2002-09-27 08:34:39 | multi-database |
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2002-09-27 09:20:07 | Re: query speed depends on lifetime of frozen db? |
Previous Message | Shridhar Daithankar | 2002-09-27 08:06:26 | Re: query speed depends on lifetime of frozen db? |