From: | Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info> |
---|---|
To: | Madison Kelly <linux(at)alteeve(dot)com> |
Cc: | Gary Doades <gpd(at)gpdnet(dot)co(dot)uk>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: DB is slow until DB is reloaded |
Date: | 2010-01-04 21:00:56 |
Message-ID: | 1262638856.29852.4.camel@bnicholson-desktop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, 2010-01-04 at 15:53 -0500, Madison Kelly wrote:
> Gary Doades wrote:
> > From your queries it definitely looks like its your stats that are the
> > problem. When the stats get well out of date the planner is choosing a
> > hash join because it thinks thousands of rows are involved where as only
> > a few are actually involved. Thats why, with better stats, the second
> > query is using a loop join over very few rows and running much quicker.
> >
> > Therefore it's ANALYZE you need to run as well as regular VACUUMing.
> > There should be no need to VACUUM FULL at all as long as you VACUUM and
> > ANALYZE regularly. Once a day may be enough, but you don't say how long
> > it takes your database to become "slow".
> >
> > You can VACUUM either the whole database (often easiest) or individual
> > tables if you know in more detail what the problem is and that only
> > certain tables need it.
> >
> > Setting up autovacuum may well be sufficient.
> >
> > Cheers,
> > Gary.
>
> That explains things, thank you!
>
> For the record; It was taking a few months for the performance to become
> intolerable. I've added CLUSTER -> ANALYZE -> VACUUM to my nightly
> routine and dropped the VACUUM FULL call. I'll see how this works.
I think you are going down the wrong route here - you should be looking
at preventative maintenance instead of fixing it after its broken.
Ensure that autovacuum is running for the database (assuming that you
are on a relatively modern version of PG), and possibly tune it to be
more aggressive (we can help).
This will ensure that the condition never comes up.
ps - if you do go with the route specify, no need to VACUUM after the
CLUSTER. CLUSTER gets rid of the dead tuples - nothing for VACUUM to
do.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-01-04 21:03:48 | Re: DB is slow until DB is reloaded |
Previous Message | Madison Kelly | 2010-01-04 20:53:41 | Re: DB is slow until DB is reloaded |